Services -- 4 : Using the SERVICE_NAMES parameter (non-RAC, PDB)

In my previous blog post, I have demonstrated using DBMS_SERVICE to create and start services.

But there is another way.  Using the SERVICE_NAMES parameter.  Going by most google references, SERVICE_NAME seems to be more popular than DBMS_SERVICE.

Can it be used in a Pluggable Database ?  Let' try.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 22:39:59 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size      2925024 bytes
Variable Size    973082144 bytes
Database Buffers   654311424 bytes
Redo Buffers     13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> 

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:43:36

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=14129))
Service "pdb1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$ 


SQL> connect system/oracle@PDB1 
Connected.
SQL> show con_id

CON_ID
------------------------------
3
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> alter system set service_names='NEW_SVC_1,NEW_SVC_2';
alter system set service_names='NEW_SVC_1,NEW_SVC_2'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> !oerr ora 65040
65040, 00000, "operation not allowed from within a pluggable database"
// *Cause:  An operation was attempted that can only be performed in the root
//          container.
// *Action: Switch to the root container to perform the operation.
//

SQL> 

So, apparently, ORA-65040 means that we can't define SERVICE_NAMES for a PDB.

SQL> connect sys/oracle as sysdba
Connected.
SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set service_names='NEW_SVC_1,NEW_SVC_2';

System altered.

SQL> 

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:53:56

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=14129))
Service "NEW_SVC_1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "NEW_SVC_2" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$ 

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ tnsping NEW_SVC_1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:54:57

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_SVC_1)))
OK (30 msec)
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@new_svc_1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 22:55:04 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: system/oracle@new_svc_1
Last Successful login time: Mon Jun 27 2016 22:44:13 +08:00

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

SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 

The new Services are actually running in the ROOT, not in the PDB .  Unlike my earlier blog post where I used the new services to connect to the PDB.

Therefore, for a PDB environment, I'd use DBMS_SERVICE as I've demonstrated here and here.
.
.
.