Oracle Streams - Replicación entre dos BBDD
Oracle Streams propaga y administra datos, transacciones y eventos en una fuente de datos ya sea dentro de una base de datos, o de una base de datos a otra.
Bien a continuación les dejo un paso a paso un tanto "extenso" para montar el Oracle Streams:
Requisitos:
En nuestro caso utilizamos 2 computadoras de 4GB de RAM, procesador i3, 500GB
de disco duro.
A continuación se observa un video que muestra la configuración con Oracle Streams de 2 máquinas (origen y destino) , y para más detalle debajo del video se muestran los pasos a seguir textualmente .
Nombres del SID y TNS name:
orclp, ORCLP (Origen)
orcls, ORCLS (Destino)
Debemos configurar ambos parámetros en cada base de datos (ORCLP, ORCLS):
1. Habilitamos el modo ARCHIVELOG en
ambas bases de datos:
DB Origen: orclp
SQL> connect sys/sys as sysdba
Connected.
SQL> shutdown
immediate
SQL> startup mount;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE ARCHIVE LOG
START
2. Creamos un usuario administrador para
el Stream
DB Origen: orclp
SQL> connect sys/sys as sysdba
Connected.
SQL> create user strmadmin identified by oracle;
User created.
SQL> grant connect, resource, dba
to strmadmin;
Grant
succeeded.
SQL> grant execute on dbms_aqadm to
strmadmin;
Grant succeeded.
SQL>grant execute on
dms_capture_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dbms_propagation_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dms_streams_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dms_apply_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dms_flashback to strmadmin;
Grant succeeded.
SQL> begin
dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role,
select any dictionary to strmadmin;
Grant succeeded.
2.1 Ahora realizamos las mismas
operaciones en la base de datos destino: orcls
SQL> conn sys/sys as sysdba
Connected.
SQL> create user
strmadmin identified by oracle;
User created.
SQL> grant connect, resource, dba
to strmadmin;
Grant succeeded.
SQL> grant execute on dbms_aqadm to strmadmin;
Grant succeeded.
SQL>grant execute on
dms_capture_adm to strmadmin;
Grant succeeded.
SQL>grant execute on
dbms_propagation_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dms_streams_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dms_apply_adm to strmadmin;
Grant succeeded.
SQL>grant execute on dms_flashback to strmadmin;
Grant succeeded.
SQL> begin
dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role,
select any dictionary to strmadmin;
Grant succeeded.
3. Ahora deberemos configurar el fichero
INIT.ORA con los siguientes parámetros:
DB Origen: orclp
SQL> conn sys/sys as sysdba
Connected.
SQL> alter system
set global_names=true;
System altered.
SQL> alter system set
streams_pool_size = 100 m;
System altered.
DB destino: orcls
SQL> conn sys/sys as sysdba
Connected.
SQL> alter system
set global_names=true;
System altered.
SQL> alter system set
streams_pool_size = 100 m;
System altered.
4. Ahora crearemos el enlace en cada
base de datos apuntando a la otra:
DB Origen: orclp
SQL> connect strmadmin/oracle@orclp
Connected.
SQL> create
database link ORCLS
2 connect to strmadmin
3 identified by oracle
4 using 'orcls';
Database link created.
DB Destino: orcls
SQL> connect strmadmin/oracle@orcls
Connected.
SQL> create database link ORCLP
2 connect to strmadmin
3 identified by oracle
4 using 'orclp';
Database link created.
5. Configuramos el origen y el destino
de las consultas:
DB origen: orclp
SQL> connect strmadmin/oracle@orclp
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
DB Destino: orcls
SQL> conn strmadmin/oracle@orcls
Connected.
SQL> EXEC
DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Configuramos el
esquema para los streams:
Esquema: SCOTT
Table: tablastream
NOTA: El esquema scott viene bloqueado en las
versiones 10g asi que deberemos desbloquearlos:
DB Origen: orclp
SQL> connect sys/sys as sysdba
Connected.
SQL> alter user
scott account unlock identified by oracle;
User altered.
SQL> connect scott/oracle@orclp
Connected.
SQL> create table tablastream ( no number primary key,name varchar2(20),ddate
date);
Table created.
DB Destino: orcls
SQL> connect sys/sys as sysdba
Connected.
SQL> alter user scott account
unlock identified by oracle;
User altered.
SQL> connect scott/oracle@orcls
Connected.
SQL> create table tablastream ( no number primary key,name varchar2(20),ddate
date);
Table created.
7. Creamos un acceso
suplementario en la base de datos origen:
DB Origen: orclp
SQL> connect scott/oracle@orclp
Connected.
SQL> alter table tablastream
2 add supplemental log data (primary key,unique) columns;
Table altered.
8. Configuramos el
proceso de captura en la base de datos origen:
DB Origen: orclp
SQL> connect strmadmin/oracle@orclp
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.tablastream',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /
PL/SQL procedure successfully completed.
9. Configuramos el
proceso de propagación:
DB Origen: orclp
SQL> connect strmadmin/oracle@orclp
Connected.
SQL> begin
dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.tablastream',
3 streams_name => 'ORCLP_TO_ORCLS',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@ORCLS',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'ORCLP',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
10. Configuramos el
SCN:
DB Origen: orclp
SQL> connect strmadmin/oracle@orclp
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'scott.pibe',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
11. Configuramos el
proceso de aplicacion en la base de datos destino:
DB Destino: orcls
SQL> connect strmadmin/oracle@orcls
Connected.
SQL> begin
dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.tablastream',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'ORCLP',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
12. Iniciamos la
captura y el proceso de aplicación de datos:
DB Origen: orclp
SQL> connect
strmadmin/oracle@orclp
Connected.
SQL> begin
dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
Db Destino: orcls
SQL> connect strmadmin/oracle@orcls
Connected.
SQL> begin
dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
NOTA: El entorno de
replicación ya esta listo!, lo que debemos hacer a continuación es probarlo...
SQL> connect scott/oracle@orclp
Connected.
SQL> --DDL operation
SQL> alter table tablastream add
(flag char(1));
Table altered.
SQL> --DML operation
SQL> begin
2 insert into tablastream values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> conn scott/oracle@orcls
Connected.
SQL> --TEST DDL operation
n desc tablastream
Name Null Type
------------------------------------------------------------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)
SQL> --TEST DML operation
NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 16-06-12 1
Como pueden ver, hemos actualizado la tabla en la orclp y el stream se ha replicado a la orcls.
------------------------------------------------------------------------
Contactos:
David Romeo Flores Rodríguez ------------------------ drflores19@hotmail.es
Juan Pablo Arana Rivera --------------------------------ark.ar.96@facebook.com
Karla Rebeca Mira Huezo ------------------------------- krmh10@gmail.com
Sonia Maribel Campos Castaneda --------------------- smacc11@gmail.com
Edwin Abel Martínez Linares -------------------------- edwinma007@hotmail.com
Leer nota completa...
