Skip to content

real life oracle dba scenarios – control file





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.

control file information

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

hii i am working on oracle 10g 10.2.0.4.0 on solaris 10 have asm and rac setup(2 node rac).

i have only one control file–+DATA_DG1/ftssdb/controlfile/current.270.664476369

i am backing up these control file with rman

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/rman_node1/%F’;

c-31850833-20100909-00 is a backed up piece of control file.

now suddenly one system admin deleted that control file…how can i recover my database using rman backup??

RMAN>SET DBID 3386862614
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
or
RMAN>SET DBID 3386862614
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO ‘/backup/rman_node1/%F’;
RESTORE CONTROLFILE FROM AUTOBACKUP;
}

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.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: -1 (from 1 vote)
real life oracle dba scenarios - control file, 10.0 out of 10 based on 1 rating

Post a Comment

You must be logged in to post a comment.