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




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:
Continue reading “Purging histograms”