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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 保護模式2024-03-11模式
- 10g DG保護模式的切換：從最大效能到最大可用2019-06-21模式
- DATAGUARD監控，保護和自動修復最佳實踐2018-06-04
- 保護模式：段機制2024-03-12模式
- 保護性暫停模式2021-06-08模式
- 保護模式篇——PAE分頁2021-10-22模式
- 保護模式篇——總結與提升2021-10-31模式
- 圖解CPU的真實模式與保護模式2024-07-04圖解模式
- 轉載 利用SEH異常處理機制繞過GS保護2018-04-19
- Linux從頭學08：Linux 是如何保護核心程式碼的？【從真實模式到保護模式】2021-08-25Linux模式
- 6 Oracle Data Guard Protection Modes 保護模式2020-03-23Oracle模式
- 過載保護原理與實戰2020-12-12
- x64 番外篇——保護模式相關2022-04-02模式
- Oracle-DG最大保護模式下,dg備庫出現問題對主庫有什麼影響？2021-04-23Oracle模式
- RAC環境下建立物理DATAGUARD(1)2019-05-19
- RAC環境下建立物理DATAGUARD(2)2019-05-01
- 使用Broker管理Data Guard——停用、改保護模式等2023-01-12模式
- win10桌面眼睛保護模式怎麼開啟_win10設定電腦眼睛保護模式步驟2020-05-24Win10模式
- 微服務過載保護原理與實戰2020-12-16微服務
- 10G DG最大可用模式下備庫發生故障時主庫保護級別的變化2019-04-29模式
- EOCR-SE2電動機保護器失速與堵轉保護功能的使用方法2020-10-23
- 【DATAGUARD】Oracle21c Dataguard建立注意事項及主要引數介紹2021-10-21Oracle
- Oracle Dataguard故障轉移(failover)操作2020-07-28OracleAI
- redis配置檔案中的保護模式protected-mode2022-06-30Redis模式
- 保護創新、促進仿製：對中國建立藥品專利保護體系的政策建議2020-07-06
- 保護期限2024-03-20
- AlDente 2.1 Alpha (限制最大充電量保護電池)支援m12021-02-22
- 滴水中級班保護模式階段測試作業2020-11-08模式
- 【等級保護】等級保護共分為幾級？保護物件是指什麼？2022-03-03物件
- Oracle的過載保護-資料庫資源限制2021-12-13Oracle資料庫
- gitlab分支保護2024-11-30Gitlab
- Java併發(二十三)----同步模式之保護性暫停2024-02-04Java模式
- 轉載-Java設計模式之Decorator2020-04-06Java設計模式
- 精密進近復飛轉彎保護區的繪製（三）2021-02-18
- 如何在無頭模式下下載和最大化視窗？2020-12-29模式
- 森林-環境保護2024-07-16
- 保護網站安全2022-12-17網站
- 資料庫保護2024-03-25資料庫
- 敏感資訊保護2020-07-09