Saturday, 18 January 2025

Using EXPDP to export a Full Oracle Database and break into multiple DUMP Files

Using EXPDP to export a FULL Oracle database backup is an easy task. It gets a little complex when you have specific requirements with what to extract, for example: export a single table or schema, or some particular query output, or INCLUDE some list of object and vice versa i.e. EXCLUDING some objects from the backup. Well I intend to provide a full picture on various options with examples but it will increase the post length to uncontrolled manner so I'll break down each type of activity and then create a MASTER article explaining all features for your help.

For this post, I'll show your 2 things,

  • Exporting a FULL Database backup.
  • Breaking the DUMP file into multiple pieces.

Note: I am running this on my personal laptop for practice sessions. Due to limitation of resources, I might do something that you shouldn't do in REAL WORLD. So be careful and improvise as per your scenario. If you are also learning, feel free to play with database, that's how you learn :)


Let's begin.

1) Login to the database you want to export.

[oracle@oravm19c admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 17 12:43:17 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

2) Create directory you want to use in export. This directory will hold the DUMP files'. Also, I'll be using SYS for the EXPDP so I don't need to grant anything to this schema. If you are using some other user to EXPORT. You'll need to grant that user needed privilege.

SQL> create directory DB_DUMP as '/u01/app/expdp';

Directory created.

Let's say you want to use XYZ schema for this FULL export. Then add the below.

SQL> grant read, write on directory DB_DUMP to XYZ;
SQL> grant DATAPUMP_EXP_FULL_DATABASE to XYZ;

3) Now just simply export the FULL database export as shown below.

[oracle@oravm19c admin]$ expdp directory=DB_DUMP full=y dumpfile=full%U.dmp filesize=200M logfile=full.log

Export: Release 19.0.0.0.0 - Production on Fri Jan 17 12:51:46 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_FULL_01":  sys/******** AS SYSDBA directory=DB_DUMP full=y dumpfile=full%U.dmp filesize=200M logfile=full.log 
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.054 KB      36 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P181"           135.4 KB     163 rows
. . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows
. . exported "WMSYS"."WM$HINT_TABLE$"                    9.984 KB      97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS"               6.960 KB       2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.078 KB      11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.375 KB       1 rows
. . exported "LBACSYS"."OLS$PROPS"                       6.234 KB       5 rows
. . exported "WMSYS"."WM$ENV_VARS$"                      6.015 KB       3 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.812 KB      12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.757 KB       8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.539 KB       2 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows
. . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows
. . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows
. . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows
. . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows
. . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows
. . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows
. . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows
. . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows
. . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
. . exported "MDSYS"."RDF_PARAM$"                        6.515 KB       3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP"                       0 KB       0 rows
. . exported "SYS"."SQL$_DATAPUMP"                           0 KB       0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"                 0 KB       0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                    0 KB       0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP"                        0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.648 KB       4 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           9.726 KB      13 rows
. . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows
. . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/expdp/full01.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Fri Jan 17 13:16:14 2025 elapsed 0 00:23:57

4) Verify the DUMP files extracted on physical server.

[oracle@oravm19c expdp]$ ls -lrt
total 3620
-rw-r-----. 1 oracle oinstall 3694592 Jan 17 13:16 full01.dmp
-rw-r--r--. 1 oracle oinstall    9164 Jan 17 13:16 full.log
[oracle@oravm19c expdp]$ 

Since it's a blank database that I am using, only 1 DUMP file is created but it's having '01' suffix since I used %U option. If you have a large database, there should be multiple files with continuous sequence depending upon the FILESIZE parameter specified in EXPDP command. Also, based on your DB size estimate, you should decide FILESIZE parameter so that full dump is broken in manageable number of sub pieces.

I hope this helps !!

No comments:

Post a Comment