数据库中redo log丢失怎么办

106次阅读

共计 4124 个字符，预计需要花费 11 分钟才能阅读完成。

这篇文章主要介绍了数据库中 redo log 丢失怎么办，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

数据库版本：Release 11.2.0.3.0

错误描述：数据库在正常关闭（shutdown immediate）模式下，数据库 redo log 被误删除。

1，数据库正常关闭，删除 redo log 文件。

——— 数据库无法启动.
SQL archive log list;
Database log mode No Archive Mode ————- 非归档模式。
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 1
Current log sequence 1

-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 16 08:39:02 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL startup
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 528485124 bytes
Database Buffers 406847488 bytes
Redo Buffers 4919296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel————– 数据库启动失败！
Process ID: 5103
Session ID: 125 Serial number: 5

=========tail -f alert_PROD1.log ————-log 显示 redo 文件丢掉。

ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_lgwr_4990.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/PROD1/redo01.log
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_lgwr_4990.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/PROD1/redo01.log
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_5103.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/PROD1/redo01.log
Mon Oct 16 08:39:10 2017
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_5105.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/PROD1/redo01.log
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
System state dump requested by (instance=1, osid=5103), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_diag_4980.trc
USER (ospid: 5103): terminating the instance due to error 313
Dumping diagnostic data in directory=[cdmp_20171016083911], requested by (instance=1, osid=5103), summary=[abnormal instance termination].
Instance terminated by USER, pid = 5103

解决方法：

进行不完全恢复，然后 resetlog 方式打开数据库。

SQL recover database until cancel;
Media recovery complete.
SQL alter database open resetlogs;
Database altered.

log 信息：

ALTER DATABASE RECOVER database until cancel
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER database until cancel
alter database open resetlogs
RESETLOGS after complete recovery through change 1105760
Resetting resetlogs activation ID 2082284563 (0x7c1d2413)
Mon Oct 16 08:40:47 2017
Time drift detected. Please check VKTM trace file for more details.
Mon Oct 16 08:40:48 2017
Setting recovery target incarnation to 3
Mon Oct 16 08:40:48 2017
Assigning activation ID 2178882977 (0x81df1da1)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/PROD1/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Oct 16 08:40:48 2017
SMON: enabling cache recovery
[5294] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4294618830 end:4294618860 diff:30 (0 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Oct 16 08:40:49 2017
QMNC started with pid=20, OS id=5305
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Mon Oct 16 08:40:49 2017
Starting background process CJQ0
Mon Oct 16 08:40:49 2017
CJQ0 started with pid=21, OS id=5317