Multiplexing of Control Files Using RMAN, SPFILE & PFILE in Oracle

March 13, 2021
()

Multiplexing of Control Files Using RMAN, SPFILE & PFILE in Oracle

Here we are going to demonstrate multiplexing of Control Files in Oracle using available options SPFILE, PFILE & RMAN. Before moving ahead first we will have a basic understanding of Control Files in Oracle.


Control File
is a physical file on OS level and very lightweight binary file for the database, minimum of one control file require which contains all physical structure records of the database. Below are major information captured in the control file which is used to start/open Oracle Database.

  • Database creation Timestamp
  • Database name
  • Name and location of all datafiles, redo logfiles associated with the database.
  • Log sequence number (SCN)
  • Checkpoint information 

As we know very well minimum of one control file requires while database creation. But what about if we have only one control file and due to any reason that the control file corrupted or the location of the control file (mount point) is not accessible so it’s a potential outage for business or if we don’t have a backup strategy for control file then lost.


To avoid this situation, it’s always recommended to multiplex (minimum two or more than two control files) your control files to a different location i.e different mount points/directory. Here we will multiplex control files to two different mount points (/u01 & /u02) using available options SPFILE, PFILE & RMAN and all available options require minimal downtime to take in the effect of multiplex control files.

 

1. SPFILE
2. PFILE
3. RMAN

1. SPFILE

Step1.1: Validate existing control files.
Here we have two control files in the same mount point /u01.

[oracle@ol6-112-lab1 ~]$ . oraenv
ORACLE_SID = [suncdb] ? labdb01
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ol6-112-lab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 13 13:47:00 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/labdb0
                                                 1/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/labdb01/
                                                 control02.ctl
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl


Step 1.2: Add new control file to SPFILE
Here we will add a new control file “/u02/labdb01/milti_ctl_spfile/control03.ctl” to SPFILE.

SQL> alter system set control_files= '/u01/app/oracle/oradata/labdb01/control01.ctl', '/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl', '/u02/labdb01/milti_ctl_spfile/control03.ctl' scope=spfile;

System altered.


Step 1.3: Stop database.
To take effect of the new control file in the database we need to restart the database since it’s not modifiable on run time.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Step 1.4: Copy the control file to a new location.
This is a very important step to copy any existing control file to a new location and make sure the database is clean shutdown. Since we are already aware that the control file contains all physical structures so it requires copying in shutdown mode so that all control files have the same information. If any-one control file has mismatch information among all control files your database never will come online in open mode.

[oracle@ol6-112-lab1 ~]$ cp /u01/app/oracle/oradata/labdb01/control01.ctl /u02/labdb01/milti_ctl_spfile/control03.ctl
[oracle@ol6-112-lab1 ~]$ ls -lrt /u02/labdb01/milti_ctl_spfile/control03.ctl
-rw-r----- 1 oracle oinstall 10076160 Mar 13 14:18 /u02/labdb01/milti_ctl_spfile/control03.ctl


Step 1.5: Start the database in normal mode.

SQL> startup;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             322964208 bytes
Database Buffers          507510784 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.


Step 1.6: Validate control files.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/labdb0
                                                 1/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/labdb01/
                                                 control02.ctl, /u02/labdb01/mi
                                                 lti_ctl_spfile/control03.ctl
SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
/u02/labdb01/milti_ctl_spfile/control03.ctl

2. PFILE

Step2.1: Validate existing control files.
Here we have now three control files in two different mount points.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/labdb0
                                                 1/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/labdb01/
                                                 control02.ctl, /u02/labdb01/mi
                                                 lti_ctl_spfile/control03.ctl
SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
/u02/labdb01/milti_ctl_spfile/control03.ctl


Step 2.2: Create PFILE using SPFILE.

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

File created.


Step 2.3: Prepare PFILE adding the new control file.
Edit PFILE in vi editor mode and add new control file name “/u02/labdb01/milti_ctl_pfile/control04.ctl”. We are doing here before stopping the database to reduce the downtime.

[oracle@ol6-112-lab1 ~]$ cat /home/oracle/initlabdb01.ora | grep -i control
*.control_files='/u01/app/oracle/oradata/labdb01/control01.ctl','/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl','/u02/labdb01/milti_ctl_spfile/control03.ctl','/u02/labdb01/milti_ctl_pfile/control04.ctl'  ==> Before edit
[oracle@ol6-112-lab1 ~]$ vi /home/oracle/initlabdb01.ora
[oracle@ol6-112-lab1 ~]$ cat /home/oracle/initlabdb01.ora | grep -i control
*.control_files='/u01/app/oracle/oradata/labdb01/control01.ctl','/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl','/u02/labdb01/milti_ctl_spfile/control03.ctl','/u02/labdb01/milti_ctl_pfile/control04.ctl'  ==> After edit


Step 2.4: Stop database.
Require to stop the database to copy the control file to a new location.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Step 2.5: Copy the control file to a new location.
This is a very important step to copy any existing control file to a new location and make sure the database is clean shutdown. Since we are already aware that the control file contains all physical structures so it requires copying in shutdown mode so that all control files have the same information. If any-one control file has mismatch information among control files your database never will come online in open mode.

