Running a two node RAC 19c cluster.
Also, running a two node Data Guard 19c environment to provide resilience for the primary environment.
If you were to do a Data Guard switch you would need to manually change any connection strings to reflect the fact that that Data Guard environment is the "new" primary otherwise applications or SQL Developer / TOAD etc will be pointing to the "original" i.e now standby side.
However, there is a way to have your connections "float" between your Primary and Secondary environments.
Please note - SCAN listeners, RAC, DataGuard, TDE etc outside of this post.
++++++++++++++++++++
Example values - very simplistic setup for clarity :
ENV_lhr34g - database. ENV1 & ENV2 (RAC instances) - pdbs read write
ENV_lhr456 - database. ENV1 & ENV2 (Data Guard env) - pdbs read only (active dataguard)
TEST - pluggable database
TSTS - pluggable database service (example)
db-1 & db2 (two nodes on 'Primary' side)
db-3 & db-4 (two nodes on 'Secondary' i.e. 'DataGuard' side)
Remember after "switch over" these will swap.
env1-scan.example.com - SCAN listener name on primary
env2-scan.example.com - SCAN listener name on secondary (Data Guard side)
++++++++++++++++++++
Added the service (after a bit of trial and error with syntax and options) -
[oracle@db-1
~]$ srvctl add service -d ENV_lhr34g -s TSTS -pdb TEST -l primary -r
"ENV1,ENV2"
Started the service -
[oracle@db-1 ~]$ srvctl
start service -service TSTS -db ENV_lhr34g
Check
[oracle@db-1 ~]$ srvctl
status database -db ENV_lhr34g -v
Instance ENV1 is running on node db-1 with online services TSTS. Instance status: Open.
Instance ENV2 is running on node db-2 with online services TSTS. Instance status: Open.
Added a "TEST" entry to the tnsnames.ora on both primary nodes to test connection.
Amended a default entry in the tnsnames.ora - you might want to adjust this entry to suit your environment.
TEST =(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS_LIST=(FAILOVER = ON)(LOAD_BALANCE = OFF)(ADDRESS=(PROTOCOL=TCP)(HOST=env1-scan.example.com)(PORT=1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = env2-scan.example.com)(PORT = 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TSTS.example.com)))
Login with that entry
sqlplus user1/password@TEST
SQL> select host_name from gv$instance where instance_number=userenv('instance');
HOST_NAME
----------------------------------------------------------------
db-1Trying logging in and out a few times and SCAN listener will point you to either db-1 or db-2
Now amend the Data Guard
side
Add an entry on the DataGuard side but do not start the service - note the database name is the one on the DataGuard side.
[oracle@db-3 ~]$ srvctl add service -d ENV_lhr456 -s TSTS -pdb TEST -l primary -r "ENV1,ENV2"
DO NOT start the service
this will happen when you do a switchover !
(If you try and start the service prior to switching over it will fail with an error and the only way to start it, is to stop the service on the primary first and then start the service on the Data Guard side. However, that would mean you would connect to the read only pdb on the Data Guard side and not the primary database. One way to ensure you always connect to the primary is to change the ordering of the HOST= option in the service name in the tnsnames.ora file so that the connection is correct - something I have not had a chance to try but may update this at a later date. If you do want to connect to the read only pdb on the secondary suggest an alternative service name is created. For simplicity suggest the service is only started on the primary - let the dataguard switchover look after it).
Add a tnsnames entry as per the primary side on both Data Guard nodes.
As the grid user you might want to run "crsctl stat res -t" to check the status of the services.
e.g.
ora.env_lhr346.tsts.svc
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE STABLE
Will be "online online" if this is primary
To confirm your pdb connection is working simply do a Data Guard switchover (outside of the scope of this ) but for Oracle cloud simply a case of going into the Cloud console and enabling switchover.
Once the switchover is complete - again check the connection but this time, the sqlplus connection will report the "new" primary (try logging in and out a few times to confirm you see db-3 and db-4 the new servers you should be connecting to).
Also check "crsctl stat res -t" as above to confirm the service is now online.
sqlplus user1/password@TEST
SQL> select host_name from gv$instance where instance_number=userenv('instance');
HOST_NAME
----------------------------------------------------------------
db-3
SQL> show con_name;
CON_NAME
------------------------------
TEST As always, any improvements, amendments or correction please let me know and always test on a "sandbox" environment prior to any production changes.
