Striim 3.9.4 / 3.9.5 documentation

Replicating Oracle data to another Oracle database

The first step in Oracle-to-Oracle replication is the initial load.

  1. Use select min(start_scn) from gv$transaction to get the SCN number of the oldest open or pending transaction.

  2. Use select current_scn from V$DATABASE; to get the SCN of the export.

  3. Use Oracle's exp or expdp utility, providing the SCN from step 2, to export the appropriate tables and data from the source database to a data file.

  4. Use Oracle's imp or impdp to import the exported data into the target database.

Once initial load is complete, the following sample application would continuously replicate changes to the tables SOURCE1 and SOURCE2 in database DB1 to tables TARGET1 and TARGET2 in database DB2 using DatabaseWriter. The StartSCN value is the SCN number from step 1. In  the WHERE clause, replace  ######### with the SCN from step 2.

CREATE SOURCE OracleCDC USING OracleReader (
  Username:'striim',
  Password:'******', 
  ConnectionURL:'10.211.55.3:1521:orcl1',
  Tables:'DB1.SOURCE1;DB1.SOURCE2', 
  Compression:true
  StartSCN:'...'
)
OUTPUT TO OracleCDCStream;

CREATE CQ FilterCDC
INSERT INTO FilteredCDCStream
SELECT x 
FROM OracleCDCStream x
WHERE TO_LONG(META(x,'COMMITSCN')) > #########;

CREATE TARGET WriteToOracle USING DatabaseWriter ( 
  ConnectionURL:'jdbc:oracle:thin:@10.211.55.3:1521:orcl1', 
  Username:'striim',
  Password:'******', 
  Tables:'DB1.SOURCE1,DB2.TARGET1;DB1.SOURCE2,DB2.TARGET2'
)
INPUT FROM FilteredCDCStream;

The FilterCDC CQ filters out all transactions that were replicated during initial load.

The following Oracle column types are supported:

  • BINARY DOUBLE

  • BINARY FLOAT

  • BLOB

  • CHAR

  • CLOB

  • DATE

  • FLOAT

  • INTERVAL DAY TO SECOND

  • INTERVAL YEAR TO MONTH

  • LONG

  • NCHAR

  • NUMBER

  • NVARCHAR

  • RAW

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

  • VARCHAR2

Limitations:

  • The primary key for a target table cannot be BLOB or CLOB.

  • TRUNCATE TABLE is not supported for tables containing BLOB or CLOB columns.

Understanding Oracle-to-Oracle DDL replication

Reading DDL transactions requires Oracle 11g and LogMiner. It is enabled by setting OracleReader's DictionaryMode property to OfflineCatalog.

When OracleReader output including DDL is written to another Oracle instance by DatabaseReader, changes to tables and other objects in a source database are duplicated in the corresponding objects in a target database. For example, if the source contains a table created with this DDL:

CREATE TABLE TEST (EMPID INT PRIMARY KEY, ENAME VARCHAR2(10));

and the following DDL was entered to add a column:

ALTER TABLE TEST ADD (SALARY NUMBER);

in both the source and target database the table's new description would be:

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPID                                     NOT NULL NUMBER(38)
ENAME                                              VARCHAR2(10)
SALARY                                             NUMBER

Future INSERT or UPDATE operations to the revised table will be handled correctly.

Oracle DDL cannot be replicated to other DatabaseWriter targets. If you wish to write data (DML) to a non-Oracle database using the same application, you may filter out the DDL with a CQ using a META(OperationType = 'DDL') function (see Using the META() function).

Mapping source objects to target objects

Before initiating DDL replication, you must create tables, indexes, views, and other objects in the target database with the same DDL properties as each object to be replicated from the source database. If necessary, the target objects may have different names (see the discussion of the Tables property in DatabaseWriter for details), but using the same names simplifies development and maintenance.

Specify source objects and map them to target objects using the Tables properties of OracleReader and DatabaseWriter. To replicate all objects in a schema, use wildcards: for example, in OracleReader, Tables:'HR.%' , and in DatabaseWriter, Tables:'HR.%,HR.%'

