cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

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

No ratings


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;

Comments

Hello

We are an Oracle shop so I was wondering if PTC officially supports Oracle 12c as an External Data Storage for ThingWorx?  I didn't see it listed anywhere in the list of comparable Database Storage Options for ThingWorx (taken from KCS Article CS257403).  Can someone please advise?

 

Many Thanks

Dan

 

Hi,

 

Oracle is not supported as Persistence Provider for Thingworx. But you can use Oracle database for different tasks by connecting to it via JDBC driver from Thingworx.

 

 Regards,

Raluca Edu

Spoiler
Hello

Do you know if PTC is considering supporting Oracle 12C as persistent provider in the future? If so do you have an estimated date when it would be supported or roadmap?

Also, you mentioned that I can used Oracle for different tasks.  Could you please provide examples of the tasks?

Thanks Again

Dan

Hi Dan,

 

For the moment, there is no information regarding Oracle as persistence provider.

Regarding what tasks can be done by connecting to Oracle DB with JDBC, for example you can extract data from Oracle to create a mashup/dashboard in Thingworx. Mashup could contain, for example, graphs,charts, tables, etc.

On the other hand, you can create a mashup that could help with inserting data into Oracle database (by creating fields to insert values, similar to Oracle Forms), or you could get some data from ThingWorx and you want to insert it in some Oracle table/s.

All of these operations should be put in a context of a business need, I have just provided some general examples. 

If you have other questions, please let me know.

 

Raluca Edu

 

Version history
Last update:
‎Dec 23, 2016 10:09 AM
Updated by:
Labels (1)
Attachments
Tags (2)