Oracle Streams

Qualche giorno fa, incuriosito dalla lettura di un post di Chen Shapira, ho iniziato a studiare e a fare qualche prova con Oracle Streams, una tecnologia che permette la propagazione e la gestione di dati, transazioni ed eventi nell'ambito di un database, oppure tra un database sorgente ed uno di destinazione.

In sostanza, Oracle Streams consente di replicare uno schema presente su un db sorgente in un db di destinazione, inoltre, ogni istruzione DDL, oppure DML eseguita nell'ambito dello schema da replicare presente sul db sorgente verrà eseguita anche sullo schema replicato presente nel db di destinazione.
Su databases OLTP, il processo di estrazione dei dati non può essere eseguito direttamente poichè interferirebbe con le normali istruzioni DDL/DML, perciò l'architettura Oracle Streams utilizza gli archivelog, decisamente mento intrusivi.
Prima di poter utilizzare uno stream Oracle, è necessario assicurarsi che siano soddisfatti alcuni prerequisiti:

1) configurare opportunamente i due db server in modo tale che sia possibile eseguire tnsping dal db server sorgente a quello di destinazione e viceversa
2) porre in ARCHIVELOG MODE entrambi i database
3) creare un utente per la gestione dello stream Oracle ad entrambi i database
4) creare un db link per la creazione iniziale, da eseguire solo la prima volta, dello schema replica sul db di destinazione mediante le utilities di data pump
5) creare un db link dal db di destinazione a quello sorgente, utile nel caso in cui la creazione iniziale dello schema replica avvenga a livello di rete senza servirsi di un file dmp intermedio (io ho creato anche questo db link in ogni caso)
6) configurazione delle utilities expdp ed impdp sia sul database sorgente sia su qyello di destinazione
7) creare una directory Oracle e una corrispondente directory sul file system del db server sorgente per ospitare lo script generato dalla procedura DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS che si occupa della replica dello schema via stream Oracle.

Supponendo, per comodità che i punti 1) e 2) siano già soddisfatti, parto direttamente dal punto 3). D'ora in poi, la sigla "PROD" identifica il database sorgente e la sigla "TEST" identifica quello di destinazione. Lo schema da replicare sarà l'onnipresente "SCOTT"!

1) OK
2) OK
3) creiamo un utente dedicato alla gestione dello stream, sia su PROD sia su TEST, connettendoci come utente SYS as SYSDBA:

CREATE TABLESPACE streams_tbs DATAFILE '/u01/app/oracle/oradata/streams_tbs.dbf' SIZE 25M;

CREATE USER strmadmin
IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;

4) impostiamo un db link da PROD a TEST per l'importazione iniziale dello schema da replicare:

-- su PROD
connect strmadmin/strmadminpw@PROD

CREATE DATABASE LINK TEST
CONNECT TO STRMADMIN
IDENTIFIED BY STRMADMINPW
USING 'TEST';

5) ora creiamo anche un db link da TEST a PROD:

-- su TEST
connect strmadmin/strmadminpw@TEST

CREATE DATABASE LINK PROD
CONNECT TO STRMADMIN
IDENTIFIED BY STRMADMINPW
USING 'PROD';

6) configuriamo le utilities di data pump sui due db server mediante la creazione di una directory Oracle chiamata "source" sul db server che ospita PROD e di una directory Oracle chiamata "dest" sul db server che ospita TEST. Le due directories a livello di sistema operativo vanno create con gli appositi comandi del sistema operativo in uso (mkdir, per GNU/Linux):

-- su PROD
conn sys@PROD as sysdba
CREATE OR REPLACE DIRECTORY source AS '/u01/app/oracle/oradata/source';
GRANT READ, WRITE ON DIRECTORY source TO strmadmin;

-- su TEST
conn sys@TEST as sysdba
CREATE OR REPLACE DIRECTORY dest AS '/u01/app/oracle/oradata/dest';
GRANT READ, WRITE ON DIRECTORY dest TO strmadmin;

