ORA-00205: error in identifying control file, check alert log for more info

 

Below scenario applies only when the all the control files are either lost or not accessible.....

Recovering lost controlfiles:

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      READ WRITE

SQL> @sql.sql

SQL> select * from v$controlfile;

STATUS    NAME                    IS_ BLOCK_SIZE FILE_SIZE_BLKS

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

   +DATA2/orcl/control01.ctl        NO      16384        594

   +DATA2/orcl/control02.ctl        NO      16384        594

SQL> select * from V$CONTROLFILE_RECORD_SECTION ;

TYPE                RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

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

DATABASE                316         1         1          0      0        0

CKPT PROGRESS               8180        11         0          0      0        0

REDO THREAD                256         8         1          0      0        0

REDO LOG                 72        16         3          0      0        3

DATAFILE                520       100         7          0      0       43

FILENAME                524      2298        11          0      0        0

TABLESPACE                 68       100         8          0      0        6

TEMPORARY FILENAME             56       100         1          0      0        1

RMAN CONFIGURATION           1108        50         2          0      0        2

LOG HISTORY                 56       292        11          1     11       11

OFFLINE RANGE                200       163         0          0      0        0

ARCHIVED LOG                584        28         5          1      5        5

BACKUP SET                 40       409         8          1      8        8

BACKUP PIECE                736       200         8          1      8        8

BACKUP DATAFILE             200       245        14          1     14       14

BACKUP REDOLOG                 76       215         3          1      3        3

DATAFILE COPY                736       200         1          1      1        1

BACKUP CORRUPTION             44       371         0          0      0        0

COPY CORRUPTION              40       409         0          0      0        0

DELETED OBJECT                 20       818         1          1      1        1

PROXY COPY                928       246         0          0      0        0

BACKUP SPFILE                124       131         4          1      4        4

DATABASE INCARNATION             56       292         2          1      2        2

FLASHBACK LOG                 84      2048         0          0      0        0

RECOVERY DESTINATION            180         1         1          0      0        0

INSTANCE SPACE RESERVATION          28      1055         1          0      0        0

REMOVABLE RECOVERY FILES          32      1000         4          0      0        0

RMAN STATUS                116       141        22          1     22       22

THREAD INSTANCE NAME MAPPING          80         8         8          0      0        0

MTTR                    100         8         1          0      0        0

DATAFILE HISTORY            568        57         0          0      0        0

STANDBY DATABASE MATRIX          400        31        31          0      0        0

GUARANTEED RESTORE POINT         212      2048         0          0      0        0

RESTORE POINT                212      2083         0          0      0        0

DATABASE BLOCK CORRUPTION          80      8384         0          0      0        0

ACM OPERATION                104        64         6          0      0        0

FOREIGN ARCHIVED LOG            604      1002         0          0      0        0

37 rows selected.

Remove controlfiles:

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name

CONTROLFILE  UNPROT  FINE     AUG 05 07:00:00  Y    Current.261.886783841

CONTROLFILE  UNPROT  FINE     AUG 05 07:00:00  Y    Current.262.886783841

ASMCMD> rm -rf *

ORA-15032: not all alterations performed

ORA-15028: ASM file '+data2/orcl/controlfile/Current.262.886783841' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

ASMCMD> rm -rf *

ASMCMD> ls -l

ASMCMD-8002: entry 'controlfile' does not exist in directory '+data2/orcl/'

ASMCMD>

SQL> startup

ORACLE instance started.

Total System Global Area  430075904 bytes

Fixed Size           2214056 bytes

Variable Size         385877848 bytes

Database Buffers       37748736 bytes

Redo Buffers           4235264 bytes

ORA-00205: error in identifying control file, check alert log for more info

alert_log:

NOTE: Loaded library: System

SUCCESS: diskgroup DATA2 was mounted

ORA-00210: cannot open the specified control file

ORA-00202: control file: '+DATA2/orcl/control02.ctl'

ORA-17503: ksfdopn:2 Failed to open file +DATA2/orcl/control02.ctl

