We want to flshback out primary database without rebuilding our standby database. for doing this flashback should be enabled on both primary and standby with adequate flashback retention and space enough space in flash recovery area
this is our setup
INDIA - INDIA1, INDIA2 - standby
LONDON - LONDON1, LONDON2 - Primary
nodes - ol6-112-rac1, ol6-112-rac2current status of databases
LONDON2@ol6-112-rac2 > select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
INDIA READ WRITE PRIMARY YES
INDIA1@ol6-112-rac1 > select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
INDIA READ ONLY WITH APPLY PHYSICAL STANDBY YES
broker status
DGMGRL> show configuration
Configuration - INDIA
Protection Mode: MaxPerformance
Databases:
LONDON - Primary database
INDIA - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database 'LONDON'
Database - LONDON
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
LONDON1
LONDON2
Database Status:
SUCCESS
DGMGRL> show database 'INDIA'
Database - INDIA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
INDIA1 (apply instance)
INDIA2
Database Status:
SUCCESS
flashback settings
LONDON2@ol6-112-rac2 > sho parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
LONDON2@ol6-112-rac2 > sho parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 3901M
INDIA1@ol6-112-rac1 > sho parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
INDIA1@ol6-112-rac1 > sho parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 5000M
create restore point on primary
LONDON2@ol6-112-rac2 > create restore point CLEAN_DB guarantee flashback database;
Restore point created.
LONDON2@ol6-112-rac2 > select * from v$restore_point;
LONDON2@ol6-112-rac2 > /
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE
---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------------------------------------------------------
1492419 1 YES 104857600 03-DEC-21 11.18.30.000000000 AM YES
CLEAN_DBnow create a table on primary and insert few records
LONDON2@ol6-112-rac2 > create table test (id number, created date);
Table created.
LONDON2@ol6-112-rac2 > Insert into test values (1,sysdate);
1 row created.
LONDON2@ol6-112-rac2 > Insert into test values (2,sysdate);
1 row created.
LONDON2@ol6-112-rac2 > commit;
Commit complete.
LONDON2@ol6-112-rac2 > alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select * from test order by 2;
Session altered.
LONDON2@ol6-112-rac2 >
ID CREATED
---------- --------------------
1 03-DEC-2021 11:20:51
2 03-DEC-2021 11:20:57
verify it reached in standby
INDIA1@ol6-112-rac1 > alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
INDIA1@ol6-112-rac1 > select * from test order by 2;
ID CREATED
---------- --------------------
1 03-DEC-2021 11:20:51
2 03-DEC-2021 11:20:57stop primary database and prepare for flashback
[oracle@ol6-112-rac2 admin]$ srvctl stop database -d LONDON -o immediate
[oracle@ol6-112-rac2 admin]$ srvctl status database -d LONDON
Instance LONDON1 is not running on node ol6-112-rac1
Instance LONDON2 is not running on node ol6-112-rac2
[oracle@ol6-112-rac2 admin]$ srvctl start instance -d LONDON -i LONDON2 -o mount
[oracle@ol6-112-rac2 admin]$ srvctl status database -d LONDON
Instance LONDON1 is not running on node ol6-112-rac1
Instance LONDON2 is running on node ol6-112-rac2perform flashback of primary
[oracle@ol6-112-rac2 admin]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 3 11:29:41 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
LONDON2@ol6-112-rac2 > flashback database to restore point CLEAN_DB;
Flashback complete.
LONDON2@ol6-112-rac2 > alter database open resetlogs;
Database altered.start primary
[oracle@ol6-112-rac2 admin]$ srvctl stop database -d LONDON -o immediate
[oracle@ol6-112-rac2 admin]$ srvctl start database -d LONDON
[oracle@ol6-112-rac2 admin]$ srvctl status database -d LONDON
Instance LONDON1 is running on node ol6-112-rac1
Instance LONDON2 is running on node ol6-112-rac2verify newly created table is not present in primary now
LONDON2@ol6-112-rac2 > alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select * from test order by 2;
Session altered.
LONDON2@ol6-112-rac2 >
select * from test order by 2
*
ERROR at line 1:
ORA-00942: table or view does not exist
but it is still there in standby
INDIA1@ol6-112-rac1 > alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select * from test order by 2;
Session altered.
INDIA1@ol6-112-rac1 >
ID CREATED
---------- --------------------
1 03-DEC-2021 11:20:51
2 03-DEC-2021 11:20:57
On the primary database, determine an SCN that is at least two SCNs prior to the SCN when the OPEN RESETLOGS command was issued.
LONDON2@ol6-112-rac2 > SELECT TO_CHAR(resetlogs_change# - 2) FROM v$database;
TO_CHAR(RESETLOGS_CHANGE#-2)
----------------------------------------
1492419On the standby database, obtain the current SCN by using the following query:
INDIA1@ol6-112-rac1 > SELECT TO_CHAR(current_scn) FROM v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
1495496Flash back the standby database to the “before RESETLOGS” SCN that you queried in step 1
INDIA1@ol6-112-rac1 > FLASHBACK STANDBY DATABASE TO SCN 1492419 ;
Flashback complete.
INDIA1@ol6-112-rac1 > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.stop start standby
[oracle@ol6-112-rac1 admin]$ srvctl stop database -d INDIA
[oracle@ol6-112-rac1 admin]$ srvctl start database -d INDIA
INDIA1@ol6-112-rac1 > select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
INDIA READ ONLY WITH APPLY PHYSICAL STANDBY YESso now table gone from standby as well
INDIA1@ol6-112-rac1 > alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
INDIA1@ol6-112-rac1 > select * from test order by 2;
select * from test order by 2
*
ERROR at line 1:
ORA-00942: table or view does not exist
create another table on primary
LONDON2@ol6-112-rac2 > create table test2 (id number, created date);
Table created.
LONDON2@ol6-112-rac2 > Insert into test2 values (1,sysdate);
1 row created.
LONDON2@ol6-112-rac2 > Insert into test2 values (2,sysdate);
1 row created.
LONDON2@ol6-112-rac2 > commit;
Commit complete.
LONDON2@ol6-112-rac2 > select * from test2 order by 2;
ID CREATED
---------- --------------------
1 03-DEC-2021 11:38:52
2 03-DEC-2021 11:39:01verify it reached standby
INDIA1@ol6-112-rac1 > select * from test2 order by 2;
ID CREATED
---------- --------------------
1 03-DEC-2021 11:38:52
2 03-DEC-2021 11:39:01
DBATracker 