Revert the entire database to a previously saved state - Oracle 11g
Error recovery, historical reporting, trend analysis, data forensics and
fraud detection are just some of the business problems that can be
solved by using the Flashback Data Archive feature in Oracle 11g
database. The Flashback option can be enabled for the entire database or
for a selected set of tables. It can be enabled in the database with no
application changes.
In some cases especially with large data sets, restoring the database from a backup becomes one of the time consuming tasks. In such situations, using the database flashback to go back to a previously saved restore point saves quite a bit of time. This article demonstrates how to enable database flashback and to go back to a specified restore point. Check Oracle Total Recall with Oracle Database 11g Release 2 white paper for more information on Flashback Data Archive (FDA).
Steps to perform
Example
The following example uses guaranteed restore point to flashback the database in a two-node RAC environment. Most of the example is self-explanatory.
Note:
It is not mandatory to enable logging for flashback database in order to create and use restore points. The requirement in such a case is to put the database in ARCHIVELOG mode and creating the first guaranteed restore point when the database is in mounted state.
In some cases especially with large data sets, restoring the database from a backup becomes one of the time consuming tasks. In such situations, using the database flashback to go back to a previously saved restore point saves quite a bit of time. This article demonstrates how to enable database flashback and to go back to a specified restore point. Check Oracle Total Recall with Oracle Database 11g Release 2 white paper for more information on Flashback Data Archive (FDA).
Steps to perform
- Configure the following initialization parameters:
db_recovery_file_dest&db_recovery_file_dest_size - Enable Archive Log mode
- Enable database Flashback option
- Create a restore point. Decide whether to create a normal or a guaranteed restore point
-------------------------------------------------------------------------------------------------------- - Finally flashback database to the created restore point when required
Example
The following example uses guaranteed restore point to flashback the database in a two-node RAC environment. Most of the example is self-explanatory.
% srvctl status database -d DEMO
Instance DEMO1 is running on node racnode01
Instance DEMO2 is running on node racnode02
/* stop all the database instances except one (anyone) in RAC config */
% srvctl stop instance -d DEMO -i DEMO2
% export ORACLE_SID=DEMO1
/* put one of the instances in non-cluster mode */
% sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile;
% srvctl stop instance -d DEMO -i DEMO1
% sqlplus / as sysdba
SQL> startup mount
/* enable archive log mode */
SQL> alter database archivelog;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 512G
/* enable flashback option */
SQL> alter database flashback on;
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
/* put the instance back in cluster mode and restart the database */
SQL> alter system set cluster_database=true scope=spfile;
SQL> alter database open;
% srvctl stop instance -d DEMO -i DEMO1
% srvctl start database -d DEMO
/* create a guaranteed restore point */
% sqlplus / as sysdba
SQL> create restore point demo_clean_before_test guarantee flashback database;
Restore point created.
SQL> column NAME format A25
SQL> column TIME format A40
SQL> set lines 120
SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
2 from V$RESTORE_POINT where GUARANTEE_FLASHBACK_DATABASE='YES';
NAME SCN TIME GUA STORAGE_SIZE
------------------------- ---------- -------------------------------- --- ------------
DEMO_CLEAN_BEFORE_TEST 17460960 21-AUG-11 01.01.20.000 AM YES 67125248
/* flashback database to the saved restore point */
% srvctl stop database -d DEMO
% export ORACLE_SID=DEMO1
% rman TARGET /
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'DEMO_CLEAN_BEFORE_TEST';
Starting flashback at 21-AUG-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:25
Finished flashback at 21-AUG-11
RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened
RMAN> SHUTDOWN IMMEDIATE;
% srvctl start database -d DEMO
/* ============================================================================== */
/* alternatively run the following RMAN script as shown below */
% cat restore.rman
RUN {
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT 'DEMO_CLEAN_BEFORE_TEST';
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE;
}
EXIT
% rman TARGET / cmdfile=restore.rman
Note:
It is not mandatory to enable logging for flashback database in order to create and use restore points. The requirement in such a case is to put the database in ARCHIVELOG mode and creating the first guaranteed restore point when the database is in mounted state.
No comments
Comments with links will not be published.