风情 2013-03-31
前段时间一朋友在生产库上误操作,本来他是打算重启一下DG环境,结果在备库命令执行错误。
本应该执行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
结果朋友执行成了如下命令:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
中断了DG主备库的通信环境,这个finish是用来做Failover时用的。 当时让朋友在主库重新生成了一份standby controlfiles,然后copy到备库,在按正常模式启动就可以了。
因为数据库识别主备库就是通过控制文件来的,所以理论上,只需要重新生成一份standby 控制文件就可以了。 后来朋友测试了一下,正常的拉起来了。
今天看到了当时的记录,就顺便模拟一下整个操作,顺便练练手。
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.
在备库执行如下命令:
SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE FINISH;
Database altered.
[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命令,导致心跳中断了。