Transcript Migration to Exadata

Migration to Exadata
Arup Nanda
Longtime DBA and now DMA
What Is This Session About?
• Various Methods for Migrating Application from non-Exadata to
Exadata
• Will
– use only three commonly used methods
– skip well understood methods, e.g. backup/restore
– discuss tips and gotchas
Migration to Exadata
2
Why Migration is Necessary
• Exadata is
– Oracle 11.2 Database
– ASM Storage
– Clustered
• If source system is different from any one of the above, it
becomes complex
Migration to Exadata
3
Considerations for Migration
• Source
– Data
– O/S, Storage
• Changes to data layout
– Re-partitioning, sorting
• Downtime tolerance
• Is compression needed
•Changes to
•block size
•datafile layout
•characterset
•extent size
Migration to Exadata
4
Logical Migration
• Data Pump Export/Import
• Pros
– Restructuring is possible
• Data layout, sorting, partitioning,
– Charactersets
• Cons
– Time
Migration to Exadata
5
Saving Time
Source
Machine
Exadata Node1
Switch
•
•
•
•
Exadata Node2
Fiber makes it fast to export
Extra NIC Card helps in import
You can use all the nodes of Exadata for import
You can use another intermediate server for NFS
Migration to Exadata
6
Tips
•
•
•
•
Put the Exadata DB in NOARCHIVELOG mode
Pre-create all the tablespaces with autoextend on
Create the empty table structure
Disable triggers and constraints (incl. PK and UK)
alter table xyz disable trigger t1;
alter table xyz disable constraint c1;
• Create indexes after import
• With parallel degree and NOLOGGING
• Enable triggers and constraints with novalidate
alter table xyz enable constraint c1 novalidate;
Migration to Exadata
7
Tips Contd.
• Performs a Metadata Export from Source
$ expdp … contents=metadata_only
• Import this into Exadata
$ impdp … full=y
• This will create all objects and grants
• Do this twice to make sure you get all the objects
Migration to Exadata
8
Precreate all Tables
• Pre-create all the tables by SQLFILE
$ impdp … sqlfile=cr_tables.sql include=TABLE
• This will create an SQL script containing all the tables.
• If you decide to use a multibyte characterset, here is your chance:
create table xyz (
col1 varchar2(20 BYTE)
…)
• Change to
create table xyz (
col1 varchar2(20 CHAR)
…)
Migration to Exadata
9
Create Index Scripts
• Create index creation scripts for all indexes by schema
$ impdp … contents=metadata_only schema=s1
sqlfile=s1_in.sql
• Create all indexes after the schema’s table import is complete
• Grant Privileges after the indexes are created
• Create all constraints after the indexes are created and privileges
granted
Migration to Exadata
10
Other Tips
• Compression
$ expdp … COMPRESSION=ALL
• Helps if you are I/O bound but have enough CPU
• Parallel Export and Import
$ expdp … PARALLEL=n DUMPFILE=exp%U.dmp
Migration to Exadata
11
Sample Sequence
1.
2.
3.
4.
5.
6.
7.
8.
Export all Metadata
Create all objects (no data) in Exadata, except MVs
Disable triggers, constraints and drop indexes
Export all data from source
Import all data to Exadata
Create indexes of the schema after it’s imported
Enable constraints and triggers
Import Metadata one last time
Migration to Exadata
12
CTAS
Source
Machine
Exadata Node1
Switch
Exadata Node2
• Create database link to Source
create table s1.xyz nologging as select * from s1.xyz@link1;
• Create indexes after the table is imported
• Grant all privileges
• Re-enable constraints and triggers after the index is created
Migration to Exadata
13
DPI
• Pre-create all tables
– With privileges, triggers, etc. but not indexes or constraints
• Direct Path Insert over DB Link
•
•
•
•
•
insert /*+ append */ into xyz select * from xyz@link1;
Can be parallelized
Can load partition by partition
Can load older partition ahead of time
Create indexes, etc. after the table load is over.
Fastest way overall
Migration to Exadata
14
Sequence
Time
Insert T1
T1 Indexes
Insert T2
T1 Constraints
T2 Indexes
Migration to Exadata
15
Tuning Network
/etc/sysctl.conf
net.core.rmem_default = <rd>
net.core.rmem_max = <rm>
net.core.wmem_default = <rd>
net.core.wmem_max = <wm>
SQLNET.ORA
DEFAULT_SDU_SIZE=<ds>
LISTENER.ORA
LISTENER =
(DESCRIPTION =
(ADDRESS = …
(SEND_BUF_SIZE=<wm>)
(RECV_BUF_SIZE=<rm>)
)…
TNSNAMES.ORA
DWP =
(DESCRIPTION =
(SDU=<ds>)
(ADDRESS =
…
(SEND_BUF_SIZE=<rm>)
(RECV_BUF_SIZE=<wm>)
)
(CONNECT_DATA = …
Migration to Exadata
16
Transportable Tablespace
Source
Machine
Exadata
•Copy all the files
•Import the metadata to plug-in
•Plug in the tablespace
Migration to Exadata
17
Byte Order Change
• If the source system is big-endian, e.g. HP-UX, Solaris, AIX, etc.,
you can’t directly plug in
• You will need to convert it to little endian first.
RMAN> convert datafile myts1.dbf
from platform 'HP-UX (64-bit)'
db_file_name_convert
'/mydb/myts1.dbf','+MYDG';
Migration to Exadata
18
Thank You!
Migration to Exadata
19