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