How to Migrate your Database to Oracle Exadata. Noam Cohen, Oracle DB Consultant, E&M Computing

(1)

How to Migrate your Database

to Oracle Exadata

How to Migrate your Database

to Oracle Exadata

Noam Cohen, Oracle DB Consultant,

E&M Computing

(2)

• Working with Oracle Since 2000

• Versions 8.0 – 11g

• Consulting on all areas – from

Infrastructure to Application

Development

• And now… Exadata

[email protected]

Who am I

(3)

• Migration Considerations

• Migration Strategy

– Physical – Logical

Agenda

(4)

• Migration Considerations

• Migration Strategy

– Physical – Logical

Agenda

(5)

• Oracle Exadata – Synergy between

Hardware and Software:

– Smart Scans

– Storage Indexes

– Hybrid Columnar Compression – Smart Flash Cache over Flash PCI – High Speed InfiniBand Backbone

Migration Considerations

(6)

• So how do you migrate to Exadata

Machine? Same as you’d migrate to a

traditional Oracle machine.

• Minimal downtime is a major

consideration.

• BUT – Never compromise Best Practice

Configurations

Migration Considerations

(7)

• Best Practice Considerations:

– Hybrid Columnar Compression. – Smart Scan VS. Index Scan.

– ORDER BY tables for better usage of Storage Indexes.

– ASM Diskgroups 4MB AU Size.

– Large Extents (8MB) for Large Segments.

Migration Considerations

(8)

Migration Methods

• Methods Overview

Logical Migration

•Data unloaded from source, loaded into

Exadata database w/ SQL

•Easier to migrate subset

•Easier to implement structural best practices

•Generally less restrictive

Physical Migration

•Data remains in datafiles (block-for-block)

•Most methods are whole database migration

•Generally more restrictive

(9)

Migration Methods

• Generally speaking: OLTP •Typical Strategy: –Structure intact •Migration method: –1st_{: Physical} –2nd_{: Logical} Data Warehouse •Typical Strategy: –Change Structure (remove indexes,

reorder tables etc.)

–Change Storage (new compression, optimize extent sizing)

–Change Platform (Big to Little Endian).

•Migration method:

–1st_{: Logical}

(10)

• Migration Considerations

• Migration Strategy

–

Physical

– Logical

Agenda

(11)

• Data remains in datafiles (block-for-block)

– Database extent sizes remain the same

• Most methods whole database migration

(except TTS)

– Inherit legacy database configuration

• indexes, MVs, no compression

• Stricter requirements

– Platform and version changes restricted

Physical Migration

(12)

• Best practice challenged

– Suboptimal sizing

– Migrate unnecessary objects

• Objects can be recreated post migration,

but

– Why not use logical method in the first place?

Physical Migration

(13)

• Methods at a Glance:

– ASM Rebalance to Exadata Storage

• Connect Exadata storage to existing database nodes

• ADD grid disks to existing ASM disk groups, DROP legacy storage from existing ASM disk groups

– Partition roll-in, roll-out to Exadata Storage • Connect Exadata storage to existing database nodes

• Only load into newly created partitions on Exadata storage • Drop old partitions from traditional storage

– Data Guard Physical Standby

• Create Physical Standby on Sun Oracle Database Machine • Data Guard switchover

Physical Migration

(14)

– Data Guard Physical Standby

• Create Physical Standby on Sun Oracle Database Machine

• Data Guard switchover

• Optionally Upgrade to 11.2 (Note #1055938.1)

– Transportable Database (Note:732053.1)

• RMAN CONVERT DATABASE ON TARGET to generate scripts.

• Transfer datafiles to Exadata Storage and Run scripts.

– Transportable Tablespace (TTS)

• Build empty 11.2 Exadata database

• TTS export source system metadata

• Transfer files to Exadata (CONVERT if source system big endian)

• TTS import metadata into Exadata database

Physical Migration

(15)

Physical Migration

Big endian source >= 10.1

Little endian source>=10.1, <11.2 Transportable

tablespaces

Little endian source on 11.2 Transportable database

Linux source on 11.2, archiving and LOGGING Data Guard

Physical Standby

Add Exadata storage to existing 11.2 Linux x86-64 database

Partition roll-in/out

Add Exadata storage to existing 11.2 Linux x86-64 database that uses ASM w/ 4MB AU

ASM rebalance

When to Use Method

(16)

• Migration Considerations

• Migration Strategy

– Physical

–

Logical

Agenda

(17)

• Data unloaded from source, loaded into

Exadata database w/ SQL

• Move only the user data

• Best practices can be added

– 4MB ASM AU size set for new disk groups – Large extents (8MB) for large database

segments

– Table compression, if desired

– Partitioning (added or changed), if desired

Logical Migration

(18)

– Data Guard Logical Standby

• Create logical standby on 11.2 Sun Oracle Database Machine

• Change table storage characteristics, as desired (Note:737460.1) – Data Pump Unload/Load

• Data Guard switchover

• Optionally – combine with Database Upgrade (Note #1055938.1)

– GoldenGate / Streams

• Create and upgrade replica on Sun Oracle Database Machine • Stop apply

• Implement best practices on replica (e.g. unload, recreate, reload) • Start apply to catch up

• Disconnect users from primary, reconnect to Sun Oracle Database Machine

– Data Pump

• Create Exadata database

• Import user data into Exadata using Data Pump

– Network mode -Direct import from source via dblink

– File mode -Export to dump file(s), transfer file(s), Import

Logical Migration

(19)

• Methods at a Glance: – Data Pump

• Create Exadata database

• Import user data into Exadata using Data Pump

– Network mode _{-Direct import from source via dblink}

– File mode _{-Export to dump file(s), transfer file(s), Import}

– CTAS / IAS

• Create Exadata database • CTAS or IAS

– From external tables in DBFS staging area

– From dblink to source database

Logical Migration

(20)

Logical Migration

Initial bulk load

CTAS / IAS

Data type restriction with other methods

Data Pump

Minimal downtime requirement Different source platform

Oracle GoldenGate / Streams

Rolling database upgrade requirement Table storage characteristics will be changed

Data Guard Logical Standby

When to Use Method

(21)

• In practice:

– Current DWs usually not on Linux x86-64 and not running 11g, so most physical methods eliminated

• Most DWs replaced by Exadata are running either Oracle on big-endian UNIX, or competitor (e.g. DB2,Netezza,Teradata)

– Customers only want tables with user data in order to implement new database

configuration determined during testing

Migration Methods

(22)

• Most common methods used thus far:

– Combination for staged migration

• CTAS/IAS or Data Pump for the initial bulk load into Exadata while source remains in use

– Perform daily loads (external tables) into both source and Exadata

• Initially users serviced by source database

• Move users over to Exadata

– Stop daily load into source

Migration Methods

(23)

• Noam Cohen

– [email protected]