12cR1 RAC Posts -- 9 : Adding a Service to a PDB in RAC

My 2node RAC environment has 1 PDB.  Here I add (create) a new Service to the PDB.

Oracle recommends using srvctl instead of DBMS_SERVICE to add a Service.

Can I add a service without having the PDB OPEN ?

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:16:01 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB                            MOUNTED

SQL> alter pluggable database pdb open;  -- command issued in both instances

Pluggable database altered.

SQL>
SQL> select con_id, pdb, name, creation_date, clb_goal
  2  from v$services
  3  where pdb='PDB'
  4  /

    CON_ID PDB
---------- ------------------------------
NAME                                                             CREATION_ CLB_G
---------------------------------------------------------------- --------- -----
         3 PDB
pdb.racattack                                                    29-DEC-16 LONG


SQL>

I add the Service to the TNSNAMES.ORA and try to connect to it.

MYSVC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYSVC.racattack)
    )
  )

[oracle@collabn1 ~]$ tnsping MYSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:22:43

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYSVC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:22:51 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

So, apparently, the service isn't running.   Shall I try re-adding the service ?

[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
PRKO-3117 : Service MYSVC already exists in database RAC
[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC -pdb PDB
PRKO-2002 : Invalid command line option: -pdb
[oracle@collabn1 ~]$  srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:31:15 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 

Yet, it doesn't work !   How do I add and start service to a PDB ?  What's missing ?

[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl start service -db RAC -service MYSVC
[oracle@collabn1 ~]$

SQL> select distinct name from v$services;

NAME
----------------------------------------------------------------
pdb.racattack
RAC.racattack
MYSVC
SYS$BACKGROUND
RAC_DGB
RACXDB
SYS$USERS


[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:43:01 2017

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

Last Successful login time: Sun Apr 16 2017 23:30:21 +08:00

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

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>

I had forgotten to *START* the service !   Let me go back and add another service with the PDB CLOSEd.

SQL> alter pluggable database pdb close immediate;  -- on both instances

Pluggable database altered.

SQL>

[oracle@collabn1 ~]$ srvctl add service -db RAC -pdb PDB -service NEWSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT
[oracle@collabn1 ~]$

SQL> alter pluggable database pdb open;  -- on both instances

Pluggable database altered.

SQL>

NEWSVC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NEWSVC.racattack)
    )
  )

[oracle@collabn1 ~]$ srvctl start service -db RAC -service NEWSVC
[oracle@collabn1 ~]$ tnsping NEWSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:54:38

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NEWSVC.racattack)))
OK (0 msec)


[oracle@collabn1 ~]$ sqlplus hemant/hemant@NEWSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:55:25 2017

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

Last Successful login time: Sat Apr 29 2017 23:54:51 +08:00

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

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>

However, these entries in PDB_PLUG_IN_VIOLATIONS seem to be a bug :

SQL> alter pluggable database pdb close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> select * from pdb_plug_in_violations;

TIME
---------------------------------------------------------------------------
NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
ERROR_NUMBER       LINE
------------ ----------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
ACTION
--------------------------------------------------------------------------------
29-APR-17 11.58.32.409572 PM
PDB
Service Name Conflict                                            WARNING
           0          1
Service name or network name of service MYSVC in the PDB is invalid or conflicts
 with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.

29-APR-17 11.58.32.412142 PM
PDB
Service Name Conflict                                            WARNING
           0          2
Service name or network name of service NEWSVC in the PDB is invalid or conflict
s with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.


SQL>

So, remember, it is not sufficient to ADD a Service. You must also START the Service using srvctl.
.
.
.