Oracle Streams Configuration

  • PRIM.ODS.COM = Global Database name of the Source (capture) Site
  • STAN.ODS.COM = Global Database name of the Target (apply) Site
  • STRMADMIN = Streams Administrator with password strmadmin
  • Global Names parameter should be set to true
Create STRMADMIN User on both Sites (PRIM & STAN)
ALTER system SET global_names=TRUE scope=BOTH;

CREATE USER strmadmin IDENTIFIED BY strmadmin;

ALTER USER STRMADMIN DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA TO STRMADMIN;

EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege =>;;; DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee =>;;; 'strmadmin',
grant_option =>;;; FALSE); END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege =>;;; DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee =>;;; 'strmadmin',
grant_option =>;;; FALSE);
END;
/
Update tnsnames.ora with connection entries
STAN.ODS.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAN.ODS.COM)
)
)

PRIM.ODS.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM.ODS.COM)
)
)
STAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAN)
)
)

PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)
Create DB Links on both sites (PRIM & STAN)
conn sys/oracle@prim.ods.com AS sysdba

CREATE public DATABASE link stan.ods.com USING 'stan.ods.com';

conn strmadmin/strmadmin@prim.ods.com

SELECT USER FROM dual@stan.ods.com;
USER
------------------------------
STRMADMIN

CREATE DATABASE link stan.ods.com CONNECT TO strmadmin IDENTIFIED BY strmadmin;

conn sys/oracle@stan.ods.com AS sysdba

CREATE public DATABASE link prim.ods.com USING 'prim.ods.com';

conn strmadmin/strmadmin@stan.ods.com

CREATE DATABASE link prim.ods.com CONNECT TO strmadmin IDENTIFIED BY strmadmin;

SELECT USER FROM dual@prim.ods.com;
USER
------------------------------
STRMADMIN
Create Streams Capture & Apply Queue
CONNECT strmadmin/strmadmin@prim.ods.com
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name =>;; 'STREAMS_QUEUE',
queue_table =>;;'STREAMS_QUEUE_TABLE',
queue_user =>;; 'STRMADMIN');
END;
/

conn strmadmin/strmadmin@stan.ods.com

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name =>;; 'STREAMS_QUEUE',
queue_table =>;;'STREAMS_QUEUE_TABLE',
queue_user =>;; 'STRMADMIN');
END;
/

CONNECT strmadmin/strmadmin@prim.ods.com

BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
streams_name =>;; 'STRMADMIN_PROP',
source_queue_name =>;; 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name =>;; 'STRMADMIN.STREAMS_QUEUE@stan.ods.com',
include_dml =>;; TRUE,
include_ddl =>;; TRUE,
source_database =>;; 'prim.ods.com');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
streams_type =>;; 'CAPTURE',
streams_name =>;; 'STRMADMIN_CAPTURE',
queue_name =>;; 'STRMADMIN.STREAMS_QUEUE',
include_dml =>;; TRUE,
include_ddl =>;; TRUE,
source_database =>;; 'prim.ods.com');
END;
/

CONNECT strmadmin/strmadmin@stan.ods.com

BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
streams_type =>;;; 'APPLY',
streams_name =>;;; 'STRMADMIN_APPLY',
queue_name =>;;; 'STRMADMIN.STREAMS_QUEUE',
include_dml =>;;; TRUE,
include_ddl =>;;; TRUE,
source_database =>;;; 'prim.ods.com');
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name =>;; 'STRMADMIN_APPLY',
parameter =>;; 'disable_on_error',
VALUE =>;; 'n');
END;
/
Export & Import the database from PRIM to STAN
exp USERID=SYSTEM/oracle@prim.ods.com FULL=Y FILE=full.dmp LOG=exp.log OBJECT_CONSISTENT=Y STATISTICS = NONE

Copy the dump file to the standby server and start the import

imp USERID=SYSTEM/oracle@stan.ods.com CONSTRAINTS=Y FULL=Y FILE=full.dmp IGNORE=Y COMMIT=Y LOG=imp.log STREAMS_INSTANTIATION=Y
Start the Capture & Apply process
conn strmadmin/strmadmin@stan.ods.com

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name =>;;; 'STRMADMIN_APPLY');
END;
/
conn strmadmin/strmadmin@prim.ods.com

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name =>;;; 'STRMADMIN_CAPTURE');
END;
/

Try to create a user on the primary database and see if it replicates to the standby database.
How to Recover Streams After Dropping Propagation [ID 749181.1]
How to setup Database Level Streams Replication [ID 459922.1]
Streams Configuration Report and Health Check Script [Doc ID 273674.1]

Tagged with →  
Share →
0 comments
Skip to toolbar