Oracle Database startup throws ORA-00214 error after abrupt shutdown ( due to power failure )

 

Oracle Database startup throws ORA-00214 after abrupt shutdown ( due to power failure )

Issue

SQL> startup

ORACLE instance started.

Fixed Size 2256752 bytes

Total System Global Area 2889490432 bytes

Variable Size 1124073616 bytes

Database Buffers 1744830464 bytes

Redo Buffers 18329600 bytes

ORA-00214: control file '/usr1/control/ebportal/control02.ctl' version 286152

inconsistent with file '/apps/app/oracle/oradata/ebportal/control01.ctl'

version 286092

Observation

The database crashed while the mirrored copies of the Control File were being updated, causing them to be out of sync.

Solution

The following steps should be carried out by an experienced Oracle DBA who is familiar with backing up and recovering databases.

Outline of solution:

Step 1) open the database with a single valid copy of the Control File
Step 2) Shut down the database
Step3) copy that version of the Control File onto the other mirrored copies

Step 1) open the database with a single valid copy of the Control File

Here in this case Control File /apps/app/oracle/oradata/ebportal/control01.ctl is the valid one, 

change the database initialization parameter control_files from:

*.control_files='/apps/app/oracle/oradata/ebportal/control01.ctl','/usr1/control/ebportal/control02.ctl'

to
*.control_files='/apps/app/oracle/oradata/ebportal/control01.ctl'

How to do this 

 a) first you need to check your database is running with PFILE or SPFILE

$ sqlplus "/as sysdba"

SQL> SHOW PARAMETER SPFILE 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /apps/app/oracle/product/11.2.0.4.0/dbs/spfileebportal.ora

This means that the database is running with SPFILE

locate the spfileebportal.ora  ( it is in  /apps/app/oracle/product/11.2.0.4.0/dbs)  ( usually SPFILE is located in $ORACLE_HOME/dbs folder) 

[oracle@122DBlive]$ cd /apps/app/oracle/product/11.2.0.4.0/dbs

[oracle@122DBlive]$ ls -lrt
total 20
-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 Nov 27  2015 lkEBPORTAL
-rw-r-----. 1 oracle oinstall 1536 Jan 10  2023 orapwebportal
-rw-r-----. 1 oracle oinstall 2560 May 17 16:56 spfileebportal.ora   --->> this is the spfile
-rw-rw----. 1 oracle oinstall 1544 Aug 14 14:33 hc_ebportal.dat

[oracle@122DBlive]$

 b)  Create PFILE from SPFILE

     SQL> create pfile from spfile; 

      File created.

[oracle@122DBlive]$ ls -lrt

total 24

-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 Nov 27  2015 lkEBPORTAL
-rw-r-----. 1 oracle oinstall 1536 Jan 10  2023 orapwebportal
-rw-r-----. 1 oracle oinstall 2560 May 17 16:56 spfileebportal.ora
-rw-rw----. 1 oracle oinstall 1544 Aug 14 14:33 hc_ebportal.dat
-rw-r--r--. 1 oracle oinstall  944 Aug 14 14:40 initebportal.ora   --->>  this one is created now

Now edit this file initebportal.ora  using vi editor 

and change the database initialization parameter control_files from:

*.control_files='/apps/app/oracle/oradata/ebportal/control01.ctl','/usr1/control/ebportal/control02.ctl'

to
*.control_files='/apps/app/oracle/oradata/ebportal/control01.ctl'

$ sqlplus "/as sysdba"

SQL> startup mount pfile=initebportal.ora

ORACLE instance started.

 
Total System Global Area 2889490432 bytes
Fixed Size                  2256752 bytes
Variable Size            1124073616 bytes
Database Buffers         1744830464 bytes
Redo Buffers               18329600 bytes
Database mounted.

SQL> recover database

Media recovery complete

SQL> alter database open;
 
Database altered.

Now the database is opened successfully 

Step 2) Shut down the database

SQL> shut immediate

Database closed.
Database dismounted.

ORACLE instance shut down.

SQL> exit

Step 3) copy that version of the Control File onto the other mirrored copies

$ cp /apps/app/oracle/oradata/ebportal/control01.ctl /usr1/control/ebportal/control02.ctl

Now edit this file initebportal.ora  again using vi editor 

and change the database initialization parameter control_files from:

*.control_files='/apps/app/oracle/oradata/ebportal/control01.ctl'

to

*.control_files='/apps/app/oracle/oradata/ebportal/control01.ctl','/usr1/control/ebportal/control02.ctl'

Start the database again

$ sqlplus "/as sysdba"

 

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 14 15:07:31 2023
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to an idle instance.

 

SQL> startup pfile=initebportal.ora

ORACLE instance started.

 
Total System Global Area 2889490432 bytes
Fixed Size                  2256752 bytes
Variable Size            1124073616 bytes
Database Buffers         1744830464 bytes
Redo Buffers               18329600 bytes
Database mounted.
Database opened.

Now it is opened with 2 control files   as configured in pfile.

Now create SPFILE from PFILE

SQL> create spfile from pfile;
 
File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Start the database with SPFILE now

SQL> startup

ORACLE instance started.

Total System Global Area 2889490432 bytes

Fixed Size                  2256752 bytes

Variable Size            1124073616 bytes

Database Buffers         1744830464 bytes

Redo Buffers               18329600 bytes

Database mounted.

Database opened.

Finally database is successfully recovered

My Oracle Support Note 1014751.6 "ORA-00214: On Startup Or Shutdown  can be checked if you have oracle support.