snapshot-based backup

Do it before applying Oracle PSU/BP patches or change a configuration.

Steps taken:

lvcreate -L1G -s -c 32K -n root_snap /dev/VGExaDb/LVDbSys1

e2label /dev/VGExaDb/root_snap DBSYS_SNAP

mkdir /mnt/snap/root
mount /dev/VGExaDb/root_snap /mnt/snap/root -t ext4

mount -l

lvcreate -L5G -s -c 32K -n u01_snap /dev/VGExaDb/LVDbOra1

e2label /dev/VGExaDb/u01_snap DBORA_SNAP

mkdir -p /mnt/snap/u01

mount /dev/VGExaDb/u01_snap /mnt/snap/u01 -t ext4

mount -l

umount /mnt/bkp_tst

cd /mnt/snap/

tar -pjcvf /mnt/bkp_dump/root_exaoradb01.tar.bz2 * /boot –exclude /mnt/bkp_dump/root_exaoradb01.tar.bz2 –exclude /mnt/bkp_dump > /tmp/bkptar.stdout 2> /tmp/bkptar.stderr

— umount snapshots
cd /

umount /mnt/snap/u01
umount /mnt/snap/root

lvremove /dev/VGExaDb/u01_snap
lvremove /dev/VGExaDb/root_snap

Documentation:

http://www.toadworld.com/platforms/oracle/w/wiki/11617.exadata-create-snapshot-based-backup-of-oracle-linux-database-server

http://docs.oracle.com/cd/E80920_01/DBMMN/maintaining-exadata-database-servers.htm#DBMMN21377

 

 

Purging histograms

Oracle will automatically create histograms during night maintenance window. This might impact a query performance, so use dbms_stats to purge it. You can also change or disable the automatic collection statistics job.

The following command will purge a column histogram:

exec dbms_stats.delete_column_stats( ownname=>'&OWNER', tabname=>'&TABLE', colname=>'&COLUMN', col_stat_type=>'HISTOGRAM');

Or, if you need to purge all histograms from a table, use this one:

select 'exec dbms_stats.delete_column_stats('''||owner||''','''||table_name||''','''||column_name||''', col_stat_type=> ''HISTOGRAM''); '
from dba_tab_columns
where owner = '&OWNER' and table_name = '&TABLE_NAME' and histogram not in ('NONE')
/

Another good info at:

https://blogs.oracle.com/optimizer/how-do-i-drop-an-existing-histogram-on-a-column-and-stop-the-auto-stats-gathering-job-from-creating-it-in-the-future

https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work

http://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm
Continue reading “Purging histograms”