Oracle 19C Data Guard基礎運維-03 Failovers(物理)

Oracle 19C Data Guard 基礎運維 -0 3 Failovers( 物理 )

原主庫	原備庫	Failovers	新主庫	獨立庫
192.168.31.90	192.168.31.100		192.168.31.100	192.168.31.90
cjcdb	chendb		chendb	cjcdb

Failover ：

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/data-guard-concepts-and-administration.pdf

Figure 9-4 Failover to a Standby Database 

Performing a Failover to a Physical Standby Database  

主庫意外當機，並無法啟動

場景一：沒有歸檔間隙，零資料丟失

主庫模擬故障：

重新命名system 資料檔案

[oracle@cjcos01 CJCDB]$ pwd

/u01/app/oracle/oradata/CJCDB

[oracle@cjcos01 CJCDB]$ mv system01.dbf system01.dbf.bak

SQL> alter system checkpoint;

alter system checkpoint

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 5309

Session ID: 45 Serial number: 38130

備庫日誌：

2020-04-18T08:49:26.394680+08:00

 rfs (PID:6276): Possible network disconnect with primary database

啟動主庫失敗：

SQL> startup

ORACLE instance started.

Total System Global Area 1375728192 bytes

Fixed Size       9134656 bytes

Variable Size    1107296256 bytes

Database Buffers   251658240 bytes

Redo Buffers       7639040 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/CJCDB/system01.dbf'

SQL> select status from v$instance;

STATUS

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

MOUNTED

備庫：3.100

1. 檢查 dg 恢復模式

SQL> select database_role,protection_level,protection_mode from v$database;

DATABASE_ROLE  PROTECTION_LEVEL     PROTECTION_MODE

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

PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2 檢查 archive_gap ( 沒有 gap 說明備庫執行 failovers 不會丟失資料 )

SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

no rows selected

檢查沒有歸檔gap後，最好在檢查主從庫歸檔日誌是否完全同步，備庫同步日誌是否沒有錯誤。

3 備庫取消 DG 應用 ( 關閉 MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

4 備庫執行 failover

---謹慎操作，確保資料已完全同步後再切換，避免切換後資料丟失。

SQL> ALTER DATABASE FAILOVER TO chendb;

Database altered.

5 開啟備庫

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database open;

Database altered.

6 新主庫執行全備

7 新主庫檢視狀態

SQL> select database_role,protection_level,protection_mode from v$database;

DATABASE_ROLE  PROTECTION_LEVEL     PROTECTION_MODE

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

PRIMARY  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

FAILED DESTINATION

SQL> insert into test1 select * from test1;

1  rows created.

SQL> commit;

Commit complete.

修復原主庫

SQL> shutdown immediate

[oracle@cjcos01 CJCDB]$ mv system01.dbf.bak system01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 1375728192 bytes

Fixed Size       9134656 bytes

Variable Size    1107296256 bytes

Database Buffers   251658240 bytes

Redo Buffers       7639040 bytes

Database mounted.

Database opened.

此時原主庫變成的一個獨立的資料庫，可以讀寫方式開啟

SQL> select database_role,protection_level,protection_mode from v$database;

DATABASE_ROLE  PROTECTION_LEVEL     PROTECTION_MODE

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

PRIMARY  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

嘗試將原主庫切換為 physical standby

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

alter database commit to switchover to physical standby with session shutdown

*

ERROR at line 1:

ORA-16416: No viable Physical Standby switchover targets available

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

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

此時原故障主庫變成了一個獨立的資料庫，若想恢復成現有主庫的 Physical Standby ，可以通過現有主庫的資料進行重新搭建，或通過原故障主庫failovers 之前的備份，進行恢復，在通過現有主庫進行 rman 增量追加資料。

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2686922/，如需轉載，請註明出處，否則將追究法律責任。