Oracle E-Business Suite (EBS) Data Pump Migration Playbook for Archive DBAs

TargetInstanceProp.DB_BLOCK_SIZE=8192
TargetInstanceProp.DB_PLATFORM_ID=13
TargetInstanceProp.DB_TIME_ZONE=+00\:00
TargetInstanceProp.DB_VERSION=23.26.2.2.0
TargetInstanceProp.DIRECTORIES=DATA_PUMP_DIR,SQL_TCB_DIR
TargetInstanceProp.NLS_CHARACTERSET=AL32UTF8
TargetInstanceProp.NLS_NCHAR_CHARACTERSET=AL16UTF16
TargetInstanceProp.PDB_LOCKDOWN=DWCS
TargetInstanceProp.TABLESPACES=DATA,DBFS_DATA,SAMPLESCHEMA,SYSAUX,SYSTEM,TEMP
TargetInstanceProp.TABLESPACE_BLOCK_SIZE.DATA=8192
TargetInstanceProp.TABLESPACE_BLOCK_SIZE.DBFS_DATA=8192
TargetInstanceProp.TABLESPACE_BLOCK_SIZE.SAMPLESCHEMA=8192
TargetInstanceProp.TABLESPACE_BLOCK_SIZE.SYSAUX=8192
TargetInstanceProp.TABLESPACE_BLOCK_SIZE.SYSTEM=8192
TargetInstanceProp.TABLESPACE_BLOCK_SIZE.TEMP=8192

# cpat-ebs-adw26ai.cfg
connectstring=jdbc:oracle:thin:@ebsdb01.example.com:1521/ebs_pdb
username=SYS
sysdba=true
targetcloud=ADWS
migrationmethod=DATAPUMP
analysisprops=adw-26ai-target-properties.properties
outdir=./cpat-output
outfileprefix=EBS01
reportformat=TEXT,JSON,HTML
full=true

# Run CPAT.
premigration.sh --parfile cpat-ebs-adw26ai.cfg

-- Total segment size
select round(sum(bytes)/1024/1024/1024, 2) as db_size_gb
from dba_segments;

-- Largest schemas
select owner,
       round(sum(bytes)/1024/1024/1024, 2) as size_gb
from dba_segments
group by owner
order by size_gb desc;

-- Largest tables
select owner,
       segment_name,
       round(bytes/1024/1024/1024, 2) as size_gb
from dba_segments
where segment_type like 'TABLE%'
order by bytes desc
fetch first 30 rows only;

-- Source character set. Match this in the target when the target standard allows it.
select parameter, value
from nls_database_parameters
where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

-- Source CPU/process capacity for Data Pump planning.
select name, value
from v$parameter
where name in ('cpu_count','processes','sessions','parallel_max_servers');

# Preferred: DMU
# 1. Start DMU from a workstation or database server that can reach source EBS.
# 2. Connect to the source database as SYSDBA or an approved DBA user.
# 3. Set the target character set, for example AL32UTF8.
# 4. Scan the active Oracle E-Business Suite (EBS) schemas.
# 5. Export the report and review invalid data, lossy conversion, and column expansion risk.

cd $ORACLE_HOME/dmu
./dmu

# Fallback for older Oracle homes: CSSCAN.
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/csminst.sql

csscan userid=system full=y tochar=AL32UTF8 array=102400 process=8 log=ebs_csscan

ls -lh ebs_csscan.*
grep -i "exception\|lossy\|truncation\|exceed\|invalid" ebs_csscan.*

-- Run on the source Oracle E-Business Suite (EBS) database as APPS or a DBA user.
BEGIN
  FOR r IN (
    select oracle_username
    from   applsys.fnd_oracle_userid
    where  read_only_flag in ('A','E','U')
  )
  LOOP
    DBMS_STATS.GATHER_SCHEMA_STATS(
      ownname          => r.oracle_username,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      cascade          => TRUE
    );
  END LOOP;
END;
/

set heading off feedback off pages 0 trimspool on
spool active_ebs_schemas.txt

