Data Replication Using Oracle Downstream Capture

The ever increasing demand of sharing, consolidating and
synchronizing critical enterprise data and the ability to provide secondary
access to updated information without compromising production system performance
require a technology like Oracle Streams.

Oracle Streams is versatile, robust and high performance
information distribution software used for sharing data across multiple Oracle
or heterogeneous database systems. It uses Oracle’s LogMiner-based technology
to extract database changes from the redo log files.

This article describes how to effectively leverage and set
up Oracle archived-log downstream capture technology to replicate your
enterprise data.

Oracle Downstream Capture

Data can basically be captured on a local database where the
capture process runs on the source database or on a remote database where the
capture process runs on a database other than the source database also called
the downstream database.

Oracle downstream capture feature was introduced in Oracle
10g Release 1, to shift the data capturing operations from the source database
to the downstream database. This relieves the system resources on the
production database server for other critical activities.

With archived-log downstream capture, archived redo logs
from the source database are shipped to the downstream database using any copy
mechanisms such as the redo transport services or file transfer protocol (FTP).
The capture process scans and captures DML/DDL changes from the archived redo
logs based on a set of defined rules, formats the captured changes into events
called logical change records (LCRs) and queues them in a staging queue. A
propagation process propagates the LCRs to a destination queue on the destination
database and they are then dequeued and applied by the apply process.

The disadvantage of archived-log based downstream capture is
that database changes on the source are not reflected immediately on the
destination database. Changes in the online redo logs cannot be propagated to
the destination database until they are archived. If the latency of data
capture is not acceptable, explore using real-time downstream capture.
Real-time downstream capture however, does not allow you to capture changes
from multiple source databases on the same downstream database. Refer to http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14229/strms_capture.htm#sthref170
for a description of real-time downstream capture.

Configure Archived-Log Downstream Capture

Typically, the downstream database and the destination
database are hosted on different systems. If you want to avoid the overhead of
maintaining a downstream database and additional database licensing costs, an
alternate solution is to set up the downstream capture process in the
destination database.

We will illustrate the procedure to set up an environment
where the downstream database is also the destination database and replicate a
schema on the source database to the destination database. The steps listed in
this section are also applicable if the downstream and destination databases
are located on different systems.

An overview of the Oracle Streams environment:

Step 1: Configure archive log mode

Verify ladb is in archive log mode. Archived redo logs are
required by the capture process to capture changes.

Step 2: Modify initialization parameters

Step 3: Set up tnsnames.ora

Add the TNS entries on trout3 and trout4.

LADB.WORLD =

    (DESCRIPTION =

         (ADDRESS = (PROTOCOL = TCP)(HOST = trout3)(PORT = 1521))

         (CONNECT_DATA =

         (SERVER = DEDICATED)

          (SERVICE_NAME = ladb)

         )

    )

SFDB.WORLD =

     (DESCRIPTION =

         (ADDRESS = (PROTOCOL = TCP)(HOST = trout4)(PORT = 1521))

         (CONNECT_DATA =

         (SERVER = DEDICATED)

              (SERVICE_NAME = sfdb)

         )

    )

Step 4: Create Streams administrator

Create a Streams administrator, strmadmin, with the required
administrator privileges on ladb and sfdb. You should create a tablespace on sfdb
to exclusively house the Streams administrator’s tables and indexes. The
strmadmin user on ladb however, does not create any database objects. The account
is used to perform administrative tasks such as objects instantiation and
obtaining the first System Change Number (SCN) for the capture process at the
downstream database.

create tablespace streamsts datafile ‘/u02/oradata/sfdb/streamsts01.dbf’ size 100M;

create user strmadmin identified by strmadmin default tablespace streamsts temporary tablespace temp;

grant dba, select_catalog_role to strmadmin;

exec dbms_streams_auth.grant_admin_privilege (‘strmadmin’, true);

Step 5: Create application schema

