Synchronisation base en ligne/base archive

[Shuret]

Bonjour,

Je voudrais savoir s'il est possible de faire la chose suivante :

- avoir une base en ligne sur laquelle des transactions sont faites tout au long de la journée (et la nuit, 7/7-24/24)
- des purges sont faites régulièrement sur cette base et suppriment des éléments plus vieux qu'une certaine date (entre 5 jours et un an)
- avoir une base d'archive dont le contenu est identique à la base en ligne mais sans les purges : les données supprimées dans la base en ligne ne le sont pas dans la base d'archive

Autrement dit : synchroniser les insert/update mais pas les delete. Je ne sais pas s'il existe ce genre d'outil ou s'il faut carrément le faire à la main car j'imagine que l'existant se contente d'une synchro type online/backup, donc à l'identique. Il semble qu'il existe des outils répondant en partie à ce problème sur PostGreSQL et MySQL.

La problématique est de ne pas impacter la base en ligne qui gère des transactions avec un impératif de temps de réponse par des requêtes de statistiques. C'est pourquoi ces stats seraient faites sur la base d'archive, qui d'ailleurs contiendrait des données plus vieille pour faire des stats plus complètes ou retrouver des choses qu'on n'a plus sur la base en ligne. Les stats peuvent être de la simple consultation ou de l'agrégat sur de longues périodes avec beaucoup de données. Elles sont accessibles à plusieurs clients simultanément (plusieurs dizaines ou centaines à terme).

Voilà, je pense avoir été clair, sinon n'hésitez pas à poser vos questions. Et merci pour votre aide !

[Andrei_L]

Bonjour,

