How Do I Read Control File

The third most asked question in is “How Do I Read a Control File” in Oracle Database? Since the control file is a binary file thus it’s not directly accessible to us. In order to read its content, we need a way to decrypt it into a user-readable file. And there are two ways of doing that. Let’s start with the first one which is –

Backup Control file to Trace.

The easiest way to read a control file is to alter the database to backup the control file to trace. Like this-

Step 1- Connect With the database using the SYS user

C> SQLPLUS / as SYSDBA

Step 2- Write Alter Database Statement

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:\\trace_cf.txt';

On successful execution this statement will create a user-readable version of the Control file and save it into my D: drive with the name ‘trace_cf.txt’. You can change this name and location according to your preference.

Now let’s check out the second way to read a control file in Oracle.

The Oradebug utility

The second way to read a control file is through the ORADEBUG utility of the Oracle Database. Let me show you the process-

Step 1- Connect With the database using the SYS user

C>SQLPLUS / as SYSDBA

Step 2- Debug the current process

SQL> ORADEBUG setmypid;

Step 3- Set the size of the trace file to unlimited

SQ>ORADEBUG UNLIMIT;

By default, the size of the trace file is not set to unlimited. We have to do it manually

Step 4- Use the alter session statement to display the physical content of the control file.

SQL> alter session set events 'immediate trace name controlf level 9';

In the end, we need the name and location of the trace file into which the ORADEBUG utility saved the control file content and we can find that like this –

SQL> oradebug tracefile_name

On successful execution, you will have the name and location of the trace file which has all the content of the control file in a user-readable format.

That is how you check what control file contains in Oracle Database. Do make sure to subscribe to my YouTube channel. Since next we will be learning how to multiplex control files in Oracle Database.

Thanks and have a great day.