Control file recovery

Our Production server controlfile got corupted due to server rebooted multiple times

Release: Oracle Server – Enterprise Edition – Version: 10.2.0.3

Symptoms

Instance terminates on startup with

ORA-00600: internal error code, arguments: [3716], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN…
Wed Mar 18 21:56:19 2009
Trace dumping is performing id=[cdmp_20090318215619]
Wed Mar 18 21:56:22 2009
Shutting down instance (abort)
License high water mark = 10
Instance terminated by USER, pid = 1716436

Call stack :- kct_dump_debug kctucd kctrec kcvcrv kcfopd adbdrv opiexe opiosq0 kpooprx
Cause

Solution

Take a backup of existing state of controlfile

SQL> Alter database backup controlfile to ‘<Name>’ ;

SQL> Alter database backup controlfile to trace ;

Get the list of Current file backup

Rman> List backup of controlfile ;

Rman> Shutdown immediate ;

Rman> connect target / catalog username/pwd@connectstring

Rman> Startup nomount

Rman> Restore controlfile from ‘<piece handle>’ ;

Rman> recovery database ;

SQL> Alter database open ;

It will asked to do open resetlog as it’s a restored backup controlfile

Alter database open resetlogs;

Do a Shutdown and take backup of the database.

======================
Exectuted steps are as

Open database in nomount state:

[oracle@dbsr34-node1 bin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Mar 14 10:45:46 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2936012800 bytes
Fixed Size 2075656 bytes
Variable Size 436208632 bytes
Database Buffers 2483027968 bytes
Redo Buffers 14700544 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@dbsr34-node1 ~]$ cd /u01/app/oracle/product/10.2.0/sdg/bin/
[oracle@dbsr34-node1 bin]$ ./rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Wed Mar 14 11:00:43 2012

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

connected to target database: sdg (not mounted)

Restore controlfile from rman backup

RMAN> restore controlfile from ‘/backup/rman/cfc-1978130194-20120314-02’;

Starting restore at 14-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=420 instance=sdg1 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+DATAGROUP/mscdxb/control01.ctl
output filename=+DATAGROUP/mscdxb/control02.ctl
output filename=+DATAGROUP/mscdxb/control03.ctl
Finished restore at 14-MAR-12

Mount the database:

RMAN> sql ‘alter database mount’;

sql statement: alter database mount
released channel: ORA_DISK_1

Recover database:

RMAN> recover database;

Starting recover at 14-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=420 instance=sdg1 devtype=DISK

starting media recovery

