Converting an Administrator-Managed Database to a Policy-Managed Database and Vice-Versa

(VERSION INFORMATION: Oracle GI & DB11.2.0.3; Red Hat 5u7)

 

Oracle RAC provides a new method to manage your clustered database. Traditionally, databases were administrator managed, where a DBA managed each instance of the database by defining specific instances to run on specific nodes in the cluster. To help implement dynamic grid configurations, Oracle RAC 11g release 2 (11.2) introduces policy-managed databases, where the DBA is required only to define the cardinality (number of database instances required). Oracle Clusterware manages the allocation of nodes to run the instances and Oracle RAC allocates the required redo threads and undo tablespaces, as needed (when database uses OMFs).

Now, let convert ORCL database from the old administrator managed to policy-managed database.

Record the current configuration.

$ srvctl config database -d orcl

$ srvctl config service -d orcl

creating a server pool called tobi_cluster_pool with two node as maximum.

[oracle@red1 ~]$ srvctl add srvpool -g tobi_cluster_pool -l 0 -u 2

[oracle@red1 ~]$ srvctl status srvpool -a

In the above screenshot, two node in the cluster have been assigned to server pool tobi_cluster_pool automatically.

let now modify database(orcl) and include it as part of tobi_cluster_pool.

[oracle@red1 ~]$  srvctl modify database -d orcl -g tobi_cluster_pool

[oracle@red1 ~]$ srvctl status srvpool –a

[oracle@red1 ~]$ srvctl config db -d orcl

Since we did not specify an instance prefix, then the first 12 characters of the unique name of the database becomes the prefix. you can specify prefix with option -i

Manually create a node and instance binding to ensure that orcl_1 always runs on the same node, as follows:

[oracle@red1 ~]$ srvctl modify instance -d orcl -n red1 -i orcl_1

[oracle@red1 ~]$ srvctl modify instance -d orcl -n red2 -i orcl_2                                                

Configure Oracle Enterprise Manager to recognize the change you made in the previous procedure, as follows:

1.       [oracle@red1 ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1  /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl_1

2.       [oracle@red1 ~]$ ssh red2 cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl2  /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl_2

3.       [oracle@red1 ~]$ ORACLE_SID=orcl_1

Instance name(SID) will have changed  to db_unique_name_#

4.       [oracle@red1 ~]$ export ORACLE_SID

5.       [oracle@red1 ~]$ emca -config dbcontrol db –cluster

To get the cluster name, run below

[grid@red2 ~]$ cemutlo -n

Creating singleton services in policy managed databases:

[oracle@red1 ~]$ srvctl start db -d orcl -o open

[oracle@red1 ~]$ srvctl add service -d orcl -s dtptest.tobi.com -g tobi_cluster_pool -c singleton

[oracle@red1 ~]$ srvctl modify service -d orcl -s dtptest.tobi.com -x true  #set the DTP (Distributed Transaction Processing) option to true.

[oracle@red1 ~]$ srvctl status service -d orcl -s dtptest.tobi.com

Start the service

[oracle@red1 ~]$ srvctl start service -d orcl -s dtptest.tobi.com

NOTE: You cannot directly convert a policy-managed database to an administrator-managed database. Instead, you can remove the policy-managed configuration using the srvctl remove database and srvctl remove service commands, and then register the same database as an administrator-managed database

Change from policy to administrator managed database

[oracle@red1 ~]$ srvctl stop db -d orcl

[oracle@red1 ~]$ srvctl remove database -d orcl

 [oracle@red1 ~]$ srvctl config db -d orcl

Add database orcl as administrator managed

[oracle@red1 ~]$ srvctl add database -d orcl -o /u01/app/oracle/product/11.2.0/dbhome_1 -c RAC -m tobi.com

[oracle@red1 ~]$ srvctl config db -d orcl

Add node and instance to the newly added administrator managed database.

[oracle@red1 ~]$ srvctl add database -d orcl -o /u01/app/oracle/product/11.2.0/dbhome_1 -c RAC -m tobi.com

[oracle@red1 ~]$ srvctl add instance -d orcl -n red1 -i orcl1

[oracle@red1 ~]$ srvctl add instance -d orcl -n red2 -i orcl2

When you add a node that does not exist on the cluster you receive error PRKO-2006: Invalid node name: hostname

Add service

[oracle@red1 ~]$ srvctl add service -d orcl -s swing.tobi.com -j LONG -B service_time -r orcl1,orcl2

[oracle@red1 ~]$ srvctl config db -d orcl

Ref & More reading: Oracle® Real Application Clusters Administration and Deployment Guide
11g Release 2 (11.2)