Organizations are continuously looking for ways to modernize their database platforms to improve agility, reduce operational overhead, and take advantage of cloud-native capabilities. Oracle offers multiple pathways to modernize your database estate, and one of the latest innovations is Oracle Autonomous Database Serverless (ADB-S)@Google Cloud.
By bringing Oracle Autonomous Database closer to Google Cloud workloads, organizations can leverage the power of Oracle’s self-managing, self-securing, and self-repairing database while benefiting from the scalability and ecosystem of Google Cloud.
In this blog series, we will explore a practical approach to performing a manual database migration to Autonomous Database Serverless (ADB-S)@Google Cloud. We’ll walk through the key steps involved in provisioning the target environment, configuring a custom NFS server, and migrating the database using Oracle Data Pump.
This is part 3 of the blog series where we perform database migration to ADB-S using Data Pump Export and Import.
I have divided the remaining blog as below sections.
Prerequisites
Step 1 – Perform Data Pump Export
Step 2 – Perform Data Pump Import
Prerequisites
It is highly recommended to generate a Cloud Premigration Advisor Tool(CPAT) report for your source database , review and take necessary action before proceeding with migration.
I skipped CPAT part since I am only migrating one schema with just one table.
Ensure that you have completed part 1 and part2 of the blog series.
Step 1 – Perform Data Pump Export
It is recommended to set the streams_pool_size to 256MB on the source database.
Use below SQL to set the streams_pool_size.
alter system set streams_pool_size=256M scope=both;
I am planning to migrate a schema called hr01.
Invoke Data Pump export from the source database server using below command.
expdp system@pdb1 exclude=cluster,indextype,db_link schemas=hr01 \
directory=DATA_PUMP_DIR_EXP dumpfile=hr01.dmp metrics=yes logtime=all
where :
pdb1 – TNS alias for my source database PDB.
DATA_PUMP_DIR_EXP – Database directory created in part 2 of the blog.
hr01.dmp – Data Pump export dump file name.
Below is output of a successful export.
[oracle@ap-src-db ~]$ expdp system@pdb1 exclude=cluster,indextype,db_link schemas=hr01 directory=DATA_PUMP_DIR_EXP dumpfile=hr01.dmp metrics=yes logtime=all
Export: Release 19.0.0.0.0 – Production on Wed Jun 17 10:27:41 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
17-JUN-26 10:27:52.795: Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/@pdb1 exclude=cluster,indextype,db_link schemas=hr01 directory=DATA_PUMP_DIR_EXP dumpfile=hr01.dmp metrics=yes logtime=all
17-JUN-26 10:27:54.147: W-1 Startup took 2 seconds
17-JUN-26 10:27:59.235: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
17-JUN-26 10:27:59.911: W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
17-JUN-26 10:28:00.007: W-1 Completed 1 TABLE_STATISTICS objects in 0 seconds
17-JUN-26 10:28:05.412: W-1 Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
17-JUN-26 10:28:05.870: W-1 Completed 1 MARKER objects in 5 seconds
17-JUN-26 10:28:05.998: W-1 Processing object type SCHEMA_EXPORT/USER
17-JUN-26 10:28:06.035: W-1 Completed 1 USER objects in 1 seconds
17-JUN-26 10:28:06.214: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
17-JUN-26 10:28:06.243: W-1 Completed 1 DEFAULT_ROLE objects in 0 seconds
17-JUN-26 10:28:06.350: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
17-JUN-26 10:28:06.376: W-1 Completed 1 TABLESPACE_QUOTA objects in 0 seconds
17-JUN-26 10:28:06.920: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
17-JUN-26 10:28:06.949: W-1 Completed 1 PROCACT_SCHEMA objects in 0 seconds
17-JUN-26 10:28:12.460: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
17-JUN-26 10:28:12.485: W-1 Completed 1 TABLE objects in 3 seconds
17-JUN-26 10:28:21.332: W-1 . . exported “HR01″.”EMP” 5.523 KB 1 rows in 0 seconds using direct_path
17-JUN-26 10:28:21.910: W-1 Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
17-JUN-26 10:28:23.118: W-1 Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
17-JUN-26 10:28:23.157: *****************************************************************************************************
17-JUN-26 10:28:23.158: Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
17-JUN-26 10:28:23.160: /mnt/exp/hr01.dmp
17-JUN-26 10:28:23.243: Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Wed Jun 17 10:28:23 2026 elapsed 0 00:00:36
Step 2 – Perform Data Pump Import
We can perform the Data Pump Import using one of the below options.
Option 1 – Data Pump Import using APIs
Option 2 – DataPump Import using impdp utility
Let’s explore them in detail.
Option 1 – Data Pump Import using APIs
We can invoke the Data Pump APIs either from SQL*Plus or from the SQL Developer interface in ADB-S console.
I am using SQL*Plus which I have installed on the NFS server.
i. Check ADB-S access to Export Dump files.
Use below SQL to list the dump files in NFS.
SQL> SELECT object_name FROM DBMS_CLOUD.LIST_FILES('CUSTOM_NFS');
Below is the output from my environment.
OBJECT_NAME
——————
export.log
hr01.dmp
We are able to list the export dump file and log file.
ii. Initiate DataPump Import.
Use below PL/SQL to initiate the Data Pump Import using APIs.
SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
l_job_handle NUMBER;
l_job_name VARCHAR2(32);
l_job_state VARCHAR2(64);
l_data_pump_dir VARCHAR2(32) := 'CUSTOM_NFS';
l_dump_file_name VARCHAR2(64) := 'hr01.dmp';
l_parallel NUMBER := 1;
BEGIN
-- Create a unique job name
l_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME(
prefix => 'import_job'
);
-- Open a new Data Pump job
l_job_handle := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'SCHEMA',
job_name => l_job_name
);
-- Add the source dump file
DBMS_DATAPUMP.ADD_FILE(
handle => l_job_handle,
filename => l_dump_file_name,
directory => l_data_pump_dir,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);
-- Add the logfile
DBMS_DATAPUMP.ADD_FILE(
handle => l_job_handle,
filename => 'import_adb.log',
directory => l_data_pump_dir,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
DBMS_DATAPUMP.SET_PARAMETER(
handle => l_job_handle,
name => 'METRICS',
value => 1
);
DBMS_DATAPUMP.SET_PARAMETER(
handle => l_job_handle,
name => 'LOGTIME',
value => 'ALL'
);
-- Specify the schemas
DBMS_DATAPUMP.METADATA_FILTER(
handle => l_job_handle,
name => 'SCHEMA_LIST',
value => '''HR01'''
);
-- Start the job
DBMS_DATAPUMP.START_JOB(l_job_handle);
-- Output Result
DBMS_OUTPUT.PUT_LINE('Job '''||l_job_name||''': '||l_job_state);
EXCEPTION
WHEN OTHERS THEN
DBMS_DATAPUMP.STOP_JOB(l_job_handle);
RAISE;
END;
/
where:
l_data_pump_dir – Directory used to attach NFS mount (We did this in part 2 of the blog.).
l_dump_file_name – Export dump file name (From step 1)
Update below section in above PL/SQL with your schemas.
— Specify the schemas
DBMS_DATAPUMP.METADATA_FILTER(
handle => l_job_handle,
name => ‘SCHEMA_LIST’,
value => ”’HR01”’
);
Below is sample output.
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
Job ‘IMPORT_JOB450’:
PL/SQL procedure successfully completed.
iii. Monitor Data Pump Import Job
Use below to monitor the DataPump job.
col job_name for a20
col job_mode for a20
col state for a20
col operation for a20
set lines 120
select job_name,job_mode,state,operation from dba_datapump_jobs;
Below is sample output.
JOB_NAME JOB_MODE STATE OPERATION
IMPORT_JOB450 SCHEMA COMPLETING IMPORT
iv. Verify the Import log file
Use below SQL to read the import log file from ADB.
set serveroutput on SIZE 1000000
set lines 150
DECLARE
FileIn UTL_FILE.FILE_TYPE;
v_sql VARCHAR2 (32767);
BEGIN
FileIn := UTL_FILE.FOPEN ('CUSTOM_NFS', 'import_adb.log', 'R',4000);
IF UTL_FILE.IS_OPEN(FileIn) THEN
LOOP
BEGIN
UTL_FILE.GET_LINE (FileIn, v_sql,32767);
dbms_output.put_line(v_sql);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
END IF;
UTL_FILE.FCLOSE (FileIn);
END;
/
Below is sample output of a successful Import.
/SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
17-JUN-26 15:32:46.404: W-1 Startup on instance 4 took 0 seconds
17-JUN-26 15:32:46.744: W-1 Master table “ADMIN”.”IMPORT_JOB450″ successfully loaded/unloaded
17-JUN-26 15:32:47.013: Starting “ADMIN”.”IMPORT_JOB450″:
17-JUN-26 15:32:47.036: W-1 Processing object type SCHEMA_EXPORT/USER
17-JUN-26 15:32:47.459: W-1 Completed 1 USER objects in 0 seconds
17-JUN-26 15:32:47.460: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
17-JUN-26 15:32:47.509: W-1 Completed 1 DEFAULT_ROLE objects in 0 seconds
17-JUN-26 15:32:47.509: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
17-JUN-26 15:32:47.555: W-1 Completed 1 TABLESPACE_QUOTA objects in 0 seconds
17-JUN-26 15:32:47.555: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
17-JUN-26 15:32:47.702: W-1 Completed 1 PROCACT_SCHEMA objects in 0 seconds
17-JUN-26 15:32:47.702: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
17-JUN-26 15:32:47.918: W-1 Completed 1 TABLE objects in 0 seconds
17-JUN-26 15:32:47.928: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
17-JUN-26 15:32:48.343: W-1 . . imported “HR01″.”EMP” 5.523 KB 1 rows in 1 seconds using external_table
17-JUN-26 15:32:48.363: W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
17-JUN-26 15:32:48.414: W-1 Completed 1 TABLE_STATISTICS objects in 0 seconds
17-JUN-26 15:32:48.414: W-1 Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
17-JUN-26 15:32:48.429: W-1 Completed 1 MARKER objects in 0 seconds
17-JUN-26 15:32:48.448: W-1 Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 1 seconds
17-JUN-26 15:32:48.456: Job “ADMIN”.”IMPORT_JOB450″ successfully completed at Wed Jun 17 15:32:48 2026 elapsed 0 00:00:03
PL/SQL procedure successfully completed.
Option 2 – Data Pump Import using impdp
i. Check ADB-S access to Export Dump files.
Use below SQL to list the dump files in NFS.
SQL> SELECT object_name FROM DBMS_CLOUD.LIST_FILES('CUSTOM_NFS');
Below is the output from my environment.
OBJECT_NAME
——————
export.log
hr01.dmp
ii. Download Data Pump Binaries
Download below packages from Oracle Instant Client Downloads.
Basic Package (ZIP) –
Tools Package (ZIP) – Includes expdp and impdp binaries.
I have downloaded 19.31 version.
I have uploaded the packages to Custom NFS server and unzipped them to /home/amalraj_puthenchira/instantclient_19_31/.
iii. Configure environment
Create a tnsnames.ora file and update it with ADB-S TNS alias.
You can get the connection string from ADB-S console. I have used TLS connection string.
It is recommended to use <db_name>_high connection string for Data Pump Import.
Execute below commands.
export LD_LIBRARY_PATH=/home/amalraj_puthenchira/instantclient_19_31/
export PATH=/home/amalraj_puthenchira/instantclient_19_31:$PATH
export TNS_ADMIN=/home/amalraj_puthenchira
iv. Initiate Data Pump Import
Initiate Data Pump Import using below command.
impdp admin@apatp_high directory=custom_nfs dumpfile=hr01.dmp logtime=all \
metrics=yes logfile=import_adb.log
[amalraj_puthenchira@ap-custom-nfs instantclient_19_31]$ ./impdp admin@apatp_high directory=custom_nfs dumpfile=hr01.dmp logtime=all metrics=yes logfile=import_adb.log
Import: Release 19.0.0.0.0 – Production on Wed Jun 17 16:49:44 2026
Version 19.31.0.0.0
Copyright (c) 1982, 2026, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
17-JUN-26 16:49:59.663: W-1 Startup on instance 4 took 0 seconds
17-JUN-26 16:50:00.014: W-1 Master table “ADMIN”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
17-JUN-26 16:50:00.282: Starting “ADMIN”.”SYS_IMPORT_FULL_01″: admin/@apatp_high directory=custom_nfs dumpfile=hr01.dmp logtime=all metrics=yes logfile=import_adb.log
17-JUN-26 16:50:00.307: W-1 Processing object type SCHEMA_EXPORT/USER
17-JUN-26 16:50:00.733: W-1 Completed 1 USER objects in 0 seconds
17-JUN-26 16:50:00.733: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
17-JUN-26 16:50:00.785: W-1 Completed 1 DEFAULT_ROLE objects in 0 seconds
17-JUN-26 16:50:00.785: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
17-JUN-26 16:50:00.830: W-1 Completed 1 TABLESPACE_QUOTA objects in 0 seconds
17-JUN-26 16:50:00.830: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
17-JUN-26 16:50:00.947: W-1 Completed 1 PROCACT_SCHEMA objects in 0 seconds
17-JUN-26 16:50:00.947: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
17-JUN-26 16:50:01.261: W-1 Completed 1 TABLE objects in 1 seconds
17-JUN-26 16:50:01.271: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
17-JUN-26 16:50:01.712: W-1 . . imported “HR01″.”EMP” 5.523 KB 1 rows in 0 seconds using external_table
17-JUN-26 16:50:01.731: W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
17-JUN-26 16:50:01.782: W-1 Completed 1 TABLE_STATISTICS objects in 0 seconds
17-JUN-26 16:50:01.782: W-1 Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
17-JUN-26 16:50:01.797: W-1 Completed 1 MARKER objects in 0 seconds
17-JUN-26 16:50:01.816: W-1 Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
17-JUN-26 16:50:01.824: Job “ADMIN”.”SYS_IMPORT_FULL_01″ successfully completed at Wed Jun 17 16:50:01 2026 elapsed 0 00:00:03
That’s it .
We have successfully migrated our source schema to ADB-S using Data Pump.
Tip :
Please note that Production Database Migration require careful planning and also fine tuning lot of Data Pump parameters to meet various requirements including security and performance.
Please refer Data Pump Best Practices before proceeding with any Production Migration.
Leave a Reply