Database Reference
In-Depth Information
DISpLaYING the CONteNtS OF a CONtrOL FILe
you can use the
ALTER SESSION
statement to display the physical contents of the control file; for example,
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> alter session set events 'immediate trace name controlf level 9';
SQL> oradebug tracefile_name
the prior line of code displays the following name of the trace file:
/orahome/app/oracle/diag/rdbms/o12c/O12C/trace/O12C_ora_15545.trc
in oracle 11g and above, the trace file is written to the
$ADR_HOME/trace
directory. you can also view the trace
directory name via this query:
SQL> select value from v$diag_info where name='Diag Trace';
in oracle 10g and below, the trace directory is defined by the
USER_DUMP_DEST
initialization parameter. you can
inspect the contents of the control file when troubleshooting or when you're trying to gain a better understanding
of oracle internals.
Viewing Control File Names and Locations
If your database is in a nomount state, a mounted state, or an open state, you can view the names and locations of the
control files, as follows:
SQL> show parameter control_files
You can also view control file location and name information by querying the
V$CONTROLFILE
view. This query
works while your database is mounted or open:
SQL> select name from v$controlfile;
If, for some reason, you can't start your database at all, and you need to know the names and locations of the
control files, you can inspect the contents of the initialization (parameter) file to see where they're located. If you're
using an
spfile
, even though it's a binary file, you can still open it with a text editor. The safest approach is to make a
copy of the
spfile
and then inspect its contents with an OS editor:
$ cp $ORACLE_HOME/dbs/spfileO12C.ora $ORACLE_HOME/dbs/spfileO12C.copy
$ vi $ORACLE_HOME/dbs/spfileO12C.copy
You can also use the
strings
command to search for values in a binary file:
$ strings $ORACLE_HOME/dbs/spfileO12C.ora | grep -i control_files
