If all else fails and you do not have a backup control file, don’t worry; you have another option, the create controlfile command. Normally, manually executing the command can be challenging because you need to know a lot of information about your database (like the names and locations of all the database datafiles). However, you can prepare for the possibility of having to use the create controlfile command by creating one in advance.
The alter database backup controlfile to trace command will create a trace file with the create controlfile command in it for you. The trace file is stored in the new diagnostic directory structure in Oracle Database 11g. The diagnostic directory structure is a new standard introduced in Oracle Database 11g that defines where Oracle stores files related to database troubleshooting and diagnostics.The base directory of this structure is defined by the parameter DIAGNOSTIC_DEST. Here is an example of the setting of DIAGNOSTIC_DEST on an Oracle database:
SQL> show parameter diag
NAME TYPE VALUE
———————————— ———– ———
diagnostic_dest string C:\ORACLE
A whole book could be written on the new 11g diagnostic capabilities, but what we are interested in is where user-generated trace files get created, because when we issue the alter database backup controlfile to trace command, the resulting file will be a usergenerated trace file.
In this case, the trace file will be created in $DIAGNOSTIC_BASE\diag\rdbms\orcl\orcl\trace as shown in this code example:
SQL> alter database backup controlfile to trace;
Database altered.
C:\oracle\diag\rdbms\orcl\orcl\trace>dir
Volume in drive C has no label.
Volume Serial Number is 08DE-E1AB
Directory of C:\oracle\diag\rdbms\orcl\orcl\trace
08/02/2008 03:38 PM <DIR> .
08/02/2008 03:38 PM <DIR> ..
08/02/2008 03:38 PM 1,027,520 alert_orcl.log
08/02/2008 03:38 PM 9,572 orcl_ora_12120.trc
08/02/2008 03:38 PM 91 orcl_ora_12120.trm
4 File(s) 1,037,183 bytes
4 Dir(s) 9,964,507,136 bytes free
The trace file is called orcl_ora_12120.trc (it’s easy to tell since there are no other trace files in the directory). Another option with the alter database backup controlfile to trace command is to define an alternate location for the trace file. The syntax for this command is as follows:
alter database backup controlfile to trace as ’/tmp/my_control_trace.trc’;
If you look in the file, you will find a trace-file header in it first. Later down the trace file you will find two different versions of the create controlfile command. Here is an example of the create control file command that you might find in this file:
CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:\ORACLE\ORADATA\ORCL\REDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:\ORACLE\ORADATA\ORCL\REDO02.LOG’ SIZE 50M,
GROUP 3 (
‘C:\ORACLE\ORADATA\ORCL\REDO03A.LOG’,
‘C:\ORACLE\ORADATA\ORCL\REDO03B.LOG’
) SIZE 100M
— STANDBY LOGFILE
DATAFILE
‘C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF’,
‘C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF’,
‘C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF’,
‘C:\ORACLE\ORADATA\ORCL\USERS01.DBF’,
‘C:\ORACLE\ORADATA\ORCL\REVEAL_DATA_01.DBF’,
‘C:\ORACLE\ORADATA\ORCL\REVEAL_INDEX_01.DBF’,
‘C:\ORACLE\ORADATA\ORCL\USERS02.DBF’
CHARACTER SET WE8MSWIN1252;
You will notice that this output includes the datafile names, the location and names of the online redo logs, and other information needed by the create controlfile command. The trace file contains other output that will be required to complete the recovery process,so you should back up the trace file as it is. In Chapter 3 we will address the process of recovering from a control-file loss using the output contained in the trace files.
asiandba 