8、故障問題解決

在之前的系列中，已經將Primary和Standby進行安裝，並且redo apply過程已經測試成功。但是，在實驗中，還是存在一系列問題需要完善補充。

筆者在測試之後，就直接關閉伺服器。重新啟動之後，首先啟動standby服務例項。

[oracle@SimpleLinux ~]$ export ORACLE_SID=ora11gsy

[oracle@SimpleLinux ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 11:36:52 2014

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 301993220 bytes

Database Buffers 62914560 bytes

Redo Buffers 6176768 bytes

ORA-00205: error in identifying control file, check alert log for more info

啟動mount過程中，需要訪問控制檔案，報錯控制檔案不存在。控制檔案資訊是寫入到spfile、pfile中的。所以檢視一下控制檔案資訊：

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/dbs/cntrlora11

gsy.dbf

control_management_pack_access string DIAGNOSTIC+TUNING

在之前還原controlfile的時候，目錄明顯不是這樣。當時資訊如下：

RMAN> restore standby controlfile from '/standbybackup/o1_mf_ncnnf_TAG20140524T204716_9r156r7j_.bkp';

Starting restore at 24-MAY-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/u01/app/oradata/ORA11GSY/controlfile/o1_mf_9r18tmv6_.ctl

output file name=/u01/app/fast_recovery_area/ORA11GSY/controlfile/o1_mf_9r18tpkf_.ctl

Finished restore at 24-MAY-14

對應目錄中，也的確有對應的OMF檔案。

[oracle@SimpleLinux ~]$ cd /u01/app/oradata/ORA11GSY/controlfile/

[oracle@SimpleLinux controlfile]$ ls -l

total 9520

-rw-r-----. 1 oracle oinstall 9748480 May 24 23:15 o1_mf_9r18tmv6_.ctl

這也就解釋了為什麼在之前建立之後，系統執行正常。重啟之後，故障出現的原因。就是由於新的控制檔案資訊沒有寫入到spfile或者pfile中，Oracle選擇了一個系統預設控制檔案資訊而導致的。

此時，我們還發現Oracle還是再使用之前建立pfile啟動ora11gsy例項。

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

[oracle@SimpleLinux ~]$ cd /u01/app/oracle/dbs

[oracle@SimpleLinux dbs]$ ls -l

total 19080

-rw-rw----. 1 oracle oinstall 1544 May 24 23:14 hc_ora11g.dat

-rw-rw----. 1 oracle oinstall 1544 May 25 11:37 hc_ora11gsy.dat

-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r--r--. 1 oracle oinstall 774 May 24 21:17 initora11g.ora

-rw-r--r--. 1 oracle oinstall 774 May 24 21:18 initora11gsy.ora

-rw-r-----. 1 oracle oinstall 24 Apr 1 12:39 lkORA11G

當前最簡單策略是修改initora11gsy.ora檔案，加入控制檔案目錄資訊：

control_files='/u01/app/oradata/ORA11GSY/controlfile/o1_mf_9r18tmv6_.ctl','/u01/app/fast_recovery_area/ORA11GSY/controlfile/o1_mf_9r18tpkf_.ctl'

重新啟動資料庫。

SQL> startup

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 301993220 bytes

Database Buffers 62914560 bytes

Redo Buffers 6176768 bytes

Database mounted.

Database opened.

重新建立spfile，再次啟動資料庫。

SQL> create spfile from pfile;

File created.

SQL> startup force;

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 301993220 bytes

Database Buffers 62914560 bytes

Redo Buffers 6176768 bytes

Database mounted.

Database opened.

SQL> show parameter control_files;

NAME TYPE VALUE

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

control_files string /u01/app/oradata/ORA11GSY/cont

rolfile/o1_mf_9r18tmv6_.ctl, /

u01/app/fast_recovery_area/ORA

11GSY/controlfile/o1_mf_9r18tp

kf_.ctl

啟動standby資料庫的redo apply過程。

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

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

READ ONLY WITH APPLY PHYSICAL STANDBY

9、同步測試

啟動standby之後，可以啟動primary進行測試。

[oracle@SimpleLinux dbs]$ export ORACLE_SID=ora11g

[oracle@SimpleLinux dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 12:07:36 2014

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 301993220 bytes

Database Buffers 62914560 bytes

Redo Buffers 6176768 bytes

Database mounted.

Database opened.

Primary端建立一個資料表T。

SQL> create table t as select * from dba_objects;

Table created.

到standby端，我們發現redo apply執行過程。

SQL> conn sys/oracle@ora11gsy as sysdba

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

86032

同步執行成功！

10、引數補充

至此，DG環境已經搭建完成，還需要一些補充過程需要完成。首先，Primary端的standby log是缺失的。如果發生switchover或者failover，我們是沒有辦法在Primary端進行操作的。

SQL> select group#, dbid from v$standby_log;

GROUP# DBID

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

Primary端建立standby log日誌。

SQL> alter database add standby logfile group 4 size 50m;

Database altered

SQL> alter database add standby logfile group 5 size 50m;

Database altered

SQL> select group#, dbid, bytes, status from v$standby_log;

GROUP# DBID BYTES STATUS

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

4 UNASSIGNED 52428800 UNASSIGNED

5 UNASSIGNED 52428800 UNASSIGNED

standby端同樣，如果發生switchover或者failover，在傳遞日誌上也有一些問題。

SQL> conn sys/oracle@ora11gsy as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=ora11gsy valid_for=(online_logfiles,primary_role) db_unique_name=ora11gsy

log_archive_dest_20 string

log_archive_dest_21 string

自己傳給自己日誌，顯然有一些問題，需要進行修改。

SQL> alter system set log_archive_dest_2='SERVICE=ora11g valid_for=(online_logfiles,primary_role) db_unique_name=ora11g';

System altered

11、switchover實驗

最後進行一下switchover實驗。首先需要在Primary端進行操作，終止操作。

SQL> conn sys/oracle@ora11g as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

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

Database altered

啟動primary到standby mount狀態。

[oracle@SimpleLinux trace]$ export ORACLE_SID=ora11g

[oracle@SimpleLinux trace]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 12:31:28 2014

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 327159044 bytes

Database Buffers 37748736 bytes

Redo Buffers 6176768 bytes

SQL> alter database mount standby database;

Database altered.

Standby端進行角色切換。

SQL> conn sys/oracle@ora11gsy as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

SQL> alter database commit to switchover to primary with session shutdown;

Database altered

SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

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

ORA11G ARCHIVELOG MOUNTED PRIMARY NOT ALLOWED ora11gsy

啟動伺服器：

SQL> alter database open;

Database altered

standby到primary的日誌傳遞是開始的，但是由於原Primary沒有啟用apply過程，所以applied狀態為NO。

SQL> select recid, sequence#, STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log where name='ora11g';

RECID SEQUENCE# STANDBY_DEST ARCHIVED APPLIED

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

9 13 YES YES NO

11 14 YES YES NO

將Primary端的Redo Apply過程加以應用。

SQL> conn sys/oracle@ora11g as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PHYSICAL STANDBY –角色已經變化

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

Database altered

檢視傳遞日誌的應用情況。

SQL> select recid, sequence#, STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log where sequence#>12;

RECID SEQUENCE# STANDBY_DEST ARCHIVED APPLIED

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

15 13 NO YES YES

16 14 NO YES YES

下面進行簡單測試，在ora11gsy上，清理資料表T。

SQL> delete t;

86032 rows deleted

SQL> commit;

Commit complete

在ora11g上，應用啟用，可以看到t資料表資料取值為0。

SQL> conn sys/oracle@ora11g as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

SQL> alter database recover managed standby database cancel;

Database altered

SQL> alter database open;

Database altered

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

Database altered

SQL> select count(*) from t;

COUNT(*)

----------

0

實驗switchover成功。

12、結論

Oracle DG是目前比較常見的資料高可用策略，由於操作方式的不同，我們有很多的安裝選擇的。綜合實際安裝條件和要求進行選擇，是我們需要掌握的要點。

使用RMAN備份集搭建Oracle Datagard Step by Step（三）

相關文章