0gR2最大保護模式DataGuard建立 (轉載)
jinqibingl發表於2013-04-06
10gR2最大保護模式DataGuard建立
一、設定主庫歸檔
設定主庫為force loggingSQL> alter database force logging;設定主庫為歸檔模式：SQL> archive log listSQL> shutdown immediateSQL> startup mountSQL> alter database archivelog;SQL> archive log list為主資料庫新增"備用聯機日誌檔案"，這裡要保證備日誌檔案與主庫聯機日誌檔案相同大小。新增備用日誌檔案是規則：備用日誌最少應該比redo log 多一個。推薦的備重做日誌數依賴於主資料庫上的執行緒數。
(每執行緒日誌檔案最大數目 + 1 ) * 執行緒數
alter database add standby logfile
group 4 ('/data/oradata/std_redo04a.log','/data/oradata/std_redo04b.log') size 50m,
group 5 ('/data/oradata/std_redo05a.log','/data/oradata/std_redo05b.log') size 50m,
group 6 ('/data/oradata/std_redo06a.log','/data/oradata/std_redo06b.log') size 50m,
group 7 ('/data/oradata/std_redo07a.log','/data/oradata/std_redo08b.dbf') size 50m;
二、修改主庫引數檔案
SQL> create pfile from spfile;
主庫initorcl.ora
======================
*.audit_file_dest='/opt/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/oracle/admin/orcl/bdump'
*.core_dump_dest='/opt/oracle/admin/orcl/cdump'
*.user_dump_dest='/opt/oracle/admin/orcl/udump'
*.compatible='10.2.0.1.0'
*.control_files='/data/oradata/control01.ctl','/data/oradata/control02.ctl','/data/oradata/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.job_queue_processes=10
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#修改的部分
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bforcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=orcl_%t_%s_%r.dbf
*.#以下為了角色切換設定
*.FAL_CLIENT = orcl
*.FAL_SERVER = bforcl
*.STANDBY_FILE_MANAGEMENT =AUTO
oracle@suse10~> mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
oracle@suse10~> sqlplus "/as sysdba"
SQL> create spfile from pfile;
三、配置主資料庫listener及tnsnames
主庫listener.ora
=======================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle/10.2.0.1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
)
)
)
tnsnames.ora
=============
RCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BFORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bforcl)
)
)
生成備用庫控制檔案
alter database create standby controlfile as '/tmp/standby_ctl01.ctl';
四、複製檔案到備機
oracle@suse10~> scp /data/oradata/* oracle@192.168.0.143:/data/oradata/
oracle@suse10 dbs> scp initorcl.ora oracle@192.168.0.143:` pwd `
oracle@suse10 admin> scp listener.ora tnsnames.ora oracle@192.168.0.143:` pwd `
oracle@suse10 tmp> scp standby_ctl01.ctl oracle@192.168.0.143:/home/oracle
生成備用庫密碼檔案(保證SYS密碼與主庫相同)
orapwd file=orapwbforcl.ora password=admin entries=10
多重備用庫控制檔案
cp standby_ctl01.ctl standby_ctl02.ctl
mkdir -p /data/{oradata,archvie}
mkdir -p /opt/oracle/admin/bforcl/{adump,bdump,cdump,pfile,udump}
五、修改備用庫引數檔案及listener
*.audit_file_dest='/opt/oracle/admin/bforcl/adump'
*.background_dump_dest='/opt/oracle/admin/bforcl/bdump'
*.core_dump_dest='/opt/oracle/admin/bforcl/cdump'
*.user_dump_dest='/opt/oracle/admin/bforcl/udump'
#修改的部分
*.control_file=('/data/oradata/standby_ctl01.ctl','/data/oradata/standby_ctl02.ctl')
*.DB_UNIQUE_NAME=bforcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=bforcl_%t_%s_%r.dbf
*.FAL_CLIENT = bforcl
*.FAL_SERVER = orcl
*.STANDBY_FILE_MANAGEMENT =AUTO #此引數保證主庫上對錶空間/資料檔案的操作會自動應用到備用庫上。
備庫listener.ora
=======================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = bforcl)
(ORACLE_HOME = /opt/oracle/10.2.0.1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
)
)
)
tnsnames.ora
=============
RCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BFORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bforcl)
)
)
六、測試主備機之間網路聯通性
tnsping orcl
tnsping bforcl
tnsping並不能保證可以互相登陸。
這裡要確保sqlplus 可以互相登陸對方資料庫。
七、開啟備用庫到mount狀態
檢視備用庫是否包含備用日誌。如果不包含備用日誌檔案，不能執行在"最大保護"或"最大可用性"模式。
SQL> select * from v$logfile;
SQL> select * from v$standby_log;
正式執行中v$standy_log中的某一個日誌檔案同步接收主庫LGWR程式傳送過來的重做條目。
備庫的standbyredo最好是刪除之後，重新做。
八、開啟主庫，修改主庫DataGuard保護模式
SQL> startup mount
SQL> select name,db_unique_name,protection_mode from v$database;
NAME DB_UNIQUE_NAME PROTECTION_MODE
----- --------------- --------------------
ORCL orcl MAXIMUM PERFORMANCE
SQL> alter database set standby database to maximize protection;
開啟主庫到OPEN狀態，監控alert日誌檔案，檢視是否配置成功。
切換主庫保護模式的語法：
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE }
九、開啟備用庫恢復程式
recover managed standby database diconnect from session;
執行上面這條語句，備用庫會在主庫日誌檔案切換(備用日誌檔案切換)歸檔後，使用歸檔檔案恢復資料庫。
recover managed standby database using current logfile disconnect from session;
alter database add standby logfile
group 4 ('/data/oradata/std_redo04a.log','/data/oradata/std_redo04b.log') size 50m,
group 5 ('/data/oradata/std_redo05a.log','/data/oradata/std_redo05b.log') size 50m,
group 6 ('/data/oradata/std_redo06a.log','/data/oradata/std_redo06b.log') size 50m,
group 7 ('/data/oradata/std_redo07a.log','/data/oradata/std_redo08b.dbf') size 50m;
二、修改主庫引數檔案
SQL> create pfile from spfile;
主庫initorcl.ora
======================
*.audit_file_dest='/opt/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/oracle/admin/orcl/bdump'
*.core_dump_dest='/opt/oracle/admin/orcl/cdump'
*.user_dump_dest='/opt/oracle/admin/orcl/udump'
*.compatible='10.2.0.1.0'
*.control_files='/data/oradata/control01.ctl','/data/oradata/control02.ctl','/data/oradata/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.job_queue_processes=10
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#修改的部分
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bforcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=orcl_%t_%s_%r.dbf
*.#以下為了角色切換設定
*.FAL_CLIENT = orcl
*.FAL_SERVER = bforcl
*.STANDBY_FILE_MANAGEMENT =AUTO
oracle@suse10~> mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
oracle@suse10~> sqlplus "/as sysdba"
SQL> create spfile from pfile;
三、配置主資料庫listener及tnsnames
主庫listener.ora
=======================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle/10.2.0.1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
)
)
)
tnsnames.ora
=============
RCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BFORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bforcl)
)
)
生成備用庫控制檔案
alter database create standby controlfile as '/tmp/standby_ctl01.ctl';
四、複製檔案到備機
oracle@suse10~> scp /data/oradata/* oracle@192.168.0.143:/data/oradata/
oracle@suse10 dbs> scp initorcl.ora oracle@192.168.0.143:` pwd `
oracle@suse10 admin> scp listener.ora tnsnames.ora oracle@192.168.0.143:` pwd `
oracle@suse10 tmp> scp standby_ctl01.ctl oracle@192.168.0.143:/home/oracle
生成備用庫密碼檔案(保證SYS密碼與主庫相同)
orapwd file=orapwbforcl.ora password=admin entries=10
多重備用庫控制檔案
cp standby_ctl01.ctl standby_ctl02.ctl
mkdir -p /data/{oradata,archvie}
mkdir -p /opt/oracle/admin/bforcl/{adump,bdump,cdump,pfile,udump}
五、修改備用庫引數檔案及listener
*.audit_file_dest='/opt/oracle/admin/bforcl/adump'
*.background_dump_dest='/opt/oracle/admin/bforcl/bdump'
*.core_dump_dest='/opt/oracle/admin/bforcl/cdump'
*.user_dump_dest='/opt/oracle/admin/bforcl/udump'
#修改的部分
*.control_file=('/data/oradata/standby_ctl01.ctl','/data/oradata/standby_ctl02.ctl')
*.DB_UNIQUE_NAME=bforcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=bforcl_%t_%s_%r.dbf
*.FAL_CLIENT = bforcl
*.FAL_SERVER = orcl
*.STANDBY_FILE_MANAGEMENT =AUTO #此引數保證主庫上對錶空間/資料檔案的操作會自動應用到備用庫上。
備庫listener.ora
=======================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = bforcl)
(ORACLE_HOME = /opt/oracle/10.2.0.1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
)
)
)
tnsnames.ora
=============
RCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BFORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bforcl)
)
)
六、測試主備機之間網路聯通性
tnsping orcl
tnsping bforcl
tnsping並不能保證可以互相登陸。
這裡要確保sqlplus 可以互相登陸對方資料庫。
七、開啟備用庫到mount狀態
檢視備用庫是否包含備用日誌。如果不包含備用日誌檔案，不能執行在"最大保護"或"最大可用性"模式。
SQL> select * from v$logfile;
SQL> select * from v$standby_log;
正式執行中v$standy_log中的某一個日誌檔案同步接收主庫LGWR程式傳送過來的重做條目。
備庫的standbyredo最好是刪除之後，重新做。
八、開啟主庫，修改主庫DataGuard保護模式
SQL> startup mount
SQL> select name,db_unique_name,protection_mode from v$database;
NAME DB_UNIQUE_NAME PROTECTION_MODE
----- --------------- --------------------
ORCL orcl MAXIMUM PERFORMANCE
SQL> alter database set standby database to maximize protection;
開啟主庫到OPEN狀態，監控alert日誌檔案，檢視是否配置成功。
切換主庫保護模式的語法：
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE }
九、開啟備用庫恢復程式
recover managed standby database diconnect from session;
執行上面這條語句，備用庫會在主庫日誌檔案切換(備用日誌檔案切換)歸檔後，使用歸檔檔案恢復資料庫。
recover managed standby database using current logfile disconnect from session;
（如果要使用最大保護模式，那麼要用這個語句，開啟redo實時應用）
這條語句與上面不同的是，備用日誌檔案切換，生成歸檔日誌前，先恢復資料庫。
recover managed standby database finish;
這條語句是在做切換時，儘量多的保護資料。從備用日誌檔案中恢復資料
recover managed standby database cancel;
取消備用庫自動恢復
可以在使用上面兩條語句時，檢視v$managed_standby的不同。
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
RFS IDLE
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 APPLYING_LOG
RFS IDLE
RFS IDLE
RFS IDLE
在恢復過程中，可以檢視v$standby_log,此檢視中會有日誌檔案與生產機的當前日誌檔案是同步的。
十、需要注意
最大保護模式下，至少要有一個備用庫的設定滿足LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM'並且，主庫設定為MAXIMUM PROTECTION模式。
最大保護模式下，備用庫還是會等待備用庫切換日誌後歸檔時或歸檔前才會恢復備用系統。而不是主資料庫的操作適時反映在備用庫。適時的僅僅是日誌檔案合而已。這一點要注意。
在主備庫切換的時候可以執行recover managed standby database finish應用適時同步過來的日誌資訊，這樣可以最大限度的保證資料。
測試：
1.在主庫開啟情況下關閉備庫：
oracle 10g R2中，執行在LGWR最大保護模式下的備庫，主庫沒有關閉的情況下不允許關閉備庫。
也就是說，每當關閉資料庫時，必須先關閉主庫才能關閉備用庫。
開啟資料庫情況剛好相反。備庫沒有MOUNT而直接開啟主庫是不允許的。在資料庫開啟階段會報錯
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
alter中錯誤資訊見下："錯誤"
開啟資料庫必須是先mount備用庫，才能開啟主資料庫。
SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
alter日誌中資訊：
Completed: ALTER DATABASE RECOVER managed standby database cancel
Wed Jul 4 06:47:40 2007
Attempt to shut down Standby Database
Standby Database operating in NO DATA LOSS mode
Detected primary database alive, shutdown primary first, shutdown aborted
2.網路中斷
測試斷開備用機網路
$ifdown eth0
在主資料庫做操作
SQL> create table t (id number);
操作一直處於掛起狀態。
檢視主資料庫alter日誌顯示如下錯誤，
如果網路斷開，主庫LGWR程式會一直掛起一直探測是否可以傳輸資料到備用機，直到備用機網路再次連線。
主資料庫alter錯誤資訊：
Thu Jul 5 14:52:22 2007
ORA-16198: LGWR received timedout error from KSR
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
ORA-16198: LGWR received timedout error from KSR
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LNSb started with pid=16, OS id=12830
Error 12560 received logging on to the standby
Thu Jul 5 14:52:52 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12834
Error 12560 received logging on to the standby
Thu Jul 5 14:53:17 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12836
Error 12560 received logging on to the standby
Thu Jul 5 14:53:42 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12838
Error 12560 received logging on to the standby
......
Thu Jul 5 14:56:13 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12863
Thu Jul 5 14:56:33 2007
LGWR: RFS network connection re-established at host 'bforcl'
LGWR: RFS destination opened for reconnect at host 'bforcl'
附：
開啟資料庫時的錯誤資訊：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
主庫alter日誌錯誤：
alter database open
Wed Jul 4 04:07:55 2007
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=7898
Wed Jul 4 04:07:57 2007
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in MAXIMUM PROTECTION mode
Wed Jul 4 04:07:57 2007
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNSb started with pid=16, OS id=7902
ARC1 started with pid=15, OS id=7900
Wed Jul 4 04:08:05 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:08:05 2007
LGWR: Error 16086 verifying archivelog destination LOG_ARCHIVE_DEST_2
Wed Jul 4 04:08:05 2007
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LGWR: Continuing...
LGWR: Minimum of 1 applicable standby database required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR: terminating instance due to error 16072
Instance terminated by LGWR, pid = 7882
備庫alter錯誤：
Primary database is in MAXIMUM PROTECTION mode
Changing standby controlfile to MAXIMUM PROTECTION mode
RFS[4]: No standby redo logfiles selected (reason:1)
Wed Jul 4 04:02:58 2007
Errors in file /opt/oracle/admin/bforcl/udump/bforcl_rfs_9362.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:09:51 2007
這條語句與上面不同的是，備用日誌檔案切換，生成歸檔日誌前，先恢復資料庫。
recover managed standby database finish;
這條語句是在做切換時，儘量多的保護資料。從備用日誌檔案中恢復資料
recover managed standby database cancel;
取消備用庫自動恢復
可以在使用上面兩條語句時，檢視v$managed_standby的不同。
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
RFS IDLE
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 APPLYING_LOG
RFS IDLE
RFS IDLE
RFS IDLE
在恢復過程中，可以檢視v$standby_log,此檢視中會有日誌檔案與生產機的當前日誌檔案是同步的。
十、需要注意
最大保護模式下，至少要有一個備用庫的設定滿足LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM'並且，主庫設定為MAXIMUM PROTECTION模式。
最大保護模式下，備用庫還是會等待備用庫切換日誌後歸檔時或歸檔前才會恢復備用系統。而不是主資料庫的操作適時反映在備用庫。適時的僅僅是日誌檔案合而已。這一點要注意。
在主備庫切換的時候可以執行recover managed standby database finish應用適時同步過來的日誌資訊，這樣可以最大限度的保證資料。
測試：
1.在主庫開啟情況下關閉備庫：
oracle 10g R2中，執行在LGWR最大保護模式下的備庫，主庫沒有關閉的情況下不允許關閉備庫。
也就是說，每當關閉資料庫時，必須先關閉主庫才能關閉備用庫。
開啟資料庫情況剛好相反。備庫沒有MOUNT而直接開啟主庫是不允許的。在資料庫開啟階段會報錯
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
alter中錯誤資訊見下："錯誤"
開啟資料庫必須是先mount備用庫，才能開啟主資料庫。
SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
alter日誌中資訊：
Completed: ALTER DATABASE RECOVER managed standby database cancel
Wed Jul 4 06:47:40 2007
Attempt to shut down Standby Database
Standby Database operating in NO DATA LOSS mode
Detected primary database alive, shutdown primary first, shutdown aborted
2.網路中斷
測試斷開備用機網路
$ifdown eth0
在主資料庫做操作
SQL> create table t (id number);
操作一直處於掛起狀態。
檢視主資料庫alter日誌顯示如下錯誤，
如果網路斷開，主庫LGWR程式會一直掛起一直探測是否可以傳輸資料到備用機，直到備用機網路再次連線。
主資料庫alter錯誤資訊：
Thu Jul 5 14:52:22 2007
ORA-16198: LGWR received timedout error from KSR
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
ORA-16198: LGWR received timedout error from KSR
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LNSb started with pid=16, OS id=12830
Error 12560 received logging on to the standby
Thu Jul 5 14:52:52 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12834
Error 12560 received logging on to the standby
Thu Jul 5 14:53:17 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12836
Error 12560 received logging on to the standby
Thu Jul 5 14:53:42 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12838
Error 12560 received logging on to the standby
......
Thu Jul 5 14:56:13 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12863
Thu Jul 5 14:56:33 2007
LGWR: RFS network connection re-established at host 'bforcl'
LGWR: RFS destination opened for reconnect at host 'bforcl'
附：
開啟資料庫時的錯誤資訊：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
主庫alter日誌錯誤：
alter database open
Wed Jul 4 04:07:55 2007
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=7898
Wed Jul 4 04:07:57 2007
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in MAXIMUM PROTECTION mode
Wed Jul 4 04:07:57 2007
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNSb started with pid=16, OS id=7902
ARC1 started with pid=15, OS id=7900
Wed Jul 4 04:08:05 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:08:05 2007
LGWR: Error 16086 verifying archivelog destination LOG_ARCHIVE_DEST_2
Wed Jul 4 04:08:05 2007
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LGWR: Continuing...
LGWR: Minimum of 1 applicable standby database required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR: terminating instance due to error 16072
Instance terminated by LGWR, pid = 7882
備庫alter錯誤：
Primary database is in MAXIMUM PROTECTION mode
Changing standby controlfile to MAXIMUM PROTECTION mode
RFS[4]: No standby redo logfiles selected (reason:1)
Wed Jul 4 04:02:58 2007
Errors in file /opt/oracle/admin/bforcl/udump/bforcl_rfs_9362.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:09:51 2007
轉載自：http://zwfha.blog.163.com/blog/static/2826824520076551552244/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9606200/viewspace-757853/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 10gR2最大保護模式DataGuard建立2007-12-25模式
- 建立DATAGUARD最大保護模式-測試手記2013-04-08模式
- dataguard三種保護模式2014-11-06模式
- DataGuard切換保護模式2016-06-14模式
- 【DataGuard】不能沒有你，我的Standby ——Oracle DataGuard最大保護模式 故障實驗2014-11-27Oracle模式
- 【DataGuard】Oracle DataGuard 資料保護模式切換2014-08-09Oracle模式
- 【DataGuard】Oracle Dataguard三種保護模式特點總結2017-01-12Oracle模式
- 11g dataguard 型別、保護模式、服務2017-03-10型別模式
- 聊聊Dataguard的三種保護模式實驗（上）2015-11-03模式
- 聊聊Dataguard的三種保護模式實驗（下）2015-11-10模式
- Windows的保護模式 (轉)2007-12-07Windows模式
- 最大效能保護，最大資料保護，最大可用性，LGWR, ARCH2009-03-06大資料
- 保護模式知識（三） (轉)2007-12-14模式
- 保護模式2024-03-11模式
- 建立 oracle data gurd 以及三種保護模式2014-09-02Oracle模式
- ８０３８６保護模式簡介(轉)2007-08-14模式
- 【DataGuard】調整Data Guard資料保護模式詳細步驟2011-10-12模式
- oracle實驗記錄 (oracle 10G dataguard(6)保護模式)2009-11-16Oracle模式
- 最大效能模式DATAGUARD 搭建 及SWITCH2009-12-17模式
- 10g DG保護模式的切換：從最大效能到最大可用2019-06-21模式
- 探索Oracle11gR2 之 DataGuard_03 三種保護模式2013-08-15Oracle模式
- DG的保護模式2016-03-25模式
- 真實模式和保護模式2017-03-14模式
- 保護模式下的特權級檢查 (轉)2007-12-14模式
- 保護性暫停模式2021-06-08模式
- 保護模式：段機制2024-03-12模式
- 【DataGuard】使用Grid Control調整Oracle物理Data Guard資料保護模式2010-08-12Oracle模式
- 一步一步學DataGuard(22)Standby之選擇資料保護模式2008-04-04模式
- 在桌面上建立螢幕保護程式圖示(轉)2007-08-12
- Oracle DataGuard 常用維護命令參考手冊 [轉載記錄]2009-03-05Oracle
- 保護模式篇——PAE分頁2021-10-22模式
- 11g 最大保護模式 standby database網路故障導致主庫當機2015-06-12模式Database
- DATAGUARD監控，保護和自動修復最佳實踐2018-06-04
- 保護模式篇——總結與提升2021-10-31模式
- 【DG】DG的3種保護模式2017-06-24模式
- 引數配置 -- 最大效能模式 dataguard 不影響Production DB .2008-04-02模式
- 圖解CPU的真實模式與保護模式2024-07-04圖解模式
- dataguard型別轉換與模式轉化2017-03-10型別模式