Tuesday, April 19, 2011

Loss Of Control-file in various Scenario's

Control file is a small binary file that is part of an Oracle database. The control file is used to keep       track of the database's status and physical structure. The control file is absolutely crucial to database operation . Here , we will discuss the various scenario's when control file(s) get lost or corrupt.

CASE 1 : If one of the controlfile get lost or corrupted 
when the database is shut down and on  startup we get the following error due to loss of controlfile.

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 15:41:33 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area  318046208 bytes
Fixed Size                  1332920 bytes
Variable Size             239077704 bytes
Database Buffers           71303168 bytes
Redo Buffers                6332416 bytes
ORA-00205: error in identifying control file, check alert log for more info 

Checked the Alert log file and the following information are in  the alert log file.
ALTER DATABASE   MOUNT
Mon Apr 18 15:42:12 2011
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Mon Apr 18 15:42:14 2011
Checker run found 1 new persistent data failures
ORA-205 signalled during: ALTER DATABASE   MOUNT

To solve this issue, copy one of the existing control file (say control01.ctl or control03.ctl ) and paste it where the missing  control file was earlier residing and rename the controlfile which one is missing, as in above example, control file (CONTROL02.CTL) is missing and then following the below steps:

SQL> alter database mount;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database ;
NAME          OPEN_MODE
---------           ----------
NOIDA          READ WRITE

CASE  2:  When all the controlfile are lost 
If  we  have  valid  backup and  if  all  the  control files  are  lost  then  we  can  recover  the control  files from autobackup of controlfile or by specifying the location of autobackup control file.

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 16:21:55 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount
ORACLE instance started.
Total System Global Area  318046208 bytes
Fixed Size                  1332920 bytes
Variable Size             272632136 bytes
Database Buffers           37748736 bytes
Redo Buffers                6332416 bytes

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>rman target sys/xxxx@noida

Recovery Manager: Release 11.1.0.6.0 - Production on Mon Apr 18 16:33:34 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: NOIDA (not mounted)

RMAN> restore controlfile from 'D:\orcl_bkp\cf\C-1502483083-20110418-01';  (location of controlfile)
Starting restore at 18-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL03.CTL
Finished restore at 18-APR-11

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN> recover database;
Starting recover at 18-APR-11
Starting implicit crosscheck backup at 18-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 18-APR-11
Starting implicit crosscheck copy at 18-APR-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-APR-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file D:\ORACLE\ORADATA\NOIDA\RE
archived log file name=D:\ORACLE\ORADATA\NOIDA\REDO01.LOG thread=1 sequence=19
media recovery complete, elapsed time: 00:00:05
Finished recover at 18-APR-11

RMAN> alter database open resetlogs;
database opened

CASE 3 :   When we donot have any backup and and all control files are lost or corrupted

SQL> startup nomount
ORACLE instance started.
Total System Global Area  318046208 bytes
Fixed Size                  1332920 bytes
Variable Size             281020744 bytes
Database Buffers           29360128 bytes
Redo Buffers                6332416 bytes

Now we create the controlfile manually on command prompt 

SQL> CREATE CONTROLFILE REUSE DATABASE  "NOIDA"   NORESETLOGS archivelog
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'D:\oracle\oradata\noida\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\oracle\oradata\noida\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\oracle\oradata\noida\REDO03.LOG' SIZE 50M
DATAFILE
'D:\oracle\oradata\noida\SYSTEM01.DBF' ,
'D:\oracle\oradata\noida\USERS01.DBF' ,
'D:\oracle\oradata\noida\EXAMPLE01.DBF' ,
'D:\oracle\oradata\noida\SYSAUX01.DBF' ,
'D:\oracle\oradata\noida\TRANS.DBF' ,
'D:\oracle\oradata\noida\UNDOTBS01.DBF'   ;
Control file created.

SQL> archive log list
Database log mode                              Archive Mode
Automatic archival                               Disabled
Archive destination                              D:\archive\
Oldest online log sequence                  1
Next log sequence to archive              1
Current log sequence                          1

SQL> select first_change# ,group# from v$log;
FIRST_CHANGE#     GROUP#
------------- ----------
      1313491          1
            0                3
            0               2

SQL> alter database open;

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
---------     ----------
NOIDA     READ WRITE


Enjoy   J J J



5 comments:

Unknown said...

Hi Neeraj thank you for great explanation with all scenario it helped me lot.

Can you please post all the maximus scenario about RMAN backup and recovery. it will very helpful for me..

Thank you,
Naveen Singh

Anonymous said...

Hello Neeraj sir,

I have few doubts here
1)When we create Control file then why we don't need to execute recover CMD to sync the details in Control file?

2)When we restore control file from backup we need to execute recover CMD to synch the CTRL file info with DBF and after that even though online redo are intact still we need to open DB with reset log option ? May I know what is the reason?

3)In create CTRL file statement we specify the option resetlog or noresetlog
EG create control file reuse database "test" resetlog

Here we have specified resetlog option then when we open DB at that time also we will have to specify , alter database open resetlog;
Then what is the purpuse of mentioning resetlog in create CTRL file statment? And what is the differce between mentiong resetlog in create control file statment and specifying resetlog option while opening the DB?

Request you to please help .

hungju said...

when you use old control file to restore/recover database,
does it cause any data lost?

hungju said...

when you use old control file to restore/recover database,
does it cause any data lost?

Unknown said...

Steps to Solve Oracle Database Recovery Problem because of Power Failure via DB Recovery Support
It sounds extremely bizarre and odd on the off chance that somebody say their information is lost in light of energy disappointment. This appears to be shockingly in light of the fact that correct now you can't do anything, you don't have any alternative how to recuperate your lost information. On the off chance that you look through any help organization on Google you will discover numerous more organizations yet how you can believe them? Well! Try not to go anyplace, simply contact to Cognegic's DB Recovery Services or Exchange Database Recovery to get back your basic information. We give easy to use bolster and under your financial plan.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801