Migrate Enterprise Manager 12.1.0.4.0 to a PDB from a non-CDB

One of the things I’ve been eager to try with the latest (12.1.0.4.0) EM release was to change the repository from a non-CDB to a PDB. There’s a note in the EM documentation that discusses migrating the SYSMAN schema option mentioned here. As a mentionable note, Martin Bach wrote a post on switching the repository on 12.1.0.1.0.

My existing configuration:

OMS: 12.1.0.4.0
OMR: 12.1.0.1.0
SID: sindy

Intended configuration.

CDB: comicscdb
PDB: JL

There are two major steps in the progress:

Migrate the repository to a PDB from a non-CDB.
Reconfigure OMS to point to the new repository.

Note: If you did not have a non-CDB 12.1.0.1.0 database repository, for example an 11gR2 database, then you must upgrade it to a 12.1.0.1.0 version before moving forward.

Let’s begin!

1. Migrate Repository from non-CDB to PDB

Tim Hall happened to write an excellent post on how to accomplish this easily. He mentioned 3 methods, and I will use the DBMS_PDB one.

1.1. Where is the existing repository?

[oracle@as-sandbox-n1 ~]$ emctl config oms -list_repos_details
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=as-sandbox-n1)(PORT=1521)))(CONNECT_DATA=(SID=sindy)))
Repository User : SYSMAN

1.2. First, we need to shutdown oms completely.

[oracle@as-sandbox-n1 batman]$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

1.3. Next, shutdown and startup the sindy in read only mode.

ORACLE_SID = [OEM] ? sindy
-bash: [: too many arguments
The Oracle base has been set to /u01/app/oracle
[oracle@as-sandbox-n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 13:52:31 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 4960579584 bytes
Fixed Size 2298544 bytes
Variable Size 1275071824 bytes
Database Buffers 3674210304 bytes
Redo Buffers 8998912 bytes
Database mounted.
Database opened.
SQL>

1.4. Using the DBMS_PDB.DESCRIBE procedure, create an XML file.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/tmp/sindy.xml');
END;
/

PL/SQL procedure successfully completed.

1.5. Again, shutdown sindy.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

1.6. Next, we connect to the container database (comicscdb) and create a new PDB using the XML file generated in step 1.4. Please note the use of FILE_NAME_CONVERT to copy datafiles to a new location. Since I’m using Oracle Managed Filesystem, I have to specify each data file(s) (except UNDO) and temp file(s). Thanks to Daniel Hafner on his blog post for the solution.

[oracle@as-sandbox-n1 ~]$ . oraenv
ORACLE_SID = [sindy] ? comicscdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@as-sandbox-n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 14:07:34 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE PLUGGABLE DATABASE jl USING '/tmp/sindy.xml'
COPY
FILE_NAME_CONVERT = (
'/u03/oracle/oradata/SINDY/datafile/o1_mf_system_92w8kslh_.dbf', '/u03/oracle/oradata/comicscdb/jl/system.dbf',
'/u03/oracle/oradata/SINDY/datafile/mgmt_ecm_depot1.dbf', '/u03/oracle/oradata/comicscdb/jl/mgmt_ecm_depot1.dbf',
'/u03/oracle/oradata/SINDY/datafile/o1_mf_sysaux_92w8j2h5_.dbf', '/u03/oracle/oradata/comicscdb/jl/sysaux.dbf',
'/u03/oracle/oradata/SINDY/datafile/mgmt.dbf', '/u03/oracle/oradata/comicscdb/jl/mgmt.dbf',
'/u03/oracle/oradata/SINDY/datafile/o1_mf_users_92w8mjq6_.dbf', '/u03/oracle/oradata/comicscdb/jl/users.dbf',
'/u03/oracle/oradata/SINDY/datafile/mgmt_deepdive.dbf', '/u03/oracle/oradata/comicscdb/jl/mgmt_deepdive.dbf',
'/u03/oracle/oradata/SINDY/datafile/o1_mf_veridata_96lsh7mn_.dbf', '/u03/oracle/oradata/comicscdb/jl/veridata.dbf',
'/u03/oracle/oradata/SINDY/datafile/o1_mf_ts_mdba_97hwhc99_.dbf', '/u03/oracle/oradata/comicscdb/jl/mdba.dbf',
'/u03/oracle/oradata/SINDY/datafile/o1_mf_temp_92w8nlr8_.tmp','/u03/oracle/oradata/comicscdb/jl/temp01.dbf',
'/u03/oracle/oradata/SINDY/datafile/o1_mf_veridata_96lshmsj_.tmp','/u03/oracle/oradata/comicscdb/jl/veridata_temp.dbf'
);

Pluggable database created.

SQL>

1.7. At this point, we are ready to convert the non-CDB to a PDB using the provided script.

SQL> alter session set container=jl;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql
...
SQL>
SQL> -- reopen the PDB
SQL> alter pluggable database "&pdbname" close;
old   1: alter pluggable database "&pdbname" close
new   1: alter pluggable database "JL" close
...
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>

1.8. Switch to the new container and open it!

SQL> ALTER SESSION SET CONTAINER=jl;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
4 JL                             READ WRITE NO

SQL>

1.9. Check whether the new PDB is registered correctly again the listener.

[oracle@as-sandbox-n1 ~]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 11-JUN-2014 15:01:43
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "comicscdb" has 1 instance(s).
Instance "comicscdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "comicscdbXDB" has 1 instance(s).
Instance "comicscdb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: as-sandbox-n1.bias.com, pid: 25818>
(ADDRESS=(PROTOCOL=tcp)(HOST=as-sandbox-n1)(PORT=46301))
Service "jl" has 1 instance(s).
Instance "comicscdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

That concludes the first step in the migration process.

2. Reconfigure OMS to point to new PDB Repository

We shutdown the original non-CDB database in the previous section, however it does need to be up for this section.

2.1. Startup the existing repository database (sindy).

2.1. Since we already shutdown OMS in 1.2, the next step is to start it up in admin mode.

[oracle@as-sandbox-n1 jl]$ emctl start oms -admin_only
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Admin Server only...
Admin Server Successfully Started

2.2. Modify the OMS’s connect descriptor as shown below. I used the output from step 1.1. and edited it accordingly to point to the new PDB. Please note that I use the SERVICE_NAME not SID in the connect descriptor. This is the only way to connect to a PDB using a listener.

[oracle@as-sandbox-n1 jl]$ emctl config oms \
-store_repos_details \
-repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=as-sandbox-n1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=jl)))" \
-repos_user SYSMAN \
-repos_pwd welcome1
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
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.

2.3. Since we started the admin server in step 2.1, we need stop OMS with the “-all” option and restart.

[oracle@as-sandbox-n1 jl]$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server is not running
AdminServer Successfully Stopped
Oracle Management Server is Down

[oracle@as-sandbox-n1 jl]$ emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

2.4. Finally, change the monitoring configuration of the OMS and OMR target.

[oracle@as-sandbox-n1 jl]$ emctl config emrep -conn_desc
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Please enter repository password:
Enter password
Login successful
Target "Management Services and Repository:oracle_emrep" modified successfully
Command completed successfully!

3. Post Steps

3.1. Did it work? Let’s take a look at the console and OMS’s properties.

NewImage

3.2. Yes, it worked!

NewImage

3.3. Did the JL PDB get added as a managed target? Doesn’t look like it, but that is easily fixable.

NewImage

3.4. Head over to the “Configure Auto Discovery” home page.

NewImage

3.5. Run a discovery on the host where the repository is located. In my case, both are on the same page. Once discovery is complete, click on the # under the Discovered Targets column.

NewImage

3.6. Neither the CDB (comicscdb) or its associated PDB’s were promoted yet. Click on the CDB and then “Promote”.

NewImage

3.7. Enter the DBSMP account password and test the connection. Click “Next” to proceed.

NewImage

3.8. Review the settings and click “Save”.

NewImage

3.9. Search once more, and the target is now a managed resource in Enterprise Manager.

NewImage

Conclusion

I found this experience to be quite a relevant example for conversion of a non-CDB to a PDB. Why did I use a pluggable container to begin with? Well, why use PDB’s in the first place? It could be that you decide to share enterprise manager repository resources with other databases presumably because you want to consolidate or are simply curious like myself 🙂

Either way, I’d be interested to know whether you would consider a PDB as EM’s repository database. Please leave me your feedback, should make for an interesting conversation.

Hope this helps.

Cheers!