Oracle crashed with ORA-00333 on VMWare

I think you will face this issue mostly in development and test environments because these environments usually have a lot of VMs and maybe Oracle with multiple instances inside some of those VMs. Let’s say this is a standalone ESXi that fails due to power failure or the sys admin simply shutdown all vms at once to perform a maintenance. This could cause I/O failures and result ORA-00333 once the database is starting up. The instance will then be in MOUNT status and alter database open will fail.

To recover from such situation, simply recover your database using backup controlfile and apply the current redo log. Here is an example on Windows:

C:\Users\orcladm>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 12 09:11:03 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00333: redo log read error block 45949 count 4440

SQL> select l.status, member from v$logfile inner join v$log l using (group#);

STATUS
----------------
MEMBER
--------------------------------------------------------------------------------

INACTIVE
D:\ORACLE\SERVER\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_1_B64HQT1R_.LOG

INACTIVE
D:\ORACLE\SERVER\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_B64HQT3P_.LOG

CURRENT
D:\ORACLE\SERVER\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_B64HQT64_.LOG

SQL> recover database using backup controlfile;
ORA-00279: change 326802376 generated at 02/04/2015 04:16:55 needed for thread
1
ORA-00289: suggestion :
D:\ORACLE\SERVER\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_02_12\O1_MF_1_597_%U_.A

RC
ORA-00280: change 326802376 for thread 1 is in sequence #597

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\SERVER\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_B64HQT64_.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.
Advertisements

About Bruno Carvalho

Coffee addicted tech guy.
This entry was posted in ORACLE Database and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s