Oracle PeopleSoft Data Pump Migration Playbook for Archive DBAs

Use this playbook after the OCI foundation is available and the DBA team needs a repeatable migration path:

• Cloud Premigration Advisor Tool (CPAT) readiness
• source discovery and sizing
• PeopleSoft schema confirmation
• character-set assessment
• SYSADM schema export
• Object Storage staging
• Autonomous Database import with schema remapping
• import recovery and exception handling
• row-count and business-data validation
• archive hardening and handoff

Infrastructure provisioning belongs in the OCI build playbook. Oracle APEX reports, attachments, SSO, and authorization belong in the application playbook.

This version covers:

Campus Solutions is outside the current scope.

This playbook is for Oracle DBAs who own the source PeopleSoft databases, understand their size and workload, and need to preserve HCM and FSCM history in Autonomous Database. It assumes separate PeopleSoft environments may each contain a schema named SYSADM; schema remapping keeps those source schemas separate in the consolidated archive.

Source

PeopleSoft HCM and FSCM Oracle databases containing historical transactions, effective-dated records, configuration, attachments, and audit evidence.

Target

Oracle Autonomous AI Database using a Lakehouse workload, with separate HCM and FSCM archive schemas and an Oracle APEX parsing schema.

Outcome

A validated archive that supports historical employee, benefits, payroll, journal, voucher, supplier, customer, invoice, asset, purchasing, inventory, order, and project inquiries without keeping the PeopleSoft application stack alive.

PeopleSoftArchive is designed for reporting and historical inquiry rather than transaction entry. The workload consists primarily of searches, joins, filters, aggregations, drill-down reports, attachment retrieval, and exports. Autonomous Database provides a managed Oracle Database platform with integrated Oracle APEX, elastic compute, managed backups, and SQL access close to the archived data.

Do not size the target from source database allocation alone. Review actual schema size, largest tables, attachment volume, expected archive users, report workload, character-set conversion, and growth before selecting storage and compute.

The tested implementation used this pattern:

1. Verify that SYSADM contains the PeopleSoft application data.
2. Export SYSADM separately from each PeopleSoft source.
3. Upload dump files and export logs to OCI Object Storage.
4. Precreate the target archive schema in Autonomous Database.
5. Import with REMAP_SCHEMA and PeopleSoft-specific exclusions.
6. Recover failed tables individually when required.
7. Validate tables, representative row counts, attachments, and reports.
8. Build semantic reporting views in the source archive schemas.
9. Grant only required reporting access to PSAPPS and create synonyms.

The HCM and FSCM examples below come from a small demonstration environment. Their durations illustrate the tested process; they are not production estimates.

Run the current Oracle Cloud Premigration Advisor Tool against each source database before finalizing the migration. Review findings against the archive scope rather than treating the target as a complete PeopleSoft clone.

For each finding, record one decision:

• remediate before export
• handle during import
• validate after import
• exclude because it supports only the retired PeopleSoft runtime

Save the CPAT report, source database version, character set, schema size, major findings, and approved exceptions with the migration evidence.

Do not publish or reuse hard-coded target property files from another Autonomous Database. Generate or validate target properties for the actual service and current Oracle release.

Do not assume the CDB, PDB, operating-system owner, Oracle home, or application owner. Discover them first.

ps -ef | grep pmon
sudo find /opt/oracle/psft -name sqlplus 2>/dev/null

Connect using an approved DBA account:

export ORACLE_SID=<SOURCE_CDB>
sqlplus / as sysdba

SHOW PDBS;
ALTER SESSION SET CONTAINER=<PEOPLESOFT_PDB>;
SHOW CON_NAME;

SELECT owner,
       COUNT(*) AS segment_count,
       ROUND(SUM(bytes)/1024/1024/1024,2) AS size_gb
FROM   dba_segments
WHERE  owner IN ('SYSADM','PS','PEOPLE')
GROUP  BY owner
ORDER  BY size_gb DESC;