select owner
from   dba_tables
where  owner in (
  select oracle_username
  from   applsys.fnd_oracle_userid
  where  read_only_flag in ('A','E','U')
)
and    num_rows > 0
group  by owner
order  by owner;

spool off

-- Review row totals before using the list in export/import parfiles.
select owner,
       count(*) table_count,
       sum(num_rows) total_rows
from   dba_tables
where  owner in (
  select oracle_username
  from   applsys.fnd_oracle_userid
  where  read_only_flag in ('A','E','U')
)
and    num_rows > 0
group  by owner
order  by owner;

df -h
sudo mkdir -p /u01/install/dpump
sudo chown oracle:oinstall /u01/install/dpump
sudo chmod 750 /u01/install/dpump
df -h /u01/install/dpump

create or replace directory EBS_EXPORT_DIR as '/u01/install/dpump';
grant read, write on directory EBS_EXPORT_DIR to system;

select directory_name, directory_path
from dba_directories
where directory_name = 'EBS_EXPORT_DIR';

directory=EBS_EXPORT_DIR
dumpfile=ebsdb_metadata_full_%U.dmp
logfile=ebsdb_metadata_full.log
job_name=EBS_META_EXP
full=y
content=metadata_only
parallel=2
filesize=20G
logtime=all
metrics=y
keep_master=y

source /u01/install/APPS/19.0.0/ebscdb_apps.env

nohup expdp system/manager@//localhost:1521/ebsdb \
  parfile=ebsdb_metadata_only_export.par \
  > ebsdb_metadata_only_export.out 2>&1 &

tail -f /u01/install/dpump/ebsdb_metadata_full.log
ls -lh /u01/install/dpump/ebsdb_metadata_full_*.dmp
grep -i "successfully completed\|error\|ora-\|warning" /u01/install/dpump/ebsdb_metadata_full.log

directory=EBS_EXPORT_DIR
dumpfile=ebsdb_data_full_%U.dump
logfile=ebsdb_data_full.log
job_name=EBS_DATA_EXP
full=y
content=data_only
parallel=8
filesize=20G
logtime=all
metrics=y
keep_master=y

nohup expdp system/manager@//localhost:1521/ebsdb \
  parfile=ebsdb_data_only_export.par \
  > ebsdb_data_only_export.out 2>&1 &

tail -f /u01/install/dpump/ebsdb_data_full.log
ls -lh /u01/install/dpump/ebsdb_data_full_*.dump
grep -i "successfully completed\|error\|ora-\|warning" /u01/install/dpump/ebsdb_data_full.log

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"
source ~/.bashrc
oci --version

mkdir -p ~/.oci
chmod 700 ~/.oci
vi ~/.oci/config
chmod 600 ~/.oci/config
chmod 600 ~/.oci/oci_api_key.pem

oci os ns get

[DEFAULT]
user=<user_ocid>
fingerprint=<api_key_fingerprint>
tenancy=<tenancy_ocid>
region=<oci_region>
key_file=/home/oracle/.oci/oci_api_key.pem

oci os object put --bucket-name ebsdump --file /u01/install/dpump/ebsdb_metadata_full_01.dmp --name ebsdb_metadata_full_01.dmp
oci os object put --bucket-name ebsdump --file /u01/install/dpump/ebsdb_metadata_full_02.dmp --name ebsdb_metadata_full_02.dmp
oci os object put --bucket-name ebsdump --file /u01/install/dpump/ebsdb_metadata_full.log --name ebsdb_metadata_full.log

oci os object put --bucket-name ebsdump --file /u01/install/dpump/ebsdb_data_full_01.dump --name ebsdb_data_full_01.dump
oci os object put --bucket-name ebsdump --file /u01/install/dpump/ebsdb_data_full_02.dump --name ebsdb_data_full_02.dump
oci os object put --bucket-name ebsdump --file /u01/install/dpump/ebsdb_data_full.log --name ebsdb_data_full.log

