PL/SQL-Debugging on Autonomous Database

Toad for Oracle & DevOps Toolkit
1

Toad 2026 R1 is unable to start a debugging session on autonomous databases. It displays "Unable to find RAC connection information for ..... (INST_ID=3).

I understand that Autonomous Databases run as a cluster database (i.e. DBMS_UTILITY.IS_CLUSTER_DATABASE = TRUE). But there are no means to create a TNS-Configuration-entry to identify each specific instance-Name of the Cluster-node.

Toad should check, if it is connected to an ADB and in that case does not need to identify a specific instance in the TNS-Data.

2

You can indeed make tnsnames entries for each node of an autonomous database. I have done this myself.

These entries are required when using RAC because the debugger session must be guaranteed to connect to the same node as the main session (otherwise debugging is not possible)

This is what mine looks like (details redacted)


# Pluggable, node 1
OraCloud_26c_Plug_RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxx.xx.xx)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)
                    (SERVICE_NAME=servicename.subprivate1.vcn12345.oraclevcn.com)
                    (INSTANCE_NAME = blahblah_1)))


# Pluggable, node 2
OraCloud_26c_Plug_RAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.yyy.yy.yy)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)
                    (SERVICE_NAME=servicename.subprivate1.vcn12345.oraclevcn.com)
                    (INSTANCE_NAME = blahblah_2)))
3

Maybe I should have been more precise in my initial post. I am talking about the "serverless" version of the Autonomous Database, where the instance-name is not a stable connection parameter, that is known. On a serverless ADB the IP-Addresses of the cluster are unknown, hence you cannot configure multiple RAC-Node-Entries.

Although I am able to add the instance-parameter to my tns-entry, once a connection is made, this is no stable, reliable solution, because any other day the available instance_name for my ADB may change. So: after I was able to start a debug session yesterday by adding an "instance_name"-parameter, I get the error "Unable to find RAC connection information for ..." today, since the instance-Number of my connection had changed.

So far the instance-Number for my connections remained stable across multiple connections within the same day. It only seems to change, when re-connecting after longer periods of disconnection (8hrs or more). Maybe the connection-mechanism für Debugging sessions in TOAD should be changed for "serverless ADB"-sessions, so that no "instance_name"-parameter is necessary.

4

Hi Markus,

Thanks for that info. I have not heard about this offering before, but after reading about it online I see what you mean about not being able to specify instance name.

Documentation says that I can run this SQL to determine if the DB is serverless. If serverless, the value in this field will be a JSON doc with 'Shared' somewhere in there. I have run it on my autonomous DB and it returns null (as expected)

To confirm, can you run this for me and let me know the result? Feel free to redact out anything sensitive, and if you prefer to email it to me rather than post it here, you can send it to john.dorlon@quest.com

SELECT cloud_identity 
FROM v$pdbs 
WHERE rownum = 1;

Also, it looks like I can run this SQL and look for the suffixes _high, _medium, _low, _tp, or _tpurgent on the service name

SELECT sys_context('USERENV', 'SERVICE_NAME') FROM dual;

Thanks.

5

Hi John,

We have ADB-D databases hosted in OCI. When running this query, there are no JSON values containing "shared", although there is "INFRASTRUCTURE" : "Dedicated".

We have no access to the GI in this environment, but selecting from gv$service does have an inst_id column (presently set to 1 for all of our services) that may be helpful.

Regards,

Doug

6

Hi Doug,

Thanks for that info. Is this a "Serverless" DB?

As I understand it, when you see "Dedicated" there, it means that this DB is running on an Exadata server, and if you see "Shared", then it is their "Serverless" offering.

I found that info here, about 4/5 of the way down the page.

7

Hi John,
here is the "cloud identity"-column from v$pdbs:
{
"DATABASE_NAME" : "ADBDEVMH",
"REGION" : "eu-frankfurt-1",
"TENANT_OCID" : "OCID1.TENANCY.OC1..blablah...",
"DATABASE_OCID" : "OCID1.AUTONOMOUSDATABASE.OC1.EU-FRANKFURT-1.ANTHELJblablah...",
"COMPARTMENT_OCID" : "ocid1.compartment.oc1..aaaaaaaau2fwblabla...",
"OUTBOUND_IP_ADDRESS" :
[
"132.xxx.xxx.xxx"
],
"PUBLIC_DOMAIN_NAME" : "``adb.eu-frankfurt-1.oraclecloud.com``",
"TENANT_ACCOUNT_NAME" : "<....>",
"AUTOSCALABLE_STORAGE" : false,
"BASE_SIZE" : 21474836480,
"INFRASTRUCTURE" : "Serverless",
"SERVICE" : "ATP",
"APPLICATIONS" :
[
"GRAPH",
"SPATIAL",
"DATAACCESSCOMMON",
"ODI",
"ORDS",
"DATABASEACTIONS",
"MONGODB",
"OMLMOD",
"APEX",
"OML"
],
"AVAILABILITY_DOMAIN" : "jRWc:EU-FRANKFURT-1-AD-3",
"CUSTOMER_LOGICAL_AZ" : null,
"LOGICAL_CPG_ID" : null,
"COMPUTE_MODEL" : "ECPU",
"COMPUTE_COUNT" : 4,
"COMPUTE_AUTOSCALING" : false,
"PE_FQDN" : null,
"AUTONOMOUS_DATA_GUARD" :
[
],
"PROVIDER_NAME" : "OCI",
"PRIVATE_IP_ADDRESS" : null,
"OUTBOUND_PRIVATE_IP_ADDRESS" : null,
"DATABASE_RESOURCE_ID" : "{"tenant_name":"OCID1.TENANCY.OC1..AAAAAAAAHMTblabla...","cloud_database_name":"OCID1.AUTONOMOUSDATABASE.OC1.EU-FRANKFURT-1.ANTHELblabla...","group_id":1803,"cloud_identifier_string":"ori:OCI:eu-frankfurt-1"}"
}

And yes, the service-name in the Userenv-Context for Service_Name corresponds to my tns-entry. In my case: GBA35C3573202B6_ADBDEVMH_tp.adb.oraclecloud.com

8 
SQL> select name, value from v$parameter where name = 'pdb_lockdown'

NAME                 VALUE     
-------------------- ----------
pdb_lockdown         PAAS

DBMS_CLOUD: Beware that starting with 19.9, this package can be manually installed on-prem (KB54212)

It appears that Markus is running on ADB-S (shared infrastructure) and we are running on ADB-D (dedicated infrastructure).

9

Thank you both for this.

It's too late to do anything for 2026R2 with this, but I'll make some changes for 2026R3 when beta becomes available again.

Are either of you able to use the beta in your environments?

10

I can install the beta on our OCI jump box, but have only been testing it against on-prem databases to date.

My testing may not be as effective as you would like, since in OCI our DBs apparently do not have enough eCPUs configured to go multi-node. All sessions for each of the DBs are residing on a single node. Some DBs on node1 and others on node2, but none of them span both nodes.

11

@markus_031 as a workaround, you may be able to use the JDWP debugger instead of DBMS debugger. I kind of hesitate to suggest this though because JDWP can be tricky to set up.

12

Thank you for that workaround John.

Though I prefer to adjust my tns-entry to whatever instance I am currently connected, once I start debugging. The main thing is that this won't be a permanent solution. I can definitely live with it until 2026-R3.