CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Streams Demo 1
Version 10.2
 
Setup As SYS - Prepare Database and Instance
conn / as sysdba

-- validate Oracle parameters
archive log list                    -- document this
show parameter aq_tm_processes      -- min 3
show parameter compatible           -- must be 10.1.0 or above
show parameter global_names         -- must be TRUE
show parameter job_queue_processes  -- min 2 recommended 4-6
show parameter open_links           -- not less than the default 4
show parameter shared_pool_size     -- must be 0 or at least 100MB 
show parameter streams_pool_size    -- min. 15MB (10MB/capture 1MB/apply) - dflt 48MB
show parameter undo_retention       -- min. 3600 (1 hr.)

-- Examples of altering initialization parameters
alter system set aq_tm_processes=3 scope=BOTH;
alter system set compatible=10.2.0.1.0 scope=BOTH;
alter system set global_names=TRUE scope=BOTH;
alter system set job_queue_processes=6 scope=BOTH;
alter system set open_links=4 scope=BOTH;
alter system set streams_pool_size=48M scope=BOTH; -- very slow if making smaller
alter system set undo_retention=3600 scope=BOTH;
-- Retest parameter after modification

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;
 
Setup As SYS - Create Streams Administrators
On Alpha 1 create user localadmin, on Alpha 2 create user remoteadmin
conn / as sysdba

CREATE USER localadmin
IDENTIFIED BY localadmin
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 20M ON user_data;

GRANT create session TO localadmin;
GRANT create database link TO localadmin;
GRANT create procedure TO localadmin;

GRANT aq_administrator_role TO localadmin IDENTIFIED BY localadmin;

GRANT execute ON dbms_streams_adm TO localadmin;
GRANT execute ON dbms_streams_auth TO localadmin;

SELECT username, account_status, created
FROM dba_users
ORDER BY 1;

SELECT *
FROM dba_sys_privs
WHERE grantee = 'LOCALADMIN';

set linesize 131
col privilege format a15
col owner format a15

SELECT role, owner, table_name, privilege
FROM role_tab_privs
WHERE role = 'AQ_ADMINISTRATOR_ROLE'
ORDER BY 4, 2, 3;

exec dbms_streams_auth.grant_admin_privilege('LOCALADMIN', TRUE);

exec dbms_streams_auth.grant_remote_admin_access('LOCALADMIN');
 
Configure TNSNAMES.ORA
ALPHA1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = alpha1.psoug.org)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alpha1db)
    )
  )

ALPHA2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = alpha2.psoug.org)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alpha2db)
    )
  )
 
Create DB Link
on Alpha 1 on Alpha 2
SELECT * FROM global_names;

conn localadmin/localadmin

CREATE DATABASE LINK alpha2db.psoug.org
CONNECT TO remadmin
IDENTIFIED BY remadmin
USING 'ALPHA2.PSOUG.ORG';

SELECT SYSDATE
FROM dual@alpha2;
SELECT * FROM global_names;

conn remadmin/remadmin

CREATE DATABASE LINK alpha1db.psoug.org
CONNECT TO locadmin
IDENTIFIED BY locadmin
USING 'ALPHA1.PSOUG.ORG';

SELECT SYSDATE
FROM dual@alpha1;
 
Prepare Schema Tables for Streams Replication
on Alpha 1 on Alpha 2
conn scott/tiger

SELECT table_name
FROM user_tables;

ALTER TABLE audit_trail 
ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE bonus
ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE dept 
ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE emp 
ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE salgrade 
ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
conn / as sysdba

drop user scott cascade;

CREATE USER scottrep
IDENTIFIED BY scottrep
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
QUOTA 0 ON sysaux
QUOTA 50M ON user_data;

GRANT create session TO scottrep;
GRANT create table TO scottrep;
GRANT create type TO scottrep;
 
Create Streams Queues
on Alpha 1 on Alpha 2
dbms_streams_adm.set_up_queue(
queue_table    IN VARCHAR2 DEFAULT 'streams_queue_table',
storage_clause IN VARCHAR2 DEFAULT NULL,
queue_name     IN VARCHAR2 DEFAULT 'streams_queue',
queue_user     IN VARCHAR2 DEFAULT NULL,
comment        IN VARCHAR2 DEFAULT NULL);
conn locadmin/locadmin

exec dbms_streams_adm.set_up_queue(
queue_table    => 'alpha1_scott_qtab',
storage_clause => 'PCTFREE 0 PCTUSED 99'
queue_name => 'a1_scott_capture_q',
queue_user => NULL,
comment    => 'Demo Alpha1 Streams Queue');
conn remadmin/remadmin

exec dbms_streams_adm.set_up_queue(
queue_table    => 'alpha2_scott_qtab',
storage_clause => 'PCTFREE 0 PCTUSED 99',
queue_name => 'a2_scott_apply_q',
queue_user => NULL,
comment    => 'Demo Alpha2 Streams Queue');
 
Create Capture Process

Alpha 1 Local Administrator's Only

set serveroutput on
dbms_streams_adm.add_schema_rules(
schema_name        IN  VARCHAR2,
streams_type       IN  VARCHAR2,
streams_name       IN  VARCHAR2 DEFAULT NULL,
queue_name         IN  VARCHAR2 DEFAULT 'streams_queue',
include_dml        IN  BOOLEAN  DEFAULT TRUE,
include_ddl        IN  BOOLEAN  DEFAULT FALSE,
include_tagged_lcr IN  BOOLEAN  DEFAULT FALSE,
source_database    IN  VARCHAR2 DEFAULT NULL,
dml_rule_name      OUT VARCHAR2,
ddl_rule_name      OUT VARCHAR2,
inclusion_rule     IN  BOOLEAN  DEFAULT TRUE,
and_condition      IN  VARCHAR2 DEFAULT NULL);