archive log thread 1 sequence 23610 is already on disk as file +DATAGROUP/sdg/onlinelog/group_5.301.755030995
archive log thread 1 sequence 23611 is already on disk as file +DATAGROUP/sdg/onlinelog/group_1.297.755030991
archive log thread 1 sequence 23612 is already on disk as file +DATAGROUP/sdg/onlinelog/group_2.298.755030993
archive log thread 2 sequence 21741 is already on disk as file +DATAGROUP/sdg/onlinelog/group_7.304.755714703
archive log thread 2 sequence 21742 is already on disk as file +DATAGROUP/sdg/onlinelog/group_3.299.755030993
archive log thread 2 sequence 21743 is already on disk as file +DATAGROUP/sdg/onlinelog/group_6.302.755030997
archive log thread 2 sequence 21744 is already on disk as file +DATAGROUP/sdg/onlinelog/group_4.300.755030995
archive log filename=/backup/arch/1_23588_755030991.dbf thread=1 sequence=23588
archive log filename=/backup/arch/2_21719_755030991.dbf thread=2 sequence=21719
archive log filename=/backup/arch/2_21720_755030991.dbf thread=2 sequence=21720
archive log filename=/backup/arch/2_21721_755030991.dbf thread=2 sequence=21721
archive log filename=/backup/arch/2_21722_755030991.dbf thread=2 sequence=21722
archive log filename=/backup/arch/1_23589_755030991.dbf thread=1 sequence=23589
archive log filename=/backup/arch/2_21723_755030991.dbf thread=2 sequence=21723
archive log filename=/backup/arch/2_21724_755030991.dbf thread=2 sequence=21724
archive log filename=/backup/arch/1_23590_755030991.dbf thread=1 sequence=23590
archive log filename=/backup/arch/1_23591_755030991.dbf thread=1 sequence=23591
archive log filename=/backup/arch/1_23592_755030991.dbf thread=1 sequence=23592
archive log filename=/backup/arch/2_21725_755030991.dbf thread=2 sequence=21725
archive log filename=/backup/arch/1_23593_755030991.dbf thread=1 sequence=23593
archive log filename=/backup/arch/2_21726_755030991.dbf thread=2 sequence=21726
archive log filename=/backup/arch/2_21727_755030991.dbf thread=2 sequence=21727
archive log filename=/backup/arch/2_21728_755030991.dbf thread=2 sequence=21728
archive log filename=/backup/arch/1_23594_755030991.dbf thread=1 sequence=23594
archive log filename=/backup/arch/2_21729_755030991.dbf thread=2 sequence=21729
archive log filename=/backup/arch/2_21730_755030991.dbf thread=2 sequence=21730
archive log filename=/backup/arch/2_21731_755030991.dbf thread=2 sequence=21731
archive log filename=/backup/arch/1_23595_755030991.dbf thread=1 sequence=23595
archive log filename=/backup/arch/1_23596_755030991.dbf thread=1 sequence=23596
archive log filename=/backup/arch/2_21732_755030991.dbf thread=2 sequence=21732
archive log filename=/backup/arch/2_21733_755030991.dbf thread=2 sequence=21733
archive log filename=/backup/arch/1_23597_755030991.dbf thread=1 sequence=23597
archive log filename=/backup/arch/2_21734_755030991.dbf thread=2 sequence=21734
archive log filename=/backup/arch/2_21735_755030991.dbf thread=2 sequence=21735
archive log filename=/backup/arch/1_23598_755030991.dbf thread=1 sequence=23598
archive log filename=/backup/arch/1_23599_755030991.dbf thread=1 sequence=23599
archive log filename=/backup/arch/1_23600_755030991.dbf thread=1 sequence=23600
archive log filename=/backup/arch/2_21736_755030991.dbf thread=2 sequence=21736
archive log filename=/backup/arch/1_23601_755030991.dbf thread=1 sequence=23601
archive log filename=/backup/arch/1_23602_755030991.dbf thread=1 sequence=23602
archive log filename=/backup/arch/1_23603_755030991.dbf thread=1 sequence=23603
archive log filename=/backup/arch/2_21737_755030991.dbf thread=2 sequence=21737
archive log filename=/backup/arch/1_23604_755030991.dbf thread=1 sequence=23604
archive log filename=/backup/arch/1_23605_755030991.dbf thread=1 sequence=23605
archive log filename=/backup/arch/2_21738_755030991.dbf thread=2 sequence=21738
archive log filename=/backup/arch/1_23606_755030991.dbf thread=1 sequence=23606
archive log filename=/backup/arch/1_23607_755030991.dbf thread=1 sequence=23607
archive log filename=/backup/arch/2_21739_755030991.dbf thread=2 sequence=21739
archive log filename=/backup/arch/1_23608_755030991.dbf thread=1 sequence=23608
archive log filename=/backup/arch/2_21740_755030991.dbf thread=2 sequence=21740
archive log filename=/backup/arch/1_23609_755030991.dbf thread=1 sequence=23609
archive log filename=+DATAGROUP/sdg/onlinelog/group_5.301.755030995 thread=1 sequence=23610
archive log filename=+DATAGROUP/sdg/onlinelog/group_7.304.755714703 thread=2 sequence=21741
archive log filename=+DATAGROUP/sdg/onlinelog/group_3.299.755030993 thread=2 sequence=21742
archive log filename=+DATAGROUP/sdg/onlinelog/group_6.302.755030997 thread=2 sequence=21743
archive log filename=+DATAGROUP/sdg/onlinelog/group_1.297.755030991 thread=1 sequence=23611
archive log filename=+DATAGROUP/sdg/onlinelog/group_4.300.755030995 thread=2 sequence=21744
archive log filename=+DATAGROUP/sdg/onlinelog/group_2.298.755030993 thread=1 sequence=23612
media recovery complete, elapsed time: 00:00:50
Finished recover at 14-MAR-12

Open Database with resetlogs:

RMAN> Alter database open resetlogs;

database opened

RMAN>

SQL> select open_mode from v$database;

read,write

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle backup & recovery. 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