Monthly Archives: November 2010

Rman, duplicate from incrementally updated backup

In the last post i addressed how to create and maintain an incrementally updated rman backup, and how to switch to the backup, in case some corruption happens to the production database.

In this post, instead of doing the switch, we will use the incrementally updated backup to create a cloned copy of the original database. The practical use of this method will be that, if you are creating incrementally updated backups every night, you can use the backup as the source to clone the database (ie you dont have to run a whole backup of your database again).

rman has a command named duplicate that allows you to clone an existing database.

In this case we are cloning a database named rk01, to another database named rk02 on the same host, using a incrementally updated backup, with the rman duplicate command.

First follow the steps to create the incrementally updated backup from my previous post.

Enable Archivelog And Block Change Tracking
Run a level 0 full image copy backup of the database
Perform updates in the database
Perform an incremental level 1 backup
Apply the incremental level 1 backup to the level 0 image copy

At this point after the incrementally updated backup is created, in the previous post, i was doing a switch. Instead of that we will do the rman duplicate.

Create a spfile for the rk02 database (I usually make a pfile from spfile from rk01, edit the pfile change all the occurances of rk01 to rk02 and create the spfile for rk02 from this pfile. Also create the necessary directories for bdump,adump etc.)

Create a password file for the rk02 database (orapwd file=$ORACLE_HOME/dbs/orapwrk02 entries=30)

Startup the database rk02 in nomount mode

export ORACLE_SID=rk02

sqlplus / as sysdba

startup nomount

Switch the current logfile in the rk01 database

This is an important step. Miss this and your rman duplicate will likely fail. The reason is that the scn until which the incremental backup is taken (And was applied to the original backup copy), is still in the online redo log. When you do a duplicate rman looks for a backup that has a ckp scn that is < the first_change# in your current online redo log. Once you do a switch, and then run the duplicate, rman will be able to find the incrementally updated backup as the source to clone from.

export ORACLE_SID=rk01

sqlplus / as sysdba

alter system archive log current;

Perform the rman duplicate.

Then issue the rman duplicate command (If you are cloning to a physically separate host, you will have to create appropriate listener.ora entries, tnsnames.ora entries and make the location of the backup copy available to your target host).

export ORACLE_SID=rk02

run the following script.

#!/bin/bash
rman target="sys/manager@rk01" auxiliary / log=/u01/oraback/clonefromiub.log << EOF
RUN
{
  SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/rk02/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/rk02/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/rk02/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/u01/oradata/rk02/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/u01/oradata/rk02/example01.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/rk02/temp01.dbf';
  DUPLICATE  TARGET DATABASE  TO rk02
    LOGFILE
      GROUP 1 ('/u01/oradata/rk02/redo01a.log',
               '/u01/oradata/rk02/redo01b.log') SIZE 4M REUSE,
      GROUP 2 ('/u01/oradata/rk02/redo02a.log',
               '/u01/oradata/rk02/redo02b.log') SIZE 4M REUSE;
}
EXIT;
EOF

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

Copying database files from one asm instance to another using asmcmd

Often one might have to copy datafiles from one database instance to another (And hence one ASM instance to another), for eg: when copying datafiles from prod to development.

You can use the cp command in asmcmd to accomplish this. cp can copy files from one instance to another. You can specify the username/password@connecstring for the target asm instance in the cp command.

ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295 sys/password@myserver.+ASM:/scratch/backup/myexamples.bak