Assuming that there is already an existing apps schema in ladb,
create the destination apps schema on sfdb.

create tablespace appsts datafile '/u02/oradata/sfdb/appsts01.dbf' size 100M; 
create user apps identified by apps default tablespace appsts temporary tablespace temp;
grant connect, resource to apps;

Step 6: Enable table level supplemental logging

Set up supplemental logging as the apps user on ladb.
Supplemental logging logs additional columns information into the redo logs for
row identification at the destination database.

The following examples enable supplemental logging for the
customer and orders tables:

alter table customers add supplemental log data (primary key, unique) columns;
alter table orders add supplemental log data (primary key, unique) columns;

Step 7: Create database link

As the Streams administrator, create a private database link
from sfdb to ladb. The Streams administrator uses the database link to perform
administrative tasks on ladb.

create database link ladb.world connect to strmadmin identified by strmadmin using 'ladb.world';

Step 8: Create Streams queues

Database changes are captured in queues and propagated to
other databases. Create the capture queue on the downstream database and the
apply queue on the destination database. In this example, since the downstream
database is also the destination database, create both the capture and apply
queues on sfdb.

connect strmadmin/strmadmin@sfdb.world

begin

     dbms_streams_adm.set_up_queue(

 queue_table  => ‘capture_sfqtab’,

 queue_name   => ‘capture_sfq’,

 queue_user   => ‘strmadmin’);

end;

/

begin

     dbms_streams_adm.set_up_queue(

      queue_table  => ‘apply_sfqtab’,

 queue_name   => ‘apply_sfq’,

 queue_user   => ‘strmadmin’);

end;

/

Step 9: Create capture process

Use the create_capture procedure to create the capture
process, capture_sfstrm on the downstream database, sfdb. The capture process
executes the following activities on ladb – extracts the data dictionary to the
redo log, prepares objects instantiation and gets the first SCN.

connect strmadmin/strmadmin@sfdb.world

begin

 dbms_capture_adm.create_capture (

 queue_name  => ‘capture_sfq’,

 capture_name  => ‘capture_sfstrm’,

 source_database => ‘ladb.world’,

 use_database_link => true);

end;

/

Step 10: Configure capture process

Rules are added in positive or negative rule sets using the dbms_streams_adm
package. Changes are captured using positive rule set by specifying a true
value for the inclusion_rule parameter. In the add_schema_rules procedure
below, we specify the previously created capture streams name and queue name
and create a positive rule set for the capture process to extract DML and DDL
changes for the apps schema.

connect strmadmin/strmadmin@sfdb.world

begin

 dbms_streams_adm.add_schema_rules (

 schema_name  => ‘apps’,

 streams_type  => ‘capture’,

 streams_name  => ‘capture_sfstrm’,

 queue_name  => ‘capture_sfq’,

 include_dml  => true,

 include_ddl  => true,

 source_database => ‘ladb.world’,

 inclusion_rule => true);

end;

/

Step 11: Configure propagation process

The configuration for the propagation process is similar to
the capture process. We add rules to the positive rule set and specify the
source queue name and destination queue name. Changes are propagated from the
source queue to the destination queue.

connect strmadmin/strmadmin@sfdb.world

begin

 dbms_streams_adm.add_schema_propagation_rules (

 schema_name  => ‘apps’,

 streams_name  => ‘prop_sfdb_to_sfdb’,

 source_queue_name => ‘capture_sfq’,

 destination_queue_name => ‘apply_sfq’,

 include_dml  => true,

 include_ddl  => true,

 source_database => ‘ladb.world’);

end;

/

Step 12: Create objects for the destination application schema

On the source system:

1. As the system user, obtain the current SCN. This SCN is
used later in the expdp command.

      select dbms_flashback.get_system_change_number() from dual;

2. As the system user, create the Oracle directory for the
export dump file. Make sure the physical directory exists on the filesystem.

     create directory expdir as '/u01/app/oracle/admin/ladb/export';   

