Transcript View Presentation Online - Mark Gurry & Associates

MGA Duplica
Replication Tool
Why Replication?
1.
High Availability and Avoidance of Data Loss

2.
Split activities across databases (one database
OLTP and the other Reports)

3.
Replicate to alternate databases
It is often difficult to achieve acceptable performance when
OLTP users are mixed with Report users
Populate Web databases outside main OLTP
database

The main database must be secure
Why Replication?
4.
5.
Simplification

Summarisation and aggregation of data to speed performance
and reduce development and maintenance time

Similar to materialized views but without the performance and
administration overhead of the materialized view logs and
without the restrictions of the materialized views
Disaster Recovery

Populate a Disaster Recovery Database

Some sites also use the DB for reporting

Decoupling for High Availability

Avoids one database going down impacting others when
database links are used
Regional
Database
Reporting
Database
Source
Database
Firewall
Web
Database
Hot
Standby
Why Duplica?
1. Reliable Replication
 Duplica has been replicating from the mission critical
24 * 7 Australian Electricity market database to 38
external power companies for many years
 A wide variety of site use the product
 Proven technology used by banks, stock exchanges
and other companies with large volumes of
transactions
Why Duplica?
2. Fast Replication with little impact on
source database

Replicates in parallel using multi-threading and array
processing

Benchmarks indicate 18,000,000 inserts and 3,000,000
updates and deletes per hour for each thread

You can set a row limit to ease the impact of the data
transfer
Why Duplica?
3. Versatility
 Runs beautifully on RAC
 Runs on any operating system that the Oracle
RDBMS supports
 Can also replicate to SQL*Server
Why Duplica?
4. Allows staggered replication
 Protects your network and source database
performance
 Replicate critical data right now and non critical data
overnight
 Allows real time replication on all critical tables
Why Duplica?
5. Vertical and Horizontal partitioning

Why replicate all the data when only certain columns and
rows are required

Allows the enforcement of security with sites being
replicated to only replicating data that they are entitled to
see

Allows you to encrypt data for confidentiality

Allows you to selectively replicate your regional data, for
example, replicate Californian data to your local database
from the national database
Why Duplica?
6. Fast failover and failback
 Have choice of timestamp replication or log based
replication
 Both allow you to easily see data that is missing after
a failover and failback
 You cannot lose data with Duplica based replication
Why Duplica?
6. Simplifies Change Management
 Dynamically generates the SQL to insert, update and
delete at the source database, bringing only the
columns that it needs
 This allows delayed applying of DDL at the target
databases
Why Duplica?
7. Easy Administration
 Turning tables to replicate on and off is a breeze
 The product runs in robot mode with many sites
running the product without a DBA
 Duplica contains extensive health checks and
auditing when they are required
The Duplica Architecture
The Architecture
•
Duplica uses Suites to Replicate data
•
You enter Related Tables into a Suite
•
You will typically have many Suites
•
Tables are given a sequence within the Suite to
indicate the order of replication
•
•
Must maintain referential integrity
Suites can be replicated at different intervals
The Architecture
•
Duplica detects the data to replicate using
either
•
A LOG table that is populated by a trigger
OR
•
•
Using a Timestamp column on the source tables (if
one exists)
If rows are deleted from tables, you must use
the LOG table approach
The Architecture
•
Sample LOG table
•
CREATE TABLE "GDS"."STR_LOG“
(
DML_TIMESTAMP
DATE
,
DML_COPY
VARCHAR2(1)
,
DML_OPERATION
VARCHAR2(1)
,
DML_COUNT
NUMBER
,
SYS_DB_ID
NUMBER
,
SYS_PROD_ID
NUMBER
)
The Architecture
•
Duplica in installed into the Target Database
•
The installation includes a number of tables, indexes,
views, sequences and stored procedures
•
Data is pulled from source to the target using the
PL/SQL stored procedures
•
Duplica uses a Delphi front end
•
Multiple DBAs may have the front end installed in their
PCs. They will all access the same Duplica objects in the
target databases
•
DBMS_JOBS is used to schedule the replication of
suites
The Architecture
•
Some sites replicate from one database to
another and then from the target database to a
3rd database
•
This is used for Disaster Recovery as well as
creating multiple User Acceptance or Test
Database with real data
The User Interface
Suites are set up with one
or more tables
Audit the row counts at the
Source and target database
Schedule and unschedule
the suite with ease
Place tables into suite
Timestamp column
Download order
provides referential
integrity
Place your WHERE
criteria here for the
table. You can also
replicate from views
Duplica puts replication, database and error and audit logs
at your fingertips