Réplication de schéma automatique avec Oracle Streams

Oracle Streams Ajouter un commentaire

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;

4 Responses to “Réplication de schéma automatique avec Oracle Streams”

  1. Oracle Streams « Cours DBA Oracle Says:

    [...] http://blogorak.estsurinternet.com/oracle-streams-replication-oracle/replication-de-schema-automatiq... Chapitre: Chapitre H : La réplication [...]

  2. chris8609 Says:

    Bonjour, merci pour cette article ou vous dites clairement ou faire les étapes.
    je vais vous exposer mon “problème”: je souhaite synchronisé les données du base de prod vers une base de dév. la base prod est sous oracle 11g SE et la base de dev sous oracle 10g. Après des recherches, je vois que je peux utiliser streams avec la capture synchro étant sous une SE. Mais la je ne trouve aucun exemple pour mettre ceci en place avec deux bases distantes. j’ai fait un test en étant sur la même base (demo.t1 vers copy.t1) cela fonctionne. Donc j’ai repris le même script en utilisant les dblink mais ça ne fonctionne pas. Donc je viens vers vous pour savoir si vous pourriez m’aider.

    merci d’avance.
    Chris.

  3. hicham Says:

    merci pour ce tutoriel que je trouve très important;

    mais j’aimerai bien que vous spéciéfier les taches a faire pour chaque destination. par ex il n’ai pas claire si on va mettre la base de destination en mode archivelog ou non

    merci

  4. Jean-Louis Says:

    Bonjour,

    j’ai suivi ton tuto sa marche impeccable, j’ai donc fait un test de coupure réseaux. Je m’explique :
    je débranche le cable reliant mes 2 bases, j’insere une ligne dans la base source et au COMMIT il me met des erreurs (ORA 02050) en faite il me dit que la base destination n’est pas connecté.

    Y’a t-il un moyen de continuer de faire de commit sur la base source et au moment de la reconnexion mettre à jour la base destination ? Si oui comment faire ?

    Merci pour ton tuto

    Cordialement

Faire un commentaire