{"id":2044,"date":"2012-12-08T06:49:00","date_gmt":"2012-12-08T06:49:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=6"},"modified":"2026-02-07T14:45:24","modified_gmt":"2026-02-07T20:45:24","slug":"using-flashback-during-repeated-load-tests","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2012\/2044\/","title":{"rendered":"Using Flashback during repeated load tests"},"content":{"rendered":"
We are in the middle of migrating several databases to a new data center. One of the requirement is to do rigorous load and stress tests on the new servers to make sure they can handle the load and the configuration is correct. For this purpose, there are several load\/stress test scripts are written and tested using OATS [Oracle Application Testing Suite]. <\/p>\n

Since the load\/stress tests are done repeatedly, the test data [database] need to be restored to its pre-test state before doing the next test. As these\u00a0database\u00a0sizes are large, it will take a while to do the restore. But, the flashback feature of the database comes handy.<\/p>\n

Enable Flashback<\/h2>\n

To enable flashback, from 11gR2\u00a0onward\u00a0there is no need to restart the database in mount mode. Flashback can be enabled and disabled while the database is online.<\/p>\n

Before you enable flashback, set the following parameters.<\/p>\n

SQL> show parameter recovery\n\nNAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 TYPE \u00a0 \u00a0 \u00a0 \u00a0VALUE\n------------------------------------ ----------- ------------------------------\ndb_recovery_file_dest \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0string \u00a0 \u00a0 \u00a0+ORAARCH\ndb_recovery_file_dest_size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 big integer 50000M<\/pre>\n
 <\/p>\n
Enable flashback using:<\/div>\n
<\/div>\n
\n
SQL> ALTER DATABASE FLASHBACK ON;<\/pre>\n
You can verify if flashback is enabled by querying v$database.<\/p>\n<\/div>\n
\n
\n
SQL> select flashback_on from v$database;\nFLASHBACK_ON\n------------------\nYES<\/pre>\n
 <\/p>\n<\/div>\n<\/div>\n
Flashback logs are written to the directory speficied by the DB_RECOVERY_FILE_DEST parameter. The retention of these files depends on the setting of parameter:<\/div>\n
<\/div>\n
\n
SQL> show parameter db_flashback\n\nNAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 TYPE \u00a0 \u00a0 \u00a0 \u00a0VALUE\n------------------------------------ ----------- ------------------------------\ndb_flashback_retention_target \u00a0 \u00a0 \u00a0 \u00a0integer \u00a0 \u00a0 1440\nSQL><\/pre>\n
 <\/p>\n<\/div>\n
<\/div>\n
The database must be in ARCHIVELOG mode.<\/div>\n
<\/div>\n
Create Restore Point<\/h2>\n
During load\/stress tests we like to make sure the database is flashbackable to pre-test state. We ensure this by creating a guaranteed restore point.<\/div>\n
<\/div>\n
\n
\n
SQL> create restore point BEFORE_LOAD_TEST_G GUARANTEE FLASHBACK DATABASE;\n\nRestore point created.\n\nSQL><\/pre>\n
Restore points created in the database can be viewed from v$restore_point view.<\/p>\n<\/div>\n<\/div>\n
<\/div>\n
\n
\n
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,\n\u00a0\u00a0\u00a0\u00a0\u00a0 GUARANTEE_FLASHBACK_DATABASE\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 V$RESTORE_POINT ;\n\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SCN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TIME DATABASE_INCARNATION# GUA\n---------------------- ---------- ------------------------------ --------------------- ---\nBEFORE_LOAD_TEST_G\u00a0 \u00a0 \u00a0 \u00a051274755 26-NOV-12 02.52.04.000000000 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 YES<\/pre>\n
You will have to keep track of the space usage on the flash recovery area to make sure you have enough free space.<\/p>\n<\/div>\n<\/div>\n
<\/div>\n
\n
\n
SQL>\u00a0 select * from V$FLASH_RECOVERY_AREA_USAGE;\n\nFILE_TYPE\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES\n-------------------- ------------------ ------------------------- ---------------\nCONTROL FILE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nREDO LOG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nARCHIVED LOG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.47\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 218\nBACKUP PIECE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nIMAGE COPY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nFLASHBACK LOG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.24\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15\nFOREIGN ARCHIVED LOG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\n\n7 rows selected.\n\nSQL><\/pre>\n
After the test is done, it is time to flashback!<\/p>\n<\/div>\n<\/div>\n
<\/div>\n
Flashback the Database<\/h2>\n
To flashback, the database has to be restarted in mount state. Here the example shows a RAC database shutdown and startup.<\/div>\n
<\/div>\n
\n
$ srvctl stop database -d mydb\n$ srvctl start instance -d mydb -n mynode1 -o mount<\/pre>\n
Use RMAN to do the flashback, the advantage is that if any archivelog that is not present in the disk is required for flashback recovery, RMAN can automatically restore the archivelogs from the backup media.<\/p>\n<\/div>\n
<\/div>\n
\n
$ rman target \/\n\nRMAN has commands to list the restore points in DB.\n\nRMAN> list restore point BEFORE_LOAD_TEST_G;\nusing target database control file instead of recovery catalog\n\nSCN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RSP Time\u00a0 Type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Time\u00a0\u00a0\u00a0\u00a0\u00a0 Name\n---------------- --------- ---------- --------- -------------------\n51274755\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GUARANTEED 26-NOV-12 BEFORE_LOAD_TEST_G<\/pre>\n
Now ready to flashback the database…<\/p>\n<\/div>\n
<\/div>\n
\n
\n
RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_LOAD_TEST_G;\n\nStarting flashback at 27-NOV-12\nusing target database control file instead of recovery catalog\nallocated channel: ORA_SBT_TAPE_1\nchannel ORA_SBT_TAPE_1: SID=997 instance=prod2 device type=SBT_TAPE\nchannel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012091607)\nallocated channel: ORA_SBT_TAPE_2\nchannel ORA_SBT_TAPE_2: SID=1139 instance=prod2 device type=SBT_TAPE\nchannel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.5 (2012091607)\nallocated channel: ORA_SBT_TAPE_3\nchannel ORA_SBT_TAPE_3: SID=1281 instance=prod2 device type=SBT_TAPE\nchannel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 7.5 (2012091607)\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=1424 instance=prod2 device type=DISK\nstarting media recovery\narchived log for thread 1 with sequence 169 is already on disk as file +ORAARCH\/prod\/archivelog\/2012_11_26\/thread_1_seq_169.482.800375559\narchived log for thread 2 with sequence 500 is already on disk as file +ORAARCH\/prod\/archivelog\/2012_11_26\/thread_2_seq_500.480.800373637\nmedia recovery complete, elapsed time: 00:00:04\nFinished flashback at 27-NOV-12\nRMAN>\n\nRMAN> alter database open resetlogs;\ndatabase opened\nRMAN><\/pre>\n
 <\/p>\n<\/div>\n