If you prefer, you may limit replication to selected objects by specifying their fully-qualified names: for example, in OracleReader, Tables:'HR.EMPLOYEES, HR.DEPARTMENTS' , and in DatabaseWriter, Tables:'HR.EMPLOYEES,HR.EMPLOYEES; HR.DEPARTMENTS,HR.DEPARTMENTS'

Functions,  indexes, packages,  procedures, and views must be specified individually. For example, to replicate HR.EMP_DETAILS_VIEWi and its underlying tables, you would specify Tables:'HR.EMPLOYEES, HR.DEPARTMENTS, HR.JOBS, HR.LOCATIONS, HR.COUNTRIES, HR.REGIONS, HR.EMP_DETAILS_VIEW' in OracleReader and Tables:'HR.EMPLOYEES,HR.EMPLOYEES; HR.DEPARTMENTS,HR.DEPARTMENTS; HR.JOBS,HR.JOBS; HR.LOCATIONS,HR.LOCATIONS; HR.COUNTRIES,HR.COUNTRIES; HR.REGIONS,HR.REGIONS; HR.EMP_DETAILS_VIEW,HR.EMP_DETAILS_VIEW' in DatabaseWriter.

If DatabaseWriter's Tables string includes a mapping for a view or index but not its underlying tables, DatabaseWriter will assume that the tables exist in the target schema. For instance, in the previous example, if you mapped only HR.EMP_DETAILS_VIEW, DatabaseWriter would assume that the six underlying tables exist in the target. However, instead of relying on this, we strongly recommend explicitly mapping the tables instead. Similarly, DatabaseWriter will assume that any unmapped objects referenced in a mapped function, package, or procedure exist in the target, but we strong recommend explicitly mapping all objects.

Objects may be replicated within a schema, in which case the target object's name must be different: for example, in  OracleReader, Tables:'HR.EMPLOYEES', and in DatabaseWriter, Tables:'HR.EMPLOYEESCOPY'. When the source and target are in same database instance, ANALYZE, GRANT, and REVOKE operations may result in an infinite loop, so you should filter them out with a CQ using functions such as META(OperationName = 'ANALYZE') (see Using the META() function).

Sample DDL replication application

After initial load and before running the replication application, run the following on the source database to dump the DDL to the LogMiner dictionary:

Execute DBMS_LOGMNR_D.BUILD(OPTIONS =>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

The following application will replicate both data and changes to the DDL for all replicable objects in database DB1 in the source to database DB2 in the target:

CREATE SOURCE OracleCDC USING OracleReader (
  Username:'striim',
  Password:'******',
  ConnectionURL:'10.211.55.3:1521:orcl1',
  Tables:'DB1.%',
  DictionaryMode:'OfflineCatalog'
)
OUTPUT TO OracleCDCStream;

CREATE TARGET WriteToOracle USING DatabaseWriter (
  ConnectionURL:'jdbc:oracle:thin:@10.211.55.3:1521:orcl1',
  Username:'striim',
  Password:'******',
  Tables:'DB1.%,DB2.%'
)
INPUT FROM OracleCDCStream;

It may take several minutes for Striim to read the LogMiner data, during which time the application's status will be DEPLOYING. When this process is complete, Striim writes a message to striim. server.log of the format Catalog object evolution is complete. OracleReader is positioned to read from followed by an SCN number, and the application's status changes to RUNNING.

Warning

Once you have initiated replication, the target objects evolve to match DDL changes to the source objects. Do not modify target objects directly.

You may begin replication from a particular transaction (see the discussion of startSCN  in Replicating Oracle data to another Oracle database). In that case, be sure that the specified SCN is from a position after the last DDL change to the source objects, and that the target objects reflect the current DDL of their source objects. Any DDL changes after that point will be applied to the target.

If you already have an application that replicates data (DML), as described in Replicating Oracle data to another Oracle database., you can simply change DictionaryMode to OfflineCatalog to replicate DDL as well. Note that you may need to modify the DatabaseWriter Tables property to add additional objects such as indexes and views.

Recovery considerations for Oracle-to-Oracle DDL replication

If objects were added or renamed by DDL replication, and the Tables properties do not use wildcards, you must edit the application to add all new and renamed objects to both the OracleReader and DatabaseWriter Tables properties before recovering the application.

See Recovering applications) for more information.