RMAN Incrementally Updated Backups

In this post I will explore the RMAN Incremental Backups. Incremental backups are used best in Data Warehouse environments where there is a lot of data to backup and keeping multiple copies on the disk takes space.


There are few alternatives Oracle provides to optimize backups of large databases and the incrementally updated backups strategy is one of them. Actually is the Oracle recommended backup policy!

You can NOT use OS methods to take Incremental Backups. Only RMAN will let you take Incremental backups.

The incrementally updated backups allow you to apply database changes incrementally (i.e. every day) to an image copy (not zipped) full backup! That is you update the level 0 full backup with new data incrementally all the time. This way you don't have to keep old full backups, spread over days, separately with their individual archive logs and controlfiles. Even you don't have to take full backups periodically any more! You only take one full backup (level 0) at the beginning and you keep on updating it every day incrementally with the new changes in the database. By applying the changes to the last backup copy, you are bringing the backup copy forward in time. This is also called rolling forward the backup copy.

In my case I used this backup methodology to reduce the ammount of disk space used for backups, as I didn't need to keep and individual full backup of the datawarehouse for each day. I only took one full backup (level 0) at the beginning. And my retention policy was thereafter for 7 days. In this case the retention policy is the duration of time I kept the incremental backups before applying them on the full backup.

To improve the performance of incrementally updated backups another feature called Change Tracking has also been introduced in Oracle 10g. This lets you record the changes in the blocks of datafiles in a separate datafile in the database called Change Tracking File. Then when the time for backup comes, RMAN reads the Change Tracking File to find out the changes which happened to the database instead of scanning whole datafile. This makes the backup much more faster.

So, before running the incrementally updated backup backup script, I enable change tracking in the database like this:

1. First check to see if change tracking is already enabled by querying the DBA view like this:

SQL> conn / as sysdba

Connected

SQL> SELECT STATUS''FILENAME''BYTES FROM V$BLOCK_CHANGE_TRACKING
SQL> /

STATUS''FILENAME''BYTES
--------------------------------------------------------------------------------
DISABLED

2. Then enable Change Tracking.

SQL> alter database enable block change tracking using file '/usr/local/oracle/testdw0/rman_change_track.dbf';

Database altered

The Script I used to take an Incrementally Updated Backup is rman_incr_backup.sh below. This is a sript which uses non-default RMAN location as I explicitly indication where the backups should be stored on disk. This version doesn't use FRA (Flash Recovery Area) either.

# ########################################
# !/bin/bash
# Unix controls
trap cleanup 1 2 3 15
cleanup()
{
echo "Caught CTRL-C Signal ... exiting script."
exit 1
}
# Oracle Variables
export ORACLE_SID=testdw0
export ORACLE_BASE=/usr/local/oracle
export ORACLE_HOME=/usr/local/oracle/product/10.2.0.1
export PATH=$PATH:${ORACLE_HOME}/bin
# RMAN INCREMENTALLY UPDATED BACKUPS (Window of 24 hours)
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/local/oracle/backups/rman_bkps/dwbacks/atbckp_cntrlfile_testdw0%F';
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK FORMAT '/usr/local/oracle/backups/rman_bkps/dwbacks/databasefiles_%d_%u_%s_%T';
CROSSCHECK BACKUP;
RECOVER COPY OF DATABASE with TAG 'testdw0_incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY with TAG 'testdw0_incr_update' DATABASE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP as compressed backupset ARCHIVELOG ALL format '/usr/local/oracle/backups/rman_bkps/dwbacks/archivelogs_%d_%u_%s_%T' DELETE INPUT;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#########################################

RMAN Recovery

In this post I will try to explain how a database recovery is done on a New Host using the latest RMAN full Hot Backup.

The recovery is done on a New Host with the same directory structure and the same database name and DBID. Lets assume that you have used the Rman Hot Backup Script provided in this blog, which takes backups of the database using a retention policy with a recovery window of 2 days. The full script is here:

http://www.databasesystems.info/2008/02/rman-hot-backup-script.html

That is, it will only keep backups and archive logs necessary to recover in any point of time in the last 2 days. During recovery, RMAN will use the latest backups and archivelogs from the backup directory.


1. On the New Host connect to RMAN after you set ORACLE_SID to newSID

oracle@NEWHOST:~ . oraenv
ORACLE_SID = [testdw0] ? testdb0



oracle@NEWHOST:~$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jun 20 15:00:13 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)

RMAN>

2. Set the DBID to the DBID of the database you want to restore and recover, you can find the DBID in backup logs or in the autobackup controlfile name.

RMAN> set DBID 347812949

executing command: SET DBID

RMAN>

3. Start the database with nomount. It will fail to startup properly with LRM-00109 as below, because you don't have init.ora file yet for your instance to start.

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/local/oracle/product/10.2.0.1/dbs/inittestdb0.ora'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes


RMAN>

4. Indicate the location of your controlfile autobackups.

As this location contains the controlfile which has built into it your init.ora as well.

RMAN> set controlfile autobackup format for device type disk to '/usr/local/oracle/backups/rman_cntrl/autobackup_control_file%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

RMAN>

5. Restore the init.ora file.

It is perfectly valid at this point aflter you restore the init.ora to edit it to suit your needs. Change directory locations for controlfiles etc...

RMAN> run
{
allocate channel c1 type disk;
restore spfile to pfile '/usr/local/oracle/product/10.2.0.1/dbs/inittestdb0.ora' from autobackup;
shutdown abort;
};

