Important !! Clustering Factor Calculation Improvement (Fix You)

Richard Foote's Oracle Blog

Believe me, this article is worth reading :)

I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now be calculated. Many thanks to Martin Decker for pointing this out to me.

As anyone who has attended my Index Seminars will know, the CF of an index is one of the most important statistics used by the Cost Based Optimizer (CBO) in determining the most efficient execution plan. As such, it has always been an issue for me that the manner in which the CF is calculated has been so flawed.

Basically, the CF is calculated by performing a Full Index Scan and looking at the rowid of each index entry. If the…

View original post ещё 1 108 слов

Реклама

ERP Галактика — не пускает пользователей (ORA-20001: RECORD LOCKED)

  • В моем случае ERP Галактика на базе Oracle 11g

Произошел разрыв по сети между сервером ERP Галактика и сервером СУБД Oracle. После сбоя новые подключения от пользователей (кроме администраторов) к ERP Галактика перестали устанавливаться. В логе моей базы Oracle alert_[db_name].log стали появляться сообщения подобного вида:

Errors in file x:\oracle\11.2.0\diag\rdbms\gal\trace\gal_ora_3688.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: RECORD LOCKED AAALsvAAFAAAACHAAB
ORA-06512: at «GAL.ALX$ACTIVEUSERS», line 1
ORA-04088: error during execution of trigger ‘GAL.ALX$ACTIVEUSERS’
ORA-06512: at «GAL.GAL_SYSFUNC_PKG», line 1
ORA-06512: at line 1

где GAL — это схема базы Oracle, где хранятся все данные ERP Галактика.
Триггер GAL.ALX$ACTIVEUSERS установлен на таблицу GAL.X$ACTIVEUSERS, через которую отслеживаются и устанавливаются новые подключения пользователей ERP Галактики. Следовательно строка с ROWID AAALsvAAFAAAACHAAB заблокирована в таблице GAL.X$ACTIVEUSERS. Чтобы найти, кто из подключенных пользователей заблокировал эту строку, я написал и выполнил следующий запрос:

SELECT GAL.INTTOHEX (ss.SID, 4) || GAL.INTTOHEX (ss.SERIAL#, 4)
          L_SESS,
       ss.SID,
       ss.SERIAL#,
       ss.USERNAME,
       loc.ADDRESS
  FROM V$SESSION ss, GAL.SYS#LOCKS loc
 WHERE     STATUS <> 'KILLED'
       AND loc.dbsessionid =
                 GAL.INTTOHEX(ss.SID, 4)
              || GAL.INTTOHEX(ss.SERIAL#, 4)
       AND loc.address = 'AAALsvAAFAAAACHAAB';

Определив Sid и Serial# блокирующей сессии по результатам запроса, я смог убить эту сессию, чтобы строка AAALsvAAFAAAACHAAB стала достуной.

ALTER SYSTEM KILL SESSION 'sid, Serial#' IMMEDIATE;

После того, как сессия была убита, новые пользовательские сессии к ERP Галактика стали устанавливаться.

Remove Listener.log without restart Oracle Listener service

  • Oracle on Windows

Иногда требуется удалить файл listener.log, который стал занимать много свободного места на диске, но при этом не останавливая службу Oracle Listener. Для этого имеется следующее решение:

1. Переходим в каталог с listener.log и запускаем консоль управления listener.

CMD>
CD c:\OracleDB11_R2\11.2.0\diag\tnslsnr\srv01test\test01listener\trace\
LSNRCTL

2. В консоли LSNRCTL делаем текущим listener, для которого будем удалять файл-лог, и выполняем команду отключения логирования.

LSNRCTL>
set current_listener test01listener
set log_status off
exit

3. Удаляем физически файл-лог.

CMD>
del test01listener.log

4. Затем в консоли LSNRCTL включаем логирование listener.

LSNRCTL>
set current_listener test01listener
set log_status on
exit

5. Слушатель (Listener) начнет запись в новый файл-лог.

Distributed transaction and ORA-01591: lock held by in-doubt distributed transaction

Иногда при работе с удаленной базой non-Oracle по средствам Heterogenous service (HSODBC) или Oracle GateWay (DG4MSQL) может возникнуть ситуация, когда во время распределенной транзакции происходит сбой (при DML-операциях) и транзакция зависает, что может вызвать эксклюзивные блокировки некоторых объектов или вообще заблокировать доступ к удаленной базе.
Чтобы разрешить эту ситуацию нужно выполнить следующие шаги:

1. Проверяем alert_<your_dbname>.log на ошибки связанные с распределенными транзакциями или смотрим на всплывающую ошибку при работе с удаленной базой. Например, ORA-01591: lock held by in-doubt distributed transaction 9.3.919809,
где 9.3.919809 — это ID транзакции, которая вызывает ошибку.

2. Проверяем в каком состоянии (State) находится транзакция:

SELECT LOCAL_TRAN_ID,
STATE,
FAIL_TIME,
TOP_OS_USER,
TOP_OS_TERMINAL
FROM SYS.PENDING_TRANS$
WHERE LOCAL_TRAN_ID = '9.3.919809';

LOCAL_TRAN_ID   STATE       FAIL_TIME              TOP_OS_USER      TOP_OS_TERMINAL
---             ---         ---                    ---              ---
9.3.919809      prepared    12.05.2014 14:55:00    ITWARE\ITUSER    IT03

LOCAL_TRAN_ID - ID транзакции
STATE - состояние транзакции (особое внимание!)
FAIL_TIME - время сбоя
TOP_OS_USER - имя пользователя ОС
TOP_OS_TERMINAL - имя компьютера пользователя

3. Определив состоянии зависшей транзакции, можем перейти к таблице 1, где описаны варианты решения проблем при помощи пакета DBMS_TRANSACTION (состояние транзакции в поле State Column):

STATE Column

State of Global Transaction

State of Local Transaction

Normal Action

     Alternative Action

Collecting Rolled back Rolled back None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Committed Committed Committed None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Prepared Unknown Prepared None Force commit or rollback
Forced commit Unknown Committed None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Forced rollback Unknown Rolled back None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Forced commit Mixed Committed Manually remove  inconsistencies then use PURGE_MIXED
Forced rollback Mixed Rolled back Manually remove inconsistencies then use PURGE_MIXED

Пример
После того, как произошел сбой при обновлении данных в одной из таблиц удаленной базы (MS SQL) через интерфейс Oracle DG4MSQL, дальнейшее подключение к этой базе из Oracle стало невозможным. Процесс DG4MSQL.exe отключен, сессия пользователя отключена, но зависшая транзакция не завершилась (не откатилась).
Выполнив п.1 — 3, описанные выше, определил, что зависшая распределенная транзакция 9.3.919809 находится в состоянии prepared. По таблице 1 в качестве альтернативы решения, предлагается выполнить commit force или rollback force.
Я использовал:

commit force '9.3.919809';

Затем проверил вновь состояние транзакции, оно изменилось на forced commit. По таблице 1 предлагается удалить транзакцию при помощи процедуры DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY:

EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('9.3.919809');

После выполнения данной процедуры, транзакция была успешно удалена, и она исчезла из зависших (запрос п.2 ничего не вернул по ней). После чего подключение к удаленной базе было восстановлено.

Дополнительно:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txns001.htm#ADMIN12211
http://www.dba-oracle.com/t_two_phase_commit_2pc.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txnman006.htm#ADMIN12266
http://blog.itpub.net/38267/viewspace-713103
https://blogs.oracle.com/db/entry/oracle_support_master_note_for_troubleshooting_managed_distributed_transactions_doc_id_1006641

Load data from file OS (txt, csv) using UTL_FILE

Написал процедуру LOADFROMFILE используя функционал пакета UTL_FILE, позволялющий быстро и практично загружать данные из текстового файла ОС (txt, csv) с разделителями (delimeters) в указанную таблицу. Процедура имеет несколько важных проверок, которые сократят ваше время, например, она сама считает количество столбцов в файле и сравнивает с количеством столбцов в таблице; конвертирует записи в тип DATE, где это нужно; возвращает сообщения об ошибках связанных с неверностью входных параметров, отсутствия файла и т.п.

 

CREATE OR REPLACE PROCEDURE LOADFROMFILE(dir_name VARCHAR2, file_name VARCHAR2, owner_name VARCHAR2, tab_name VARCHAR2, delim CHAR, exception_msg out VARCHAR2)
AS
/*
dir_name - Oracle директория расположения файлов
file_name - имя текстового файла
owner_name - имя владельца таблицы БД Oracle
tab_name - имя таблицы БД Oracle
delim - разделитель (delimeter) данных в файле (например, ';')
exception_msg - это возвращаемое сообщение о состоянии выполнения процедуры, позволяющзая понять, где мы допустили ошибку
*/
V_LINE CLOB;
F1 UTL_FILE.FILE_TYPE;
i INT;
colmax INT;
DATA_TYPE VARCHAR2(50);
TYPE arr_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
arr arr_table;
STRCOM CLOB;
str CLOB;
tabnamechek INT;
cnt INT DEFAULT 0;
csvfields INT;

BEGIN

    -- получаем количество столбцов в таблице
    select nvl((SELECT count(*) 
    FROM all_tables t, all_tab_columns c 
    WHERE t.owner=upper(owner_name) AND T.TABLE_NAME = C.TABLE_NAME AND t.TABLE_NAME = upper(tab_name) 
    GROUP BY t.TABLE_NAME),0) INTO colmax from dual;

  IF colmax > 0 THEN
    -- открываем файл для чтения
    F1 := UTL_FILE.FOPEN(dir_name, file_name, 'R');

    -- проверяем открылся ли файл
    IF UTL_FILE.IS_OPEN(F1) THEN
       exception_msg:='File '|| file_name ||' is Open';
    ELSE
       exception_msg:='File '|| file_name ||' is not Open';
    END IF;
    -- считываем построчно записи из файла
    LOOP
        cnt:=cnt+1;

       BEGIN
            UTL_FILE.GET_LINE(F1,V_LINE);
            -- получаем количество столбцов в одной строке файла
            csvfields:=regexp_count(regexp_replace(V_LINE,'.*?'),''||delim||'') + 1;
            -- проверяем совпадает ли количество столбцов таблицы и файле
            IF csvfields = colmax THEN
                -- получаем запись для каждого столбца строки
               FOR i IN 1..csvfields
               LOOP
                    -- заносим записи N-строки в массив
                    arr(i):=trim(REGEXP_SUBSTR(V_LINE, '[^'||delim||']+', 1, i));
                    
                    SELECT C.DATA_TYPE INTO DATA_TYPE
                    FROM all_tables t, all_tab_columns c 
                    WHERE t.owner=upper(owner_name) AND T.TABLE_NAME = C.TABLE_NAME AND t.TABLE_NAME = upper(tab_name)
                    AND C.COLUMN_ID = i;
                        
                    -- составляем часть кода динамического sql
                    -- если тип столбца DATE, то конвертируем полученную запись в дату
                    IF DATA_TYPE = 'DATE' THEN
                        str:=str||'to_date('''||arr(i)||''',''dd.mm.yyyy hh24:mi:ss'')'||',';
                    ELSE 
                        str:=str||''''||arr(i)||''''||',';
                    END IF;

                    -- вставляем полученную строку в таблицу
                    IF i = csvfields THEN

                            BEGIN
                                STRCOM:='INSERT INTO '||owner_name||'.'||tab_name||' values('||regexp_replace(str,',$','')||')';

                                EXECUTE IMMEDIATE STRCOM;
                                COMMIT;

                                str:=NULL;
                                arr.DELETE;
                            END;
                                              
                    END IF;
                    
               END LOOP;
               
            ELSE
                exception_msg:='Wrong number of columns '||file_name||', because csv('||csvfields||') <> tab('||colmax||').';
                EXIT;
                UTL_FILE.FCLOSE(F1);
            END IF;
       EXCEPTION WHEN No_Data_Found THEN EXIT;
       END;
                    
    END LOOP;
    -- закрываем файл
    UTL_FILE.FCLOSE(F1);
   exception_msg:=exception_msg || chr(13) || 'Data load!'; 
 ELSE
   exception_msg:='Table name OR owner name is wrong!';
 END IF;     

    EXCEPTION 
    WHEN utl_file.invalid_operation THEN exception_msg:='File '|| file_name ||' is Not Open.';
    WHEN UTL_FILE.INVALID_PATH THEN exception_msg:='Invalid file '|| file_name ||' path.';
    WHEN UTL_FILE.READ_ERROR THEN exception_msg:='Read error file '|| file_name ||'.';
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN exception_msg:='File ' || file_name || ' handle was Invalid.';
    WHEN UTL_FILE.INTERNAL_ERROR THEN exception_msg:='An unspecified error in PL/SQL.';
    WHEN OTHERS THEN exception_msg:=('ERROR: '||DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    
END;

 

Вызов процедуры свыполняется следующим образом:

declare
    outrep varchar2(4000);
begin
    LOADFROMFILE('DBSTAT','apex_stat_sga_16052014_14.csv','SDUSER','STAT_SGA',';',outrep);
    dbms_output.put_line(outrep);
end;

--dbms_output--
File apex_stat_sga_16052014_14.csv is Open.
Data load!

Как получить инициалы (INITIALS) из ФИО (NAME)

  • Oracle 10, 11

Данная конструкция позволяет из ФИО (NAME) сотрудника получить фамилию (LASTNAME), имя (FIRSTNAME), отчество (FATHERNAME) и иницалы (INITIALS).

with t as
(select 'Иванов Петр Васильевич' as name from dual)
select t.name,
regexp_replace(t.name, ' (.*)') LASTNAME,
regexp_replace(t.name, ' (.*)|^[^ ]* ') FIRSTNAME,
regexp_replace(t.name, '(.*) ') FATHERNAME,
regexp_replace(regexp_replace(t.name, ' (.*)|^[^ ]* '),'.*','.',2,1)||regexp_replace(regexp_replace(t.name, '(.*) '),'.*','.',2,1) INITIALS
from t;

NAME                    LASTNAME  FIRSTNAME  FATHERNAME  INITIALS
----------------------  ------    ----       ----------  ----                                                            
Иванов Петр Васильевич  Иванов    Петр       Васильевич  П.В.

Расчет рабочих часов в указанном периоде

Я рассматривал случай рабочего дня с 08:00 по 17:00, включая обед с 12:00 по 13:00. Функция рассчитывает количество часов, которые попадают в заданный диапазон времени с учетом выходных дней (суббота и воскресенье), рамок рабочего дня и обеда.

CREATE OR REPLACE function worktime(beg_datetime in date, end_datetime in date)
return number
as
    kol_day number;
    type arr_table is table of number index by binary_integer;
    arr arr_table;
    res number default 0;
    
begin

   -- Чистим массив
   arr.delete;
   
 if beg_datetime < end_datetime then
   -- Расчет количества дней в заданном периоде дат
   kol_day := round(trunc(end_datetime) - trunc(beg_datetime));
   
   for i in 0..kol_day loop
      -- Проверка выходных дней (exclude Sunday, Saturday)
      if not to_char(beg_datetime + i, 'd') in (7, 1) then 
        -- Расчет количества рабочего времени по каждому дню
        arr(i) := case        
        -- если промежуточный день
        when not trunc(beg_datetime + i) in (trunc(beg_datetime), trunc(end_datetime)) 
            then 8
        -- если даты заданного дипазона совпадают
        when kol_day = 0 and trunc(beg_datetime + i) = trunc(end_datetime) then
           round(end_datetime - (beg_datetime + i), 3) 
        else case 
             -- если начало периода
             when trunc(beg_datetime + i) = trunc(beg_datetime) and
             to_char(beg_datetime, 'hh24') >= 8 and to_char(beg_datetime, 'hh24') < 12 
                then round((((trunc(beg_datetime) + 17/24) - beg_datetime)*24) - 1,3)
             when trunc(beg_datetime + i) = trunc(beg_datetime) and 
             to_char(beg_datetime, 'hh24') >= 13 and to_char(beg_datetime, 'hh24') < 17
                then round((((trunc(beg_datetime) + 17/24) - beg_datetime)*24),3)
             -- если конец периода
             when trunc(beg_datetime + i) = trunc(end_datetime) and
             to_char(end_datetime, 'hh24') >= 8 and to_char(end_datetime, 'hh24') < 12 
                then round((end_datetime - (trunc(end_datetime) + 8/24)) * 24,3)
             when trunc(beg_datetime + i) = trunc(end_datetime) and 
             to_char(end_datetime, 'hh24') >= 13 and to_char(end_datetime, 'hh24') < 17
                then round((end_datetime - (trunc(end_datetime ) + 8/24 + 1/24)) * 24,3)     
             -- ошибка
             else 0
             end
        end;
        
        -- Расчет суммароного рабочего времени за период
        res:= res + arr(i);
      
      end if;
                        
   end loop;

 else
 res:=-1;    
    DBMS_OUTPUT.PUT_LINE('Указан неверный диапазон дат!');
 
 end if;
   return res;
end;
/

Пример, вызова функции:

select worktime(to_date('07.04.2014 16:12:34','dd.mm.yyyy hh24:mi:ss'), to_date('20.04.2014 09:20:15','dd.mm.yyyy hh24:mi:ss')) RES from dual;

RES
---
72.791

Репликация данных из MySQL в Oracle используя Oracle GoldenGate

Использовал дистрибутивы СУБД MySQL и Oracle:

  • MySQL Community Server 5.6.16 (Windows x86)
  • Oracle DB 11g Release 2 v11.2.0.1 (Windows x64)

Использовал дистрибутивы Oracle GoldenGate:

A. Установка и настройка OGG для сервера-источника MySQL (SOURCE)

A1. Подготовка сервера MySQL для развертывания OGG

•    Движок хранения данных (storage engine) только InnoDB
•    В файле конфигурации сервера MySQL (my.cnf) должны быть указаны параметры бинарных логов:
log-bin – этот параметр определяет место размещения бинарных журналов и необходим для OGG.
log-bin-index – этот параметр определяет место размещения индексов бинарных журналов. По умолчанию та же директории, где и бинарные журналы.
max_binlog_size – задается максимальный размер журнального файла в байтах, при достижения предела, все записи будут писаться в новый журнальный файл.
binlog_format – формат бинарных журналов (ROW, STATEMENT, MIXED). Extractor будет работать только с журналами в формате ROW, остальные форматы приведут к аварийному завершению.

Пример:
binlog_format=row
log-bin=»d:/MySQL DB/mybinlogs/uapbin»
log-bin-index=»d:/MySQL DB/mybinlogs/uapbin.index»
log-error=»d:/MySQL DB/mybinlogs/uapbin.err»
max_binlog_size=20971520

•    Остальные параметры MySQL сервера настраиваются индивидуально.

A2. Установка OGG для MySQL

1.    На одном из дисков, который выделяем под файлы базы данных и для хранения trail-файлов, создаем директорию OGG11MGR. Например, D:\OGG11MGR.
2.    Разархивируем файлы дистрибутива из архива дистрибутива в созданный каталог.
3.    В директории запускаем консоль ggsci.exe и в ней выполняем команду:
ggsci> CREATE SUBDIRS

После чего в директории C:OGG11MGR появятся рабочие каталоги: dirpcs, dirout, dirtmp, dirchk, dirdat, dirdef, dirrpt, dirsql.
4.    Из директории C:OGG11MGR копируем файлы category.dll, ggsmsg.dll в c:WindowsSystem32.
5.    Теперь зададим собственное имя для менеджера процессов OGG. Для этого запустим ggsci и выполним команду:
ggsci> EDIT PARAMS ./GLOBALS

После чего откроется текстовый редактор, где будет предложено создать новый файл, следует согласиться и прописать в файле параметр:
MGRSERVNAME OGGMGR1

OGGMGR1 – уникальное имя менеджера процессов.
Сохранить изменения в файле и закрыть его.
6.    Далее создаем экземпляр OGG (управляющий процесс, который создает OGG процессы, выделяет номера портов и выполняет обслуживание файлов). Для этого вызовем консоль и выполним:
ggsci> EDIT PARAMS MGR

откроется текстовый редактор, где будет предложено создать новый файл, следует согласиться и прописать в файле следующие параметры:
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS *

,где PORT – TCPIP порт для менеджера процессов OGG, по которому он взаимодействует с удаленными процессами. Используйте порт по умолчанию, если это возможно.
DYNAMICPORTLIST – список доступных портов, по которым локальные процессы OGG могут связываться с удаленными процессами OGG на других машинах.
AUTOSTART – параметр, который позволяет автоматически запускать процессы OGG после запуска менеджера процессов.
AUTORESTART – параметр, для автоматического перезапуска процессов OGG при сбоях по сети, что может вызвать прерывание чтения транзакционных журналов. RETRIES – максимальное количество перезапусков, WAITMINUTES – время ожидания в минутах, от аварии до перезапуска.
STARTUPVALIDATIONDELAY – сколько секунд ожидать, до проверки состояния процесса OGG. И если после этого времени процесс не ответил, то регистрировать ошибку.
PURGEOLDEXTRACTS – позволяет очищать обработанные файлы для того, чтобы они не потребляли много дискового места.
7.    Сохранить изменения в файле и закрыть его.
8.    Настроим менеджер процессов как службу Windows. Для этого в директории C:OGG11MGR вызовем утилиту install c опцией addservice (добавляет менеджер в качестве сервиса с именем указанным в MGRSERVNAME параметре в файле GLOBALS, если таковой существует, или по умолчанию GGSMGR):
cmd> install addservice

После чего проверяем созданную нами (OGGMGR1) службу в списке служб Windows и настраиваем для нее автоматический запуск.
9.    Запускаем менеджер процессов (службу OGGMGR1) в списке служб или же в консоли ggsci:
ggsci> start manager

10.    Инсталлируем менеджер событий (addevents — добавляет OGG события в диспетчере событий Windows):
cmd> install addevents

11.    На этом установка и настройка OGG на источнике (MySQL) завершена. Далее рассмотрим установку и настройку на целевой базе Oracle.

B. Установка и настройка OGG для целевого сервера Oracle (TARGET)

1.     На одном из дисков, который выделяем под файлы базы данных и для хранения trail-файлов, создаем директорию OGG11MGR. Например, D:\OGG11MGR.
2.     Разархивируем файлы дистрибутива из архива в созданный каталог.
3.     В директории запускаем консоль ggsci.exe и в ней выполняем команду:
ggsci> CREATE SUBDIRS

После чего в директории D:\OGG11MGR появятся следующие каталоги: dirpcs, dirout, dirtmp, dirchk, dirdat, dirdef, dirrpt, dirsql.
4.     Из директории D:\OGG11MGR копируем файлы category.dll, ggsmsg.dll в c:\Windows\System32.
5.     Теперь зададим собственное имя для менеджера процессов OGG. Для этого запустим ggsci и выполним команду:
ggsci> EDIT PARAMS ./GLOBALS

После чего откроется текстовый редактор, где будет предложено создать новый файл, следует согласиться и прописать в файле параметр:
MGRSERVNAME OGGMGR2

OGGMGR2 – уникальное имя менеджера процессов.
Сохранить изменения в файле и закрыть его.
6.     Далее конфигурируем диспетчер процессов OGG (управляющий процесс, который создает OGG процессы, выделяет номера портов и выполняет обслуживание). Для этого вызовем консоль и выполним:
ggsci> EDIT PARAMS MGR

откроется текстовый редактор, где будет предложено создать новый файл, следует согласиться и прописать в файле параметр:
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS *

,где PORT – TCP\IP порт для менеджера процессов OGG, по которому он взаимодействует с удаленными процессами. Используйте порт по умолчанию, если это возможно.
DYNAMICPORTLIST – список доступных портов, по которым локальные процессы OGG могут связываться с удаленными процессами OGG на других машинах.
AUTOSTART – параметр, который позволяет автоматически запускать процессы OGG после запуска менеджера процессов.
AUTORESTART – параметр, для автоматического перезапуска процессов OGG при сбоях по сети, что может вызвать прерывание чтения транзакционных журналов. RETRIES – максимальное количество перезапусков, WAITMINUTES – время ожидания в минут, от аварии до перезапуска.
STARTUPVALIDATIONDELAY – сколько секунд ожидать, до проверки состояния процесса OGG. И если после этого времени процесс не ответил, то регистрировать ошибку.
PURGEOLDEXTRACTS – позволяет очищать обработанные файлы для того, чтобы они не потребляли много дискового места.
7.     Сохранить изменения в файле и закрыть его.
8.     Настроим менеджер процессов как служба Windows. Для этого в директории C:\OGG11MGR вызовем утилиту install c опцией addservice (добавляет менеджер в качестве сервиса с именем указанным в MGRSERVNAME параметре в файле GLOBALS, если таковой существует, или по умолчанию GGSMGR):
cmd> install addservice

После чего проверяем созданную нами (OGGMGR2) службу в списке служб Windows и настраиваем для нее автоматический запуск.
9.     Запускаем менеджер процессов (службу OGGMGR2) в списке служб или же в консоли ggsci:
ggsci> start manager

10. Инсталлируем менеджер событий (addevents — добавляет OGG события в диспетчере событий Windows):
cmd> install addevents

На этом установка и настройка OGG на целевом сервере (Oracle) завершена. Далее приступим к созданию самой схемы репликации.

C. Создание OGG-схемы репликации MySQL-to-Oracle

* все настройки выполняются из консоли ggsci.

C1. На сервере источнике (SOURCE — MySQL) заходим в консоль ggsci:
1.    Создаем группу захватчика (extract), например, с именем EXT01:
ggsci> ADD EXTRACT EXT01, TRANLOG, BEGIN NOW

TRANLOG – указывает, что в качестве источника данных будут выступать транзакционные логи.
BEGIN NOW – начать захват данных сразу после запуска extract.
2.    Создаем канал для передачи данных в файлы с названием ua:
ggsci> ADD EXTTRAIL D:\OGG11MGR\dirdat\ua, EXTRACT EXT01, MEGABYTES 10

MEGABYTES — допустимый размер файлов.
3.    Редактируем файл конфигурации извлечения данных:
ggsci> EDIT PARAM EXT01

В отрывшийся файл добавляем параметры подключения к источнику и извлечения данных, указывая нужные для репликации таблицы:
extract EXT01
cachemgr cachesize 128M
dboptions host localhost, connectionport 3306
sourcedb 2ceh_2otd_fms2_realtime_db, userid ggdirector, password **********
TRANLOGOPTIONS  ALTLOGDEST «d:\MySQL DB\mybinlogs\uapbin.index»
discardfile D:\OGG11MGR\dirrpt\EXT01.dsc, purge
exttrail D:\OGG11MGR\dirdat\ua
table 2ceh_2otd_fms2_realtime_db.CONNECTION_PLCS;
table 2ceh_2otd_fms2_realtime_db.PLC11_ALL_DATA_REALTIME;
table 2ceh_2otd_fms2_realtime_db.PLC11_FAILS_REALTIME;
table 2ceh_2otd_fms2_realtime_db.PLC12_ALL_DATA_REALTIME;
table 2ceh_2otd_fms2_realtime_db.PLC12_FAILS_REALTIME;
table 2ceh_2otd_fms2_sortirovka.data;

cachemgr cachesize – задаем размер кэша памяти,к оторый может использовать extract\pump\replicat. По умолчанию на Win XP OGG определяет его 1Гб, на Win 7 x64 = 64Гб.
dboptions host, connectionport, sourcedb, userid, password – это опции соединения с базой данных соотвественно – имя хоста, порт (на котором база работает), имя базы, логин и пароль пользователя (под которым будут читаться данные).
TRANLOGOPTIONS  ALTLOGDEST – параметр для указания расположения файла индексов бинарных логов MySQL.
Discardfile – параметр, указывающий путь к отчет ошибок при сбоях.
Table – указывает на таблицу, которая будет включена в репликацию.

Затем сохраняем изменения.
3.    Конфигурируем группу DATA PUMP EXTRACT
ggsci> ADD EXTRACT PUMP01, EXTTRAILSOURCE D:\OGG11MGR\dirdat\ua, BEGIN NOW

BEGIN NOW – начать захват данных сразу после запуска pump
4.    Создаем канал для передачи данных удаленно (на target)
ggsci> ADD RMTTRAIL D:\OGG11MGR\dirdat\ua, EXTRACT PUMP01, MEGABYTES 10

RMTTRAIL – задает полный путь трэйл-файлам, с которых осуществляется накат данных на целевую базу.
MEGABYTES — допустимый размер файлов.
5.    Редактируем файл конфигурации передачи/хранения данных:
ggsci> EDIT PARAM PUMP01

В файл добавляем параметры извлечения:
extract PUMP01
cachemgr cachesize 128M
dboptions host localhost, connectionport 3306
sourcedb 2ceh_2otd_fms2_realtime_db, userid ggdirector, password *******
discardfile D:\OGG11MGR\dirrpt\PUMP01.dsc, purge
rmthost serv01.test.int, mgrport 7809
rmttrail D:\OGG11MGR\dirdat\ua
table 2ceh_2otd_fms2_realtime_db.CONNECTION_PLCS;
table 2ceh_2otd_fms2_realtime_db.PLC11_ALL_DATA_REALTIME;
table 2ceh_2otd_fms2_realtime_db.PLC11_FAILS_REALTIME;
table 2ceh_2otd_fms2_realtime_db.PLC12_ALL_DATA_REALTIME;
table 2ceh_2otd_fms2_realtime_db.PLC12_FAILS_REALTIME;
table 2ceh_2otd_fms2_sortirovka.data;

cachemgr cachesize – задаем размер кэша памяти,к оторый может использовать extract\pump\replicat. По умолчанию на Win XP OGG определяет его 1Гб, на Win 7 x64 = 64Гб.
dboptions host, connectionport, sourcedb, userid, password – это опции соединения с базой данных соотвественно – имя хоста, порт (на котором база работает), имя базы, логин и пароль пользователя (под которым будут читаться данные).
TRANLOGOPTIONS  ALTLOGDEST – параметр для указания расположения файла индексов бинарных логов MySQL.
Discardfile – параметр, указывающий путь к отчет ошибок при сбоях.
Rmthost – имя или ip удаленного хоста.
Mgrport – порт, на котором работает удаленный агент OGG.
Table – указывает на таблицу, которая будет включена в репликацию.

Затем сохраняем изменения.

C2. Далее на целевом сервере (TARGET — Oracle) запускаем консоль ggsci:

1. Создаем группу репликации, например, REP01:
ggsci> ADD REPLICAT REP01, EXTTRAIL D:\OGG11MGR\dirdat\ua, BEGIN NOW, NODBCHECKPOINT

EXTTRAIL – для replicat значение этого параметра соответствует значению RMTTRAIL (для pump).
BEGIN NOW — начать захват данных сразу после запуска rep01.
NODBCHECKPOINT – не будет хранить контрольную точку в целевой базе данных.

2. Редактируем файл конфигурации репликации
ggsci> EDIT PARAM REP01

В файл добавляем параметры подключения к базе Oracle и применения полученных данных:
replicat REP01
cachemgr cachesize 128M
SETENV (NLS_LANG=»AMERICAN_AMERICA.CL8MSWIN1251″)
discardfile D:\OGG11MGR\dirrpt\REP01.dsc, purge
assumetargetdefs
userid ggDirector@ORCL, password ********
map 2ceh_2otd_fms2_realtime_db.CONNECTION_PLCS, target UAPOGG.connection_plcs, ignoredeletes, ignoretruncates;
map 2ceh_2otd_fms2_realtime_db.PLC11_ALL_DATA_REALTIME, target UAPOGG.plc11_all_data_realtime, ignoredeletes, ignoretruncates;
map 2ceh_2otd_fms2_realtime_db.PLC11_FAILS_REALTIME, target UAPOGG.plc11_fails_realtime, ignoredeletes, ignoretruncates;
map 2ceh_2otd_fms2_realtime_db.PLC12_ALL_DATA_REALTIME, target UAPOGG.plc12_all_data_realtime, ignoredeletes, ignoretruncates;
map 2ceh_2otd_fms2_realtime_db.PLC12_FAILS_REALTIME, target UAPOGG.plc12_fails_realtime, ignoredeletes, ignoretruncates;
map 2ceh_2otd_fms2_sortirovka.data, target UAPOGG.sortirovka_data, ignoredeletes, ignoretruncates;

cachemgr cachesize – задаем размер кэша памяти, который может использовать extract\pump\replicat. По умолчанию на Win XP OGG определяет его 1Гб, на Win 7 x64 = 64Гб.
dboptions host, connectionport, sourcedb, userid, password – это опции соединения с базой данных соотвественно – имя хоста, порт (на котором база работает), имя базы, логин и пароль пользователя (под которым будут читаться данные).
Discardfile – параметр, указывающий путь к отчет ошибок при сбоях.
Assumetargetdefs – указывает на то, что структура таблиц источника и целевой базы схожи.
SETENV  — задаем переменную окружения Windows, например для кодировки NLS_LANG.
Map, target – указывает какой таблице источника соответствует таблицу целевой базы.

Сохраняем изменения.

C3. После того, как элементы репликации созданы, запустим их в следующем порядке:
На сервере источнике:
ggsci> start EXT01
ggsci> start PUMP01

На целевом сервере:
ggsci> start REP01

Проверим с помощью команды состояние элементов репликации:
ggsci> info <>

<> — имя элемента репликации, например info EXT01

Если все в порядке (все элементы в состоянии Running), тогда ждем изменений в реплицируемой таблице, и смотрим на целевой базе, как туда записались данные. Иначе смотрим ошибки в файле ggserr.log (корневая папка) или файлах .rpt в каталоге D:\OGG11MGR\dirrpt.

Конвертация Unixtime в Oracle Date

Известно, что unixtime считается в секундах и начальная точка отсчета берется 01.01.1970 00:00:00. Тогда зная это можно преобразовать unixtime в oracle date по следующей формуле:

select to_char(to_date('01.01.1970 00:00:00','dd.mm.yyyy hh24:mi:ss') + 1/24/60/60 * :unixtime, 'dd.mm.yyyy hh24:mi:ss') as from_unix from dual;

Например, переменная :unixtime = 1391010620, тогда запрос вернет нам следующее:

select to_char(to_date('01.01.1970 00:00:00','dd.mm.yyyy hh24:mi:ss') + 1/24/60/60 * 1391010620, 'dd.mm.yyyy hh24:mi:ss') as from_unix from dual;

FROM_UNIX
-------------------
29.01.2014 15:50:20

Импорт из ActiveDirectory в Oracle

Подключиться из Oracle к AD возможно с помощью пакета DBMS_LDAP (доступ по Lightweight Directory Access Protocol).

Создадим таблицу для регистрации данных из AD.

CREATE TABLE USERS_AD
( CN          VARCHAR2(256 BYTE),
JOB_TITLE   VARCHAR2(256 BYTE),
DEPARTMENT  VARCHAR2(256 BYTE),
LOGIN       VARCHAR2(256 BYTE),
STATUS      VARCHAR2(256 BYTE),
DN_SHORT    VARCHAR2(128 BYTE),
DN          VARCHAR2(512 BYTE)
);

Создадим процедуру извлечения данных из AD и регистрации их в таблице USERS_AD.

CREATE OR REPLACE procedure AD_TO_DB
AS

ldap_server Varchar2 (256);
ldap_port Varchar2 (256);
ldap_dn Varchar2 (256);
ldap_user Varchar2 (256);
ldap_password Varchar2 (256);
ldap_base Varchar2 (256);
ldap_session dbms_ldap.session;
retval PLS_INTEGER;
ldap_message dbms_ldap.message;
ldap_attrs dbms_ldap.string_collection;
ldap_entry dbms_ldap.message;
ldap_attr_name VARCHAR2(256);
ldap_ber_elmt dbms_ldap.ber_element;
ldap_attr_index PLS_INTEGER;
entry_index PLS_INTEGER;
attr_index PLS_INTEGER;
i PLS_INTEGER;
ldap_vals dbms_ldap.string_collection;
ldap_log Varchar2 (256);
ldap_cn Varchar2 (256);
ldap_dep Varchar2 (256);
ldap_title Varchar2 (256);
ldap_stat Varchar2 (128);
ldap_dn_short Varchar2 (128);

begin

execute immediate 'truncate table USER_FROM_AD';
commit;

retval := -1;

--Подключение к серверу AD
ldap_server := 'ldap.terra.int'; --указать ваш dns или ip сервера AD
ldap_port := '389'; --by default TCP port 389
ldap_session := dbms_ldap.init(hostname => ldap_server,
portnum => ldap_port);
dbms_output.put_line(RPAD('LDAP session ',25,' ') || ': ' || RAWTOHEX(SUBSTR(ldap_session,1,8)) || '(returned from init)');

--Авторизация в AD
ldap_user := 'cn=droid,ou=specials,dc=terra,dc=int'; --указать пользователя домена, который имеет доступ к AD
ldap_password := 'xxxxxxx';
--запускаем простую аутентификацию к AD по имени пользователя и ввода пароля
retval := dbms_ldap.simple_bind_s(ld => ldap_session,
dn => ldap_user,
passwd => ldap_password);
dbms_output.put_line(RPAD('LDAP User ',25,' ') || ': ' || ldap_user);
dbms_output.put_line(RPAD('LDAP Pass ',25,' ') || ': ' || ldap_password);

dbms_ldap.use_exception := TRUE;

--Просмотр аттрибутов по ветке AD
ldap_base := 'ou=regions,ou=company users,dc=terra,dc=int'; --интересующая ветка AD
ldap_attrs(1) := 'cn';  --ФИО
ldap_attrs(2) := 'title'; --должность
ldap_attrs(3) := 'department';  --подразделение
ldap_attrs(4) := 'sAMAccountName';  --логин
ldap_attrs(5) := 'userAccountControl'; --статус
--filter => 'objectclass = *'
--запускаем функцию синхронного поиска в каталоге с фильтром по объектному классу - пользователи
retval := DBMS_LDAP.search_s(ld       => ldap_session,
base     => ldap_base,
scope    => DBMS_LDAP.SCOPE_SUBTREE,
filter   => 'objectclass=User',
attrs    => ldap_attrs,
attronly => 0,
res      => ldap_message);
dbms_output.put_line(RPAD('search_s Returns ',25,' ') || ': '|| TO_CHAR(retval));
dbms_output.put_line(RPAD('LDAP message ',25,' ') || ': ' || RAWTOHEX(SUBSTR(ldap_message,1,8)) || '(returned from search_s)');

--Подсчет вернувшихся значений
retval := dbms_ldap.count_entries(ld => ldap_session,
msg => ldap_message);
dbms_output.put_line(RPAD('Number of Entries ',25,' ') || ': '|| TO_CHAR(retval));

--Получение первой записи
ldap_entry := dbms_ldap.first_entry(ld => ldap_session,
msg => ldap_message);
entry_index := 0;

<< entry_loop >>
WHILE ldap_entry IS NOT NULL
LOOP

--Вывести текущую запись
ldap_dn := DBMS_LDAP.get_dn(ld => ldap_session,
ldapentry => ldap_entry);

ldap_dn_short := trim(replace(substr(replace(ldap_dn,substr(ldap_dn,-35,100),''),instr(replace(ldap_dn,substr(ldap_dn,-35,100),''),'OU=',-1)),'OU=',''));
--Получить первый атрибут
ldap_attr_name := dbms_ldap.first_attribute(ld => ldap_session,
ldapentry => ldap_entry,
ber_elem => ldap_ber_elmt);
ldap_cn := NULL;
ldap_title := NULL;
ldap_dep := NULL;
ldap_log := NULL;
ldap_stat := NULL;

attr_index := 1;
<< attributes_loop >>
WHILE ldap_attr_name IS NOT NULL
LOOP

--Получить значение, связанное с атрибутом
ldap_vals := DBMS_LDAP.get_values (ld => ldap_session,
ldapentry => ldap_entry,
attr => ldap_attr_name);

<< values_loop >>
FOR i in ldap_vals.FIRST..ldap_vals.LAST
LOOP

CASE ldap_attr_name
WHEN 'cn' THEN ldap_cn := ldap_vals(i);
WHEN 'title' THEN ldap_title := ldap_vals(i);
WHEN 'department' THEN ldap_dep := ldap_vals(i);
WHEN 'sAMAccountName' THEN ldap_log := ldap_vals(i);
WHEN 'userAccountControl' THEN ldap_stat := ldap_vals(i);
END CASE;

END LOOP values_loop;
--Следующий аттрибут
ldap_attr_name := dbms_ldap.next_attribute(ld => ldap_session,
ldapentry => ldap_entry,
ber_elem => ldap_ber_elmt);

attr_index := attr_index + 1;

END LOOP attributes_loop;

--Освобождаем ber element
IF (ldap_ber_elmt IS NOT NULL) THEN
dbms_ldap.ber_free(ber => ldap_ber_elmt, freebuf => 0);
END IF;

--Следующее значение для аттрибута
ldap_entry := dbms_ldap.next_entry(ld => ldap_session,
msg => ldap_entry);

entry_index := entry_index + 1;

--Если получены данные, то пишем их в таблицу
IF entry_index > 0 THEN
EXECUTE IMMEDIATE 'INSERT INTO USER_AD values(:1, :2, :3, :4, :5, 6:, :7)'
USING ldap_cn, ldap_title, ldap_dep, ldap_log, ldap_stat, ldap_dn_short, ldap_dn;
END IF;

END LOOP entry_loop;

COMMIT;

--Сообщение LDAP
retval := dbms_ldap.msgfree(lm => ldap_message);
DBMS_OUTPUT.PUT_LINE('AD operation successful!');

--Завершение сессии в AD
retval := DBMS_LDAP.unbind_s(ld => ldap_session);
dbms_output.put_line(RPAD('unbind_res Returns ',25,' ') || ': ' ||TO_CHAR(retval));

--Исключение ошибок
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' Error code : ' || TO_CHAR(SQLCODE));
dbms_output.put_line(' Error Message : ' || SQLERRM);
dbms_output.put_line(' Exception encountered .. exiting');

END AD_TO_DB;
/

Теперь можно запустить процедуру и получить пользовательские данные из AD.