7) creiamo una directory Oracle e una corrispondente directory sul file system del db server che ospita il database PROD. Questa directory conterrà lo script da eseguire per la replica dello schema SCOTT da PROD a TEST via stream Oracle:

CONNECT strmadmin/strmadminpw@PROD
CREATE OR REPLACE DIRECTORY SCRIPT_DIR AS '/home/oracle/script_dir';

Una volta soddisfatti tutti i requisiti, sul db server che ospita PROD mi sono creato uno script da lanciare come utente strmadmin con il seguente contenuto:

DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'scott',
source_database => 'prod',
destination_database => 'test',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => null,
propagation_name => 'prop_scott',
log_file => 'exp_scott.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema,
perform_actions => false,
script_name => 'schema_replication.sql',
script_directory_object => 'script_dir'
);
END;

Direi che è d'obbligo una spiegazione dei parametri fondamentali impostati, altrimenti non si capisce molto:

* schema_names: il nome dello schema (o degli schemi, separati da virgole) da propagare (in questo caso scott)
* source_database: il database sorgente
* destination_database: il database di destinazione
* capture_name: il nome del processo di cattura configurato per acquisire le modifiche effettuate su PROD
* capture_queue_table: il nome della tabella di accodamento per ogni coda utilizzata da un processo di cattura
* capture_queue_name: il nome di ogni coda utilizzata da un processo di cattura
* capture_queue_user: va impostato a null per indicare che la procedura non può dare il grant di alcun privilegio
* propagation_name: il nome di ciascuna propagazione configurata per propagare le modifiche
* log_file: il nome del file di log generato da expdp
* bi_directional: se impostato a 'false' indica che la propagazione dele informazioni è monodirezionale dal db di produzione a quello di test
* include_dll: impostato a 'true' indica che sia le istruzioni DDL sia quelle DML vanno replicate dal db sorgente a quello di destinazione
* instantiation: se impostato a DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA indica che l'istanziazione dello schema di destinazione verrà effettuata utilizzando le utilities expdp/impdp
* perform_action: se impostato a 'true' consente l'esecuzione dello script PL/SQL, se impostato a 'false' ne memorizza il risultato in un ulteriore script che verrà posizionato nella directory indicata dal parametro 'script_directory_object'
* script_name: impostato con la stringa 'schema_replication.sql'. Questo script verrà creato nella directory Oracle 'script_dir' (/home/oracle/script_dir) e conterrà tutti i passaggi per istanziare lo schema di replica
* script_directory_object: la directory Oracle in cui memorizzare lo script 'schema_replication.sql'

Lanciando lo script non viene avviato lo stream Oracle, quindi nemmeno il processo di replica dell'utente SCOTT sul db TEST, ma viene creato lo script schema_replication.sql nella directory /home/oracle/script_dir che andrà eseguito a parte sempre come utente strmadmin@PROD.
La creazione dello script intermedio consente di verificare se non vi sono stati errori durante l'esecuzione della procedura DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA; in alternativa, è sempre possibile saltare la creazione dello script intermedio e lanciare direttamente la procedura DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA con questi parametri:

DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'scott',
source_database => 'prod',
destination_database => 'test',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => null,
propagation_name => 'prop_scott',
log_file => 'exp_scott.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema,
perform_actions => true
);
END;

Da notare che la direttiva "perform_actions" ora è impostata a true e non sono presenti le direttive "script_name" e "script_directory_object".
Se tutto funziona correttamente, sul db TEST sarà presente una replica esatta dello schema SCOTT del db PROD; ora non resta che testare se una qualsiasi istruzione DDL/DML eseguita sullo schema SCOTT@PROD viene replicata anche su SCOTT@TEST.
Buona replica!!!

Ah, lascio qualche utile link per approfondire l'argomento:
* http://www.dba-oracle.com/t_streams_schema_replication.htm
* http://ca.geocities.com/mosicr@rogers.com/OracleStreams101.htm
* http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_strm_a.htm#ARPLS305