Configure the network environment for Multitenant Database

This topic describes how to configure the network environment on Multitenant Database.

Create a Service:

List of Services:

col NAME for A30
col PDB for A20
set linesize 100
SQL> SELECT service_id, name, pdb
FROM v$services ORDER BY service_id;  

SERVICE_ID NAME                           PDB
---------- ------------------------------ --------------------
         0 pdbnodata.localdomain          PDBNODATA
         0 salespdb.localdomain           SALESPDB
         1 SYS$BACKGROUND                 CDB$ROOT
         2 SYS$USERS                      CDB$ROOT
         5 orclXDB                        CDB$ROOT
         6 orcl.localdomain               CDB$ROOT
         8 pdbtest.localdomain            PDBTEST

7 rows selected.

Create a Service for PDB:

DBMS_SERVICE package will use for creating a new service. For GI env, you may use srvctl to create and manger a service. When execute DBMS_SERVICE.CREATE_SERVICE procedure, it will create the service on current container.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        READ WRITE NO
         7 PDBNODATA                      MOUNTED

SQL> alter session set container=PDBTEST;

Session altered.

SQL> BEGIN
  DBMS_SERVICE.CREATE_SERVICE('SRV_PDBTEST1','SRV_PDBTEST1');
END;
/
PL/SQL procedure successfully completed.

SQL> SELECT service_id, name, pdb
FROM v$services
ORDER BY service_id;  

SERVICE_ID NAME                           PDB
---------- ------------------------------ ------------------------------
         8 pdbtest.localdomain            PDBTEST

SQL> EXEC DBMS_SERVICE.START_SERVICE('SRV_PDBTEST1');

PL/SQL procedure successfully completed.

SQL> SELECT service_id, name, pdb
FROM v$services
ORDER BY service_id;  

SERVICE_ID NAME                           PDB
---------- ------------------------------ ------------------------------
         1 SRV_PDBTEST1                   PDBTEST
         8 pdbtest.localdomain            PDBTEST

SQL> conn / as sysdba
Connected.
SQL> SELECT service_id, name, pdb
FROM v$services
ORDER BY service_id;  

SERVICE_ID NAME                           PDB
---------- ------------------------------ ------------------------------
         0 salespdb.localdomain           SALESPDB
         0 pdbnodata.localdomain          PDBNODATA
         1 SYS$BACKGROUND                 CDB$ROOT
         1 SRV_PDBTEST1                   PDBTEST
         2 SYS$USERS                      CDB$ROOT
         5 orclXDB                        CDB$ROOT
         6 orcl.localdomain               CDB$ROOT
         8 pdbtest.localdomain            PDBTEST

8 rows selected.

Make sure that the Service has been included on listener. If not add automatically, then run Alter system register.

[oracle@ocm12c admin]$ lsnrctl status
....
Service "srv_pdbtest1.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Connect to database using service:

SQL> conn USER_PDB/oracle@ocm12c.localdomain:1521/srv_pdbtest1.localdomain
Connected.
SQL>
SQL>
SQL>
SQL> show con_name

CON_NAME
------------------------------
PDBTEST
SQL>
SQL>
SQL> conn C##USERCON/oracle@ocm12c.localdomain:1521/srv_pdbtest1.localdomain
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDBTEST
SQL> select * from tab;

TNAME
--------------------------------------------------------------------------------
TABTYPE  CLUSTERID
------- ----------
ROOT_TEST
TABLE

Add service to TNS file:

[oracle@ocm12c admin]$ tnsping SERTEST1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 01-SEP-2020 21:26:38

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocm12c.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SRV_PDBTEST1.localdomain)))
OK (0 msec)

[oracle@ocm12c admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 1 21:26:42 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn USER_PDB/oracle@SERTEST1
Connected.

Create a Listener:

Oracle Doc Link:

Database Administrator -> Database Net Services Reference -> 7 Oracle Net Listener Parameters in the listener.ora File

Database Administrator -> Database Net Services Administrator’s Guide -> 13 Enabling Advanced Features of Oracle Net Services (example 13-1)

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc) (queuesize=50))))
SID_LIST_listener=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/oracle8)
      (PROGRAM=extproc)))

Leave a Reply