<< Oracle高可用>>部分书面作业 - 第八课 Data Gaurd 故障分析和处理

1.模拟由于网络，监听器的原因导致归档无法传到standby上的情形，分别从相应的主备数据库的视图和alert文件定位故障的原因；当故障排除后，看Oracle是否能自动恢复归档的传递，

模拟：备库故意没起监听，其他正常

[oracle@dg2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2013 10:37:34

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@dg2 ~]$

主库做日志切换：

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME         OPEN_MODE  DATABASE_ROLE
--------- ------------------------------ ---------- ----------------
ORCLDB      WENDING             READ WRITE PRIMARY

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
     1        1          30   52428800         1 YES INACTIVE
       597872 02-AUG-13

     2        1          29   52428800         1 YES INACTIVE
       597871 02-AUG-13

     3        1          31   52428800         1 NO  CURRENT
       618179 11-AUG-13


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
     1        1          33   52428800         1 YES INACTIVE
       618516 11-AUG-13

     2        1          32   52428800         1 YES INACTIVE
       618503 11-AUG-13

     3        1          34   52428800         1 NO  CURRENT
       618520 11-AUG-13


SQL>

主库alter log：

...
Sun Aug 11 09:34:56 2013
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Sun Aug 11 09:34:56 2013
Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_arc0_29538.trc:
ORA-12541: TNS:no listener
FAL[server, ARC0]: Error 12541 creating remote archivelog file 'db_phystdby'
FAL[server, ARC0]: FAL archive failed, see trace file.
Sun Aug 11 09:34:56 2013
Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_arc0_29538.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Sun Aug 11 09:34:56 2013
ORACLE Instance WENDING - Archival Error. Archiver continuing.
Sun Aug 11 09:34:58 2013
Successfully onlined Undo Tablespace 1.
Sun Aug 11 09:34:58 2013
SMON: enabling tx recovery
Sun Aug 11 09:34:58 2013
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=29552
Sun Aug 11 09:35:03 2013
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Aug 11 09:35:03 2013
Completed: ALTER DATABASE OPEN
Sun Aug 11 09:40:28 2013
Thread 1 advanced to log sequence 32
  Current log# 2 seq# 32 mem# 0: /u01/oracle/oradata/ORCLDB/redo02.log
Sun Aug 11 09:40:28 2013
Shutting down archive processes
Sun Aug 11 09:40:33 2013
ARCH shutting down
ARC5: Archival stopped
Sun Aug 11 09:40:34 2013
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Sun Aug 11 09:40:34 2013
Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_arc2_29542.trc:
ORA-12541: TNS:no listener
FAL[server, ARC2]: Error 12541 creating remote archivelog file 'db_phystdby'
FAL[server, ARC2]: FAL archive failed, see trace file.
Sun Aug 11 09:40:34 2013
Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_arc2_29542.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Sun Aug 11 09:40:34 2013
ORACLE Instance WENDING - Archival Error. Archiver continuing.
Sun Aug 11 09:41:02 2013
Thread 1 advanced to log sequence 33
  Current log# 1 seq# 33 mem# 0: /u01/oracle/oradata/ORCLDB/redo01.log
Thread 1 cannot allocate new log, sequence 34
Checkpoint not complete
  Current log# 1 seq# 33 mem# 0: /u01/oracle/oradata/ORCLDB/redo01.log
Thread 1 advanced to log sequence 34
  Current log# 3 seq# 34 mem# 0: /u01/oracle/oradata/ORCLDB/redo03.log
Sun Aug 11 09:45:53 2013
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Sun Aug 11 09:45:53 2013
Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_arc2_29542.trc:
ORA-12541: TNS:no listener
PING[ARC2]: Heartbeat failed to connect to standby 'db_phystdby'. Error is 12541.
Sun Aug 11 09:50:53 2013
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Sun Aug 11 09:50:53 2013
Errors in file /u01/oracle/admin/ORCLDB/bdump/wending_arc2_29542.trc:
ORA-12541: TNS:no listener
PING[ARC2]: Heartbeat failed to connect to standby 'db_phystdby'. Error is 12541.

备库alter log未见异常。

模拟：故障排除，恢复备库监听：

[oracle@dg2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2013 10:49:08

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/oracle/product/10.2.0//bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/oracle/product/10.2.0/network/admin/listener.ora
Log messages written to /u01/oracle/product/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-AUG-2013 10:49:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@dg2 ~]$ exit
exit

SQL> alter system register;

System altered.

SQL> !
[oracle@dg2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2013 10:49:32

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-AUG-2013 10:49:10
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PHYSTDBY" has 1 instance(s).
  Instance "PHYSTDBY", status READY, has 1 handler(s) for thi