Main thing to look out for is that repository connection string is updated in various files the EM uses. Current connection string could be found out with the following command.
emctl config oms -list_repos_details Oracle Enterprise Manager Cloud Control 13c Release 4 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved. Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SID=emandb1))) Repository User : SYSMAN
Mos Docs 2431726.1, 2144665.1 and 2214218.1 all mention the files where the connection string is specified. These are available in the following directories.
cd /opt/app/software/em/gc_inst/user_projects/domains/GCDomain/config/fmwconfig embi-policystoremerge-jpscfg.xml <property name="jdbc.url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SID=emandb1)))"/> jps-config-jse.xml <property name="jdbc.url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SID=emandb1)))"/> jps-config.xml <property name="jdbc.url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SID=emandb1)))"/> cd /opt/app/software/em/gc_inst/user_projects/domains/GCDomain/config/jdbc all *.xml files in this directory as per 2214218.1 cd /opt/app/software/em/gc_inst/em/EMGC_OMS1 emgc.properties EM_REPOS_CONNECTDESCRIPTOR=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=console-srv)(PORT\=2020)))(CONNECT_DATA\=(SID\=emandb1)))
Main issue here is that connection string uses SID. Once the repos DB is plugged in as a PDB, the SID cannot be used to connect to it. Service name (SERVICE_NAME) must be used instead. Therefore as the first step the connection string would be updated to use service name instead of SID.
The current repos DB is called emandb1.
Service "emandb1" has 1 instance(s). Instance "emandb1", status READY, has 1 handler(s) for this service...If the non-CDB is plugged as a PDB using the same name emandb1 then this will result in a default service for PDB called emandb1. This would allow OMS to connect to the PDB without requring any connection string change. Also changing the connecting string before non-CDB conversion would allow any connectivity related issues to be ironed out beforehand.
With this in mind change the connection string to use service_name instead of SID. First check the connection string with service_name works by using it on a sqlplus connection
sqlplus sysman@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SERVICE_NAME=emandb1)))" SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 7 09:10:44 2021 Version 19.12.0.0.0 SQL>
Then use the following command (mentioned in 2214218.1, 1395107.1 and CC Advanced Installation and Configuration Guide) to change the connecting string. The documentation states to shutdown OMS before running this command. The normal output is shown below.
emctl stop oms Oracle Enterprise Manager Cloud Control 13c Release 4 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved. Stopping Oracle Management Server... Oracle Management Server Successfully Stopped Oracle Management Server is Down JVMD Engine is Down emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SERVICE_NAME=emandb1)))" -repos_user SYSMAN Oracle Enterprise Manager Cloud Control 13c Release 4 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved. Enter Repository User's Password : Successfully updated datasources and stored repository details in Credential Store. If there are multiple OMSs in this environment, run this store_repos_details command on all of them. And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'. It is also necessary to restart the BI Publisher Managed Server.
But at times the command has also complained admin server is down when only oms stopped is called. It seems the admin server neeeds to be up connection string change to work. Good thing is comamnd will bring up the admin server if it is down. See the output below for this case.
emctl stop oms emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SERVICE_NAME=emandb1)))" -repos_user SYSMAN Oracle Enterprise Manager Cloud Control 13c Release 4 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved. Enter Repository User's Password : Admin server is down. It is required to update repository details. This command will try to bring it up. Starting Admin Server only... Admin Server Successfully Started Successfully updated datasources and stored repository details in Credential Store. If there are multiple OMSs in this environment, run this store_repos_details command on all of them. And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'. It is also necessary to restart the BI Publisher Managed Server. emctl stop oms -all emctl start oms emctl config oms -list_repos_details Oracle Enterprise Manager Cloud Control 13c Release 4 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved. Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SERVICE_NAME=emandb1))) Repository User : SYSMAN
Above shows the new connetion string uses service_name. Checking in individual files mentioned earlier, all but embi-policystoremerge-jpscfg.xml and jps-config-jse.xml had the connection string changed correctly. The connectring string in file embi-policystoremerge-jpscfg.xml still refered to SID. Manually edit the connection string to use service_name. In jps-config-jse.xml there are serveral references to connection string and section under serviceInstance name="audit.db" didn't have the SID changed to service name. Make the change manually. Once done login to cloud control and check all is working as expected. If any connectivity errors are there correct them before proceeding to next steps. The EM Repos DB target shown on the console may require re-discovering and promoting
Next step is to create a CDB to plug the repos non-CDB as a PDB. The repos DB has very specific requirements when it comes to their memory parametrs, redo log file sizes, hidden parameters and etc (refer earlier post). The simplest way to accomplish this is to create a template from current non-CDB and use the template to create a the CDB. Below two commands does exactly this.
dbca -createTemplateFromDB -templateName /home/oracle/emrepos13.4.dbt -sourceDB emandb1 -sysDBAUserName sys -maintainFileLocations false -silent dbca -silent -createDatabase -templateName /home/oracle/emrepos13.4.dbt -gdbName emandb -sysPassword xxxx -systemPassword xxx -emConfiguration NONE -storageType ASM -asmsnmpPassword xxxx -diskGroupName DATA -recoveryGroupName FRA -createAsContainerDatabase true
The CDB is called emandb(different to current non-CDB repos DB name). The database created from template will have tablespaces that were created for EM repos use such as MGMT_AD4J_TS,MGMT_ECM_DEPOT_TS,MGMT_TABLESPACE. These could be dropped from the CDB to save space (alternatively edit the template so these are not created in the first place).
Shutdown the OMS as the next step is to plug the non-CDB as a PDB. For this test case used the manual method. Last set of steps are shown below. The non-CDB is plugged in as a PDB has the same name, emandb1.
SQL> CREATE PLUGGABLE DATABASE emandb1 USING '/home/oracle/emandb1_non_cdb.xml' copy;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EMANDB1 MOUNTED
SQL> alter session set container=emandb1;
Session altered.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/system.303.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/sysaux.306.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/undotbs1.304.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/users.299.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/oradbaudit.298.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/mgmt_ad4j_ts.305.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/mgmt_ecm_depot_ts.297.1082632693
+DATA/EMANDB/A148AAC6957E5A34E0530500160A7405/DATAFILE/mgmt_tablespace.307.1082632693
8 rows selected.The new PDB now contains the datafiles related to EM related tablespaces. Complete the conversion with the following.@?/rdbms/admin/noncdb_to_pdb.sql
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 EMANDB1 MOUNTED
SQL> alter pluggable database open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 EMANDB1 READ WRITE NOOnce the PDB is open the listener will have a service emandb1 (PDB default service) for emandb CDB instance.Service "emandb" has 1 instance(s). Instance "emandb", status READY, has 1 handler(s) for this service... Service "emandb1" has 1 instance(s). Instance "emandb", status READY, has 1 handler(s) for this service...Use sqlplus with same connection string as before to check connectivity to the PDB.
sqlplus sysman@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SERVICE_NAME=emandb1)))"If connectivity is fine start the OMS.
emctl start oms Oracle Enterprise Manager Cloud Control 13c Release 4 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved. Starting Oracle Management Server... WebTier Successfully Started Oracle Management Server Successfully Started Oracle Management Server is Up JVMD Engine is UpDiscover the new EM repos db and related PDB targets and promote them. The dbsnmp common user in CDB will be in locked status. Unlock the dbsnmp account and give a new password.
The old EM repos db will be in down state while newly promoted CDB and PDB will be shown up state.
Once confident all is working as expected the old EM repos DB could be dropped.
Update 28/09/2021
Two incidents occured with regard to change of the connection string. The -list_repos_details showed service_name being used but there were two resources still refering SID. One is the management service.
Similar connection string related incident could be seen for management service and repository as well.
Repository still seem to use the SID in the connection string.
To fix this issue manually edit the conenction string to include service name and save. Similar to previous incident no need to enter username or password (leave the default in place).
There were no other incidents after these changes.
This issue may have been due to not executing the "emctl config emrep". This could be done by running
emctl config emrep -conn_desc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=console-srv)(PORT=2020)))(CONNECT_DATA=(SERVICE_NAME=emandb1)))"
Update 17/01/2022
This scenario is now documented in 13.5 upgrade guide.
Migrating the SYSMAN Schema to a Database Configured with CDB and PDB