allocated channel: c1
channel c1: sid=36 devtype=DISK

Starting restore at 20-JUN-07

channel c1: looking for autobackup on day: 20070620
channel c1: autobackup found: /usr/local/oracle/backups/rman_cntrl/autobackup_control_filec-347812949-20070620-02
channel c1: SPFILE restore from autobackup complete
Finished restore at 20-JUN-07

Oracle instance shut down

RMAN>

6. Restart the instance with the proper init.ora file you restoed in step 5.

RMAN> startup force nomount;

Oracle instance started

Total System Global Area 608174080 bytes

Fixed Size 1220844 bytes
Variable Size 192941844 bytes
Database Buffers 411041792 bytes
Redo Buffers 2969600 bytes

RMAN>

7. Restore the controlfile

First indicate where is the controlfile you want to use.

RMAN> set controlfile autobackup format for device type disk to '/usr/local/oracle/backups/rman_cntrl/autobackup_control_file%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Now restore the controlfile.

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 20-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20070620
channel ORA_DISK_1: autobackup found: /usr/local/oracle/backups/rman_cntrl/autobackup_control_filec-347812949-20070620-02
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/usr/local/oracle/testdb0/control01.ctl
output filename=/usr/local/oracle/testdb0/control02.ctl
output filename=/usr/local/oracle/testdb0/control03.ctl
Finished restore at 20-JUN-07

released channel: ORA_DISK_1

RMAN>

8. Now you have a controlfile, mount the database

RMAN> ALTER DATABASE MOUNT;

database mounted

RMAN>

9. Indicate RMAN where your hotbackups are

This is where you tell RMAN where you backups are. Try changing backup files directory and re-run this command with the appropriate location and you will see that it will 'find' the files and update, the control file or catalog, accordingly.

RMAN> CATALOG START WITH '/usr/local/oracle/backups/rman_bkps/hot_backups/';

searching for all files that match the pattern /usr/local/oracle/backups/rman_bkps/hot_backups/
no files found to be unknown to the database

RMAN>

10. Restore the database

RMAN> restore database;

Starting restore at 20-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /usr/local/oracle/testdb0/system01.dbf
restoring datafile 00002 to /usr/local/oracle/testdb0/undotbs01.dbf
restoring datafile 00003 to /usr/local/oracle/testdb0/sysaux01.dbf
restoring datafile 00004 to /usr/local/oracle/testdb0/users01.dbf
restoring datafile 00005 to /usr/local/oracle/testdb0/ts1.dbf
restoring datafile 00006 to /usr/local/oracle/testdb0/apex.dbf
restoring datafile 00007 to /usr/local/oracle/testdb0/FLOW_1.dbf
channel ORA_DISK_1: reading from backup piece /usr/local/oracle/backups/rman_bkps/hot_backups/databasefiles_TESTDB0_s3iko9hm_899_20070620
channel ORA_DISK_1: restored backup piece 1
piece handle=/usr/local/oracle/backups/rman_bkps/hot_backups/databasefiles_TESTDB0_s3iko9hm_899_20070620 tag=TAG20070620T104510
channel ORA_DISK_1: restore complete, elapsed time: 00:03:16
Finished restore at 20-JUN-07

RMAN>

11. Recover the database, will automatically apply archivelogs and will stop (will fail like below on the last log)

This is normal as Oracle will always try to roll-forward the database to the latest log, will always ask for the latest archivelogs and in this will stop on the last available one. At this point we will assume that we are doing CANCEL based recovery and open the database with RESETLOGS option. You could avoid this error by first finding out which archivelog sequence is the last one and the recovering up to that point. Since RMAN does NOT do CANCEL BASED RECOVERY. Nevertheless here is what happens if you just say RECOVER DATABASE at this point

RMAN> recover database;

Starting recover at 20-JUN-07
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=333
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=334
channel ORA_DISK_1: reading from backup piece /usr/local/oracle/backups/rman_bkps/hot_backups/archivelogs_TESTDB0_s5iko9p1_901_20070620
channel ORA_DISK_1: restored backup piece 1
piece handle=/usr/local/oracle/backups/rman_bkps/hot_backups/archivelogs_TESTDB0_s5iko9p1_901_20070620 tag=TAG20070620T104905
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archive log filename=/usr/local/oracle/arch/testdb0_1_333_614535884.arc thread=1 sequence=333
archive log filename=/usr/local/oracle/arch/testdb0_1_334_614535884.arc thread=1 sequence=334
unable to find archive log
archive log thread=1 sequence=335
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/20/2007 15:22:07
RMAN-06054: media recovery requesting unknown log: thread 1 seq 335 lowscn 1793924713

RMAN>

You could have avoided seing this ugly error message at the end of your recovery, if you only knew until which archivelog sequence number (usually last sequence number avaliable) you want to recover to. You can obtain this last sequence number by issuging the command:

RMAN> list backup of archivelog from time='sysdate-7';

This would have shown you the last available sequence number from your archivelog backups and then you would more gracefully recover to that sequence number like this:

RMAN> recover database until sequence=;

Then next, you would open the database as in step 12 below.


12. Then you have to open the database with RESETLOGS option


You have to since you are doing a cancel based recovery from a full hot backups with no online redo logs. Redo logs are not present and this step creates them and resets them to 0.

RMAN> alter database open resetlogs;

database opened

RMAN>

You are done! You have now the database fully operational on the new host from an RMAN full hot backup.