A recovery instance that causes Oracle database to fail to be started due to a system crash

The author opensOracle DatabaseWhen preparing for something, suddenly the computer blue screen,System Crash. After the instance is restarted, start the instance and prepare to connect to the Oracle database for continued use, but find that the instance cannot be connected, always reported:

 
 

  
  
SQL> conn system/test @ test
  
  
 
  
  
ERROR:
  
  
 
  
  
ORA-12514: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor
 
 

At first, I thought that the database was not started, so I restarted the Database Service and still could not connect to the database. At that time, I did not doubt the database was faulty, this is because the system crashes many times when the oracle database is opened, and the system can still be used normally after being restarted.

After several failed connections, I remembered to look at the alter file. A problem occurred:

 
 

  
  
Sun Jul 17 13:26:15 2011  
  
  
 
  
  
Recovery of Online Redo Log: Thread 1 Group 2 Seq 3 Reading mem 0  
  
  
 
  
  
Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.2.0\DB_1\ORADATA\TEST\REDO02.LOG  
  
  
 
  
  
Sun Jul 17 13:26:15 2011  
  
  
 
  
  
Errors in file d:\oracle\product\10.2.0\db_1\admin\test\bdump\test_dbw0_2904.trc:  
  
  
 
  
  
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kcbzdh+583] [PC:0x4A41AF] [ADDR:0xECDC0214] [UNABLE_TO_READ] [] 
 
 

The REDO02.LOG of the redo log file Group 2 needs to be restored.

Here, because it is a new database and there is no important data, it is OK as long as the database can be restored. Take the following measures:

Delete this file and restart to open the database:

 
 

  
  
SQL> startup mount
  
  
 
  
  
The ORACLE routine has been started.
  
  
 
  
  
Total System Global Area 167772160 bytes
  
  
 
  
  
Fixed Size 1247876 bytes
  
  
 
  
  
Variable Size 71304572 bytes
  
  
 
  
  
Database Buffers 88080384 bytes
  
  
 
  
  
Redo Buffers 7139328 bytes
  
  
 
  
  
The database has been loaded.
 
 

Run the following code:

 
 

  
  
SQL> alter database open;
  
  
 
  
  
Alter database open
  
  
 
  
  
*
  
  
 
  
  
Row 3 has an error:
  
  
 
  
  
ORA-00313: Unable to open a member of log group 2 (for thread 1)
  
  
 
  
  
ORA-00312: Online log 2 thread 1:
  
  
 
  
  
'D: \ ORACLE \ PRODUCT \ 10.2.0 \ DB_1 \ ORADATA \ TEST \ REDO02.LOG'
  
  
 
  
  
ORA-27041: Unable to open file
  
  
 
  
  
OSD-04002: Unable to open file
  
  
 
  
  
O/S-Error: (OS 2) the system cannot find the specified file.
  
  
 
  
  
SQL> select group #, sequence #, status from v $ log;
  
  
 
  
  
GROUP # SEQUENCE # STATUS
  
  
 
  
  
--------------
  
  
 
  
  
1 2 INACTIVE
  
  
 
  
  
3 1 INACTIVE
  
  
 
  
  
2 3 CURRENT
 
 

Because I deleted the current log file, the error cannot be automatically restored.

 
 

  
  
SQL> alter database drop logfile member 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ DB_1 \ ORADATA \ T
  
  
 
  
  
EST \ REDO02.LOG ';
  
  
 
  
  
Alter database drop logfile member 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ DB_1 \ ORADATA \ TEST \ R
  
  
 
  
  
Edo02.log'
  
  
 
  
  
*
  
  
 
  
  
Row 3 has an error:
  
  
 
  
  
ORA-00361: Unable to delete the last log Member
  
  
 
  
  
D: \ ORACLE \ PRODUCT \ 10.2.0 \ DB_1 \ ORADATA \ TEST \ REDO02.LOG (group 2)
 
 

You cannot delete the REDO02.LOG, but you do not want to rename the log. Restore the deleted REDO02.LOG.

 
 

  
  
SQL> alter database recover;
  
  
 
  
  
The database has been changed.
  
  
 
  
  
SQL> alter database open;
  
  
 
  
  
The database has been changed.
  
  
 
  
  
SQL> shutdown immediate;
  
  
 
  
  
The database has been closed.
  
  
 
  
  
The database has been detached.
  
  
 
  
  
The ORACLE routine has been disabled.
  
  
 
  
  
SQL> startup
  
  
 
  
  
The ORACLE routine has been started.
  
  
 
  
  
Total System Global Area 167772160 bytes
  
  
 
  
  
Fixed Size 1247876 bytes
  
  
 
  
  
Variable Size 71304572 bytes
  
  
 
  
  
Database Buffers 88080384 bytes
  
  
 
  
  
Redo Buffers 7139328 bytes
  
  
 
  
  
The database has been loaded.
  
  
 
  
  
The database has been opened.
  
  
 
  
  
SQL>
 
 

In this way, the Oracle database can be used again.

Note: The above method can only be applied to the testing environment of the local machine or the testing environment that is not important. Do not perform this operation on the production database without worrying about data loss, to avoid data loss.