- 最后登录
- 2013-2-16
- 在线时间
- 19 小时
- 威望
- 29
- 金钱
- 268
- 注册时间
- 2012-3-13
- 阅读权限
- 10
- 帖子
- 32
- 精华
- 0
- 积分
- 29
- UID
- 292
|
2#
发表于 2012-5-15 11:54:24
回复 1# 的帖子
这个看主库的alert日志就可以看到了!
附上测试记录:
6.5 最高可用性模式下的故障模拟
确定当前data guard的数据保护模式处在最高可用性模式
DGMGRL> show configuration;
Configuration
Name: DG_YANG
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
dg53 - Primary database
dg54 - Logical standby database
Current status for "DG_YANG":
SUCCESS
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
---------------- -------------------- ----------------------- -------------------------------
ORCL10G READ WRITE PRIMARY MAXIMUM AVAILABILITY
在备库上配置防火墙,拒绝任何ip连接1521端口
[root@dg54 ~]# iptables -I INPUT -p tcp --dport 1521 -j REJECT
[root@dg54 ~]# iptables -L -n
Chain INPUT (policy ACCEPT)
target prot opt source destination
REJECT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:1521 reject-with icmp-port-unreachable
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
[oracle@dg53 ~]$ tnsping dg54
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 16-NOV-2011 16:58:14
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.54)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg54_DGMGRL.yang.com)))
TNS-12541: TNS:no listener
主库上的事务commit将会卡住一段时间,最终会成功提交
SQL> set timing on
SQL> update hr.employees set salary=300000 where employee_id=100;
1 row updated.
Elapsed: 00:00:00.63
SQL> commit;
Commit complete.
Elapsed: 00:02:04.28
主库上的日志信息如下:
[oracle@dg53 ~]$ tail -f /u01/app/oracle/admin/orcl10g/bdump/alert_dg53.log
Wed Nov 16 17:01:01 2011
ORA-16198: LGWR received timedout error from KSR
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Wed Nov 16 17:01:01 2011
Errors in file /u01/app/oracle/admin/orcl10g/bdump/dg53_lgwr_19252.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR: Network asynch I/O wait error 16198 log 3 service '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dg54.yang.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg54_XPT.yang.com)(INSTANCE_NAME=dg54)(SERVER=dedicated)))'
Wed Nov 16 17:01:01 2011
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Failed to archive log 3 thread 1 sequence 70 (16198)
Wed Nov 16 17:01:01 2011
LGWR: Closing remote archive destination LOG_ARCHIVE_DEST_2: '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dg54.yang.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg54_XPT.yang.com)(INSTANCE_NAME=dg54)(SERVER=dedicated)))' (error 16198)
(dg53)
Wed Nov 16 17:01:01 2011
Errors in file /u01/app/oracle/admin/orcl10g/bdump/dg53_lgwr_19252.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR: Error 16198 closing archivelog file '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dg54.yang.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg54_XPT.yang.com)(INSTANCE_NAME=dg54)(SERVER=dedicated)))'
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dg54.yang.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg54_XPT.yang.com)(INSTANCE_NAME=dg54)(SERVER=dedicated)))'
接下来主库事务提交的将不会被卡住
SQL> update hr.employees set salary=400000 where employee_id=100;
1 row updated.
Elapsed: 00:00:00.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
关闭防火墙后,主备间的sql apply服务会继续运行
[root@dg54 ~]# service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
SQL> update hr.employees set salary=24000 where employee_id=100;
1 row updated.
Elapsed: 00:00:00.01
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
[oracle@dg53 ~]$ tail -f /u01/app/oracle/admin/orcl10g/bdump/alert_dg53.log
LNSb started with pid=30, OS id=20840
Wed Nov 16 17:19:18 2011
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 72
LGWR: Standby redo logfile selected for thread 1 sequence 72 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 72
Current log# 2 seq# 72 mem# 0: +DATA/orcl10g/onlinelog/group_2.262.766837967
Current log# 2 seq# 72 mem# 1: +FRA/orcl10g/onlinelog/group_2.258.766837973
Wed Nov 16 17:19:18 2011
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Standby redo logfile selected for thread 1 sequence 71 for destination LOG_ARCHIVE_DEST_2
[oracle@dg54 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 16 17:23:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select salary from hr.employees where employee_id=100;
SALARY
----------
24000 |
|