The tested HCM and FSCM environments stored the primary PeopleSoft application data in SYSADM. Confirm this for every customer environment before exporting.

-- Total database segment size
SELECT ROUND(SUM(bytes)/1024/1024/1024,2) AS database_size_gb
FROM   dba_segments;

-- Schema sizes
SELECT owner,
       ROUND(SUM(bytes)/1024/1024/1024,2) AS size_gb
FROM   dba_segments
GROUP  BY owner
ORDER  BY size_gb DESC;

-- Largest SYSADM segments
SELECT owner,
       segment_name,
       segment_type,
       ROUND(bytes/1024/1024/1024,2) AS size_gb
FROM   dba_segments
WHERE  owner = 'SYSADM'
ORDER  BY bytes DESC
FETCH FIRST 30 ROWS ONLY;

-- Character sets
SELECT parameter, value
FROM   nls_database_parameters
WHERE  parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

-- Capacity relevant to Data Pump
SELECT name, value
FROM   v$parameter
WHERE  name IN ('cpu_count','processes','sessions','parallel_max_servers');

If source and target character sets differ, test for expansion and conversion failures before the final import. Preserve the assessment results with the migration evidence.

Use a dedicated export volume sized for the expected dump files and logs. The following is a pattern; device names and mount points are customer-specific.

lsblk
sudo mkfs.xfs <EXPORT_DEVICE>             # New, empty volume only
sudo mkdir -p <EXPORT_PATH>
sudo mount <EXPORT_DEVICE> <EXPORT_PATH>
sudo chown <ORACLE_OWNER>:<ORACLE_GROUP> <EXPORT_PATH>
sudo chmod 750 <EXPORT_PATH>
df -h <EXPORT_PATH>

Inside the PeopleSoft PDB:

CREATE OR REPLACE DIRECTORY <EXPORT_DIRECTORY>
AS '<EXPORT_PATH>';

GRANT READ, WRITE ON DIRECTORY <EXPORT_DIRECTORY>
TO <APPROVED_EXPORT_USER>;

SELECT directory_name, directory_path
FROM   dba_directories
WHERE  directory_name = '<EXPORT_DIRECTORY>';

The Oracle directory object does not create the operating-system directory.

export ORACLE_SID=<HCM_CDB>
export ORACLE_PDB_SID=<HCM_PDB>

nohup expdp "'/ as sysdba'" \
  schemas=SYSADM \
  job_name=PSHCM_SYSADM_EXPORT \
  directory=PSHCM_EXPORT_DIR \
  dumpfile=pshcm_sysadm_%U.dmp \
  logfile=pshcm_sysadm_export.log \
  filesize=20G \
  parallel=2 \
  logtime=all \
  metrics=y \
  keep_master=y \
  > <HCM_EXPORT_PATH>/pshcm_expdp_console.out 2>&1 &

Monitor and verify:

tail -f <HCM_EXPORT_PATH>/pshcm_expdp_console.out
tail -f <HCM_EXPORT_PATH>/pshcm_sysadm_export.log
grep -E "ORA-|UDE-|error|failed" <HCM_EXPORT_PATH>/pshcm_sysadm_export.log
ls -lh <HCM_EXPORT_PATH>/pshcm_sysadm_*.dmp
df -h <HCM_EXPORT_PATH>

In the tested environment, the HCM export created two dump pieces and completed in approximately 15 minutes. Production timing will depend on source size, compression, CPU, I/O, and storage throughput.

export ORACLE_SID=<FSCM_CDB>
export ORACLE_PDB_SID=<FSCM_PDB>

