Oracle "Floating" Services

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-1

Trying 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.

 

ORA-02266: Unique/Primary Keys In Table Referenced While Truncating Table

 

Trying to do a data only import into a table and hitting the error below :

ORA-39120: Table "XYZ"."LOOKUPS" can't be truncated, data will be skipped. Failing error is:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

The primary key in the table( which we are truncating) , seems to be referring to a another child table with foreign key.

Run below script to get the parent table , child table relation

set lines 200

COL CHILD_TABLE FOR a50

col CONSTRAINT_NAME for a50

col owner form a40

col FK_column form a40

col table_name form a50

select b.owner, b.table_name child_table,  c.column_name FK_column, b.constraint_name

    from dba_constraints a, dba_constraints b, dba_cons_columns c

    where a.owner=b.r_owner

    and b.owner=c.owner

    and b.table_name=c.table_name

    and b.constraint_name=c.constraint_name

    and a.constraint_name=b.r_constraint_name

    and b.constraint_type='R'

  and a.owner='&owner'

   and a.table_name='&table_name'

  and a.CONSTRAINT_TYPE='P'

Enter the values and then do a manual - alter table disable constraint.

For tables with multiple constraints this is easier i.e.

  

 select 'alter table ' || owner || '.' || table_name || ' disable constraint ' || constraint_name ||';' stmt from all_constraints

  where r_constraint_name in (select constraint_name from all_constraints where table_name = 'LOOKUPS')

Run the output - remember to enable the constraints when complete - the db I was doing this was restricted so no issue of any others "hitting" the tables.

Import should now work.