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.
\nIn this situation Oracle has three great technologies that can help: physical standby database, flashback database and RMAN.<\/p>\n
Here I’m using Oracle Database 10.2.0.4 EE.<\/p>\n
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.<\/p>\n
Create backup and standby controlfile. Give backupsets some tag, that can later be used for cleaning up the backups from primary database.<\/p>\n
\nRMAN> backup device type disk format '\/home\/oracle\/backup\/%U' as compressed backupset\n tag 'testdb_init' database INCLUDE CURRENT CONTROLFILE FOR STANDBY\n plus archivelog;\n\nStarting backup at 24-JUL-09\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: sid=159 devtype=DISK\nchannel ORA_DISK_1: starting compressed full datafile backupset\n... and so on\nFinished backup at 24-JUL-09\n<\/pre>\nI also need to take spfile and password file.<\/p>\n
\n$ cp $ORACLE_HOME\/dbs\/spfiletest1.ora \/home\/oracle\/backup\/\n$ cp $ORACLE_HOME\/dbs\/orapwtest1 \/home\/oracle\/backup\/\n<\/pre>\nNow, copy everything over to the test database. I’m leaving all file locations exactly the same as in primary database.<\/p>\n
\n$ scp \/home\/oracle\/backup\/* oracle@testdb:\/home\/oracle\/backup\/\n<\/pre>\nOn test server:<\/p>\n
\n$ cd backup\/\n$ cp spfiletest1.ora $ORACLE_HOME\/dbs\/\n$ cp orapwtest1 $ORACLE_HOME\/dbs\/\n<\/pre>\nStart up the test database instance and change service name to be different from the one used in production.<\/p>\n
\nSQL> startup nomount\nORACLE instance started.\n\nTotal System Global Area 608174080 bytes\nFixed Size 1268920 bytes\nVariable Size 167773000 bytes\nDatabase Buffers 436207616 bytes\nRedo Buffers 2924544 bytes\nSQL> show parameter service\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nservice_names string test1\n\nSQL> alter system set service_names='test_test1' scope=both;\n\nSystem altered.\n<\/pre>\nRestore controlfile and restore the datafiles.<\/p>\n
\nRMAN> restore controlfile from '\/home\/oracle\/backup\/0tkkuvob_1_1';\n\nStarting restore at 24-JUL-09\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: sid=156 devtype=DISK\n\nchannel ORA_DISK_1: restoring control file\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:08\noutput filename=\/u01\/app\/oracle\/oradata\/TEST1\/controlfile\/o1_mf_56lyof8g_.ctl\noutput filename=\/u01\/app\/oracle\/flash_recovery_area\/TEST1\/controlfile\/o1_mf_56lyofm4_.ctl\nFinished restore at 24-JUL-09\n\nRMAN> alter database mount;\nRMAN> sql \"alter database flashback off\";\n\nRMAN> catalog start with '\/home\/oracle\/backup';\n\nsearching for all files that match the pattern \/home\/oracle\/backup\n\nList of Files Unknown to the Database\n=====================================\nFile Name: \/home\/oracle\/backup\/0ukkuvok_1_1\nFile Name: \/home\/oracle\/backup\/0tkkuvob_1_1\n\nDo you really want to catalog the above files (enter YES or NO)? yes\ncataloging files...\ncataloging done\n\nList of Cataloged Files\n=======================\nFile Name: \/home\/oracle\/backup\/0ukkuvok_1_1\nFile Name: \/home\/oracle\/backup\/0tkkuvob_1_1\n\nRMAN> restore database;\n\nStarting restore at 24-JUL-09\n...\nFinished restore at 24-JUL-09\n\nRMAN> recover database;\n\nStarting recover at 24-JUL-09\nusing channel ORA_DISK_1\n\nstarting media recovery\nunable to find archive log\narchive log thread=1 sequence=51\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of recover command at 07\/24\/2009 17:09:50\nRMAN-06054: media recovery requesting unknown log: thread 1 seq 51 lowscn 360057\n<\/pre>\nRecover database will fail at the end, but this is only because it didn’t find the current log. This doesn’t matter here.<\/p>\n
Test that media recovery process will start.<\/p>\n
\nSQL> alter database recover managed standby database disconnect from session;\n\nDatabase altered.\n\nSQL> select process, status, sequence# from v$managed_standby where process like 'MRP%';\n\nPROCESS STATUS SEQUENCE#\n--------- ------------ ----------\nMRP0 WAIT_FOR_LOG 51\n\nSQL> alter database recover managed standby database cancel;\n\nDatabase altered.\n<\/pre>\nIf MRP* is started and waiting for log (and no error in alert.log), then everything is good.<\/p>\n
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.
\nAfter flashback has been enabled, create a guaranteed restore point.<\/p>\n\nSQL> alter database flashback on;\n\nDatabase altered.\n\nSQL> create restore point prod_point guarantee flashback database;\n\nRestore point created.\n<\/pre>\nNow, just activate standby database and open it.<\/p>\n
\nSQL> alter database activate standby database;\nSQL> startup mount force\nSQL> alter database set standby database to maximize performance;\nSQL> alter database open;\n<\/pre>\nDelete the backups from both databases. Just for cleaning up.<\/p>\n
\nRMAN> delete backup tag 'testdb_init';\n<\/pre>\nSyncing with production<\/h3>\n
To make it more interesting, I’ll add some new datafiles to the primary database.<\/p>\n
\nSQL> alter tablespace sysaux add datafile size 10m autoextend on next 10m maxsize 1g;\n\nTablespace altered.\n\nSQL> create tablespace test5891 datafile size 10m autoextend on next 10m maxsize 1g;\n\nTablespace created.\n<\/pre>\nFirst, in test database find out the restore point SCN. This is the point from where to create the incremental backup.<\/p>\n
\nSQL> select to_char(scn) from v$restore_point where name = 'PROD_POINT';\n\nTO_CHAR(SCN)\n---------------\n390703\n<\/pre>\nFind out the current production database log sequence.<\/p>\n
\nSQL> select sequence# from v$log where status = 'CURRENT';\n\n SEQUENCE#\n----------\n 57\n\nSQL> alter system checkpoint;\n<\/pre>\nCreate incremental backup from production and transport it to the test site. Also include all archived logs that were generated during backup.<\/p>\n
\nRMAN> BACKUP INCREMENTAL FROM SCN=390703 device type disk format '\/home\/oracle\/backup\/%U'\n as compressed backupset database INCLUDE CURRENT CONTROLFILE FOR STANDBY;\n\nRMAN> sql \"alter system archive log current\";\n\nRMAN> backup device type disk format '\/home\/oracle\/backup\/%U' as compressed backupset archivelog from sequence 57;\n<\/pre>\nClose test database and flash it back to the restore point.<\/p>\n
\nSQL> shutdown immediate\nSQL> startup mount\nSQL> flashback database to restore point PROD_POINT;\nSQL> drop restore point PROD_POINT;\nSQL> alter database flashback off;\nSQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;\nSQL> startup mount force\n<\/pre>\nPerform any cleanups, like added datafiles while the test database was open.<\/p>\n
Register backup and then apply the changes.<\/p>\n
\nRMAN> catalog start with '\/home\/oracle\/backup';\n\nsearching for all files that match the pattern \/home\/oracle\/backup\n\nList of Files Unknown to the Database\n=====================================\nFile Name: \/home\/oracle\/backup\/13kl0v38_1_1\nFile Name: \/home\/oracle\/backup\/12kl0uuv_1_1\nFile Name: \/home\/oracle\/backup\/14kl0v87_1_1\n\nDo you really want to catalog the above files (enter YES or NO)? yes\ncataloging files...\ncataloging done\n\nList of Cataloged Files\n=======================\nFile Name: \/home\/oracle\/backup\/13kl0v38_1_1\nFile Name: \/home\/oracle\/backup\/12kl0uuv_1_1\nFile Name: \/home\/oracle\/backup\/14kl0v87_1_1\n\nRMAN> recover database;\n\nStarting recover at 25-JUL-09\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting incremental datafile backupset restore\n...\n\nstarting media recovery\n\nchannel ORA_DISK_1: starting archive log restore to default destination\n...\nunable to find archive log\narchive log thread=1 sequence=59\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of recover command at 07\/25\/2009 12:10:43\nRMAN-06054: media recovery requesting unknown log: thread 1 seq 59 lowscn 391208\n<\/pre>\nAt 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.<\/p>\n
Now, I’ll check that standby database is working, create a new restore point and open the database.<\/p>\n
\nSQL> alter database recover managed standby database disconnect from session;\n\nDatabase altered.\n\nSQL> select process, status, sequence# from v$managed_standby where process like 'MRP%';\n\nPROCESS STATUS SEQUENCE#\n--------- ------------ ----------\nMRP0 WAIT_FOR_LOG 59\n\nSQL> alter database recover managed standby database cancel;\n\nDatabase altered.\n\nSQL> alter database flashback on;\n\nDatabase altered.\n\nSQL> create restore point prod_point guarantee flashback database;\n\nRestore point created.\n\nSQL> alter database activate standby database;\n\nDatabase altered.\n\nSQL> startup mount force\nORACLE instance started.\n...\nDatabase mounted.\nSQL> alter database set standby database to maximize performance;\n\nDatabase altered.\n\nSQL> alter database open;\n\nDatabase altered.\n<\/pre>\nThat’s it. Pretty easy actually \ud83d\ude42<\/p>\n
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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4],"class_list":["post-62","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/62","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=62"}],"version-history":[{"count":0,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/62\/revisions"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}