Flashback your Standby DB

Env: 11.2.0.3 on Linux 5.8

In one of my database env, we have a multitude of application schemas. Very rarely someone would either purge their local data or drop their table by mistake and get back to us to restore it. It is not possible to restore the complete backup as it would affect other schemas too.

Below is how we progress in such issue.

1. PRIMARY : User drops the table in Primary
2. STANDBY : Stop the MRP in Standby
3. STANDBY : We create a restore point on the Stanaby DB
4. STANDBY : Flashback standby db to desired timestamp
5. STANDBY : Export the particular schema data
6. PRIMARY : Import the data into Primary
7. STANDBY : Flashback database to restore point created in step-2
8. STANDBY : Start the MRP  
9. STANDBY : Drop the restore point

To have the above working we need to be using the flashback recovery area and flashback enabled on the standby(not necessary on Primary)
We must be having the following parameters set in both PRIMARY and STANDBY  database instances.

DB_RECOVERY_FILE_DEST_SIZE
DB_RECOVERY_FILE_DEST
DB_FLASHBACK_RETENTION_TARGET

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_recovery_file_dest='/archive/jbscrowd' SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH SID='*';


Steps:
+++++++++++++++
1. ENABLE FLASHBACK DATABASE ON STANDBY :
STANDBY: alter database flashback on;

2. CHECK THE EARLIEST POINT YOU CAN FLASHBACK THE STANDBY:

SELECT TO_CHAR(OLDEST_FLASHBACK_TIME, 'DD-MM-YYYY HH24:MI:SS') OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG; 2 3
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME
-------------------- ---------------------------------------------------------
115847564 30-09-2013 01:27:47

3. RECOVER MANAGED STANDBY DATABASE CANCEL;
4. CREATE RESTORE POINT BEFORE_FLASHBACK GUARANTEE FLASHBACK DATABASE; 
5. FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('30-09-2013 01:30:00', 'DD-MM-YYYY HH24:MI:SS'); 
6. ALTER DATABASE OPEN READ ONLY; 
7. EXPORT THE TABLE OR WHATEVER IS DROPPED/DELETED 
8. SHUTDOWN IMMEDIATE; 
9. STARTUP MOUNT; 
10. FLASHBACK DATABASE TO RESTORE POINT BEFORE_FLASHBACK; 
11. DROP RESTORE POINT BEFORE_FLASHBACK; 
12. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 
13. CHECK SCN SYNC BETWEEN PRIMARY AND STANDBY: 

select inst_id, process, status, count(*) , max(delay_mins) from gv$managed_standby group by inst_id, process, status order by inst_id, process, status; 

set pages 999
set linesize 100
column max_redo_seq format a25
column max_recovery_seq format a25
column gap_seq format a25

SELECT thread,
       max(redo) || ' : ' || max(next_scn_redo) as max_redo_seq,
       max(recovery) || ' : ' ||   max(next_scn_recovery) as max_recovery_seq,
       (max(redo) - max(recovery)) || ' : ' || (max(next_scn_redo) - max(next_scn_recovery)) as gap_seq FROM ( select distinct a.thread# as thread,
       (a.sequence#) as redo,
       (a.next_change#) as next_scn_redo,
       0 as next_scn_recovery,
       0 as recovery
from v$archived_log a,
     (select thread# , max(next_change#) as next_chg from  v$archived_log group by thread#) b where a.thread# = b.thread#
and   a.next_change# = b.next_chg
union all
select distinct a.thread# as thread,
       0 as redo,
       0  as next_scn_redo,
       (a.next_change#) as next_scn_recovery,
       (a.sequence#) as recovery
from v$log_history a,
    (select thread# , max(next_change#) as next_chg from  v$log_history group by thread#) b where a.thread# = b.thread#
and   a.next_change# = b.next_chg
)
group by thread
order by thread

Referance: http://www.oracle.com/us/solutions/sap/wp-ora4sap-flashback11g-1-303814.pdf

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s