A 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:
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
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 .
when you use old control file to restore/recover database,
does it cause any data lost?
when you use old control file to restore/recover database,
does it cause any data lost?
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
Post a Comment