DECLARE
 dml_rname VARCHAR2(30);
 ddl_rname VARCHAR2(30);
BEGIN
  dbms_streams_adm.add_schema_rules(
  schema_name  => 'SCOTT',
  streams_type => 'CAPTURE'
  streams_name => 'A1_SCOTT_CAPTURE',
  queue_name   => 'A1_SCOTT_CAPTURE_Q',
  include_dml  => TRUE,
  include_ddl  => TRUE,
  include_tagged_lcr => FALSE,
  dml_rule_name  => dml_rname,
  ddl_rule_name  => ddl_rname,
  inclusion_rule => TRUE,
  and_condition  => NULL);

  dbms_output.put_line('DML Rule Name: ' || dml_rname);
  dbms_output.put_line('DDL Rule Name: ' || ddl_rname);
END;
/

Now look in user_ views
 
Export / Import Scott Schema (can we do this with DataPump?)
on Alpha 1 on Alpha 2
exp system/oracle1 file=$HOME/scott.dmp log=$HOME/scott.log object_consistent=Y owner=SCOTT

FTP scott.dmp to Alpha 2
imp system/oracle1 file=$HOME/scott.dmp log=$HOME/scott.log fromuser=SCOTT touser=SCOTTREP streams_instantiation=Y
 
Create Propagation Process
on Alpha 1
set serveroutput on
dbms_streams_adm.add_global_propagation_rules(
streams_name           IN  VARCHAR2 DEFAULT NULL,
source_queue_name      IN  VARCHAR2,
destination_queue_name IN  VARCHAR2,
include_dml            IN  BOOLEAN DEFAULT TRUE,
include_ddl            IN  BOOLEAN DEFAULT FALSE,
include_tagged_lcr     IN  BOOLEAN DEFAULT FALSE,
source_database        IN  VARCHAR2 DEFAULT NULL,
dml_rule_name          OUT VARCHAR2,
ddl_rule_name          OUT VARCHAR2,
inclusion_rule         IN  BOOLEAN DEFAULT TRUE,
and_condition          IN  VARCHAR2 DEFAULT NULL,
queue_to_queue         IN  BOOLEAN DEFAULT NULL);


DECLARE
 dml_rname VARCHAR2(30);
 ddl_rname VARCHAR2(30);
BEGIN
  dbms_streams_adm.add_global_propagation_rules(
  streams_name           => 'A1_SCOTT_CAPTURE',
  source_queue_name      => 'A1_SCOTT_CAPTURE_Q',
  destination_queue_name => 'A2_SCOTT_APPLY_Q',
  include_dml            => TRUE,
  include_ddl            => TRUE,
  include_tagged_lcr     => FALSE,
  source_database        => 'ALPHA1DB,
  dml_rule_name          => dml_rname,
  ddl_rule_name          => ddl_rname,
  inclusion_rule         => TRUE,
  and_condition          => NULL,
  queue_to_queue         => NULL);

  dbms_output.put_line('DML Rule Name: ' dml_rname);
  dbms_output.put_line('DDL Rule Name: ' ddl_rname);
END;
/

Now look in user_ views

 
Create Capture Process
on Alpha 2
set serveroutput on
dbms_streams_adm.add_schema_rules(
schema_name        IN  VARCHAR2,
streams_type       IN  VARCHAR2,
streams_name       IN  VARCHAR2 DEFAULT NULL,
queue_name         IN  VARCHAR2 DEFAULT 'streams_queue',
include_dml        IN  BOOLEAN  DEFAULT TRUE,
include_ddl        IN  BOOLEAN  DEFAULT FALSE,
include_tagged_lcr IN  BOOLEAN  DEFAULT FALSE,
source_database    IN  VARCHAR2 DEFAULT NULL,
dml_rule_name      OUT VARCHAR2,
ddl_rule_name      OUT VARCHAR2,
inclusion_rule     IN  BOOLEAN  DEFAULT TRUE,
and_condition      IN  VARCHAR2 DEFAULT NULL);

DECLARE
 dml_rname VARCHAR2(30);
 ddl_rname VARCHAR2(30);
BEGIN
  dbms_streams_adm.add_schema_rules(
  schema_name  => 'SCOTTREP',
  streams_type => 'APPLY'
  streams_name => 'A2_SCOTT_APPLY',
  queue_name   => 'A2_SCOTT_APPLY_Q',
  include_dml  => TRUE,
  include_ddl  => TRUE,
  include_taged_lcr => FALSE,
  dml_rule_name  => dml_rname,
  ddl_rule_name  => ddl_rname,
  inclusion_rule => TRUE,
  and_condition  => NULL);

  dbms_output.put_line('DML Rule Name: ' dml_rname);
  dbms_output.put_line('DDL Rule Name: ' ddl_rname);
END;
/

-- Now look in user_ views

 
Cleanup As Streams
on Alpha 1 on Alpha 2
exec dbms_streams_auth.grant_remote_admin_access('LOCALADMIN');

exec dbms_streams_auth.grant_admin_privilege( 'LOCALADMIN', TRUE);
 
 
Related Topics
Database Link
DBMS_OUTPUT
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
Export
Import
Streams Demo 2
User
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [135 users online]    © 2010 psoug.org