Converting Oracle Enterprise Manager Management Service (OMS) Repository from Legacy Non-CDB to CDB Architecture

The other day I was given the task of converting a single instance Oracle Enterprise Manager Management Service (OMS) Repository from legacy non-CDB (container database) to CDB architecture. You'll see the steps are basically the same as converting a regular database; my aim here is to put together all the extra steps required for the OMS repository in one blog post.

The first thing you want to do is make sure you have a good backup. During the first exercise of this process I had to do a restore of my non-CDB repository. I won't got into how to do a backup here, but it is worth mentioning. I also created a flashback GRP (Guaranteed Restore Point) as another fallback procedure.

Wed Nov 04 08:02 OracleOmsHost oracle bin $ sqlplus / as sysdba
 SQL> alter database flashback on;
 
 Database altered.
 
 SQL> create restore point PRIOR_TO_CDB guarantee flashback database;
 
 Restore point created.

This database called emrep was upgraded from 12.1 to 19.6 in the past months. One of the things that was missed during the post-upgrade fix-ups was to check and correct user tables that depend on Oracle-Maintained types. Before doing the conversion I needed to correct this. You can check with the query below to assess the state of your database.

SQL> set lines 200 pages 999
 SQL> col OWNER format a30
 SQL> col TABLE_NAME format a40
 SQL> SELECT DISTINCT owner, table_name FROM dba_tab_cols WHERE data_upgraded = 'NO';
 
 OWNER TABLE_NAME
 ------------------------------ ----------------------------------------
 SYSMAN EM_EVENT_RECONCILE_STATE_E
 SYSMAN EM_NOTIFY_BLACKOUT_BACKLOG_E
 SYSMAN EM_NOTIFY_QTABLE
 SYSMAN EM_RULE_SIM_EVTS_E
 SYSMAN EM_NOTIFY_REQUEUE_E
 SYSMAN EM_EVENT_BUS_TABLE
 SYSMAN EM_EVENT_BLACKOUT_BACKLOG_E
 SYSMAN EM_EVENT_ADR_BLACKOUT_STATE_E
 
 8 rows selected.

There are two ways to upgrade these datatypes:

1. Run @?/rdbms/admin/utluptabdata.sql
2. Run for each table from the query above run alter table <OWNER>.<TABLE_NAME> upgrade including data;

   I chose to run them manually. One thing to note, is that after doing this, I highly recommended that you recompile any invalid objects in the database. I used the following @?/rdbms/admin/utlrp.sql

After upgrading the tables that depend on Oracle-Maintained types, I wanted to make sure that all components in the database were valid and gathering additional information prior to the conversion.

I used this query I called verify_data_dictionary to check a) if all components were valid and b) that no invalid objects were present.