vous pouvez utiliser 'Oracle streams' (http://docs.oracle.com/cd/E11882_01/...r.htm#CHDJACGF).
Afin que conserver toutes les lignes dans la table de destination, créez et enregistrez une 'DML Handler' procédure pour 'DELETE' à cette table.

Ne se flatter pas que vous pouvez configurer telle synchronisation une fois et l'oublier depuis. Les programmes de la synchronisation partielle sont fragiles souvent.
Imagines qu'une contrainte soit ajoutée à la table source, et les lignes de la table source satisfassent à la contrainte, mais il y a des lignes vieux de la table de destination que ne satisfassent pas.

Puisque la synchronisation exigera des efforts pour la maintenir, n'est-il opportun de déployer un entrepôt de données étoffé (avec modèle de données en étoile)?
Vous pouvez avoir des requêtes historiques très rapides ainsi.

[Shuret]

Bonjour,

Merci pour votre réponse, je vais lire cette doc dès que j'en aurai le temps
Par contre, est-ce que ce système peut fonctionner entre Oracle et MySQL ? Je vais également me renseigner sur la conception d'un data warehouse, puisque c'est de ça que nous avons besoin.

[Andrei_L]

Bonjour,

Un exemple de la configuration d'Oracle Streams avec 'downstream capture'. L'effort additionnel de la base source sera minimal.
La base OR11203A est une base source, la base OR11203B est une base de destination.
Toute les deux bases sont 'Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit, Linux'.


Un schéma destiné pour réplication:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
sys@OR11203A>CREATE user TEST_SCHEMA IDENTIFIED BY PSW;
Utilisateur créé.
 
sys@OR11203A> GRANT connect, resource TO TEST_SCHEMA;
Autorisation de privilèges (GRANT) acceptée.
 
sqlplus TEST_SCHEMA/PSW@OR11203A
test_schema@OR11203A> CREATE TABLE TEST_TABLE(
    TEST_ID number,
    TEST_VAL number,
    constraint PK_TEST_TABLE PRIMARY KEY(TEST_ID)
)
/
TABLE créée.
 
test_schema@OR11203A>INSERT INTO TEST_TABLE(TEST_ID, TEST_VAL) VALUES(1,2);
1 ligne créée.
 
test_schema@OR11203A> commit;
Validation effectuée.

Vérifions la connectivité:
de la base source à la base de destination:

Code :

1
2
3
 
tnsping OR11203B
OK

de la base de destination à la base source:

Code :

1
2
3
 
tnsping OR11203A
OK

Vérifions les paramètres de l'instance source:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
sys@OR11203A> SHOW parameter global_names
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
global_names                         BOOLEAN     TRUE
 
sys@OR11203A> SHOW parameter compatible
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
compatible                           string      11.2.0.0.0
 
sys@OR11203A> SELECT log_mode FROM v$database;
 
LOG_MODE
------------
ARCHIVELOG

Vérifions les paramètres de l'instance de destination:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
sys@OR11203B> SHOW parameter global_names;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
global_names                         BOOLEAN     TRUE
 
sys@OR11203B> SHOW parameter compatible;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
compatible                           string      11.2.0.0.0
 
sys@OR11203B> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG

Changions des paramètres d'instances:
La base source

Code :

1
2
3
4
5
6
7
8
9
 
sys@OR11203A> ALTER system SET log_archive_config='dg_config=(OR11203A,OR11203B)' scope=BOTH;
Système modifié.
 
sys@OR11203A>ALTER system SET log_archive_dest_2='service=OR11203B async noregister valid_for=(online_logfiles,primary_role) db_unique_name=OR11203B' scope=BOTH;
Système modifié.
 
sys@OR11203A> ALTER system SET log_archive_dest_state_2=enable scope=BOTH;
Système modifié.

La base de destination:

Code :

1
2
3
4
5
6
7
8
9
10
 
sys@OR11203B> ALTER system SET log_archive_config='dg_config=(OR11203A,OR11203B)' scope=BOTH;
Système modifié.
 
sys@OR11203B> ALTER DATABASE ADD standby logfile;
Base de données modifiée.
sys@OR11203B> ALTER DATABASE ADD standby logfile;
Base de données modifiée.
sys@OR11203B> ALTER DATABASE ADD standby logfile;
Base de données modifiée.

Transportons le fichier des mots de passe du server source vers le serveur de destination:

Code :

1
2
3
4
 
rsync /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwOR11203A  desthost://u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Changions le nom du fichier de mots de passe dans la base de destination:
mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwOR11203A /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwOR11203B

Créons les tablespaces

Code :

1
2
3
4
5
6
 
sys@OR11203A>CREATE tablespace STREAMS;
Tablespace créé.
 
sys@OR11203B>CREATE tablespace STREAMS;
Tablespace créé.

Créons les utilisateurs:
dans la base source:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
sys@OR11203A> CREATE user strmadmin IDENTIFIED BY PSW DEFAULT tablespace STREAMS;
Utilisateur créé.
 
sys@OR11203A> GRANT dba TO strmadmin;
Autorisation de privilèges (GRANT) acceptée.
 
 
sys@OR11203A>begin
    dbms_streams_auth.grant_admin_privilege(
        grantee=>'strmadmin',
        grant_privileges=>true);
end;
/
 
Procédure PL/SQL terminée avec succès.
 
sys@OR11203A>begin
   dbms_streams_auth.grant_remote_admin_access(grantee=>'strmadmin');
end;
/
Procédure PL/SQL terminée avec succès.

dans la base de destination

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
sys@OR11203B>CREATE user STRMADMIN IDENTIFIED BY PSW DEFAULT tablespace STREAMS;
 
Utilisateur créé.
 
sys@OR11203B>GRANT dba TO strmadmin;
Autorisation de privilèges (GRANT) acceptée.
 
 
 
sys@OR11203B>begin
    dbms_streams_auth.grant_admin_privilege(
        grantee=>'strmadmin',
        grant_privileges=>true);
end;
/
 
Procédure PL/SQL terminée avec succès.

Créons des liens:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
sqlplus STRMADMIN/PSW@OR11203A
CREATE DATABASE link OR11203B.LOCAL
connect TO STRMADMIN
IDENTIFIED BY PSW
USING 'OR11203B'
/
 
Lien de base de données créé.
 
sqlplus STRMADMIN/PSW@OR11203B
 
CREATE DATABASE link OR11203A.LOCAL
connect TO STRMADMIN
IDENTIFIED BY PSW
USING 'OR11203A'
/
 
Lien de base de données créé.

Créons une réplication:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
strmadmin@OR11203B>begin
    dbms_streams_adm.maintain_schemas(
        schema_names => 'TEST_SCHEMA',
        source_directory_object => NULL,
        destination_directory_object => NULL,
        source_database => 'OR11203A.LOCAL',
        destination_database => 'OR11203B.LOCAL',
        capture_name => 'CAPTURE',
        capture_queue_table => 'STREAMS_QUEUE_QT',
        capture_queue_name => 'STREAMS_QUEUE',
        apply_name => 'APPLY',
        apply_queue_table => 'STREAMS_QUEUE_QT',
        apply_queue_name => 'STREAMS_QUEUE',
        include_ddl=>true,
        instantiation=>dbms_streams_adm.instantiation_schema_network
        );
end;
/
 
job finished
Procédure PL/SQL terminée avec succès.
 
strmadmin@OR11203B>begin
    dbms_capture_adm.set_parameter(
        capture_name => 'CAPTURE',
        parameter => 'downstream_real_time_mine',
        value => 'Y');
end;
/
 
Procédure PL/SQL terminée avec succès.
 
 
sys@OR11203A> ALTER system archive log current;
Système modifié.

Examinons les résultats:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
strmadmin@OR11203B> strmadmin@OR11203B> SELECT * FROM TEST_SCHEMA.TEST_TABLE;
 
   TEST_ID   TEST_VAL
---------- ----------
         1          2
 
 
test_schema@OR11203A>INSERT INTO TEST_TABLE(TEST_ID, TEST_VAL) VALUES(3,4);
1 ligne créée.
 
test_schema@OR11203A> commit;
Validation effectuée.

Attendons quelques minutes jusqu'à des objets de Log Miner soient créés (Des messages "LOGMINER: Begin mining logfile" apparaissent dans le fichier d'alerte ).

