Written by: ilmarkerm

Category: Blog entry

Published: July 25, 2009

It’s common to use a production database copy for testing. But how to keep it fresh? If production database is large and located on a remote site (even on another country/continent), then copying the full database backup over network may be too unreasonable and if the test database needs to be open for a longer time, then transporting and storing production archived logs to test site may be too unreasonable also.
In this situation Oracle has three great technologies that can help: physical standby database, flashback database and RMAN.

Here I’m using Oracle Database 10.2.0.4 EE.

Initial setup

This is just to create a test database as physical standby database. Do it in any way you like. Just make sure you create a guaranteed restore point before activating the standby.

Create backup and standby controlfile. Give backupsets some tag, that can later be used for cleaning up the backups from primary database.

RMAN> backup device type disk format '/home/oracle/backup/%U' as compressed backupset
  tag 'testdb_init' database INCLUDE CURRENT CONTROLFILE FOR STANDBY
  plus archivelog;

Starting backup at 24-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
... and so on
Finished backup at 24-JUL-09

I also need to take spfile and password file.

$ cp $ORACLE_HOME/dbs/spfiletest1.ora /home/oracle/backup/
$ cp $ORACLE_HOME/dbs/orapwtest1 /home/oracle/backup/

Now, copy everything over to the test database. I’m leaving all file locations exactly the same as in primary database.

$ scp /home/oracle/backup/* oracle@testdb:/home/oracle/backup/

On test server:

$ cd backup/
$ cp spfiletest1.ora $ORACLE_HOME/dbs/
$ cp orapwtest1 $ORACLE_HOME/dbs/

Start up the test database instance and change service name to be different from the one used in production.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268920 bytes
Variable Size             167773000 bytes
Database Buffers          436207616 bytes
Redo Buffers                2924544 bytes
SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      test1

SQL> alter system set service_names='test_test1' scope=both;

System altered.

Restore controlfile and restore the datafiles.

RMAN> restore controlfile from '/home/oracle/backup/0tkkuvob_1_1';

Starting restore at 24-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=/u01/app/oracle/oradata/TEST1/controlfile/o1_mf_56lyof8g_.ctl
output filename=/u01/app/oracle/flash_recovery_area/TEST1/controlfile/o1_mf_56lyofm4_.ctl
Finished restore at 24-JUL-09

RMAN> alter database mount;
RMAN> sql "alter database flashback off";

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/0ukkuvok_1_1
File Name: /home/oracle/backup/0tkkuvob_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/0ukkuvok_1_1
File Name: /home/oracle/backup/0tkkuvob_1_1

RMAN> restore database;

Starting restore at 24-JUL-09
...
Finished restore at 24-JUL-09

RMAN> recover database;

Starting recover at 24-JUL-09
using channel ORA_DISK_1

starting media recovery
unable to find archive log
archive log thread=1 sequence=51
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2009 17:09:50
RMAN-06054: media recovery requesting unknown log: thread 1 seq 51 lowscn 360057

Recover database will fail at the end, but this is only because it didn’t find the current log. This doesn’t matter here.

Test that media recovery process will start.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process, status, sequence# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG         51

SQL> alter database recover managed standby database cancel;

Database altered.

If MRP* is started and waiting for log (and no error in alert.log), then everything is good.

Set up flashback. Make sure that you have enough diskspace in FRA (flashback recovery area) for all flashback logs generated while the test database is open.
After flashback has been enabled, create a guaranteed restore point.

SQL> alter database flashback on;

Database altered.

SQL> create restore point prod_point guarantee flashback database;

Restore point created.

Now, just activate standby database and open it.

SQL> alter database activate standby database;
SQL> startup mount force
SQL> alter database set standby database to maximize performance;
SQL> alter database open;

Delete the backups from both databases. Just for cleaning up.

RMAN> delete backup tag 'testdb_init';

Syncing with production

To make it more interesting, I’ll add some new datafiles to the primary database.

SQL> alter tablespace sysaux add datafile size 10m autoextend on next 10m maxsize 1g;

Tablespace altered.

SQL> create tablespace test5891 datafile size 10m autoextend on next 10m maxsize 1g;

Tablespace created.

First, in test database find out the restore point SCN. This is the point from where to create the incremental backup.

SQL> select to_char(scn) from v$restore_point where name = 'PROD_POINT';

TO_CHAR(SCN)
---------------
390703

Find out the current production database log sequence.

SQL> select sequence# from v$log where status = 'CURRENT';

 SEQUENCE#
----------
        57

SQL> alter system checkpoint;

Create incremental backup from production and transport it to the test site. Also include all archived logs that were generated during backup.

RMAN> BACKUP INCREMENTAL FROM SCN=390703 device type disk format '/home/oracle/backup/%U'
  as compressed backupset database INCLUDE CURRENT CONTROLFILE FOR STANDBY;

RMAN> sql "alter system archive log current";

RMAN> backup device type disk format '/home/oracle/backup/%U' as compressed backupset archivelog from sequence 57;

Close test database and flash it back to the restore point.

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to restore point PROD_POINT;
SQL> drop restore point PROD_POINT;
SQL> alter database flashback off;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> startup mount force

Perform any cleanups, like added datafiles while the test database was open.

Register backup and then apply the changes.

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/13kl0v38_1_1
File Name: /home/oracle/backup/12kl0uuv_1_1
File Name: /home/oracle/backup/14kl0v87_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/13kl0v38_1_1
File Name: /home/oracle/backup/12kl0uuv_1_1
File Name: /home/oracle/backup/14kl0v87_1_1

RMAN> recover database;

Starting recover at 25-JUL-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
...

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
...
unable to find archive log
archive log thread=1 sequence=59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2009 12:10:43
RMAN-06054: media recovery requesting unknown log: thread 1 seq 59 lowscn 391208

At the end, recover database will fail, because it cant find the current log, but it doesn’t matter here. Looking at the datafiles, RMAN has automatically created all newly added datafiles. That’s nice and easy.

Now, I’ll check that standby database is working, create a new restore point and open the database.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process, status, sequence# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG         59

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point prod_point guarantee flashback database;

Restore point created.

SQL> alter database activate standby database;

Database altered.

SQL> startup mount force
ORACLE instance started.
...
Database mounted.
SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open;

Database altered.

That’s it. Pretty easy actually 🙂

So if you think this method can be useful to you, then try it out. I needed to work this out, because test database is on another continent from primary and physical standbys and the network link is pretty slow. Test database is open usually about a month so using incremental backups for refreshing is pretty much the only thing reasonable.

Actually I use this method with backups taken from physical standby database, so in that case it’s not necessary to take the archivelogs also, just shut down media recovery process before starting the backup.