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.