將DG調整為最大保護模式，報ORA-03113: end-of-file on communication channe

在將Data Guard調整為最大保護模式MAXIMUM PROTECTION ）的時候，遇到了以下的問題ORA-03113: end-of-file on communication channel。

執行過程如下：

 

SQL> alter database set standby database to maximize PROTECTION;alter database set standby database to maximize PROTECTION*ERROR at line 1:ORA-01126: database must be mounted in this instance and not open in any instanceSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  390070272 bytesFixed Size                  2021024 bytesVariable Size             142608736 bytesDatabase Buffers          243269632 bytesRedo Buffers                2170880 bytesDatabase mounted.SQL> alter database set standby database to maximize PROTECTION;Database altered.SQL> select PROTECTION_MODE,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;PROTECTION_MODE      OPEN_MODE  DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------- ------------------------------ ---------------- --------------------MAXIMUM PROTECTION   MOUNTED    WENDING                        PRIMARY          TO STANDBYSQL> alter database open;alter database open*ERROR at line 1:ORA-03113: end-of-file on communication channel

分析日誌：

[oracle@dg1 bdump]$ tail -10f alert_WENDING.logLGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWRLGWR: Minimum of 1 synchronous standby database requiredSat Jan  4 23:05:16 2014Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_lgwr_10086.trc:ORA-16072: a minimum of one standby database destination is requiredSat Jan  4 23:05:16 2014Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_lgwr_10086.trc:ORA-16072: a minimum of one standby database destination is requiredLGWR: terminating instance due to error 16072Instance terminated by LGWR, pid = 10086

 

其實，第一步處理過程，應該查詢一下log_archive_dest參數，檢查一下同步的方式以及AFFIRM 是否設定，其次就是standy log日誌是否添加，這一切建立完成以後再進行日誌檢查。

可能在第一步檢查完成之後就已經可以解決問題。

對了，還有一個要開啟FLASHBACK_ON ；

分別將FLASHBACK_ON開啟。SQL> select FLASHBACK_ON from v$database;FLASHBACK_ON------------------NOSQL> alter database flashback on;Database altered.

添加standy redolog

[oracle@dg2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 5 01:00:35 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter database add standby logfile group 4 ('/u01/oracle/oradata/ORCLDB/stdby_redo04.log') size 50M;Database altered.SQL> alter database add standby logfile group 5 ('/u01/oracle/oradata/ORCLDB/stdby_redo05.log') size 50M;Database altered.SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/ORCLDB/stdby_redo06.log') size 50M;Database altered.

 

 

[oracle@dg1 bdump]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 5 01:10:32 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter database set standby database to maximize PROTECTION;alter database set standby database to maximize PROTECTION*ERROR at line 1:ORA-01126: database must be mounted in this instance and not open in any instanceSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  390070272 bytesFixed Size                  2021024 bytesVariable Size             142608736 bytesDatabase Buffers          243269632 bytesRedo Buffers                2170880 bytesDatabase mounted.SQL> alter database set standby database to maximize PROTECTION;Database altered.SQL> select PROTECTION_MODE,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;PROTECTION_MODE      OPEN_MODE  DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS-------------------- ---------- ------------------------------ ---------------- --------------------MAXIMUM PROTECTION   MOUNTED    WENDING                        PRIMARY          TO STANDBY

 

設定參數，開啟資料庫。

SQL>  alter system set log_archive_dest_2 = 'SERVICE=db_phystdby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PHYSTDBY AFFIRM ';System altered.SQL> alter database open;Database altered.