Oracle Tech Zone

ALTER system SET global_names=true;
SELECT * FROM global_name;
 
GLOBAL_NAME
------------
STRM1.COM
GLOBAL_NAME
------------
STRM2.COM

ALTER system SET job_queue_processes=10;

ALTER system SET streams_pool_size=50M scope=spfile;

ALTER system SET open_links=4 scope=spfile;

ALTER system SET parallel_max_servers=20 scope=BOTH;

-- strm1
CREATE tablespace tbs_logmn datafile '/oracle/oradata/strm1/tbs_ logmn01.dbf'
size 100m autoextend ON;
execute dbms_logmnr_d.set_tablespace('tbs_logmn');

-- minimal supplemental log mode
ALTER DATABASE ADD supplemental log DATA;
COLUMN log_min HEADING 'Minimum|Supplemental|Logging?' FORMAT A12
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging?' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging?' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging?' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging?' FORMAT A12
SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min,
SUPPLEMENTAL_LOG_DATA_PK log_pk,
SUPPLEMENTAL_LOG_DATA_FK log_fk,
SUPPLEMENTAL_LOG_DATA_UI log_ui,
SUPPLEMENTAL_LOG_DATA_ALL log_all
FROM V$DATABASE;
 
Minimum      PRIMARY KEY  FOREIGN KEY  UNIQUE       ALL COLUMNS
Supplemental Supplemental Supplemental Supplemental Supplemental
Logging?     Logging?     Logging?     Logging?     Logging?
------------ ------------ ------------ ------------ ------------
YES          NO           NO           NO           NO

-- Create streams tablespace
sqlplus sys@strm1 AS sysdba
CREATE tablespace tbs_stream datafile '/oracle/oradata/strm1/tbs_stream01.dbf'
size 100m autoextend ON;
-- Create streams admin
CREATE user strmadmin IDENTIFIED BY nhy67ujm DEFAULT tablespace tbs_stream;
-- streams admin MUST have dba privilege
GRANT connect,resource,dba TO strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/
-- Confirm
SELECT * FROM dba_streams_administrator;
USERNAME                       LOC ACC
------------------------------ --- ---
STRMADMIN                      YES YES

-- Create streams tablespace
sqlplus sys@strm2 AS sysdba
CREATE tablespace tbs_stream datafile '/oracle/oradata/strm2/tbs_stream01.dbf'
size 100m autoextend ON;
-- Create streams admin
CREATE user strmadmin IDENTIFIED BY nhy67ujm DEFAULT tablespace tbs_stream
TEMPORARY tablespace temp;
-- Grant dba to streams admin
GRANT connect,resource,dba TO strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/
-- Confirm
SELECT * FROM dba_streams_administrator;
USERNAME                       LOC ACC
------------------------------ --- ---
STRMADMIN                      YES YES

-- Note: database link name must same as global name of target db
-- command below can change global name
ALTER DATABASE RENAME global TO strm1.com;
-- strm1: connect as stream admin
conn strmadmin@strm1
strmadmin@STRM1> CREATE DATABASE link strm2.com connect TO strmadmin
IDENTIFIED BY nhy67ujm USING 'strm2';

-- login as sys
sys@STRM1> SELECT NAME,CTIME,USERID FROM sys.link$;
NAME       CTIME               USERID
---------- ------------------- ----------
STRM2.COM  2009/05/03 23:55:16 STRMADMIN
 
-- source
exec dbms_streams_adm.set_up_queue();
SELECT name,queue_table FROM user_queues;
-- target
EXEC  DBMS_STREAMS_ADM.SET_UP_QUEUE();
-- s
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'dbausr.test_nolog',
    streams_name            => 'str1_to_str2',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@strm2.com',
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'strm1.com',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/
SELECT propagation_name,STATUS FROM dba_propagation;
 
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'dbausr.test_nolog',
    streams_type   => 'capture',
    streams_name   => 'capture_simp',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/
SELECT capture_name,STATUS FROM dba_capture;
-- t
DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@strm2.com(
    source_object_name    => 'dbausr.test_nolog',
    source_database_name  => 'strm1.com',
    instantiation_scn     => iscn);
END;
/
 
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'dbausr.test_nolog',
    streams_type    => 'apply',
    streams_name    => 'apply_simp',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'strm1.com',
    inclusion_rule  => true);
END;
/
SELECT apply_name,STATUS FROM dba_apply;
 
BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_simp',
    parameter   => 'disable_on_error',
    value       => 'n');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_simp');
END;
/
 
-- s
BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_simp');
END;
/
SELECT capture_name,STATUS FROM dba_capture;