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

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, and you will see that the steps are basically the same as converting a regular database, but just putting together all the extra steps that need to be done for the OMS repository in one blog post. 

The first thing that you want to do is make sure that you have a good backup, as 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 it. I also create flashback GRP (Guaranteed Restore Point) for this to have 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 and upgraded to 19.6  in the past months and 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, so before doing the conversion I needed to correct this. You can check with the query below to see if you have them in 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 2 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, it is highly recommended to 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 are valid and gather additional information prior to the conversion.

I use this query I called verify_data_dictionary to check if all components are valid and no invalid objects are 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 now, everything is ready to do the conversion. One thing to note is that in this post I won’t go into how to create a CDB, but this link can help you for that.I highly recommend that when creating the CDB, is to create it with the same components as the non-CDB or else you will have PDB (Pluggable DataBase) violations that you will have to clear before doing the conversion.

For the conversion, the first thing that you have to do is open the non-CDB in read only mode, generate the description XML file and shutdown the database. As we are dealing with an OMS, you first need to shutdown the OMS before doing 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 have done this, you now have to check if the plugging in the non-CDB to the CDB database will be compatible. You do this with the DBMS_PDB.CHECK_PLUG_COMPATIBILITY package. I will be changing the name of the database from EMREP to PDBEMREP

After running the compatibility package, you need to also check for any violations and clear any errors before doing the conversion. If you only get warnings, these can be skipped and you can proceed with the conversion if you want. 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.

As there were no errors in the compatibility check, I proceed to create the PDB as a copy and do the conversion using noncdb_to_pdb.sql. I did this COPY method due to small size and allowing me to have a fallback in case of an error during the conversion. Keep in mind that with a pretty big database the COPY option might not be the best option for you, so decide wisely when doing this.

Also after doing the conversion, for me it is important to save the PDB state as read-write, as I want it to be 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.

Now that the conversion is complete, I will create 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"

What I proceeded to do was to update the OMS repository details with the new service that I had created. For this I need 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 that 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, it is nothing that you haven’t read before, but as I mentioned, I just wanted to have the steps in the same place for future reference.

Tags:
,
Rene Antunez
[email protected]
No Comments

Sorry, the comment form is closed at this time.