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.
0 comments:
Post a Comment
Comments with links will not be published.