Oracle Streams Configure and Sample - Heart Beat

Oracle Streams Configure
Oracle Live(Prod) Database Name : ORCL
Oracle Downstreams Database Name : Power
Oracle Target Database Name : Blue

1.Configure Oracle Net so that the ALL database can communicate.
2.Preparing to Copy Redo Log Files for Downstream Capture

Source(Live db ORCL) initORCL.ora
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,power)'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ABLE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_5='SERVICE=power ARCH OPTIONAL NOREGISTER REOPEN=60 TEMPLATE=D:\TEST\arc%s%t%r.arc DB_UNIQUE_NAME=power'

Downstream(Power) initpower.ora
alter system set log_Archive_config='DG_CONFIG=(ORCL,POWER)'

Follwing Steps in Live DB(Prod)

3. Create Sample table and upadte every 1 minute sample data.
create user ms identified by ms;
ALTER USER MS Default tablespace users Quota unlimited on users;
create table ms.stream_heartbeat (GLOBAL_NAME VARCHAR2(4000),LAST_ACTIVITY_DATE DATE) TABLESPACE USERS;
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);
INSERT INTO STREAM_HEARTBEAT ((SELECT GLOBAL_NAME FROM GLOBAL_NAME),SYSDATE)

4. Enable Supplemental log.
ALTER TABLE ms.stream_heartbeat ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY ) COLUMNS;

5. Create sample procedure (Streams Heart Beat)
create or replace procedure proc_stream_heartbeat_ms IS
begin
update ms.stream_heartbeat
set last_Activity_Date = sysdate;
end proc_stream_heartbeat_ms;

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Streams_ms_Heartbeat',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN ms.proc_stream_heartbeat_ms; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
end_date => NULL,
enabled => TRUE,
comments => 'Heartbeat job for ms Schema');
End;

BEGIN
DBMS_SCHEDULER.DISABLE('Streams_ms_Heartbeat');
-- and then
DBMS_SCHEDULER.ENABLE('Streams_ms_Heartbeat');
END;

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

6. Create Streams User.
Create user strmadmin Identified by st$1$1admin ;
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';

7. Connet Streams User and run instantiation.
DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@blue(
source_object_name => 'ms.stream_heartbeat ',
source_database_name => 'orcl',
instantiation_scn => source_scn);
END;
/

Following Step are Downstream db;

1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;

2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
Grant connect, resource, dba,aq_Administrator_role to strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

3. Connect strmadmin/strm$1admin

4.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';

5. Now Configure Streams Stepup in Downstrams (Capture and Propagation)
6.Create Streams Queue Stepup
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

7. Create Capture Stepup
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'streams_queue',
capture_name => 'stream1_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'orcl',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/


8. Add table to Capture.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'ms.stream_heartbeat',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

9. Create Propagation Setup.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'ms.stream_heartbeat',
streams_name => 'stream1_propogation',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@blue',
include_dml => true,
include_ddl => false,
source_database => 'orcl',
inclusion_rule => true);
END;
/


Following Steps are Target DB.

1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;

2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
GRANT CONNECT, RESOURCE, DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

3.Connect strmadmin/strm$1admin

4.Create Streams Queue Stepup (Apply)
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

5.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';

6. Create sample table.
create table ms.stream_heartbeat as (select * from ms.stream_heartbeat@orcl);
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);

7. Create Apply Processing.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'msr.stream_heartbeat',
streams_type => 'apply',
streams_name => 'apply1_stream',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => FALSE,
source_database => 'orcl',
inclusion_rule => TRUE );
END;
/

Now Streams Configuraction Compelete.

1. Start Capture.

2. Start Propagation.

3. Start Apply.