Oracle Streams for Near Real Time Asynchronous Replication

1
Oracle Streams for Near Real Time Asynchronous
Replication

• Nimar S. Arora
• Oracle USA

2
Overview

• Information Sharing
• Replication Uses
• Desirable Characteristics of Replications
• Misconceptions in Research
• Streams Replication
• Performance Assumptions
• Parallel Apply

3
Information Sharing

• Get information from those who have it and give
  to those who need it
• Capture and Consumption of Information
• Explicit Capture and Consumption is Messaging
• Implicit Capture and Consumption is Replication
• Cross Product
• Cross Platform

4
Replication Uses

• High Availability
• Disaster Recovery
• Data Warehouse Loading
• Online Upgrade
• Etc..

5
Desirable Characteristics of Replication

• Zero or low latency
• Easy to detect and resolve inconsistencies
• Zero or low impact on OLTP performance
• Online instantiation
• Keep up with any workload
• Large or small transactions
• DDLs and DMLs
• Flexible
• Arbitrary topology over LAN or WAN
• Arbitrary data filtering and transformations

6
Misconceptions in Research

• Inconsistencies are hard
• Asynchronous replication replicates a transaction
  after it commits on the source
• Synchronous replication doesnt hurt source
  performance

7
Streams Replication
Empno job .. 7901 sales 7902
coding ...
Update scott.emp set jobcoding where empno7902
EMP
EMP
Propagation
Queue
Queue
ack
Capture
Redo Log
Logical Change Record (LCR)
8
Performance Assumptions

• LCRs can be processed in memory faster than they
  can be written to storage
• Network bandwidth is comparable to storage
  bandwidth
• Network latencies are irrelevant when streaming
  data without acknowledgement
• Applying LCRs is slower than storage bandwidth
  (hence parallel apply is a must)

9
Parallel Apply

• Respect all database constraints
• Primary key
• Unique key
• Foreign key
• Respect commit order for non-database constraints
• For DDLs, respect table locks

10
Parallel Apply (sample schema)
SCOTT.EMP Name Type ------------------------
----------------------------------- EMPNO
NUMBER(4) ENAME VARCHAR2(10) JOB
VARCHAR2(9) MGR NUMBER(4) HIREDATE
DATE SAL NUMBER(7,2) COMM NUMBER(7,2)
DEPTNO NUMBER(2) OFFICE
NUMBER(3)
Primary Key -
Foreign Key -
Unique Key -
11
Parallel Apply (example)
T1 Insert emp .. (7902,.. T1 Commit
T2 update emp .. Empno7901 T2 update emp ..
Empno7902 T2 Commit
Source
T3 Insert emp .. (7904,.. T3 Commit
Apply
T3 insert emp .. 7904 T3 commit
T1 insert emp .. (7902,.. T1 commit
T2 update emp .. 7901 T2 update emp ..
7902 T2 commit
Replica
12
Parallel Apply (example)

• FOREIGN KEY
• T4 Insert into emp (empno, ..) values (7905,
  ..)
• Commit
• T5 Insert into emp(empno,mgr, ..) values (7906,
  7905..)
• Commit
• UNIQUE CONSTRAINT
• T6 update emp set office102 where
  office101
• Commit
• T7 update emp set office101 where
  office100
• Commit
• Supplemental logging at source for multi-column
  constraints

13
Parallel Apply (Dependency Hash Table)

• For each row change, each constraint, compute
  hash value
• Hash value hash of ltconstraint, column
  valuesgt
• Overwrite transaction id in each of the hash
  slots
• Depend upon prior transaction id in each of the
  hash slots
• Example T2 depends upon T1

xid
T2
7901
.
T2
7902
T1
14
A