Oracle 19c Updates on Active Data Guard using DML Redirection

Previously, you could only update Global Temp Tables on Active Data Guard standby database. From Oracle 19c, you can also update regular tables.

  • Updates on Active Data Guard:
    • Update will be redirected to the primary
    • Primary makes update, generates & sends redo for that update to all standbys
    • Active Data Guard session sees the update in redo apply and resumes
  • Preserves ACID properties for ADG session
    • Redirected update only visible to session before commit; visible to all sessions after commit
  • For “Mostly Read, Occasional Updates” applications – for example, recording user logins for auditing purposes
  • Enabled by ADG_REDIRECT_DML at system or session level
The following setup is based on Active Data guard configuration is MaxAvailability, but the behavior is the same when configuring MaxPerformance

A step by step guide to demonstrate this feature:

Current configuration

Basic setup 

The setup is primary database sends its redo logfiles through a far sync instance to a remote standby database.
DGMGRL> show configuration

Configuration - PRODCONF

  Protection Mode: MaxAvailability
  Members:
  orcl - Primary database
    fdb  - Far sync instance
      sdb  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

Redo Route configuration

Primary

The redo log files are transferred to the far sync instance, if the latter will not be available, the transport will be done to the standby as second priority.
DGMGRL> show database orcl RedoRoutes
  RedoRoutes = '(LOCAL : ( FDB PRIORITY=1, SDB PRIORITY=2 ) )'

Far Sync

Far sync receives the redo logfiles and transfers them to the standby asynchronously
DGMGRL> show far_sync fdb RedoRoutes
  RedoRoutes = '( ORCL : SDB ASYNC)'

LogXptMode configuration

Far sync 

Far sync instance receives the redo logfiles synchronously
DGMGRL> show far_sync fdb LogXptMode
  LogXptMode = 'SYNC'

Standby

Standby database receives the redo logfiles asynchronously
DGMGRL> show database sdb LogXptMode
  LogXptMode = 'ASYNC'
The standby is in sync and there is no lag
DGMGRL> show database sdb

Database - sdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    orcl

Database Status:
SUCCESS

Prepare the primary database

Connect as sys

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:15:51 2018
Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.1.0.0.0

Enable the feature ADG_REDIRECT_DML

SYS@orcl:SQL> alter system set ADG_REDIRECT_DML=TRUE scope=both;

System altered.

Connect to pluggable database

SYS@orcl:SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED   READ ONLY  NO
3 PDB   READ WRITE NO
SYS@orcl:SQL> alter session set container=pdb;

Session altered.

Create a test user

SYS@orcl:SQL> create user test_user identified by test_user;

User created.
SYS@orcl:SQL> grant connect, create session, create table to test_user;
Grant succeeded.
SYS@orcl:SQL> alter user test_user quota unlimited on users;

User altered.

Connect to the test user

SYS@orcl:SQL> connect test_user/test_user@pdb_svc
Connected.
Create sample tables
TEST_USER@primary_host/pdb_svc:SQL> create table regular_table(col1 number);

Table created.
TEST_USER@primary_host/pdb_svc:SQL> create global temporary table global_tt1(col1 number);

Table created.

Connect to the standby database

Connect as sys

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:15:51 2018
Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.1.0.0.0

Enable the feature ADG_REDIRECT_DML

SYS@sdb:SQL> alter system set ADG_REDIRECT_DML=TRUE scope=both;

System altered.

Connect to the test user

$ sqlplus test_user/test_user@standby_host:1521/pdb_svc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:24:28 2018
Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 27 2018 13:20:05 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.1.0.0.0

Do the magic 😊

TEST_USER@standby_host:1521/pdb_svc:SQL> set timing on

Check that inserting to a regular table is working on the standby database

TEST_USER@standby_host:1521/pdb_svc:SQL> insert into regular_table(col1) values(1);

1 row created.

Elapsed: 00:00:01.23
Look above at the elapsed time of the insert – this is the time in my environment that the standby asked from the primary to perform the insert and then the updated blocks are transferred from the primary to the standby.
TEST_USER@standby_host:1521/pdb_svc:SQL> select * from regular_table;

      COL1
----------
1

Elapsed: 00:00:00.03
TEST_USER@standby_host:1521/pdb_svc:SQL> commit;

Commit complete.

Elapsed: 00:00:01.06
Look above at the elapsed time of the commit

Check that inserting to a global temporary table is working on the standby database

TEST_USER@standby_host:1521/pdb_svc:SQL>  insert into global_tt1(col1) values(1);

1 row created.

Elapsed: 00:00:00.02
Look above at the elapsed time of the insert – much faster then working with a normal table.
TEST_USER@standby_host:1521/pdb_svc:SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
Look above at the elapsed time of the commit – look like it didn’t take time at all
TEST_USER@standby_host:1521/pdb_svc:SQL> select * from global_tt1;

no rows selected

Elapsed: 00:00:00.00
Check that we can create global temporary table on the standby database

TEST_USER@standby_host:1521/pdb_svc:SQL> create global temporary table global_tt2(col1 number);

Table created.

Elapsed: 00:00:01.07
TEST_USER@standby_host:1521/pdb_svc:SQL>  insert into global_tt2(col1) values(1);

1 row created.

Elapsed: 00:00:00.01

Check if primary database is updated

TEST_USER@primary_host/pdb_svc:SQL> select * from regular_table;

      COL1
----------
1
TEST_USER@primary_host/pdb_svc:SQL> desc global_tt2
 Name Null?   Type
 ----------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------
 COL1   NUMBER
Yossi

Comments

Post a Comment

Popular posts from this blog

Data Guard - Changing IP Addresses

Image
When changing IP of a host we should update/recheck the following places: /etc/hosts or DNS listener.ora tnsnames.ora Database parameters (local_listener, remote_listener) Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts. In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration Show the environment DGMGRL> show configuration Configuration - dr   Protection Mode: MaxAvailability   Members:   pdb7 - Primary database     fdb7 - Far sync instance       sdb7 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS   (status updated 351 seconds ago) Show detailed information of the Primary
Read more

Install Oracle Internet Directory (OID) in Standalone mode

Image
Oracle Internet Directory (OID) is an LDAP server which uses an Oracle database as a datastore. Client machines can use the OID for all TNS lookups. With OID. Once all client machines are configured correctly any modifications to the TNS lookups can be done from a central location reducing the amount of client machine administration. This guide is about installing and using Oracle Internet Directory in Standalone mode, no need for Oracle Fusion Middleware Infrastructure. Step-by-step guide Download Oracle Internet Directory (Part of identity management) from http://www.oracle.com/technetwork/middleware/id-mgmt/downloads/index.html Install OID, follow instructions: https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/installing-product-software.html#GUID-5D47117E-3983-4BED-BADB-CB7DDD63DB34 Starting the Installation Program: ./fmw_12.2.1.3.0_oid_linux64.bin Configure the Domain: $ORACLE_HOME/oracle_common/common/bin/config.sh Start the Servers for Standal
Read more

Fixing & Registering ORACLE_HOMES in Central Inventory

Central Inventory location is determined by oraInst.loc located in: HP/Solaris /var/opt/oracle/oraInst.loc Linux /etc/oraInst.loc Windows \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc To check if all oracle homes are registered within the Central Inventory run the following command: ${ORACLE_HOME}/oui/bin/opatch lsinventory -all Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /software/oracle/OEM10gR2/agent10g Central Inventory : /software/oracle/oraInventory from : /var/opt/oracle/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /software/oracle/OEM10gR2/agent10g/oui Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt ----------------------------------... List of Oracle Homes:
Read more