Introduction à Oracle Streams

Oracle Streams pas de Commentaire »

Oracle Streams est une fonctionnalité standard d’Oracle (depuis la version 9i) qui permet de répliquer des données, d’une base source vers une ou plusieurs bases destination.

Et ce avec différents niveau de granularité :

  • réplication de table(s)
  • de schema(s)
  • de base complète

sachant que chaque niveau peut lui même être affiné en le filtrant par des règles…

Comment fonctionne Oracle Streams

Oracle Streams s’appuie sur 3 processus distincts :

  • la capture : sur la db source, intercepte toutes les modifications faites sur les données (via les redo logs) et les transforme en enregistrements logiques (Logical Change Record ou LCRs). CEs LCRs sont empilés dans une queue de messages,
  • la propagation : entre db source et destination, transporte ces LCRs et les empile dans une queue de messages
  • l’application (apply process) : sur la db destination, qui dépile les messages, les transforme et répercute les modifications sur les données cibles.

streams_capture

détail d’une capture

streams_propagation

détail de la propagation

Techniquement, la configuration et la mise en oeuvre d’Oracle Streams se fait avec un nombre restreint de procédures du package PL/SQL standards DBMS_STREAMS_ADM. Ce package permet :

  • la gestion des queues de messages (procédure DBMS_STREAMS_ADM.SETUP_QUEUES, DBMS_STREAMS_ADM.REMOVE_QUEUES
  • la gestion des règles de filtrage (procédures DBMS_STREAMS_ADM.ADD_%_RULES)
  • (procedures DBMS_STREAMS_ADM.MAINTAIN_GLOBAL, DBMS_STREAMS_ADM.MAINTAIN_SCHEMA, DBMS_STREAMS_ADM.MAINTAIN_TABLES,…)

Quelques utilisations d’Oracle Streams

Oracle streams peut être utilisé notamment pour les sctions suivantes :

  • réplication automatique de données
  • chargement d’infocentre / entrepot de données
  • sauvegarde de données
  • audit
  • gestion de piles de messages

Surveillance d’Oracle STreams

On peut également, administrer une partie de Streams avec la GRID Console

Les principales vues du dictionnaire de données concernant Streams sont les suivantes :

  • DBA_APPLY, DBA_APPLY_%
  • DBA_CAPTURE, DBA_CAPTURE_%
  • DBA_HIST_STREAMS_%
  • DBA_PROPAGATION
  • DBA_RULE_%
  • DBA_STREAMS_%

ainsi que les vues dynamiques suivantes :

V$BUFFERED_%, V$PROPAGATION_SENDER, V$PROPAGATION_RECEIVER, V$RULE_%, V$STREAMS_%

Réplication de schéma automatique avec Oracle Streams

Oracle Streams 4 Commentaires »

Cet article vous propose une mise en oeuvre pas à pas, d’une réplication automatique de schéma, entre 2 bases de données Oracle.

Une fois les process mis en place, toute modification sur le schema source (update, delete, insert) et même DDL (create, alter , drop,…) sera répercutée automatiquement et en temps réel (si on a COMMITé bien sûr ;-) , sur le schéma répliqué.

Pour plus d’infos voir mon autre article sur ce qu’est Oracle STREAMS et comment marche Oracle STREAMS

Cet exemple a été testé sur une 10gR2.

Pour quoi ca va marcher :

  • db1 et db2 sont en 10gR2
  • db1 est en ARCHIVELOG
  • il existe un user d’administration STREAMS (strmadmin) sur db1 ET db2
  • on veut répliquer toutes les tables du schéma SCOTT2 de db1, de manière synchrone
  • le schéma SCOTT2 existe avec droits de création d’objets et quotas sur db2
  • DB1 est accessible à partir de db2 via un database link

Mise en oeuvre pas à pas

1) verif archivelog mode sur source

SQL> select name, log_mode
from v$database;

2) verifier positionner les paramètres minimaux de INSTANCE

job_queue_processes >=2
sga_target=0 (sinon streams_pool_size >=200M)
open_links>=2
logmnr_max_persistent_sessions >=1
parallel_max_servers >=2

on pourra utiliser pour ce faire la commande :

SQL> ALTER SYSTEM SET nom_parametre = valeur SCOPE=BOTH;

3) créer un user d’administration de stream avec son TBS sur la source et la destination

–creation TBS
SQL>CREATE TABLESPACE streams_dat
DATAFILE ‘/work/devun/streams_dat1.dbf’ SIZE 25M
AUTOEXTEND ON MAXSIZE 256M;
– creation administrateur
SQL>CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_dat
QUOTA UNLIMITED ON streams_dat;
– cession des privileges adequats
SQL>grant dba to strmadmin;
– et en tant que SYS
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => ’strmadmin’,
grant_privileges => true);
END;
/

– idem sur destination

4) créer les queues nécessaire (sur S et D)

