Introduction

 
You’ve probably been considering Physical standby database is only for SELECT queries and recovery purpose but not at all for DML operations. Now from 12c DML queries are supported but with limitations, In 12c physical standby database we can perform Insert queries over global temporary tables. To use this feature database should be in Open/Read-Only mode which is Active Data Guard. I am going to present how DML queries work on physical standby database.

Temporary Tables

 
Moving further, I don't want to explain what temporary table is, but I would like to provide real time example where it will be useful and what is the necessity of the temporary tables. Temporary tables requirement is purely on application demand, I have personal experience on temporary tables closely with PeopleSoft applications and of course other applications as well. For an example PeopleSoft - AE where the temporary tables are system's auxiliary tables and where the house keeping work will be carried out by the application engine, Same time we can assign a temp table to an application engine. When an application engine start with temporary table the application engine program assigns the available instance.
 
There are advantages and few disadvantages with temporary tables, Advantage with global temporary tables we can sort, join and filter them and after the end of session the data will be truncated or we can preserve rows so that truncate can be performed manually. The disadvantage of temporary tables is there is possibility of high I/O and lot of redo generation same as normal tables. So Global temporary tables being an advantage or disadvantage is totally up to the context in which it is being used

12c: TEMP_UNDO_ENABLED

 
Oracle 12c introduced a new parameter "TEMP_UNDO_ENABLED", the default value is "False" , by setting of this parameter the transaction can splits its undo for temporary objects into temporary undo log and for normal objects into permanent undo log, Prior to 12c temporary and normal table transactions uses same undo. Now with the new parameter setting of TEMP_UNDO_ENABLED to TRUE, It serves separate undo for temporary tables and for normal tables, Further temporary tables will not logged in undo unless temporary tables uses temporary tablespaces. This will help improve in performance and reduce UNDO and REDO.
 
TEMP_UNDO_ENABLED is a dynamic parameter either we can at set session level or database level, As soon as we set the parameter to TRUE then it allows to writes in Temporary tablespace but there will be no undo and redo generated for Global temporary tables. Ensure COMPATIBLE is set to 12.0.0 or higher to use this parameter. When it comes in RAC each instance can have different values that mean one instance can have False and other instance can have True.

TEMP_UNDO_ENABLED with Standby Database

 
So far we have discussed about the parameter TEMP_UNDO_ENABLED and how to use this parameter, but let's see how this parameters works with standby database? This is an interesting feature of 12c with TEMP_UNDO_ENABLED and Data Guard. As we all know standby database can be opened for only read only operations and no read wire operations such as (INSERT, UPDATE, DELETE...).
 
With the 12c, new feature TEMP_UNDO_ENABLED to TRUE, it enables DML on temporary tables when database is in read only mode/Active Data Guard. However still DDL commands should be issued on primary database. Let's suppose if we perform DML such as INSERT of few rows on Physical standby then there will be no redo generated because it's an temporary table however undo will be generated. Prior to 12c we cannot perform any DML operations from Physical standby databases, they remained as Read-Only.

Demo: How TEMP_UNDO_ENABLED practically works?

 
To test this feature firstly we need to create a temporary table and it can be with preserve rows or delete on session completion.
 
1. Created table and gather redo size on Primary database.
SQL> conn c##nassyam/manager

Connected.

SQL> create global temporary table ADG_GTT on commit preserve rows as select * from testobj;

Table created.

SQL>

 
 
From other session we can monitor redo usage of the session from the following query.
select v$session.sid, username, value redo_size
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = 'redo size'
and username='C##NASSYAM'
and value > 0
and username is not null
order by value
/
       SID USERNAME                        REDO_SIZE
---------- ------------------------------ ----------
        18 C##NASSYAM                          29216
2) Let's add few rows to the table with autotrace statistics to know the details of redo consumption.
 SQL> show user
USER is "C##NASSYAM"
SQL> set autotrace traceonly statistics;
SQL> insert into ADG_GTT select * from testobj;

91431 rows created.


Statistics
----------------------------------------------------------
         21  recursive calls
       8525  db block gets
       3056  consistent gets
       1580  physical reads
     603304  redo size
        860  bytes sent via SQL*Net to client
        841  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      91431  rows processed

