13 February, 2024

Adding a PDB (and Service) to a RAC database -- 1 - service running on only 1 Instance

On my existing, RAC database I have :

[oracle@node1 ~]$ srvctl status database -db DB21CRAC
Instance DB21CRAC1 is running on node node1
Instance DB21CRAC2 is running on node node2
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl status service -d DB21CRAC -s hemantpdb
PRKO-2017 : Service hemantpdb does not exist for database DB21CRAC.
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service hemantpdb -pdb HEMANTPDB
PRKO-2017 : Service hemantpdb does not exist for database DB21CRAC.
[oracle@node1 ~]$
[grid@node1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 13-FEB-2024 15:06:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                13-FEB-2024 14:51:13
Uptime                    0 days 0 hr. 14 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/21.3.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/node1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.115)(PORT=1521)))
Services Summary...
Service "0f488ad896262f80e0636f38a8c0fc18" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
Service "DB21CRAC" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
Service "DB21CRACXDB" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
Service "hemantpdb" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@node1 ~]$
[oracle@node1 ~]$SQL> select inst_id, name, network_name from gv$services order by 1;

   INST_ID NAME             NETWORK_NAME
---------- ---------------- ----------------
         1 hemantpdb        hemantpdb
         1 DB21CRAC         DB21CRAC
         1 SYS$BACKGROUND
         1 DB21CRACXDB      DB21CRACXDB
         1 SYS$USERS
         2 hemantpdb        hemantpdb
         2 DB21CRAC         DB21CRAC
         2 SYS$BACKGROUND
         2 DB21CRACXDB      DB21CRACXDB
         2 SYS$USERS

10 rows selected.

SQL>
SQL> select inst_id, con_id, name, open_mode from gv$pdbs order by 1,2;

   INST_ID     CON_ID NAME             OPEN_MODE
---------- ---------- ---------------- ----------
         1          2 PDB$SEED         READ ONLY
         1          3 HEMANTPDB        READ WRITE
         2          2 PDB$SEED         READ ONLY
         2          3 HEMANTPDB        READ WRITE

SQL>


Which means that I have created a custom PDB called "HEMANTPDB" and there is a default service called "hemantpdb" on each instance.  However, this services is NOT listed when I check via srvctl. 

This is because srvctl queries the cluster for information about services.  Service"hemantpdb" is created by default when I run CREATE PLUGGABLE DATABASE HEMANTPDB.


So, I can add a new service and configure Transparent Application Failover for SELECT failover but with only the first instance(DB21CRAC1) as the only one to start the service on initially{and the second instance (DB21CRAC2) as the alternate} :

[oracle@node1 ~]$ srvctl add service -db DB21CRAC  -service newservice -preferred DB21CRAC1 -available DB21CRAC2 -tafpolicy BASIC -failovertype SELECT -pdb HEMANTPDB
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl config service -db DB21CRAC  -service newservice
Service name: newservice
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Reset State: NONE
Failover type: SELECT
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name: HEMANTPDB
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Failback :  no
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: DB21CRAC1
Available instances: DB21CRAC2
CSS critical: no
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl start service -db DB21CRAC -service newservice
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC1
[oracle@node1 ~]$




If the database instance DB21CRAC1 fails (crashes), the ClusterWare starts the service on DB21CRAC2.  Here I kill the DB21CRAC process and then verify that the service has restarted on DB1CRAC2  :

[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC1
[oracle@node1 ~]$ ps -ef |grep smon
oracle    2951     1  0 16:05 ?        00:00:00 ora_smon_DB21CRAC1
root      3521     1  1 14:50 ?        00:00:48 /u01/app/21.3.0.0/grid/bin/osysmond.bin
grid      4068     1  0 14:50 ?        00:00:00 asm_smon_+ASM1
oracle    4146 25526  0 16:05 pts/0    00:00:00 grep --color=auto smon
[oracle@node1 ~]$ kill -9 2951
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC2
[oracle@node1 ~]$


In this case, connections using this service name ("newservice") will connect (failover) to the 2nd database instance running on node2 of the Cluster.

10 February, 2024

21 January, 2024

Video demonstration of RAC Client Query Session and Query Failover

 I've put up a short video that shows a client session connected to a RAC SCAN IP address failing over to the surviving RAC instance when the database node goes down


Video demonstration of RAC Client Query Session and Query Failover

When a client connects to a RAC Instance via a SCAN listener : If the node (Linux in this case) goes down abnormally, Grid Infrastructure does a failover of the SCAN IP address to any of the surviving nodes. 

 In this demo, I have two connections to two different SCAN IPs, one on node1 and the other on node2 (i.e. two different Linux DB Servers running RAC). When node2 goes down abruptly, the user's session (using SQL*Developer in this case) will automatically reconnect when the SCAN IP is migrated to the surviving node (node1) and can re-query. 

 By default, Transactions do not failover (as I show from the alert log, the node1 instance does a recovery and rollback of failed transactions of node2). However, you CAN build Application Continuity into your architecture to failover and resume transactions as well !!