Choose the method from the source format, the objects that must arrive, the consistency requirement, and the permitted interruption to application writes. Then prove that the target works and that recovery and backout are possible.
Oracle DBA Lesson 83 Part 1 — Choose a Data Movement Method
Three practical choices
| Job | Useful candidate | Why it fits | Evidence to collect |
|---|---|---|---|
| Daily 100 MB CSV feed with rejected records | SQL*Loader | Maps external file fields to table columns and inserts records | Load log, inserted, rejected, discarded, and skipped counts, record reconciliation, and a controlled retry |
| Test copy of an application schema | Schema-mode Data Pump export/import | Transfers logical table data and selected object metadata, including stored code and relevant grants | Object and dependency inventory, consistency point, import log, row reconciliation, and application tests |
| 2 TB tenant with a short write outage | An eligible PDB method or physical transport workflow | Can preserve an appropriate physical unit instead of rebuilding every logical object | PDB and tablespace boundaries, compatibility, actual copy and synchronization timing, a cutover test, and a recovery and backout rehearsal |
These sizes state the requirement for each job. They do not establish a speed advantage, an outage result, or a universal tool choice.
SQL*Loader. A file contains records and fields. A table stores rows and columns. The loader's mapping defines how the external fields become database values. Rejected records need investigation. Records filtered out by selection conditions are discarded rather than rejected. Reconciliation must account for the entire feed, including intentional skips and headers. Correcting errors requires a restart or reload plan that prevents duplicate or missing rows. See SQL*Loader concepts and record handling.
External tables. With an appropriate access driver, an external table exposes supported files as rows for SQL processing. For a normal ORACLE_LOADER CSV feed, the database server must be able to access the file through the configured directory and operating-system permissions. Querying the external table reads the external records. Storing transformed rows in an ordinary application table requires an insertion such as INSERT ... SELECT. This can suit SQL-based validation or transformation. External tables and SQL*Loader have different access, datatype, rejection, and loading restrictions. Select the method against the actual feed. See managing tables, including external tables.
Data Pump. A schema copy needs both the rows and the definitions that make them usable. Schema-mode export and import can recreate selected tables, stored code, and relevant metadata and grants, and can load the data. Check dependent objects, referenced schemas, users, roles, directory objects, database links, external files, and other application resources. A schema export is not a complete environment clone. Export and import filtering and privileges affect what is included. See Data Pump overview and Data Pump export: schema scope and consistency options.
Physical movement. A tenant isolated in a compatible pluggable database may be a candidate for a supported clone or relocation. A self-contained tablespace set may be a candidate for transport. For conventional transportable tablespaces, the transported set includes physical data-file copies plus a Data Pump metadata dump. Administrative tablespaces such as SYSTEM and SYSAUX are excluded. Self-containment includes dependencies such as partitions and indexes, not simply files whose names match the application.
Clone, relocation, transportable tablespaces, and an RMAN database transport are distinct procedures. Each has its own release, architecture, availability, encryption, platform, and recovery requirements. The existence of a feature does not establish permission to use it in a particular edition or licence. Evaluate the exact supported procedure before selecting it. See cloning a PDB, transporting data and physical compatibility, RMAN cross-platform transport, and licensing information.
What must remain true during the move?
- Consistency. Related data must represent the required source state. Choose the relevant quiesce or a supported consistent-read strategy. For Data Pump,
FLASHBACK_SCNorFLASHBACK_TIMEcan specify a consistent source point where supported. They are mutually exclusive and require sufficient undo and the applicable privileges. DDL and changes outside the selected objects need their own coordination. Check the default choices against the application's consistency requirement. - Downtime. Define the maximum interruption to writes and service. Measure final synchronization, validation, and service cutover in a rehearsal. Total transfer time and the final write outage are different measurements. A 2 TB size alone supplies neither number. A clone is a copy. It needs an explicit plan for any writes that occur after the copy's source point.
- Compatibility. Inventory source and target releases, architecture, character sets, time-zone data where it is relevant, features, and encryption keys. Physical workflows also have platform and endian restrictions. Endian format is the order of bytes in physical files. Supported tablespace procedures can convert between endian formats. An RMAN cross-platform whole-database transport requires the same endian format. Matching character sets alone does not establish general compatibility.
- Recovery and backout. Define the required restore and recovery scope and the acceptable data loss. Preserve and test the backups, redo, control information, and encryption keys the chosen procedure requires. Plan what happens to new target writes if application traffic must return to the source.
Conventional transportable-tablespace creation normally requires the selected source tablespaces to be read-only while the transportable set is created. Advanced workflows can change the preparation and outage pattern, but they require their own supported procedure and rehearsal. Do not assume a whole-file copy fits a short write outage. A logical method remains a candidate when physical prerequisites fail, subject to measured timing and the required scope.
Establish the source and target boundary
Run the inventory through each intended service. Capture the source and the target separately.
SELECT SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name
FROM dual;The container identifies where the session landed. The service identifies the connection route. Repeat the query on the intended target service. Do not infer the target from a similar name. Use the service names the query returns. Those names may be qualified.
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');NLS_CHARACTERSET identifies the database character set. NLS_NCHAR_CHARACTERSET identifies the national character set. Compare both source and target values and apply the selected method's conversion and compatibility rules. These two parameters do not answer the platform, encryption, entitlement, or complete recoverability questions.
SELECT banner_full FROM v$version;Capture the full banner_full value on each endpoint, including the installed release update. Select the documented source and target compatibility rules for the actual versions. A shared major-release name is not enough.
Resolve the three jobs
For the daily CSV, start with SQL*Loader and build record reconciliation and retry into the load. Choose an external table instead when server-side SQL inspection or transformation fits the feed and its restrictions better.
For the test schema copy, start with schema-mode Data Pump. Inventory dependencies and select a consistency point. Validate imported rows, stored code, grants, and application behavior, including resources outside the schema.
For the large tenant, inventory the physical boundary first. If it is an eligible PDB, evaluate a supported PDB method. If its objects form a self-contained tablespace set, evaluate the relevant transport workflow. Keep the choice provisional until compatibility, staging capacity, the measured write outage, target validation, and recovery and backout pass. If neither physical boundary fits, compare other supported methods with the requirements rather than forcing physical transport.
A successful movement must be followed by application-level validation. Reconcile the required data, inspect object validity and dependencies, connect through the target service, test transactions and expected values, and compare performance with the required limits. Keep failed or untested items open.
Recap
Choose the method from the source format, the objects that must arrive, the consistency requirement, and how long application writes can stop. Use SQL*Loader when a file needs field mapping, loading, and a record-error workflow. Use an external table when the server can read the file and SQL should inspect or transform the records before they are inserted. Use schema-mode Data Pump when a test copy needs rows, stored code, and grants. Treat a large tenant as a physical candidate only when a PDB or a self-contained tablespace set is eligible, compatibility is supported, and a measured cutover and recovery rehearsal pass. Read each endpoint through its own service, and test the recovery scope the application needs.
No comments:
Post a Comment