nohup expdp "'/ as sysdba'" \
  schemas=SYSADM \
  job_name=PSFSCM_SYSADM_EXPORT \
  directory=PSFSCM_EXPORT_DIR \
  dumpfile=psfscm_sysadm_%U.dmp \
  logfile=psfscm_sysadm_export.log \
  filesize=20G \
  parallel=2 \
  logtime=all \
  metrics=y \
  keep_master=y \
  > <FSCM_EXPORT_PATH>/psfscm_expdp_console.out 2>&1 &

tail -f <FSCM_EXPORT_PATH>/psfscm_expdp_console.out
tail -f <FSCM_EXPORT_PATH>/psfscm_sysadm_export.log
grep -E "ORA-|UDE-|error|failed" <FSCM_EXPORT_PATH>/psfscm_sysadm_export.log
ls -lh <FSCM_EXPORT_PATH>/psfscm_sysadm_*.dmp
df -h <FSCM_EXPORT_PATH>

The tested FSCM export completed in approximately 34 minutes. Do not use FULL=Y simply to capture PeopleSoft data when the validated archive scope is the SYSADM schema. A schema export avoids unnecessary database and runtime objects and succeeded where an attempted full export encountered metadata errors.

Use instance principals, a controlled transfer host, or another approved enterprise authentication method when available. Do not embed OCI API private keys in scripts or articles.

oci --version
oci os ns get

Upload each generated dump file rather than assuming there will always be exactly two pieces:

for file in <EXPORT_PATH>/<PREFIX>_*.dmp; do
  oci os object put \
    --bucket-name <ARCHIVE_BUCKET> \
    --file "$file" \
    --name "$(basename "$file")"
done

oci os object put \
  --bucket-name <ARCHIVE_BUCKET> \
  --file <EXPORT_PATH>/<EXPORT_LOG> \
  --name <EXPORT_LOG>

oci os object put \
  --bucket-name <ARCHIVE_BUCKET> \
  --file <EXPORT_PATH>/<CONSOLE_OUTPUT> \
  --name <CONSOLE_OUTPUT>

Verify the uploaded objects:

oci os object list \
  --bucket-name <ARCHIVE_BUCKET> \
  --prefix <OBJECT_PREFIX> \
  --query "data[].{name:name,size:size}" \
  --output table

Preserve the export parfile or command, dump manifest, dump sizes, export log, console output, and Object Storage listing.

Create separate schemas because both source databases use SYSADM.

CREATE USER PSHCM_SYSADM
IDENTIFIED BY "<HCM_ARCHIVE_PASSWORD>"
DEFAULT TABLESPACE DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON DATA;

GRANT CREATE SESSION, DWROLE TO PSHCM_SYSADM;

CREATE USER PSFSCM_SYSADM
IDENTIFIED BY "<FSCM_ARCHIVE_PASSWORD>"
DEFAULT TABLESPACE DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON DATA;

GRANT CREATE SESSION, DWROLE TO PSFSCM_SYSADM;

For a clean repeatable test import, drop and recreate only the exact target schema after confirming the target, backup or restore point, and business approval:

DROP USER <EXACT_ARCHIVE_SCHEMA> CASCADE;

Never parameterize this operation with an unresolved shell variable or broad pattern.

Create a database credential using an approved OCI user, auth token, resource principal, or API-signing credential. Keep all secrets out of source control and published documentation.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'PEOPLESOFT_DUMP_CRED',
    username        => '<APPROVED_OCI_USERNAME>',
    password        => '<OCI_AUTH_TOKEN>'
  );
END;
/

If the implementation uses an API-signing credential, inject its private key through an approved secret-handling process. Do not paste private-key material into worksheets, chat, Terraform variables, or published commands.

Use a controlled host that can resolve and reach the Autonomous Database private endpoint. Configure the wallet when mTLS is required.

export TNS_ADMIN=<WALLET_DIRECTORY>
sqlplus admin/'<ADMIN_PASSWORD>'@<ADB_SERVICE>
impdp admin/'<ADMIN_PASSWORD>'@<ADB_SERVICE> help=y

