CNDBA社区
Orcle 12c DG 新特性---Active Data Guard Support for DML on Global Temporary Tables
作者：
Expect-乐
1 说明
This feature expands the number of read-only applications that can be off-loaded from production databases to an Active Data Guard standby database. Even though an Active Data Guard standby database is open in read-only mode, reporting applications are now able to write to global temporary tables at the standby database without any modification.
Active Data Guard支持在全局临时表上进行DML操作，这个特性增加了可从生产数据库卸载到Active Data Guard备用数据库的只读应用程序的数量。即使备库是只读的，报表程序也可以写入全局临时表中。
默认情况，临时表的undo记录是存储到undo表空间中，并记录redo，和管理永久性表一样。然而限制可以设置TEMP_UNDO_ENABLED参数来将临时表的undo和永久性表的undo数据区分开。当这个参数设置为TRUE时，临时表的undo叫作临时undo。
启用临时undo有以下好处：
1. Temporary undo reduces the amount of undo stored in the undo tablespaces. --很显然会减少undo表空间大小
Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records.
2. Temporary undo reduces the size of the redo log. --同样也会减少redo log的大小
Performance is improved because less data is written to the redo log, and components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse.
3. Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.
--临时undo允许在备库上对临时表做DML操作。但是必须先再主库上创建临时表。
注意：启用临时undo，会占用临时表空间的大小，要将临时表空间设置到合适的大小。
可以会话级别和系统级别启用临时undo。
When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session. Therefore, if temporary undo is enabled for a session and the session uses temporary objects, then temporary undo cannot be disabled for the session. Similarly, if temporary undo is disabled for a session and the session uses temporary objects, then temporary undo cannot be enabled for the session.
如果一个会话启用临时undo，并且该会话使用了临时对象，那么临时undo是无法被禁用的。同样如果一个会话的临时undo被禁用了，并且该会话使用了临时对象，那么该会话的临时undo是无法被启用的。
Temporary undo is enabled by default for a physical standby database with the Oracle Active Data Guard option. The TEMP_UNDO_ENABLED initialization parameter has no effect on a physical standby database with Active Data Guard option because of the default setting. --这里物理备库不受参数值影响
2 实验
在之前版本中，备库是不允许进行任何DML操作的，哪怕是临时表也无法进行DML操作。都会提示：ORA-16000: database open for read-only access
2.1 主库创建临时表
SQL> conn lei/lei@sihong
Connected.
SQL> create global temporary table temp_cndba(id number,name varchar2(50));
Table created.
2.2 备库对临时表执行DML操作
注意：默认情况下，DG物理备库是启用临时UNDO的。而不受temp_undo_enabled参数影响。
SQL> show parameter temp_undo_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
SQL> insert into temp_cndba values(1,'www.cndba.cn');
1 row created.
SQL> update temp_cndba set name='a';
3 rows updated.可以看到，对临时表的DML操作时没有影响的。
2.3 备库验证生成REDO是否减少
SQL> set autotrace traceonly statistics
SQL> insert into temp_cndba select OBJECT_ID,object_name from dba_objects;
90943 rows created.
Statistics
----------------------------------------------------------
4648 recursive calls
2459 db block gets
10664 consistent gets
1364 physical reads
0 redo size
859 bytes sent via SQL*Net to client
876 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
368 sorts (memory)
0 sorts (disk)
90943 rows processedSQL> update temp_cndba set name='a';
90946 rows updated.
Statistics
----------------------------------------------------------
20 recursive calls
205494 db block gets
979 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
838 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
90946 rows processed2.4 主库验证REDO
注意：默认情况下temp_undo_enabled是FALSE，即没有启用临时UNDO。
SQL> show parameter temp_undo_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
SQL> set autotrace traceonly statistics
SQL> insert into temp_cndba select OBJECT_ID,object_name from dba_objects;
90943 rows created.
Statistics
----------------------------------------------------------
348 recursive calls
2468 db block gets
5452 consistent gets
3 physical reads
303276 redo size
858 bytes sent via SQL*Net to client
876 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
90943 rows processed2.4.1 启用临时UNDO
SQL> alter session set temp_undo_enabled=true;
Session altered.
SQL> show parameter temp_undo_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean TRUE--再次插入数据
SQL> set autotrace traceonly statistics
SQL> insert into temp_cndba select OBJECT_ID,object_name from dba_objects;
90943 rows created.
Statistics
----------------------------------------------------------
6 recursive calls
2473 db block gets
4461 consistent gets
0 physical reads
280 redo size --可以看到生成的reod大大减少
854 bytes sent via SQL*Net to client
876 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
90943 rows processed
SQL> update temp_cndba set name='a';
90943 rows updated.
Statistics
----------------------------------------------------------
24 recursive calls
203620 db block gets
978 consistent gets
0 physical reads
0 redo size
856 bytes sent via SQL*Net to client
838 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
90943 rows processed3 总结
1. DG环境下，物理备库默认是启用临时UNDO的，不受temp_undo_enabled参数值影响。
2. 其他环境下，默认临时UNDO是没有启用的
3. 临时UNDO支持会话级别和系统级别的修改，立即生效
4. 数据库版本要是12.0.0以及以上
5. 物理备库可以对临时表进行DML操作，首先要在主库创建临时表。
版权声明：本文为博主原创文章，未经博主允许不得转载。
Active Data Guard Support for DML on Global Temporary Tables
