If you loss or if your all copies of control file is corrupted and if
you have backup of your control file then it is required to restore your
control file from your backup.
Restore control file to default location:
----------------------------------------------
The default location is defined by CONTROL_FILES parameter of
pfile/spfile. If you don't specify any location while restoring your
control file then the control file will be restored to the location set
by CONTROL_FILES parameter. TO determine DBID checkhttp://oracledatabasegrid.blogspot.in/2013/08/how-to-discover-find-dbid.html
RMAN>SET DBID 236329879
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
Restore of the Control File from Control File Autobackup
-------------------------------------------------------------
If you are not using a recovery catalog, you must restore your control
file from an autobackup. The database must be in a NOMOUNT state. And
you have to set DBID. RMAN uses the autobackup format and DBID to
determine where to find for the control file autobackup.
RMAN>SET DBID 236329879
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO 'autobackup_format';
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
Restore of the Control File When Using a Flash Recovery Area
---------------------------------------------------------------------
Suppose you restored a backup of the control file. Now in that control
file the backup information may not updated/full. May be it contains
only current backup information of that session while taking backup. If
you use flash recovery area then RMAN automatically catalog the backups
in the flash recovery area. As a result the restored control file has a
complete and accurate record of all backups in your flash recovery area
and any other backups that were known to the control file at the time of
the backup.
Restoring a Control File When Using a Recovery Catalog
------------------------------------------------------------------
The recovery catalog contains a complete record of your backups, including backups of the control
file. Therefore, you do not have to specify your DBID or control file autobackup format.
Just use,
$rman TARGET / CATALOG catdb/catdb
RMAN> RESTORE CONTROLFILE;
Restore of the Control File From a Known Location
-----------------------------------------------------
If you know the backuppiece of controlfile or any copy then simply you can use,
RMAN> RESTORE CONTROLFILE from 'filename';
Restore of the Control File to a New Location
---------------------------------------------------
In prior cases RMAN restore the control file to the location specified by CONTROL_FILES parameter of the spfile or pfile.
If you want to restore the control file to another location use,
RMAN>RESTORE CONTROLFILE TO 'give_here_new_location';
You can also change CONTROL_FILES parameter and then perform RESTORE CONTROLFILE to change location.
Limitations When Using a Backup Control File
------------------------------------------------
After you restore your database using a backup control file, you must
run RECOVER DATABASE and perform an OPEN RESETLOGS on the database.
Problem Description:
ORA-00214: control file {name} version {num} inconsistent with file {name}.
Cause of The Problem:
Oracle detects an inconsistency between the mirrored copies of the control file.
All copies of the control file must have the same internal sequence
number for oracle to start up the database or shut it down in normal or
immediate mode.
If the database is running and the checkpoint in the file header could not be advanced the datafile will be taken offline.
Typical scenarios in which you may receive an ORA-00214 include:
1. You have restored the control file from backup, but forgot to copy it
onto all of the mirrored copies of the control file as listed in the
"CONTROL_FILES" parameter in the initialization parameter.
2. You have moved one or more copies of the control file to a different location while the database was up and running.
3. You accidentally overwrote one of the copies of the control file with an old copy.
4. The database or the system crashed while the mirrored copies of the
control file were being updated, causing them to be out of sync.
5. You are restoring a database backup that was improperly taken with the database up and running ("fuzzy" backup).
Solution of the Problem:
To fix the error start your database with single copy of the control
file and then shut the database down and then copy the version of good
copy of control file onto the other mirror copies.
Step 01) If database is still up do a
shutdown abort.
Step 02) If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it to include just one copy of control file.
If you use spfile then after issuing
startup nomount use show control_files to see existing controlfiles inside spfile and then you can use
ALTER SYSTEM SET CONTROL_FILES=file_name; in order to point just one copy of control file.
Step 03) Start the database in restricted mode.
startup restrict
If it is fine go to step 04.
If instead you get ORA-1122, ORA-1110, and ORA-1207, go back to step 2 and try with another control file.
If you have already tried each and every one of the mirrored copies
unsuccessfully, you must create a new control file for the database.
If you get ORA-1113 and ORA-1110 pointing to one of the datafiles, it
means the copy of the control file you picked is good, but the
referenced datafile must be recovered before the database can be
opened.Then RECOVER DATBASE, apply the log it prompt and ALTER DATABASE
OPEN.
Step 04) Shut the database down.
SQL>shutown
Step 05) Copy the good mirrored copy of the control file that you
just used to bring the database up onto all other copies, as originally
listed in the CONTROL_FILES parameter of your init.ora file.
Step 06) Edit the init.ora file's CONTROL_FILES parameter to include all mirror copy again.
Step 07) Start the database.
Every database has a control file which is a binary file that records
the physical structure of the database. Control file is called the heart
of the database.
The control file includes:
• The database name.
• Names and locations of associated datafiles and redo log files.
• The timestamp of the database creation.
• The current log sequence number.
• Checkpoint information.
• Information of the tablespaces.
• Datafile offline ranges.
• The log history.
• Archived log information.
• Database backup information taken through RMAN.
• The current log sequence number.
• Datafile copy information.
How to see the control file Contents:
--------------------------------------
Control file creation sql command/syntax can be seen by,
alter database backup controlfile to trace as '/u01/ctrl.txt'
After issuing the command if you open /u01/ctrl.txt you will see the sql script of creation controlfile.
If you don't use trace suppose you use,
alter database backup controlfile as '/u01/ctrl.txt' then you will get a duplicate binary copy of the current control file.
Before Oracle 10.2g if we need to change the limit of MAXLOGFILE,
MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES then the
possible solutions is either RE-create new controlfile or create a new
database.
But from Oracle 10.2g all sections of the control file are now
automatically extended when they run out of space. This means that there
is no longer a requirement to re-create the control file when changes
in the configuration parameter the MAXLOGFILE, MAXLOGMEMBERS,
MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are needed.
Two different Sections of Control file:
--------------------------------------------
1)Circularly reusable Sections:
The CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days
before a reusable record in the control file can be reused. Example of
circularly reusable records are archive log records and various backup
records.
2)Not circularly reusable Sections:
Records such as datafile, tablespace, and redo thread records, which are
never reused unless the corresponding object is dropped from the
tablespace.
For the circularly reusable sections the behaviour remain same with previous version.
The new feature in Oracle 10.2g is that for the non-reusable records we
now also extend the control file size if we go over the previous limit.
Though the values for MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY,
MAXDATAFILES, and MAXINSTANCES are still useful since they initialize
the control file at a certain size, but they no longer set a hard limit
for the number of records in the control file.
Therefore 10.2g onwards, we can get rid of re-creating controlfile
whenever we need change parameter like MAXLOGFILE,
MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES and keep the
database alive.
Error Description
While creating cloning database I have modified controlfile contents as below where I like to change my database name to krishna.
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE DATABASE "krishna" RESETLOGS ARCHIVELOG
.
.
.
'F:\ORACLE\KRISHNA\SYSTEM01.DBF',
.
.
.
CHARACTER SET WE8MSWIN1252
;
Now I ran this script to create controlfile and it fails
SQL> @f:\controlfile.ctl
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1247516 bytes
Variable Size 71304932 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
CREATE CONTROLFILE REUSE DATABASE "krishna" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name krishna in file header does not match given name of
krish
ORA-01110: data file 1: 'F:\ORACLE\krish\SYSTEM01.DBF'
Cause of The Problem
Control file script has been changed, more specifically database name has been changed.
Solution of the Problem
To create the new controlfile for the new database you must use the word 'SET'
in the CREATE CONTROLFILE COMMAND. The SET DATABASE command is what enables the
changes to the name of the database. So the script will be like below
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "krishna" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\KRISHNA\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\KRISHNA\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\KRISHNA\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\KRISHNA\SYSTEM01.DBF',
'F:\ORACLE\KRISHNA\UNDOTBS01.DBF',
'F:\ORACLE\KRISHNA\SYSAUX01.DBF',
'F:\ORACLE\KRISHNA\USERS01.DBF',
'F:\ORACLE\KRISHNA\EXAMPLE01.DBF',
'F:\ORACLE\KRISHNA\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
Now run this will successfully run,
SQL> @f:\controlfile.ctl
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1247516 bytes
Variable Size 71304932 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Control file created.
This example is based on,
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if
you lose your all copies of controlfiles and you don't have any backup
of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/krishna/control01.ctl
/u01/krishna/control02.ctl
/u01/krishna/control03.ctl
2.Delete all copies of controlfile. Here with one command I have
deleted. But before deleting with one such command be conscious that
this command will not delete other important files. You can delete files
one by one.
SQL> !rm /u01/krishna/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have
previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use
that file and modify. If you don't have any backup of controlfile then
you have to create a new one as below. Note that you have to remember
the name of datafile and online redo log file.
-bash-3.00$ vi /u01/krishna/control_file.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "krishna" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/krishna/redo01.log' SIZE 50M,
GROUP 2 '/u01/krishna/redo02.log' SIZE 50M,
GROUP 3 '/u01/krishna/redo03.log' SIZE 50M
DATAFILE
'/u01/krishna/system01.dbf',
'/u01/krishna/undotbs01.dbf',
'/u01/krishna/sysaux01.dbf',
'/u01/krishna/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/u01/krishna/
control_file.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/krishna/temp01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 100 MAXSIZE 1024M;
