Friday, August 18, 2023

Automatic flashback in Standby Database following Primary Database flashback ( Oracle 19c)

When flashback or point-in-time recovery is performed on the primary database, a standby that is in mounted mode can automatically follow the same recovery procedure performed on the primary. This is the cool feature of 19c .

When flashback or point-in-time recovery is performed either on a primary database or a PDB in the primary database, the primary database or PDB is moved to a previous point in time and the primary is then opened with the RESETLOGS option. A new incarnation of the primary or the PDB in the primary is created. For the standby to automatically follow the primary, the MRP performs the following actions:

  • detects the new incarnation
  • flashes back the standby or the PDB on the standby to the same point in time as that of the primary or the PDB on the primary
  • restarts the standby recovery and moves the standby to the new branch of redo
  • The flashback operation will succeed only when the standby database has sufficient flashback data.

If you do not want the standby to automatically follow the primary, either keep the standby database in OPEN mode or stop the MRP process on the standby. 

I will illustrate this process in the upcoming test scenario.

 

##############################################################################################################
## Primary Database
##############################################################################################################
SQL> create table vishwa.test( name varchar2(100), num number) ;
Table created.
 
SQL> Begin
for x in 1..3
loop
for x in 1..100000
Loop
insert into vishwa.test values('sdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', x);
End Loop;
commit;
end loop;
END;
/
PL/SQL procedure successfully completed.
 
SQL> select count(1) from vihwa.test;
COUNT(1)
----------
300000
##############################################################################################################
## Standby Database
##############################################################################################################
SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
INST_ID NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- --------- -------------------- ---------------- -------------------- --------
1 TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
 
SQL> select count(1) from vishwa.test;
COUNT(1)
----------
300000

Having created the table, the next step is to establish a guaranteed restore point (GRP) within the primary database

SQL> create restore point TEST_GRP guarantee flashback database;
Restore point created.

Upon inspecting both my primary and standby databases, I observe the presence of the restore point in both instances. However, the distinction lies in the fact that in the standby database, the restore point is not a guaranteed one; rather, it is categorized as a replicated restore point, with the addition of the suffix "_PRIMARY" to the restore point's GRP name

##############################################################################################################
## Primary Database
##############################################################################################################
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN scn FOR 999999999999999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
SELECT
database_incarnation# as Incar,
scn,
name,
time,
storage_size,
guarantee_flashback_database
FROM
v$restore_point
ORDER BY 4;
INCAR SCN NAME TIME STORAGE_SIZE GUA
----- -------------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
2 2142920 TEST_GRP 25-JUL-23 10.50.59.000000000 AM 52428800 YES
 
 
##############################################################################################################
## Standby Database
##############################################################################################################
INCAR SCN NAME TIME STORAGE_SIZE GUA
----- -------------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
2 2142920 TEST_GRP_PRIMARY 25-JUL-23 10.50.59.000000000 AM 0 NO

In contrast to the primary database log, the creation of the restore point is not reflected in the standby database log. As illustrated below, I am performing a verification within the log to confirm whether the restore point was indeed established.

##############################################################################################################
## Primary Database log
##############################################################################################################
[oracle@primary trace]$ cat alert_TESTDB.log | grep TEST_GR
Created guaranteed restore point TEST_GRP
 
##############################################################################################################
## Standby Database log
##############################################################################################################
[oracle@standby trace]$ cat alert_TESTDB.log | grep TEST_GRP | wc -l
0

The next step is to drop the table as if it were the disaster that I want to flashback my database from.

##############################################################################################################
## Primary Database
##############################################################################################################
SQL> drop table vishwa.test;
Table dropped.
 
SQL> select count(1) from vishwa.test;
select count(1) from vishwa.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
 
##############################################################################################################
## Standby Database
##############################################################################################################
SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
INST_ID NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- --------- -------------------- ---------------- -------------------- --------
1 TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
 
SQL> select count(1) from vishwa.test;
select count(1) from vishwa.test
*
ERROR at line 1:
ORA-00942: table or view does not exist

Before I proceed with performing a flashback of the primary database, I will conduct a validation process to ensure that the Data Guard environment is functioning correctly

DGMGRL> validate database testdb_stdby;
Database Role: Physical standby database
Primary Database: testdb
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
...
 
DGMGRL> show database testdb_stdby;
Database - testdb_stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 8.00 KByte/s
Real Time Query: ON
Instance(s):
TESTDB
Database Status:
SUCCESS

