Oracle DB - Use flashback database to recover the database to the point in time before failover. Physical Standby Database

1. 在DGTEST_S_US，Enable Flashback Database
2. 抄下DGTEST_P SCN  select to_char(current_scn) from v$database;   7316107016
3. 在DGTEST_P做一些交易，確認有被寫到DGTEST_S_US
4. 停止testdb1, testdb2, testdb3， make snapshot of testdb3
5. 開啟testdb3做測試
select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database;
READ ONLY WITH APPLY, DGTEST_S_US, PHYSICAL STANDBY, 7316107470

SQL> alter database activate physical standby database;
alter database activate physical standby database
*
ERROR at line 1:
ORA-10457: cannot close standby database due to active media recovery

SQL> recover managed standby database cancel; 

SQL> alter database activate physical standby database;

Database altered.

SQL> select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database;

OPEN_MODE
------------------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
MOUNTED
DGTEST_S_US
PRIMARY
0

SQL> alter database open;


--測試

create table scott.emp_copy as select * from scott.emp;


--回復 standby db

6. 做FLASHBACK DATABASE 到Activate前的SCN

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
7316107953

SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
                7316107470
                
select to_char(standby_became_primary_scn) from v$database;

SQL> flashback database to scn 12841128892;
flashback database to scn 7316107470
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             318768104 bytes
Database Buffers          192937984 bytes
Redo Buffers                8015872 bytes
Database mounted.
SQL> flashback database to scn 12841128892;

Flashback complete.

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
0

SQL> alter database convert to physical standby;

Database altered.

SQL> select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database;
select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database
                                                                           *
ERROR at line 1:
ORA-01507: database not mounted


SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             318768104 bytes
Database Buffers          192937984 bytes
Redo Buffers                8015872 bytes
Database mounted.
SQL> select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database;

OPEN_MODE
------------------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
MOUNTED
DGTEST_S_US
PHYSICAL STANDBY
7316107470

SQL> alter database open;

Database altered.

SQL> select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database;

OPEN_MODE
------------------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
READ ONLY
DGTEST_S_US
PHYSICAL STANDBY
7316107470

7. 開啟testdb1 and testdb2
8. 啟動 DGTEST_P and DGTEST_S
9. DGMGRL會自動帶起DETEST_S_US的 managed standby recovery.

Thu Aug 21 10:45:31 2014
Data Guard: Database open completed; restarting redo-apply ...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (DGTEST)

SQL> select OPEN_MODE, DB_UNIQUE_NAME, database_role, to_char(current_scn) from v$database;

OPEN_MODE
------------------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
READ ONLY WITH APPLY
DGTEST_S_US
PHYSICAL STANDBY
7316108323

SQL> create table scott.emp_copy2 as select * from scott.emp_copy;
SQL> select * from scott.emp_copy2;
SQL> alter system switch logfile;

10.用Data Guard Broker 再次確認

oracle_DGTEST_testdb1.rgp.raritan.com$dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;

Configuration - DGTEST_P

  Protection Mode: MaxPerformance
  Databases:
    DGTEST_P    - Primary database
    DGTEST_S    - Physical standby database
    DGTEST_S_US - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS