Tuesday, April 29, 2008

Create a standby database with RMAN

After the incident with missing forced logging in primary database the logical standby had to be created again. Compared to what is normal these days the database is not huge, but the standby database remains on a server in a different city and the link between isn't exactly a digital super highway. Also I had to restrict the consumed bandwidth during transfer with secure copy (scp -l 2500, restricts to 2500 kbs) or else connections to the primary database might suffer.

RMAN provides the command duplicate target database for standby, but you don't have to use this in order to create a physical standby. Actually some prefer not to use the duplicate command; the target database RMAN needs to be connected to while the duplicate process executes is the source database, which may be your main production database. If it takes some time to finish and you forget that your target is not the new database (the clone), but the primary, and you then do a shutdown immediate... well, you probably do that mistake only once.

Anyway you need a backup of the database and of the controlfile in standby format. In RMAN:

run {
backup current controlfile for standby
format '/s01/backup/foo/standby.ctl';
allocate channel c1 device type disk
format '/s01/backup/foo/%U' maxpiecesize 10G
backup database ;

I use the MAXPIECESIZE-option in order to limit the size of each file, it makes it all more manageable. Another useful option is RATE, which limits how much is read pr second from the database files. I did not use it since RMAN did not put a severe load on our SAN while the backup was running. The next three days where spent copying the files to the other server.

The instance for the physical standby was started in nomount and with RMAN connected to the soon to be physical database as target, restore was done like this:

restore standby controlfile
from '/s01/backup/bar/standby.ctl';
alter database mount;
catalog start with '/s01/backup/bar/ld';
crosscheck backup;
restore database;

The path in the catalog-command is just a common part for the backup pieces, the files started with ld. The restore actually finished several days after the backup was started on primary and physical standby would have to apply many archivelogs to catch up with primary. Here is where another useful feature of RMAN comes to rescue, incremental backup from a specific SCN. With the database at least in mount run the following query to find SCN of the physical standby:

select current_scn from v$database;

Then on the primary start an incremental backup in RMAN:

backup incremental from scn 42 database
format '/s01/backup/foo/incr%U';

42 might not be your SCN (wasn't our either), I used the SCN from the query minus one (a small overlap, didn't bother to check the manual). The incremental backup was of course much smaller and took less time than a full backup to complete and transfer. After the backup was transferred to the other server I executed an incomplete recovery:

catalog start with '/s01/backup/bar/incr';
recover database noredo;

This does not take much time to complete and the physical standby is not far behind primary. Normal apply can now be started with:

alter database recover managed standby database disconnect;

No comments :