First see below the oracle views where you can get more information about the control file and then we will go on to review some real life scenarios where we use the control file.
scenario 1
I wanted to see how my control file looks. This is because i wanted to create a test database from scratch and wanted to use the controlfile copy of another database.so i executed the below command in sqlplus after logging in as sysdba.
alter datababse backup controlfile to trace;
now i could see the trace file in the udump directory.
or if you want to get the control file into a directory of your choice then you can use this command
alter database backup controlfile to trace ‘/some/arbitrary/path’;
scenario 2
we specified our control file parameters in our init.ora file.our database is up and running .we are running on a unix server. our sysadmin accidentally deleted one of the control file.Oracle already has a copy of this file in the servers memory so it did not immediately give a problem. over the weekend we had some maintenance and we restarted the database. Then the database failed to startup giving an error that control file is missing.then we did the following to solve this error.
1)cleanly shutdown the database.
2)since we have multiple controlfile file copies and this information i saw in the init.ora file of my database.
3)so i copied one control file copy into the destination from where the sysadmin accidentally deleted the file.
4)Then i started the database and the database came up cleanly.
you can see some more discussion about this topic on the oracle forums here
scenario 3
We had a test database . This database had only one control file.That was a mistake because normally it is best practice to define 3 control file copies in the init.ora file.
So one fine day our control file got deleted accidentally and the database is not coming up.so we performed the below activities to bring up the database.
fortunately in our daily backups we run the backup control file to trace command and i could see the file in our udump directory.
so important point to remember is that if you have only one control file and that is damaged then you need to have the backup control file that has been created using the backup controlfile to trace command.
1)
sql>startup nomount
sql>execute the controlfile script from the udump that you found above.
sql> recover database using backup controlfile;
sqL. alter database open resetlogs;
Note: datafile header scn will be stamped to controlfile a
ok now you know how to recover your database when you have only one control file and when the control file is corrupted.
what will you do if you dont have the control file backup that was created with the backup controlfile to trace command?then use some other database and create a dummy controlfile copy using the backup controlfile to trace command and then modify that file so the datafile names and other file names are exactly the same as your current database. below are the steps.
If no backup control file trace available, then create perform the below action plan
Any other database
sqL> ALTER DATABASE BACKUP CONTROLFILE TO trace;
sql> show dump
check in udump
now you need edit the controlfile file and make file setting etc.so the file names should be exactly same as in your database… you can use alert log for the same
sql>execute the script
sql> recover database using backup controlfile;
sqL. alter database open resetlogs;
scenario 4
in the initial days of my dba career, one day i ran thefollowing command
SQL>alter database backup controlfile to trace;
now i knew that this file would be created in the udump directory so find out about my udump directory i ran the following command
SQL>show parameter user_dump_dest
this gave me the path where i could find the control file copy. i went to the path but i could see lot of trace files in this directory but i did not know which one contained my control file information.so my colleague told me that the file with the latest timestamp is the one.so to avoid this confusion these days i normally use below command to create the backup control file. you can replace the path in below command with any path that you prefer and as you can see i am specifying my own name for the trace file.
alter database backup controlfile to trace as ‘c:\cont.txt’
scenario 5
This is from oracle forums.
| rman backup control file Posted: 09.09.2010 04:04 |
||||
|
||||
scenario 5
until now we saw various scenarios on how to restore a control file from the backup control file. one important thing to remember is
the command alter database backup controlfile to trace creates a file and it has similar text as below
CREATE CONTROLFILE SET DATABASE “MDB” RESETLOGS
MAXLOGFILES 20
MAXLOGMEMBERS 4
MAXDATAFILES 22
MAXINSTANCES 2
MAXLOGHISTORY 292
DATAFILE
but make sure there are no blanklines between the lines.otherwise the script will fail when you run the creation statement.
more info in this metalink article How to Recreate a Controlfile [ID 735106.1]. below is some part of the information from the metalink article.
It is important to delete everything above the “CREATE CONTROLFILE” and everything after the CHARACTER SET. Ensure you leave the semi colon. “;”.In the above example we are choosing the NORESETLOGS option and running the database in archivelog mode. After successfully saving the script you are now able to recreate the controlfile. When shutting down the database ensure that you shutdown with the immediate option.
