Oracle Streams
Oracle Streams allows data propagation between Oracle Databases (homogeneous) and between Oracle and non-Oracle databases (heterogeneous environment).
Oracle Streams can be used for:
- Replication
- Message Queuing
- Loading data into a Data Warehouse
- Event Notification
- Data Protection
History
Oracle Streams was first shipped with Oracle 9i release 9.2.
Oracle Streams is only available with the Oracle Database Enterprise Edition.
Oracle 11gR2 is the last release supporting Streams. Oracle now recommends to use Golden Gate.
Components of Oracle Streams
Oracle Streams consists of the following components:
Capture
Database changes (DDL and DML) are captured from the redo logs and packaged into Logical Change Records or LCRs. The LCRs are then moved into the Staging environment. Data and events may be changed or formatted by a predefined set of rules before they are packaged into an LCR.
Your database must be in ARCHIVE log mode.
alter database archivelog;
Additionally, you may also need to enable supplemental logging for the database to ensure all changes are captured in the log files:
alter database add supplemental log data (primary key, unique index) columns;
You also need to enable force logging to ensure NOLOGGING operations are considered.
alter database force logging;
Create a queue table - used to queue captured changes:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strm_scott_qtable',
storage_clause => 'storage (initial 10M next 10M maxextents unlimited)',
queue_name => 'strm_scott_queue',
queue_user => 'scott');
END;
/
Staging
LCRs are stored in the Staging environment until a subscriber picks them up to be used or consumed. The subscriber may be another staging environment or a user application.
Consumption
During Consumption, LCRs are picked up and applied to a database. Consumption allows the LCR to be modified before it is applied to the database.
Replication at schema level
Oracle Streams can be used to share data and messages between two oracle databases at different levels. Oracle streams will have two or more databases independent of each other and similar at replication level only.
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 this example, we will replicate a schema called NASA between two databases and have taken
1. Source database (TESTDB) on server DBA1
2. Target database (DEMODB) on server DBA2
On source database and target database do the following steps (TESTDB):
SQL> create tablespace strts datafile '/disk1/oradata/srcdir/strts.dbf' size 100m;
SQL> create user strmadmin identified by strmadmin default tablespace strts;
SQL> grant dba to strmadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('STRMADMIN');
With user STRMADMIN:
SQL> alter database add supplemental log data;
On target database do the following steps (DEMODB):
SQL> create tablespace strts datafile '/disk1/oradata/srcdir/strts.dbf' size 100m;
SQL> create user strmadmin identified by strmadmin default tablespace strts;
SQL> grant dba to strmadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('STRMADMIN');
With user STRMADMIN:
SQL> alter database add supplemental log data;
listener.ora at source database (TESTDB):
TESTDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba1)(PORT = 7546))
)
)
)
SID_LIST_TESTDB =
(SID_LIST =
(SID_DESC =
(SID_NAME = TESTDB)
(ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
)
)
listener.ora at target side (DEMODB):
DEMODB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba2)(PORT = 7589))
)
)
)
SID_LIST_DEMODB=
(SID_LIST =
(SID_DESC =
(SID_NAME = DEMODB)
(ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
)
)
tnsnames.ora at source (TESTDB):
to_DEMODB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=dba2)(PORT=7589))
)
(CONNECT_DATA =
(SID = DEMODB)
)
)
tnsnames.ora at target side (DEMODB):
to_testdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=dba1)(PORT=7546))
)
(CONNECT_DATA =
(SID = testdb)
)
)
Streams parameter at source (TESTDB):
###########source side streams parameters################### db_unique_name=testdb log_archive_dest_1='location=/disk1/oradata/srcdir/arch valid_for=(online_logfiles,primary_role)mandatory' log_archive_config='send,dg_config=(testdb,demodbdb)' log_archive_dest_2='service=to_tgtlist lgwr async noregister valid_for=(online_logfiles,primary_role) db_unique_name=demodb' 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=oracle.com log_archive_max_processes=10 global_names=true
Streams parameter at target (DEMODB):
############target streams parameters################ db_unique_name=demodb log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,primary_role)' log_archive_config='receive,dg_config=(testdb,demodb)' 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=oracle.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:
$ orapwd file=orapw$ORACLE_SID password=sys
On target server, rename the password file according to instance name of target:
$ mv orapwTESDB orapwDEMODB
Log on to STRMADMIN:
SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- DEMODB SQL> alter database rename global_name to demodb.oracle.com; Database altered. SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- DEMODB.ORACLE.COM
On source side create database link which points to target database.
SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- DEMODB.ORACLE.COM SQL> create database link DEMODB.ORACLE.COM connect to strmadmin identified by strmadmin using 'to_demodb'; SQL> select * from dual@DEMODB.ORACLE.COM; D - X SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- TESTDB SQL> alter database rename global_name to testdb.oracle.com; Database altered. SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- TESTDB.ORACLE.COM
On target database (DEMODB):
SQL> create database link TESTDB.ORACLE.COM connect to stradm identified by stradm using 'to_tesdb'; Database link created. SQL> select * from dual@TESTDB.ORACLE.COM; D - X
On source database (TESDB):
$ mkdir /disk1/oradata/srcdir/strdir
SQL> create directory strdir as '/disk1/oradata/srcdir/strdir'; Directory created.
On target database (DEMODB):
$ mkdir /disk1/oradata/tgtdir/strdir
SQL> create directory strdir as '/disk1/oradata/tgtdir/strdir';
Directory created.
SQL> alter database add standby logfile ('/disk1/oradata/tgtdir/str1.log','/disk1/oradata/tgtdir/str2.log','/disk1/oradata/tgtdir/str3.log') size 10m;
SQL> 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
On source side run the following (TESTDB):
SQL> 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. SQL> alter system switch logfile; System altered.
On target side run the following (DEMODB):
begin
dbms_streams_adm.maintain_schemas (
schema_names=>'nasa',
source_database=>'testdb.oracle.COM',
source_directory_object=>'strdir',
destination_database=>'demodb.oracle.COM',
destination_directory_object=>'strdir',
capture_queue_name=>'DS_STREAMS_QUEUE',
apply_queue_name=>'DS_STREAMS_QUEUE',
bi_directional=>true,
include_ddl=>true);
end;
/
Some Streams Notes
DECLARE iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@A(
source_object_name => 'schema.table',
source_database_name => 'B',
instantiation_scn => iscn);
END;
/
BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => \'schema.table\'); END; /