Avoid placing passwords directly in shell history. Use an approved credential mechanism for production execution.

nohup impdp admin/'<ADMIN_PASSWORD>'@<ADB_SERVICE> \
  directory=DATA_PUMP_DIR \
  credential=PEOPLESOFT_DUMP_CRED \
  dumpfile=<HCM_DUMP_URI_1>,<HCM_DUMP_URI_2> \
  schemas=SYSADM \
  remap_schema=SYSADM:PSHCM_SYSADM \
  job_name=PSHCM_SYSADM_IMPORT \
  logfile=pshcm_sysadm_import.log \
  parallel=1 \
  partition_options=merge \
  transform=segment_attributes:n \
  transform=dwcs_cvt_iots:y \
  transform=constraint_use_default_index:y \
  exclude=index,cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link,grant \
  logtime=all \
  metrics=y \
  > <WORK_DIRECTORY>/pshcm_impdp_console.out 2>&1 &

tail -f <WORK_DIRECTORY>/pshcm_impdp_console.out
grep -E "ORA-|UDI-|UDE-|error|failed|fatal" <WORK_DIRECTORY>/pshcm_impdp_console.out

Tested HCM outcome

• Source schema: SYSADM
• Target schema: PSHCM_SYSADM
• Parallel degree: 1
• Main import duration: approximately 2 hours 29 minutes
• Dump pieces: two
• One table required recovery after a transient Object Storage read failure

Recover a Failed HCM Table

The tested import failed to load PS_SS_BP_COMPS after an Object Storage HTTP read error. Confirm the target count before retrying:

SELECT COUNT(*)
FROM PSHCM_SYSADM.PS_SS_BP_COMPS;

Reimport only the failed table:

nohup impdp admin/'<ADMIN_PASSWORD>'@<ADB_SERVICE> \
  directory=DATA_PUMP_DIR \
  credential=PEOPLESOFT_DUMP_CRED \
  dumpfile=<HCM_DUMP_URI_1>,<HCM_DUMP_URI_2> \
  tables=SYSADM.PS_SS_BP_COMPS \
  remap_schema=SYSADM:PSHCM_SYSADM \
  table_exists_action=truncate \
  job_name=PSHCM_BP_COMPS_REIMPORT \
  logfile=pshcm_bp_comps_reimport.log \
  parallel=1 \
  transform=segment_attributes:n \
  logtime=all \
  metrics=y \
  > <WORK_DIRECTORY>/pshcm_bp_comps_reimport.out 2>&1 &

The retry completed successfully and the target table contained 28 rows. This is an example of table-level recovery; every retry must be based on the actual import log and verified against the source.

The first tested FSCM import used PARALLEL=2 and exceeded the Autonomous Database PGA limit. The stalled job was terminated, and the final import used PARALLEL=1.

Inspect and stop a failed Data Pump job

impdp admin/'<ADMIN_PASSWORD>'@<ADB_SERVICE> \
  attach=PSFSCM_SYSADM_IMPORT

At the Data Pump prompt:

Import> STATUS
Import> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes

Confirm job status:

SELECT owner_name,
       job_name,
       state,
       degree,
       attached_sessions
FROM   dba_datapump_jobs
WHERE  job_name LIKE 'PSFSCM_SYSADM_IMPORT%';

Final FSCM import

TABLE_EXISTS_ACTION=REPLACE was used because the earlier attempt had already created and partially loaded objects. For a new clean schema, omit this option unless the reviewed recovery plan requires it.

