In my previous post, I have demonstrated how a new service can be created for a Pluggable Database (PDB) in Oracle RAC.
Custom Services are configured as Resources. Thus, in that post, I have demonstrated the custom service "newservice" for the PDB "hemantpdb" being configured in the Cluster.
However, PDBs can also be configured and managed as resources. (see the reference in the Oracle Database Features documentation).
This is the current listing of resources in my 2-node 21c RAC Cluster :
[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.chad
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.net1.network
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.ons
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE node2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE node1 Started,STABLE
2 ONLINE ONLINE node2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.cdp1.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp2.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp3.cdp
1 ONLINE ONLINE node2 STABLE
ora.cvu
1 ONLINE ONLINE node1 STABLE
ora.db21crac.db
1 ONLINE ONLINE node2 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE node1 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.db21crac.newservice.svc
1 ONLINE ONLINE node1 STABLE
ora.node1.vip
1 ONLINE ONLINE node1 STABLE
ora.node2.vip
1 ONLINE ONLINE node2 STABLE
ora.qosmserver
1 ONLINE ONLINE node1 STABLE
ora.scan1.vip
1 ONLINE ONLINE node1 STABLE
ora.scan2.vip
1 ONLINE ONLINE node1 STABLE
ora.scan3.vip
1 ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$
Above, we can see
the database resource "ora.db21crac.db"
the pdb resource "ora.db21crac.hemantpdb.pdb"
the service resource "ora.db21crac.newservice.svc" (which I created in my previous post)
Now, I will add a new PDB "pdb2"
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:04:31 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> create pluggable database pdb2 admin user pdb2_admin identified by pdb2_admin;
Pluggable database created.
SQL>
SQL> col name format a30
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
HEMANTPDB READ WRITE
PDB2 MOUNTED
SQL>
[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.chad
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.net1.network
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.ons
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE node2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE node1 Started,STABLE
2 ONLINE ONLINE node2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.cdp1.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp2.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp3.cdp
1 ONLINE ONLINE node2 STABLE
ora.cvu
1 ONLINE ONLINE node1 STABLE
ora.db21crac.db
1 ONLINE ONLINE node2 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE node1 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.db21crac.newservice.svc
1 ONLINE ONLINE node1 STABLE
ora.node1.vip
1 ONLINE ONLINE node1 STABLE
ora.node2.vip
1 ONLINE ONLINE node2 STABLE
ora.qosmserver
1 ONLINE ONLINE node1 STABLE
ora.scan1.vip
1 ONLINE ONLINE node1 STABLE
ora.scan2.vip
1 ONLINE ONLINE node1 STABLE
ora.scan3.vip
1 ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$
The new PDB, "pdb2" has been created but not opened yet (it is still only in the MOUNT state). However, if I try to manually add it, I get an error message that CARDINALITY wasn't specified for the first PDB that had been manually created from the SQL command line but not registered via srvctl. However, I *can* add the new PDB without having to specify CARDINALITY and then verify the status in the Cluster :
[oracle@node1 ~]$ srvctl add pdb -db DB21CRAC -pdb PDB2 -cardinality ALL -startoption open
PRCZ-4036 : failed to create pluggable database PDB2 with '-cardinality' option because this option was not specified for the previously created pluggable databases for database DB21CRAC
[oracle@node1 ~]$ srvctl config pdb -db DB21CRAC
Pluggable database name: HEMANTPDB
Application Root PDB:
Cardinality: %CRS_SERVER_POOL_SIZE%
Maximum CPU count (whole CPUs): 0
Minimum CPU count unit (1/100 CPU count): 0
Management policy: MANUAL
Rank value: 0
Start Option: open
Stop Option: immediate
[oracle@node1 ~]$ srvctl add pdb -db DB21CRAC -pdb PDB2 -startoption open
[oracle@node1 ~]$ srvctl config pdb -db DB21CRAC
Pluggable database name: HEMANTPDB
Application Root PDB:
Cardinality: %CRS_SERVER_POOL_SIZE%
Maximum CPU count (whole CPUs): 0
Minimum CPU count unit (1/100 CPU count): 0
Management policy: MANUAL
Rank value: 0
Start Option: open
Stop Option: immediate
Pluggable database name: PDB2
Application Root PDB:
Cardinality: %CRS_SERVER_POOL_SIZE%
Maximum CPU count (whole CPUs): 0
Minimum CPU count unit (1/100 CPU count): 0
Management policy: AUTOMATIC
Rank value: 0
Start Option: open
Stop Option: immediate
[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.chad
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.net1.network
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.ons
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE node2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE node1 Started,STABLE
2 ONLINE ONLINE node2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.cdp1.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp2.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp3.cdp
1 ONLINE ONLINE node2 STABLE
ora.cvu
1 ONLINE ONLINE node1 STABLE
ora.db21crac.db
1 ONLINE ONLINE node2 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE node1 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.db21crac.newservice.svc
1 ONLINE ONLINE node1 STABLE
ora.db21crac.pdb2.pdb
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE STABLE
ora.node1.vip
1 ONLINE ONLINE node1 STABLE
ora.node2.vip
1 ONLINE ONLINE node2 STABLE
ora.qosmserver
1 ONLINE ONLINE node1 STABLE
ora.scan1.vip
1 ONLINE ONLINE node1 STABLE
ora.scan2.vip
1 ONLINE ONLINE node1 STABLE
ora.scan3.vip
1 ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$
If I then OPEN the new PDB on both instances, I can query the status again :
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:19:20 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL>
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:19:36 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL>
[grid@node1 ~]$ crsctl status resource ora.db21crac.pdb2.pdb
NAME=ora.db21crac.pdb2.pdb
TYPE=ora.pdb.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on node1, ONLINE on node2
[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.chad
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.net1.network
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.ons
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE node2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE node1 Started,STABLE
2 ONLINE ONLINE node2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.cdp1.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp2.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp3.cdp
1 ONLINE ONLINE node2 STABLE
ora.cvu
1 ONLINE ONLINE node1 STABLE
ora.db21crac.db
1 ONLINE ONLINE node2 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE node1 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.db21crac.newservice.svc
1 ONLINE ONLINE node1 STABLE
ora.db21crac.pdb2.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.node1.vip
1 ONLINE ONLINE node1 STABLE
ora.node2.vip
1 ONLINE ONLINE node2 STABLE
ora.qosmserver
1 ONLINE ONLINE node1 STABLE
ora.scan1.vip
1 ONLINE ONLINE node1 STABLE
ora.scan2.vip
1 ONLINE ONLINE node1 STABLE
ora.scan3.vip
1 ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$
I can now add a custom service "pdb2service" for pdb2 to use DB21CRAC2 (instance 2, instead of instance 1) as preferred and with SESSION failover (and compare it with "newservice" for hemantpdb that I had added in the previous blogpost) :
[oracle@node1 ~]$ srvctl add service -db DB21CRAC -service pdb2service -preferred DB21CRAC2 -available DB21CRAC1 \
> -tafpolicy BASIC -failovertype SESSION -pdb PDB2
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service pdb2service
Service pdb2service is not running.
[oracle@node1 ~]$ srvctl config service -db DB21CRAC -service pdb2service
Service name: pdb2service
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: SESSION
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: PDB2
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: DB21CRAC2
Available instances: DB21CRAC1
CSS critical: no
[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
Service uses Java: false
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl start service -db DB21CRAC -service pdb2service
[oracle@node1 ~]$ srvctl config service -db DB21CRAC -service pdb2service
Service name: pdb2service
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: SESSION
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: PDB2
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: DB21CRAC2
Available instances: DB21CRAC1
CSS critical: no
Service uses Java: false
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service pdb2service
Service pdb2service is running on instance(s) DB21CRAC2
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC1
[oracle@node1 ~]$
[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.chad
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.net1.network
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
ora.ons
ONLINE ONLINE node1 STABLE
ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE node1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE node2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE node1 Started,STABLE
2 ONLINE ONLINE node2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.cdp1.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp2.cdp
1 ONLINE ONLINE node1 STABLE
ora.cdp3.cdp
1 ONLINE ONLINE node2 STABLE
ora.cvu
1 ONLINE ONLINE node1 STABLE
ora.db21crac.db
1 ONLINE ONLINE node2 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE node1 Open,HOME=/u01/app/o
racle/product/21.3.0
.0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.db21crac.newservice.svc
1 ONLINE ONLINE node1 STABLE
ora.db21crac.pdb2.pdb
1 ONLINE ONLINE node1 STABLE
2 ONLINE ONLINE node2 STABLE
ora.db21crac.pdb2service.svc
1 ONLINE ONLINE node2 STABLE
ora.node1.vip
1 ONLINE ONLINE node1 STABLE
ora.node2.vip
1 ONLINE ONLINE node2 STABLE
ora.qosmserver
1 ONLINE ONLINE node1 STABLE
ora.scan1.vip
1 ONLINE ONLINE node1 STABLE
ora.scan2.vip
1 ONLINE ONLINE node1 STABLE
ora.scan3.vip
1 ONLINE ONLINE node2 STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$
So, I have two PDBs "hemantpdb" and "pdb2" and their corresponding services ("newservice" and "pdb2service") using alternate database instances as Preferred Instances. "hemantpdb"'s service "newservices" runs on node1 (database instance DB21CRAC1) and "pdb2"'s service "pdb2service" runs on node2 (database instance DB21CRAC2) while both PDBs are actually OPEN on both nodes.
No comments:
Post a Comment