oracle.notes: августа 2013

2013-08-22

Flashback

Flashback version query (as of)

Работает через UNDO, не требует включения flashback в базе

SELECT * FROM par;
 
        ID TXT
---------- ----------
         1 doc 1
         2 doc 2
         3 today
SELECT * FROM par AS OF TIMESTAMP to_date('21.08.2013 22:55:00', 'DD.MM.YYYY HH24:MI:SS');
 
        ID TXT
---------- ----------
         1 doc 1
         2 doc 2

Вариант с датой дает 3 секундную погрешность. Есть вариант работы через scn

-- Такой функцией можно узнать scn по времени с погрешностью
SELECT timestamp_to_scn( to_date('21.08.2013 22:55:00', 'DD.MM.YYYY HH24:MI:SS')) FROM dual;
 
TIMESTAMP_TO_SCN(TO_DATE('21.0
------------------------------
                       4192406

SELECT * FROM par AS OF SCN 4192406;
 
        ID TXT
---------- ----------
         1 doc 1
         2 doc 2

Flashback version query (versions between) Пишет историю жизни строк в течении UNDO_RETENTION. Если нижняя граница раньше, sysdate - UNDO_RETENTION, то будет ошибка ORA-30052


INSERT INTO par VALUES (4, 'doc 4');
COMMIT;
UPDATE par p SET p.txt = 'new value' WHERE ID = 4;
COMMIT;

SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP SYSDATE - 1/24/60 * 15 AND SYSDATE;
 
VERSIONS_STARTSCN VERSIONS_STARTTIME                                VERSIONS_ENDSCN VERSIONS_ENDTIME                                  VERSIONS_XID     VERSIONS_OPERATION         ID TXT
----------------- ------------------------------------------------- --------------- ------------------------------------------------- ---------------- ------------------ ---------- ----------
          4234414 22.08.13 13:37:30                                                                                                   03001B001E0F0000 U                           4 new value
          4234333 22.08.13 13:34:51                                         4234414 22.08.13 13:37:30                                 0A002000A20B0000 I                           4 doc 4
                                                                                                                                                                                   1 doc 1
                                                                                                                                                                                   2 doc 2
                                                                                                                                                                                   3 today
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP SYSDATE - 1/24 AND SYSDATE
 
ORA-30052: недопустимое выражение для нижней границы снимков

Что бы увидеть все изменения, можно использовать конструкции MINVALUE и MAXVALUE


SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

Работает по UNDO, не требует включения flashback для БД flashback_transaction_query Пишет изменения из UNDO. Одна из самых полезных фич -- колонка undo_sql.


SELECT * FROM flashback_transaction_query WHERE table_name = 'PAR';
 
XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER                     UNDO_CHANGE# OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
0100020071D50000   40244472 22.08.2013 13:4   40244474 22.08.2013 13:43 SYSTEM                                    1 UPDATE                           PAR                                                                              SYSTEM                           AAAiW3AABAAAZr6AAA  update "SYSTEM"."PAR" set "TXT" = 'doc 4' where ROWID = 'AAAiW3AABAAAZr6AAA';
0200190091C10000   40244474 22.08.2013 13:4   40244496 22.08.2013 13:44 SYSTEM                                    1 INSERT                           PAR                                                                              SYSTEM                           AAAiW3AABAAAZr6AAB  delete from "SYSTEM"."PAR" where ROWID = 'AAAiW3AABAAAZr6AAB';
03000B00E9E50000   40244496 22.08.2013 13:4   40244498 22.08.2013 13:44 SYSTEM                                    1 UPDATE                           PAR                                                                              SYSTEM                           AAAiW3AABAAAZr6AAB  update "SYSTEM"."PAR" set "TXT" = 'doc 1' where ROWID = 'AAAiW3AABAAAZr6AAB';
0A0003001ECD0000   40244417 22.08.2013 13:4   40244472 22.08.2013 13:43 SYSTEM                                    1 INSERT                           PAR                                                                              SYSTEM                           AAAiW3AABAAAZr6AAA  delete from "SYSTEM"."PAR" where ROWID = 'AAAiW3AABAAAZr6AAA';

На Oracle 12C с pluggable database таблица почему-то не заполняется

SQL> alter session set container=db1;
 
Session altered
 
SQL> create table tbl(id number);
 
Table created
 
SQL> insert into tbl values (1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> SELECT * FROM flashback_transaction_query;
 
SELECT * FROM flashback_transaction_query
 
ORA-01295: несоответствие DB_ID между словарем USE_ONLINE_CATALOG и файлами журналов
 
SQL> alter session set container=CDB$ROOT;
 
Session altered
 
SQL> SELECT * FROM flashback_transaction_query;
 
XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER                     UNDO_CHANGE# OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
 
SQL>

Flashback archive (oracle total recall) Фича позволяет обойти ограничения с UNDO для запросов Flashback version query. Создатет несколько дополнительных табличек, в которые записывает транзакции и изменения. Flashback version query после этого работает в течении интервала, заданного в retention при создании flashback archive


-- Создаем архив, указываем где храним и сколько храним.
-- Можно добавлять tablespace, указывать квоты, чистить

SQL> create flashback archive default fa tablespace users retention 1 year;
 
Done
 
-- При создании таблицы указываем, что хранить для нее архив
SQL> create table tbl(a number) FLASHBACK ARCHIVE;
 
Table created
 

SQL> insert into tbl values(1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> update tbl set a = 2;
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> delete from tbl;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
-- Помимо того, что можно делать SELECT ... AS OF ... еще создаются таблички в которые можно поглядеть
SQL> 
SQL> SELECT * FROM SYS_FBA_HIST_101864;
SELECT * FROM SYS_FBA_HIST_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OPERATION          A
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ----------
AAAY3oAABAAAa/ZAAA                                                                 30076147   30076158 05000E00CC680000 I                  1
AAAY3oAABAAAa/ZAAA                                                                 30076162   30076162 08001C00D0680000 D                  2
AAAY3oAABAAAa/ZAAA                                                                 30076158   30076162 0A000300094C0000 U                  2

SQL> SELECT * FROM SYS_MFBA_NHIST_101864;
SELECT * FROM SYS_MFBA_NHIST_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OPERATION          A
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ----------
SQL> SELECT * FROM SYS_FBA_TCRV_101864;
SELECT * FROM SYS_FBA_TCRV_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OP
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --
AAAY3oAABAAAa/ZAAA                                                                 30076147   30076158 05000E00CC680000 I
AAAY3oAABAAAa/ZAAA                                                                 30076158   30076162 0A000300094C0000 U
 
-- Попробуем изменить структуру. После него SELECT AS OF не заработал, одна созданная табличка пропала
SQL> alter table tbl add b varchar2(100);
 
Table altered
 
SQL> insert into tbl values (1, 'txt 1');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> update tbl set b = 'new txt';
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL> SELECT * FROM SYS_FBA_HIST_101864;
SELECT * FROM SYS_FBA_HIST_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OPERATION          A B
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- --------------------------------------------------------------------------------
AAAY3oAABAAAa/ZAAA                                                                 30076147   30076158 05000E00CC680000 I                  1 
AAAY3oAABAAAa/ZAAA                                                                 30076162   30076162 08001C00D0680000 D                  2 
AAAY3oAABAAAa/ZAAA                                                                 30076158   30076162 0A000300094C0000 U                  2 
SQL> SELECT * FROM SYS_MFBA_NHIST_101864;
SELECT * FROM SYS_MFBA_NHIST_101864;
 
SELECT * FROM SYS_MFBA_NHIST_101864
 
ORA-00942: таблица или представление пользователя не существует
SQL> SELECT * FROM SYS_FBA_TCRV_101864;
SELECT * FROM SYS_FBA_TCRV_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OP
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --
AAAY3oAABAAAa/ZAAA                                                                 30076147   30076158 05000E00CC680000 I
AAAY3oAABAAAa/ZAAA                                                                 30076158   30076162 0A000300094C0000 U
 
SQL> 
SQL> SELECT * FROM SYS_FBA_HIST_101864;
SELECT * FROM SYS_FBA_HIST_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OPERATION          A B
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- --------------------------------------------------------------------------------
AAAY3oAABAAAa/ZAAA                                                                 30076147   30076158 05000E00CC680000 I                  1 
AAAY3oAABAAAa/ZAAA                                                                 30076162   30076162 08001C00D0680000 D                  2 
AAAY3oAABAAAa/ZAAA                                                                 30076158   30076162 0A000300094C0000 U                  2 
SQL> SELECT * FROM SYS_FBA_DDL_COLMAP_101864;
SELECT * FROM SYS_FBA_DDL_COLMAP_101864;
 
  STARTSCN     ENDSCN XID              OPERATION COLUMN_NAME                                                                      TYPE                                                                             HISTORICAL_COLUMN_NAME
---------- ---------- ---------------- --------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
  30076127                                       A                                                                                NUMBER                                                                           A
  30076535                                       B                                                                                VARCHAR2(100)                                                                    B
SQL> SELECT * FROM SYS_FBA_TCRV_101864;
SELECT * FROM SYS_FBA_TCRV_101864;
 
RID                                                                                STARTSCN     ENDSCN XID              OP
-------------------------------------------------------------------------------- ---------- ---------- ---------------- --
AAAY3oAABAAAa/ZAAA                                                                 30076147   30076158 05000E00CC680000 I
AAAY3oAABAAAa/ZAAA                                                                 30076158   30076162 0A000300094C0000 U
 
SQL>

Не работает под пользователем с привелегией DBA (system)

create flashback archive default fa tablespace users retention 1 year
 
ORA-55611: Нет полномочий для управления архивом Flashback по умолчанию

Не работает для pluggable database в 12С

create flashback archive default fa tablespace users retention 1 year
 
ORA-65131: Функция Flashback Data Archive  не поддерживается для подключаемой базы данных.

Документация

2013-08-21

Машина времени или "ой, я тут случайно удалила"

Если пользователь случайно попортил данные, а наша система достаточно сложная с большим количеством таблиц и восстанавливаться не охота, то помимо flashback version query (который as of timestamp или scn) можно использовать пакет DBMS_FLASHBACK

Пример использования с процедурой enable_at_time (так же есть вариант работы с scn)


-- готовим объекты
CREATE TABLE par(ID NUMBER PRIMARY KEY, txt VARCHAR2(10));
CREATE TABLE chi(par_id NUMBER REFERENCES par(ID) ON DELETE CASCADE, sm NUMBER);

INSERT INTO par VALUES (1, 'doc 1');
INSERT INTO par VALUES (2, 'doc 2');

INSERT INTO chi VALUES(1, 100);
INSERT INTO chi VALUES(2, 200);

-- Злой пользователей портит нашу жизнь
DELETE FROM par WHERE ID = 2;
UPDATE chi SET sm= -100 WHERE par_id = 1;

-- Тут мы фиксируем время, на которое будут выполняться запросы
-- В этом примере используется 1 минута назад, но 
BEGIN 
  --dbms_flashback.disable;
  DBMS_FLASHBACK.enable_at_time(SYSDATE - 1/24/60); 
END;
/

-- Запросы без AS OF возвращают данные не испорченные пользователем
SELECT * FROM par;
SELECT * FROM chi;

Ну и ложка дегтя:
create table as select в этом режиме сделать невозможно
insert select в этом режиме сделать невозможно

Это делает эту фичу не сильно пригодной, для использования, хотя обходной маневр все таки есть -- пооткрывать курсоры


BEGIN 
  dbms_flashback.disable;
  DBMS_FLASHBACK.enable_at_time(SYSDATE - 1/24/60); 
END;
/

SELECT * FROM par;
SELECT * FROM chi;

DECLARE
  CURSOR par_cur IS SELECT * FROM par;
  par_row par_cur%ROWTYPE;
  CURSOR chi_cur IS SELECT * FROM chi;
  chi_row chi_cur%ROWTYPE;
BEGIN
  OPEN par_cur;
  OPEN chi_cur;
-- Отключаем фичу, что позволит нам вставлять данные в таблицу
  dbms_flashback.disable;

-- таблицы для бекапов создаем в другой сессии 
  LOOP
    FETCH par_cur INTO par_row;
    EXIT WHEN par_cur%NOTFOUND;
    INSERT INTO par_bkp VALUES par_row;
  END LOOP;

  LOOP
    FETCH chi_cur INTO chi_row;
    EXIT WHEN chi_cur%NOTFOUND;
    INSERT INTO chi_bkp VALUES chi_row;
  END LOOP;

COMMIT;
END;
/

SELECT * FROM par_bkp;
SELECT * FROM chi_bkp;

Но, бесспорно, ctas ... as of ... удобнее

Pluggable database

1. В V$/GV$ запросы небходимо дописывать условие con_id=NNN
или сделать так:


ALTER SESSION SET container=db2;

и после этого все запросы будут выполняться только для db2

2. Открыть PDB можно командой


alter pluggable database pdb_name open;

3. Вьюхи: из dba_xxx сделали cdb_xxx (их порядка 900). Добавили новые pdb_xxx (2 штуки)
Посмотреть список pdb можно в v$pdbs

4. Создать одну базу из другой можно например так


alter pluggable database db1 close immediate;
alter pluggable database db1 open read only;
-- Куда кидаем файлы
alter system set db_create_file_dest=/home/oracle/oradata/db2;
create pluggable database db2 from db1;
alter pluggable database db2 open;

5. 1 - CDB$ROOT
2 - PDB$SEED
3 .. - остальные PDB

2013-08-16

clonedb

Отличное выступление Тима Холла

CloneDB утилита, которая позволяет быстро создать копию базы. На базе источнике создается копия (image copy или backup), которая используется клоном в режиме read-only.
Если клон вносит изменения в данные, то они сохраняются в Copy-on-Write location. Если измений не много, то эти файлы получаются маленькими по размеру.

Алгоритм:
1. Создаем бекап базы источника, кладем бекап в место, доступное для клона. Клонированная база будет использовать бекап как файлы данных.
2. Подготавливаем NFS Client в базе-клоне и монтируем файловую систему. На NFS будет находится COW location
3. Подготавливаем pfile для клона, заполняем на клоне переменные среды
4. Прогоняем скрипт clonedb.pl. Он генерирует два sql скрипта с созданием БД и маппингом бекап-файлов с copy-on-write файлами с использованием пакета dbms_dnfs.
Файлы БД в controlfile клона находятся в backup location
5. Прогоняем созданные скрипты. В 11 версии была ошибка по пересозданию temp tablespace.
Все, база создается за несколько минут вне зависимости от размера источника.

Такие копии хорошо делать, если надо что-то "покрутить". Не подходят, если на клоне будет много изменений и не очень валидны для тестирования производительности.

Неудобно (но достаточно терпимо), что необходимо использовать NFS-клиент
Примечание: к COW-файл системам относятся например ZFS, NetApp, btrfs.

Документация

NB: Эксперементы показывают, что поднять клонированную базу получается только из backup as copy

2013-08-12

Использование новых типов данных в SQL из PL/SQL

В Oracle 12c появилась возможность в SQL, вызываемом из PL/SQL использовать новые типы данных:

BOOLEAN
табличные типы, объявленные в спецификации пакета
ассоциативные массивы (index by)

Документация

Пример:


create or replace package tst is
  TYPE tpt IS TABLE OF VARCHAR2(100);
  TYPE tpt_indexed IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  
  PROCEDURE p;  
  PROCEDURE p2;
  PROCEDURE p3;  
  FUNCTION f(a BOOLEAN) RETURN VARCHAR2; 

end tst;
/

create or replace package body tst is

  t1 tpt;
  t1_ind tpt_indexed;
  
  PROCEDURE p IS
    i NUMBER;
  BEGIN
    t1 := tpt('table type 1', 'table type 2');
  
    FOR rec IN (SELECT VALUE(t) t FROM TABLE(t1) t) LOOP
      dbms_output.put_line(rec.t);      
    END LOOP;
  END;

  PROCEDURE p2 IS
    i NUMBER;
  BEGIN
    t1_ind(1) := 'table index by - 1';
    t1_ind(2) := 'table index by - 2';
  
    FOR rec IN (SELECT VALUE(t) t FROM TABLE(t1_ind) t) LOOP
      dbms_output.put_line(rec.t);      
    END LOOP;
  END;

  FUNCTION f(a BOOLEAN) RETURN VARCHAR2 IS
  BEGIN
    IF a THEN 
      RETURN 'param is true';
    ELSE
      RETURN 'param is false';
    END IF;
  END;
  
  PROCEDURE p3 IS
    i1 VARCHAR2(20);
    i2 VARCHAR2(20);
  BEGIN
    EXECUTE IMMEDIATE 'SELECT tst.f(:p1), tst.f(:p2) FROM dual' INTO i1, i2 USING 1=1, 1=0;
    dbms_output.put_line(i1);
    dbms_output.put_line(i2);    
  END;  
end tst;

BEGIN
  tst.p;
  tst.p2;
  tst.p3;  
END;
/

Results:
table type 1
table type 2
table index by - 1
table index by - 2
param is true
param is false

/

Комментарии

Определение типа должно находится в спецификации. В теле не работает
Что бы передать boolean необходимо использовать EXECUTE IMMEDIATE - USING

2013-08-02

FORALL INDICES OF VALUES OF


DECLARE
  TYPE tbl IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  t tbl;
  idx_t tbl;
  i PLS_INTEGER;
BEGIN
-- Обычный FORALL работает только с плотными коллекциями. Если мы удалили элемент, то будет ошибка
  begin
    t(1) := 1;
    t(10) := 10;
    FORALL i IN t.FIRST .. t.last 
      INSERT INTO a VALUES (t(i));
    dbms_output.put_line('Обработано строк ' || SQL%ROWCOUNT);      
  EXCEPTION WHEN OTHERS THEN
     dbms_output.put_line('Ошибка ' || SQLCODE || ' ' || SQLERRM);
  END;

-- INDICES OF позволяет работать с sparse коллекциями. Синтаксис немного изменен
  begin
    t(1) := 1;
    t(10) := 10;
    FORALL i IN INDICES OF t 
      INSERT INTO a VALUES (t(i));
    dbms_output.put_line('Обработано строк ' || SQL%ROWCOUNT);      
  EXCEPTION WHEN OTHERS THEN
     dbms_output.put_line('Ошибка ' || SQLCODE || ' ' || SQLERRM);
  END;
  
-- VALUES OF позволяет бегать по одной коллекции, а работать с другой. При этом коллекция, по которой мы бегаем не должна быть плотной
  begin
    t(1) := 1;
    t(10) := 10;
    idx_t(1) := 1;
    idx_t(3) := 10;
    FORALL i IN VALUES OF idx_t
      INSERT INTO a VALUES (t(i));
    dbms_output.put_line('Обработано строк ' || SQL%ROWCOUNT);      
  EXCEPTION WHEN OTHERS THEN
     dbms_output.put_line('Ошибка ' || SQLCODE || ' ' || SQLERRM);
  END;


END;
/

Выводы

Обычный FORALL работает только с плотными коллекциями. Если мы удалили элемент, то будет ошибка
INDICES OF позволяет работать с sparse коллекциями. Синтаксис немного изменен
ALUES OF позволяет бегать по одной коллекции, а работать с другой. При этом коллекция, по которой мы бегаем не должна быть плотной