Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处置方法

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby处理方法一．背景说明前段时

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法

一．背景说明

前段时间一朋友在生产库上误操作，本来他是打算重启一下DG环境，结果在备库命令执行错误。

本应该执行

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

结果朋友执行成了如下命令：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

中断了DG主备库的通信环境，这个finish是用来做Failover时用的。当时让朋友在主库重新生成了一份standby controlfiles，然后copy到备库，在按正常模式启动就可以了。

因为数据库识别主备库就是通过控制文件来的，所以理论上，只需要重新生成一份standby 控制文件就可以了。后来朋友测试了一下，正常的拉起来了。

今天看到了当时的记录，就顺便模拟一下整个操作，顺便练练手。

二．演示过程

2.1 DG 环境说明

OS： Oracle Linux6.3

DB： 11.2.0.3

SQL> select * from v$version;

BANNER

--------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 -Production

NLSRTL Version 11.2.0.3.0 - Production

主库：

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL>

SQL> set pagesize 200

SQL> select sequence#,applied fromv$archived_log order by sequence# desc;

SEQUENCE# APPLIED

---------- ---------

14 YES

14 NO

13 YES

13 NO

12 NO

12 YES

11 YES

11 NO

10 NO

10 YES

9 YES

9 NO

8 NO

8 YES

7 YES

7 NO

6 YES

6 NO

5 NO

4 NO

20 rows selected.

备库：

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

SQL>

SQL> select sequence#,applied fromv$archived_log order by sequence# desc;

SEQUENCE# APPLIED

---------- ---------

14 YES

13 YES

12 YES

11 YES

10 YES

9 YES

8 YES

7 YES

6 YES

9 rows selected.

2.2 模拟故障

在备库执行如下命令：

SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE FINISH;

Database altered.

2.3 查看主库 alert log

[oracle@dg1 trace]$ pwd

/u01/app/oracle/diag/rdbms/dave_pd/dave/trace

[oracle@dg1 trace]$ tail -30 alert_dave.log

Thread 1 advanced to log sequence 14 (LGWRswitch)

Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/dave/redo02.log

Fri Mar 29 03:30:12 2013

Archived Log entry 17 added for thread 1sequence 13 ID 0x3312f7c4 dest 1:

Fri Mar 29 03:30:13 2013

LNS: Standby redo logfile selected forthread 1 sequence 14 for destination LOG_ARCHIVE_DEST_2

Fri Mar 29 03:43:10 2013

Time drift detected. Please check VKTMtrace file for more details.

Fri Mar 29 04:45:31 2013

Time drift detected. Please check VKTMtrace file for more details.

Fri Mar 29 06:28:35 2013

Time drift detected. Please check VKTMtrace file for more details.

Fri Mar 29 07:08:14 2013

Thread 1 advanced to log sequence 15 (LGWRswitch)

Current log# 3 seq# 15 mem# 0: /u01/app/oracle/oradata/dave/redo03.log

Fri Mar 29 07:08:16 2013

Archived Log entry 20 added for thread 1sequence 14 ID 0x3312f7c4 dest 1:

Fri Mar 29 07:08:17 2013

LNS: Standby redo logfile selected forthread 1 sequence 15 for destination LOG_ARCHIVE_DEST_2

Fri Mar 29 07:34:48 2013

Time drift detected. Please check VKTMtrace file for more details.

Fri Mar 29 07:48:55 2013

LNS: Attempting destinationLOG_ARCHIVE_DEST_2 network reconnect (3135)

LNS: Destination LOG_ARCHIVE_DEST_2 networkreconnect abandoned

Error 3135 for archive log file 3 to'dave_st'

Errors in file/u01/app/oracle/diag/rdbms/dave_pd/dave/trace/dave_nsa2_3181.trc:

ORA-03135: connection lost contact

LNS: Failed to archive log 3 thread 1sequence 15 (3135)

Fri Mar 29 07:51:45 2013

PING[ARC1]: Heartbeatfailed to connect to standby 'dave_st'. Error is 16143.

因为我们在备库执行的Finish命令，导致心跳中断了。

2.4 查看备库alert log

[oracle@dg2 trace]$ pwd

/u01/app/oracle/diag/rdbms/dave_st/dave/trace

[oracle@dg2 trace]$ tail -20 alert_dave.log

Terminal Recovery: thread 1 seq# 15 redorequired

Terminal Recovery:

Recovery of Online Redo Log: Thread 1 Group5 Seq 15 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/dave/stdbyredo02.log

Identified End-Of-Redo (failover) forthread 1 sequence 15 at SCN 0xffff.ffffffff

Incomplete Recovery applied until change1082890 time 03/29/2013 07:48:53

MRP0: Media Recovery Complete (dave)

Terminal Recovery: successful completion

Fri Mar 29 07:48:49 2013

ARCH: Archival stopped, error occurred.Will continue retrying

ORACLE Instance dave - Archival Error

Forcing ARSCN to IRSCN for TR 0:1082890

Attempt to set limbo arscn 0:1082890 irscn0:1082890

Resetting standby activation ID 856881092(0x3312f7c4)

ORA-16014: log 5 sequence# 15 not archived,no available destinations

ORA-00312: online log 5 thread 1:'/u01/app/oracle/oradata/dave/stdbyredo02.log'

MRP0: Background Media Recovery processshutdown (dave)

Fri Mar 29 07:48:50 2013

Terminal Recovery: completion detected(dave)

Completed: ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE FINISH

[oracle@dg2 trace]$

2.5 在主库切换归档

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select sequence#,applied fromv$archived_log order by sequence# desc;

