Step-by-step Multiplex Oracle 19c Controlfile in ASM
#oracle #ASM #controlfile #multiplexing #oracle19c
It is a best practice for any database to have controlfile as well as redolog file to be multiplexed .
This helps during any disk failure to keep one copy of the files safely .
So let's see how to multiplex a control file when it is in ASM .
1-Login to the database and check the status
[oracle@DB1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 25 01:52:52 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name,open_mode,instance_name from v$database ,v$instance ;
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
TESTDB READ WRITE TESTDB
2- Check the path of controlfile
SQL> select name from v$controlfile ;
NAME
--------------------------------------------------------------------------------
+REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789
3- Now make sure you have the 2nd disk group is mounted to keep the another copy of controlfile .Here we will multiplex our control file to +REDOLOG02 disk group .
4-Now make changes to the parameter file (spfile) in the database .
SQL> alter system set control_files='+REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789','+REDOLOG02' scope=spfile ;
System altered.
5-Once spfile is modified we will start the database in nomount state .
SQL> shu immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@DB1 ~]$ . oraenv
ORACLE_SID = [TESTDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DB1 ~]$
[oracle@DB1 ~]$
[oracle@DB1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 25 01:52:52 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 12446800 bytes
Variable Size 1644167168 bytes
Database Buffers 9059696640 bytes
Redo Buffers 21106688 bytes
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789, +REDOLOG02
As you can see we have another path for control file .But the control file is yet to be multiplxed .We will do it through RMAN utility .
[oracle@DB1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 25 01:52:52 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from '+REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789' ;
Starting restore at 25-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1590 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789
output file name=+REDOLOG02/TESTDB/CONTROLFILE/current.260.1076460247
Finished restore at 25-JUN-20
Now as you can see ,we have restored a copy of exiting control file to a new location i.e in our case it is the +REDOLOG02 .
6- Now update the spfile and restart the database .
[oracle@DB1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 25 01:52:52 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sql> alter system set control_files='+REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789','+REDOLOG02/TESTDB/CONTROLFILE/current.260.1076460247' scope=spfile ;
Statement processed
SQL> shu immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 12446800 bytes
Variable Size 1644167168 bytes
Database Buffers 9059696640 bytes
Redo Buffers 21106688 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile ;
NAME
--------------------------------------------------------------------------------
+REDOLOG01/TESTDB/CONTROLFILE/current.260.1023456789
+REDOLOG02/TESTDB/CONTROLFILE/current.260.1076460247
As we can see now our control file has been copied to/multiplexed another diskgroup .
Happy Learning !!!!