Streams Setup between oracle database.

Os : Windows XP
Version: 10.2.0.1
Database Mode: ArchiveLog

As per my understanding from oracle documentation; I configured this streams setup between two oracle databases.

Pre-Request Configuration:

Source Database Name : KnbDb
Downstream (Remote) database name: RDB

Before implementing streams you should configure the below database parameter in source and remote database.

SQL> show parameter db_unique

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string knbdb

SQL> show parameter global

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=(D:\ArchiveLog\KnbDb)


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable

SQL> show parameter archive

NAME TYPE VALUE
------------------------------------ ----------- ----------------------
archive_lag_target integer 1800
log_archive_config string dg_config=(knbdb,rdb)


SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

PS: my database configured automatic shared management; It’s automatically take care streams pool size

Database Configuration

Once completed your pre-request configuration; three are few steps you need to configure.

1. Create Steams Tablespace

CREATE TABLESPACE Streams DATAFILE '/usr/oracle/dbs/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Create Steams User

CREATE USER strmadmin IDENTIFIED BY admin
DEFAULT TABLESPACE streams
QUOTA UNLIMITED ON streams;

GRANT DBA TO strmadmin;

3. Grant Admin Privilege

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/


CREATE DIRECTORY admin_dir AS '/usr/admin';

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => false,
file_name => 'grant_strms_privs.sql',
directory_name => 'admin_dir');
END;
/


Run the GRANT_ADMIN_PRIVILEGE procedure to generate a script named grant_strms_privs.sql and place this script in the /usr/admin directory on your computer system:

.
4. Execute the script in SQL*Plus:

SET ECHO ON
SPOOL grant_strms_privs.out
@/usr/admin/grant_strms_privs.sql
SPOOL OFF


PS: As per your environment repeat the above configuration from downstream and source database.

Network Configuration

Source Database:

1. Connect Strmadmin User

2. Create database link which pointing to downstream database

Remote database:

1. Connect Strmadmin User

2. Create database link which pointing to source database

Script:

Create database link <> Connect to <> identified by <> using <>