Database Replication with Oracle 11G Streams

Starting from Oracle HA (High Availability) products, Oracle Dataguard, RAC and RMAN are the most commonly used products. Oracle Streams emerges as a replication product, but mainly uses Oracle Logical Standby technology.
Oracle Streams generally consists of three different processes called Apply, Propagate and Capture. Apply is the process that runs the LCR records in the queue on the target database. The probogate process is a process that takes LCR records from the capture plan and writes them to the apply plan. The Apply process is the process that runs the LCR transactions on the Target database that are waiting in the apply queue.

• 

If the capture process runs on the Target database and changes are applied to the same database, this event is called “Down stream capture”. Since the Archivelog scan is performed on the target database, no load occurs on the Source database. Propagation is not necessary in such a structure, because capture and apply plans can be set to use the same plan structure.

We can do table replication using Oracle Streams, or we can replicate the records in a schema. We can use the capture process for a table from which we can select, or for multiple tables. Replication of multiple tables from a schema replication can be prevented using “Rulesets”.
Making the necessary Presets
A database user must be created for the plans and plan tables required for the operation of the stream procedures.
The Strem user must have dba privileges and must not be longer than 32 characters, otherwise an error may occur in the executed procedures.

create user stream identified by stream123;

grant connect,resource to stream;

grant create database link to stream;

grant create any directory to stream;

grant dba to stream;

exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(‘stream’,TRUE);

779/5000To synchronize changes to tables, databases must establish connections through Dblink. Streams procedures use datapump as the background process for this process. After the table equality is achieved, the Streams process starts via Archivelogs. It uses the SCN (System change number) to check when the changes start. To update these values, it takes the SCN value with Dblink over the source database and updates the SCN value of the table on the Target database. Briefly, new updates will update if values larger than the current SCN number after synchronization.
The dblink definitions must be on the Streams Manager Schema. Let’s do the Dblink creation process as follows.

select* from v$parameter where name in(‘db_name’,’db_domain’);

select* from dba_db_links where owner=’<streams_admin_username>’;

select * from dba_db_links where owner=’stream’;

select * from v$parameter where name in(‘db_name’,’db_domain’);
select* from dba_db_links where owner=’<streams_admin_username>’;
select * from dba_db_links where owner=’stream’;
select* from global_name;
select * from dba_db_links;
select * from all_db_links;

connect stream/stream123@orcldg;
connect stream/stream123@orcl;

// target
create database link orcldg connect to stream identified by stream123 using ‘orcldg’;
// source
create database link orcl connect to stream identified by stream123 using ‘orcl’;

Create Directory

create or replace directory script_directory as ‘/home/oracle/streamss/’;
create or replace directory source_directory as ‘/home/oracle/streams/’;
create or replace directory destination_directory as ‘/home/oracle/streams/’;

DECLARE tables DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tables(1):=’stream.test’;
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
table_names =>tables,
source_directory_object =>NULL,
destination_directory_object =>NULL,
source_database =>’orcl’,
destination_database =>’orcl’,
capture_name =>’stream_capture’,
capture_queue_name =>’stream_que’,
capture_queue_user =>’stream’,
propagation_name =>’stream_pro’,
apply_name =>’stream_apply’,
apply_queue_name =>’stream_quee’,
apply_queue_user =>’stream’,
perform_actions =>TRUE,
script_directory_object =>’script_directory’,
script_name =>’perform_actions.sql’,
bi_directional =>FALSE,
include_ddl =>TRUE,
instantiation =>DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
END;

More Coming soon..