Prior to oracle database 12c, the procedures for converting from single-instance databases to Real Application Clusters involved following steps:<\/p>\n
With the introduction of multitenant architecture in Oracle database 12c, applications can be represented by pluggable databases within a container database (CDB). The number of active\/configured instances of the PDB are decided at the CDB level. A PDB which is part of a single instance CDB will have only one instance whereas a PDB within a RAC CDB will have multiple instances. Hence, a single instance PDB can be quickly converted to RAC by unplugging it from single instance CDB and plugging it into a RAC multitenant container database (CDB). The control file, Undo tablespace, Redo logs, password file and SPfile of the destination RAC CDB would already be on shared storage. Hence, while plugging in the PDB, only following two activities need to be done:<\/p>\n
– Create metadata describing the plugged-in PDB in the destination CDB
\n– Move the datafiles of the PDB to the shared storage (if they are not there already)<\/p>\n
This procedure simplifies and speeds the process of converting a single instance database to RAC considerably.<\/p>\n
To be able to employ this method, the source and target CDB platforms must meet the following requirements:<\/p>\n
Now I will demonstrate the above procedure. Currently, I have one single instance CDB called cdbs<\/strong> having a PDB called pdbs<\/strong>. Also, in my 3 node oracle database 12c cluster , there is a 3-instance policy managed RAC CDB called cdb1<\/strong> assigned to server pool ora.cdb1pool. Let\u2019s say that I want to have my application which is represented by single instance PDB (pdbs<\/strong>) to run on 3 servers to facilitate high availability and load balancing.\u00a0In order to meet my objective, I will unplug PDB pdbs<\/strong> from single instance CDB (cdbs<\/strong>) and plug it in 3-instance RAC CDB (cdb1<\/strong>) with name pdbrac<\/strong> so that the plugged-in PDB pdb<\/strong>rac<\/strong> will also have 3 instances. Also, since datafiles of PDB pdbs<\/strong> are currently on non-shared filesystem, I will move them to shared storage on ASM while plugging in.<\/p>\n Here is the demonstration:<\/p>\n Current scenario:<\/strong><\/p>\n Nodes in cluster : 3 Destination <\/strong>database:<\/strong> Verify that services of single instance PDB pdbs and CDB cdbs are registered with the listener on host01<\/strong><\/p>\n Verify that services of <\/strong>RAC <\/strong> PDB pdb1 and CDB cdb1 are registered with the listener on host02 and instance cdb1_1 is running on host02<\/strong><\/p>\n Verify that services of PDB pdb1 and CDB cdb1 are registered with the listener on host03 and instance cdb1_2 is running on host03<\/strong><\/p>\n To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. The information will be used by the plugging operation.<\/p>\n Use SQL*Plus to close the PDB before it can be unplugged.<\/strong><\/p>\n Unplug the closed PDB and then specify the path and name of the XML file.<\/strong><\/p>\n Drop the closed PDB and keep the data files.<\/strong><\/p>\n Verify that unplugged PDB is no longer part of cdbs<\/strong><\/p>\n This command should be issued from the node where target database instance is running.<\/p>\n Since cdb1 instance is running on host02, copy the xml file from host01 to host02<\/strong><\/p>\n Find out the name of the instance of destination RAC CDB cdb1 running on host02<\/strong><\/p>\n Make sure that the to-be-plugged-in PDB (pdbs) is compatible with the new host CDB (cdb1).<\/strong><\/p>\n Check the Compatibility of the Unplugged PDB (pdbs) with the target CDB (cdb1)<\/strong><\/p>\n Since these are warnings, we can continue.<\/p>\n Copy datafiles of PDB pdbs from host01 to host02 in same location as host01<\/strong><\/p>\n Plug the PDB (pdbs) into destination CDB (cdb1) with name pdbrac and copy the datafiles to DATA diskgroup on shared storage<\/strong><\/p>\n Verify that PDB (pdbs) has been successfully plugged into into target CDB (cdb1) with name pdbrac<\/strong><\/p>\n Verify that datafiles for plugged in PDB (pdbrac) have been copied to DATA diskgroup on shared storage<\/strong><\/p>\n Open all instances of plugged-in PDB pdbrac<\/strong><\/p>\n Connect to instance of PDB pdbrac running on host03 and verify that it belongs to instance cdb1_2 of CDB cdb1<\/strong><\/p>\n Connect to instance of PDB pdbrac running on host02 and verify that it belongs to instance cdb1_1 of CDB cdb1<\/strong><\/p>\n Oracle multitenant architecture reduces the number of tasks to be performed when converting a single instance database to RAC as SPfile, controlfile, undo tablespace(s), Redo logs, password file and instance to node mapping belongs to CDB rather than to each individual PDB. Moreover, conventional pre-12c methods of conversion required RAC and the standalone environments to be using the same oracle release. But in case of oracle database 12c multitenant architecture, this restriction is not there. The destination RAC CDB can be running a different oracle software version and as a result upgrading of the application can be carried it along with its conversion to RAC.<\/p>\n","protected":false},"excerpt":{"rendered":" Prior to oracle database 12c, the procedures for converting from single-instance databases to Real Application Clusters involved following steps: Configure the control file attributes and move control file to shared storage Make the initialization parameter file entries for cluster-enabled environments and move SPfile to shared storage Create an undo tablespace for each additional instance Create redo threads for each additiona…<\/p>\n","protected":false},"author":316217,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48373,48444,48447,48471,48476,48485],"coauthors":[],"class_list":["post-73167","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-cdb","tag-multit","tag-ncdb","tag-pdb","tag-pluggable-databases","tag-rac"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73167","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316217"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73167"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73167\/revisions"}],"predecessor-version":[{"id":91658,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73167\/revisions\/91658"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73167"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nNames of nodes : host01, host02, host03
\nSource database:<\/strong>
\nSource single instance CDB : cdbs<\/strong> <\/strong>
\nSingle instance PDB in cdbs<\/strong> : pdbs<\/strong><\/p>\n
\nDestination policy managed RAC CDB : cdb1<\/strong>
\nAssigned to serverpool : ora.cdb1pool
\nACTIVE_SERVERS=host02 host03<\/p>\nOverview:<\/h2>\n
\n
\n
Implementation:<\/h2>\n
Verify that source single instance CDB (cdbs) is currently running on host01<\/h3>\n
[grid@host03 ~]$ srvctl status database -d cdbs\r\nInstance cdbs is running on node host01<\/strong>\r\nCDBS> sho parameter db_name\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_name string cdbs<\/strong>\r\n\r\nCDBS> sho parameter cluster_database\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ncluster_database boolean FALSE<\/strong>\r\ncluster_database_instances integer 1<\/strong><\/pre>\n
[oracle@host01 ~]$ lsnrctl stat\r\n\r\n...\r\n\r\n (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.9.201.246)(PORT=1521)))\r\nServices Summary...\r\nService \"+ASM\" has 1 instance(s).\r\n Instance \"+ASM1\", status READY, has 2 handler(s) for this service...\r\nService \"cdbs<\/strong>\" has 1 instance(s).\r\n Instance \"cdbs\", status READY, has 1 handler(s) for this service...\r\nService \"cdbsXDB\" has 1 instance(s).\r\n Instance \"cdbs\", status READY, has 1 handler(s) for this service...\r\nService \"pdbs<\/strong>\" has 1 instance(s).\r\n Instance \"cdbs\", status READY, has 1 handler(s) for this service...\r\nThe command completed successfully<\/pre>\n
Verify that source single instance CDB (cdbs) has one PDB (pdbs) having non-ASM datafiles<\/h3>\n
CDBS> sho pdbs\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDBS <\/strong> READ WRITE YES\r\n\r\nCDBS> alter session set container=pdbs;\r\n\r\nSession altered.\r\nCDBS> select name from v$datafile;\r\n\r\nNAME\r\n --------------------------------------------------------------------------------\r\n\/u01\/app\/oracle\/oradata\/cdbs\/undotbs01.dbf<\/span>\r\n\/u01\/app\/oracle\/oradata\/cdbs\/pdbs\/system01.dbf<\/span>\r\n\/u01\/app\/oracle\/oradata\/cdbs\/pdbs\/sysaux01.dbf<\/span>\r\n\/u01\/app\/oracle\/oradata\/cdbs\/pdbs\/pdbs_users01.dbf<\/span><\/pre>\nVerify that destination policy managed RAC CDB (cdb1)<\/h3>\n
\n
[grid@host03 ~]$ srvctl status database -d cdb1\r\nInstance cdb1_1 is running on node host02<\/span>\r\nInstance cdb1_2 is running on node host03<\/span>\r\nDatabase cdb1 is not running on node host01<\/span>\r\n\r\n[grid@host03 ~]$ crsctl status serverpool\r\nNAME=Free\r\nACTIVE_SERVERS=\r\n\r\nNAME=Generic\r\nACTIVE_SERVERS=host01\r\n\r\nNAME=ora.cdb1pool<\/span>\r\nACTIVE_SERVERS=host02 host03<\/span>\r\n\r\nNAME=ora.cdbs\r\nACTIVE_SERVERS=host01<\/pre>\n[oracle@host02<\/span> ~]$ lsnrctl stat\r\n...\r\nServices Summary...\r\nService \"+ASM\" has 1 instance(s).\r\n Instance \"+ASM3\", status READY, has 2 handler(s) for this service...\r\nService \"cdb1<\/span>\" has 1 instance(s).\r\n Instance \"cdb1_1<\/span>\", status READY, has 1 handler(s) for this service...\r\nService \"cdb1XDB\" has 1 instance(s).\r\n Instance \"cdb1_1\", status READY, has 1 handler(s) for this service...\r\nService \"pdb1<\/span>\" has 1 instance(s).\r\n Instance \"cdb1_1<\/span>\", status READY, has 1 handler(s) for this service...\r\nThe command completed successfully<\/pre>\n[oracle@host03<\/span> ~]$ lsnrctl stat\r\nServices Summary...\r\nService \"+ASM\" has 1 instance(s).\r\n Instance \"+ASM2\", status READY, has 2 handler(s) for this service...\r\nService \"cdb1<\/span>\" has 1 instance(s).\r\n Instance \"cdb1_2<\/span>\", status READY, has 1 handler(s) for this service...\r\nService \"cdb1XDB\" has 1 instance(s).\r\n Instance \"cdb1_2\", status READY, has 1 handler(s) for this service...\r\nService \"pdb1<\/span>\" has 1 instance(s).\r\n Instance \"cdb1_2<\/span>\", status READY, has 1 handler(s) for this service...\r\nThe command completed successfully<\/pre>\nVerify that destination RAC CDB (cdb1) has currently only one PDB (PDB1)<\/h3>\n
CDB1> sho parameter db_name\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_name string cdb1<\/span>\r\nCDB1> sho parameter cluster\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ncluster_database boolean TRUE<\/span>\r\ncluster_database_instances integer 3<\/span>\r\ncluster_interconnects string\r\nCDB1> sho pdbs\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1<\/span> MOUNTED<\/pre>\nUnplug pluggable database pdbs from single instance CDB (cdbs)<\/h3>\n
SQL> conn sys\/oracle@cdbs as sysdba<\/code>
\nCDBS> alter pluggable database pdbs close immediate;<\/code><\/p>\nCDBS>alter pluggable database pdbs unplug into '\/u01\/app\/oracle\/oradata\/pdbs.xml';<\/code><\/p>\nCDBS>drop pluggable database pdbs keep datafiles;<\/code><\/p>\nCDBS>select pdb_name, status from cdb_pdbs where pdb_name in ('PDBS');<\/code>
\nno rows selected<\/code><\/p>\nPlug pluggable database PDBS into RAC CDB CDB1<\/h3>\n
[oracle@host01 ~]$ scp \/u01\/app\/oracle\/oradata\/pdbs.xml host02:\/u01\/app\/oracle\/oradata\/pdbs.xml\r\npdbs.xml \r\n 100% 3372 3.3KB\/s 00:00<\/pre>\n
[oracle@host02<\/span> ~]$ ps -ef |grep pmon\r\noracle 8525 6894 0 16:54 pts\/2 00:00:00 grep pmon\r\ngrid 17855 1 0 09:08 ? 00:00:06 asm_pmon_+ASM3\r\noracle 20871 1 0 09:09 ? 00:00:07 ora_pmon_cdb1_1<\/span><\/pre>\n[oracle@host02<\/span> ~]$ export ORACLE_SID=cdb1_1\r\n\r\n[oracle@host02 <\/span>~]$ sqlplus \/ as sysdba\r\n CDB1>set serveroutput on\r\n\r\n DECLARE\r\n compatible BOOLEAN := FALSE;\r\n BEGIN \r\n compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(\r\n pdb_descr_file => '\/u01\/app\/oracle\/oradata\/pdbs.xml', pdb_name=>'PDBS');\r\n if compatible then\r\n DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');\r\n else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');\r\n end if;\r\n END;\r\n \/<\/pre>\nCDB1> col cause for a10\r\n col name for a10\r\n col message for a35 word_wrapped\r\n select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='PDBS';\r\n\r\nNAME CAUSE TYPE MESSAGE STATUS\r\n---------- ---------- --------- ----------------------------------- ---------\r\n\r\nPDBS OPTION WARNING<\/span> Database option RAC mismatch: PDB PENDING\r\n installed version NULL. CDB\r\n installed version 12.1.0.1.0.\r\n\r\nPDBS Parameter WARNING<\/span> CDB parameter sga_target mismatch: PENDING\r\n Previous 394264576 Current\r\n 662700032\r\n\r\nPDBS Parameter WARNING<\/span> CDB parameter pga_aggregate_target PENDING\r\n mismatch: Previous 131072000\r\n Current 220200960<\/pre>\n[oracle@host02 ~]$ mkdir -p \/u01\/app\/oracle\/oradata\/cdbs\/pdbs<\/code><\/p>\n[oracle@host02 ~]$ scp host01:\/u01\/app\/oracle\/oradata\/cdbs\/pdbs\/* \/u01\/app\/oracle\/oradata\/cdbs\/pdbs\/<\/code><\/p>\nCDB1L> create pluggable database pdbrac<\/span> using '\/u01\/app\/oracle\/oradata\/pdbs.xml' <\/code>
\ncopy
\nFILE_NAME_CONVERT = ('\/u01\/app\/oracle\/oradata\/cdbs\/pdbs', '+DATA'<\/span>);<\/code><\/code><\/p>\n CDB1> sho pdbs\r\n\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 MOUNTED\r\n 4 PDBRAC<\/span> MOUNTED\r\n\r\n CDB1>alter pluggable database PDBrac open;\r\n\r\n CDB1> sho pdbs\r\n\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 MOUNTED\r\n 4 PDBRAC<\/span> READ WRITE YES\r\n\r\nCDB1> alter session set container=pdbrac;<\/pre>\nPDBRAC1> select name from v$datafile;\r\n\r\n NAME\r\n--------------------------------------------------\r\n+DATA<\/span>\/CDB1\/DATAFILE\/undotbs1.261.853323067\r\n+DATA<\/span>\/system01.dbf\r\n+DATA<\/span>\/sysaux01.dbf\r\n+DATA<\/span>\/pdbs_users01.dbf<\/pre>\nSQL> alter pluggable database pdbrac open instances=all;<\/code><\/p>\nVerify that multiple instances of Plugged-in PDB can be accessed<\/h3>\n
SQL> conn system\/oracle@host03<\/span>:1521\/pdbrac<\/span>\r\nConnected.\r\nSQL> sho con_name\r\nCON_NAME\r\n------------------------------\r\nPDBRAC\r\n\r\nSQL> sho parameter instance_name\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ninstance_name string cdb1_2<\/span><\/pre>\nSQL> conn system\/oracle@host02:1521\/pdbrac\r\nConnected.\r\nSQL> sho con_name\r\n\r\nCON_NAME\r\n------------------------------\r\nPDBRAC\r\nSQL> sho parameter instance_name\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ninstance_name string cdb1_1<\/span><\/pre>\nConclusion<\/h2>\n