How to view the content in the Oracle11g Control File

How to view the content in the Oracle11g Control File

A Control File is one of Oracle physical files. It records the database name, data File location, and other information. The importance of the control file is that the database will go down once the control file is corrupted. The control file is a very small binary file. You cannot edit the control file. Oracle automatically modifies the control file. Can we view the specific information in the control file? The answer is yes. We can obtain detailed information about the control file by dumping the file.

1. Generate a dump file for the Control File

SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production
PL/SQL Release 11.2.0.3.0-Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0-Production
NLSRTL Version 11.2.0.3.0-Production

SQL> alter database backup controlfile to trace;

Database altered.

SQL>

2. Find the generated dump file
We know that the TRACE files generated by users are stored in the udump directory. In Oracle10g, The udump path is $ ORACLE_HOME/admin/SID/udump, while the Oracle11g path is quite different. You can view the user_dump_dest parameter to locate the specific location of the udump directory.

SQL> show parameter user_dump_dest

NAME TYPE VALUE
-----------------------------------------------------------------------------
User_dump_dest string/u01/app/oracle/diag/rdbms/bkjia/HOEGH/trace
SQL>
Then, we use the ls command to find the recently generated TRACE file, which is the bkjia_ora_304.trc file shown in the figure below.

[Oracle @ bkjia trace] $ ls-ltr

Total 1332
Three hundred words are omitted here
-Rw-r ----- 1 oracle oinstall 932 May 9 bkjia_mmon_4805.trm
-Rw-r ----- 1 oracle oinstall 9750 May 9 bkjia_mmon_4805.trc
-Rw-r ----- 1 oracle oinstall 4562 May 9 bkjia_dbrm_4789.trm
-Rw-r ----- 1 oracle oinstall 80534 May 9 bkjia_dbrm_4789.trc
-Rw-r ----- 1 oracle oinstall 97 May 9 21:29 bkjia_ora_304.trm
-Rw-r ----- 1 oracle oinstall 12786 May 9 bkjia_ora_304.trc
3. view the control file details
Now we can use the more command to view the details of the control file.

[Oracle @ bkjia trace] $ more bkjia_ora_304.trc

Trace file/u01/app/oracle/diag/rdbms/bkjia/HOEGH/trace/bkjia_ora_304.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: bkjia.example.com
Release: 2.6.18-164. el5PAE
Version: #1 SMP Thu Sep 3 02:28:20 EDT 2009
Machine: i686
VM name: VMWare Version: 6
Instance name: bkjia
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 304, image: oracle@bkjia.example.com (TNS V1-V3)

* ** 21:29:07. 246
-- The following are current System-scope REDO Log Archival related
-- Parameters and can be encoded in the database initialization file.
--
-- LOG_ARCHIVE_DEST =''
-- LOG_ARCHIVE_DUPLEX_DEST =''
--
-- LOG_ARCHIVE_FORMAT = % t _ % s _ % r. dbf
--
-- DB_UNIQUE_NAME = "bkjia"
--
-- LOG_ARCHIVE_CONFIG = 'send, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES = 4
-- STANDBY_FILE_MANAGEMENT = MANUAL
-- STANDBY_ARCHIVE_DEST =? /Dbs/arch
-- FAL_CLIENT =''
-- FAL_SERVER =''
--
-- LOG_ARCHIVE_DEST_1 = 'location =/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
'

-- LOG_ARCHIVE_DEST_1 = 'mandatory NOREOPEN nodelay'

-- LOG_ARCHIVE_DEST_1 = 'arch noaffrem expedite noverify sync'

-- LOG_ARCHIVE_DEST_1 = 'noregister noalternate nodependency'

-- LOG_ARCHIVE_DEST_1 = 'nomax _ FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1 = 'valid _ FOR = (PRIMARY_ROLE, ONLINE_LOGFILES )'

-- LOG_ARCHIVE_DEST_STATE_1 = ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- Control file and uses it to open the database. The first set opens
-- The database with the NORESETLOGS option and shoshould be used only if
-- The current versions of all online logs are available. The second
-- Set opens the database with the RESETLOGS option and shocould be used
-- If online logs are unavailable.
-- The appropriate set of statements can be copied from the trace
-- A script file, edited as necessary, and executed when there is
-- Need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- To open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- Available.
-- After mounting the created controlfile, the following SQL
-- Statement will place the database in the appropriate
-- Protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
Create controlfile reuse database "bkjia" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app/oracle/oradata/bkjia/redo01.log 'SIZE 50 m blocksize 512,

GROUP 2'/u01/app/oracle/oradata/bkjia/redo02.log 'SIZE 50 m blocksize 512,

GROUP 3 '/u01/app/oracle/oradata/bkjia/redo03.log' SIZE 50 m blocksize 512

-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/bkjia/system01.dbf ',

'/U01/app/oracle/oradata/bkjia/sysaux01.dbf ',

'/U01/app/oracle/oradata/bkjia/undotbs01.dbf ',

'/U01/app/oracle/oradata/bkjia/users01.dbf ',

'/U01/app/oracle/oradata/bkjia/example01.dbf ',

'/U01/app/oracle/oradata/bkjia/test01.dbf'

Character set AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- Disk. Any one log file from each branch can be used
-- Re-create incarnation records.
-- Alter database register logfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Arch1_2017762197622.dbf ';

-- Alter database register logfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Archive ipv860888149.dbf ';

-- Recovery is required if any of the datafiles are restored backups,
-- Or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
Alter database open;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
Alter tablespace temp add tempfile '/u01/app/oracle/oradata/bkjia/temp01.dbf'

SIZE 30408704 reuse autoextend on next 655360 MAXSIZE 32767 M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- To open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- Be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- Statement will place the database in the appropriate
-- Protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
Create controlfile reuse database "bkjia" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app/oracle/oradata/bkjia/redo01.log 'SIZE 50 m blocksize 512,

GROUP 2'/u01/app/oracle/oradata/bkjia/redo02.log 'SIZE 50 m blocksize 512,

GROUP 3 '/u01/app/oracle/oradata/bkjia/redo03.log' SIZE 50 m blocksize 512

-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/bkjia/system01.dbf ',

'/U01/app/oracle/oradata/bkjia/sysaux01.dbf ',

'/U01/app/oracle/oradata/bkjia/undotbs01.dbf ',

'/U01/app/oracle/oradata/bkjia/users01.dbf ',

'/U01/app/oracle/oradata/bkjia/example01.dbf ',

'/U01/app/oracle/oradata/bkjia/test01.dbf'

Character set AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- Disk. Any one log file from each branch can be used
-- Re-create incarnation records.
-- Alter database register logfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Arch1_2017762197622.dbf ';

-- Alter database register logfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Archive ipv860888149.dbf ';

-- Recovery is required if any of the datafiles are restored backups,
-- Or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
Alter database open resetlogs;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
Alter tablespace temp add tempfile '/u01/app/oracle/oradata/bkjia/temp01.dbf

We can see that the dump file contains the database name, the location of the data file, and the SQL statement for creating the control file according to the need for resetlogs. The control file is very important. We can back up it just in case.