Oracle Streams 11Gr2

Setup Oracle Streams_SID_22598

Oracle 11gr2:

crear usuarios :

CHOLGUACO (MAPACHE) y ATACAMA71(GINETA)

####################
Step 1:
####################

Create Users & Setup Privileges

CREATE USER SADM IDENTIFIED BY SADM;
GRANT CONNECT, RESOURCE, DBA TO SADM;
GRANT SELECT_CATALOG_ROLE TO SADM;
GRANT UNLIMITED TABLESPACE TO SADM;
EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'SADM');



####################
Step 2:
####################

Create database links at source and target databases

mapache =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 10.20.1.31)
          (Port = 1538)
        )
    )
    (CONNECT_DATA = (SID = mapache)
    )
  )


gineta =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = tcp)
        (HOST = atacama) (PORT = 1545))
        (ADDRESS = (PROTOCOL = tcp) ))
        (CONNECT_DATA = (SID = gineta)))


connect SADM/SADM@mapache
CREATE DATABASE LINK TST2 CONNECT TO SADM IDENTIFIED BY SADM  USING 'TST2';

connect SADM/SADM@gineta
CREATE DATABASE LINK TST1  CONNECT TO SADM IDENTIFIED BY SADM  USING 'TST1';



####################
Step 3:
####################

Create the queue at TST1 - Source Database

connect SADM/SADM@mapache
BEGIN
        DBMS_STREAMS_ADM.SET_UP_QUEUE (
        QUEUE_TABLE  => 'STRTEST',
        QUEUE_NAME   => 'C1_STREAM_Q1',
        QUEUE_USER   => 'SADM');
END;
/


Create the queue at TST2 (Target) ##
connect SADM/SADM@gineta
BEGIN
        DBMS_STREAMS_ADM.SET_UP_QUEUE (
        QUEUE_TABLE  => 'A1_STREAM_Q1_QT',
        QUEUE_NAME   => 'A1_STREAM_Q1',
        QUEUE_USER   => 'SADM');
END;
/

####################
Step 4:
####################

Create capture at source


Connect to TST1...
connect SADM/SADM@mapache
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name         =>'JBOSS',
   streams_type        =>'CAPTURE',
   streams_name        =>'C1_STREAM',
   queue_name          =>'SADM.C1_STREAM_Q1',
   include_dml         =>TRUE,
   include_ddl         =>TRUE,
   source_database     =>'mapache');
END;
/


select *
  from ALL_OBJECTS
 where OBJECT_NAME = 'STRTEST';

####################
Step 5:
####################

Create apply process at target


connect SADM/SADM@gineta
BEGIN
        DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
        SCHEMA_NAME             => 'JBOSS',
        STREAMS_TYPE            => 'APPLY',
        STREAMS_NAME            => 'A1_STREAM',
        QUEUE_NAME              => 'SADM.A1_STREAM_Q1',
        INCLUDE_DML             => TRUE,
        INCLUDE_DDL             => TRUE,
        SOURCE_DATABASE         => 'mapache');
END;
/


BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => 'A1_STREAM',
    parameter  => 'disable_on_error',
    value      => 'n');
END;
/

####################
Step 6:
####################

Create propagation at source


connect SADM/SADM@mapache
BEGIN
        DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
        SCHEMA_NAME             => 'JBOSS',
        STREAMS_NAME            => 'P1_STREAM',
        SOURCE_QUEUE_NAME       => 'SADM.C1_STREAM_Q1',
        DESTINATION_QUEUE_NAME  => 'SADM.A1_STREAM_Q1@gineta',
        INCLUDE_DML             =>  TRUE,
        INCLUDE_DDL             =>  TRUE);
END;
/

####################
Step 7:
####################

Instantiation at GINETA

At source MAPACHE...

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
expdp SADM/SADM@TST1 SCHEMAS=EDW_APP_OWNER DIRECTORY=expadmin DUMPFILE=EDW_APP_OWNER.dmp logfile=EDW_APP_OWNER.log PARALLEL=4 FLASHBACK_SCN=<scn>


At Target GINETA...

impdp SADM/SADM@TST2 SCHEMAS=EDW_APP_OWNER DIRECTORY=expadmin DUMPFILE=EDW_APP_OWNER.dmp logfile=EDW_APP_OWNER.log PARALLEL=4

Check if schema instantiation is working fine..

select * from DBA_APPLY_INSTANTIATED_SCHEMAS;

connect SADM/SADM@gineta
declare
   v_scn number;
begin
    v_scn := 943015;
    dbms_output.put_line('Scn : ' || v_scn);
    dbms_apply_adm.set_schema_instantiation_scn(
                    source_schema_name => 'JBOSS',
                    source_database_name => 'mapache',
                    instantiation_scn => v_scn,
                    recursive => true);
end;
/

connect SADM/SADM@gineta
declare
source_scn number;
 begin
 source_scn := dbms_flashback.get_system_change_number();
 dbms_apply_adm.set_table_instantiation_scn@gineta
 ( source_object_name => 'jboss.STRTEST',
 source_database_name => 'mapache',
 instantiation_scn => source_scn);
 end;
 /


####################
Step 8:
####################

At target start apply

connect SADM/SADM@gineta
exec dbms_apply_adm.start_apply('A1_STREAM');

####################
Step 9:
####################

At source start capture

connect SADM/SADM@mapache
exec DBMS_CAPTURE_ADM.START_CAPTURE('C1_STREAM');




########################
STOP ORACLE STREAMS
########################


GINETA:


STEP 1. STOP THE APPLY PROCESS:

BEGIN
  DBMS_APPLY_ADM.STOP_APPLY(
    apply_name => 'A1_STREAM');
END;
/


STEP 2. STOP THE CAPTURE PROCESS

BEGIN
  DBMS_CAPTURE_ADM.STOP_CAPTURE(
    capture_name => 'C1_STREAM');
END;
/



MAPACHE:

STEP 3. STOP THE PROPAGATION PROCESS:

BEGIN
  DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
    propagation_name => 'P1_STREAM');
END;
/