Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 11, 2015

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

1 comment:

Mary Baxter said...

We often see error because of trouble with some kind library dll file, however in our time it is not a problem, you can download fix tool from my favorite website here: http://fix4dll.com/msvcp140_dll and it will fix it for 5 minutes