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>



6 комментариев:

  1. стандартное поведение для базы в read only

    ОтветитьУдалить
  2. >>если Вам для отчетов нужны временные таблицы, Вам придется создать dblink на как какую-то другую БД, например на собсвенную primary
    Это не будет работать. select с использованием db link'a всегда явно начинает транзакцию, поэтому на standby отвалится с ORA-16000: database open for read-only access.
    Явное указание транзакции как read only уберет эту ошибку, но insert/delete/update будут падать уже с ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.
    Кроме этого, у Оракла серьезные проблемы с использованием GTT в глобальных транзакциях - официальный ответ техсуппорта был "мы их (GTT) в таком случае не поддерживаем". А проблемы какие-то очень нехорошие, летели 600-е с data corruption.

    ОтветитьУдалить
  3. Timur, спасибо. Если можете - напишите пожалуйста поподробнее.
    Insert через db link будет падать ?
    Любая попытка сделать GTT в транзакции с db link приводит к ORA-600 ?

    ОтветитьУдалить
  4. Падать должен :-) любой запрос, использующий дб линк на базе открытой в read only, без явного указания set transaction read only. Insert over db link будет падать если начать read only транзакцию явно.
    >>Любая попытка сделать GTT в транзакции с db link приводит к ORA-600 ?
    Нет, у нас возникали проблемы при использовании GTT в распределенной среде (9.2.0.8, спарк).
    PS. BTW, и subquery factoring clause в случае read only не поможет - материализации подзапроса не будет. В общем, read only надо трактовать дословно.

    ОтветитьУдалить
  5. Timur, видимо за тот небольшой промежуток времени между 9.2.0.8 и 11g произошли положительные изменения в Standby. У меня все работает, так как я описывал. Я обновил пост, читать после слов Update 1.

    ОтветитьУдалить
  6. Очень может быть. Похоже это появилось именно в 11g, т.к. в доке 10g такого параграфа нет, и пример не работает.
    PS. commit нужен только при обращении к 2 и более db link'ам. Видимо Оракл наконец-то привел в соответствие определение distributed transaction и реальный код.

    ОтветитьУдалить