debugMinor
Recover from corrupted REDO files in oracle 12c when DB engine fails to start
Viewed 0 times
corruptedoracleenginefails12cstartredorecoverfileswhen
Problem
I have an Oracle 12.0.1.2.0 (Enterprise package) installation on my Windows 7 development machine. After an unexpected power failure, the Oracle engine fails to start:
Consequently, I am unable to connect with SQLplus to execute any commands and hence, none of the guides I found online to clear the REDO files works for me.
This is the startup log:
```
...
Mon Jun 19 10:56:47 2017
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Mon Jun 19 10:56:47 2017
Started redo scan
Errors in file C:\APP\SONORA\diag\rdbms\orcl\orcl\trace\orcl_ora_4864.trc (incident=43430):
ORA-00353: log corruption near block 37327 change 3289952 time 06/14/2017 18:09:53
ORA-00334: archived log: 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG'
Incident details in: C:\APP\SONORA\diag\rdbms\orcl\orcl\incident\incdir_43430\orcl_ora_4864_i43430.trc
Sweep [inc][43430]: completed
Errors in file C:\APP\SONORA\diag\rdbms\orcl\orcl\trace\orcl_ora_4864.trc (incident=43431):
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 37327 change 3289952 time 06/14/2017 18:09:53
ORA-00334: archived log: 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG'
Incident details in: C:\APP\SONORA\diag\rdbms\orcl\orcl\incident\incdir_43431\orcl_ora_4864_i43431.trc
Errors in file C:\APP\SONORA\diag\rdbms\orcl\orcl\trace\orcl_m000_4680.trc (incident=43462):
ORA-00353: log corruption near block 37327 change 3289952 time 06/14/2017 18:09:53
ORA-00312: online log 1 thread 1: 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG'
Incident details in: C:\APP\SONORA\diag\rdbms\orcl\orcl\incident\incdir_43462\orcl_m000_4680_i43462.trc
Mon Jun 19 10:57:06 2017
Dumping diagnostic data in directory=[cdmp_20170619
Consequently, I am unable to connect with SQLplus to execute any commands and hence, none of the guides I found online to clear the REDO files works for me.
ORA-01033: ORACLE initialization or shutdown in progress
01033. 00000 - "ORACLE initialization or shutdown in progress"
*Cause: An attempt was made to log on while Oracle is being started up or shutdown.
*Action: Wait a few minutes. Then retry the operation.
Vendor code 1033This is the startup log:
```
...
Mon Jun 19 10:56:47 2017
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Mon Jun 19 10:56:47 2017
Started redo scan
Errors in file C:\APP\SONORA\diag\rdbms\orcl\orcl\trace\orcl_ora_4864.trc (incident=43430):
ORA-00353: log corruption near block 37327 change 3289952 time 06/14/2017 18:09:53
ORA-00334: archived log: 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG'
Incident details in: C:\APP\SONORA\diag\rdbms\orcl\orcl\incident\incdir_43430\orcl_ora_4864_i43430.trc
Sweep [inc][43430]: completed
Errors in file C:\APP\SONORA\diag\rdbms\orcl\orcl\trace\orcl_ora_4864.trc (incident=43431):
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 37327 change 3289952 time 06/14/2017 18:09:53
ORA-00334: archived log: 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG'
Incident details in: C:\APP\SONORA\diag\rdbms\orcl\orcl\incident\incdir_43431\orcl_ora_4864_i43431.trc
Errors in file C:\APP\SONORA\diag\rdbms\orcl\orcl\trace\orcl_m000_4680.trc (incident=43462):
ORA-00353: log corruption near block 37327 change 3289952 time 06/14/2017 18:09:53
ORA-00312: online log 1 thread 1: 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG'
Incident details in: C:\APP\SONORA\diag\rdbms\orcl\orcl\incident\incdir_43462\orcl_m000_4680_i43462.trc
Mon Jun 19 10:57:06 2017
Dumping diagnostic data in directory=[cdmp_20170619
Solution
Do you have another log file in the group? If not...
Mount the database:
Clear the log file:
Reset logs & open the database:
If you get a
You might have to set the hidden parameter
Mount the database:
STARTUP MOUNT;Clear the log file:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG';
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;Reset logs & open the database:
ALTER DATABASE OPEN RESETLOGS;If you get a
ORA-01624 during any of this, then a different solution is needed.You might have to set the hidden parameter
_allow_resetlogs_corruption= true and/or recreate the control file to recover from this.Code Snippets
STARTUP MOUNT;ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'C:\APP\SONORA\ORADATA\ORCL\REDO01.LOG';
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;ALTER DATABASE OPEN RESETLOGS;Context
StackExchange Database Administrators Q#176718, answer score: 2
Revisions (0)
No revisions yet.