Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

How to export the dumpfile backup into multiple directory in Oracle database 19C

Posted by Mir Sayeed Hassan on November 13th, 2024

How to export the dumpfile backup into multiple directory in Oracle database 19C

Check the database version

SYS> select name, status, open_mode, version from V$database, v$instance;

NAME        STATUS    OPEN_MODE      VERSION
------------------------------------------------
ORA19CDB    OPEN     READ WRITE     19.0.0.0.0

This scenario is when the client requested to take the table/schema/fulldb dumpfile backup by using the multiple directory due to insufficient space available in 1 directory.

Create 2 different directories into two different locations.

Location 1:

[oracle@ora19cdb ~]$ mkdir -p /u02/backup/dump_bkp/
[oracle@ora19cdb ~]$ chown oracle:oinstalll /u02/backup/dump_bkp/
SYS > create or replace directory dump_bkp1 as '/u02/backup/dump_bkp1/';
Directory created.
SYS > grant read, write on directory dump_bkp1 to system;
Grant succeeded.
SYS > grant read, write on directory dump_bkp1 to hassan;
Grant succeeded.

Location 2:

[oracle@ora19cdb ~]$ mkdir -p /u03/backup/dump_bkp/
[oracle@ora19cdb ~]$ chown oracle:oinstall /u03/backup/dump_bkp/
SYS > create or replace directory dump_bkp2 as '/u03/backup/dump_bkp2/';
Directory created.
SYS > grant read, write on directory dump_bkp2 to system;
Grant succeeded.
SYS > grant read, write on directory dump_bkp2 to hassan;
Grant succeeded.
SYS > col owner for a10
SYS > col directory_name for a20
SYS > col directory_path for a40
SYS > select owner, directory_name, directory_path from dba_directories where directory_name like '%DUMP%';

OWNER    DIRECTORY_NAME           DIRECTORY_PATH
--------------------------------------------------------
SYS       DUMP_BKP1            /u02/backup/dump_bkp1/
SYS       DUMP_BKP2            /u03/backup/dump_bkp2/
SYS > set lines 300 pages 3000
SYS > col grantee for a14
SYS > col owner for a14
SYS > col table_name for a15
SYS > select grantee,owner,table_name,privilege from dba_tab_privs where grantee='HASSAN';

GRANTEE    OWNER   TABLE_NAME        PRIVILEGE
-------------- ------------------------------------------
HASSAN     SYS     DUMP_BKP1         READ
HASSAN     SYS     DUMP_BKP1         WRITE
HASSAN     SYS     DUMP_BKP2         READ
HASSAN     SYS     DUMP_BKP2         WRITE

Start export of the backup in 2 different directories as mention above also use the parallel option to distribute the data across two directories.

[oracle@ora19cdb dump_bkp1]$ expdp schemas=hassan dumpfile=Dump_BKP1:hassan_%U.dmp,DUMP_BKP2:hassan_%U.dmp logfile=hassan.log parallel=2

Export: Release 19.0.0.0.0 - Production on Wed Nov 13 05:26:48 2024
Version 19.18.0.0.0

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

Username: / as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=hassan dumpfile=Dump_BKP1:hassan_%U.dmp,DUMP_BKP2:hassan_%U.dmp logfile=hassan.log parallel=2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows
. . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows
. . exported "HASSAN"."T1" 5.632 KB 9 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u02/backup/dump_bkp1/hassan_01.dmp
/u03/backup/dump_bkp2/hassan_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 13 05:27:51 2024 elapsed 0 00:00:46

Verify the files from 2 different directory path

[oracle@ora19cdb ~]$ ll /u02/backup/dump_bkp1/
total 736
-rw-r-----. 1 oracle oinstall 753664 Nov 13 05:27 hassan_01.dmp
[oracle@ora19cdb ~]$ ll /u03/backup/dump_bkp2/
total 344
-rw-r-----. 1 oracle oinstall 352256 Nov 13 05:27 hassan_01.dmp

=====Hence tested and verified in our test env=====