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/[email protected]:1521/srv_pdbtest1.localdomain
Connected.
SQL>
SQL>
SQL>
SQL> show con_name
CON_NAME
------------------------------
PDBTEST
SQL>
SQL>
SQL> conn C##USERCON/[email protected]: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)))
