DatabaseVerse: Journey into the World of Databases

Many things started to change when Oracle 12c was announced (long time ago, feeling old right now). Oracle’s multitenant architecture was introduced and there was a lot of confusion (to be honest) about how it worked. Everything seems to be clear now, but there are still customers that are not using the Multitenant architecture yet. If you are a customer or you are operating/maintaining Oracle databases, it is important that you know and are aware that the non-CDB architecture was deprecated in version 12c and is unsupported in version 21c.

It is highly advisable to transition from non-CDB to CDB architecture in a timely manner if you have not already done so.

It is obvious that challenges will arise when migrating your database from a non-CDB to a CDB architecture. It is not “transparent” for all use cases, and this is one of the points I wanted to discuss.

In my previous post, I talked about similar situations and commented about two important variables and how to use them. This post is like a continuation from the previous one. If you haven’t read it, you can take a look now or later here: ORACLE_SID AND TWO TASK

Ok, let’s assume for this example a couple of things:

• My app is legacy and it only connects to my database using the ORACLE_SID
• My app user is appuser
• My Database is a non-CDB, database name is DATABASEVERSE and ORACLE_SID = DATABASEVERSE
• My app is using a TNS entry as below:

MyDatabase =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = DATABASEVERSE)
    )
  )

Great, with that information in mind I can simulate the connection using sqlplus for example as follow:

export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree
export TNS_ADMIN=$ORACLE_HOME/network/admin
sqlplus appuser/pass@MyDatabase 

Now… Let’s assume that my database: DATABASEVERSE was migrated from non-CDB to CDB architecture and it is a PDB now as below:

Let’s see if I can connect:

BTW, message in 23c version is as above, in previous version looks like this:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

So, connection failed… why?

Well, the DATABEVERSE database is now a PDB. A PDB is not an instance and connections to a pluggable database use SERVICE_NAME and not SID.

Let’ check the listener:

There is a SERVICE_NAME = databaseverse and as you can see above, there is one instance related. The instance name is FREE, which is where my PDB belongs right now.

And you may be wondering, what can be done in this situation? My application is only able to establish a connection when using ORACLE_SID, and it is unable to utilize SERVICE_NAME.

Don’t worry, a solution is here to help!

MAGIC TRICK: USE_SID_AS_SERVICE_<listener_name> = ON

There is a control parameter that can be added in the listener.ora file, that parameter is: USE_SID_AS_SERVICE_<listener_name> = ON

This will allow the system identifier (SID) in the connect descriptor to be understood as a service name when a user tries to connect to a database.

Database clients with earlier releases of Oracle Database that have hard-coded connect descriptors using SID, or in this case, an old application that connects only if SID is used, can continue as is and be able to connect to a container or pluggable database.

So, let’s configure the parameter and see if I’m able to connect.

First, let’s set the control parameter in the listener.ora file:

After changing the listener.ora file, I have restarted the listener. Let’s see if I am able to connect using the SID:

As you can see, I was able to connect to my PDB, this is because the listener will interpret the value for SID = DATABASEVERSE as SERVICE_NAME = DATABASEVERSE and allow the connection.

Wonderful, my app is able to connect now!

In conclusion, migrating from non-CDB to CDB architecture in Oracle 12c and beyond is essential for the longevity and supportability of your database. Although there may be challenges, configuring the listener parameter “USE_SID_AS_SERVICE” can help ensure your legacy applications can still connect seamlessly. Don’t delay the transition and reap the benefits of a robust and future-proof Oracle architecture.

As always, it is crucial to exercise caution when implementing the advice provided in this post. Adapting recommendations to specific needs is essential, as blindly following them can lead to unexpected issues. Always prioritize thorough testing before deploying this kind of changes to production environments, ensuring functionality, compatibility, and security. By doing so, you can mitigate risks and ensure successful implementations. Stay mindful, proceed with caution, and prioritize testing for reliable and secure outcomes.