If you are working for Financial/Banking domain, you will have a Guidelines given by the Auditors and doesn’t matter if you are restoring a fresh database or making a copy of it. You need to follow it. So recently we had a request to restore a database for PUC purpose, but since it was on a production server we had to abide the rules and guidelines given by the Auditors. Hence we were forced to Multiplex Controlfile in Oracle RAC 19c database which we restored.
Table of Contents
What Multiplexing Controlfile or Redolog Means ?
In layman Language, if a file is important for you, you will start creating multiple copies of it. And you will try to keep them on different medias/ storage. This is called multiplexing. Same way we make a copy of it. The only difference is that All the files will be used by Oracle.
Why to Multiplex Controlfile or Redologs
Reason remain same, point in time recovery in case of any disaster like Corruption, or one storage associated with the database server goes offline and storage team can’t bring it up. Having two members give you chance to recover the database upto 99.9999%
Multiplexing of Redo log and Controlfile Requires Downtime ?
For Primary Database – Multiplexing Redo log doesn’t require downtime. Multiplexing Controlfile requires complete downtime of the database as
1. To update a static parameter in Oracle, you require database bounce.
2. For making a consistent copy you require downtime.
For Standby Database which is in Mount Stage– You need to stop the MRP ( media recovery process) of database before you start redo log multiplexing.
For Active Standby database – If you have active standby data-guard in your environment then there are chances that the database is used to reporting. Hence you might need to inform application team for the downtime for Redo log multiplexing as well As you have to stop the MRP while adding redo log members.
Let’s get started with step by step to Multiplex Controlfile in Oracle RAC 19c.
About My Setup
OS – AIX
Oracle Version – 19c with April 21 PSU
DB State – Physical Standby
Multiplex Controlfile in Oracle RAC 19c.
Step 1 – Backup current controlfile before starting the activity.
oracle@oracledbworld/DBHOME/oracle$rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 27 03:01:49 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: oracledbworld (DBID=4220483413)
RMAN> run
{
allocate channel ch01 type disk;
backup current controlfile for standby format '/oradump/oracledbworld/before_controlfile_multiplexing_sso.ctl';
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=11131 instance=oracledbworld1 device type=DISK
Starting backup at 27-APR-22
channel ch01: starting full datafile backup set
channel ch01: specifying datafile(s) in backup set
including standby control file in backup set
channel ch01: starting piece 1 at 27-APR-22
channel ch01: finished piece 1 at 27-APR-22
piece handle=/oradump/oracledbworld/before_controlfile_multiplexing.ctl tag=TAG20220427T030202 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 27-APR-22
Starting Control File and SPFILE Autobackup at 27-APR-22
piece handle=/DBHOME/oracle/app/product/19.3.0/dbs/c-4220483413-20220427-00 comment=NONE
Finished Control File and SPFILE Autobackup at 27-APR-22
released channel: ch01
RMAN> exit
Recovery Manager complete.
Step 2 We have to update the control_files parameter in spfile/pfile
oracle@oracledbworld/DBHOME/oracle$sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 27 03:02:46 2022 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> alter system set control_files='+DATA_oracledbworld/oracledbworlddr_p9/controlfile/current.824.1029941897','+REDO3_oracledbworld' scope=spfile sid='*'; System altered. SQL>
Step 3 Stop the database and open it in nomount
oracle@oracledbworld/DBHOME/oracle$srvctl status database -d oracledbworld -v Instance oracledbworld1 is running on node oracledbworld. Instance status: Open,Readonly. Instance oracledbworld2 is running on node oracledbworld2. Instance status: Open,Readonly. oracle@oracledbworld/DBHOME/oracle$
oracle@oracledbworld/DBHOME/oracle$srvctl stop database -d oracledbworld
oracle@oracledbworld/DBHOME/oracle$srvctl start instance -d oracledbworld -i oracledbworld1 -o nomount
oracle@oracledbworld/DBHOME/oracle$srvctl status database -d oracledbworld -v Instance oracledbworld1 is running on node oracledbworld. Instance status: Dismounted. Instance oracledbworld2 is not running on node oracledbworld2 oracle@oracledbworld/DBHOME/oracle$
Step 4 Restore the controlfile from existing one
We will check if control_files parameter is updated correctly or not –
SQL> def
DEFINE _DATE = "27-04-2022" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oracledbworld1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1911000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1911000000" (CHAR)
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA_oracledbworld/oracledbworlddr_p9/c
ontrolfile/current.824.1029941
897, +REDO3_oracledbworld
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
Then we will restore the controlfile as follows –
oracle@oracledbworld/DBHOME/oracle$rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 27 03:06:10 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: oracledbworld (not mounted)
RMAN> run
{
allocate channel ch01 type disk;
restore controlfile from '+DATA_oracledbworld/oracledbworlddr_p9/controlfile/current.824.1029941897';
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=6427 instance=oracledbworld1 device type=DISK
Starting restore at 27-APR-22
channel ch01: copied control file copy
output file name=+DATA_oracledbworld/oracledbworlddr_p9/controlfile/current.824.1029941897
output file name=+REDO3_oracledbworld/oracledbworldDR_P9/CONTROLFILE/current.308.1103079991
Finished restore at 27-APR-22
released channel: ch01
RMAN>
Note down the control file name from above output.
Step 5 Update control_files parameter and restart the database
Lets update control_files parameter in spfile/pfile and restart the database.
oracle@oracledbworld/DBHOME/oracle$sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 27 03:09:21 2022 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> alter system set control_files='+DATA_oracledbworld/oracledbworlddr_p9/controlfile/current.824.1029941897','+REDO3_oracledbworld/oracledbworldDR_P9/CONTROLFILE/current.308.1103079991' scope=spfile sid='*'; System altered. SQL>
oracle@oracledbworld/DBHOME/oracle$srvctl status database -d oracledbworld -v Instance oracledbworld1 is running on node oracledbworld. Instance status: Dismounted. Instance oracledbworld2 is not running on node oracledbworld2 oracle@oracledbworld/DBHOME/oracle$
oracle@oracledbworld/DBHOME/oracle$srvctl stop database -d oracledbworld
oracle@oracledbworld/DBHOME/oracle$srvctl start database -d oracledbworld -o "read only"
Final Check –
SQL> def DEFINE _DATE = "27-04-2022" (CHAR) DEFINE _CONNECT_IDENTIFIER = "oracledbworld1" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1911000000" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0" (CHAR) DEFINE _O_RELEASE = "1911000000" (CHAR) SQL> select name,open_mode,database_role from v$database; select name from v$controlfile; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- oracledbworld READ ONLY PHYSICAL STANDBY SQL> NAME -------------------------------------------------------------------------------- +DATA_oracledbworld/oracledbworlddr_p9/controlfile/current.824.1029941897 +ARCH/oracledbworlddr_p9/controlfile/current.256.1029941897 +REDO3_oracledbworld/oracledbworldDR_P9/CONTROLFILE/current.308.1103079991 SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 oracle@oracledbworld/DBHOME/oracle$
Thanks for reading!