This was first published on https://blog.dbi-services.com/service-696c6f76656d756c746974656e616e74-has-1-instances (2017-04-08)
Republishing here for new followers. The content is related to the the versions available at the publication date
Weird title, isn’t it? That was my reaction when I did my first ‘lsnrctl status’ in 12.2: weird service name… If you have installed 12.2 multitenant, then you have probably seen this strange service name registered in your listener. One per PDB. It is not a bug. It is an internal service used to connect to the remote PDB for features like Proxy PDB. This name is the GUID of the PDB which makes this service independent of the name or the physical location of the PDB. You can use it to connect to the PDB, but should not. It is an internal service name. But on a lab, let’s play with it.
I have two Container Databases on my system:
18:01:33 SQL> connect sys/oracle@//localhost/CDB2 as sysdba Connected. 18:01:33 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------ -------- ---- ---- ---------- 2 PDB$SEED READ ONLY NO
CDB2 has been created without any pluggable databases (except PDB$SEED of course).
18:01:33 SQL> connect sys/oracle@//localhost/CDB1 as sysdba Connected. 18:01:33 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------ -------- ---- ---- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1 READ WRITE NO
CDB1 has one pluggable database PDB1.
PDB1 has its system files in /u01/oradata/CDB1/PDB1/ and I’ve a user tablespace datafiles elsewhere:
18:01:33 SQL> select con_id,file_name from cdb_data_files; CON_ID FILE_NAME ------ ------------------------------------- 1 /u01/oradata/CDB1/users01.dbf 1 /u01/oradata/CDB1/undotbs01.dbf 1 /u01/oradata/CDB1/system01.dbf 1 /u01/oradata/CDB1/sysaux01.dbf 4 /u01/oradata/CDB1/PDB1/undotbs01.dbf 4 /u01/oradata/CDB1/PDB1/sysaux01.dbf 4 /u01/oradata/CDB1/PDB1/system01.dbf 4 /u01/oradata/CDB1/PDB1/USERS.dbf 4 /var/tmp/PDB1USERS2.dbf
Both are registered to the same local listener:
SQL> host lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-APR-2017 18:01:33 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 07-APR-2017 07:53:06 Uptime 0 days 10 hr. 8 min. 27 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/VM104/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=1521))) Services Summary... Service "4aa269fa927779f0e053684ea8c0c27f" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "CDB1" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "CDB1XDB" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "CDB2" has 1 instance(s). Instance "CDB2", status READY, has 1 handler(s) for this service... Service "CDB2XDB" has 1 instance(s). Instance "CDB2", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... The command completed successfully
Each container database declares its db_unique_name as a service: CDB1 and CDB2, with an XDB service for each: CDB1XDB and CDB2XDB, each pluggable database has also its service: PDB1 here. This is what we had in 12.1 but in 12.2 there is one more service with a strange name in hexadecimal: 4aa269fa927779f0e053684ea8c0c27f
Want to know more about it? Let’s try to connect to it:
SQL> connect sys/oracle@(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=4aa269fa927779f0e053684ea8c0c27f))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.104)(PORT=1521))) as sysdba Connected. SQL> select sys_context('userenv','cdb_name'), sys_context('userenv','con_name'), sys_context('userenv','service_name') from dual; SYS_CONTEXT('USERENV','CDB_NAME') SYS_CONTEXT('USERENV','CON_NAME') SYS_CONTEXT('USERENV','SERVICE_NAME') --------------------------------- --------------------------------- ------------------------------------- CDB1 PDB1 SYS$USERS
With this service, I can connect to the PDB1 but the service name I used in the connection string is not a real service:
SQL> select name from v$services; NAME ---------------------------------------------------------------- pdb1 SQL> show parameter service NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string CDB1
The documentation says that SYS$USERS is the default database service for user sessions that are not associated with services so I’m connected to a PDB here without a service.
The internal service name is the GUID of the PDB, which identifies the container even after unplug/plug.
SQL> select pdb_id,pdb_name,con_uid,guid from dba_pdbs; PDB_ID PDB_NAME CON_UID GUID ------ -------- ------- ---- 4 PDB1 2763763322 4AA269FA927779F0E053684EA8C0C27F
This internal service has been introduced in 12cR2 for Proxy PDB feature: access to a PDB through another one, so that you don’t have to change the connection string when you migrate the PDB to another server.
I’ll create a Proxy PDB in CDB2 to connect to PDB1 which is in CDB1. This is simple: create a database link for the creation of the Proxy PDB which I call PDB1PX1:
18:01:33 SQL> connect sys/oracle@//localhost/CDB2 as sysdba Connected. 18:01:33 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------ -------- ---- ---- ---------- 2 PDB$SEED READ ONLY NO 18:01:33 SQL> create database link CDB1 connect to system identified by oracle using '//localhost/CDB1'; Database link CDB1 created. 18:01:38 SQL> create pluggable database PDB1PX1 as proxy from PDB1@CDB1 file_name_convert=('/u01/oradata/CDB1/PDB1','/u01/oradata/CDB1/PDB1PX1'); Pluggable database PDB1PX1 created. 18:02:14 SQL> drop database link CDB1; Database link CDB1 dropped.The Proxy PDB clones the system tablespaces, and this is why I had to give a file_name_convert. Note that the user tablespace datafile is not cloned, so I don’t need to convert the ‘/var/tmp/PDB1USERS2.dbf’. The dblink is not needed anymore once the Proxy PDB is created, as it is used only for the clone of system tablespaces. The PDB is currently in mount.
18:02:14 SQL> connect sys/oracle@//localhost/CDB2 as sysdba Connected. 18:02:14 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------ -------- ---- ---- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1PX1 MOUNTED
The system tablespaces are there (I’m in 12.2 with local undo which is required for Proxy PDB feature)
18:02:14 SQL> select con_id,file_name from cdb_data_files; CON_ID FILE_NAME ------ --------- 1 /u01/oradata/CDB2/system01.dbf 1 /u01/oradata/CDB2/sysaux01.dbf 1 /u01/oradata/CDB2/users01.dbf 1 /u01/oradata/CDB2/undotbs01.dbf
I open the PDB
18:02:19 SQL> alter pluggable database PDB1PX1 open; Pluggable database PDB1PX1 altered.
I have now 3 ways to connect to PDB1: with the PDB1 service, with the internal service, and through the Proxy PDB service. I’ve tested the 3 ways:
18:02:45 SQL> connect demo/demo@//localhost/PDB1 18:02:56 SQL> connect demo/demo@//localhost/PDB1PX1 18:03:06 SQL> connect demo/demo@//localhost/4aa269fa927779f0e053684ea8c0c27f
and I’ve inserted each time into a DEMO table the information about my connection:
SQL> insert into DEMO select '&_connect_identifier' "connect identifier", current_timestamp "timestamp", sys_context('userenv','cdb_name') "CDB name", sys_context('userenv','con_name') "con name" from dual;
Here is the result:
connect identifier timestamp CDB name container name ------------------ --------- -------- -------------- //localhost/PDB1 07-APR-17 06.02.50.977839000 PM CDB1 PDB1 //localhost/PDB1PX1 07-APR-17 06.03.01.492946000 PM CDB1 PDB1 //localhost/4aa269fa927779f0e053684ea8c0c27f 07-APR-17 06.03.11.814039000 PM CDB1 PDB1
We are connected to the same databases. As for this test I’m on the same server with same listener, I can check what is logged in the listener log.
Here are the $ORACLE_BASE/diag/tnslsnr/$(hostname)/listener/alert/log.xml entries related to my connections.
When connecting directly to PDB1 the connection is simple:
<msg time='2017-04-07T18:02:45.644+02:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='VM104' host_addr='192.168.78.104' pid='1194'> <txt>07-APR-2017 18:02:45 * (CONNECT_DATA=(SERVICE_NAME=PDB1)(CID=(PROGRAM=java)(HOST=VM104)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=27523)) * establish * PDB1 * 0 </txt> </msg>
I am connecting with SQLcl which is java: (PROGRAM=java)
When connecting through the Proxy PDB I see the connection to the Proxy PDBX1:
<msg time='2017-04-07T18:02:56.058+02:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='VM104' host_addr='192.168.78.104' pid='1194'> <txt>07-APR-2017 18:02:56 * (CONNECT_DATA=(SERVICE_NAME=PDB1PX1)(CID=(PROGRAM=java)(HOST=VM104)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=27524)) * establish * PDB1PX1 * 0 </txt> </msg>
This is the java connection. But I can also see the connection to the remote PDB1 from the Proxy PDB
<msg time='2017-04-07T18:03:01.375+02:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='VM104' host_addr='192.168.78.104' pid='1194'> <txt>07-APR-2017 18:03:01 * (CONNECT_DATA=(SERVICE_NAME=4aa269fa927779f0e053684ea8c0c27f)(CID=(PROGRAM=oracle)(HOST=VM104)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.104)(PORT=16787)) * establish * 4aa269fa927779f0e053684ea8c0c27f * 0 </txt> </msg>
Here the program is (PROGRAM=oracle) which is a CDB2 instance process connecting to the CDB1 remote through the internal service.
When I connect to the internal service, I see the same connection to PDB1’s GUID but from (PROGRAM=java) directly
<msg time='2017-04-07T18:03:06.671+02:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='VM104' host_addr='192.168.78.104' pid='1194'> <txt>07-APR-2017 18:03:06 * (CONNECT_DATA=(SERVICE_NAME=4aa269fa927779f0e053684ea8c0c27f)(CID=(PROGRAM=java)(HOST=VM104)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=27526)) * establish * 4aa269fa927779f0e053684ea8c0c27f * 0 </txt> </msg>
So each user PDB, in addition to the PDB name and additional services you have defined, registers an additional internal service, whether the PDB is opened our closed. And the fun is that Proxy PDB also register this additional service. Here is my listener status:
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=1521))) Services Summary... Service "4aa269fa927779f0e053684ea8c0c27f" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "4c96bda23b8e41fae053684ea8c0918b" has 1 instance(s). Instance "CDB2", status READY, has 1 handler(s) for this service... Service "CDB1" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "CDB1XDB" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "CDB2" has 1 instance(s). Instance "CDB2", status READY, has 1 handler(s) for this service... Service "CDB2XDB" has 1 instance(s). Instance "CDB2", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "pdb1px1" has 1 instance(s). Instance "CDB2", status READY, has 1 handler(s) for this service... The command completed successfully
This “4c96bda23b8e41fae053684ea8c0918b” is the GUID of the Proxy PDB.
SQL> select sys_context('userenv','cdb_name'), sys_context('userenv','con_name'), sys_context('userenv','service_name') from dual; SYS_CONTEXT('USERENV','CDB_NAME') -------------------------------------------------------------------------------- SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- SYS_CONTEXT('USERENV','SERVICE_NAME') -------------------------------------------------------------------------------- CDB1 PDB1 SYS$USERS
So that’s a fourth way to connect to PDB1: through the internal service of the Proxy PDB.
Then you can immediately imagine what I tried…
Because the internal service name is used to connect through Proxy PDB, can I create an proxy for the proxy?
18:03:32 SQL> create pluggable database PDB1PX2 as proxy from PDB1PX1@CDB2 2 file_name_convert=('/u01/oradata/CDB1/PDB1/PX1','/u01/oradata/CDB1/PDB1PX2'); Error starting at line : 76 File @ /media/sf_share/122/blogs/proxypdb.sql In command - create pluggable database PDB1PX2 as proxy from PDB1PX1@CDB2 file_name_convert=('/u01/oradata/CDB1/PDB1/PX1','/u01/oradata/CDB1/PDB1PX2') Error report - ORA-65280: The referenced pluggable database is a proxy pluggable database.
Answer is no. You cannot nest the Proxy PDB.
Don’t panic when looking at services registered in the listener. Those hexadecimal service names are expected in 12.2, with one per user PDB. You see them, but have no reason to use them directly. You will use them indirectly when creating a Proxy PDB which makes the location where users connect independent from the physical location of the PDB. Very interesting from migration because client configuration is independent from the migration (think hybrid-cloud). You can use this feature even without the multitenant option. Want to see all multitenant architecture options available without the option? Look at the ITOUG Tech day agenda
Good research job once again. Thanks for sharing.