Where is Oracle Control File Location

  • by

Oracle Control File Location

We use different ways to find out the location of control file when the database is online and offline.

1. When DB is Online

Finding out the location of control file is pretty easy for an online database, no matter the database is NOMOUNT, MOUNT or OPEN.

Single-instance DB

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCLCD
                                                 B/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/ORCLCDB/
                                                 control02.ctl

RAC DB

[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/ORCLCDB/control01.ctl, +
                                                 DATA/ORCLCDB/control02.ctl, +D
                                                 ATA/ORCLCDB/control03.ctl

2. When DB is Idle

It would be a little trouble to find out the location of control file when the database is shutdown.

Single-instance DB

Since the database is shutdown, there's no instance in the server, so we have to inspect the parameter file (PFILE) instead, which is a plain-text file.

To get the most current PFILE, you can derive it from the server parameter file (SPFILE). If SPFILE is at the default location, you do it without specifying the location of SPFILE.

[oracle@ora19c1 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile' from spfile;

File created.

If SPFILE is not at the default location, you have to provide a valid path for the file creation. If you don't know, you need to know where to find SPFILE.

[oracle@ora19c1 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile' from spfile='/u02/spfile/spfileORCLCDB.ora';

File created.

Then we can find the control file location by a simple grep.

[oracle@ora19c1 ~]$ grep -i control_files /home/oracle/pfile
*.control_files='/u01/app/oracle/oradata/ORCLCDB/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl'

RAC DB

Let's see current status on both nodes.

[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is not running on node primary01
Instance ORCLCDB2 is not running on node primary02

Apparently, the database is not running. For creating a PFILE for later inspection, we should provide the location of SPFILE.

[oracle@primary01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile' from spfile='+DATA/ORCLCDB/spfile';

File created.

Then we can find the control file location by a simple grep.

[oracle@primary01 ~]$ grep -i control_files /home/oracle/pfile
*.control_files='+DATA/ORCLCDB/control01.ctl','+DATA/ORCLCDB/control02.ctl','+DATA/ORCLCDB/control03.ctl'

Now, we can conclude from the above approaches, finding the location of control files is absolutely related to SPFILE.

Leave a Reply

Your email address will not be published. Required fields are marked *