本文討論10G DATA GUARD進行角色切換的步驟。

伺服器資訊：
primary：原主庫，切換後就是備庫
standby：原備庫，切換後就是主庫

1、檢查primary的SWITCHOVER狀態（當前是主庫）
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

如果SWITCHOVER_STATUS不是TO STANDBY，則表示當前主庫不能執行switchover，這時候需要檢查主庫的設定了。

2、在primary執行（當前是主庫）
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

此時後臺會做一系統的操作，如停止相關程式、建立standby 控制檔案等。

後臺日誌如下：
Wed Jun 27 22:50:18 2007
Stopping background process CJQ0
Wed Jun 27 22:50:18 2007
SMON: disabling tx recovery
Wed Jun 27 22:50:18 2007
Stopping background process QMNC
Wed Jun 27 22:50:20 2007
Stopping Job queue slave processes
Wed Jun 27 22:50:20 2007
Job queue slave processes stopped
Wed Jun 27 22:50:21 2007
SMON: disabling cache recovery
Wed Jun 27 22:50:21 2007
Shutting down archive processes
Archiving is disabled
Wed Jun 27 22:50:26 2007
ARCH shutting down
ARC3: Archival stopped
Wed Jun 27 22:50:31 2007
ARCH shutting down
ARC2: Archival stopped
Wed Jun 27 22:50:36 2007
ARCH shutting down
ARC1: Archival stopped
Wed Jun 27 22:50:41 2007
ARCH shutting down
ARC0: Archival stopped
Wed Jun 27 22:50:42 2007
Thread 1 closed at log sequence 34
Successful close of redo thread 1
Wed Jun 27 22:50:42 2007
ARCH: Noswitch archival of thread 1, sequence 34
ARCH: End-Of-Redo Branch archival of thread 1 sequence 34
ARCH: Archiving is disabled due to current logfile archival
Clearing standby activation ID 1463601975 (0x573ccb37)
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;
Archivelog for thread 1 sequence 34 required for standby recovery
MRP0 started with pid=10, OS id=2963
Wed Jun 27 22:50:44 2007
MRP0: Background Managed Standby Recovery process started (primary)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /u01/archivelog/1_34_626106231.arc
Identified End-Of-Redo for thread 1 sequence 34
Wed Jun 27 22:50:50 2007
Media Recovery End-Of-Redo indicator encountered
Wed Jun 27 22:50:50 2007
Media Recovery Applied until change 162373
Wed Jun 27 22:50:50 2007
MRP0: Media Recovery Complete: End-Of-REDO (primary)
Resetting standby activation ID 1463601975 (0x573ccb37)
Wed Jun 27 22:50:51 2007
MRP0: Background Media Recovery process shutdown (primary)
Wed Jun 27 22:50:52 2007
Switchover: Complete - Database shutdown required (primary)
Wed Jun 27 22:50:52 2007
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

從alert資訊可以看到，oracle還要求在primary建立standby redo log，這裡由於我們之前在primary上已經建立的standby redo log，這一步我們就不用做了。

3、重啟primary（目前是備庫）

SQL> shutdown immediate
ORA-01507: database not mounted

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

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 88081256 bytes
Database Buffers 75497472 bytes
Redo Buffers 2932736 bytes
Database mounted.

4、驗證primary的狀態（當前是備庫）
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

如果SWITCHOVER_STATUS是TO PRIMARY，表示當前資料庫已經處於standby角色中了。

5、在standby上執行（當前是備庫，即將轉為主庫）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

此時後臺日志如下：
Wed Jun 27 22:49:10 2007
ALTER DATABASE SWITCHOVER TO PRIMARY (primary)
Wed Jun 27 22:49:10 2007
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 162373
Online log /u01/oracle/oradata/primary/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oracle/oradata/primary/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oracle/oradata/primary/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 162371
Wed Jun 27 22:49:10 2007
Switchover: Complete - Database shutdown required (primary)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Wed Jun 27 22:49:13 2007
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Wed Jun 27 22:49:23 2007
ARCH shutting down
ARC0: Archival stopped
Wed Jun 27 22:49:28 2007
ARCH shutting down
ARC1: Archival stopped

6、重啟standby（當前是主庫）

SQL> shutdown immediate
ORA-01507: database not mounted

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

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 79692648 bytes
Database Buffers 83886080 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

可以看到，SWITCHOVER_STATUS已經變成TO STANDBY了。

7、驗證切換是否正常
--在primary執行
SQL> alter database recover managed standby database disconnect from session;

Database altered.

--在standby執行
SQL> create table test1(id int);

Table created.

SQL> insert into test1 values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

--在primary執行
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from test1;

ID
----------
10

可見，DG執行正常。如果需要再切換恢復，按照上面的步驟再走一遍就可以。

這裡我們其實忽略了很多步驟，包含在新備庫新增standby redo log，修改新備庫的引數檔案等等，但由於我們之前在配置DG的時候已經預先做了這些工作了，使得切換步驟很簡單。
在管理DG時的一個好習慣就是在修改主庫或者備庫的任何東西都要考慮switch over或者failover，儘量在修改一端的同時修改另一端的對應資訊，使得switch over或者failover儘量方便。

10G DG SWITCH OVER

相關文章