3. Export the application schema.

$> expdp system/oracle schemas=apps  directory=expdir 
          logfile=expapps_ds.log dumpfile=apps_ds.dmp flashback_scn=<the current SCN 
          obtained above>
    

On the destination system:

1. As the system user, create the Oracle directory for the
export dump file. Make sure the physical directory exists on the filesystem.

     create directory impdir as '/u01/app/oracle/admin/sfdb/import';

2. Copy the export dump file, apps_ds.dmp from the source
system to /u01/app/oracle/admin/sfdb/import.

3. Import the application schema:

     $> impdp system/oracle directory=impdir dumpfile=apps_ds.dmp logfile=impapps_ds.log

Step 13: Configure apply process

Create the apply process and add rules to the positive rule
set. The apply process dequeues the LCR events and applies the changes to the
destination schema.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_streams_adm.add_schema_rules (
  schema_name => 'apps',
  streams_type => 'apply',
  streams_name => 'apply_sfstrm',
  queue_name => 'apply_sfq',
  include_dml => true,
  include_ddl => true,
  source_database => 'ladb.world');  
end;
/

Configure the apply process to continue running even when
there are errors during the apply process.

begin
 dbms_apply_adm.set_parameter (
  apply_name => 'apply_sfstrm',
  parameter => 'disable_on_error',
  value => 'n');
end;
/

Step 14: Start apply process

Start the apply process at the destination database.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_apply_adm.start_apply (
  apply_name => 'apply_sfstrm');
end;
/

Step 15: Start capture process

Start the capture process at the downstream database.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_capture_adm.start_capture (
  capture_name => 'capture_sfstrm');
end;
/

Step 16: Test Oracle Streams

The Streams environment is now ready to capture, propagate
and apply changes from the source database to the destination database.

SQL> connect apps/apps@sfdb.world
Connected.
SQL> select * from customers;
      CUST_ID NAME    EMAIL           
---------- ------- --------------- 
         1 MIKE    mike@netk.com
         2 JOE     joe@apg.net
         3 MARY    mary@gq.org
SQL> select * from orders;
  ORDER_ID ORDER_DAT CUSTOMER_ID ORDER_TOTAL
---------- --------- ----------- -----------
         1 26-JAN-06           1       100.5
         2 27-JAN-06           2          90
         3 28-JAN-06           3        80.5

Perform DML and DDL changes on the source database:

SQL> connect apps/apps@ladb.world
Connected.
SQL> insert into customers values (4,'JAY','jay@defg.com');
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table orders;
Table truncated.
SQL> alter table customers add (address varchar2(20));
Table altered.
SQL> alter system switch logfile;
System altered.

Verify that changes are applied on the destination database:

SQL> connect apps/apps@sfdb.world
Connected.
SQL> select * from customers;
      CUST_ID NAME    EMAIL           ADDRESS
---------- ------- --------------- --------------------
         1 MIKE    mike@netk.com
         2 JOE     sue@apg.net
         3 MARY    mary@gq.org
         4 JAY     jay@defg.com
SQL> select * from orders;
no rows selected

Troubleshooting/Monitoring

Listed here are a few views for obtaining information on the
capture, propagation and apply processes. They provide information such as the
status of the processes, the number of messages enqueued and dequeued and the
error messages encounter during capture and apply.

• v$streams_apply_reader
• v$streams_apply_coordinator
• v$streams_capture
• dba_apply
• dba_apply_error
• dba_capture
• dba_propagation
• dba_queue_schedules

Conclusion

With Oracle Streams’ replication, information can be shared
easily among multiple databases. This article focuses on archived-log
downstream capture solution. Alternatively, Oracle Streams supports local
capture and real-time downstream capture, which are also easy to implement.
Hopefully this article has presented a straightforward and concise overview of
Oracle downstream capture and its capabilities.