Autonomous RMAN online backup

Regardless there are many topics written about RMAN and way of making a lot of variety of backup strategy (scenarios), for me a nice approach was a challenge to create autonomous consistent backup. "Autonomous consistent backup" stand for backup which could be run in any moment on any configuration, with 100% success its own consistency against parent database using its own files and no other external resource (like RMAN catalog for an example). This backup can be used for testing as well for some reporting purpose.

Autonomous consistent backup is typically created as a “point in time” process based on some periods (monthly, quarterly, yearly etc.) in a case when company data backup retention policy fail beyond supported time (company does not save so many backups in the past) and it is based on ad hock basis… regardless time of run or configuration of database.

Except condition that database must be in archive log mode, main problem is always the same: how to determine which archived redo log files should we backup along with data files to restore database from this backup set? Here is an example one way of treating this issue.

Even thought, we could find dynamically number of groups and its members, just to make easy of understanding the procedure I'll use fixed values. In my case I’ll show solving the problem with four groups with two members in each group (number of members are not important but only number of groups). This value is easy to get:

select distinct THREAD# from v$log order by 1;
THREAD#
----------
1
2
3
4

If you use RAC then you can get the same result through gv$log view in a way:

SELECT THREAD# FROM gv$log WHERE inst_id = THREAD#;

But I see no advantage in this query so probably the best way best way is to use v$log in all situations, whether is RAC or single instance database.

Basic idea of the whole example, which can be easy copied in bash script (same may be done on Windows as well with some changes in script), should be like this:
   1. Find all redo log’s minimum sequence numbers in moment before starting of backup
   2. Switch archive log
   3. Backup database
   4. Switch archive log
   5. Find all redo log’s maximum sequence numbers
   6. Backup archived log files created between min and max sequence numbers

1. Find all redo log’s minimum sequence# numbers in moment before starting of backup
Finding minimum sequence# is doen through easy sqlplus command:

select  MIN(sequence#) from v$log where thread#=1;
select  MIN(sequence#) from v$log where thread#=2;
select  MIN(sequence#) from v$log where thread#=3;
select  MIN(sequence#) from v$log where thread#=4;

In bash script all together for four threads looks like:

MIN1=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=1;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MIN2=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=2;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MIN3=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=3;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MIN4=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=4;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`

Interesting part is how easy is in bash script to read result from sqlplus prompt back and forth.

2-4 Switch archive log, backup database, switch archive log
Here is classic RMAN script with some difference from classic backup scripts at the begging and at the end:

rman target / nocatalog log=autonomous_backup.log
run {
sql "alter system archive log current";
backup database include current controlfile;
sql "alter system archive log current";
restore database preview;
}

Do not use other command then “alter system switch logfile” on RAC environment. Command "restore database preview" gives you comprehensive explanation about all necessary files and data for this backup-very useful for restore/recover part in the future.

5 Find all redo log’s maximum sequence# numbers
Now it easy to repeat step from step 1 with assigning values in different variables in a slightly changed script.

MAX1=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=1;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MAX2=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=2;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MAX3=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=3;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MAX4=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=4;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`

6 Backup archived log files created between min and max sequence# numbers
Now is RMAN archived log backup part where we use previously collected MINx and MAXx values:

rman target / nocatalog log=autonomous_backup_arch.log
run {
backup archivelog sequence between ${MIN1} and ${MAX1} thread 1;
backup archivelog sequence between ${MIN2} and ${MAX2} thread 2;
backup archivelog sequence between ${MIN3} and ${MAX3} thread 3;
backup archivelog sequence between ${MIN4} and ${MAX4} thread 4;
}

In all RMAN scripts I didn't wanted to place "allocate channel..." commands because this depends on your default RMAN configuration. Put it before all operation if you need it.

As you can see all scripts are run with nocatalog switch even thought it can run against the catalog as well. This solution works against RAC as well in non RAC database.

To make this script work put all together in one .sh file and run as ora_dba user with appropriate environment. If will be more interest for this topic, I can post bash script which will dynamically examine number of groups in for loop instead of (as in this example) fixed 4 values.