SQL> alter table SYSMAN.EM_EVENT_RECONCILE_STATE_E upgrade including data;
 Table altered.
 ...
 alter table SYSMAN.EM_EVENT_ADR_BLACKOUT_STATE_E upgrade including data;
 Table altered.
 
 SQL> @?/rdbms/admin/utlrp.sql
 ...
 SQL> SELECT DISTINCT owner, table_name
 FROM dba_tab_cols
 WHERE data_upgraded = 'NO'
 ORDER BY 1,2;
 
 no rows selected
 
 SQL> @verify_data_dictionary.sql
 DATABASE NAME
 =============
 
 DBNAME
 ---------------
 emrep
 
 DBA_REGISTRY CONTENTS
 ================================================================
 
 COMP_ID COMP_NAME VERSION STATUS
 -------------------- ---------------------------------------- --------------- ---------------
 CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
 CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
 JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
 XML Oracle XDK 19.0.0.0.0 VALID
 CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
 RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
 XDB Oracle XML Database 19.0.0.0.0 VALID
 OWM Oracle Workspace Manager 19.0.0.0.0 VALID
 
 8 rows selected.
 
 LIST APPLIED PATCHES
 =======================
 
 ACTION_TIME ID ACTION VERSION BUNDLE COMMENTS
 ------------------------------ ---------- ------------------------------ --------------- ------------------------ ------------------------------------------------------------
  BOOTSTRAP 12.1.0.2 RDBMS_12.1.0.2.0DBBP
  BOOTSTRAP 19 RDBMS_19.6.0.0.0DBRU
 08-MAY-20 08.01.08.980934 PM RU_APPLY 19.0.0.0 Patch applied on 19.
 08-MAY-20 08.03.31.551571 PM UPGRADE 19.0.0.0 Upgraded from 12.1.0
 
 LIST APPLIED SQL PATCHES
 =======================
 
 DESCRIPTION ACTION ACTION_DATE VERSION
 ------------------------------------------------------------------------------------- ------------------------------ -------------------- ---------------
 Database Release Update : 19.6.0.0.200114 (30557433) APPLY 08/05/20 20:01:13
 
 COUNT OF INVALID OBJECTS
 ========================
 
  COUNT(*)
 ----------
  0
 
 INVALID OBJECTS GROUPED BY OBJECT TYPE AND OWNER
 ================================================
 
 no rows selected
 
 LIST OF SYS INVALID OBJECTS
 =======================
 
 no rows selected
 
 DST VERSION
 =======================
 
 PROPERTY_NAME VALUE
 ------------------------------------------------------------ --------------------
 DST_PRIMARY_TT_VERSION 35
 DST_SECONDARY_TT_VERSION 0
 DST_UPGRADE_STATE NONE
 
 
 FILENAME VERSION CON_ID
 -------------------- ------- ----------
 timezlrg_35.dat 35 0

From my point of view everything was now ready for conversion. Note: in this post I won't go into how to create a CDB, but this link can help you with that.

I highly recommend when creating the CDB, you create it with the same components as the non-CDB, or you'll have PDB (Pluggable DataBase) violations you'll have to clear before doing the conversion.

For the conversion, the first thing you have to do is open the non-CDB in read-only mode, generate the description XML file and shutdown the database. Since we're dealing with an OMS, you need to shut the OMS down before following these steps.

Wed Nov 04 08:21 OracleOmsHost oracle bin $ ./emctl stop oms -all
 
 Stopping Oracle Management Server...
 WebTier Successfully Stopped
 Node Manager Not Running
 Oracle Management Server is Down
 JVMD Engine is Down
 BI Publisher Server is Down
 
 Wed Nov 04 08:32 OracleOmsHost oracle bin $ sqlplus / as sysdba
 
 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 
 SQL> STARTUP OPEN READ ONLY;
 ORACLE instance started.
 
 Total System Global Area 5016385624 bytes
 Fixed Size 8906840 bytes
 Variable Size 3053453312 bytes
 Database Buffers 1946157056 bytes
 Redo Buffers 7868416 bytes
 Database mounted.
 Database opened.
 
 SQL> BEGIN
  DBMS_PDB.DESCRIBE(
  pdb_descr_file => '/home/oracle/working/antunez/emrep_conversion/cdb_emrep_19c.xml');
 END;
 /
 
 PL/SQL procedure successfully completed.
 
 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

Once you've done this, you have to check if the plugin of the non-CDB to the CDB database will be compatible. You do this with the DBMS_PDB.CHECK_PLUG_COMPATIBILITY package. I'll be changing the name of the database from EMREP to PDBEMREP.

After running the compatibility package, you also need to check for any violations, and clear any errors before doing the conversion. If you only get warnings, you can chose to skip these and proceed with the conversion. Remember that after doing the plugin, you will need to run noncdb_to_pdb.sql.

