Sunday, February 14, 2016

Oracle RAC: Creating service on pdb

standard application failover 
Using srvctl add service "srvnsp" on pluggable database "CBN" 

srvctl add service -d p1tncd -s srvnsp -pdb cbn -preferred p1tncd1,p1tncd2
srvctl modify service -d p1tncd -s srvnsp -failoverdelay 5
srvctl modify service -d p1tncd -s srvnsp -clbgoal SHORT
srvctl modify service -d p1tncd -s srvnsp -rlbgoal SERVICE_TIME

[oracle@tnc1 ~]$ srvctl config database -d p1tncd
Database unique name: p1tncd
Database name: p1tncd
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +SDXH/P1TNCD/PARAMETERFILE/spfile.271.903695731
Password file: +SDXH/P1TNCD/PASSWORD/pwdp1tncd.256.903695139
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: FRA,SDXH
Mount point paths: 
Services: srvnsp
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: p1tncd1,p1tncd2
Configured nodes: tnc1,tnc2
Database is administrator managed

start service after creating  & checking status of services 
[oracle@tnc1 admin]$ srvctl start service -db p1tncd -service srvnsp

[oracle@tnc1 admin]$ srvctl status service -db p1tncd -service srvnsp
Service srvnsp is running on instance(s) p1tncd1,p1tncd2

After creating service add following entry in tnsnames.ora

SRVNSP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tnc-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srvnsp)
    )
  )

check service status on CDB$ROOT database 
SQL> select name,pdb from dba_services;
NAME                          PDB
-------------------- --------------------
SYS$BACKGROUND      CDB$ROOT
SYS$USERS                 CDB$ROOT
p1tncdXDB                 CDB$ROOT
p1tncd                       CDB$ROOT

How to remove service using srvctl 
[oracle@tnc1 admin]$ srvctl remove service -d p1tncd -s srvnsp

--Nikhil Tatineni--
--12c: Oracle RAC--

Querys to monitor RAC

following few  Query's will help to find out culprits-  Query to check long running transaction from last 8 hours  Col Sid Fo...