Transactional Data Management Solutions
Eliminating Database Downtime When Upgrading or Migrating to Oracle 10g
Background
Nick WagnerDirector of Product Management, GoldenGate Software
Transactional Data Management for Oracle and other databases
8 years of Product Management, primarily focused on Database Replication Solutions for High Availability, Disaster Recovery, Reporting, and Data Integration
5 Years – Product Manager for Quest SharePlex for Oracle
Agenda
GoldenGate Overview
What is Transactional Data Management?
High Availability Concerns: Upgrades and Migrations
Technology Choices and Trade-offs
Near-Zero Downtime Solution: Using Oracle XTTS and GoldenGate
Process for 9i 10g Cross-Platform migration
Failback Contingency
Post Migration: Data Verification
Summary, Q&A
About GoldenGate Software
Established, Loyal Customer Base
Leading Industry Solutions
250 customers... 1500+ solutions implemented… in 35 countries
18,000 Node ATM Network with 24/7
Availability
Saving $ millions with real-time DW
and zero downtime migrations.
Achieving paperless enterprise for this
visionary healthcareprovider
GoldenGate Software is a privately held software company that offers Transactional Data
Management solutions.
3.7 billion transactions
processed annually
2 Million Real-Time Transactions Per Day
Synchronized to Customer Websites
Transactional Data Management
TDM provides guaranteed capture, routing, transformation, delivery, and verification of data transactions across heterogeneous environments in real time.
TDM must be:
Real timeMoves with sub-second latency
Heterogeneous Moves transactions across different databases and platforms
TransactionalMaintains transaction integrity
GoldenGate differentiates on:
PerformanceHandles thousands of transactions per second with very low impact on IT systems
Extensibility & FlexibilityOpen architecture to meet demanding customer needs and data environments
ReliabilitySupports continuous operations and availability
HA/DR – Systematic View
Unplanned outage
System Failure
Data Failure
System Changes
Data Changes
Active
Database
Planned outage
Physical MediaLogical corruption
Node deathPower failure
UpgradesMigrations
Maintenance
1
2 3
Upgrades vs. Migrations
System Changes
Planned outage
UpgradesMigrations
Upgrade – Change of database version only
In place upgradesRolling upgrades (least amount of outage time)
Migration – Change in database vendor, platform, hardware
Challenges in HA Environments
Maintaining SLA during planned outage
Revenue ImpactCustomer ExpectationsInterdependencies, Integration
Data issuesInstantiating
Terabytes/PetabytesStaging areasChange ManagementSpecial Handling
Synchronization issuesIncremental data movementSource database impact
Failback strategySystem/Application verificationContinued data growth
Technology Choices for Oracle Migrations
“Traditional Solutions”…Export/ImportFlat files/SQL*LoaderData PumpSynchronous replicationBackup/Recovery
Transportable tablespacesCross-platform transportable tablespacesStandby databasesStreamsTransactional Data Management
Non mission-critical systems High availability systems
Technology Choices for Oracle Migrations
“Traditional Solutions”…Export/ImportFlat files/ SQL*LoaderData PumpSynchronous replicationBackup/Recovery
Migration time dependent on size of dataAssume a moderate to significant amount of planned downtimeSignificant overhead on the source databaseNo ongoing management of transactionsComplex, error prone, unmanageableNo real-time data verification strategyNo manageable failback strategy
Non mission-critical systems
Technology Choices for Oracle Migrations
Transportable TablespacesNo updates possibleNo incremental solutionNo failback solution
Standby Databases (Logical)No Rolling upgrade in 9iCannot be used for
heterogeneous migration/upgradeNo real time verification solution
StreamsRolling Upgrade not supported in
9iLimited Datatype support (e.g. no
LONG support in 9i)No real time verification solution
Transportable tablespacesCross-platform transportable tablespacesStandby databasesStreamsTransactional Data Management
High availability systems
Available Solutions/Techniques, Tradeoffs
Scenario Unload/
Load
Export/ Import
Backup/ Roll
Forward
Transportable
TableSpaces
Standby Databases
Dataguard Streams
TDM
9i 10g Yes Yes No Yes No No Yes
< 9i 10g Yes Yes No Yes No No Yes
9i 10g cross
platform
Yes Yes No No No No Yes
9i 10g RAC/ ASM
Yes Yes No Yes No No Yes
Non-Oracle 10g
Yes No No No No No Yes
DowntimeWeeks/Days Hours/Minutes Minutes/Seconds
Real TimeExtended downtime
Eliminating Downtime Using TDM
Transactional Data Management
9i 10g
Application Server
Oct 13, 200506:30:05
XTTS
Solaris Linux
Eliminating Downtime Using TDM
Transactional Data Management
9i 10g
Application Server
Oct 13, 200506:30:05
Oct 13, 200506:30:30*
* Depends on Application Switchover time
XTTS
Solaris Linux
Zero DB Downtime
<1 minute App switchover time
How GoldenGate TDM Works
Trail files: Stages and queues data for routing.
Delivery: Applies transactional data with guaranteed integrity.
Route: Data is compressed, encrypted for routing to targets.
Capture: Committed changes are captured (and can be filtered) as they occur by reading the transaction logs.
No Downtime Migration: 9i 10g Cross-Platform
9i Solaris
1. Start GoldenGate TDM’s Capture process2. Set up Clone database, then Upgrade to
10g 3. Cross platform transportable tablespaces
metadata export4. Use a full database NOROWS export
(Views, Packages, etc)
1
9i Clone 10g
Solaris23,4
No Downtime Migration: 9i 10g Cross-Platform
9i Solaris
9i Clone 10g
Solaris
10g
Linux
1. Start GoldenGate TDM’s Capture process
2. Set up Clone database, then Upgrade to 10g
3. Cross platform transportable tablespaces metadata export
4. Use a full database NOROWS export (Views, Packages, etc)
5. Set up a new 10g vanilla target
1
23,4
5
6,7
6. Cross platform transportable tablespaces metadata import
7. Full import with IGNORE option
No Downtime Migration: 9i 10g Cross-Platform
9i Solaris
9i Clone 10g
Solaris
10g
Linux
1. Start GoldenGate TDM’s Capture process
2. Set up Clone database, then Upgrade to 10g
3. Cross platform transportable tablespaces metadata export
4. Use a full database NOROWS export (Views, Packages, etc)
5. Set up a new 10g vanilla target
1
2
5
8
6. Cross platform transportable tablespaces metadata import
7. Full import with IGNORE option8. Start GoldenGate TDM Apply process at
target 9. **SWITCHOVER** (not depicted)
3,4
6,7
No Downtime Migration: 9i 10g Cross-Platform
9i Solaris
10g
Linux
1. Start GoldenGate TDM’s Capture process
2. Set up Clone database, then Upgrade to 10g
3. Cross platform transportable tablespaces metadata export
4. Use a full database NOROWS export (Views, Packages, etc)
5. Set up a new 10g vanilla target
1
5
8
6. Cross platform transportable tablespaces metadata import
7. Full import with IGNORE option8. Start GoldenGate TDM Apply process at target9. **SWITCHOVER** (not depicted)10. Start TDM Capture at target (new source)11. Start TDM Apply at target (old source)
10
11
9i Clone 10g
Solaris23,4
6,7
Failback contingency
Migration/Upgrade Detailed Steps1. Start GoldenGate TDM Capture process (captures consistent data point = QScn)
2. Do a point-in-time recovery of an existing backup until Qscn in a staging area. Call this database Dpitr.
3. Upgrade Dpitr to 10g on Solaris. Advance compatibility to 10.0 or higher.
4. Set up a vanilla 10g database on Linux. Call this database Dtarget.
5. Unplug the user tablespaces from Dpitr using the Oracle Cross Platform Transportable Tablespaces feature using source side endian conversion. Also take a NOROWS full export.
(Note the conversion would not be required if the endian systems were the same.)
6. Plug the set of tablespaces into Dtarget using the Cross Platform transportable tablespace feature.
7. Make the set if user tablespaces in Dtarget Read Write; Do a NOROWS import with IGNORE=Y option.
8. Start GoldenGate Apply process at Dtarget and synchronize up to the changes generated since Qscn.
9. Switchover the application from Dprod to Dtarget.
Migration/Upgrade with Failback
1. Start GoldenGate TDM Capture process (captures consistent data point = QScn)
2. Do a point-in-time recovery of an existing backup until Qscn in a staging area. Call this database Dpitr.
3. Upgrade Dpitr to 10g on Solaris. Advance compatibility to 10.0 or higher.
4. Set up a vanilla 10g database on Linux. Call this database Dtarget.
5. Unplug the user tablespaces from Dpitr using the Oracle Cross Platform Transportable Tablespaces feature using source side endian conversion. Also take a NOROWS full export.
(Note the conversion would not be required if the endian systems were the same.)
6. Plug the set of tablespaces into Dtarget using the Cross Platform transportable tablespace feature.
7. Make the set if user tablespaces in Dtarget Read Write; Do a NOROWS import with IGNORE=Y option.
8. Start GoldenGate Apply process at Dtarget and synchronize up to the changes generated since Qscn.
9. Start GoldenGate Capture on Dtarget.
10. Switchover the application from Dprod to Dtarget.
11. Start GoldenGate Apply on Dprod.
Addressing Failback
Stop application at new Primary (10g) Real-time TDM synchronization ensures old primary is
synchronized Switchover Application to old primary (9i) Start Primary database
9i Solaris
10g
Linux
GoldenGate Veridata™Comparisons run while data sources are kept onlineSupport for large data volumesSelective comparison optionsUnparalleled speed and efficiencyFlexible reporting for discrepancy analysis
Post Upgrade/Migration: Data Verification
GoldenGate Veridata: How it Works
The user chooses tables or files on the source and target databasesThe comparison is initiated from the GUI, command line or batchAs the databases continue to change, GoldenGate Veridata reports:
Persistent discrepancies In-flight data discrepancies (user configurable)
Key Technical Highlights
Rolling upgrade/migration using two databasesNo instantiation using primary databaseOffload any conversion to staging databaseSynchronize transactions across databasesVerify data replication and transactional integrityHave a failover strategy
TDM Upgrade/Migration Advantages
Real Time Allows for highest application availability
Heterogeneous
Allows movement across platforms/databases
Transactional Maintains transactional integrity
Performance No impact on source database
Downtime Only incurred during Application switchover
Verification Real time (dual) verification after migration
Failback With no data loss, in real time
Thank You
Q+AContact Information:
Phone: +1 415-777-0200301 Howard Street, Suite 2100, San Francisco, CA 94105
www.goldengate.com
Technology Environments Supported
Databases O/S and Platforms
OracleDB2 OS/390DB2 UDBMicrosoft SQL ServerMySQLEnscribeSQL/MPSQL/MXSybaseTeradata…and all ODBC compatible databases
UnixWindows NT, 2000, XPLinuxSun SolarisHP-UXIBM AIXHP NonStopTRU64IBM OS/390 and z/OS
In addition, GoldenGate's technology solutions offer open APIs that allow for access to custom data sources, data targets and adapters.