SQL>

So the redo size after the creation of table is 29216 and for insert operation generated size is 603304.
 
Now the total redo size is below as per the query from Step 1 is 29216 +603304 = 632520
       SID USERNAME                        REDO_SIZE
---------- ------------------------------ ----------
        
        18 C##NASSYAM                         632520

SQL>


So far we haven't used TEMP_UNDO_ENABLED and if we are using this parameter then below view will be useful to track how much temp used.
SQL> select * from v$tempundostat;

no rows selected

SQL>

3. Check status on Physical standby.
 
SQL> select db_unique_name, database_role,open_mode from v$database;

DB_UNIQUE_NAME            DATABASE_ROLE    OPEN_MODE
------------------------- ---------------- --------------------
india                     PHYSICAL STANDBY READ ONLY WITH APPLY

SQL>

SQL> select count(*) from c##nassyam.adg_gtt;

  COUNT(*)
----------
         0

SQL>
 
4. Let's use 12c feature TEMP_UNDO_ENABLED on both sites.
SQL> alter system set temp_undo_enabled=true scope=both;

System altered.

SQL> show parameter temp_undo_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     TRUE
SQL>


SQL> set autotrace traceonly statistics;
SQL> insert into ADG_GTT select * from testobj;

91431 rows created.


Statistics
----------------------------------------------------------
         36  recursive calls
       8509  db block gets
       3089  consistent gets
       1534  physical reads
        280  redo size
        857  bytes sent via SQL*Net to client
        841  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      91431  rows processed

SQL>

From the Step 2, the same query processes 91431 rows and the generated redo was 603304 but after changing TEMP_UNDO_ENABLED to true then the redo size surprisingly dropped to 280? Yes that's what parameter does to redirects usage of redo to the temp. From the view v$sortusage we can gather temp usage details of sessions.
 
 
5. Let's do actual same test on Physical standby database.
SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
india                          PHYSICAL STANDBY READ ONLY

SQL>
SQL> conn c##nassyam/manager
Connected.
SQL> select count(*) from adg_gtt;

  COUNT(*)
----------
         0

SQL> insert into ADG_GTT select * from testobj;<------ DML on Standby

91431 rows created.

SQL> select count(*) from adg_gtt;                <------ New rows on Standby

  COUNT(*)
----------
     91431
    
SQL> set autotrace traceonly statistics;

SQL> insert into ADG_GTT select * from testobj;

91431 rows created.


Statistics
----------------------------------------------------------
        312  recursive calls
       8499  db block gets
       3542  consistent gets
       1534  physical reads
          0  redo size
        854  bytes sent via SQL*Net to client
        841  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         35  sorts (memory)
          0  sorts (disk)
      91431  rows processed

SQL>
SQL> select count(*) from adg_gtt;

  COUNT(*)
----------
    182862

 
So the INSERT/DML is now possible on global temporary tables even on Physical standby database.
 
Let's verify the temp usage on physical standby database.
 

How application works with Active Data Guard 12c with TEMP_UNDO_ENABLED

We will take example how application works with standby database, For OBIEE with 11g when configuring Active Data Guard Firstly we disable temporary tables just to ensure no DDL/DML commands are executed on standby database because physical standby won't support other than select queries then connection pools will be created to the standby database for the queries to be run and according to the context of the queries they will run on primary database with the help of connection pool.

 
But from 12c even though we create temporary tables from primary database still the DML's on the temporary tables are allowed to execute on physical standby. Hence Insert/DML on temporary tables can be offloaded to physical standby database.

Conclusion

 
Active Data Guard feature was limited only for the Read-only queries until 11g, but with the new feature of 12c parameter TEMP_UNDO_ENABLED write access is allowed on Physical standby database through temporary tables. By setting the parameter on primary database temporary undo reduces the redo which can lead improvement in primary database performance and also reduces network bandwidth consumption and more important less I/O on standby database.
 
Reference: https://docs.oracle.com/database/121/REFRN/refrn10326.htm#REFRN10326