Managing Data Movement and Replication
Managing Data Movement and Replication
This domain covers moving data between databases with Data Pump, SQL*Loader, external tables and transportable tablespaces, plus the GoldenGate model for ongoing replication.
Core rules
- expdp and impdp run as server-side jobs. A DIRECTORY object must exist in the database and the corresponding OS directory must be readable and writable by the Oracle software owner; the DIRECTORY object itself is not moved by the job.
- PARALLEL and multiple dump files using the %U substitution increase throughput. A dump file set is a single logical unit; you cannot import from an incomplete set.
- Useful parameters: CONTENT=DATA_ONLY or METADATA_ONLY, ESTIMATE_ONLY, REMAP_SCHEMA, REMAP_TABLESPACE, REMAP_DATA (package-based value transformation), EXCLUDE and INCLUDE.
- NETWORK_LINK runs the job on the target and pulls data through a database link without writing dump files.
- Transportable tablespaces move data files directly: the set must be self-contained, tablespaces are made READ ONLY, source and target must share platform and endianness unless RMAN CONVERT is used, and metadata moves via expdp/impdp with TRANSPORT_DATAFILES.
- SQL*Loader conventional path uses SQL and buffers; direct path writes blocks directly, bypassing the buffer cache, and leaves indexes unusable after some loads.
- GoldenGate: Extract reads redo (integrated capture recommended) and writes trail files; Replicat applies them at the target; checkpoints track position, and supplemental logging of key columns is required for update/delete replication.
Worked example
An import of the HR schema fails with ORA-00959 (tablespace does not exist) because the target lacks the EXAMPLE tablespace. Reasoning: Data Pump does not create tablespaces. Either pre-create EXAMPLE on the target or add REMAP_TABLESPACE=example:users. The related ORA-01950 (no privileges on tablespace) points to a missing quota rather than a missing tablespace.
Traps that cost marks
- Exporting the SYS schema, which Data Pump explicitly warns against and cannot fully replicate.
- Assuming the DIRECTORY object creates an OS directory.
- Running transportable tablespaces while the tablespaces are read-write.