miércoles, 20 de junio de 2012

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.


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 


No hay comentarios: