failover之前一直沒有做，現在終於完整地測試了一遍。這個主要是記得開啟閃回功能，確保最後的reinstate順利完成。
就這樣就能在failover之後順利地把原主庫恢復為備庫。
----failover測試：

----2.2SBDB作為主庫：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ WRITE MAXIMUM PERFORMANCE SESSIONS ACTIVE PRIMARY

----2.4ORA11GR2作為備庫：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ ONLY WITH APPLY MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY

----開啟主庫的flashback功能：

SQL> alter database flashback on;

Database altered.

#已經開啟。

----主庫模擬故障不正常關庫：

--在不正常關庫前可以檢視主備庫的最大日誌號：

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

2 and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

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

210 2017-03-19 22:32:10

#經核對，主備庫的日誌號是相同的，則同步沒有延遲。

----主庫不正常關庫模擬：

SQL> shutdown abort;

ORACLE instance shut down.

SQL> select status from v$instance;

select status from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 26669

Session ID: 88 Serial number: 313

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

select open_mode,protection_mode,switchover_status,database_role from v$database

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 26669

Session ID: 88 Serial number: 313

----檢視備庫此時的狀態：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ ONLY WITH APPLY MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY

#現在不是正常的switchover切換，所以備庫依然顯示切換狀態為NOT ALLOWED 。

----備庫登入DGbroker工具進行failover切換：

