With the release of Oracle 12c Database the concept of Pluggable Databases (PDBs) was introduced. Within a Container Database (CDB) one or many of these PDBs can exist. Each PDB is a self-contained database, with its own system, sysaux and user tablespaces. Each database has its own unique service name, and essentially functions as a stand-alone database.
Oracle's Database as a Service (DBaaS) is based on 12c, and uses the same concepts.
Some applications - BI Cloud Service (BICS) as an example - require database connections to be defined using an Oracle SID, and not by a Service Name. By default, the SID is not externally available for these PDBs, which causes connection issues for these applications.
This article will outline a simple method by which the listener within a PDB in an Oracle DBaaS environment can be set to use the Service Name as a SID option, and thus allow these applications to connect.
More information can be found in this support note My Oracle Support note 1644355.1
The pre-requists for this approach are:
a. From within the DBaaS console, identify the PDB database and it's IP address.
b. Confirm that a connection can be made to the PDB using the service name. If it the connection can not be made, see these instructions on how to resolve this within the Compute Node.
c. Open Putty and Set Up a Connection using the IP of the PDB obtained in step (a) and port 22.
d. Expand the 'Connection' / 'SSH' / 'Auth' menu item. Browse in the 'Private key file for authentication' section to the key that the DBaaS administrator provided, and then click 'Open' in Putty to initiate the SSH session.
e. Login as the user 'opc' and enter the passphrase that the DBaaS administrator provided when prompted.
f. Use the following commands to change the user to 'oracle', and set the environmental variables:
sudo su - oracle
The correct Oracle SID should be displayed so you can just hit <enter> when prompted. Only change this if it does not match the SID displayed in the DBaaS console in step (a).
g. The next set of commands will change the working directory to the Oracle DB home, take a copy of the existing Listener.ora file, and then stop the listener:
cp listener.ora listener.oraBKP
h. The next commands will append the line 'USE_SID_AS_SERVICE_LISTENER=on' to the listener.ora file, and then re-start the Listener.
echo USE_SID_AS_SERVICE_LISTENER=on >> listener.ora
i. The final set of commands register the database to the listener.
sqlplus / as sysdba
alter system register;
j. The Service Name can now be used as a SID for applications that can only connect with a SID. Use SQL Developer to confirm that a connection can be made using the Service Name from before - but this time in the SID field:
k. If Apex is used, it may be necessary to make a change within the Apex listener to reference the service name. Before making the change, test to see if Apex is available by loging in. If it works, then no change is required. To make the change, follow steps (d) - (f) from above, and then type the following commands to locate the directory of the Apex listener configuration:
Make a copy of the apex.xml file, then edit it and change the <entry key="db.sid"> key to be the service name. Finally, go to the GlassFish Administration from the DBaaS Cloud Service Console (requires port 4848 to be accessible - this can be made available in the Compute Cloud console - see step (b) above):
Within the 'Applications' option, select 'Reload' under Apex.
This article walked through the steps to configure the listener in a DBaaS PDB database to allow connections based on the Service Name as a SID option.