<\/p>\n<\/div>\n
Database flashback is done!<\/div>\n
<\/div>\n
You may now start the other instances in the cluster, if applicable.<\/div>\n
<\/div>\n
\n
$ srvctl start instance -d mydb -n mynode2<\/pre>\n
 <\/p>\n
Flashback using time,\u00a0instead\u00a0of restore point.<\/span><\/p>\n<\/div>\n
Sometimes, it is required to flashback a database without a restore point – to a time. It can be done using the syntax:<\/div>\n
<\/div>\n
If you are restoring to a time, instead of restore point, use the below syntax.<\/div>\n
\n
<\/div>\n
\n
RMAN> flashback database to time \"to_timestamp('2012-11-29 12:00:00', 'YYYY-MM-DD HH24:MI:SS')\";\n\nStarting flashback at 30-NOV-12\nusing channel ORA_SBT_TAPE_1\nusing channel ORA_SBT_TAPE_2\nusing channel ORA_SBT_TAPE_3\nusing channel ORA_DISK_1\nstarting media recovery\nchannel ORA_SBT_TAPE_1: starting archived log restore to default destination\nchannel ORA_SBT_TAPE_1: restoring archived log\narchived log thread=2 sequence=56\nchannel ORA_SBT_TAPE_1: restoring archived log\narchived log thread=1 sequence=36\nchannel ORA_SBT_TAPE_1: reading from backup piece PROD_1211301159_AL0_501_1_1_800714350\nchannel ORA_SBT_TAPE_1: piece handle=PROD_1211301159_AL0_501_1_1_800714350 tag=DS1PROD2_1211301159_AL0\nchannel ORA_SBT_TAPE_1: restored backup piece 1\nchannel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:45\nchannel ORA_SBT_TAPE_1: deleting archived log(s)\narchived log file name=+ORAARCH\/prod\/archivelog\/2012_11_30\/thread_1_seq_36.794.800732539 RECID=652 STAMP=800736143\nchannel ORA_SBT_TAPE_1: deleting archived log(s)\narchived log file name=+ORAARCH\/prod\/archivelog\/2012_11_30\/thread_2_seq_56.792.800732539 RECID=651 STAMP=800736142\nmedia recovery complete, elapsed time: 00:00:04\nFinished flashback at 30-NOV-12\nRMAN>\n\nRMAN> alter database open resetlogs;\ndatabase opened\nRMAN><\/pre>\n
 <\/p>\n<\/div>\n<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
We are in the middle of migrating several databases to a new data center. One of the requirement is to do rigorous load and stress tests on the new servers to make sure they can handle the load and the configuration is correct. For this purpose, there are several load\/stress test scripts are written and … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[134],"class_list":["post-2044","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-oracle-11g"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2044","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=2044"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2044\/revisions"}],"predecessor-version":[{"id":2953,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2044\/revisions\/2953"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=2044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=2044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=2044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}