Flashback feature of Oracle database has been of interest to me since its inception in 9i. Thank you OTech Magazine for publishing my article on Flashback in the winter edition.
Read the article here.
There are several excellent articles in this edition of the Magazine. More than 100 pages of tech content. Don’t miss!!
Merry Christmas!
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 database sizes are large, it will take a while to do the restore. But, the flashback feature of the database comes handy.
To enable flashback, from 11gR2 onward there is no need to restart the database in mount mode. Flashback can be enabled and disabled while the database is online.
Before you enable flashback, set the following parameters.
SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +ORAARCH db_recovery_file_dest_size big integer 50000M
SQL> ALTER DATABASE FLASHBACK ON;
You can verify if flashback is enabled by querying v$database.
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
SQL> show parameter db_flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL>
SQL> create restore point BEFORE_LOAD_TEST_G GUARANTEE FLASHBACK DATABASE; Restore point created. SQL>
Restore points created in the database can be viewed from v$restore_point view.
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT ; NAME SCN TIME DATABASE_INCARNATION# GUA ---------------------- ---------- ------------------------------ --------------------- --- BEFORE_LOAD_TEST_G 51274755 26-NOV-12 02.52.04.000000000 PM 2 YES
You will have to keep track of the space usage on the flash recovery area to make sure you have enough free space.
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 18.47 0 218 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 12.24 0 15 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected. SQL>
After the test is done, it is time to flashback!
$ srvctl stop database -d mydb $ srvctl start instance -d mydb -n mynode1 -o mount
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.
$ rman target / RMAN has commands to list the restore points in DB. RMAN> list restore point BEFORE_LOAD_TEST_G; using target database control file instead of recovery catalog SCN RSP Time Type Time Name ---------------- --------- ---------- --------- ------------------- 51274755 GUARANTEED 26-NOV-12 BEFORE_LOAD_TEST_G
Now ready to flashback the database…
RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_LOAD_TEST_G; Starting flashback at 27-NOV-12 using target database control file instead of recovery catalog allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=997 instance=prod2 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012091607) allocated channel: ORA_SBT_TAPE_2 channel ORA_SBT_TAPE_2: SID=1139 instance=prod2 device type=SBT_TAPE channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.5 (2012091607) allocated channel: ORA_SBT_TAPE_3 channel ORA_SBT_TAPE_3: SID=1281 instance=prod2 device type=SBT_TAPE channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 7.5 (2012091607) allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1424 instance=prod2 device type=DISK starting media recovery archived 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 archived 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 media recovery complete, elapsed time: 00:00:04 Finished flashback at 27-NOV-12 RMAN> RMAN> alter database open resetlogs; database opened RMAN>
$ srvctl start instance -d mydb -n mynode2
Flashback using time, instead of restore point.
RMAN> flashback database to time "to_timestamp('2012-11-29 12:00:00', 'YYYY-MM-DD HH24:MI:SS')";
Starting flashback at 30-NOV-12
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_DISK_1
starting media recovery
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=56
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_SBT_TAPE_1: reading from backup piece PROD_1211301159_AL0_501_1_1_800714350
channel ORA_SBT_TAPE_1: piece handle=PROD_1211301159_AL0_501_1_1_800714350 tag=DS1PROD2_1211301159_AL0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_SBT_TAPE_1: deleting archived log(s)
archived log file name=+ORAARCH/prod/archivelog/2012_11_30/thread_1_seq_36.794.800732539 RECID=652 STAMP=800736143
channel ORA_SBT_TAPE_1: deleting archived log(s)
archived log file name=+ORAARCH/prod/archivelog/2012_11_30/thread_2_seq_56.792.800732539 RECID=651 STAMP=800736142
media recovery complete, elapsed time: 00:00:04
Finished flashback at 30-NOV-12
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>