– en tant que strmadmin !!!
– creation des tables de queue
– sur source
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ’strmadmin.streams_queue_table_S’,
queue_name => ’strmadmin.streams_queue_S’);
END;
/

– sur destination
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ’strmadmin.streams_queue_table_D’,
queue_name => ’strmadmin.streams_queue_D’);
END;
/

check :
SQL> select OWNER , NAME ,QUEUE_TABLE ,QID
from DBA_QUEUES where OWNER = ‘STRMADMIN’

5) créer LES db link nécessaires

– de la source vers la destination…
– pour la propagation (je suppose)

SQL>CONNECT strmadmin/strmadmin@DEVUN
SQL>CREATE DATABASE LINK STREAMS_DEST
CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING ‘PPRUN’;

– de la destination vers la source
– ca c’est par qu’on utilise DBMS_DATAPUMP
– qui importe la source a travers un dblink
– pour initialiser le compte
SQL>CREATE DATABASE LINK DEVUN
CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING ‘DEVUN’;

6) create schema cible ’scott2′ VIDE sur db2, s’il n’existe pas

SQL> CREATE USER SCOTT2
identified by tiger2;
SQL> alter user scott2 default tablespace USERS;
SQL> alter user scott2 quota unlimited on users;
SQL> grant create session, resource to scott2;

7) simulation à blanc (ceux qui sont optimistes peuvent sauter cette étape…)

– il est possible de faire un test à blanc qui généer un script SQL
- et de verifier si la configuration est correcte
– on a a lors besoin de creer des DIRECTORY et de spécifier des emplacements pour le script
– parametres : source_directory_object, destination_directory_object,
– script_directory_object,script_name

BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names=>’scott2′,
source_directory_object=>’STREAMS_SRC_DIR’,
destination_directory_object=>’STREAMS_DEST_DIR’,
script_directory_object=>’STREAMS_SRC_DIR’,
script_name=>’script_streams.sql’,
source_database=>’DEVUN’,
destination_database=>’STREAMS_DEST’,
capture_name=>’capture_scott2′,
capture_queue_table=>’strmadmin.streams_queue_table_S’,
capture_queue_name=>’strmadmin.streams_queue_S’,
capture_queue_user=>null,
apply_name=>’apply_scott2′,
apply_queue_table=>’strmadmin.streams_queue_table_D’,
apply_queue_name=>’strmadmin.streams_queue_D’,
apply_queue_user=>null,
propagation_name=>’prop_scott2′,
bi_directional=>false,
include_ddl=>true,
instantiation=>dbms_streams_adm.instantiation_schema_network,
perform_actions=>false
);
END;

8) ACTIVATION effective de la réplication

– source, dest directories, script_directory_object et script_name inutiles et perform_action a TRUE
– l’initialisation du compte se fait par DBMS_DATAPUMP
– (grace au parametre : instantiation=>dbms_streams_adm.instantiation_schema_network)

BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names=>’scott2′,
source_directory_object=>NULL,
destination_directory_object=>NULL,
source_database=>’DEVUN’,
destination_database=>’STREAMS_DEST’,
capture_name=>’capture_scott2′,
capture_queue_table=>’strmadmin.streams_queue_table_S’,
capture_queue_name=>’strmadmin.streams_queue_S’,
capture_queue_user=>null,
apply_name=>’apply_scott2′,
apply_queue_table=>’strmadmin.streams_queue_table_D’,
apply_queue_name=>’strmadmin.streams_queue_D’,
apply_queue_user=>null,
propagation_name=>’prop_scott2′,
bi_directional=>false,
include_ddl=>true,
instantiation=>dbms_streams_adm.instantiation_schema_network,
perform_actions=>true
);
END;

Le miracle

SQL> select count(*) from scott2.dept;
5
SQL> select count(*) from scott2.dept@DB2;
5
SQL> insert into scott2.dept
values (60, ‘DRH’, ‘Soisy”)
1 row created
SQL> select count(*) from scott2.dept;
6
SQL> select count(*) from scott2.dept@DB2;
5
SQL> commit;
SQL> select count(*) from scott2.dept@DB2;
6
– CQFD !

TroubleShooting

L’erreur étant humaine et malheureusement assez fréquemment présente, voici en vrac quelques commandes, pour vérifier ou ca coince … si par le plus grand des hasards…ca coince

—————————–
check des differents process
—————————–
sur la db source

statut de la capture :
SQL>SELECT * FROM DBA_CAPTURE;

statut de la propagation :
SQL>SELECT * FROM DBA_PROPAGATION;

sur la db dest :
statut apply :
SQL>select * from dba_apply
ou
SQL>select apply_name, queue_name,
status, to_char(STATUS_CHANGE_TIME,’DD/MM HH24:MI’) time,
error_number, error_message
from DBA_apply

si aborted
SQL>SELECT REASON, SUGGESTED_ACTION
FROM DBA_ALERT_HISTORY
WHERE MODULE_ID LIKE ‘%STREAMS%’;

