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); |
|
|
|