[oracle@oracle ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/skysky

Connected.

DGMGRL> show configuration;

Configuration - SBDB

Protection Mode: MaxPerformance

Databases:

SBDB - Primary database

ORA11GR2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ORA-01034: ORACLE not available

ORA-16625: cannot reach database "SBDB"

DGM-17017: unable to determine configuration status

----進行failover切換：

DGMGRL> failover to 'ORA11GR2';

Performing failover NOW, please wait...

Failover succeeded, new primary is "ORA11GR2"

DGMGRL>

#failover切換成功。

----切換成功後檢視DGbroker配置檔案：

DGMGRL> show configuration;

Configuration - SBDB

Protection Mode: MaxPerformance

Databases:

ORA11GR2 - Primary database

SBDB - Physical standby database (disabled)

ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

---檢視現在主庫：

DGMGRL> show database 'ORA11GR2';

Database - ORA11GR2

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

ORA11GR2

Database Status:

SUCCESS

---檢視原來主庫：

DGMGRL> show database 'SBDB';

Database - SBDB

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: (unknown)

Apply Lag: (unknown)

Apply Rate: (unknown)

Real Time Query: OFF

Instance(s):

SBDB

Database Status:

ORA-16661: the standby database needs to be reinstated

----嘗試檢視failover後主庫的狀態：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

select open_mode,protection_mode,switchover_status,database_role from v$database

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 1497

Session ID: 18 Serial number: 7

SQL> select status from v$instance;

ERROR:

ORA-03114: not connected to ORACLE

----重啟新主庫的例項：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ WRITE MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY

#現在變成了可讀寫狀態，則作為主庫使用。

----一段時間後，嘗試重啟原來的主庫：

[oracle@host01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 20 00:55:20 2017

Enter password:

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 704646264 bytes

Database Buffers 121634816 bytes

Redo Buffers 2392064 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from

being opened

----檢視此時的狀態：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY

----alert日誌的記錄：

Successful mount of redo thread 1, with mount id 255649302

Allocated 3981120 bytes in shared pool for flashback generation buffer

Starting background process RVWR

Mon Mar 20 00:55:55 2017

RVWR started with pid=21, OS id=26995

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Mon Mar 20 00:55:56 2017

ALTER DATABASE OPEN

Data Guard Broker initializing...

Data Guard Broker initialization complete

Data Guard: verifying database primary role...

Starting Data Guard Broker (DMON)

Mon Mar 20 00:55:58 2017

INSV started with pid=22, OS id=27002

Mon Mar 20 00:56:01 2017

NSV1 started with pid=23, OS id=27006

Mon Mar 20 00:56:07 2017

Data Guard: version check completed

Data Guard determines a failover has occurred - this is no longer a primary database

ORA-16649 signalled during: ALTER DATABASE OPEN...

----恢復原來的主庫為新備庫：

----把原來的主庫開啟到mount狀態：

[oracle@host01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 20 01:04:39 2017

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 704646264 bytes

Database Buffers 121634816 bytes

Redo Buffers 2392064 bytes

Database mounted.

SQL>

----使用DGbroker登入Reinstate 原來的主庫（Reinstate the old primary database）：

--新主庫側：

DGMGRL> REINSTATE DATABASE 'SBDB';

Reinstating database "SBDB", please wait...

Operation requires shutdown of instance "SBDB" on database "SBDB"

Shutting down instance "SBDB"...

Unable to connect to database

ORA-12545: Connect failed because target host or object does not exist

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the REINSTATE command:

shut down instance "SBDB" of database "SBDB"

start up and mount instance "SBDB" of database "SBDB"

#這個雖然沒有一步到位，但是隻要跟著提示走，去重啟SBDB例項就可以了。重啟完之後，重新執行 REINSTATE DATABASE 'SBDB'
語句，就可以順利地REINSTATE DATABASE，把原來的主庫恢復起來了。重啟過程和REINSTATE過程可以參考本文提示到的兩段alert日誌記錄。

DGMGRL>

DGMGRL> REINSTATE DATABASE 'SBDB';

Reinstating database "SBDB", please wait...

Reinstatement of database "SBDB" succeeded

DGMGRL>

#REINSTATE DATABASE完成。

----檢視引數檔案：

DGMGRL> show configuration;

Configuration - SBDB

Protection Mode: MaxPerformance

Databases:

ORA11GR2 - Primary database

SBDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

----檢視新備庫：

DGMGRL> show database 'SBDB';

Database - SBDB

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 1 second ago)

Apply Lag: 0 seconds (computed 1 second ago)

Apply Rate: 196.00 KByte/s

Real Time Query: ON

Instance(s):

SBDB

Database Status:

SUCCESS

#已經恢復成功。

----檢視備庫的狀態：

SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ ONLY WITH APPLY MAXIMUM PERFORMANCE SWITCHOVER PENDING PHYSICAL STANDBY

#已經成功切換成DG的新備庫。

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

2 and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

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

6 2017-03-20 01:20:19
#新主備庫的最大日誌號相同。

--附加reinstate過程的日誌：

Reinstate過程中的alert日誌選段：

Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/arch/SBDB/

RFS[1]: Assigned to RFS process 27186

RFS[1]: Database mount ID mismatch [0xf3d3a60:0xf3ceb30] (255670880:255650608)

RFS[1]: Not using real application clusters

Mon Mar 20 01:14:26 2017

RSM0 started with pid=26, OS id=27188

FLASHBACK DATABASE TO SCN 3526040

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 3 Seq 211 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/SBDB/redo03.log

Mem# 1: /u01/app/oracle/oradata/SBDB/redo06.log

Incomplete Recovery applied until change 3526041 time 03/20/2017 00:38:59

Flashback Media Recovery Complete

Completed: FLASHBACK DATABASE TO SCN 3526040

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SBDB)

Mon Mar 20 01:14:36 2017

Flush standby redo logfile failed:1649

Clearing standby activation ID 255679734 (0xf3d5cf6)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Clearing online redo logfile 1 /u01/app/oracle/oradata/SBDB/redo01.log

Clearing online log 1 of thread 1 sequence number 209

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /u01/app/oracle/oradata/SBDB/redo02.log

Clearing online log 2 of thread 1 sequence number 210

Mon Mar 20 01:14:49 2017

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /u01/app/oracle/oradata/SBDB/redo03.log

Clearing online log 3 of thread 1 sequence number 211

Clearing online redo logfile 3 complete

Completed: alter database convert to physical standby

使用broker工具failover切換之後恢復原來的主庫為新備庫

相關文章