oci os object list \
  --bucket-name ebsdump \
  --query "data[].{name:name,size:size}" \
  --output table

# Oracle Linux example. Use your enterprise package process for production hosts.
sudo dnf install -y oracle-instantclient-release-el8
sudo dnf install -y oracle-instantclient-basic oracle-instantclient-sqlplus oracle-instantclient-tools
sudo dnf install -y screen unzip

sqlplus -v
impdp help=y

# Console path:
# Autonomous Database > DB Connection > Download wallet

# OCI CLI option:
oci db autonomous-database generate-wallet \
  --autonomous-database-id <adw_ocid> \
  --password '<wallet_password>' \
  --file Wallet_EBSARCHIVE.zip

mkdir -p /home/oracle/adw_wallet
unzip Wallet_EBSARCHIVE.zip -d /home/oracle/adw_wallet
chmod 600 /home/oracle/adw_wallet/*

# If needed, verify sqlnet.ora points to this wallet directory.
grep WALLET_LOCATION /home/oracle/adw_wallet/sqlnet.ora

export TNS_ADMIN=/home/oracle/adw_wallet
sqlplus admin/'<adw_admin_password>'@<adw_service_name>
impdp admin/'<adw_admin_password>'@<adw_service_name> help=y

-- Option A: auth token style credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'EBSDUMP_CRED',
    username        => '<oci_user_name_or_federated_user>',
    password        => '<oci_auth_token>'
  );
END;
/

-- Option B: API key style credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'EBSDUMP_CRED',
    user_ocid       => '<user_ocid>',
    tenancy_ocid    => '<tenancy_ocid>',
    private_key     => '<private_key_text>',
    fingerprint     => '<api_key_fingerprint>'
  );
END;
/

-- Files written by Data Pump into ADW DATA_PUMP_DIR, including SQLFILE output and logs.
select object_name, bytes, checksum, created
from table(dbms_cloud.list_files('DATA_PUMP_DIR'))
order by created desc;

-- create extract_ebs_users.par
directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_metadata_full_%U.dmp
job_name=EBS_USERS_SQLFILE
sqlfile=create_ebs_users.sql
full=y
include=user
logtime=all
metrics=y
keep_master=y

#!/usr/bin/env bash
set -euo pipefail

# Inputs from previous steps:
# - create_ebs_users.sql             generated by impdp SQLFILE
# - active_ebs_schemas.txt           generated during source schema discovery
# - optional_custom_schemas.txt      optional custom archive schemas reviewed with the app owner

TEMP_PASSWORD='<approved_temporary_password>'
KEEP_FILE='ebs_users_keep_final.txt'
SOURCE_SQL='create_ebs_users.sql'
CLEAN_SQL='create_ebs_users_clean.sql'
REJECT_SQL='create_ebs_users_rejected_for_review.sql'

# Build final keep list. Edit this file before continuing.
cat active_ebs_schemas.txt optional_custom_schemas.txt 2>/dev/null \
  | sed 's/[[:space:]]//g' \
  | grep -E '^[A-Z0-9_#$]+$' \
  | sort -u > "$KEEP_FILE"

vi "$KEEP_FILE"

# Keep CREATE/ALTER/GRANT statements only for approved schemas.
# Put removed user DDL into a reject file for audit/review.
awk -v keep="$KEEP_FILE" '
  BEGIN {
    while ((getline u < keep) > 0) allow[u]=1
  }
  {
    line=$0
    user=""
    if (match(line, /"(.[^"]*)"/, m)) user=m[1]
    else if (match(line, /(CREATE|ALTER|GRANT)[[:space:]]+(USER|CONNECT|RESOURCE|CONNECT, RESOURCE TO)[[:space:]]+([A-Z0-9_#$]+)/, m)) user=m[3]

    if (user == "" || allow[user]) print line > "create_ebs_users_keep.tmp"
    else print line > "create_ebs_users_reject.tmp"
  }
' "$SOURCE_SQL"

# Remove source tablespace/quota clauses and replace passwords.
sed -E \
  -e 's/DEFAULT TABLESPACE [^ ]+//Ig' \
  -e 's/TEMPORARY TABLESPACE [^ ]+//Ig' \
  -e 's/QUOTA [^;]+ ON [^;]+//Ig' \
  -e 's/IDENTIFIED BY VALUES '\''[^'\'']+'\''/IDENTIFIED BY "'$TEMP_PASSWORD'"/Ig' \
  -e 's/IDENTIFIED BY "[^"]+"/IDENTIFIED BY "'$TEMP_PASSWORD'"/Ig' \
  create_ebs_users_keep.tmp > "$CLEAN_SQL"

mv create_ebs_users_reject.tmp "$REJECT_SQL" 2>/dev/null || touch "$REJECT_SQL"
rm -f create_ebs_users_keep.tmp

# Add edition enablement for kept Oracle E-Business Suite (EBS) users.
# Grant DATA quota separately after users exist.
{
  echo ""
  echo "-- Review before execution: enable editions for required Oracle E-Business Suite (EBS) schemas."
  while read -r user; do
    if [ "$user" != "APPS" ]; then
      echo "ALTER USER $user ENABLE EDITIONS;"
    fi
  done < "$KEEP_FILE"
} >> "$CLEAN_SQL"

echo "Review before running:"
echo "  $CLEAN_SQL"
echo "  $REJECT_SQL"

export TNS_ADMIN=/home/oracle/adw_wallet
impdp admin/'<adw_admin_password>'@<adw_service_name> parfile=extract_ebs_users.par

# The SQLFILE is created inside ADW DATA_PUMP_DIR.
# Upload it from ADW DATA_PUMP_DIR to Object Storage.
sqlplus admin/'<adw_admin_password>'@<adw_service_name> <<SQL
BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'EBSDUMP_CRED',
    object_uri      => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/create_ebs_users.sql',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'create_ebs_users.sql'
  );
END;
/
SQL

# Download the SQL file to the bastion/workstation for review.
oci os object get \
  --bucket-name <bucket> \
  --name create_ebs_users.sql \
  --file create_ebs_users.sql

# Review and clean the downloaded file before running it:
# 1. Keep users listed in active_ebs_schemas.txt.
# 2. Add approved custom schemas in optional_custom_schemas.txt when needed.
# 3. Remove Oracle-maintained, monitoring, personal, backup, and non-Oracle E-Business Suite (EBS) users.
# 4. Remove source tablespace clauses and quotas that do not apply to ADW.
# 5. Replace generated passwords with approved temporary passwords.
# 6. Keep or add ALTER USER ... ENABLE EDITIONS for required Oracle E-Business Suite (EBS) schemas.
chmod +x clean_ebs_users_sql.sh
./clean_ebs_users_sql.sh
vi create_ebs_users_clean.sql
vi create_ebs_users_rejected_for_review.sql

sqlplus admin/'<adw_admin_password>'@<adw_service_name> @create_ebs_users_clean.sql

sqlplus admin/'<adw_admin_password>'@<adw_service_name> <<SQL
select username, account_status, editions_enabled
from dba_users
where username in ('APPS','APPLSYS','GL','AP','AR','PO','FA','CE','HR')
order by username;
SQL

# Run after create_ebs_users_clean.sql completes successfully.
awk '{print "ALTER USER " $1 " QUOTA UNLIMITED ON DATA;"}' \
  ebs_users_keep_final.txt > grant_ebs_user_quota.sql

export TNS_ADMIN=/home/oracle/adw_wallet
sqlplus admin/'<adw_admin_password>'@<adw_service_name> @grant_ebs_user_quota.sql

sqlplus admin/'<adw_admin_password>'@<adw_service_name> <<SQL
select username, tablespace_name, max_bytes
from dba_ts_quotas
where tablespace_name = 'DATA'
and username in ('APPS','APPLSYS','GL','AP','AR','PO','FA','CE','HR')
order by username;
SQL

directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_metadata_full_%U.dmp
logfile=ebs_metadata_import.log
job_name=EBS_META_IMP
full=y
content=metadata_only
remap_tablespace=%:DATA
transform=segment_attributes:n
transform=dwcs_cvt_iots:y
transform=constraint_use_default_index:y
partition_options=merge
exclude=user,profile,materialized_view,materialized_view_log
exclude=materialized_zonemap,tablespace,tablespace_quota,db_link,statistics,cluster
exclude=job,xmlschema,trusted_db_link,directory,library,context,java_class,java_source
exclude=java_resource
logtime=all
metrics=y
keep_master=y

export TNS_ADMIN=/home/oracle/adw_wallet

nohup impdp admin/'<adw_admin_password>'@<adw_service_name> \
  parfile=import_ebs_metadata.par \
  > import_ebs_metadata.out 2>&1 &

tail -f import_ebs_metadata.out

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'EBSDUMP_CRED',
    object_uri      => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebs_metadata_import.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'ebs_metadata_import.log'
  );
END;
/

oci os object get --bucket-name <bucket> --name ebs_metadata_import.log --file ebs_metadata_import.log
grep -Eo 'ORA-[0-9]+' ebs_metadata_import.log | sort | uniq -c | sort -nr

select owner, count(*) as invalid_count
from dba_objects
where status = 'INVALID'
and owner not in ('SYS','SYSTEM')
group by owner
order by invalid_count desc;

select object_type, count(*) as invalid_count
from dba_objects
where status = 'INVALID'
and owner = 'APPS'
group by object_type
order by invalid_count desc;

select *
from dba_errors
where owner = 'APPS'
order by name, sequence;

select type, count(*) as error_count
from dba_errors
where owner = 'APPS'
group by type
order by error_count desc;

select o.object_name,
       o.object_type,
       e.line,
       e.text
from dba_objects o
join dba_errors e
  on o.owner = e.owner
 and o.object_name = e.name
where o.owner = 'APPS'
and o.status = 'INVALID'
fetch first 50 rows only;

select synonym_name, table_owner, table_name
from dba_synonyms s
where owner = 'APPS'
and not exists (
  select 1
  from dba_objects o
  where o.owner = s.table_owner
  and o.object_name = s.table_name
)
order by synonym_name;

select distinct s.table_owner, s.table_name
from dba_synonyms s
where s.owner = 'APPS'
and not exists (
  select 1
  from dba_objects o
  where o.owner = s.table_owner
  and o.object_name = s.table_name
)
order by s.table_owner, s.table_name;

-- Review existing grants before adding anything.
select owner, table_name, privilege
from dba_tab_privs
where grantee = 'APPS'
and (
  table_name like 'DBMS\_%' escape '\'
  or table_name like 'DBA\_%' escape '\'
  or table_name like 'V\_$%' escape '\'
  or table_name like 'GV\_$%' escape '\'
)
order by owner, table_name, privilege;

-- Add only the grants required by compile errors and approved for the archive.
grant select on sys.v_$database to apps;
grant select on sys.v_$instance to apps;
grant execute on sys.dbms_crypto to apps;
grant execute on sys.dbms_lock to apps;
grant execute on sys.dbms_pipe to apps;
grant execute on ctxsys.ctx_ddl to apps;
grant select on sys.v_$session to apps;
grant select on sys.gv_$session to apps;
grant select on sys.v_$parameter to apps;
grant select on sys.v_$parameter2 to apps;

-- Do not grant DBMS_SYSTEM unless there is a documented archive need and security approval.

-- create extract_ebs_grants.par
directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_metadata_full_%U.dmp
sqlfile=create_ebs_grants.sql
full=y
include=object_grant
keep_master=y

export TNS_ADMIN=/home/oracle/adw_wallet
impdp admin/'<adw_admin_password>'@<adw_service_name> parfile=extract_ebs_grants.par

sqlplus admin/'<adw_admin_password>'@<adw_service_name> <<SQL
BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'EBSDUMP_CRED',
    object_uri      => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/create_ebs_grants.sql',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'create_ebs_grants.sql'
  );
END;
/
SQL

oci os object get --bucket-name <bucket> --name create_ebs_grants.sql --file create_ebs_grants.sql
grep -i 'GRANT .*TO "APPS"' create_ebs_grants.sql > ebsdb_apps_grants.sql

# Review ebsdb_apps_grants.sql before execution.

# ORA-64308 occurs during table DDL creation when a hybrid columnar
# compressed table contains a LONG column. Do not assume ADW/Data Pump
# will automatically convert LONG to another datatype during import.
# Recreate the affected table without hybrid columnar compression, or
# manually convert LONG only when the archive design and data profile allow it.
grep -n 'ORA-64308' ebs_metadata_import.log
grep -B2 'ORA-64308' ebs_metadata_import.log | grep 'Object type TABLE' | sort -u

-- create extract_ora_64308_tables.par
directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_metadata_full_%U.dmp
sqlfile=create_ora_64308_tables.sql
tables=<OWNER.TABLE_NAME>
keep_master=y

impdp admin/'<adw_admin_password>'@<adw_service_name> parfile=extract_ora_64308_tables.par

# You edit create_ora_64308_tables.sql:
# - remove hybrid columnar compression clauses
# - do not rely on automatic LONG conversion
# - replace LONG with an approved target datatype only if the archive design and data profile allow it
# - remove indexes, triggers, PK/FK sections from the generated SQL
# - create corrected tables before data import

begin
  for r in (
    select owner, object_name
    from dba_objects
    where object_type = 'SYNONYM'
    and status = 'INVALID'
    and owner in ('APPS','APPLSYS')
  ) loop
    execute immediate 'alter synonym "' || r.owner || '"."' || r.object_name || '" compile';
  end loop;
end;
/

begin
  for r in (
    select object_name
    from dba_objects
    where owner = 'APPS'
    and object_type = 'PACKAGE'
    and status = 'INVALID'
  ) loop
    execute immediate 'alter package APPS.' || r.object_name || ' compile';
    execute immediate 'alter package APPS.' || r.object_name || ' compile body';
  end loop;
end;
/

BEGIN
  UTL_RECOMP.RECOMP_PARALLEL(8);
END;
/

select owner, object_type, count(*) as invalid_count
from dba_objects
where status = 'INVALID'
and owner not in ('SYS','SYSTEM')
group by owner, object_type
order by invalid_count desc;

-- Replace these inserts with the reviewed active_ebs_schemas.txt values.
create table import_active_schemas (owner varchar2(128));

insert into import_active_schemas values ('AP');
insert into import_active_schemas values ('AR');
insert into import_active_schemas values ('GL');
commit;

set heading off feedback off pages 0 lines 200 trimspool on
spool make_indexes_unusable.sql
select 'alter index "' || owner || '"."' || index_name || '" unusable;'
from dba_indexes
where owner in (select owner from import_active_schemas)
and index_type not in ('LOB')
and status <> 'UNUSABLE';
spool off

spool disable_fk_constraints.sql
select 'alter table "' || owner || '"."' || table_name || '" disable constraint "' || constraint_name || '";'
from dba_constraints
where constraint_type = 'R'
and owner in (select owner from import_active_schemas);
spool off

spool disable_triggers.sql
select 'alter trigger "' || owner || '"."' || trigger_name || '" disable;'
from dba_triggers
where owner in (select owner from import_active_schemas);
spool off

@make_indexes_unusable.sql
@disable_fk_constraints.sql
@disable_triggers.sql

select owner_name, job_name, operation, job_mode, state
from dba_datapump_jobs
order by owner_name, job_name;

-- When keep_master=y is used, inspect the master table for phase progress.
select object_type, processing_status, processing_state, count(*) object_count
from admin.<job_name>
where process_order > 0
group by object_type, processing_status, processing_state
order by object_count desc;

-- Attach only when you need to inspect, stop, or kill a running job.
impdp admin/'<adw_admin_password>'@<adw_service_name> attach=<owner.job_name>

-- Inside the Data Pump prompt:
-- Import> STATUS
-- Import> START_JOB
-- Import> CONTINUE_CLIENT
-- Import> STOP_JOB=IMMEDIATE
-- Import> KILL_JOB

directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_data_full_%U.dump
logfile=ebs_data_only_import.log
job_name=EBS_DATA_IMP
content=data_only
schemas=<active_schema_list_comma_separated>
parallel=8
table_exists_action=append
partition_options=merge
exclude=statistics
logtime=all
metrics=y
keep_master=y

export TNS_ADMIN=/home/oracle/adw_wallet

nohup impdp admin/'<adw_admin_password>'@<adw_service_name> \
  parfile=import_ebs_data_only.par \
  > import_ebs_data_only.out 2>&1 &

tail -f import_ebs_data_only.out

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'EBSDUMP_CRED',
    object_uri      => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebs_data_only_import.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'ebs_data_only_import.log'
  );
END;
/

# ORA-12899 appears during data load when a source value is too wide
# for the target column. In Oracle E-Business Suite (EBS) archives this can happen after character
# set conversion, for example when source byte semantics meet ADW AL32UTF8.
grep -n 'ORA-12899' ebs_data_only_import.log
grep -n 'KUP-11007: conversion error loading table' ebs_data_only_import.log

# Identify affected tables/columns from the Data Pump log, then extract
# table DDL and recreate the table with wider or CHAR semantics columns.
-- create extract_ora_12899_tables.par
directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_metadata_full_%U.dmp
sqlfile=create_ora_12899_tables.sql
tables=<OWNER.TABLE_NAME>
keep_master=y

impdp admin/'<adw_admin_password>'@<adw_service_name> parfile=extract_ora_12899_tables.par

# You edit create_ora_12899_tables.sql before rerun:
# - change VARCHAR2(n BYTE) to VARCHAR2(n CHAR), or widen the column
# - replace LONG only if the target design requires it and the app can support it
# - remove indexes, triggers, PK/FK sections from generated SQL
# - recreate the corrected table, then rerun the failed table import

-- Use after reviewing ebs_data_only_import.log.
-- create import_failed_tables.par
directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_data_full_%U.dump
logfile=ebs_failed_tables_import.log
job_name=EBS_FAILED_TABLES_IMP
content=data_only
tables=AR.AR_PAYMENTS_INTERFACE_ALL,MSC.MSC_ST_SYSTEM_ITEMS
table_exists_action=append
exclude=statistics
logtime=all
metrics=y
keep_master=y

impdp admin/'<adw_admin_password>'@<adw_service_name> parfile=import_failed_tables.par

-- Large attachment tables may be handled separately.
-- Confirm archive scope and storage impact before importing FND_LOBS.

-- Optional separate import pattern:
directory=DATA_PUMP_DIR
credential=EBSDUMP_CRED
dumpfile=https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/ebsdb_data_full_%U.dump
logfile=fnd_lobs_import.log
content=data_only
tables=FND.FND_LOBS
table_exists_action=append
metrics=y
keep_master=y

set heading off feedback off pages 0 lines 300 trimspool on
spool rebuild_indexes_nologging.sql

select 'alter index "' || owner || '"."' || index_name || '" rebuild nologging;'
from dba_indexes
where owner in (select owner from import_active_schemas)
and status = 'UNUSABLE'
and index_type not in ('LOB');

spool off

@rebuild_indexes_nologging.sql

select owner, index_name, status
from dba_indexes
where owner in (select owner from import_active_schemas)
and status <> 'VALID'
order by owner, index_name;

-- Run reviewed enable scripts generated from DBA metadata.
@enable_triggers.sql
@enable_fk_constraints.sql

-- Row count spot check. Replace table list with archive scope tables.
select 'GL_JE_HEADERS' table_name, count(*) row_count from gl.gl_je_headers
union all
select 'AP_CHECKS_ALL', count(*) from ap.ap_checks_all
union all
select 'RA_CUSTOMER_TRX_ALL', count(*) from ar.ra_customer_trx_all;

-- Capture remaining import issues.
select owner, object_type, count(*) invalid_count
from dba_objects
where status = 'INVALID'
and owner not in ('SYS','SYSTEM')
group by owner, object_type
order by invalid_count desc;

# Download the export and import logs first.
oci os object get --bucket-name <bucket> --name ebsdb_data_full.log --file ebsdb_data_full.log
oci os object get --bucket-name <bucket> --name ebs_data_only_import.log --file ebs_data_only_import.log

# Compare exported row lines with imported row lines and preserve the output.
grep ' rows' ebsdb_data_full.log | grep -v '0 KB' | sort > export_rows.lst
grep ' rows' ebs_data_only_import.log | grep -v '0 KB' | sort > import_rows.lst
diff -u export_rows.lst import_rows.lst > row_count_diff.lst

# Every exported table with rows should either match import output
# or appear in archive_exceptions.md with the archive reason.
# Review exceptions before business sign-off.

-- Use for source and target when the source database is still available.
-- Compare the two CSV files as part of migration sign-off.
set heading off feedback off pages 0 lines 400 trimspool on
spool archive_table_counts.csv

select owner || ',' || table_name || ',' || num_rows
from dba_tables
where owner in (select owner from import_active_schemas)
and num_rows > 0
order by owner, table_name;

spool off

set heading off feedback off pages 0 lines 300 trimspool on

spool compile_public_synonyms.sql
select 'alter public synonym "' || object_name || '" compile;'
from dba_objects
where owner = 'PUBLIC'
and object_type = 'SYNONYM'
and status <> 'VALID';
spool off

spool compile_schema_synonyms.sql
select 'alter synonym "' || owner || '"."' || object_name || '" compile;'
from dba_objects
where owner <> 'PUBLIC'
and object_type = 'SYNONYM'
and status <> 'VALID';
spool off

@compile_public_synonyms.sql
@compile_schema_synonyms.sql

begin
  for r in (select owner from import_active_schemas) loop
    dbms_utility.compile_schema(schema => r.owner, compile_all => false);
  end loop;
  dbms_utility.compile_schema(schema => 'APPS', compile_all => false);
end;
/

select owner, object_type, count(*) invalid_count
from dba_invalid_objects
group by owner, object_type
order by owner, object_type;

-- Run only after validation and business approval.
-- Confirm these revokes do not break required APEX archive views.
revoke insert any table from apps;
revoke update any table from apps;
revoke delete any table from apps;
revoke select any sequence from apps;

-- Lock imported Oracle E-Business Suite (EBS) schemas that should not be used interactively.
-- Keep APPS unlocked for the archive application if required.
set heading off feedback off pages 0 lines 200 trimspool on
spool lock_archive_schemas.sql
select 'alter user "' || username || '" account lock;'
from dba_users
where username in (select owner from import_active_schemas)
and username <> 'APPS'
order by username;
spool off

@lock_archive_schemas.sql

-- Optional after load if approved for ADW.
exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');

# Archive ADW operations checklist
- Confirm automatic backup and restore policy.
- Record restore approach before destructive test imports.
- Record the UTC timestamp for each restore point or import checkpoint.
- Before restoring ADW, upload or download any needed DATA_PUMP_DIR logs and SQLFILE output.
- After restoring ADW, check dba_datapump_jobs and stop or clean any stale import jobs.
- Record ADW CPU/storage used for migration.
- Record steady-state ADW CPU/storage after migration.
- Decide whether ADW can be stopped outside agreed archive access windows.
- Rotate ADW wallet after implementation if migration hosts or contractors used it.
- Reset or rotate ADMIN credentials after go-live.
- Preserve Object Storage dump/log retention policy and lifecycle rules.