ORA-15173: entry 'control02.ctl' does not exist in directory 'orcl'

ORA-00210: cannot open the specified control file

ORA-00202: control file: '+DATA2/orcl/control01.ctl'

ORA-17503: ksfdopn:2 Failed to open file +DATA2/orcl/control01.ctl

ORA-15173: entry 'control01.ctl' does not exist in directory 'orcl'

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Wed Aug 05 07:31:34 2015

Checker run found 2 new persistent data failures

[oracle@collabn1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 5 07:35:38 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> connect catalog rcuser/rcuser@prod1

connected to recovery catalog database

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

70      Full    9.36M      DISK        00:00:00     04-AUG-15    

       BP Key: 73   Status: AVAILABLE  Compressed: NO  Tag: TAG20150804T152121

       Piece Name: +DATA2/orcl/autobackup/2015_08_04/s_886864881.283.886864881

 Control File Included: Ckp SCN: 1052464      Ckp time: 04-AUG-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

140     Full    9.36M      DISK        00:00:00     04-AUG-15    

       BP Key: 142   Status: AVAILABLE  Compressed: NO  Tag: TAG20150804T153009

       Piece Name: +DATA2/orcl/autobackup/2015_08_04/s_886865409.287.886865409

 Control File Included: Ckp SCN: 1053476      Ckp time: 04-AUG-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

174     Full    9.36M      DISK        00:00:00     04-AUG-15    

       BP Key: 177   Status: AVAILABLE  Compressed: NO  Tag: TAG20150804T161448

       Piece Name: +DATA2/orcl/autobackup/2015_08_04/s_886868088.263.886868089

 Control File Included: Ckp SCN: 1057324      Ckp time: 04-AUG-15

RMAN> restore controlfile;

Starting restore at 05-AUG-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece +DATA2/orcl/autobackup/2015_08_04/s_886868088.263.886868089

channel ORA_DISK_1: piece handle=+DATA2/orcl/autobackup/2015_08_04/s_886868088.263.886868089 tag=TAG20150804T161448

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DATA2/orcl/control01.ctl

output file name=+DATA2/orcl/control02.ctl

Finished restore at 05-AUG-15

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of sql command on default channel at 08/05/2015 07:37:23

RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '+DATA2/orcl/system01.dbf'

RMAN> recover database;

Starting recover at 05-AUG-15

Starting implicit crosscheck backup at 05-AUG-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

Crosschecked 6 objects

Finished implicit crosscheck backup at 05-AUG-15

Starting implicit crosscheck copy at 05-AUG-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at 05-AUG-15

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +DATA2/ORCL/ARCHIVELOG/2015_08_05/thread_1_seq_10.290.886915925

File Name: +DATA2/ORCL/ARCHIVELOG/2015_08_05/thread_1_seq_11.291.886922347

File Name: +DATA2/ORCL/AUTOBACKUP/2015_08_05/s_886914726.289.886914727

File Name: +DATA2/ORCL/AUTOBACKUP/2015_08_04/s_886868088.263.886868089

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file +DATA2/orcl/archivelog/2015_08_05/thread_1_seq_10.290.886915925

archived log for thread 1 with sequence 11 is already on disk as file +DATA2/orcl/archivelog/2015_08_05/thread_1_seq_11.291.886922347

archived log for thread 1 with sequence 12 is already on disk as file +DATA2/orcl/redo03.log

archived log file name=+DATA2/orcl/archivelog/2015_08_05/thread_1_seq_10.290.886915925 thread=1 sequence=10

archived log file name=+DATA2/orcl/archivelog/2015_08_05/thread_1_seq_11.291.886922347 thread=1 sequence=11

archived log file name=+DATA2/orcl/redo03.log thread=1 sequence=12

media recovery complete, elapsed time: 00:00:00

Finished recover at 05-AUG-15

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

alert_log:

alter database open resetlogs

Signalling error 1152 for datafile 1!

ORA-1152 signalled during: alter database open resetlogs...

Wed Aug 05 07:37:23 2015

Signalling error 1152 for datafile 1!

Signalling error 1152 for datafile 2!

Signalling error 1152 for datafile 3!

Signalling error 1152 for datafile 4!

Signalling error 1152 for datafile 5!

Checker run found 1 new persistent data failures

Wed Aug 05 07:38:06 2015

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list

1 , 2 , 3 , 4 , 5

Completed: alter database recover datafile list

1 , 2 , 3 , 4 , 5

alter database recover if needed

start until cancel using backup controlfile

Media Recovery Start

Serial Media Recovery started

ORA-279 signalled during: alter database recover if needed

start until cancel using backup controlfile

...

alter database recover logfile '+DATA2/orcl/archivelog/2015_08_05/thread_1_seq_10.290.886915925'

Media Recovery Log +DATA2/orcl/archivelog/2015_08_05/thread_1_seq_10.290.886915925

ORA-279 signalled during: alter database recover logfile '+DATA2/orcl/archivelog/2015_08_05/thread_1_seq_10.290.886915925'...

alter database recover logfile '+DATA2/orcl/archivelog/2015_08_05/thread_1_seq_11.291.886922347'

Media Recovery Log +DATA2/orcl/archivelog/2015_08_05/thread_1_seq_11.291.886922347

ORA-279 signalled during: alter database recover logfile '+DATA2/orcl/archivelog/2015_08_05/thread_1_seq_11.291.886922347'...

alter database recover logfile '+DATA2/orcl/redo03.log'

Media Recovery Log +DATA2/orcl/redo03.log

Incomplete recovery applied all redo ever generated.

Recovery completed through change 1088831 time 08/05/2015 07:30:18

Media Recovery Complete (orcl)

Completed: alter database recover logfile '+DATA2/orcl/redo03.log'

Wed Aug 05 07:38:25 2015

alter database open resetlogs

Archived Log entry 9 added for thread 1 sequence 10 ID 0x5450a6ec dest 1:

Archived Log entry 10 added for thread 1 sequence 11 ID 0x5450a6ec dest 1:

Archived Log entry 11 added for thread 1 sequence 12 ID 0x5450a6ec dest 1:

RESETLOGS after complete recovery through change 1088831

Resetting resetlogs activation ID 1414571756 (0x5450a6ec)

Wed Aug 05 07:38:26 2015

Setting recovery target incarnation to 3

CHANGE TRACKING found new resetlogs, resetting.

old SCN: 0x0000.000e6c20, new SCN: 0x0000.00109d40

old time: 886783854, new time=886923505

CHANGE TRACKING is reinitializing the change tracking file.

Wed Aug 05 07:38:26 2015

Assigning activation ID 1414712091 (0x5452cb1b)

LGWR: STARTING ARCH PROCESSES

Wed Aug 05 07:38:26 2015

ARC0 started with pid=35, OS id=8913

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Wed Aug 05 07:38:27 2015

ARC1 started with pid=36, OS id=8915

Thread 1 opened at log sequence 1

 Current log# 1 seq# 1 mem# 0: +DATA2/orcl/redo01.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Wed Aug 05 07:38:27 2015

ARC2 started with pid=37, OS id=8917

ARC1: Archival started

ARC2: Archival started

Starting background process CTWR

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Wed Aug 05 07:38:27 2015

ARC3 started with pid=38, OS id=8919

Wed Aug 05 07:38:27 2015

CTWR started with pid=39, OS id=8921

Block change tracking service is active.

Wed Aug 05 07:38:27 2015

SMON: enabling cache recovery

Successfully onlined Undo Tablespace 2.

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Wed Aug 05 07:38:28 2015

QMNC started with pid=40, OS id=8923

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Wed Aug 05 07:38:29 2015

db_recovery_file_dest_size of 12288 MB is 1.10% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Completed: alter database open resetlogs

Wed Aug 05 07:38:31 2015

Starting background process CJQ0

Wed Aug 05 07:38:31 2015

CJQ0 started with pid=47, OS id=8970