Remove MGMTDB Database From RAC

Introduction

In Oracle Real Application Clusters (RAC), the MGMTDB database plays a critical role in managing and monitoring the cluster itself. However, there might be situations where you need to remove the MGMTDB database. Whether it’s due to decommissioning, troubleshooting, or other reasons, this guide will walk you through the process of removing the MGMTDB database in Oracle RAC using commands.

MTMTDB: Grid Infrastructure Management Repository

MGMTDB is new database instance which is used for collecting or storing real time Cluster Health Monitor (CHM) data of each node. In 11g this was being stored in berkeley database but starting Oracle database 12c it is configured as Oracle Database Instance.

It can be located at any node but only one instance in the cluster, you may regard it as a RAC one node database. Sometimes we noticed that several database had performance-related issues because of MGMTDB. The repository database was able to use almost 100% of CPU resources and also we face issues while applying patches on Grid Infrastructure (GI) and Oracle database system in that kind of case if you wish you can delete or remove the MGMTDB database from cluster.

How To Remove MGMTDB Database

1. Check on which Node MGMTDB is Running
2. Check & Remove dependency for CHA(Cluster Health Analysis Service)
3. Remove MGMTDB using DBCA and validate
4. Remove MGMTDB listener

1. Check on which Node MGMTDB is Running

##Check through PMON Process##

[grid@node1 ~]$ ps -ef|grep pmon

grid     21684     1  0 Jul22 ?        00:02:23 asm_pmon_+ASM1

grid     23868     1  0 Jul22 ?        00:02:24 mdb_pmon_-MGMTDB

oracle   28677     1  0 Jul22 ?        00:05:14 ora_pmon_DBPROD1




##Check Status of MGMTDB##

[grid@node1 ~]$ srvctl status mgmtdb

Database is enabled
Instance -MGMTDB is running on node node1




##You can aslo check status through CRSCTL ##

##Go to $GRID_HOME/bin directory through root user##

[root@node1 bin]# pwd

/u01/app/12.2.0/grid/bin



[root@node1 bin]# ./crsctl status res ora.mgmtdb

NAME=ora.mgmtdb
TYPE=ora.mgmtdb.type
TARGET=ONLINE
STATE=ONLINE on node1

In above Case you can see MGMTDB is running on Node1 and you can check status of database running on which node through various ways.

2. Check & Remove dependency for CHA(Cluster Health Analysis Service)

a) Check CHA Dependency on MGMTDB

[root@node1 bin]# ./crsctl stat res ora.chad -p | grep mgmt

START_DEPENDENCIES=hard(global:ora.mgmtdb) pullup(global:ora.mgmtdb)
STOP_DEPENDENCIES=hard(global:intermediate:ora.mgmtdb)

In this we can see CHA has a dependency on MGMTDB

b) Remove Dependency by setting following attributes

[root@node1 bin]# ./crsctl modify resource ora.chad -attr "START_DEPENDENCIES=' ', STOP_DEPENDENCIES=' '" -unsupported

We have removed dependency by keeping value blank as above

c) Verify CHA should be running

[grid@node1 ~]$ srvctl status cha

Oracle Cluster Health Analysis Service is enabled
Oracle Cluster Health Analysis Service is running on nodes: node1, node2

3. Remove MGMTDB using DBCA and Validate

[grid@node1 ~]$ dbca -silent -deleteDatabase -sourceDB -MGMTDB

[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.

Prepare for db operation
32% complete

Connecting to database
35% complete
39% complete
42% complete
45% complete
48% complete
52% complete
65% complete

Updating network configuration files
68% complete

Deleting instance and datafiles
84% complete
100% complete


Database deletion completed.
Look at the log file "/u01/app/grid/cfgtoollogs/dbca/_mgmtdb/_mgmtdb.log" for further details.

MGMTDB was running on Node1 So, remove command should be run on Node1 only by grid user

[grid@node1 ~]$ srvctl status mgmtdb

PRCD-1120 : The resource for database _mgmtdb could not be found.
PRCR-1001 : Resource ora.mgmtdb does not exist

Validated MGMTDB is not longer available

4. Remove MGMTDB listener

[grid@node1 ~]$ srvctl stop mgmtlsnr

[grid@node1 ~]$ srvctl disable mgmtlsnr

MGMTDB listener stopped and disabled

Conclusion

Removing the MGMTDB database in Oracle RAC requires careful execution of commands to ensure a smooth process. Always perform these actions in a controlled environment and have backups ready to recover from any unforeseen issues. By following the steps outlined in this guide, you can safely remove the MGMTDB database from your Oracle RAC setup.

How To Relocate MGMTDB database to Another Node