top of page

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 !!!!

246 views0 comments

Recent Posts

See All

Source: Node1 (10.20.30.1) Target: Node2 (10.20.30.2) From Node1 we will setup passwordless SSH to Node2 and vice verse. 1- Login to Node1 as the same user on which the password less SSH is required

In the previous post we saw how to upgrade OMS from 13.2 to 13.4 version .(Click Here) Once OMS is upgraded we have to upgrade the agents running in the servers which are monitored by OEM . This whole

bottom of page