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.