Oracle Streams can be used to share data and messgaes between two oracle databases at different levels Unlike Oracle dataguard logical standby database ,Oracle streams will have two or more databases independent of each other and similar at replication level only. Concept of Oracle Streams is similar to logical standby but streams is more popular.
for example --
1.Table level ( 1 or more tables are replicated between two Oracle databases)
2.Schema level ( 1 or more schemas are replicated between two Oracle databases)
3.Tablespace level ( 1 or more tablespaces are replicated between two Oracle databases)
4.Database level ( changes in one database are replicated in another database)
Oracle streams is flow of information from one database to another database by capturing information at one database ,staged and applied to another database.Oracle uses advance queuing concept to capture,propagate and apply the the data between databases.
Oracle streams setup can be
1.Homogeneous ( between two oracle databases)
2.Heterogeneous (between oracle and non oracle databases)
In thes post i have replicated a schema called TEST1 between two databases and have taken
1. Source database (SRCDB) on server DBA1
2.Target database (TGTDB) on server DBA2
On source database and target database do the follwing steps
--create a tablespace dedicated for stream
create tablespace strts datafile '/disk1/oradata/srcdir/strts.dbf' size 100m;
-- create stream admin user
create user stradm identified by stradm default tablespace strts;
-- grant dba and streams related privs to streams admin user
grant dba to stradm;
SYS>>exec dbms_streams_auth.grant_admin_privilege('STRADM');
At source and target databases
STRADM>>alter database add supplemental log data;
Database altered.
listner at source database
srclist =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba3)(PORT = 7546))
)
)
)
SID_LIST_srclist =
(SID_LIST =
(SID_DESC =
(SID_NAME = src)
(ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
)
)
listener at target side
tgtlist =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba4)(PORT = 7589))
)
)
)
SID_LIST_tgtlist=
(SID_LIST =
(SID_DESC =
(SID_NAME = tgt)
(ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
)
)
tns at source
to_tgtlist =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=dba4)(PORT=7589))
)
(CONNECT_DATA =
(SID = tgt)
)
)
tns at target side
to_srclist =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=dba3)(PORT=7546))
)
(CONNECT_DATA =
(SID = src)
)
)
streams parameter at source
###########source side streams parameters###################
db_unique_name=srcdb
log_archive_dest_1='location=/disk1/oradata/srcdir/arch valid_for=(online_logfiles,primary_role)mandatory'
log_archive_config='send,dg_config=(srcdb,tgtdb)'
log_archive_dest_2='service=to_tgtlist lgwr async noregister valid_for=(online_logfiles,primary_role) db_unique_name=tgtdb'
aq_tm_processes=1
shared_pool_size=400m
job_queue_interval=1
job_queue_processes=20
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
processes=150
statistics_level=typical
timed_statistics=true
streams_pool_size=500m
db_domain=DHARAM.com
log_archive_max_processes=10
global_names=true
target side parameter
############target streams parameters################
db_unique_name=tgtdb
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,primary_role)'
log_archive_config='receive,dg_config=(srcdb,tgtdb)'
log_archive_dest_2='location=/disk1/oradata/tgtdir/arch valid_for=(standby_logfiles,primary_role)'
aq_tm_processes=1
shared_pool_size=400m
job_queue_interval=1
job_queue_processes=20
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
processes=150
statistics_level=typical
timed_statistics=true
streams_pool_size=500m
db_domain=DHARAM.com
log_archive_max_processes=10
global_names=true
db_recovery_file_dest=/disk1/oradata/tgtdir/flash
db_recovery_file_dest_size=500m
on source side create password file and send to target side
[dharam@dba3 dbs]$ orapwd file=orapw$ORACLE_SID password=sys
[dharma@dba3 dbs]$ ls orapwsrc
orapwsrc
on target database
rename the password file according to instance name of target
[dharma@dba4 dbs]$ mv orapwsrc orapwtgt
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TGTDB
STRADM>>alter database rename global_name to tgtdb.DHARAM.com;
Database altered.
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TGTDB.DHARAM.COM
on source side create database link which points to target database.
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TGTDB.DHARAM.COM
STRADM>>create database link TGTDB.DHARAM.COM connect to stradm identified by stradm using 'to_tgtlist';
STRADM>>select * from dual@TGTDB.DHARAM.COM;
D
-
X
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB
STRADM>>alter database rename global_name to srcdb.DHARAM.com;
Database altered.
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB.DHARAM.COM
on target database
STRADM>>create database link SRCDB.DHARAM.COM connect to stradm identified by stradm using 'to_srclist';
Database link created.
STRADM>>select * from dual@SRCDB.DHARAM.COM;
D
-
X
on source
[dharam@dba3 ~]$ mkdir /disk1/oradata/srcdir/strdir
STRADM>>create directory strdir as '/disk1/oradata/srcdir/strdir';
Directory created.
on target
[dharma@dba4 ~]$ mkdir /disk1/oradata/tgtdir/strdir
STRADM>>create directory strdir as '/disk1/oradata/tgtdir/strdir';
Directory created.
STRADM>>alter database add standby logfile ('/disk1/oradata/tgtdir/str1.log','/disk1/oradata/tgtdir/str2.log','/disk1/oradata/tgtdir/str3.log') size 10m;
STRADM>>select group#,sequence#,status from v$standby_log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------
3 0 UNASSIGNED
in parameter file of source
log_archive_dest_state_2=enable
SYS>>startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 966729728 bytes
Fixed Size 1340552 bytes
Variable Size 943721336 bytes
Database Buffers 16777216 bytes
Redo Buffers 4890624 bytes
Database mounted.
Database opened.
STRADM>>alter system switch logfile;
System altered.
STRADM>>/
System altered.
STRADM>>select dest_id,status,error from v$archive_dest_status;
SYS>>grant connect,resource to test1 identified by test1;
Grant succeeded.
TEST1>>@?/sqlplus/demo/demobld
Building demonstration tables. Please wait.
Demonstration table build is complete.
on target side run the following
[dharma@dba4 ~]$ vi ms.sql
begin
dbms_streams_adm.maintain_schemas(
schema_names=>'test1',
source_database=>'SRCDB.DHARAM.COM',
source_directory_object=>'strdir',
destination_database=>'TGTDB.DHARAM.COM',
destination_directory_object=>'strdir',
capture_queue_name=>'DS_STREAMS_QUEUE',
apply_queue_name=>'DS_STREAMS_QUEUE',
bi_directional=>false,
include_ddl=>false);
end;
[dharma@dba4 ~]$ sqlplus stradm/stradm
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 14:39:33 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
STRADM>>@ms.sql
PL/SQL procedure successfully completed.
STRADM>>select username from dba_users;
User-Name
----------
OUTLN
SYS
SYSTEM
TEST1
STRADM
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM
9 rows selected.
TEST1>>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
Checking for DML replication
at source side
TEST1>>select count(*) from emp;
COUNT(*)
----------
14
at target side
TEST1>>select count(*) from emp;
COUNT(*)
----------
14
at source side
TEST1>>insert into emp select * from emp;
14 rows created.
TEST1>>commit;
Commit complete.
TEST1>>select count(*) from emp;
COUNT(*)
----------
28
at target side
TEST1>>select count(*) from emp;
COUNT(*)
----------
28
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
for example --
1.Table level ( 1 or more tables are replicated between two Oracle databases)
2.Schema level ( 1 or more schemas are replicated between two Oracle databases)
3.Tablespace level ( 1 or more tablespaces are replicated between two Oracle databases)
4.Database level ( changes in one database are replicated in another database)
Oracle streams is flow of information from one database to another database by capturing information at one database ,staged and applied to another database.Oracle uses advance queuing concept to capture,propagate and apply the the data between databases.
Oracle streams setup can be
1.Homogeneous ( between two oracle databases)
2.Heterogeneous (between oracle and non oracle databases)
In thes post i have replicated a schema called TEST1 between two databases and have taken
1. Source database (SRCDB) on server DBA1
2.Target database (TGTDB) on server DBA2
On source database and target database do the follwing steps
--create a tablespace dedicated for stream
create tablespace strts datafile '/disk1/oradata/srcdir/strts.dbf' size 100m;
-- create stream admin user
create user stradm identified by stradm default tablespace strts;
-- grant dba and streams related privs to streams admin user
grant dba to stradm;
SYS>>exec dbms_streams_auth.grant_admin_privilege('STRADM');
At source and target databases
STRADM>>alter database add supplemental log data;
Database altered.
listner at source database
srclist =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba3)(PORT = 7546))
)
)
)
SID_LIST_srclist =
(SID_LIST =
(SID_DESC =
(SID_NAME = src)
(ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
)
)
listener at target side
tgtlist =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba4)(PORT = 7589))
)
)
)
SID_LIST_tgtlist=
(SID_LIST =
(SID_DESC =
(SID_NAME = tgt)
(ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
)
)
tns at source
to_tgtlist =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=dba4)(PORT=7589))
)
(CONNECT_DATA =
(SID = tgt)
)
)
tns at target side
to_srclist =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=dba3)(PORT=7546))
)
(CONNECT_DATA =
(SID = src)
)
)
streams parameter at source
###########source side streams parameters###################
db_unique_name=srcdb
log_archive_dest_1='location=/disk1/oradata/srcdir/arch valid_for=(online_logfiles,primary_role)mandatory'
log_archive_config='send,dg_config=(srcdb,tgtdb)'
log_archive_dest_2='service=to_tgtlist lgwr async noregister valid_for=(online_logfiles,primary_role) db_unique_name=tgtdb'
aq_tm_processes=1
shared_pool_size=400m
job_queue_interval=1
job_queue_processes=20
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
processes=150
statistics_level=typical
timed_statistics=true
streams_pool_size=500m
db_domain=DHARAM.com
log_archive_max_processes=10
global_names=true
target side parameter
############target streams parameters################
db_unique_name=tgtdb
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,primary_role)'
log_archive_config='receive,dg_config=(srcdb,tgtdb)'
log_archive_dest_2='location=/disk1/oradata/tgtdir/arch valid_for=(standby_logfiles,primary_role)'
aq_tm_processes=1
shared_pool_size=400m
job_queue_interval=1
job_queue_processes=20
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
processes=150
statistics_level=typical
timed_statistics=true
streams_pool_size=500m
db_domain=DHARAM.com
log_archive_max_processes=10
global_names=true
db_recovery_file_dest=/disk1/oradata/tgtdir/flash
db_recovery_file_dest_size=500m
on source side create password file and send to target side
[dharam@dba3 dbs]$ orapwd file=orapw$ORACLE_SID password=sys
[dharma@dba3 dbs]$ ls orapwsrc
orapwsrc
on target database
rename the password file according to instance name of target
[dharma@dba4 dbs]$ mv orapwsrc orapwtgt
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TGTDB
STRADM>>alter database rename global_name to tgtdb.DHARAM.com;
Database altered.
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TGTDB.DHARAM.COM
on source side create database link which points to target database.
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TGTDB.DHARAM.COM
STRADM>>create database link TGTDB.DHARAM.COM connect to stradm identified by stradm using 'to_tgtlist';
STRADM>>select * from dual@TGTDB.DHARAM.COM;
D
-
X
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB
STRADM>>alter database rename global_name to srcdb.DHARAM.com;
Database altered.
STRADM>>select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB.DHARAM.COM
on target database
STRADM>>create database link SRCDB.DHARAM.COM connect to stradm identified by stradm using 'to_srclist';
Database link created.
STRADM>>select * from dual@SRCDB.DHARAM.COM;
D
-
X
on source
[dharam@dba3 ~]$ mkdir /disk1/oradata/srcdir/strdir
STRADM>>create directory strdir as '/disk1/oradata/srcdir/strdir';
Directory created.
on target
[dharma@dba4 ~]$ mkdir /disk1/oradata/tgtdir/strdir
STRADM>>create directory strdir as '/disk1/oradata/tgtdir/strdir';
Directory created.
STRADM>>alter database add standby logfile ('/disk1/oradata/tgtdir/str1.log','/disk1/oradata/tgtdir/str2.log','/disk1/oradata/tgtdir/str3.log') size 10m;
STRADM>>select group#,sequence#,status from v$standby_log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------
3 0 UNASSIGNED
in parameter file of source
log_archive_dest_state_2=enable
SYS>>startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 966729728 bytes
Fixed Size 1340552 bytes
Variable Size 943721336 bytes
Database Buffers 16777216 bytes
Redo Buffers 4890624 bytes
Database mounted.
Database opened.
STRADM>>alter system switch logfile;
System altered.
STRADM>>/
System altered.
STRADM>>select dest_id,status,error from v$archive_dest_status;
SYS>>grant connect,resource to test1 identified by test1;
Grant succeeded.
TEST1>>@?/sqlplus/demo/demobld
Building demonstration tables. Please wait.
Demonstration table build is complete.
on target side run the following
[dharma@dba4 ~]$ vi ms.sql
begin
dbms_streams_adm.maintain_schemas(
schema_names=>'test1',
source_database=>'SRCDB.DHARAM.COM',
source_directory_object=>'strdir',
destination_database=>'TGTDB.DHARAM.COM',
destination_directory_object=>'strdir',
capture_queue_name=>'DS_STREAMS_QUEUE',
apply_queue_name=>'DS_STREAMS_QUEUE',
bi_directional=>false,
include_ddl=>false);
end;
[dharma@dba4 ~]$ sqlplus stradm/stradm
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 14:39:33 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
STRADM>>@ms.sql
PL/SQL procedure successfully completed.
STRADM>>select username from dba_users;
User-Name
----------
OUTLN
SYS
SYSTEM
TEST1
STRADM
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM
9 rows selected.
TEST1>>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
Checking for DML replication
at source side
TEST1>>select count(*) from emp;
COUNT(*)
----------
14
at target side
TEST1>>select count(*) from emp;
COUNT(*)
----------
14
at source side
TEST1>>insert into emp select * from emp;
14 rows created.
TEST1>>commit;
Commit complete.
TEST1>>select count(*) from emp;
COUNT(*)
----------
28
at target side
TEST1>>select count(*) from emp;
COUNT(*)
----------
28
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
hello , great post!
ReplyDeleteI'm trying to replicate my test schema but at the last step y get this error:
ERROR at line 1:
ORA-23616: Failure in executing block 1 for script
280F6D66BBE12A3EE053E6E5720A35F2 with
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139
ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 485
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 8039
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2904
ORA-06512: at "SYS.DBMS_STREAMS_ADM_IVK", line 2965
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 931
ORA-06512: at line 2
Thanks for providing this informative information you may also refer.
ReplyDeletehttp://www.s4techno.com/blog/2016/11/07/stp-loop-guard/
Thank you for the great information. This post is awesome, nice written and includes almost all important information. Really helpful. I want to see a more helpful post like this in the future.
ReplyDeleteThank You