Moving Data
Between Databases
EDI - Electronic Data Interchange
Caleb Small
Consulting Oracle DBA
caleb@caleb.com
www.caleb.com/dba
Database demo
_
Various Methods of Exchanging Data
Oracle-Oracle
- Import / Export
- Data Pump
- Database Link
- Replication, snapshots
- Oracle Streams
- Transportable Tablespace
Non-Oracle
- SQL*Loader
- External Tables
- SQL*Plus extract script
- Triggers, XML datagram
- Third party tools (eg. TOAD, ACCESS)
- Custom Applications (OCI, O/JDBC, UTL_FILE)
- Heterogeneous Services, Transparent Gateway
Considerations
- Real time vs batch
- Replication vs query
- Database load (query, transaction,
read-consistent)
- Network load
- Host machine load
- Elapsed time, conflict with backup window, etc
- Interruption to production system
- Relational integrity, indexes, triggers, privileges,
etc
_
TTS Basic Premise
- Export metadata
- Copy datafile(s)
- Fastest way to move large amounts of data
- Includes indexes and most (not all) other objects
- Convenient method of publishing data
- Multiple DBs can share read-only tablespace
- Can be part of backup strategy
- Recommended method for
tablespace point-in-time recovery
- Available since v8,
- Multiple block size support since 9i
- Multi platform support with RMAN CONVERT (10g)
- Upward compatible based on DB compatibility setting
- Computed compatibility level of TTS set
_
How To
- Move/copy/create all objects in specific tablespace(s)
- Make TS read-only to ensure no active txn (read
consistent)
- EXCHANGE any individual partitions into separate
tables
- Use DBMS_TTS to verify TS set is self contained
- Export the metadata
- Copy datafiles and export file to target DB
- EXCHANGE any tables back into partitions if
necessary
- Reset original TS to read-write if desired
- Use RMAN CONVERT to support cross-platform
endian format
- Import the metadata into the target DB
- ADD partitions to existing tables if necessary
- Make TS read-write if necessary
- Access the data
_
Sharing a tablespace
- All DBs must be able to read the datafile(s)
- Import metadata into all DBs
- TS must remain read-only in all DBs
- Make datafiles read-only to avoid errors!
- Cannot span platforms unless copy is made and converted
_
Limitations
- No objects with dependencies outside the TTS set
- No individual partitions without rest of table*
- No materialized views without underlying tables
- No indexes without tables
- Same character set and NCHAR set
- No duplicate TS (or object) names
- No SYSTEM TS nor SYS owned objects
- Opaque types (RAW, BFILE, AnyType, user-defined) not
converted x-platform
- BINARY_FLOAT/DOUBLE transportable with Data Pump only
- Must have been read-write in 10
- Must use RMAN if going to ASM
- Size TTS datafiles appropriately
* Single partition can be
EXCHANGED into a separate table,
transported, then EXCHANGED back
into table partition.
Does require making TS read-only and partition will be
unavailable during transport.
_
Move, Copy or Create
Create
- Consider location of
DATAFILEs and grouping of objects (table & index)
with respect to DB performance and accessability
for transport.
- All referenced objects must be contained in the TTS
set.
- Read-only during transport
Copy
- Table and data only,
no indexes, constraints, triggers, grants, etc.
- Original table unaffected, no interruption to users
- Additional storage required
- New name for the object
Move
- Includes constraints,
triggers, grants; indexes can be rebuilt
- Must include indexes that enforce constraints or
disable constraints
- Opportunity to reorganize storage
- Read only during transport
- Include referenced objects or disable contraints
during transport
- Can not move a table containing LONG or LONG RAW
_
References
Administrator's
Guide
Database Utilities
Data Warehousing Guide
PL/SQL Packages and Types Reference
Oracle Database Backup and
Recovery Advanced User's Guide
Oracle Database Recovery Manager
Reference
Oracle
Database High Availability Features
Data Guard Concepts and
Administration
Copyright
©, 2005 caleb.com
http://www.caleb.com/dba
caleb@caleb.com