Oracle streams

ORACLE STREAMS

 
2 databases
1 src as source database
1 dst as destination database

replication type 1 way of the entire schema STREAMSCHEMA
Step 1. Set all databases in archivelog mode.

Step 2. Change initialization parameters for Streams. The Streams pool size and NLS_DATE_FORMAT require a restart of the instance.

SQL> alter system set global_names=true scope=both;
SQL> alter system set undo_retention=3600 scope=both;
SQL> alter system set job_queue_processes=4 scope=both;
SQL> alter system set streams_pool_size= 20m scope=spfile;
SQL> alter system set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' scope=spfile;
SQL> shutdown immediate;
SQL> startup
Step 3. Create Streams administrators on the src and dst databases, and grant required roles and privileges. Create default tablespaces so that they are not using SYSTEM.

---at the src
SQL> create tablespace streamsdm datafile '/u01/product/oracle/oradata/orcl/strepadm01.dbf' size 100m;

---at the replica:
SQL> create tablespace streamsdm datafile '/u02/oracle/oradata/str10/strepadm01.dbf' size 100m;
---at both sites:
SQL> create user streams_adm
identified by streams_adm
default tablespace strepadm01
temporary tablespace temp;

SQL> grant connect, resource, dba, aq_administrator_role to streams_adm;
SQL> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'streams_adm', grant_privileges => true);
END;
/
Step 4. Configure the tnsnames.ora at each site so that a connection can be made to the other database.
Step 5. With the tnsnames.ora squared away, create a database link for the streams_adm user at both SRC and DST. With the init parameter global_name set to True, the db_link name must be the same as the global_name of the database you are connecting to. Use a SELECT from the table global_name at each site to determine the global name.

SQL> select * from global_name;
SQL> connect streams_adm/streams_adm@SRC
SQL> create database link DST
connect to streams_adm identified by streams_adm using 'DST';
SQL> select sysdate from dual@DST;
SLQ> connect streams_adm/streams_adm@DST
SQL> create database link SRC connect to stream_admin identified by streams_adm using 'SRC';
SQL> select sysdate from dual@SRC;

Step 6. Control what schema shall be replicated

STREAMSCHEMA is the schema to be replicated
Step 7. Add supplemental logging to the STREAMSCHEMA schema on all the
tables?

SQL> Alter table STREAMSCHEMA.tb1 add supplemental log data (ALL) columns;
SQL> alter table STREAMSCHEMA.tb2 add supplemental log data (ALL) columns;

etc...
Step 8. Create Streams queues at the primary and replica database.

---at SRC (primary):

SQL> connect stream_admin/stream_admin@ORCL
SQL> BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streams_adm.STREAMSCHEMA_src_queue_table',
queue_name => 'streams_adm.STREAMSCHEMA_src__queue');
END;
/
---At DST (replica):
SQL> connect stream_admin/stream_admin@STR10
SQL> BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stream_admin.STREAMSCHEMA_dst_queue_table',
queue_name => 'stream_admin.STREAMSCHEMA_dst_queue');
END;
/
Step 9. Create the capture process on the source database (SRC).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'STREAMSCHEMA',
streams_type =>'capture',
streams_name =>'STREAMSCHEMA_src_capture',
queue_name =>'STREAMSCHEMA_src_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database => NULL,
inclusion_rule => true);
END;
/
Step 10. Instantiate the STREAMSCHEMA schema at DST. by doing export
import : Can I use now datapump to do that ?

---AT SRC:
exp system/superman file=STREAMSCHEMA.dmp log=STREAMSCHEMA.log
object_consistent=y owner=STREAMSCHEMA

---AT DST:
---Create STREAMSCHEMA tablespaces and user:
create tablespace STREAMSCHEMA_datafile
'/u02/oracle/oradata/str10/STREAMSCHEMA_01.dbf' size 100G;
create tablespace ws_app_idx datafile
'/u02/oracle/oradata/str10/STREAMSCHEMA_01.dbf' size 100G;
create user STREAMSCHEMA identified by STREAMSCHEMA_
default tablespace STREAMSCHEMA_
temporary tablespace temp;
grant connect, resource to STREAMSCHEMA;

imp system/123db file=STREAMSCHEMA_.dmp log=STREAMSCHEMA.log fromuser=STREAMSCHEMA  touser=STREAMSCHEMA streams_instantiation=y
Step 11. Create a propagation job at the source database (SRC).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name =>'STREAMSCHEMA',
streams_name =>'STREAMSCHEMA_src_propagation',
source_queue_name =>'stream_admin.STREAMSCHEMA_src_queue',
destination_queue_name=>'stream_admin.STREAMSCHEMA_dst_queue@dst',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'SRC',
inclusion_rule =>true);
END;
/

Step 12. Create an apply process at the destination database (DST).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'STREAMSCHEMA',
streams_type =>'apply',
streams_name =>'STREAMSCHEMA_Dst_apply',
queue_name =>'STREAMSCHEMA_dst_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'SRC',
inclusion_rule =>true);
END;
/
Step 13. Create substitution key columns for äll the tables that haven't a primary key of the STREAMSCHEMA schema on DST The column combination must provide a unique value for Streams.

SQL> BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name =>'STREAMSCHEMA.tb2',
column_list =>'id1,names,toys,vendor');
END;
/

Step 14. Configure conflict resolution at the replication db (DST).

DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'names';
cols(3) := 'toys';
cols(4) := 'vendor';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name =>'STREAMSCHEMA.tb2',
method_name =>'OVERWRITE',
resolution_column=>'STREAMSCHEMA',
column_list =>cols);
END;
/
Step 15. Enable the capture process on the source database (SRC).

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMSCHEMA_src_capture');
END;
/

Step 16. Enable the apply process on the replication database (DST).

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STREAMSCHEMA_DST_apply');
END;
/
Step 17. Test streams propagation of rows from source (src) to replication (DST).

AT ORCL:

insert into STREAMSCHEMA.tb2 values (
31000, 'BAMSE', 'DR', ‘SILICON’);

AT STR10:

connect STREAMSCHEMA/STREAMSCHEMA
select * from STREAMSCHEMA.tb2 where vendor= ‘SILICON’;