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

Posted in: Oracle, Technical Track

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.

email

Authors

Interested in working with Rene? Schedule a tech call.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *