![Cover image for Standby Automatic Flashback [19c feature]](https://media.dev.to/cdn-cgi/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fs5opcvt2hpg8uoj25sau.jpg)
One of the new Database 19c Features is the possibility of automatically Flashback the standby database if the Primary has been Flashback.
There are couple of requisites though, the Standby has to have Flashback enabled, and in order for the Automatic flashback to start, the standby has to be mounted
Lets do a quick test for our 19c Standby (st19) doing the Flashback in Primary (db19):
DB_UNIQUE INSTANCE_NAME DATABASE_ROLE OPEN_MODE STATUS
--------- ---------------- ---------------- -------------------- ------------
st19 st191 PHYSICAL STANDBY READ ONLY WITH APPLY OPEN
st19 st192 PHYSICAL STANDBY READ ONLY WITH APPLY OPEN
STANDBY - SQL>
DB_UNIQUE INSTANCE_NAME DATABASE_ROLE OPEN_MODE STATUS
--------- ---------------- ---------------- -------------------- ------------
db19 db191 PRIMARY READ WRITE OPEN
db19 db192 PRIMARY READ WRITE OPEN
PRIMARY - SQL>
Lets create a Guarantee Restore Point (GRP) in our Primary Database
PRIMARY - SQL> create restore point DB19 guarantee flashback database;
Restore point created.
PRIMARY - SQL>
Now we can check the Standby and confirm that the GRP has been replicated:
STANDBY - SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, NAME, REPLICATED from v$restore_point;
SCN GUA NAME REPLICATED
---------- --- -------------------- ----------
4184494 NO DB19_PRIMARY YES
STANDBY - SQL>
Lets do flashback of the system after creating a simple table as a test:
PRIMARY - SQL>create table P42 as select distinct(table_name) from dba_tables;
Table created.
PRIMARY - SQL>select count(*) from P42;
COUNT(*)
----------
2172
PRIMARY - SQL>
STANDBY - SQL>select count(*) from P42;
COUNT(*)
----------
2172
STANDBY - SQL>
[oracle@rac1-node1 ~]$ srvctl stop database -d db19
[oracle@rac1-node1 ~]$ srvctl start instance -i db191 -d db19 -o mount
[oracle@rac1-node1 ~]$ sqlplus / as sysdba
PRIMARY - SQL>flashback database to restore point DB19;
Flashback complete.
PRIMARY - SQL>alter database open resetlogs;
Database altered.
PRIMARY - SQL> select count(*) from P42;
select count(*) from P42
*
ERROR at line 1:
ORA-00942: table or view does not exist
PRIMARY - SQL>
Something to be aware of, if the Standby is OPEN, the standby will acknowledge the primary Flashback operation, but won't do more than that, and we can see how the P42 table still exist:
2020-12-17T11:33:05.516913+00:00
rfs (PID:29075): New archival redo branch: 1059391930 current: 1022248506
2020-12-17T11:33:05.624007+00:00
rfs (PID:29077): New archival redo branch: 1059391930 current: 1022248506
rfs (PID:29077): Primary database is in MAXIMUM PERFORMANCE mode
2020-12-17T11:33:06.017191+00:00
rfs (PID:29075): Selected LNO:113 for T-1.S-1 dbid 753464436 branch 1059391930
2020-12-17T11:33:06.308893+00:00
rfs (PID:29077): Selected LNO:114 for T-1.S-2 dbid 753464436 branch 1059391930
2020-12-17T11:33:06.603423+00:00
rfs (PID:29075): A new recovery destination branch has been registered
rfs (PID:29075): Standby in the future of new recovery destination branch(resetlogs_id) 1059391930
rfs (PID:29075): Incomplete Recovery SCN:0x00000000003f6e8d
rfs (PID:29075): Resetlogs SCN:0x00000000003f6a37
rfs (PID:29075): SBPS:0x00000000003b7ed8
rfs (PID:29075): New Archival REDO Branch(resetlogs_id): 1059391930 Prior: 1022248506
rfs (PID:29075): Archival Activation ID: 0x2f200010 Current: 0x2efb8e04
rfs (PID:29075): Effect of primary database OPEN RESETLOGS
STANDBY - SQL> select inst_id, process, status MRP_stat,
thread#, sequence#, block#, BLOCKS "Total Blocks"
from gv$managed_standby
where process like 'MRP%' or process like 'RFS%' and status != 'IDLE'
order by inst_id,process,thread# ;
no rows selected
STANDBY - SQL>select count(*) from P42;
COUNT(*)
----------
2172
STANDBY - SQL>
Now, in order to have Standby flashback, we just need to restart it as mounted and once the Recovery process tries to start, the system will automatically flashback to same point the primary did, doing an automatic flashback of the database
[oracle@rac2-node1 ~]$ srvctl stop database -d st19
[oracle@rac2-node1 ~]$ srvctl start database -d st19 -o mount
2020-12-17T13:31:43.570568+00:00
Recovery Slave PR00 previously exited with exception 19909
2020-12-17T13:31:43.619669+00:00
Errors in file /u01/app/oracle/diag/rdbms/st19/st191/trace/st191_mrp0_17530.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/ST19/DATAFILE/system.280.1059395945'
2020-12-17T13:31:53.934900+00:00
Decreasing number of high priority LMS from 2 to 0
2020-12-17T13:32:03.635729+00:00
MRP0 (PID:17530): Recovery coordinator performing automatic flashback of database to SCN:0x00000000003fd9ae (4184494) <<<<<<<<
Flashback Restore Start
2020-12-17T13:32:03.911538+00:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
2020-12-17T13:32:06.719660+00:00
Flashback Restore Complete
Flashback Media Recovery Start
2020-12-17T13:32:06.806321+00:00
Setting recovery target incarnation to 3
2020-12-17T13:32:06.839646+00:00
Started logmerger process
And voila!, Flashback completed :)
Top comments (0)