Step 1 Create Users & Setup Privileges
SQL>CREATE USER SADM IDENTIFIED BY SADM;
SQL>GRANT CONNECT, RESOURCE, DBA TO SADM;
SQL>GRANT SELECT_CATALOG_ROLE TO SADM;
SQL>GRANT UNLIMITED TABLESPACE TO SADM;
SQL>EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'SADM');
Step 2 Create database links at source and target databases
connect SADM/SADM@TST1
CREATE DATABASE LINK TST2 CONNECT TO SADM IDENTIFIED BY SADM USING 'TST2';
connect SADM/SADM@TST2
CREATE DATABASE LINK TST1 CONNECT TO SADM IDENTIFIED BY SADM USING 'TST1';
Step 3 Create the queue at TST1 - Source Database
connect SADM/SADM@TST1
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE (
QUEUE_TABLE => 'C1_STREAM_Q1_QT',
QUEUE_NAME => 'C1_STREAM_Q1',
QUEUE_USER => 'SADM');
END;
/
Create the queue at TST2 (Target) ##
connect SADM/SADM@TST2
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@TST1
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'EDW_APP_OWNER',
streams_type =>'CAPTURE',
streams_name =>'C1_STREAM',
queue_name =>'SADM.C1_STREAM_Q1',
include_dml =>TRUE,
include_ddl =>TRUE,
source_database =>'TST1');
END;
/
Step 5 Create apply process at target
connect SADM/SADM@TST2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'EDW_APP_OWNER',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'A1_STREAM',
QUEUE_NAME => 'SADM.A1_STREAM_Q1',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TST1');
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@TST1
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'EDW_APP_OWNER',
STREAMS_NAME => 'P1_STREAM',
SOURCE_QUEUE_NAME => 'SADM.C1_STREAM_Q1',
DESTINATION_QUEUE_NAME => 'SADM.A1_STREAM_Q1@TST2',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE);
END;
/
Step 7 Instantiation at TST2
At source TST1...
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 TST2...
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@TST2
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 => 'EDW_APP_OWNER',
source_database_name => 'TST1',
instantiation_scn => v_scn,
recursive => true);
end;
/
Step 8 At target start apply
connect SADM/SADM@TST2
exec dbms_apply_adm.start_apply('A1_STREAM');
Step 9 At source start capture
connect SADM/SADM@TST1
exec DBMS_CAPTURE_ADM.START_CAPTURE('C1_STREAM');
