Oracle Streams

• Oracle Streams is a replication methodology where database can be replicated across different operating database.

• Oracle streams consists of three stages,

• Changes of the database will be written in the online redo log files, Oracle streams will extract the changes from the log as it is written.
• Changes are formatted as Logical Change Record (LCR).
• Streams publish captured events into staging area.
• All events captured in the capture site will be sent as queue.
• The events will stage in the queue until it is consumed by the subscribers.
• All the propagated messages will be consumed by the apply process and it will be applied at the destination site.

Below are the steps which i followed for implementing Oracle Streams on replicating their database across two operating system ( between Compaq Tru 64 and Hp-Ux )

1. The following steps has to be followed at both the sites(Source and Destination),

a) create table space streams_tbs
datafile ‘/u01/datafiles/streams.dbf’ size 1024m;

b) create user stradm identified by stradm
default tablespace streams_tbs
temporary tablespace temp
quota unlimited on streams_tbs;

c) grant connect,resource,dba to stradm;

d) execute MS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRADM');

2. The following init parameters has to be modified in both the sites,

a)    COMPATIBLE = 10.1.0 or higher
b) GLOBAL_NAMES = TRUE
c) JOB_QUEUE_PROCESSES =6
d) PARALLEL_MAX_SERVERS = 10
e) SHARED_POOL_SIZE = 10% of the shared_pool_size. 
f) OPEN_LINKS = 10.
g) PROCESSES = DEFAULT.
h) SESSIONS = DEFAULT
i) SGA_MAX_SIZE =DEFAUL
j) TIMED_STATISTICS = TRUE
k) STREAMS_POOL_SIZE > 200M
l) UNDO_RETENTION > 900 
m) CAPTURE SITE = ARCHIVE LOG MODE
n)_job_queue_interval=1 
o) alter system reset aq_tm_processes scope=spfile sid='*';

3. The following steps has to be executed at the source site ( Capture site ) ,

$sqlplus "sys as sysdba"

SQL> alter database add supplemental log data (all) columns;

4. The following steps has to be executed on the source site to create the queue,capture and propagation process,

conn stradm/stradm@source

To create queue,

begin
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
queue_name => 'STREAMS_QUEUE',
queue_user => 'STRADM');
end;
/

To create capture process,

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'SCHEMA NAME ',
streams_type => 'capture';
streams_name => 'capture';
queue_name => 'streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'SOURCE DB NAME');
end;

To create a propagation process,we have to create a database link which connects to the streams administrator user in the destination site as,

Create database link as alh1 connect to stradm identified by stradm using ‘ALH1’;

To create propagation process,

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'DEVLP',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'STREAMS_QUEUE',
destination_queue_name => STREAMS_QUEUE@DBLINK',
include_dml => true,
include_ddl => true,
source_database => 'SOURCE DB NAME');
end;
/

Once when capture and propagation process is created at the source site,do the following

Sqlplus > conn stradm/stradm

Sql > select start_scn from dba_capture:

Note down the SCN value mentioned by the query. This SCN has be to used for the instantiation process at the destination site.

5. The following steps has to be followed at the destination site to create the apply proces

conn stradm/stradm@source

To create queue,

begin
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
queue_name => 'STREAMS_QUEUE',
queue_user => 'STRADM');
end;
/

To create apply process,

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'SCHEMA NAME',
streams_type => 'apply',
streams_name => 'apply',
queue_name => 'streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'SOURCE DB NAME');
end;
/

begin
dbms_apply_adm.alter_apply(
apply_name => 'apply',
apply_user => 'SCHEMA NAME');
end;
/

6. Export of the whole schema has to be taken at the source site using the following parameter Exp system/manager file=/u01/devlp.dmp owner=devlp consistent=y statistics=none

7. Once when the export is over,take the file to the destination site and import it using the following parameters,

Imp system/manager file=/u02/devlp.dmp fromuser=devlp touser=devlp streams_instantiation=y ignore=y

8. Once after the apply process is created,execute the below query inorder to set the schema instantiation SCN at the destination site,

9. After setting the above instantiation,execute the following statement to start the apply process at the destination site,

Conn stradm/stradm

begin
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => 'SCHEMA NAME',
source_database_name => 'SOURCE DB NAME',
instantiation_scn =>Start_scn );
end;
/

10. After starting the apply process at destination site,Start the capture process at the source site by executing,

Conn stradm/stradm

begin
dbms_apply_adm.start_apply(
apply_name => 'apply');
end;
/

begin
dbms_capture_adm.start_capture(
capture_name => 'capture');
end;
/

Useful Views :

a. dba_apply;
b. dba_queue_tables;
c. V$STREAMS_APPLY_COORDINATOR
d. v$propagation_receiver;
e. dba_apply_progress;
f. V$STREAMS_APPLY_SERVER
e. dba_apply_error
g. gV$STREAMS_APPLY_READER
h. dba_apply_key_columns
i. dba_apply_dml_handlers
j. v$buffered_subscribers
select status,error_number,error_message from dba_apply