Hi Readers, in this blogpost i am sharing steps of how you can export a table from a 19c PDB database to a 12.1 PDB database where source schema,table name and tablespace name will be re-mapped at target. Also, we will use advanced compression while taking export backup.
PFB SUMMARY OF SOURCE AND TARGET –
- SOURCE PDB DETAILS –
CHECK TARGET PDB COMPATIBLE –
if compatible is not same then higher db version export backups cannot be imported into lower db version and below error is observed. However you can export from lower to higher without setting any version during bkp.
2. check source table size AND tablespace-
SELECT SEGMENT_NAME,OWNER,ROUND(BYTES/1024/1024/1024,2) GB,SEGMENT_TYPE,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE
SEGMENT_NAME IN (‘S_ORDER’)
If source table has indexes also, then remap index tablespaces as well if same name tablespaces not available at target.
for PDBs export/import create a pdb service entry in tns files and use same to connect to pdb.
service can be created as follows-
3. Now lets take the export backup. As table size is large we will use compression for both data and metadata (compress=all) to reduce dumpfile size (use only if you have advanced compression license ). exclude statistics of table and gather stats at target once import is complete (stats import otherwise take considerable time so this is better option).
Also use parallel option if using enterprise edition to reduce backup time. (as per available cpu and free memory).
Always use a parfile as it can be reused if any error occurs –
run expdp in background as – nohup expdp parfile=exp.par &
import logs –
Export: Release 19.0.0.0.0 – Production on Wed Apr 12 10:07:32 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
;;; ****
;;; Parfile values:
;;; parfile: exclude=STATISTICS
;;; parfile: version=12.1
;;; parfile: compression=all
;;; parfile: dumpfile=exp_S_ORDER_12Apr2023_%U.dmp
;;; parfile: logfile=EXPORT_S_ORDER_12APR23.log
;;; parfile: tables=SIEBEL.S_ORDER
;;; parfile: cluster=N
;;; parfile: parallel=24
;;; parfile: directory=exp
;;; parfile: userid=dbadmin/@PARCHPDB
;;; * Starting “DBADMIN”.”SYS_EXPORT_TABLE_03″: dbadmin/*@PARCHPDB parfile=exp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SIEBEL”.”S_ORDER” 64.89 GB 673703764 rows
Master table “DBADMIN”.”SYS_EXPORT_TABLE_03″ successfully loaded/unloaded
Dump file set for DBADMIN.SYS_EXPORT_TABLE_03 is:
/u03/exp/exp_S_ORDER_12Apr2023_01.dmp
/u03/exp/exp_S_ORDER_12Apr2023_02.dmp
/u03/exp/exp_S_ORDER_12Apr2023_03.dmp
/u03/exp/exp_S_ORDER_12Apr2023_04.dmp
/u03/exp/exp_S_ORDER_12Apr2023_05.dmp
/u03/exp/exp_S_ORDER_12Apr2023_06.dmp
/u03/exp/exp_S_ORDER_12Apr2023_07.dmp
/u03/exp/exp_S_ORDER_12Apr2023_08.dmp
/u03/exp/exp_S_ORDER_12Apr2023_09.dmp
/u03/exp/exp_S_ORDER_12Apr2023_10.dmp
/u03/exp/exp_S_ORDER_12Apr2023_11.dmp
/u03/exp/exp_S_ORDER_12Apr2023_12.dmp
/u03/exp/exp_S_ORDER_12Apr2023_13.dmp
/u03/exp/exp_S_ORDER_12Apr2023_14.dmp
/u03/exp/exp_S_ORDER_12Apr2023_15.dmp
/u03/exp/exp_S_ORDER_12Apr2023_16.dmp
/u03/exp/exp_S_ORDER_12Apr2023_17.dmp
/u03/exp/exp_S_ORDER_12Apr2023_18.dmp
/u03/exp/exp_S_ORDER_12Apr2023_19.dmp
/u03/exp/exp_S_ORDER_12Apr2023_20.dmp
/u03/exp/exp_S_ORDER_12Apr2023_21.dmp
/u03/exp/exp_S_ORDER_12Apr2023_22.dmp
/u03/exp/exp_S_ORDER_12Apr2023_23.dmp
/u03/exp/exp_S_ORDER_12Apr2023_24.dmp
/u03/exp/exp_S_ORDER_12Apr2023_25.dmp
Job “DBADMIN”.”SYS_EXPORT_TABLE_03″ successfully completed at Wed Apr 12 10:27:43 2023 elapsed 0 00:20:07
SCP the dumpfiles to target machine backup mountpoint (where datapump) directory is also created).
3. Check target schema default tablespace and related tablespace free space. atleast 20% extra space should be available as incase of import, table size may increase/decrease depending on datatype and db version and compression settings. In our case we added space to make free space upto 600 GB. if you dont want import to fail due to space issue then set resumable timeout parameter at target ( in seconds), so imp will wait till space is added. ALTER SYSTEM SET RESUMABLE_TIMEOUT=1800;
4. Now let us import the table using parfile. – nohup impdp parfile=imp.par &
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.0 – Production ;;; ************************************************************************** ;;; Parfile values: ;;; parfile: table_exists_action=SKIP ;;; parfile: remap_table=S_ORDER:S_ORDER_NEW ;;; parfile: remap_tablespace=SIEBEL_DATA:ADHOC_SIEBEL_DATA ;;; parfile: remap_schema=SIEBEL:SIEBEL_ADHOC ;;; parfile: dumpfile=exp_S_ORDER_12Apr2023_%U.dmp ;;; parfile: logfile=IMPORT_S_ORDER_12APR23.log ;;; parfile: tables=SIEBEL.S_ORDER ;;; parfile: cluster=N ;;; parfile: parallel=24 ;;; parfile: directory=exp12mar ;;; parfile: userid=dbadmin/********@PUBIPDB ;;; ************************************************************************** Master table “DBADMIN”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded Starting “DBADMIN”.”SYS_IMPORT_TABLE_01″: dbadmin/********@PUBIPDB parfile=imp.par Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported “SIEBEL_ADHOC”.”S_ORDER_NEW” 64.89 GB 673703764 rows
Gather Table stats post import – (use higher degree for faster response).
exec dbms_stats.gather_table_stats(ownname => ‘SIEBEL_ADHOC’,tabname => ‘S_ORDER_NEW’,method_opt =>’FOR ALL COLUMNS SIZE AUTO’,degree => 32,cascade =>true, no_invalidate=> TRUE, granularity=> ‘AUTO’,options=>’GATHER AUTO’);
check table at target. (match rows during export/import).
In our case table size has increased post import as in source compression was enabled at tablespace level, but not enabled at target.
Site Title 