ORA-00207 controlfiles are not for the same database

Cause: The database ID in the control file is not the same as the database ID in the control file used by the first instance to mount this database. Most likely one of the mounts used the wrong control file or there are two databases with the same name.

Action: Check that the control file is for the correct database and is not an old
version. When using multiplexed control files, that is, more than one control file
is referenced in the initialization parameter file, remove the control file name
listed in the message from the initialization parameter file and restart the
instance. If the message does not recur, remove the problem control file from the
initialization parameter file and create another copy of the control file using a
new file name in the initialization parameter file

Read More

Solution of Inconsistent Control File

Inconsistent error occur when Oracle detects an inconsistency between the multiplex copies of the control file. All copies of the control file must have the same internal sequence number Typical scenarios in which you may receive this inconsistencu error include:

1. You have restored the control file from backup, but you have forgot to copy it to other locations 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 someone overwrote one of the copies of the control file with an old copy.

4. You are restoring a database backup that was improperly taken with the database up and running.


To get the solution of these problem start your database with single copy of the control file and then shut the database down and then copy of this good copy of control file to the other locations as listed in the control_files parameter.
Following these steps to solve this problem:

Step 1.  If database is still up, shutdown abort.

Step 2. If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it with one copy of control file.

If you use spfile then after 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 3. startup restrict

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 4.  Shutdown the database.
SQL>shutdown

Step 5. Copy this control file and locate to the different locations.

Step 6. Edit the init.ora file CONTROL_FILES parameter to include all location of the copy control files again.

Step 7. Startup  database.

Read More

How To Create New Control File

As you know control file records the information about the physical structure of the database.It is very crucial file it is veru small binary file, So thats why we create multiplexing of control file and we back up control file time to time.But sometime when all control file have been damaged then we need to create new control file as follow:

CREATE CONTROLFILE
   SET DATABASE luck
   LOGFILE GROUP 1 ('/opt/luck/redo01.log',
                                  '/opt/luck/redo02.log'),
           GROUP 2 ('/opt/luck/gro/redo01.log',
                           '/u01/luck/gro/redo02.log'),         
   NORESETLOGS
   DATAFILE '/opt/luck/system.dbf' SIZE 3M,
                   '/opt/luck/sysaux.dbs' SIZE 5M,
                   '/opt/luck/user.dbs' SIZE 5M,
                   '/opt/luck/temp.dbs' SIZE 5M

   MAXLOGFILES 30
   MAXLOGMEMBERS 2
   MAXLOGHISTORY 200
   MAXDATAFILES 150
   MAXINSTANCES 6
   ARCHIVELOG;

Here are some steps, how to create new control file or open your database when your all control files have been damaged :

1) Make a list of all datafiles and online redo log files of the database. Execute the following statements which will provide you information about your datafiles and online redo log files:
Select member from v$logfile;
select name from v$datafile;
Selecr value from v$parameter where name='CONTROL_FILES';

2) Shutdown the database.

3) Back up all datafiles and online redo log files of the databsae.

4) Start up a new instance at nomount stage

5) Create a new control file as we done above in this post.
In above as you know we used NORESETLOGS in control file creation. When creating a new control file select RESETLOGS option if you lost any online redo log group and with this option you will need to recover of the redo logs. Otherwise, select the NORESETLOGS option.

6) Store a backup of the new control file.

7) Edit the control_files intialization parameter for the database to indicate control file now part of your database as created in step5

8) Recover the database if you are using RESETLOGS option.

9) Open the database
Alter database open;

Read More

Control file Backup

It is very important that you back up your control file, Bacause the control file records the physical structure of the database,you should make a backup of your control file after making changes to the physical structure of the database. You can create backup of control file by using this statement:

 
Alter database backup controlfile '/opt/nit/conback.ctl'

You can also backup of a control file to trace file.

Alter database backup controlfile to trace
 

You backup a control file after any change the physical structure of your database. Such includes as:

• Adding, dropping or renaming datafiles
• Adding or dropping redo log files or group
• Adding or dropping a tablespace 

  You can obtain control file information using these views as follows:

• V$CONTROLFILE
• V$PARAMETER
• V$CONTROLFILE_RECORD_SECTION
• SHOW PARAMETERS CONTROL_FILES

Read More

Multiplexing Control File

Control file is a small binary file which includes information like database name, name and location of data files and redo log files, timestamp of database creation,log sequence number and RMAN backup information if we use RMAN. Control file include these all information so thats "Why we need multiplexing of control file". When control file is damaged ther is no way you can bring it back unless you have the backup.So, oracle database should have atleast two control files. You should have backup of control file and backup of control files must be stored in a different physical disk.If a control file is damaged due to a disl failure, the damaged control file can be restored using the copy of the control file from the other disk and instance can be restarted. The steps for multiplexing control file are as follows:

1) Conn as SYSDBA
2) shutdown the database immediate
3) Open the directory look for control file 'control.ctl'
4) Create duplicate copy of 'control.ctl' ,then rename of the duplicate control file 'control01.ctl'
5) Open the initialization paramter file 'initSID.ora', copy the location of duplicate control file and paste it in the control file configuration:

control file '/opt/luck/control.ctl',
             '/opt/luck/control01.ctl'
 

6) Startup database

SQL>startup pfile='/opt/luck/initluck.ora' or you can start with spfile
 

7) check your control file

SQL>select name from v$controlfile;
Name
-------------
/opt/luck/control.ctl
/opt/luck/control01.ctl

Read More

Create Control File

when you issue the create database statement at the time of the database creation at that time control file of an oracle database is created. The name of the control file is defined by the CONTROL_FILES parameter in the pfile initialization parameter file. Example of a control file initialization parameter is as follows:

Control_files '/opt/luck/con01.ctl',

              '/opt/luck/con02.ctl'

If files names currently exist at the time of database creation, you must specify the Control Reuse clause in the CREATE DATABASE statement. The size of the control file is influenced by the following keywords in the create database or create control commands:

• MAXLOGFILES
• MAXLOGMEMBERS
• MAXLOGHISTORY
• MAXDATAFILES
• MAXINSTANCES

Read More

What is Control file

The control file is a small binary file necessary for the database to start and operate successfully. Control file records the physical structure of the database.Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use.The information in the control file can be modified only by the Oracle server.The information in the control file includes:

• Database name
• Names and locations of associated datafiles and online redo log files
• The timestamp of database creation
• Current log sequence number
• Checkpoint information
• Tablespace information
• Datafile offline ranges
• Redo log history
• Archived log information
• RMAN information means (database backup taken through RMAN)
• Datafile copy information.

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

Read More