Accidental deletion of redo log file group causes startup Database Error ORA-03113

Accidental deletion of redo log file group causes startup Database Error ORA-03113

Speaking of more than one person, learning technology requires Constant tossing and destruction. For example, backup and recovery, you can try to delete files, whether they are log files, temporary files, data files, or system files. After deleting the database, you will surely report an error when you restart the database. Some databases may even crash at the time, so that you can learn how to recover the database. Many conventional Backup recovery methods can even be used. Today, I tried to delete a redo log file group on the virtual machine.

1. Prepare the environment

We performed a test in Oracle11g, and the database was not archived.

SQL>
 

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-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>

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 9

Current log sequence 11

SQL>

2. delete a redo log file group and restart the database to report an error.

First, query the v $ log view to obtain the status of the database redo log file group.

SQL> select GROUP #, MEMBERS, ARCHIVED, STATUS from v $ log;
 

 

GROUP # MEMBERS ARC STATUS

---------------------------------------

1 1 NO INACTIVE

2 1 NO CURRENT

3 1 NO INACTIVE

SQL>
Then, run the ls command to view the data file and delete the first redo log file group (this file group has only one log member ).

[Oracle @ hoegh HOEGH] $ ls
 

Control01.ctl redo01.log sysaux01.dbf undotbs01.dbf

Control02.ctl redo02.log system01.dbf users01.dbf

Example01.dbf redo03.log temp01.dbf

[Oracle @ hoegh HOEGH] $

[Oracle @ hoegh HOEGH] $

[Oracle @ hoegh HOEGH] $ rm redo01.log

[Oracle @ hoegh HOEGH] $ ls

Control01.ctl control02.ctl example01.dbf redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
Restart the database and an error is returned.

SQL>
 

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 5196

The Session ID is 125 Serial number: 5.

SQL>

SQL> select status from v $ instance;

ERROR:

ORA-03114: not connected to ORACLE

SQL>

3. view the alarm log file to locate the problem.

ORA-03113 error is a very classic error, the error causes a variety of, from the error information does not see what causes the error, we can view valuable clues in the alarm log file.

[Oracle @ enmoedu1 trace] $ tail-40 alert_HOEGH.log
 

Wed Jul 08 21:59:30 2015

MMON started with pid = 15, OS id = 5443

Wed Jul 08 21:59:30 2015

MMNL started with pid = 16, OS id = 5445

Starting up 1 dispatcher (s) for network address \ '(ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP ))\'...

Starting up 1 shared server (s )...

ORACLE_BASE from environment =/u01/app/oracle

Wed Jul 08 21:59:39 2015

Alter database mount

Wed Jul 08 21:59:43 2015

Successful mount of redo thread 1, with mount id 2105928075

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount

Wed Jul 08 22:11:45 2015

Time drift detected. Please check VKTM trace file for more details.

Wed Jul 08 22:11:59 2015

Alter database open

Wed Jul 08 22:11:59 2015

Errors in file/u01/app/oracle/diag/rdbms/HOEGH/trace/HOEGH_lgwr_5435.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/HOEGH/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/HOEGH/trace/HOEGH_lgwr_5435.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/HOEGH/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/HOEGH/trace/HOEGH_ora_5451.trc:

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 1 thread 1: \ '/u01/app/oracle/oradata/HOEGH/redo01.log \'

USER (ospid: 5451): terminating the instance due to error 313

Wed Jul 08 22:12:00 2015

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

System State dumped to trace file/u01/app/oracle/diag/rdbms/HOEGH/trace/HOEGH_diag_5425.trc

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

Instance terminated by USER, pid = 5451
The yellow mark is the key information. We know that the file "/u01/app/oracle/oradata/HOEGH/redo01.log" cannot be found.

4. Start the database to the mount status, and redo the log file group again.

The alarm log shows that the first group of redo log file groups is lost. We can use the SQL statement "alter database clear logfile group 1;" to recreate the log file group. After confirming that the log file is successfully created, switch the database to the open state.

SQL> startup nomount
 

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

SQL> alter database mount;

Database altered.

SQL> select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 7 NO INACTIVE

3 6 NO INACTIVE

2 8 NO CURRENT

SQL>

SQL>

SQL>

SQL>

SQL> alter database clear logfile group 1;

Database altered.

SQL> select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 0 NO UNUSED

3 6 NO INACTIVE

2 8 NO CURRENT

Start the database to open

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 0 NO UNUSED

2 8 NO CURRENT

3 6 NO INACTIVE
Now we can view the file list again. The result is as follows.

[Oracle @ hoegh HOEGH] $ ls
 

Control01.ctl control02.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

[Oracle @ hoegh HOEGH] $

5. manually switch the redo log file group
To ensure that the new log file group is available, you can manually change the status of the new log file group (from UNUSED to others ).

SQL>
 

SQL> alter system switch logfile;

System altered.

SQL> select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 9 NO CURRENT

2 8 NO ACTIVE

3 6 NO INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 9 NO ACTIVE

2 8 NO ACTIVE

3 10 NO CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 9 NO INACTIVE

2 11 NO CURRENT

3 10 NO INACTIVE

SQL>
Where,
Current: indicates that this log group is the current log group, and oracle is using this log group;

Active: when the current redo group switches logs, the status changes to active. In this status, if the database is in archive mode, the archive process archives the active log group; in case of database crash, this log group is also necessary for instance recovery;

Inactive: When the active log group is archived and oracle determines that no instance is required to be restored, it will change its status to inactive, waiting for the next round of use; when the log group is inactive, if the database is in archive mode. logs must have been archived.

1 2 Next Page