Rman, incrementally updated backups, switch to copy

Some oracle customers use SAN based technologies to keep a backup copy of the database in sync (Think EMC Timefinder and BCV’s). The typical backup strategy tends to be, to keep a full copy of the database, and on a daily basis to update this backup copy of the database (Typically a BCV synchronize  operation), with only the incremental changes that have happened at the disk level.

In case off a loss of file system in the primary disks, they can switch the database to run from another copy which is maintained as a backup by the Storage subsystem.

A similar strategy can be implemented for  oracle databases, using RMAN incrementally updated backups (Without requiring any expensive SAN level software for maintaining these copies). These backup copies can be updated on a daily basis using just the incremental changes in the database. In case of a loss in the database, you can switch to this copy to be used as the database. You can also generate clones from these backups using the rman duplicate command.

At a high level the steps involved are

  • Configure the database to run in archivelog mode
  • Configure block change tracking at the database level
  • Create a level 0 full image copy of the database using rman
  • Create level 1 incremental backups of the database and update the level 0 copy with this incremental backup
  • In case of need, use the rman switch command to switch the database to this incrementally updated backup.

Below is an example of how this is done.

Enable Archivelog and Block Change Tracking

sqlplus / as sysdba
startup mount;
alter database archivelog;
alter system set log_archive_dest_1=’LOCATION=/u01/orarch/rk01′ scope=spfile;
alter database open;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE ‘/u01/orarch/rk01/rman/rman_change_track.f’ REUSE;

Run a level 0 full image copy backup of the database.

A sample script to run the full image copy backup is below
#!/bin/bash
rman target=”sys/manager@rk01″ log=/u01/oraback/fullcopy.log << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%b’;
BACKUP INCREMENTAL LEVEL 0 as copy DATABASE TAG ‘fullcopy’;
RELEASE CHANNEL ch1;
}
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%U’;
BACKUP as copy ARCHIVELOG ALL;
BACKUP as copy CURRENT CONTROLFILE;
RELEASE CHANNEL ch1;
}
EXIT;
EOF
In the above script there are two distinct rman scripts.
The first one backs up the database and the second one backs up the control file and archivelogs.
I am using the %b format string for the database backup to keep the database file names intact (In preparation for a switch) (You dont have to keep the datafile names the same, just makes it easy to understand).
You cannot do controlfile and archivelog backups using the %b format string. This is why i have two separate scripts.
I tag the backup with a name ‘fullcopy’, which later helps us with applying the incremental backup to this tag.

Now perform some updates in the database. (This simulates database changes, after which we can run an incremental backup).

#!/bin/bash
sqlplus hr/hr@rk01 <<EOC
set echo on;
select employee_id,first_name,last_name from
employees
where
employee_id = 194
/
update employees set last_name=’Adams’
where
employee_id=194
/
select employee_id,first_name,last_name from
employees
where
employee_id = 194
/
EOC
I updated the last_name for the employee number 194.

Now perform an incremental level 1 backup of the database

#!/bin/bash
rman target=”sys/manager@rk01″ log=/u01/oraback/incr.log << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%U’;
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG ‘fullcopy’
DATABASE;
RELEASE CHANNEL ch1;
}
EXIT;
EOF

Now Apply the incremental backup to the original level 0 image copy

#!/bin/bash
rman target=”sys/manager@rk01″ log=/u01/oraback/incrapply.log << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%U’;
RECOVER COPY OF DATABASE WITH TAG ‘fullcopy’;
RELEASE CHANNEL ch1;
}
EXIT;
EOF
Run the following commands to capture the location of the datafiles for the database
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/oradata/rk01/users01.dbf
/u01/oradata/rk01/undotbs01.dbf
/u01/oradata/rk01/sysaux01.dbf
/u01/oradata/rk01/system01.dbf
/u01/oradata/rk01/example01.dbf

Now switch the database to run from the copy

Shutdown and startup and mount the database; (Do not open the database)
oracle@rramads-us2:~$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Sat Nov 6 17:44:40 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: RK01 (DBID=2035601811, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy “/u01/oraback/system01.dbf”
datafile 2 switched to datafile copy “/u01/oraback/sysaux01.dbf”
datafile 3 switched to datafile copy “/u01/oraback/undotbs01.dbf”
datafile 4 switched to datafile copy “/u01/oraback/users01.dbf”
datafile 5 switched to datafile copy “/u01/oraback/example01.dbf”
RMAN> recover database;
Starting recover at 06-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oraback/system01.dbf
destination for restore of datafile 00002: /u01/oraback/sysaux01.dbf
destination for restore of datafile 00003: /u01/oraback/undotbs01.dbf
destination for restore of datafile 00004: /u01/oraback/users01.dbf
destination for restore of datafile 00005: /u01/oraback/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oraback/14lsbd4b_1_1
channel ORA_DISK_1: piece handle=/u01/oraback/14lsbd4b_1_1 tag=FULLCOPY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-NOV-10
RMAN> alter database open;
database opened
Now if you query the file_names they will point to the /u01/oraback directory

SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/oraback/users01.dbf
/u01/oraback/undotbs01.dbf
/u01/oraback/sysaux01.dbf
/u01/oraback/system01.dbf
/u01/oraback/example01.dbf
Check if the changes we made to the hr.employees table are still there (To ensure that the incremental backup did work).
SQL> select last_name from hr.employees where employee_id = 194;
LAST_NAME
————————-
Adams

Leave a Reply

Your email address will not be published. Required fields are marked *