Troubleshooting | 十字螺丝钉的DBA日记

2016年12月27日十字螺丝钉 troubleshooting Leave a comment

ORA-16038/ORA-00742: Log read detects lost write in thread %d sequence %d block %d

朋友遇到Oracle数据库的 REDO LOG 写丢失，造成数据库，且无法启动。日志如下：redo log 5无法归档，并且侦测到“写丢失”。最终导致Oracle宕机。

Errors in file /u01/app/oracle/diag/rdbmsdbcontrol/GZDBCONTROL/trace/GZDBCONTROL_ora_32198.trc:
ORA-16038: log 5 sequence# 23 cannot be archived
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 5 thread 1: '/u01/app/oracle/fast_recovery_area/GZDBCONTROL/onlinelog/o1_mf_5_d61ctj3y_.log'
USER (ospid: 32198): terminating the instance due to error 16038
System state dump requested by (instance=1, osid=32198), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbmsdbcontrol/GZDBCONTROL/trace/GZDBCONTROL_diag_32167_20161227110109.trc
Dumping diagnostic data in directory=[cdmp_20161227110109], requested by (instance=1, osid=32198), summary=[abnormal instance termination].

Errors in file /u01/app/oracle/diag/rdbmsdbcontrol/GZDBCONTROL/trace/GZDBCONTROL_ora_32198.trc:

ORA-16038: log 5 sequence# 23 cannot be archived

ORA-00742: Log read detects lost write in thread %d sequence %d block %d

ORA-00312: online log 5 thread 1: '/u01/app/oracle/fast_recovery_area/GZDBCONTROL/onlinelog/o1_mf_5_d61ctj3y_.log'

USER (ospid: 32198): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=32198), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbmsdbcontrol/GZDBCONTROL/trace/GZDBCONTROL_diag_32167_20161227110109.trc

Dumping diagnostic data in directory=[cdmp_20161227110109], requested by (instance=1, osid=32198), summary=[abnormal instance termination].

问题原因：这三种清空都可能导致“写丢失”发生：操作系统、存储系统、Oracle内部错误。解决办法：使用dump判断redo logfile是否真正发生了&#

2016年12月26日十字螺丝钉 troubleshooting Leave a comment

RFS[79]: No standby redo logfiles created for thread 2

前段时间搭建了一个测试库，主库是双节点RAC，备库是单实例。最近开发人员测试时，老反应主备数据不同步。检查主备库参数配置都没发现问题。最后通过dataguard日志，终于找出问题所在。

1 Log Apply Services       Informational       0        1888          0 NO  2015-08-06 23:26:02 Media Recovery Log /u01/app/oracle/arch/2_1046_884367293.arc
1 Log Apply Services       Warning             0        1889          0 NO  2015-08-06 23:26:03 Media Recovery Waiting for thread 2 sequence 1047 (in transit)
1 Remote File Server       Warning             0        1890          0 NO  2015-08-06 23:27:02 RFS[79]: No standby redo logfiles created for thread 2

1 Log Apply Services Informational 0 1888 0 NO 2015-08-06 23:26:02 Media Recovery Log /u01/app/oracle/arch/2_1046_884367293.arc

1 Log Apply Services Warning 0 1889 0 NO 2015-08-06 23:26:03 Media Recovery Waiting for thread 2 sequence 1047 (in transit)

1 Remote File Server Warning 0 1890 0 NO 2015-08-06 23:27:02 RFS[79]: No standby redo logfiles created for thread 2

上面第三行日志可以看到，日志报RFS[79]: No standby redo logfiles created for thread 2，意思是

2016年12月23日十字螺丝钉 troubleshooting Leave a comment

RAC 没有配置remote_listener导致无法通过scan-ip登录数据库

为现有RAC搭建了一套RAC stanby备库，在客户端用VIP可以正常连接，但是通过SCAN IP则无法连接。客户端无法登录

$ sqlplus TRADE/xxxxx@10.0.2.110/trade34
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 13:56:41 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

$ sqlplus TRADE/xxxxx@10.0.2.110/trade34

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 13:56:41 2016

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

问题原因服务端是RAC，没有设置remote_listener。 11gR2 数据库，默认设置local_listener 参数为local LISTENER（本地监听器,ip地址为vip），为VIP

2024年12月
一	二	三	四	五	六	日
« 9月
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31