EM Express configuration for new pluggable database

We are in 12c; 12.1.0.1.0.
We will show how to create new pluggable database from seed and configure EM Express for it.

Oracle Enterprise Manager Database Express (EM Express) is a web-based database management tool that is built inside the Oracle Database.
From an architectural perspective, EM Express has no mid-tier or middleware components, ensuring that its overhead on the database server is negligible.

C:\Users\hberca>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Aug 18 15:36:09 2013

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> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Oracle XML DB is Mandatory in 12c – You Cannot Uninstall it


SQL> column comp_name format a30
SQL> column status format a20
SQL> column version format a20
SQL> select comp_name,status,version
  2  from dba_registry
  3  where comp_name like '%XML%';

COMP_NAME                      STATUS               VERSION
------------------------------ -------------------- -------------
Oracle XML Database            VALID                12.1.0.1.0

The DBMS_XDB_CONFIG package provides an interface for configuring Oracle XML DB and its repository (settings for the http protocol server)
It updates the settings for xdbconfig.xsd.

PL/SQL package DBMS_XDB_CONFIG is the Oracle XML DB resource application program interface (API) for PL/SQL for DBAs to configure their system. 
This API provides functions and procedures to access and manage Oracle XML DB Repository resources using PL/SQL. 
It includes methods for managing resource security and Oracle XML DB configuration.


We are using the procedure USEDPORT - this obtains the port numbers used by other pluggable databases in the same consolidation database.
We can see we have only configured port 5500, which is the default port for cdb$root which has by default con_id = 1.

SQL> select dbms_xdb_config.usedport from dual;

USEDPORT
--------------------------------------------------------------------------------
<portlist>
  <port>
    <pdbid>1</pdbid>
    <httpport2>5500</httpport2>
  </por


We are now creating new pluggable database from seed. We will use the below command:

SQL> create pluggable database pdb123 admin user pdb123 identified by oracle file_name_convert = ('pdbseed', 'pdb123');

Pluggable database created.

After creation, a new pluggable database is in MOUNT mode. We will open it.

SQL> alter pluggable database pdb123 open read write;

Pluggable database altered.

We will now query the view v$pdbs which will show us all pluggable database in this container.
We see PDB123 has con_id=5.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           MOUNTED
         4 PDB_SAMPLE                     MOUNTED
         5 PDB123                         READ WRITE
         6 Z                              READ WRITE
         
We are switching container to pdb123 in order to set the https port for EM express:         

SQL> alter session set container=pdb123;

Session altered.

SQL>  show con_id con_name

CON_ID
------------------------------
5

CON_NAME
------------------------------
PDB123

We will use SETHTTPSPORT procedure, this sets the HTTPS port to a new value, in our case 5502.

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);

PL/SQL procedure successfully completed.

We double check the port has been set using function GETHTTPSPORT:

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
        5502

If we attempt to view the usedports in the container from the pluggable database, an error will be raised:

SQL> select dbms_xdb_config.usedport from dual;
ERROR:
ORA-31120: usedPort cannot be invoked in a Pluggable Database
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 32

We will switch to CDB$ROOT.

SQL> alter session set container=CDB$ROOT;

Session altered.   


SQL> set long 10000
SQL> select dbms_xdb_config.usedport from dual;

USEDPORT
---------------------------------------------------------
<portlist>
  <port>
    <pdbid>1</pdbid>
    <httpport2>5500</httpport2>
  </port>
  <port>
    <pdbid>5</pdbid>
    <httpport2>5502</httpport2>
  </port>
</portlist>
     
We now see that pdbid 5, which is our newly create pluggable database pdb123 is allocated port 5502.    


The USEDPORT procedure is also useful to find the used ports in case we are trying to allocate same port to another pluggable database. We will hit the error ORA-44718: Port conflict in XDB Configuration file.

SQL> alter session set container=z;

Session altered.

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);
BEGIN DBMS_XDB_CONFIG.SETHTTPSPORT(5502); END;

*
ERROR at line 1:
ORA-44718: Port conflict in XDB Configuration file
ORA-06512: at "XDB.DBMS_XDB", line 528
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 375
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 226
ORA-06512: at line 1



Reference:
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)
E17602-14

Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-12