啟動dataguard備庫到read-only狀態

1. 簡述

Standby db的一個作用是用於基於時間點的報表查詢,或者分擔主庫的部分統計報表消耗。我們可以將一個standby db啟動到read－only狀態，這時就可以提供給客戶端查詢訪問，在standby db開啟的過程中，主庫的日誌檔案還是傳遞過來，只不過不做日誌的應用。所以standby db開啟的時間越長，以後做日誌應用的時間就越長，如果主庫故障，則啟用備庫到正常狀態的時間就越長，這是需要您來權衡的。

stndby db資料庫開啟後,如果需要從read-only狀態回到standby狀態,我們不需要關閉standby db,只需要執行命令'alter database recover managed standby database disconnect from session'就可以重新回到standby狀態.

[@more@]

2. 基本環境描述

2.1 主庫基本情況

[root@primarydb ~]# more /etc/hosts

168.0.3.92 primarydb

168.0.3.93 standbydb

[oracle@primarydb ~]$ sqlplus / as sysdba

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

READ WRITE PRIMARY

2.2備庫基本情況

[oracle@standbydb ~]$ sqlplus / as sysdba

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PHYSICAL STANDBY

2.3 主庫建立一個表,用於備庫開啟時驗證資料

[oracle@primarydb ~]$ sqlplus / as sysdba

SQL>create table test02 as select * from v$parameter;

SQL>commit;

SQL>alter system switch logfile;

3. 啟動standby db到read-only狀態

3.1 如果standby db處於mount狀態,則可以透過下面的命令啟動到read-only狀態

[oracle@standbydb ~]$ sqlplus / as sysdba

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

-------[oracle@standbydb ~]$ sqlplus / as sysdba

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

MOUNTED PHYSICAL STANDBY

SQL>alter database recover managed standby database cancel;

SQL>alter database open;

3.2 如果standby db資料庫處於關閉狀態,則執行下面的命令啟動到read-only狀態

[oracle@standbydb ~]$ sqlplus / as sysdba

SQL>startup

3.3 檢查確認standby db處於開啟狀態,並且可以查詢普通的表資料

[oracle@standbydb ~]$ sqlplus / as sysdba

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

READ ONLY PHYSICAL STANDBY

SQL> select count(1) from test02;

COUNT(1)

----------

259

4. 切換回standby狀態.

4.1 切換回standby 狀態

[oracle@standbydb ~]$ sqlplus / as sysdba

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PHYSICAL STANDBY

4.2 主庫切換日誌,驗證日誌應用狀態

[oracle@primarydb ~]$ sqlplus / as sysdba

SQL> alter system archive log current;

System altered.

[root@standbydb bdump]# tail -f alert_newgridctl.log

Completed: alter database recover managed standby database disconnect from session

Tue Sep 14 12:26:35 2010

RFS[1]: Archived Log: '/oradata/archivelog/newstandby_arc/1_278_724504451.dbf'

Tue Sep 14 12:26:38 2010

Media Recovery Log /oradata/archivelog/newstandby_arc/1_278_724504451.dbf

Media Recovery Waiting for thread 1 sequence 279