nohup impdp admin/'<ADMIN_PASSWORD>'@<ADB_SERVICE> \
  directory=DATA_PUMP_DIR \
  credential=PEOPLESOFT_DUMP_CRED \
  dumpfile=<FSCM_DUMP_URI_1>,<FSCM_DUMP_URI_2> \
  schemas=SYSADM \
  remap_schema=SYSADM:PSFSCM_SYSADM \
  job_name=PSFSCM_SYSADM_IMPORT_FINAL \
  logfile=psfscm_sysadm_import.log \
  parallel=1 \
  table_exists_action=replace \
  partition_options=merge \
  transform=segment_attributes:n \
  transform=dwcs_cvt_iots:y \
  transform=constraint_use_default_index:y \
  exclude=index,cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link,grant \
  logtime=all \
  metrics=y \
  > <WORK_DIRECTORY>/psfscm_impdp_console.out 2>&1 &

Monitor:

tail -f <WORK_DIRECTORY>/psfscm_impdp_console.out
grep -E "ORA-|UDI-|UDE-|error|failed|fatal" <WORK_DIRECTORY>/psfscm_impdp_console.out

SELECT s.sid,
       s.serial#,
       s.status,
       s.event,
       s.seconds_in_wait,
       s.sql_id
FROM   v$session s
WHERE  s.module LIKE 'Data Pump%';

SELECT sid,
       serial#,
       opname,
       sofar,
       totalwork,
       units,
       elapsed_seconds,
       time_remaining
FROM   v$session_longops
WHERE  opname LIKE 'SYS_IMPORT_SCHEMA%'
AND    totalwork > 0
AND    sofar <> totalwork;

Tested FSCM outcome

• Source schema: SYSADM
• Target schema: PSFSCM_SYSADM
• Final parallel degree: 1
• Final import duration: approximately 5 hours 59 minutes
• Imported tables reported by DBA_TABLES: 82,088
• Import logs and console output retained in Object Storage

Observed conditions included:

• ORA-31684 because the target schema had been precreated
• ORA-01919 for a source role that did not exist in the target
• ORA-04036 during the initial parallel import
• existing-object messages during recovery from the earlier attempt

Review the complete final log before classifying any error as acceptable. The presence of an expected error code does not prove that all affected objects are irrelevant.

Refresh target statistics before relying on NUM_ROWS, or use actual COUNT(*) queries for audit-critical validation.

SELECT COUNT(*) AS table_count
FROM   dba_tables
WHERE  owner = 'PSFSCM_SYSADM';

SELECT table_name, num_rows
FROM   dba_tables
WHERE  owner = 'PSFSCM_SYSADM'
ORDER  BY table_name;

Representative FSCM tables:

SELECT table_name, num_rows
FROM   dba_tables
WHERE  owner = 'PSFSCM_SYSADM'
AND    table_name IN (
  'PS_LEDGER',
  'PS_JRNL_HEADER',
  'PS_JRNL_LN',
  'PS_VOUCHER',
  'PS_VOUCHER_LINE',
  'PS_DISTRIB_LINE',
  'PS_PAYMENT_TBL',
  'PS_VENDOR',
  'PS_CUSTOMER',
  'PS_PO_HDR',
  'PS_PO_LINE',
  'PS_PO_LINE_SHIP',
  'PS_PO_LINE_DISTRIB',
  'PS_RECV_HDR',
  'PS_RECV_LN_SHIP',
  'PS_REQ_HDR',
  'PS_REQ_LINE',
  'PS_BI_HDR',
  'PS_BI_LINE',
  'PS_ASSET',
  'PS_BOOK',
  'PS_PROJ_RESOURCE'
)
ORDER BY table_name;

Representative HCM areas should include employee, job, benefits, payroll, deduction, tax, and attachment records selected during discovery. Use actual source and target counts for every audit-critical table.

Raw row counts are necessary but not sufficient. Validate:

• EFFDT, EFFSEQ, and effective-status behavior
• BUSINESS_UNIT, SETID, and record-group relationships
• EMPLID and EMPL_RCD relationships
• journal and ledger control totals
• voucher distributions and payments
• purchase-order schedules, distributions, and receipts
• customer transactions, open items, and payments
• ChartFields and configured ChartField labels
• attachment metadata, versions, chunks, reconstructed sizes, and downloads
• year-end payroll and employee-document access
• representative Oracle APEX searches and drill-down paths

