DatabaseVerse: Journey into the World of Databases

Demystifying Oracle initialization parameters in a multitenant architecture – Part 2

In my previous post Demystifying Oracle initialization parameters in a multitenant architecture – Part 1, I talked about how the db_files parameters behave in a multitenant architecture. If you haven’t read that post, I suggest taking a look as well. In this post, I will talk about open_links and you will see that it behaves…

fsarcosdb
5–8 minutes
,

In my previous post Demystifying Oracle initialization parameters in a multitenant architecture – Part 1, I talked about how the db_files parameters behave in a multitenant architecture. If you haven’t read that post, I suggest taking a look as well.

In this post, I will talk about open_links and you will see that it behaves differently than db_files. Let’s check it out!

  1. LAB ENVIRONMENT
    1. PARAMETERS SAMPLING TEST
  2. OPEN_LINKS
    1. Introduction
    2. TESTING
    3. Connecting as SYSDBA – weird behavior?
  3. Conclusion

LAB ENVIRONMENT

  • Oracle Database 23c Free
  • CDB: FREE
  • PDBs:
    • PDBFREE1
    • DATABAVERSE

PARAMETERS SAMPLING TEST

Parameters sampling test for this post will be:

  • open_links

Introduction

open_links parameter value specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

TESTING

Let’s check my current value for open_links:

set linesize 250
col pdb_name for a15
col name for a10
col value for a10
col display_value for a10
col description for a30
SELECT nvl(pdb.name, 'CDB$ROOT') pdb_name,  sp.name, sp.value, sp.display_value, sp.ISSES_MODIFIABLE, sp.ISSYS_MODIFIABLE, sp.ISPDB_MODIFIABLE, sp.ISINSTANCE_MODIFIABLE, sp.DESCRIPTION 
FROM v$system_parameter sp LEFT JOIN v$pdbs pdb on sp.con_id=pdb.con_id 
WHERE sp.name in ('open_links')
ORDER BY sp.name, pdb_name;

Ok, my parameter is set in my CDB$ROOT only and the value is 4. Let’s connect to my DATABASEVERSE PDB and create some database links and perform a few tests to check if 4 is the limit as well:

create public database link dblink1 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

create public database link dblink2 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

create public database link dblink3 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

create public database link dblink4 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

create public database link dblink5 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

I have created 5 public database links:

set linesize 250
set pagesize 10
col owner for a15
col db_link for a15
col username for a15
col host for a145
select owner, db_link, username, host from dba_db_links;

Let’s open the connections for each database link and check the outcome: 

select sysdate from dual@dblink1;
select sysdate from dual@dblink2;
select sysdate from dual@dblink3;
select sysdate from dual@dblink4;
select sysdate from dual@dblink5;

Ok, so I was able to open 4 remote connections successfully until I tried the 5th and it failed with the message ORA-02020: too many database links in use.

Let’s see how many remote connections I have opened in my session (this is for each session)

select inst_id, db_link,logged_on,open_cursors from gv$dblink order by inst_id, db_link;

So, my PDB is restricting my session to open only 4 remote connections as the open_links parameter is set to 4 in my CDB$ROOT. That means that the PDB inherits the value from the CDB$ROOT.

Let’s change the value in the PDB to 3 and see how it will behave. The open_links parameter is modifiable at PDB level, but it requires a restart to take effect. If you change this value at the CDB level, it also requires a restart of the instance to take effect.

alter session set container = DATABASEVERSE;
alter system set open_links = 3 container=current scope=spfile;
alter pluggable database DATABASEVERSE close immediate;
alter pluggable database DATABASEVERSE open;

BTW, if you have the following message, it is because your current session has the dblinks connection opened. You can just run the rollback or commit command, or close the session and open a new one to avoid the following message when trying to close the PDB:

Connected to my CDB$ROOT, let’s check current value now:

Great, so in my CDB$ROOT value for open_links is 4 and in my DATABASEVERSE PDB is 3. Let’s do some testing.

Connected to my PDB, let’s open some remote connections:

select sysdate from dual@dblink1;
select sysdate from dual@dblink2;
select sysdate from dual@dblink3;
select sysdate from dual@dblink4;

So, the open_links parameter value for the PDB has priority over the one set in the CDB$ROOT, and remote connections for sessions is restricted to 3, same as value set in the PDB.

Let’s do one more exercise, I will increase the value at PDB level to 7 to check if this value can be higher than the one set in the CDB$ROOT.

In my previous post about db_files Demystifying Oracle initialization parameters in a multitenant architecture – Part 1 it didn’t work, if the limit for db_files in the PDB is higher than the CDB$ROOT then it is ignored in the PDB, but it is the same behavior for open_links? let’s find out!

alter session set container = DATABASEVERSE;
alter system set open_links = 7 container=current scope=spfile;
alter pluggable database DATABASEVERSE close immediate;
alter pluggable database DATABASEVERSE open;

After doing the changes, let’s take a look to the value for open_links:

Great, CDB$ROOT has 4 and my DATABASEVERSE PDB has the value for open_links set to 7. Before performing the test let’s create 3 more database links in my PDB:

create public database link dblink6 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

create public database link dblink7 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

create public database link dblink8 connect to system identified by Welcome123# using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DATABASEVERSE)))';

set linesize 250
set pagesize 10
col owner for a15
col db_link for a15
col username for a15
col host for a145
select owner, db_link, username, host from dba_db_links;

Great, I have 8 database links created in my PDB.

I will try to open the connection using all the database links I have created:

Ok, this is interesting. I was able to open 7 remote connections. So my PDB is totally ignoring whatever is set in the CDB$ROOT, but is that right? I guess makes sense, it’s it? but I have noted some interesting behavior if you connect locally with SYSDBA…

Connecting as SYSDBA – weird behavior?

The previous scenario is true if you have opened a connection remotely to the PDB, otherwise the CDB$ROOT value is take into consideration, not sure why to be honest, and I’m not really sure if I can call this a bug… but let me demonstrate it:

So, as you can see above in the screenshot, if you open the connection locally and connect to the PDB after it the outcome is quite different. I was able to open only 4 connections, same as the one I have set in my CDB$ROOT, even when I’m opening the connection “connected” to my PDB.

Conclusion

The open_links parameter value has some considerations that you may be aware of:

  • If you don’t set the open_links value at PDB level, then the value will be inherited from the CDB.
  • If you set the open_links value at PDB level then this value can be less than, equal or higher than the value set in the CDB$ROOT.
  • If you have multiple PDBs in your CDB, I think it is a good idea to set this value for each one of them individually. Again, this is a personal opinion. You should review what is better and appropriate for your environment, also make sure to test it.

And most importantly: make sure to review this parameter value if you are moving a PDB (unplug/plug) or migrating your NON-CDB to CDB. Otherwise, if your old database instance used to have a higher value for any reason and you haven’t taken this into consideration in your new PDB, then your PDB might inherit its new value from the CDB$ROOT. If that value is very low, your sessions or apps will fail with ORA-02020: too many database links in use.

Leave a comment

Trending