Streams Demo 1


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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

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

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us