After completing the validation process, I will proceed to perform a flashback operation on the primary database using the guaranteed restore point that was created earlier.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
 
SQL> flashback database to restore point TEST_GRP;
Flashback complete.
 
SQL> alter database open resetlogs;
Database altered.
##############################################################################################################
## From the Primary Database log
##############################################################################################################
2023-07-25T19:59:22.147780-06:00
Incomplete Recovery applied until change 2142921 time 07/25/2023 19:54:34
2023-07-25T19:59:22.154955-06:00
Flashback Media Recovery Complete
Completed: flashback database to restore point TEST_GRP
2023-07-25T19:59:38.047040-06:00
alter database open resetlogs
2023-07-25T19:59:38.048798-06:00
Data Guard Broker initializing...
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
Data Guard: broker startup completed
Data Guard: primary database controlfile verified
RESETLOGS after incomplete recovery UNTIL CHANGE 2142921 time 07/25/2023 19:54:34
2023-07-25T19:59:38.124157-06:00
NET (PID:23187): Archived Log entry 18 added for T-1.S-31 ID 0x903b45dd LAD:1
2023-07-25T19:59:38.147340-06:00
NET (PID:23187): Archived Log entry 19 added for T-1.S-29 ID 0x903b45dd LAD:1
2023-07-25T19:59:38.169097-06:00
NET (PID:23187): Archived Log entry 20 added for T-1.S-30 ID 0x903b45dd LAD:1

Since the standby database is currently in READ ONLY WITH APPLY mode, I need to follow these steps:

  1. Shut down the standby database.
  2. Start the standby database in mount mode.
  3. Disable the APPLY process for the standby database.
  4. Re-enable the APPLY process for the standby database.
  5. Open the standby database in read-only mode.

If the standby database were in the MOUNTED state, there would be no further actions required.

##############################################################################################################
##a. Standby Database
##############################################################################################################
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
 
SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
INST_ID NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- --------- -------------------- ---------------- -------------------- --------
1 TESTDB MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
 
