HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

Recover from corrupted REDO files in oracle 12c when DB engine fails to start

Submitted by: @import:stackexchange-dba··
0
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.

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 1033


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

Solution

Do you have another log file in the group? If not...

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.