In very rare case, We may end up in situation like some logical mistake has been happened to our production database and as a last resort to recover the data we may need to do a flashback operation at primary database. When we do a flashback operation on primary database subsequently redo apply will get stopped out on all standby databases.In this demonstration I will show how to bring our standby back in sync with our production database.
Here SRCDB is my primary database and TRGDB is my standby database. Flashback should be enabled on both primary and standby database.
C:\Users\Mahi>set ORACLE_SID=SRCDB
C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:38:05 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>select name ,open_mode, database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
SRCDB READ WRITE PRIMARY YES
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
16-FEB-18 07.38.37.868000 PM +05:30
SQL> select count(*) from hr.JOBS;
COUNT(*)
----------
19
C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:41:04 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name ,open_mode, database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
SRCDB READ ONLY WITH APPLY PHYSICAL STANDBY YES
SQL> select count(*) from hr.JOBS;
COUNT(*)
----------
19
SQL>
Let's do some logical mistake-
C:\Users\Mahi>sqlplus sys@srcdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:45:20 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> drop user hr cascade ;
User dropped.
SQL>
As I have configured "Real Time Apply" the logical mistake that I have done on my primary database should have applied to my standby database as well.I will verify that on my standby.
C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:46:18 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select count(*) from hr.JOBS;
select count(*) from hr.JOBS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select process,status,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CLOSING 36 4096 1522
ARCH CONNECTED 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
MRP0 APPLYING_LOG 37 3982 102400
RFS IDLE 37 3982 1
9 rows selected.
SQL>
Now shutdown the primary database and do the flashback operation to recover the dropped schema.
C:\Users\Mahi>sqlplus sys@srcdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:31:23 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
C:\Users\Mahi>set ORACLE_SID=SRCDB
C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:38:20 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2404704 bytes
Variable Size 440405664 bytes
Database Buffers 83886080 bytes
Redo Buffers 7766016 bytes
Database mounted.
SQL>
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('16-02-18 19:38:37', 'DD-MM-YY HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> select count(*) from hr.JOBS;
COUNT(*)
----------
19
SQL>
Once you do the flashback operation on the primary database, subsequently all standby databases will no longer able to apply the redo because my primary database has gone through a new incarnation.I Verified MRP process is no longer running in standby.
C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:48:51 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select process,status,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 1 1
ARCH CLOSING 37 10240 1726
RFS IDLE 2 1218 1
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
8 rows selected.
SQL>
Output from standby alertlog file-
RFS[6]: New Archival REDO Branch(resetlogs_id): 968273102 Prior: 967810818
RFS[6]: Archival Activation ID: 0x23e32516 Current: 0x23dcca7c
RFS[6]: Effect of primary database OPEN RESETLOGS
RFS[6]: Managed Standby Recovery process is active
2018-02-16 20:46:25.742000 +05:30
Setting recovery target incarnation to 3
Archived Log entry 27 added for thread 1 sequence 37 ID 0x23dcca7c dest 1:
2018-02-16 20:46:27.820000 +05:30
Archived Log entry 28 added for thread 1 sequence 1 ID 0x23e32516 dest 1:
Media Recovery Waiting for thread 1 sequence 38
MRP0: Incarnation has changed! Retry recovery...
Errors in file E:\APP\MAHI\diag\rdbms\trgdb\trgdb\trace\trgdb_pr00_7600.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2018-02-16 20:46:30.078000 +05:30
Started logmerger process
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 2718127) is orphaned on incarnation#=2
MRP0: Detected orphaned datafiles!
2018-02-16 20:46:31.113000 +05:30
Recovery will possibly be retried after flashback...
Errors in file E:\APP\MAHI\diag\rdbms\trgdb\trgdb\trace\trgdb_pr00_7416.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'E:\ORADATA\TRGDB\DATAFILE\O1_MF_SYSTEM_F80M3JN4_.DBF'
Managed Standby Recovery not using Real Time Apply
2018-02-16 20:46:32.375000 +05:30
Recovery Slave PR00 previously exited with exception 19909
Now flashback the standby database to the present time of the primary database.
C:\Users\Mahi>sqlplus sys@srcdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:59:52 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
2709581
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 21:00:22 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> flashback database to scn 2709581;
Flashback complete.
SQL>
Note:- It is better to use a SCN substracted by 1 or 2 from the resetlogs_change# that we noted from the primary database.So we can
make sure that we get the Standby close before the present time of the Primary.
Output from standby alertlog file -
flashback database to scn 2709581
Stopping Emon pool
2018-02-16 21:01:16.646000 +05:30
Waiting for shared server 'S000' to die
All dispatchers and shared servers shutdown
2018-02-16 21:01:17.740000 +05:30
CLOSE: killing server sessions.
2018-02-16 21:01:34.966000 +05:30
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
2018-02-16 21:01:36.006000 +05:30
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
ARC1: Waiting for instance close to complete
SMON: disabling cache recovery
2018-02-16 21:01:39.226000 +05:30
ARC1: Wait for instance close completed
2018-02-16 21:01:41.779000 +05:30
Flashback Restore Start
2018-02-16 21:01:46.660000 +05:30
Flashback Restore Complete
Flashback Media Recovery Start
2018-02-16 21:01:47.718000 +05:30
Started logmerger process
2018-02-16 21:01:48.983000 +05:30
Parallel Media Recovery started with 4 slaves
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 2709580
2018-02-16 21:01:50.465000 +05:30
Flashback Media Recovery Log E:\ARCHIVE\TRGDB\TRGDB_1_36_967810818.ARC
2018-02-16 21:01:52.447000 +05:30
Flashback Media Recovery Log E:\ARCHIVE\TRGDB\TRGDB_1_37_967810818.ARC
2018-02-16 21:01:54.697000 +05:30
Flashback Media Recovery Log E:\ARCHIVE\TRGDB\TRGDB_1_1_968273102.ARC
Incomplete Recovery applied until change 2709582 time 02/16/2018 20:45:28
Flashback Media Recovery Complete
2018-02-16 21:01:58.676000 +05:30
Completed: flashback database to scn 2709581
Now we need to start managed recovery process-
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CLOSING 2 6144 47
ARCH CLOSING 37 10240 1726
RFS IDLE 0 0 0
RFS IDLE 0 0 0
MRP0 WAIT_FOR_LOG 3 0 0
RFS IDLE 0 0 0
8 rows selected.
SQL> select count(*) from hr.JOBS;
select count(*) from hr.JOBS
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> recover managed standby database cancel ;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 3 1 139
ARCH CONNECTED 0 0 0
ARCH CLOSING 4 1 5
ARCH CLOSING 37 10240 1726
RFS IDLE 0 0 0
RFS IDLE 0 0 0
MRP0 APPLYING_LOG 5 1379 102400
RFS IDLE 5 1379 1
RFS IDLE 0 0 0
9 rows selected.
SQL> select count(*) from hr.JOBS;
COUNT(*)
----------
19
SQL>
My tables is back in stanby and is in sync with primary database too.
Ref:- https://uhesse.com/2010/08/06/using-flashback-in-a-data-guard-environment/
No comments:
Post a Comment