[oracle@ol6-112-lab1 ~]$ cp /u01/app/oracle/oradata/labdb01/control01.ctl /u02/labdb01/milti_ctl_pfile/control04.ctl
[oracle@ol6-112-lab1 ~]$ ls -lrt /u02/labdb01/milti_ctl_pfile/control04.ctl
-rw-r----- 1 oracle oinstall 10076160 Mar 13 14:30 /u02/labdb01/milti_ctl_pfile/control04.ctl


Step 2.6: Start the database using pfile.
In this step, we will start the database using PFILE which we prepared in Step 2.3.

SQL> startup pfile='/home/oracle/initlabdb01.ora'
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             322964208 bytes
Database Buffers          507510784 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.


Step 2.7: Validate control files.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/labdb0
                                                 1/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/labdb01/
                                                 control02.ctl, /u02/labdb01/mi
                                                 lti_ctl_spfile/control03.ctl,
                                                 /u02/labdb01/milti_ctl_pfile/c
                                                 ontrol04.ctl
SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
/u02/labdb01/milti_ctl_spfile/control03.ctl
/u02/labdb01/milti_ctl_pfile/control04.ctl


Step 2.8: Prepare SPFILE and restart the database.
Since we have started the database using PFILE so we will create an SPFILE using the same PFILE and take effect of the newly created SPFILE on the next database startup.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/home/oracle/initlabdb01.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             322964208 bytes
Database Buffers          507510784 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.


Step 2.9: Validate control files.
This is the last & final step where the value of SPFILE along with the new control file should be visible.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /db_1/dbs/spfilelabdb01.ora
SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
/u02/labdb01/milti_ctl_spfile/control03.ctl
/u02/labdb01/milti_ctl_pfile/control04.ctl


3. RMAN

Step3.1: Validate existing control files.
Here we have now four control files in two different mount points.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/labdb0
                                                 1/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/labdb01/
                                                 control02.ctl, /u02/labdb01/mi
                                                 lti_ctl_spfile/control03.ctl,
                                                 /u02/labdb01/milti_ctl_pfile/c
                                                 ontrol04.ctl
SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
/u02/labdb01/milti_ctl_spfile/control03.ctl
/u02/labdb01/milti_ctl_pfile/control04.ctl


Step 3.2: Add new control file to SPFILE
Here we will add a new control file “/u02/labdb01/milti_ctl_rman/control05.ctl” to SPFILE.

SQL> alter system set control_files= '/u01/app/oracle/oradata/labdb01/control01.ctl', '/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl', '/u02/labdb01/milti_ctl_spfile/control03.ctl', '/u02/labdb01/milti_ctl_pfile/control04.ctl', '/u02/labdb01/milti_ctl_rman/control05.ctl' scope=spfile;

System altered.


Step 3.3: Stop the database and start in NOMOUNT state.
In this step stop the database and start in the NOMOUNT state due to we are going to restore using the RMAN command.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             322964208 bytes
Database Buffers          507510784 bytes
Redo Buffers                5455872 bytes


Step 3.4: Restore the control file using the RMAN command.
Connect to RMAN and restore the control file. Here we can use any one valid control file in RMAN which will restore control files to all desire locations as we executed in Step 3.2.

[oracle@ol6-112-lab1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Mar 13 14:46:34 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LABDB01 (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/oradata/labdb01/control01.ctl';

Starting restore at 13-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=249 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/labdb01/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
output file name=/u02/labdb01/milti_ctl_spfile/control03.ctl
output file name=/u02/labdb01/milti_ctl_pfile/control04.ctl
output file name=/u02/labdb01/milti_ctl_rman/control05.ctl
Finished restore at 13-MAR-21


Step 3.5: Alter database in OPEN mode or restart the database.
In this step, we will open the database from NOMOUNT to MOUNT then OPEN. Alternatively, you can stop the instance and do normal start the database.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.


Step 3.6: Validate control files.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/labdb0
                                                 1/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/labdb01/
                                                 control02.ctl, /u02/labdb01/mi
                                                 lti_ctl_spfile/control03.ctl,
                                                 /u02/labdb01/milti_ctl_pfile/c
                                                 ontrol04.ctl, /u02/labdb01/mil
                                                 ti_ctl_rman/control05.ctl
SQL>  select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/labdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/labdb01/control02.ctl
/u02/labdb01/milti_ctl_spfile/control03.ctl
/u02/labdb01/milti_ctl_pfile/control04.ctl
/u02/labdb01/milti_ctl_rman/control05.ctl


NOTE: Optionally you can create PFILE end of activities. Always recommended to create a PFILE to a non-default location before start activities and create in default location after completion of activities in case of changes in the database’s parameter/s.

SQL> create pfile from spfile;

File created.

[oracle@ol6-112-lab1 ~]$ ls -lrt $ORACLE_HOME/dbs/initlabdb01.ora
-rw-r--r-- 1 oracle oinstall 1337 Mar 13 14:49 /u01/app/oracle/product/12.1.0/db_1/dbs/initlabdb01.ora

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!

Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

1 Comments

Leave a Reply

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