steps for "Oracle (Real Time) Downstream".

These steps are tested in the following 2 scenarios:

* Source Database 10gR2/Downstream Database 10gR2
* Source Database 10gR2/Downstream Database 11gR2=====================================
Database 1==Primary Database=="ORCL1"
=====================================
-----------------------------Start Step 1----------------------------------------------------
conn /as sysdba
CREATE TABLESPACE streams_tbs DATAFILE '/oracle/10g/oradata/orcl1/strm01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
GRANT DBA TO strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
SELECT * FROM dba_streams_administrator;
-----------------------------End Step 1----------------------------------------------------
-----------------------------Start Step 3--------------------------------------------------
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE="ORCL1" LGWR SYNC NOREGISTER
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCL1'
SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL1)' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app2/dest_1';
ALTER SYSTEM SET log_archive_dest_state_1 = 'ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_format = 'ORCL_%t_%s_%r.arc';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
-----------------------------End Step 3----------------------------------------------------
-----------------------------Start Step 5----------------------------------------------------
ALTER SYSTEM ARCHIVE LOG CURRENT;
-----------------------------Stop Step 5----------------------------------------------------
-----------------------------Start Step 7----------------------------------------------------
conn system/oracle
!mkdir /u01/schema_export
!chmod 777 /u01/schema_export
create or replace directory schema_export as '/u01/schema_export';
!expdp system/oracle SCHEMAS=SCOTT DUMPFILE=schema_export:schema.dmp LOGFILE=schema_export:schema.log
-----------------------------Stop Step 7----------------------------------------------------
====================================
Database 2==Downstream Site=="ORCL2"
====================================
-----------------------------Start Step 2----------------------------------------------------
conn /as sysdba
CREATE TABLESPACE streams_tbs DATAFILE '/oracle/10g/oradata/orcl2/strm01.dbf'SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
GRANT DBA TO strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
SELECT * FROM dba_streams_administrator;
exec DBMS_LOGMNR_D.SET_TABLESPACE ('streams_tbs');
conn strmadmin/strmadmin
create database link ostrm connect to strmadmin identified by strmadmin using 'orcl1';
select * from dual@ostrm;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/oracle/arch2/dest_2/
VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
SCOPE=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl1,orcl2)' SCOPE=BOTH;
conn /as sysdba
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/oracle/10g/oradata/orcl2/slog4.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/oracle/10g/oradata/orcl2/slog5.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/oracle/10g/oradata/orcl2/slog6.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
('/oracle/10g/oradata/orcl2/slog7.rdo') SIZE 50M;

SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;
conn /as sysdba
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app2/dest_1' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_1 = 'ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_format = 'ORCL1_%t_%s_%r.arc' SCOPE=SPFILE;

shutdown immediate
startup mount
alter database archivelog;
alter database open;

ALTER SYSTEM SET log_archive_max_processes=5 SCOPE=BOTH;
-----------------------------End Step 2----------------------------------------------------
-----------------------------Start Step 4----------------------------------------------------
conn strmadmin/strmadmin
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.DS_QT',
queue_name => 'strmadmin.DS_Q',
queue_user => 'STRMADMIN');
END;
/
select name, queue_table from user_queues;
conn strmadmin/strmadmin
BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => 'DS_Q',
apply_name => 'DS_A',
apply_captured => TRUE
);
END;
/
SELECT apply_name, status, queue_name FROM DBA_APPLY;
SELECT parameter, value, set_by_user
FROM DBA_APPLY_PARAMETERS
WHERE apply_name = 'DS_A';
conn strmadmin/strmadmin
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'DS_Q',
capture_name => 'DS_C',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'ostrm',
use_database_link => true, -- For administrative purposes.
first_scn => NULL,
logfile_assignment => 'implicit'); -- capture process accepts redo data implicitly from Source.
END;
/
SELECT capture_name, status from dba_capture;
SELECT parameter, value, set_by_user FROM DBA_CAPTURE_PARAMETERS;
conn strmadmin/strmadmin
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'DS_C',
parameter => 'downstream_real_time_mine',
value => 'y');
END;
/
-----------------------------End Step 4----------------------------------------------------
-----------------------------Start Step 6----------------------------------------------------
--check that the status of one/more of the standby logs has changed from UNASSIGNED to ACTIVE:
SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;
conn strmadmin/strmadmin
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'scott',
streams_type => 'capture',
streams_name => 'ds_c',
queue_name => 'ds_q',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ORCL1',
inclusion_rule => TRUE);
END;
/
/*
Check the created rules
*/
SELECT rule_name, rule_condition
FROM DBA_STREAMS_SCHEMA_RULES
WHERE streams_name = 'DS_C'
AND streams_type = 'CAPTURE';
-----------------------------Stop Step 6----------------------------------------------------
-----------------------------Start Step 8----------------------------------------------------
conn system/oracle
!mkdir /u01/schema_import
!chmod 777 /u01/schema_import
create or replace directory schema_import as '/u01/schema_import';
-- copy the dump file schema.dmp from '/u01/schema_export' on source site to '/u01/schema_import' on downstream site.
!impdp system/oracle SCHEMAS=SCOTT DIRECTORY=schema_import DUMPFILE=schema.dmp
/*
select * from DBA_APPLY_INSTANTIATED_OBJECTS;
select * from DBA_APPLY_INSTANTIATED_SCHEMAS;
*/
conn strmadmin/strmadmin
exec DBMS_APPLY_ADM.START_APPLY(apply_name => 'DS_A');
select apply_name, status from dba_apply;
conn strmadmin/strmadmin
exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'DS_C');
select capture_name, status from dba_capture;
-----------------------------Stop Step 8----------------------------------------------------
=====================CHECKING STREAMS PROPER FUNCTIONING==========================
Perform some DMLs on Primary/Source with scott user. Check each DML reflection on Downstream site.
==================================================================================