Wed Nov 04 08:52 OracleOmsHost oracle antunez $ . oraenv <<< cdbrepo
 ORACLE_SID = [emrep] ? cdbrepo
 The Oracle base remains unchanged with value /u02/app/oracle
 Wed Nov 04 08:52 OracleOmsHost oracle antunez $ sqlplus / as sysdba
 
 SQL> SET SERVEROUTPUT ON
 
 DECLARE
  compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  pdb_descr_file => '/home/oracle/working/antunez/emrep_conversion/cdb_emrep_19c.xml',
  pdb_name => 'PDBEMREP')
  WHEN TRUE THEN 'YES' ELSE 'NO'
 END;
 BEGIN
 DBMS_OUTPUT.PUT_LINE(compatible);
 END;
 /
 
 YES
 
 PL/SQL procedure successfully completed.
 
 SQL> set lines 200 pages 999
 SQL> select cause, type, message
 from PDB_PLUG_IN_VIOLATIONS
 where name = 'PDBEMREP';
 
 CAUSE TYPE
 ---------------------------------------------------------------- ---------
 MESSAGE
 --------------------------------------------------------------------------------
 Non-CDB to PDB WARNING
 PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
 
 Parameter WARNING
 CDB parameter processes mismatch: Previous 600 Current 320
 
 Parameter WARNING
 CDB parameter use_large_pages mismatch: Previous 'ONLY' Current 'TRUE'
 
 Parameter WARNING
 CDB parameter shared_pool_size mismatch: Previous 576M Current 0
 
 Parameter WARNING
 CDB parameter sga_target mismatch: Previous 4784M Current 4272M
 
 Parameter WARNING
 CDB parameter db_flashback_retention_target mismatch: Previous 4320 Current 1440
 
 Parameter WARNING
 CDB parameter db_securefile mismatch: Previous 'PERMITTED' Current 'PREFERRED'
 
 Parameter WARNING
 CDB parameter _allow_insert_with_update_check mismatch: Previous TRUE Current FALSE
 
 Parameter WARNING
 CDB parameter session_cached_cursors mismatch: Previous 200 Current 50
 
 Parameter WARNING
 CDB parameter job_queue_processes mismatch: Previous 50 Current 80
 
 Parameter WARNING
 CDB parameter parallel_min_servers mismatch: Previous 0 Current 8
 
 Parameter WARNING
 CDB parameter parallel_max_servers mismatch: Previous 8 Current 80
 
 Parameter WARNING
 CDB parameter pga_aggregate_target mismatch: Previous 1340000000 Current 1424M
 
 Parameter WARNING
 CDB parameter _optimizer_use_feedback mismatch: Previous FALSE Current TRUE
 
 Parameter WARNING
 CDB parameter _optimizer_gather_feedback mismatch: Previous FALSE Current TRUE
 
 Parameter WARNING
 CDB parameter _sql_plan_directive_mgmt_control mismatch: Previous 0 Current 67
 
 Parameter WARNING
 CDB parameter _px_adaptive_dist_method mismatch: Previous 'OFF' Current 'CHOOSE'
 
 Parameter WARNING
 CDB parameter optimizer_adaptive_plans mismatch: Previous FALSE Current TRUE
 
 Parameter WARNING
 CDB parameter _optimizer_strans_adaptive_pruning mismatch: Previous FALSE Current TRUE
 
 Parameter WARNING
 CDB parameter _optimizer_nlj_hj_adaptive_join mismatch: Previous FALSE Current TRUE
 
 
 20 rows selected.
 

Since there were no errors in the compatibility check, I proceeded to create the PDB as a copy, and do the conversion using noncdb_to_pdb.sql. I used this COPY method due to the small size of the PDB and because it allowed me to have a fallback in case of an error during the conversion. Keep in mind that with a large database the COPY option might not be the best choice for you, so decide wisely when doing this.

Also after doing the conversion, it was important to me to save the PDB state as read-write, since I want it to open as soon as the CDB is open.

SQL> CREATE PLUGGABLE DATABASE PDBEMREP USING '/home/oracle/working/antunez/emrep_conversion/cdb_emrep_19c.xml' COPY;
 
 Pluggable database created.
 
 SQL> ALTER SESSION SET CONTAINER=PDBEMREP;
 
 Session altered.
 
 SQL> spool noncdb_to_pdb.log
 SQL> @?/rdbms/admin/noncdb_to_pdb.sql
 
 ....
 
 SQL> spool off
 SQL> ALTER SESSION SET CONTAINER=PDBEMREP;
 
 Session altered.
 
 SQL> ALTER PLUGGABLE DATABASE OPEN;
 
 Pluggable database altered.
 
 SQL> SELECT name, open_mode FROM v$pdbs;
 
 NAME
 -------------------------------
 OPEN_MODE
 ----------
 PDBEMREP
 READ WRITE
 
 1 row selected.
 
 SQL> ALTER PLUGGABLE DATABASE PDBEMREP SAVE STATE;
 
 Pluggable database altered.

With the conversion complete, I created a database service using dbms_service.CREATE_SERVICE for this OMS repository passing the following parameters:

• service_name — Name of the service.
• network_name — Network name of the service as used in SQLNet connect descriptors for client connections.

SQL> exec dbms_service.CREATE_SERVICE('emrep','emrep');
 
 PL/SQL procedure successfully completed.
 
 SQL> exec dbms_service.start_service('emrep');
 
 PL/SQL procedure successfully completed.
 
 SQL> alter system register;
 
 System altered.
 
 SQL> exit
 
 Wed Nov 04 10:24 OracleOmsHost oracle antunez $ lsnrctl services | grep -A 2 emrep
 Service "emrep" has 1 instance(s).
  Instance "cdbrepo", status READY, has 1 handler(s) for this service...
  Handler(s):
 --
 Service "pdbemrep" has 1 instance(s).
  Instance "cdbrepo", status READY, has 1 handler(s) for this service...
  Handler(s):
 
 Wed Nov 04 10:27 OracleOmsHost oracle antunez $ sqlplus sysman@OracleEntMgr:1521/emrep
 Enter password:
 
 SQL> show user
 USER is "SYSMAN"

I proceeded to update the OMS repository details with the new service I'd created. For this I needed to stop the OMS.

Wed Nov 04 10:32 OracleOmsHost oracle bin $ ./emctl config oms -list_repos_details
 
 Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OracleOmsHost)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))
 Repository User : sysman
 
 Wed Nov 04 10:33 OracleOmsHost oracle bin $ ./emctl stop oms -all
 Oracle Enterprise Manager Cloud Control 13c Release 4
 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
 Stopping Oracle Management Server...
 WebTier Successfully Stopped
 Node Manager Not Running
 Oracle Management Server is Down
 JVMD Engine is Down
 BI Publisher Server is Down
 
 Wed Nov 04 10:37 OracleOmsHost oracle bin $ ./emctl config oms -store_repos_details -repos_conndesc '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OracleOmsHost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=emrep)))' -repos_user SYSMAN
 
 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.
 

Once I had done this, the only thing I needed to do was bounce the OMS and I was set to go.

Wed Nov 04 10:40 OracleOmsHost oracle bin $ ./emctl stop oms -all
 
 Stopping Oracle Management Server...
 WebTier Successfully Stopped
 Oracle Management Server Already Stopped
 Oracle Management Server is Down
 JVMD Engine is Down
 Stopping BI Publisher Server...
 BI Publisher Server Already Stopped
 AdminServer Successfully Stopped
 BI Publisher Server is Down
 
 Wed Nov 04 10:41 OracleOmsHost oracle bin $ ./emctl start oms
 
 Starting Oracle Management Server...
 WebTier Successfully Started
 Oracle Management Server Successfully Started
 Oracle Management Server is Up
 JVMD Engine is Up
 Starting BI Publisher Server ...
 BI Publisher Server Successfully Started
 BI Publisher Server is Up
 

As you can see, this is nothing you haven't read before, but as I mentioned, I wanted to have the steps recorded in one place for future reference.

Note: This was originally posted on rene-ace.com.