Code :

1
2
3
4
5
6
7
 
test_schema@OR11203B> SELECT * FROM TEST_TABLE;
 
   TEST_ID   TEST_VAL
---------- ----------
         1          2
         3          4

Examinons la suppression:

Code :

1
2
3
4
5
6
7
8
9
10
11
 
test_schema@OR11203A> DELETE FROM TEST_TABLE WHERE TEST_ID=1;
1 ligne supprimée.
 
test_schema@OR11203A> commit;
Validation effectuée.
 
test_schema@OR11203B> SELECT * FROM TEST_TABLE;
   TEST_ID   TEST_VAL
---------- ----------
         3          4

La synchronisation fonctionne. Excluons la suppression:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
strmadmin@OR11203B>begin
    dbms_capture_adm.stop_capture(capture_name=>'CAPTURE');
end;
/
Procédure PL/SQL terminée avec succès.
 
strmadmin@OR11203B>SET serveroutput ON
declare
    V_DML_RULE_NAME varchar2(30);
    V_DDL_RULE_NAME varchar2(30);
begin
    dbms_streams_adm.add_schema_rules(
        schema_name=>'TEST_SCHEMA',
        streams_type=>'capture',
        streams_name=>'CAPTURE',
        queue_name=>'STREAMS_QUEUE',
        include_dml=>true,
        include_ddl=>false,
        include_tagged_lcr=>false,
        source_database=>'OR11203A.LOCAL',
        dml_rule_name=>V_DML_RULE_NAME,
        ddl_rule_name=>V_DDL_RULE_NAME,
        inclusion_rule=>false,
        and_condition=>':lcr.get_command_type()=''DELETE''');
    dbms_output.put_line('dml_rule_name='||V_DML_RULE_NAME);
end;
/
dml_rule_name="STRMADMIN"."TEST_SCHEMA31"
Procédure PL/SQL terminée avec succès.
 
strmadmin@OR11203B>begin
    dbms_capture_adm.start_capture(capture_name=>'CAPTURE');
end;
/
Procédure PL/SQL terminée avec succès.

Examinons l'exclusion de la suppression:

Code :

1
2
3
4
5
6
7
8
9
10
11
 
test_schema@OR11203A> SELECT * FROM TEST_TABLE;
   TEST_ID   TEST_VAL
---------- ----------
         3          4
 
test_schema@OR11203B> SELECT * FROM TEST_TABLE;
 
   TEST_ID   TEST_VAL
---------- ----------
         3          4

Supprimons une ligne de la table source:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
test_schema@OR11203A> DELETE FROM TEST_TABLE WHERE TEST_ID=3;
1 ligne supprimée.
 
test_schema@OR11203A> commit;
Validation effectuée.
 
test_schema@OR11203A>  SELECT * FROM TEST_TABLE;
aucune ligne sélectionnée
 
test_schema@OR11203B>  SELECT * FROM TEST_TABLE;
   TEST_ID   TEST_VAL
---------- ----------
         3          4

La insertion fonctionne toujours:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
test_schema@OR11203A> INSERT INTO TEST_TABLE(TEST_ID, TEST_VAL) VALUES(5,6);
 
1 ligne créée.
 
test_schema@OR11203A> commit;
Validation effectuée.
 
test_schema@OR11203A> SELECT * FROM TEST_TABLE;
 
   TEST_ID   TEST_VAL
---------- ----------
         5          6
 
 
test_schema@OR11203B> SELECT * FROM TEST_TABLE;
 
   TEST_ID   TEST_VAL
---------- ----------
         3          4
         5          6

Et non, les Streams de la version 11.2 du SGBD Oracle ne fonctionnent pas avec MySQL, quoique fonctionnent avec certains autres SGDB (Sybase,Informix,SQL Server,DRDA)
à travers "Oracle Database Gateway".

[Shuret]

Waouh ! Merci beaucoup pour ce post très détaillé ! Dès que j'aurai le temps, j'essaie de mettre ça en place sur des bases de test.