!!! atenntion il se peut que le APPLY soit ENABLE tant qu’on a pas fait de maj sur la source
et aborte ensuite !

——————–
RECUPERATION ERREUR
——————-
begin
*
ERROR at line 1:
ORA-23616: Failure in executing block 33 for script 6EE472512816A0A0E043C0A80B47A0A0

a t’on de l’info sur le script qui s’est planté ?
SQL> SELECT * FROM dba_recoverable_script

– on recupere l’erreur exacte
SQL> SELECT * FROM DBA_RECOVERABLE_SCRIPT_ERRORS;

6EE472512816A0A0E043C0A80B47A0A0
33
-19504
ORA-19504: failed to create file “/tmp/test/export_scott.dmp”
ORA-27038: created file already exists
Additional information: 1
ORA-02063: preceding 3 lines from CIBLE_STRM_DEVUN
17/07/2009

———————————————————–
check JOBS et QUEUES
———————————————————–
SQL>select * from dba_jobs
where WHAT like ‘%dbms_aqadm.aq$_propaq%’

ou
SQL>select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,
THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME
from dba_jobs
where WHAT like ‘%dbms_aqadm.aq$_propaq%’

JOB LOGUSR PRVUSER SCHUSER THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME
18058 SYS SYS SYS 21-JUL-09 11:13:32 21-JUL-09 11:13:31 98
18059 SYS SYS SYS 21-JUL-09 11:13:42 21-JUL-09 11:13:41 88
18060 SYS SYS SYS 21-JUL-09 11:13:53 21-JUL-09 11:13:51 77
18061 SYS SYS SYS 21-JUL-09 11:14:03 21-JUL-09 11:14:01 67
18062 SYS SYS SYS 21-JUL-09 11:14:13 21-JUL-09 11:14:11 57

SQL>select NAME, OWNER ,QUEUE_TABLE, QID,QUEUE_TYPE, MAX_RETRIES
from dba_queues
where owner= ‘STRMADMIN’

NAME OWNER QUEUE_TABLE QID QUEUE_TYPE MAX_RETRIES
AQ$_STREAMS_QUEUE_TABLE_E STRMADMIN STREAMS_QUEUE_TABLE 1100193 EXCEPTION_QUEUE 0
STREAMS_QUEUE STRMADMIN STREAMS_QUEUE_TABLE 1100194 NORMAL_QUEUE 5
AQ$_STREAMS_QUEUE_TABLE1_E STRMADMIN STREAMS_QUEUE_TABLE1 1100224 EXCEPTION_QUEUE 0
STREAMS_QUEUE1 STRMADMIN STREAMS_QUEUE_TABLE1 1100225 NORMAL_QUEUE 5
AQ$_STREAMS_QUEUE_TABLE2_E STRMADMIN STREAMS_QUEUE_TABLE2 1101288 EXCEPTION_QUEUE 0
STREAMS_QUEUE2 STRMADMIN STREAMS_QUEUE_TABLE2 1101289 NORMAL_QUEUE 5

————–
check activité
————–
les messages empilés et dépilés :

SQL>select * from v$streams_apply_reader;
select * from v$propagation_sender
+ l’utilitaire ‘STRMMON’

—————————
suppression de la queue
—————————
on recupere le nom de la queue dans DBA_QUEUES et on utilise DBMS_STREAMS_ADM.REMOVE_QUEUE

BEGIN
DBMS_STREAMS_ADM.REMOVE_QUEUE(
queue_name => ’strmadmin.STREAMS_QUEUE1′,
cascade => true,
drop_unused_queue_table => true);
END;
/

—————————
gestion apply process
———————–
SI erreur ERROR at line 1:
ORA-26668: STREAMS process APPLY_SCOTT exists

SQL>select * from dba_apply;

BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => ‘APPLY_SCOTT’);
END;
/

et on peut faire apres un DROP :
begin
dbms_apply_adm.drop_apply(’APPLY_SCOTT’);
end;

————————
nettoyage/iunit apres erreur
————————–
– on recupere le script ID
SQL> SELECT SCRIPT_ID, INVOKING_PACKAGE, INVOKING_PROCEDURE,
TO_CHAR(CREATION_TIME,’DD/MM HH:MI’)
from DBA_RECOVERABLE_SCRIPT ;

SCRIPT_ID INVOKING_PACKAGE INVOKING_PROCEDURE TO_CHAR(CRE
——————————– —————- —————— ———–
6ED114012A586054E043C0A80B476054 DBMS_STREAMS_ADM MAINTAIN_SCHEMAS 16/07 12:24

begin
exec dbms_streams_adm.recover_operation(
script_id=>’6F3198DAFDBF90CAE043C0A80B4790CA ‘,
operation_mode=>’PURGE’
);
end;

–> vide dba_recoverable_script et …_errors

begin
exec dbms_streams_adm.recover_operation(
script_id=>’6F3198DAFDBF90CAE043C0A80B4790CA ‘,
operation_mode=>’ROLLBACK’
);
end;