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].
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.
Enable Flashback
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
Enable flashback using:
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
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:
SQL> show parameter db_flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
The database must be in ARCHIVELOG mode.
Create Restore Point
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.
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!
Flashback the Database
To flashback, the database has to be restarted in mount state. Here the example shows a RAC database shutdown and startup.
$ 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>
Database flashback is done!
You may now start the other instances in the cluster, if applicable.
$ srvctl start instance -d mydb -n mynode2
Flashback using time, instead of restore point.
Sometimes, it is required to flashback a database without a restore point - to a time. It can be done using the syntax:
If you are restoring to a time, instead of restore point,
use the below syntax.
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>