Oracle 19C Data Guard基礎運維-07 failover後閃回恢復dg架構

Oracle 19C Data Guard 基礎運維 -0 7   failover 後閃回恢復 dg 架構

 

將失敗的主資料庫閃回物理備用資料庫

Flashing Back a Failed Primary Database into a Physical Standby Database

這些步驟將舊的 primary database 作為 physical standby database 帶回到Oracle Data Guard 配置中。

These steps bring the old primary database back into the Oracle Data Guard   configuration as a physical standby database.

以下步驟假設已對物理備用資料庫執行 failover ，並且在 failover 時已在舊主資料庫上啟用了閃回資料庫。

The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover.

原主庫提前啟用閃回資料庫

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

NO

SQL> show parameter recover y

NAME      TYPE  VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest      string

db_recovery_file_dest_size      big integer 0

SQL>alter system set db_recovery_file_dest_size=10 g  scope=spfile;

[root@cjcos02 ~]# mkdir /flashbak

[root@cjcos02 ~]# chown oracle.oinstall /flashbak

SQL>alter system set db_recovery_file_dest='/flashbak' scope=spfile;

SQL> alter database flashback on;

SQL> shutdown immediate

SQL> Startup

SQL>select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

YES

原主庫因故障當機無法open,dg 備庫系統執行 failover;

備庫執行failover( 可參考如下連結 ) ：

http://blog.itpub.net/29785807/viewspace-2686922/

http://blog.itpub.net/29785807/viewspace-2687052/

此時原主庫無法直接轉換成備庫

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> recover managed standby database using current logfile disconnect from session;

ORA-01665: control file is not a standby control file

需要執行如下步驟，將原主庫轉換為Physical Standby Database ：

1. 在新主資料庫上，發出以下查詢以確定舊備用資料庫成為新主資料庫的 SCN

On the new primary database, issue the following query to determine the SCN at

which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

----------------------------------------

3874221

2. 關閉舊的主資料庫 ( 如果需要的話 ) ，掛載，並將它閃回到上一步中查出的 STANDBY_BECAME_PRIMARY_SCN 值。

Shut down the old primary database (if necessary), mount it, and flash it back to   the value for STANDBY_BECAME_PRIMARY_SCN that was determined in the previous   step.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

---SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

SQL> FLASHBACK DATABASE TO SCN  3874221;

3. 若要將資料庫轉換為物理備用資料庫，請在舊主資料庫上發出以下語句

To convert the database to a physical standby database, issue the following

statement on the old primary database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

4. 要開始將 redo 傳輸到新的物理備用資料庫，請在新的主資料庫上執行以下步驟

To start transporting redo to the new physical standby database, perform the

following steps on the new primary database:

a. 發出以下查詢，檢視存檔目的地的當前狀態 :

a. Issue the following query to see the current state of the archive destinations:

SQL> col dest_name for a20

SQL> col destination for a10

SQL> col protection_mode for a25

SQL> col error for a15

SQL> col srl for a10

SQL> set linesize 200

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS; 

......

b. 如有必要，啟用 destination:

b. If necessary, enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

執行日誌切換，以確保備用資料庫開始接收來自新主資料庫的重做資料，並驗證它已成功傳送。在新的主資料庫上發出以下SQL 語句 :

c. Perform a log switch to ensure the standby database begins receiving redo

data from the new primary database, and verify it was sent successfully. Issue

the following SQL statements on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

在新的備用資料庫上，您可能還需要更改LOG_ARCHIVE_DEST_n 初始化引數，以便重做傳輸服務不會將重做資料傳輸到其他資料庫。

On the new standby database, you may also need to change the

LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services

do not transmit redo data to other databases.

5. 啟動重做應用到新的物理備用資料庫

Start Redo Apply on the new physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

6. 檢視資料同步

新主庫31.100 chendb:

SQL> insert into test1 values(4);

1 row created.

SQL> insert into test1 values(5);

1 row created.

SQL> commit;

新備庫31.90 cjcdb:

SQL> select * from test1;

ID

----------

 1

 2

 3

 4

 5

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！