Yesterday, I met an Oracle database, the environment is: Windows2000 Oracle9i. After using the Windows shutdown, Oracle is unable to connect. When starting with Startup, always report ORA-00333 error, check the description of the Oracle document on this issue, as follows: ORA-00333 Redo log read Error Block String Count StringCause: AN I / O Error Occurred While Reading The log described in theaccompanying error.Action: Restore Accessibility to file, or get another copy of the file. Judgment is the online log is corrupted, and the following is a solution to this problem. Everyone is clear, online logs are classified into current online logs and non-current online logs, and the damage to the current online log is relatively simple, and the problem can be solved by the CLEAR command. 1. Start the database, encounter ORA-00312 or ORA-00313 error, such as ora-00313: Open failed for members of log group 1 of thread 1 ORA-00312: Online log 1 thread 1: 'D: / Oracle / ORADATA / Test / redo01.log 'From here we know the data files of the log group 1 corrupted from the alarm file to see more detailed information 2, view v $ log view SQL> Select Group #, Sequence #, Archived, Status from V $ Log;
Group # sequence # Archived Status ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 1 Yes Inactive 2 2 Yes Inactive 3 3 No current You can know that the group is non-current state and has been archived. 3, rebuild the log file with clear command SQL> ALTER DATABASE CLEAR LOGFILE Group 1; if this log group has not been archived, you need to use SQL> ALTER DATABASE CLEAR UNACHIVED LOGFILE Group 1; 4, open the database, re-back up the database SQL> ALTER DATABASE OPEN; Description: 1. If the damage is a non-current online log file, only the clear can be reconstructed, but if the database is archived, the log is not archived, it is necessary to force CLEAR. 2. It is recommended that CLEAR, especially for a full backup of a database. 3, this method is suitable for archiving and non-archive database
There are two cases of damage to the current log in the current online log archiving mode. First, the database is normal shutdown. There is no unresolved transaction in the log file. The current log group can be used directly with ALTER DATABASE CLEAR UNACHIVED LOGFILE Group N is rebuilt. Second, it is an active business in the log group. The database needs to be recovered. The log group needs to synchronize. There are two remedies A. The best way is to ensure the consistency of the database by incomplete recovery, but this Measures require in archive, and have available backup B. By mandatory recovery, it may cause the database inconsistent. The following is used to illustrate the two recovery methods to restore 1 by backup. Open the database, you will encounter a similar error ORA-00313: Open failed for members of log group 1 of thread 1 ora-00312: Online log 1 Thread 1, respectively, OREAD 1 ORA-00312: Online log 1 thread 1 : 'D: /oracle/oradata/test/redo01.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 2, View v $ log, discovery is the current log SQL> SELECT Group #, sequence #, archived, status from v $ log;
Group # sequence # Archived Status ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 1 No Current 2 Yes Inactive 3 3 yes inactive
3, clear found unsuccessful SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of thread 1 ORA-00312: online log 1 thread 1: 'D: /oracle/oradata/test/redo01.log'
4, all backup of the copy-effective database, does not completely restore the database to get the most recent SCN method with unsil SCN recovery or use the Until CNACEL Recover Recover Database Until Cancel to select Auto first, try to recover the archive log, and then re-re-re-re-re- Recover Database Until Cancel Enter Cancel this time, complete the incomplete recovery, that is, the recovery twice. Such as: SQL> Recover Database Until Cancel; Auto ... SQL> Recover Database Until Cancel; Cancel; 5, using Alter Database Open RESETLOGS Open Database Description: 1. The database recovered database is consistent incomplete recovery, will lose the current Transaction Data in the online log 2, this method is suitable for archiving the database and has a fully backed up of available databases. 3. After the recovery is successful, remember to make a full backup of a database. 4. It is recommended that the online log file must implement the mirror phase on different disks, avoid this happening, because any data is lost is not allowed for production.
If there is no backup, mandatory recovery 1. Open the database, you will encounter a similar error ORA-00313: Open failed for members of log group 1 of thread 1 ORA-00312: Online log 1 thread 1: 'D: / Oracle / OraData/test/redo01.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 2, view v $ log, discovery Is current log sql> select group #, sequence #, archived, status from v $ log;
Group # sequence # Archived Status ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 1 No Current 2 Yes Inactive 3 3 yes inactive
3, clear found unsuccessful SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of thread 1 ORA-00312: online log 1 thread 1: 'D: /oracle/oradata/test/redo01.log'
4, remove the database down SQL> Shutdown Immediate
5, add the following parameters in INIT
6, restart the database, use the Until Cancel to recover SQL> Recover Database Until Cancel; Cancel If an error, no longer pay, SQL> ALTER DATABASE OPEN RESETLOGS;
7, after the database is opened, execute a Full Export immediately
8, shutdown database, remove _ALL_RESETLOGS_CORRUPT parameters
9, rebuild library
10, IMPORT and complete recovery
11. It is recommended to execute Analyze Table ... Validate Structure Cascade; Description: 1, this recovery method is a recovery method after a way. In general, it is recommended not to use because the method may cause the database to be inconsistent 2, which is also lost Data, but lost data does not have more data on the previous method, mainly the submitted or not submitted data that is not written to the data file. 3. After the suggestion is successful, strictly enforce the above 7 to 11 steps, complete the inspection and analysis of the database 4, all completed the full backup of the database 5, it is recommended that the online log file must implement the mirror in different disks, avoid this The occurrence of the situation, because the loss of any data is not allowed for production.
