Oracle DBA Lesson 88 Part 2 — Move a transport set

A classic transportable-tablespace move combines physical user datafiles with a Data Pump metadata dump. The rows remain in blocks in the datafiles on disk. The dump describes the objects so that the target database can interpret those files. The files and definitions must describe the same stable source state.

Oracle DBA Lesson 88 Part 2 — Move a transport set

Part 1 checked the transport set and target compatibility. This part follows the read-only transition, the metadata export, the file copy, the import, validation, and application cutover. The procedure is classic Oracle Database 19c tablespace transport between separate source and target PDB services.

Freeze the source set

The checked transport set is APP_DATA, APP_IDX, and APP_REF: the orders table, its index, and its referenced customer table. Every other dependency must also pass the selected checks.

Stop application writes, prevent new writers, and let outstanding transactions finish. Coordinate the tablespace changes with other sessions. Do not assume that an active transaction disappears instantly. Confirm the intended source service and container before the DDL. The change requires ALTER TABLESPACE or MANAGE TABLESPACE.

ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_idx  READ ONLY;
ALTER TABLESPACE app_ref  READ ONLY;

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name IN ('APP_DATA','APP_IDX','APP_REF')
ORDER BY tablespace_name;

Check that all three status rows report READ ONLY. These statements do not make the rest of the database read-only. Keep administrative and object changes coordinated so the metadata and the copied files continue to belong to the same frozen transport set. Recheck dependencies if the set's structure changes.

DDL and Data Pump operations are not undone by a later SQL ROLLBACK.

Export definitions and review the required files

DP_DIR exists separately in each applicable PDB and points to an accessible server directory. Grant READ and WRITE on that directory object, and configure operating-system access. The source and target directory objects may point to different host locations. Launching the client from a folder does not make Data Pump read files from that folder.

The source export requires DATAPUMP_EXP_FULL_DATABASE. Do not run Data Pump as SYSDBA for this routine transport. Create transport_export.par on the utility host:

DIRECTORY=DP_DIR
DUMPFILE=transport88.dmp
LOGFILE=transport88_export.log
TRANSPORT_TABLESPACES=APP_DATA,APP_IDX,APP_REF
TRANSPORT_FULL_CHECK=YES

Invoke the source export with a password prompt or an established credential facility:

expdp TRANSPORT_ADMIN@SOURCE_SERVICE parfile=transport_export.par

TRANSPORT_TABLESPACES selects classic tablespace transport. TRANSPORT_FULL_CHECK=YES requests strict containment, including dependencies entering and leaving the set. Resolve a containment failure before continuing.

The dump contains metadata for this classic transport set. It does not contain the table's row payload. Review the export log for errors and for the datafiles reported for every tablespace. Reconcile that list with the manifest. A successful export message does not establish that the files have reached the target, or that target application reads work.

A manifest records every datafile for each selected tablespace, its source path, and its separate target destination. The list below has one datafile per tablespace. Use the complete list when a tablespace has more files.

Tablespace	Source file	Target copy
APP_DATA	/u02/oradata/source/app_data01.dbf	/u02/oradata/target/app_data01.dbf
APP_IDX	/u02/oradata/source/app_idx01.dbf	/u02/oradata/target/app_idx01.dbf
APP_REF	/u02/oradata/source/app_ref01.dbf	/u02/oradata/target/app_ref01.dbf

See Data Pump Export.

Copy and check the matching artifacts

Copy the metadata dump to the server directory represented by the target's DP_DIR. Copy every manifest-listed datafile to its separately verified target path. Keep the source originals, and keep a protected pre-import target copy where the recovery plan requires it.

For unchanged same-format copies, compare byte counts and cryptographic checksums between source and destination before import. This move has matching byte order, unencrypted files, and no target tablespace-name conflict. Also verify destination ownership, permissions, storage capacity, and access by the target database processes. File names alone do not establish integrity or consistency. Do not copy actively changing datafiles outside a supported backup or transport procedure.

A different byte order requires a supported conversion. Checksum equality across that conversion is not an appropriate expectation.

Do not attach both databases to the same writable physical file. Use separate target copies. Import may modify the target files while it loads metadata. Preserve the originals, and do not expect post-import checksums to remain identical to the pre-import copies.

Oracle documents an optional return of the source tablespaces to read/write after the copy. Keep source application writes stopped through target verification and cutover, so a clear backout point remains.

See Transporting Data.

Import using target paths

Owning schemas such as APP_USER must already exist on the target, with the necessary defaults, quotas, and privileges. Without REMAP_SCHEMA, a classic tablespace import expects those schemas. Verify application identities and grants. Do not assume the transport creates them. Provision suitable default and temporary tablespaces outside the set. The target import requires DATAPUMP_IMP_FULL_DATABASE.

Create transport_import.par:

DIRECTORY=DP_DIR
DUMPFILE=transport88.dmp
LOGFILE=transport88_import.log
TRANSPORT_DATAFILES=
  '/u02/oradata/target/app_data01.dbf',
  '/u02/oradata/target/app_idx01.dbf',
  '/u02/oradata/target/app_ref01.dbf'

Run:

impdp TRANSPORT_ADMIN@TARGET_SERVICE parfile=transport_import.par

