控制主备库日志同步

搭建好备库后,有时可能出于维护等原因,需要暂时停止主备库间日志传输,否则告警日志中会不断报错。比如我在DG环境中,只开主库进行测试,这时主库会不断查看备库是否开启,如果找不到备库就会报错:

 

Fatal NI connect error 12543, connecting to:   
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.151)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=enmostd)(CID=(PROGRAM=oracle)(HOST=Enmopri)(USER=oracle))))
 
VERSION INFORMATION:   
 TNS for Linux: Version 11.2.0.2.0 - Production    
 TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production    
 Time: 01-JUN-2011 07:49:09    
 Tracing not turned on.    
 Tns error struct:    
 ns main err code: 12543    
 
TNS-12543: TNS:destination host unreachable    
 ns secondary err code: 12560    
 nt main err code: 513    
 
TNS-00513: Destination host unreachable    
 nt secondary err code: 113    
 nt OS err code: 0

 

可手工控制主备库间日志传输起停:

SQL> show parameter log_archive_dest
 
NAME                                 TYPE        VALUE   
------------------------------------ ----------- ------------------------------    
log_archive_dest                     string    
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_    
 DEST LGWR valid_for=(ALL_LOGFI    
 LES, ALL_ROLES) db_unique_name    
 =enmodb    
log_archive_dest_2                   string      service=ENMO_STD valid_for=(on    
 line_logfiles,primary_role) db    
 _unique_name=enmostd
 
SQL> show parameter log_archive_dest_state
 
NAME                                 TYPE        VALUE   
------------------------------------ ----------- ------------------------------    
log_archive_dest_state_1             string      enable    
log_archive_dest_state_2             string      enable
 
SQL> alter system set log_archive_dest_state_2='defer';
 
System altered.
 
SQL> show parameter log_archive_dest_state
 
NAME                                 TYPE        VALUE   
------------------------------------ ----------- ------------------------------    
log_archive_dest_state_1             string      enable    
log_archive_dest_state_2             string      defer

 

此时主备库暂停日志同步,告警提示:

Wed Jun 01 08:05:00 2011   
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST    
ALTER SYSTEM SET log_archive_dest_state_2='defer' SCOPE=BOTH;

可以看出默认情况下,default scope=both

如果想开启日志传输,可使用如下命令:

 

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
 
System altered.

 

日志同步enable后,主库告警中接着出现大量报错:

 

Wed Jun 01 08:09:10 2011   
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST    
ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;    
Wed Jun 01 08:09:12 2011    
Thread 1 advanced to log sequence 147 (LGWR switch)    
 Current log# 3 seq# 147 mem# 0: /u01/app/oracle/oradata/enmodb/redo03.log    
Wed Jun 01 08:09:12 2011    
******************************************************************    
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2    
******************************************************************    
Wed Jun 01 08:09:12 2011    
ARC3: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
 
***********************************************************************
 
Fatal NI connect error 12543, connecting to:   
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.151)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=enmostd)(CID=(PROGRAM=oracle)(HOST=Enmopri)(USER=oracle))))
 
VERSION INFORMATION:   
 TNS for Linux: Version 11.2.0.2.0 - Production    
 TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production    
 Time: 01-JUN-2011 08:09:13    
 Tracing not turned on.    
 Tns error struct:    
 ns main err code: 12543    
 
TNS-12543: TNS:destination host unreachable    
 ns secondary err code: 12560
 
Error 12543 received logging on to the standby   
Error 12543 received logging on to the standby    
ARC3: Error 12543 Creating archive log file to 'ENMO_STD'    
Error 12543 for archive log file 3 to 'ENMO_STD'    
Errors in file /u01/app/oracle/diag/rdbms/enmodb/enmodb/trace/enmodb_nsa2_3563.trc:    
ORA-12543: TNS:destination host unreachable

 

注意:命令如果为如下格式(enable或是defer前后无单引号,可能会导致value中的enable变成大写,虽效果一致但可能会造成隐患,建议加单引号,按标准书写规范)

 

SQL> alter system set log_archive_dest_state_2=enable scope=both;
 
System altered.
 
SQL>  show parameter log_archive_dest_state_2
 
NAME                                 TYPE        VALUE   
------------------------------------ ----------- ------------------------------    
log_archive_dest_state_2             string      ENABLE    
log_archive_dest_state_20            string      enable

 

参考:

http://www.eygle.com/MT/mt-search.cgi?blog_id=4&tag=Dataguard&limit=20

http://blog.csdn.net/tirgerfly/archive/2005/06/07/389478.aspx

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress