VMware: Configure Oracle DB 12c for vCloud Director

With latest 12c release Oracle radically changed how databases are managed by introducing the concept of Pluggable Database. What's that? A pluggble database, or simply "PDB", is basically a database contained in another database which acts like a root container. This brings-in multitennancy capabilities in database without having to create a different instance for every tennant.

This new concept has slightly changed the way you perform tasks such as creating users, tablespaces and managing roles so I decided to come up with this post exaplaining how to set up an Oracle Database 12c for vCloud Director.

Prior to start please have a look at official vCloud Director Configure an Oracle Database documentation.

vCloud Director requires some tasks to be performed on Oracle Database:

-Create two tablespaces: CLOUD_DATA, CLOUD_INDX
-Create a user which will be using these tablespaces
-Assign certain permissions to this user
-Set up properly database initialization parameters

In Oracle Database 12c Enteprise Manager has been replaced by Enterprise Manager Express. An extremely slimmed-out version of 'ol classic EM.
Enterprise Manager Express is enabled by default only on root container and not on PDBs. After database installation completed a screen reported Enterprise Manager Express URL, which was something similar to this:

https://<IP_Address_or_FQDN>:5500/em

Where the port 5500 can be easily customized. For example in Oracle 11g by default it was port 1158.

Following screen depicts Enterprise Manager Express console for root container:

As you can see Type reports ORCL as single instance database having 1 PDB, furthermore browsing upper screen menu no tablespace item exists. Tablespaces will be created in PDBs.

Despite all commands can be performed using SQL may be conforting having a GUI on which you can check if everything has been created/modified as expected so let's first enable Enterprise Manager Express on PDBs.

This is done issuing a SQL command so open SQLPlus and connect to database as sysdba.

 connect sys/<password>@<container> as sysdba  

SQL> connect sys/mypassword@ORCL as sysdba

Connected

To issue commands directly to PDB and not to root container you need to alter the scope of your SQL session. This can be done with following command:

 alter session set container=<PDB_name>;  

SQL> alter session set container=PDBORCL;

Session altered.

To enable Enterprise Manager Express console for PDB:

 exec dbms_xdb_config.sethttpsport(<EM_Port>);  

SQL> exec dbms_xdb_config.sethttpsport(5501);

PL/SQL procedure completed

EM console is now available even for PDB and as you can see here Tablespace menu item has appeared:

Let's now perform vCloud Director's related configurations as reported in official documentation.

Tablespace creation can be done either via Enterprise Manager Express or by issuing SQL commands. If done via SQL ensure you are in the correct PDB scope:

 alter session set container=<PDB_name>;  

Create CLOUD_DATA and CLOUD_INDX tablespaces.

Edit $ORACLE_HOME variable according to your current home directory which in my case, on a Windows Server, is:

C:\app\Administrator\

 Create Tablespace CLOUD_DATA datafile '$ORACLE_HOME/oradata/cloud_data01.dbf' size 1000M autoextend on;  
 Create Tablespace CLOUD_INDX datafile '$ORACLE_HOME/oradata/cloud_indx01.dbf' size 500M autoextend on;  

Create a new user who will access these tablespaces:

 Create user <vcloud_user> identified by <vcloud_user_password> default tablespace CLOUD_DATA;  

And assign to it the following privileges:

- CONNECT (already granted to default role)
- RESOURCE (already granted to default role)
- CREATE TRIGGER
- CREATE TYPE
- CREATE VIEW
- CREATE MATERIALIZED VIEW
- CREATE PROCEDURE
- CREATE SEQUENCE

This can be done either via SQL or via Enterprise Manager Express.

Since vcloud user relies on default profile and will inherit default profile's attributes it could be a good move to change default password expiration lease of 180 days.

Last but not least we need to size our database parameters according to the ones suggested by VMware in vCloud Director documentation.

- CONNECTIONS = 75*<Number_of_vCloud_Director_Cells>+50
- PROCESSES = CONNECTIONS
- SESSIONS = PROCESSES*1.1+5
- TRANSACTIONS = SESSIONS*1.1
- OPEN_CURSORS = SESSIONS


As usual this is feasible either via SQL or via Enterprise Manager Express.

If done via SQL synthax is the following:

 alter system set <parameter>=<value> scope=<memory_AND/OR_spfile> sid=<sid>  

For example:

alter system set open_cursors=500 scope=both sid='*';

will change open_cursors parameters to 500, this will apply to both memory (changes not consistent across reboots) and spfile (changes consistent across reboots) and will affect all instances, including nested PDBs.

Please bear in mind that some configuration parameters must be edited at root container level and cannot be modified at PDB level.

This is well shown using Enterprise Manager Express, as following image depicts, processes parameter cannot be modified at PDB level.

Editing a parameter at both root DB or PDB using Enterprise Manager Express is really simple. Just go to Configuration -> Parameters, browse for the parameter you need to edit and click Set icon.

That's all!!