How to configure Oracle 12c's Pluggable Database as External DataStorage for ThingWorx

[supandey]

Introduction

Oracle 12c release introduced the concept of multi-tenant architecture for housing several databases running as service under a single database, I'll try to address the connectivity and required configuration to connect to one of the Pluggable database running in the multi-tenant architecture.

Multi-tenant database architecture in scope of ThingWorx External Data Source

What is multi-tenant Database architecture ?

Running multiple databases under a single database installation. Oracle 12c allows user to create one database called Container Database (CDB) and then spawn several databases called Pluggable Databases (PDB) running as services under it.

Why use multi-tenant architecture?

Such a setup allows users to spawn a new PDB as and when needed with limited resource requirements, easily administer several PDBs just by administering the container database - since all the PDBs are contained within a single database's tablespace structure, start and stop individual PDB leading to low cost on maintaining different databases - as the resource management is limited to one CDB.

When to use multi-tenant architecture?

In scenarios like creating PoCs, different test environments requiring external data storage, maintaining different versions of dataset, having this run in the multi-tenant architecture could help save time, money and effort.

Create Container Database (CDB)

Creation of a Container Database (CDB) is not very different from creating a non Container Database use the attached guide Installing Oracle Database Software and Creating a Database.pdf same is accessible online.

Create Pluggable Database (PDB)

Use the attached Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c PDF guide to create and plug a Pluggable Database into the Container Database created in previous step, same is accessible online

Using above guide I have bunch of pluggable databases as can be seen below. I'll be using TW724 for connecting to ThingWorx server as an external datasource for following example

Connect to a Pluggable Database(PDB) as external data source for ThingWorx

1. Download and unzip the Relational Databases Connectors Extension from ThingWorx Marketplace and extract Oracle12Connector_Extension
2. Import Oracle12Connector_Extension to the ThingWorx using Extension -> Import
3. Create a Thing using OracleDBServer12 Thing Template , e.g. TW724_PDB_Thing
4. Navigate to the Configurations for TW724_PDB_Thing to update the default configuration:
   1. JDBC Driver Class Name : oracle.jdbc.OracleDriver
   2. JDBC Connection String : jdbc:oracle:thin:@//oravm.ptcnet.ptc.com:1521/tw724.ptcnet.ptc.com
   3. Database Username : <UserName>
   4. Database Password : <password>

  5. Once done save the entity

Note: A PDB in a container database can be reached only as a service and not using the CDB's SID. In the above configuration TW724 is a PDB which can be connected to via it's service name i.e. TW724.PTCNET.PTC.COM

Let's head to the Services tab for TW724_PDB_Thing to query and access the PDB data

Creating Services to access the PDB as external database source for ThingWorx

Once the configuration is done the TW724_PDB_Thing is ready for use. The queries remain the same as any other SQL query needed to access the data from Oracle.

Service for creating a Table

Once on the Services tab for the TW724_PDB_Thing click on Add My Service select the service handler as SQL Command to use following script to create a testTable1 in the PDB

create table testTable1 (

    id NUMBER GENERATED ALWAYS AS IDENTITY primary key,

    col1 varchar2(100),

    col2 number

)

Note: GENERATED ALWAYS AS IDENTITY option is Oracle 12c specific and I included it here for the reason that with Oracle 12c the possibility to auto generate is now built in with that option simplifying the sequence generation when compared with older Oracle versions such as Oracle 11g. User creating table will need access right on creating table and sequence checkout the Oracle documentation on Identity for more on this.

Service for getting all the data from the table

Add another service with script Select * from testTable1 for getting all the data from the table

Service for inserting data into the table

Adding another service with script insert into testTable1 (col1, col2) values ('TextValue', 123)  will insert the data into the table created above

Service for getting all tables from the PDB i.e. TW724

Using Select * from tab lists all the available tables in the TW724 PDB

Summary

Just a quick wrap up on how this would look visually refer to the following image. Since this is a scalable setup - given the platform having enough resources it's possible to create upto 252 PDBs under a CDB therefore 252 PDBs could be created and configured to as many things extending the OracleDBServer12 Thing.

______________________________________________________________________________________________________________________________________________

Edit:

Common Connection Troubleshooting

If you observe the error something like this Unable to Invoke Service GetAllPDBTables on TW724_PDB_Thing : ORA-01033: ORACLE initialization or shutdown in progress

Ensure that the pluggable database, in this error TW724 (since this is what I created and used above in my services) is opened and accessible. If it's not opened use the command after logging in as sys/system (with admin rights) in CDB, which is ORCL in via SQL*Plus or SQL Developer or any SQL utility of your choice capable of connecting to Oracle DB and open the pluggable database using the command : alter pluggable database tw724 open;