• DIRECTORY locates the metadata dump and log through a target database directory object.
• TRANSPORT_DATAFILES supplies absolute target-server paths, not source paths and not directory-object names. Those files must already exist and be accessible to the target database.
• The dump identifies the tablespace and object metadata. Import associates that metadata with the supplied datafiles. Do not pre-create the transported tablespaces in the target.
• A file-based import of an existing transport dump uses DUMPFILE plus TRANSPORT_DATAFILES. Do not add TRANSPORT_TABLESPACES or NETWORK_LINK to this file-based import. Those parameters select a different import path.
• Review the parameters, the identity, and the manifest before the operation. An optional SQLFILE preview can show generated DDL when the selected import options support it. That preview is not an executed import and not a complete functional validation.

Keep application writers blocked during import and validation, independently of tablespace access mode. In the documented classic workflow, a successful tablespace import leaves the transported set read-only. Import can temporarily need writes while it loads metadata. Later release options such as TRANSPORTABLE=KEEP_READ_ONLY have their own requirements. Full transportable import is a different outcome: it normally completes with user tablespaces read/write.

Transportable import jobs are not restartable. If a job fails, investigate its errors and the partially created target state, follow the planned recovery procedure, and use clean copies when they are required. Do not rerun the job against modified files. A SQL ROLLBACK does not restore the migration.

See Data Pump Import.

Verify reads, then release target writes

Complete each check and keep its result:

Check	What it establishes
Import log and completion status	Utility errors and objects requiring investigation
Target tablespaces and datafile locations	Expected set and intended physical destinations
Tables, indexes and constraints	Expected definitions, validity/usability and enabled relationships
Row counts and business aggregates	Agreement with the frozen source for the comparisons chosen
Reader login and object privileges	Intended users can connect and read the required application objects
Application functional checks	Required application behavior works on the receiving database

Compare the same query on the frozen source and on the target:

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM app_user.orders;

Counts and totals supplement functional and integrity checks. They do not prove that every value matches. Choose reconciliations that fit the workload. Use the intended reader identity for the access test. Resolve unexpected results before allowing application writes.

After reads pass on this classic read-only target set:

ALTER TABLESPACE app_data READ WRITE;
ALTER TABLESPACE app_idx  READ WRITE;
ALTER TABLESPACE app_ref  READ WRITE;

Route application services and connections to the target, and validate writes. Maintain one application write destination. Source application writes stay stopped. Tablespace mode alone does not control where the application writes.

Back out changes after target writes

Backout depends on a recoverable source backup.

Before target application writes: investigate the target failure, confirm the source is unchanged and usable, stop or disable target writers, restore source access mode as appropriate, and route users back through the tested procedure.

After target application writes: the target can contain changes absent from the frozen source. Stop further changes as needed, inventory and reconcile committed differences, or execute the planned recovery or migration strategy before reopening the source for applications. Reopening both copies for independent writes creates further divergence.

Retain the source files, logs, manifests, and the recovery copy until validation and retention conditions are satisfied. Remove only the specifically identified target objects and files. Check for live database references before any file removal. Do not drop source tablespaces or remove source datafiles merely because the import completed.

Full transportable scope

A full transportable export uses, among its other required parameters:

FULL=YES
TRANSPORTABLE=ALWAYS

User tablespaces travel as physical datafiles with their metadata. Eligible content in administrative tablespaces that are not themselves transportable is unloaded logically, with metadata, into the dump. That includes supported user objects and data in those administrative tablespaces. It does not mean copying Oracle's entire internal dictionary or every SYS object.

The receiving database keeps its own SYSTEM and SYSAUX datafiles. Do not substitute the source's physical SYSTEM or SYSAUX files for a full transportable import.

A file-based full transportable import infers full transportable mode from the dump and uses the corresponding TRANSPORT_DATAFILES list. A network full transportable import needs FULL=YES, TRANSPORTABLE=ALWAYS, NETWORK_LINK, and TRANSPORT_DATAFILES. That invocation is different from the classic file-based import above. Full transportable import normally finishes with user tablespaces read/write, so application writers must stay blocked until validation is complete.

Check the documented release and compatibility, the database offering, default tablespaces, object storage, encryption, and platform restrictions. An object's storage cannot straddle an administrative tablespace and a transportable user tablespace. Encrypted tablespaces require the same endianness on the source and the target, and encrypted content requires the documented password and key handling. Full transportable export and transportable import are not restartable. These points distinguish the scope. They are not a complete procedure for moving an entire database.

See Transporting Data and Licensing Information.

Quiz

1. Is a classic transport metadata dump enough to recover the table's rows without the transported datafiles?

Yes, because it contains every row No, both definitions and consistent datafiles are needed Yes, if the dump is renamed Only the export log is needed

2. Which paths belong in this file-based import's TRANSPORT_DATAFILES list?

Source-server datafile paths The client's current directory Verified absolute paths of datafile copies on the target system The target directory-object name alone

3. The import completes. What should happen before releasing target application writes?

Delete the source files immediately Reopen the source and the target for independent writes Skip checks because utility completion proves application behavior Verify logs, objects, frozen-source comparisons, and intended application access

4. Why is backout harder after target application writes begin?

New target changes may be absent from the source Datafiles cease to be physical files Every target write automatically reaches the source SQL ROLLBACK reverses the entire move

5. What travels physically in a full transportable move?

The source SYSTEM datafile replaces the target SYSTEM file Only the metadata dump Eligible user tablespace datafiles, accompanied by Data Pump content and metadata The source temporary and undo files as the ordinary transport set

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily