Additional Backup Needs and Some Thoughts Which May Help

If you are a DBA of a mission critical database which usually mean there is no tolerance of downtime, data loss and mean time to recover should be minimum, ARCHIVELOG mode and a RMAN hot backup will be a mandatory starting point for a strategy.

The easiest way of scheduling this kind of a backup is done by Database(or Grid) Control, after 10.2 using Oracle’s advised backup strategy option which is a Flash Recovery Area based incremental RMAN backup will be what I advice if you already have enough disk space for FRA. Lutz is not blogging for a while and I miss Lutz’s RMAN posts so I guess this one can be another good quick guide.

Why restore before recover? The new Oracle 10g Backup Strategy

But what if you only want to restore a parameter table with lets say 86 rows, or an apex or some pl/sql application’s previous release, or just a metadata of a simple database object which is now changed or dropped? Here we all have our own workarounds mostly depending on the database version and tools we use around the database. Data Guard and Flashback options if you do have may help or you can customize your Backup strategy for these kind of alternative needs.

In order to restore a parameter table with 86 rows it should be throwing a huge rock to a frog if you go with your RMAN backup, so choosing these kind of parameter tables and including them into a daily binary table level export may be the quickest way to revert them back.

What about a PL/SQL based need, here again a daily norows binary export backup may help a lot.

A data-pump norows backup example

# CREATE or REPLACE DIRECTORY backup_dir AS '/ods01/backup/daily_norows/dp';
/dwh0/app/oracle/product/11.1.0.1/bin/expdp $BACKUP_USERNAME/$BACKUP_PASSWORD 
DUMPFILE=daily_full_norows_dp_backup.dmp.$BACKUP_DATE LOGFILE=daily_full_norows_dp_backup.log.$BACKUP_DATE 
DIRECTORY=backup_dir FULL=y EXCLUDE=TABLE_DATA

A traditional export norows backup example

/dwh0/app/oracle/product/11.1.0.1/bin/exp $BACKUP_USERNAME/$BACKUP_PASSWORD 
FILE=/ods01/backup/daily_norows/exp/daily_full_norows_dp_backup.dmp.$BACKUP_DATE 
LOG=/ods01/backup/daily_norows/exp/daily_full_norows_dp_backup.log.$BACKUP_DATE 
FULL=y ROWS=n
   

And what if you are using Apex applications or Warehouse Builder repository, then again taking daily tool level export backups will help.

OWB 11g Repository Shell Script Backup Example

There can be additional thoughts of course, like saving database structure as a SQL file daily or immediately after any structure change.

A Simple Backup Control File to Trace Shell Script Example

/dwh0/app/oracle/product/11.1.0.1/bin/sqlplus -s > /ods01/backup/daily_norows/backup_control_file_to_trace.log.$BACKUP_DATE 
2> /ods01/backup/daily_norows/backup_control_file_to_trace.err.$BACKUP_DATE <<EOF
$BACKUP_USERNAME/$BACKUP_PASSWORD
@/ods01/backup/daily_norows/backup_control_file_to_trace.sql $BACKUP_DATE 
exit ;
EOF

fatih@oracle $ cat /ods01/backup/daily_norows/backup_control_file_to_trace.sql
alter session set tracefile_identifier = backup_controlfile_to_trace;
alter database backup controlfile to trace;
SET ECHO OFF              
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 1000
spool /ods01/backup/daily_norows/backup_control_file_to_trace.name.&1
-- available after 11.1
select value from v$diag_info where name like 'Default Trace%' ;
spool off
SET FEEDBACK ON
SET PAGESIZE 24
SET ECHO ON
   

But I think critical point of all is that whatever you do do not forget to use a scheduler to automatize these jobs and control the logs of them periodically :)

Simple unix cron settings to automatize daily backups

fatih@oracle $ crontab -l
40 17 * * * /ods01/backup/daily_norows/daily_norows.sh > /ods01/backup/daily_norows/daily_norows.log 
2> /ods01/backup/daily_norows/daily_norows.err
00 18 * * * /ods01/backup/backup_repos.sh > /ods01/backup/backup_repos.log 
2> /ods01/backup/backup_repos.err