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:
- Run
@?/rdbms/admin/utluptabdata.sql - 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.
