Active Data Guard and TEMP tablespace

Вадим Гусев (Vadim.Gousev) задался целью выяснить, где происходят сортировки, когда мы переводим наш 11g Active Standby в состояние Read Only.

Как Вы конечно знаете, переведя БД в состояние Read Only мы можем запускать на ней отчеты, при этом продолжается применение redo. Таким образом наши отчеты видят актуальные данные (в зависимости от настроек с запозданием от нескольких секунд до 1 red log).

Во первых Вадим обнаружил, что TEMP datafile находится в состоянии READ WRITE

 SQL> select name, enabled from  v$tempfile
NAME                                    ENABLED
----------------------------------       ----------
/u01/app/oracle/oradata/stby/temp01.dbf  READ  WRITE

Далее, запустив достаточно большой запрос под пользователем sys, требующий сортировки он увидел, что

SQL> SELECT  s.username, u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s,  v$sort_usage u
WHERE s.saddr=u.session_addr;
USERNAME           TABLESPACE              CONTENTS     EXTENTS     BLOCKS
------------------- -----------------------  -------     ---------  ----------
SYS                 TEMP                    TEMPORARARY     6        768

И наконец, включив трассировку он уидел событие ожидания direct path wite temp

grep -i direct  /u01/app/oracle/diag/rdbms/stby/stby/trace/stby_ora_5896_10046.trc
WAIT #1:  nam='direct path write temp' ela= 49 file number=201 first dba=434953 block  cnt=15 obj#=18 tim=1229005511408393
WAIT #1: nam='direct path write temp'  ela= 4 file number=201 first dba=434968 block cnt=15 obj#=18  tim=1229005511425026
WAIT #1: nam='direct path write temp' ela= 6 file  number=201 first dba=434998 block cnt=15 obj#=18 tim=1229005511437579
WAIT  #1: nam='direct path write temp' ela= 3 file number=201 first dba=435013 block  cnt=15 obj#=18 tim=1229005511452812

Сомнений, нет TEMP используется на WRITE, когда наша Standby открыта на Read Only.

Если Вы экспериментировали с active duplicate for Standby, Вы конечно обратили внимание, что TEMP не передается, а создается на стороне Standby. И наконец, если Вам для отчетов нужны временные таблицы, Вам придется создать dblink на как какую-то другую БД, например на собсвенную primary. Создать конечно же еще на primary, чтобы он переехал на Standby.

UPDATE 1.

По мотивам комментариев
Limitations of a Read-only Database
"When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link. This is true even if you execute a generic SELECT statement on the first database link and the transaction is currently read-only."

Давайте создадим global temporary table на primary

SQL> conn scott/tiger@orcl
Connected.
SQL> create global temporary table  test_gtt(id number(4) primary key) on commit preserve rows; 
Table created.

Выполним аналитический запрос на standby и сохраним его на primary в GTT.

SQL> conn scott/tiger@stby
Connected.
SQL>  insert into test_gtt@db_orcl select empno  from emp;
 
 14 rows created.
SQL> commit;
 Commit complete.

Затем запустим наш отчет, показывающий результат процедуры на предыдущем шаге.

SQL> select * from  test_gtt@db_orcl;
         ID
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876 
      7900
      7902
      7934

14 rows selected.  
SQL>