##############################################################################################################
##b. Primary Database
##############################################################################################################
[oracle@primary ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jul 26 09:01:30 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "TESTDB"
Connected as SYSDG.
 
DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-OFF';
Succeeded.
 
DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-ON';
Succeeded.
 
##############################################################################################################
##c. Standby Database
##############################################################################################################
SQL> alter database open read only;
Database altered.

As I review the logs of my standby database, I notice that when I initiate the flashback process on my primary database and subsequently open it with the resetlogs option, this action will automatically trigger the flashback of my standby database as well.

2023-07-25T19:59:41.084190-06:00
rfs (PID:19991): Standby in the future of new recovery destination branch(resetlogs_id) 1054756778
rfs (PID:19991): Incomplete Recovery SCN:0x000000000020b3f7
rfs (PID:19991): Resetlogs SCN:0x000000000020b2ca
rfs (PID:19991): Flashback database to SCN:0x000000000020b2c9 (2142921) to follow new branch
rfs (PID:19991): New Archival REDO Branch(resetlogs_id): 1054756778 Prior: 1054754847
rfs (PID:19991): Archival Activation ID: 0x903b4441 Current: 0x903b45dd
rfs (PID:19991): Effect of primary database OPEN RESETLOGS
rfs (PID:19991): Managed Standby Recovery process is active
2023-07-25T19:59:41.086259-06:00
Incarnation entry added for Branch(resetlogs_id): 1054756778 (TESTDB)
2023-07-25T19:59:41.115292-06:00
Setting recovery target incarnation to 3
2023-07-25T19:59:41.116577-06:00
PR00 (PID:19541): MRP0: Incarnation has changed! Retry recovery...
...
2023-07-25T19:59:42.767092-06:00
Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_mrp0_19535.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u02/oradata/TESTDB_STDBY/datafile/o1_mf_system_hsdb684s_.dbf'
2023-07-25T20:00:02.784506-06:00
MRP0 (PID:19535): Recovery coordinator performing automatic flashback of database to SCN:0x000000000020b2c8 (2142920)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2023-07-25T20:00:03.076773-06:00
Setting recovery target incarnation to 2
2023-07-25T20:00:03.088461-06:00
Started logmerger process
2023-07-25T20:00:03.151319-06:00
Parallel Media Recovery started with 2 slaves
2023-07-25T20:00:03.275940-06:00
stopping change tracking
2023-07-25T20:00:03.372578-06:00
Media Recovery Log /u01/app/oracle/TESTDB_STDBY/archivelog/2023_07_25/o1_mf_1_26_hsdbzxkt_.arc
...
2023-07-25T20:00:06.743546-06:00
Media Recovery Log /u01/app/oracle/TESTDB_STDBY/archivelog/2023_07_25/o1_mf_1_29_hsdc38v6_.arc
2023-07-25T20:00:07.052081-06:00
Incomplete Recovery applied until change 2142920 time 07/25/2023 19:54:34
2023-07-25T20:00:07.060043-06:00
Flashback Media Recovery Complete
2023-07-25T20:00:07.129168-06:00
stopping change tracking
2023-07-25T20:00:07.152900-06:00
Setting recovery target incarnation to 3
2023-07-25T20:00:07.174093-06:00
Started logmerger process
2023-07-25T20:00:07.183156-06:00
PR00 (PID:20040): Managed Standby Recovery starting Real Time Apply
2023-07-25T20:00:07.242139-06:00
Parallel Media Recovery started with 2 slaves
2023-07-25T20:00:07.368253-06:00

After completing these steps, the table is successfully restored in both the primary and standby databases.

SQL> select count(1) from vishwa.test;
COUNT(1)
----------
300000
 
##############################################################################################################
## Standby Database
##############################################################################################################
SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
INST_ID NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- --------- -------------------- ---------------- -------------------- --------
1 TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
 
SQL> select count(1) from vishwa.test;
COUNT(1)
----------
30000
 

It's important to note that enabling flashback in the standby database is crucial. Neglecting to do so can lead to errors as indicated below, preventing the MRP from starting successfully

##############################################################################################################
## Standby Database
##############################################################################################################
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
 
##############################################################################################################
## From the Standby Database log
##############################################################################################################
2023-07-25T18:58:01.338567-06:00
rfs (PID:4675): Standby in the future of new recovery destination branch(resetlogs_id) 1054753080
rfs (PID:4675): Incomplete Recovery SCN:0x00000000002123dc
rfs (PID:4675): Resetlogs SCN:0x000000000020b027
rfs (PID:4675): New Archival REDO Branch(resetlogs_id): 1054753080 Prior: 1054722537
rfs (PID:4675): Archival Activation ID: 0x903bd59c Current: 0x903a9da7
rfs (PID:4675): Effect of primary database OPEN RESETLOGS
rfs (PID:4675): Managed Standby Recovery process is active
2023-07-25T18:58:58.128051-06:00
ARC2 (PID:18468): Archived Log entry 5 added for T-1.S-2 ID 0x903bd59c LAD:1
2023-07-25T18:59:38.299028-06:00
PR00 (PID:18518): MRP0: Incarnation has changed! Retry recovery...
2023-07-25T18:59:38.300079-06:00
Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_pr00_18518.trc:
ORA-19906: recovery target incarnation changed during recovery
PR00 (PID:18518): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2172426
stopping change tracking
2023-07-25T18:59:38.381319-06:00
Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_pr00_18518.trc:
ORA-19906: recovery target incarnation changed during recovery
2023-07-25T18:59:38.520995-06:00
Started logmerger process
...
Recovery Slave PR00 previously exited with exception 19909
2023-07-25T19:11:09.118089-06:00
Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_mrp0_5511.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u02/oradata/TESTDB_STDBY/datafile/o1_mf_system_hscbmkj0_.dbf'
2023-07-25T19:11:29.131828-06:00
MRP0 (PID:5511): Recovery coordinator encountered one or more errors during automatic flashback on standby
2023-07-25T19:11:29.132082-06:00
Background Media Recovery process shutdown (TESTDB)

To enable flashback in a standby database, follow these steps:

  1. Disable apply in the standby database.
  2. Ensure that ARCHIVELOG mode is enabled.
  3. Set the following two parameters:
    • db_recovery_file_dest
    • db_recovery_file_dest_size
  4. Enable flashback.
  5. Re-enable apply in the standby database.
[oracle@standby ~]$ dgmgrl /
Connected to "TESTDB_STDBY"
Connected as SYSDG.
 
DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-OFF';
Succeeded.
 
DGMGRL> exit
 
[oracle@standby ~]$ sqlplus / as sysdba
 
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
 
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle
db_recovery_file_dest_size big integer 20G
 
SQL> alter database flashback ON;
Database altered.
 
SQL> exit
[oracle@standby ~]$ dgmgrl /
Connected to "TESTDB_STDBY"
Connected as SYSDG.
 
DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-ON';
Succeeded.


 

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)