Orcle 12c DG 新特性---ADG支持在全局临时表上执行DML操作

2024-01-21 11:00:22

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有以下好处：

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.

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.

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 processed
SQL> 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 processed

2.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 processed

2.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 processed

3 总结

DG环境下，物理备库默认是启用临时UNDO的，不受temp_undo_enabled参数值影响。
其他环境下，默认临时UNDO是没有启用的
临时UNDO支持会话级别和系统级别的修改，立即生效
数据库版本要是12.0.0以及以上
物理备库可以对临时表进行DML操作，首先要在主库创建临时表。

码农公寓