SEQUENCE# APPLIED

---------- ---------

16 NO

15 NO

14 NO

14 YES

13 YES

13 NO

12 YES

12 NO

11 NO

11 YES

10 NO

10 YES

9 YES

9 NO

8 YES

8 NO

7 YES

7 NO

6 NO

6 YES

5 NO

4 NO

22 rows selected.

SQL>

2.6 再次查看主备库日志

主库日志：

Fri Mar 29 07:52:46 2013

PING[ARC1]: Heartbeat failed to connect tostandby 'dave_st'. Error is 16143.

Fri Mar 29 07:53:47 2013

PING[ARC1]: Heartbeat failed to connect tostandby 'dave_st'. Error is 16143.

Fri Mar 29 07:53:49 2013

Thread 1 advanced to log sequence 16 (LGWRswitch)

Current log# 1 seq# 16 mem# 0: /u01/app/oracle/oradata/dave/redo01.log

Fri Mar 29 07:53:49 2013

Archived Log entry 21 added for thread 1sequence 15 ID 0x3312f7c4 dest 1:

Fri Mar 29 07:53:50 2013

FAL[server, ARC2]: Error 16143 creatingremote archivelog file 'dave_st'

FAL[server, ARC2]: FAL archive failed, seetrace file.

ARCH: FAL archive failed. Archivercontinuing

ORACLE Instance dave - Archival Error.Archiver continuing.

Thread 1 advanced to log sequence 17 (LGWRswitch)

Current log# 2 seq# 17 mem# 0: /u01/app/oracle/oradata/dave/redo02.log

Fri Mar 29 07:53:57 2013

Archived Log entry 22 added for thread 1sequence 16 ID 0x3312f7c4 dest 1:

备库日志：

Fri Mar 29 07:48:50 2013

Terminal Recovery: completion detected(dave)

Completed: ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE FINISH

Fri Mar 29 07:51:34 2013

RFS[5]: Assigned to RFS process 9336

RFS[5]: No connections allowed during/afterterminal recovery.

Fri Mar 29 07:52:35 2013

RFS[6]: Assigned to RFS process 9340

RFS[6]: No connections allowed during/afterterminal recovery.

Fri Mar 29 07:53:36 2013

RFS[7]: Assigned to RFS process 9343

RFS[7]: No connections allowed during/afterterminal recovery.

Fri Mar 29 07:53:39 2013

RFS[8]: Assigned to RFS process 9345

RFS[8]: No connectionsallowed during/after terminal recovery.

2.7 在主库重建standby control file

先在备库查看一下控制文件名称，等会创建完后直接覆盖过去：

SQL> show parameter control

NAME TYPE VALUE

----------------------------------------------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/dave/c

ontrol01.ctl, /u01/app/oracle/

fast_recovery_area/dave/contro

l02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

主库创建standby controlfile：

SQL> alter database create standbycontrolfile as '/u01/control01.ctl';

Database altered.

copy到备库的目录，在覆盖原来的控制文件：

--先关闭备库：

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

--copy并覆盖：

[oracle@dg2 trace]$ cd/u01/app/oracle/oradata/dave/

[oracle@dg2 dave]$ ls

control01.ctl stdbyredo02.log stdbyredo04.log system01.dbf undotbs01.dbf

stdbyredo01.log stdbyredo03.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@dg2 dave]$ mv control01.ctlcontrol01.ctl.bak

[oracle@dg2 dave]$ ls

control01.ctl.bak stdbyredo02.log stdbyredo04.log system01.dbf undotbs01.dbf

stdbyredo01.log stdbyredo03.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@dg2 dave]$ scp192.168.1.20:/u01/control01.ctl 192.168.1.30:/u01/app/oracle/oradata/dave/

The authenticity of host '192.168.1.20(192.168.1.20)' can't be established.

RSA key fingerprint is0d:6a:5f:78:53:a0:bf:54:a8:e3:7e:67:81:06:8d:75.

Are you sure you want to continueconnecting (yes/no)? yes

Warning: Permanently added '192.168.1.20'(RSA) to the list of known hosts.

oracle@192.168.1.20's password:

oracle@192.168.1.30's password:

control01.ctl 100% 9520KB 865.5KB/s 00:11

Connection to 192.168.1.20 closed.

[oracle@dg2 dave]$ ls

control01.ctl stdbyredo01.log stdbyredo03.log sysaux01.dbf temp01.dbf users01.dbf

control01.ctl.bak stdbyredo02.log stdbyredo04.log system01.dbf undotbs01.dbf

[oracle@dg2 dave]$

[oracle@dg2 archivelog]$ cd/u01/app/oracle/fast_recovery_area/dave/

[oracle@dg2 dave]$ ls

control02.ctl

[oracle@dg2 dave]$ mv control02.ctlcontrol02.ctl.bak

[oracle@dg2 dave]$ ls

control02.ctl.bak

[oracle@dg2 dave]$

[oracle@dg2 dave]$ cp control01.ctl/u01/app/oracle/fast_recovery_area/dave/control02.ctl

2.8 在正常拉起备库

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 814227456 bytes

Fixed Size 2232760 bytes

Variable Size 478154312 bytes

Database Buffers 331350016 bytes

Redo Buffers 2490368 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managedstandby database disconnect from session;

Database altered.

SQL>

2.9 查看主备库日志

主库日志：

Fri Mar 29 08:00:51 2013

PING[ARC1]: Heartbeat failed to connect tostandby 'dave_st'. Error is 16143.

Fri Mar 29 08:01:52 2013

Error 1034 received logging on to thestandby