Connectstrings in the OCI
Under the category; asked and answered. When you have created a Data Guard config, the most obvious is that you eventually would like to connect to the Database. This is done using a connect string, often specified in tnsnames.ora or via jdbc-thin.
When you read this blog post, then you end up with a Primary and Standby database.
One of the questions I got last week “How can we add 2 service names in the same connect string”? I was a bit surprised. After a few questions, the main question was “What is the connect string we need to use in a OCI Data Guard configuration”?
One thing I always recommend is to read the getting started guides. This one is actually pretty good.
Well! That is actually a very good question. When we take this blog post, as the example, you see that we created the CLDGDEMO_PDB1 PDB in this database.
When we check the both db connection strings, we see that for AD1 the SERVICE_NAME=CLDGDEMO_fra1pv.p***.vcn***.oraclevcn.com) is used and for AD2 CLDGDEMO_fra28n.p***m.vcn***.oraclevcn.com
That immediately clarifies the first version of the question, right? The solution is rather simple.
So when you create a Database Service, you provide the PDB-name and that PDB name is also your SERVICE_NAME to connect to. If you need more services, then you need to add them manually using srvctl add service.
On the vm in AD1
|
1 2 3 4 |
[oracle@vmcldgdemovmad1 ~]$ lsnrctl service |grep Service |grep -i pdb1 Service "cldgdemo_pdb1.p***.vcn***.oraclevcn.com" has 1 instance(s). [oracle@vmcldgdemovmad1 ~]$ |
When we check the VM on AD2
|
1 2 |
[oracle@vmcldgdemovmad2 ~]$ lsnrctl service |grep Service |grep -i pdb1 [oracle@vmcldgdemovmad2 ~]$ |
After a role transition, you can just point and click this operation
When the operation is completed, we expect after the role reversal, primary in AD2 and standby in AD1, but we only want 1 service for the PDB and we want that in AD2 at this point, right?
So lets verify.
AD2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@vmcldgdemovmad2 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 28 16:42:12 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CLDGDEMO_PDB1 READ WRITE NO SQL> set pages 999 lines 200 SQL> select host_name,instance_name,database_role from v$database, v$instance; HOST_NAME INSTANCE_NAME DATABASE_ROLE ---------------------------------------------------------------- ---------------- ---------------- vmcldgdemovmad2 CLDGDEMO PRIMARY SQL> exit Disconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production Version 19.5.0.0.0 [oracle@vmcldgdemovmad2 ~]$ |
Then we go to AD1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@vmcldgdemovmad1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 28 16:42:12 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 CLDGDEMO_PDB1 MOUNTED SQL> set pages 999 lines 200 SQL> select host_name,instance_name,database_role from v$database, v$instance; HOST_NAME INSTANCE_NAME DATABASE_ROLE ---------------------------------------------------------------- ---------------- ---------------- vmcldgdemovmad1 CLDGDEMO PHYSICAL STANDBY SQL> exit Disconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production Version 19.5.0.0.0 [oracle@vmcldgdemovmad1 ~]$ |
That looks good and the most important is the service.
AD1 (standby now)
|
1 2 |
[oracle@vmcldgdemovmad1 ~]$ lsnrctl service |grep Service |grep -i pdb1 [oracle@vmcldgdemovmad1 ~]$ |
AD2 (now primary)
|
1 2 3 |
[oracle@vmcldgdemovmad2 ~]$ lsnrctl service |grep Service |grep -i pdb1 Service "cldgdemo_pdb1.p***.vcn***.oraclevcn.com" has 1 instance(s). [oracle@vmcldgdemovmad2 ~]$ |
So this leads to the answer on the question.
If I take my Demo environment as an example, your connectstring would be:
|
1 2 3 4 5 6 7 8 9 10 |
cldgdemo_pdb1 = (DESCRIPTION = (CONNECT_TIMEOUT= 120)(RETRY_COUNT=20)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3) (ADDRESS_LIST = (LOAD_BALANCE=off) (ADDRESS = (PROTOCOL = TCP)(HOST=hostname-ad1)(PORT=1521))) (ADDRESS_LIST = (LOAD_BALANCE=off) (ADDRESS = (PROTOCOL = TCP)(HOST=hostname-ad2)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME = cldgdemo_pdb1.p***.vcn***.oraclevcn.com)) |
As always, questions, remarks?
find me on twitter @vanpupi