数据库中redo log丢失怎么办-编程学习网

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

数据库版本：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