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;
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;