Compare the output of agreed source PeopleSoft queries and pages with the archive reports. Preserve validation SQL, results, exceptions, and business approval.

grep -Eo 'ORA-[0-9]+' <IMPORT_LOG> | sort | uniq -c | sort -nr
grep -E "ORA-|UDI-|UDE-|error|failed|fatal" <CONSOLE_OUTPUT>

Every failed or skipped row-bearing table must be either recovered or listed in the archive exception log with its business impact and approval.

The archive does not need every PeopleSoft runtime object to compile. It does need every table, view, function, synonym, and package required by approved archive reports.

SELECT owner, object_type, COUNT(*) AS invalid_count
FROM   dba_objects
WHERE  status = 'INVALID'
AND    owner IN ('PSHCM_SYSADM','PSFSCM_SYSADM')
GROUP  BY owner, object_type
ORDER  BY owner, object_type;

Document runtime-only objects that are intentionally excluded. Fix or replace objects needed by the reporting layer.

Upload the Data Pump logs from DATA_PUMP_DIR:

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'PEOPLESOFT_DUMP_CRED',
    object_uri      => '<OBJECT_URI>/<IMPORT_LOG>',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => '<IMPORT_LOG>'
  );
END;
/

Upload console output from the import host using an approved OCI CLI profile or instance principal, then verify object names and sizes.

Create PSAPPS as the Oracle APEX parsing schema. It should not own the imported PeopleSoft data.

CREATE USER PSAPPS
IDENTIFIED BY "<PSAPPS_PASSWORD>"
DEFAULT TABLESPACE DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON DATA;

GRANT CREATE SESSION TO PSAPPS;

Create semantic reporting views in their applicable archive owner:

• PSHCM_SYSADM.XXPSHCM_%
• PSFSCM_SYSADM.XXPSFSCM_%

Grant only the required views and approved download packages:

GRANT SELECT ON PSHCM_SYSADM.<XXPSHCM_VIEW> TO PSAPPS;
GRANT SELECT ON PSFSCM_SYSADM.<XXPSFSCM_VIEW> TO PSAPPS;
GRANT EXECUTE ON <OWNER>.<APPROVED_ATTACHMENT_PACKAGE> TO PSAPPS;

Create synonyms in PSAPPS:

CREATE OR REPLACE SYNONYM PSAPPS.<REPORTING_VIEW>
FOR <OWNER>.<REPORTING_VIEW>;

Do not grant SELECT ANY TABLE or unrestricted access to attachment BLOBs. Enforce page authorization and row-level archive security in addition to database grants.

After all views, grants, synonyms, attachment packages, and validation are complete:

• lock interactive login to imported archive owners when no longer required
• rotate implementation credentials and wallets
• keep the APEX parsing schema separate from source schemas
• preserve least-privilege grants
• document Business Unit, SetID, department, employee, payroll-document, and module authorization
• retain import dumps and logs according to the approved policy
• confirm the Autonomous Database backup and recovery model

Do not enable PeopleSoft runtime triggers, jobs, integrations, application servers, or Process Scheduler components in the archive unless a separately approved requirement exists.

• Schema export is preferred when the approved archive scope is PeopleSoft SYSADM; do not use FULL=Y without a documented need.
• Never assume SYSADM is the correct source owner without checking actual segment sizes and application data.
• Do not assume that a successful Data Pump completion means every table loaded successfully.
• Tune Data Pump parallelism to the source and target capacity. More workers can increase PGA pressure rather than reduce elapsed time.
• TABLE_EXISTS_ACTION=REPLACE is a recovery option, not the default for every clean import.
• Preserve logs and validation results with the dump files so the migration remains explainable.
• The target does not need to reproduce the PeopleSoft runtime. It must preserve the agreed historical data, business keys, attachments, and reporting context.