Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to do with the loss of redo log in the database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Database version: Release 11.2.0.3.0

Error description: database redo log was mistakenly deleted when the database was in normal shutdown (shutdown immediate) mode.

1. The database is closed normally and the redo log file is deleted.

-the database cannot be started.

SQL > archive log list

Database log mode No Archive Mode-non-archived 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: failed to start end-of-file on communication channel- database!

Process ID: 5103

Session ID: 125 Serial number: 5

= tail-f alert_PROD1.log-log shows that the redo file is lost.

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 Greater App Universe oracleUniple oradata Greater PROD1andredo01.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 Greater App Universe oracleUniple oradata Greater PROD1andredo01.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 Greater App Universe oracleUniple oradata Greater PROD1andredo01.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 Greater App Universe oracleUniple oradata Greater PROD1andredo01.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

Dumping diagnostic data in directory= [CDMP _ 20171016083911], requested by (instance=1, osid=5103), summary= [abnormal instance termination].

Instance terminated by USER, pid = 5103

Solution:

Perform an incomplete recovery, and then open the database in resetlog mode.

SQL > recover database until cancel

Media recovery complete.

SQL > alter database open resetlogs

Database altered.

Log Information:

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

Thank you for reading this article carefully. I hope the article "what if the redo log is lost in the database" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.