Oracle Streams - Replicacion con oracle database

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

SQL> select * from tabla stream;

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