Tag Archives: Logical backup script for oracle

EXPDP and IMPDP Backup Scripts in Linux

1 Reply

EXPDP and IMPDP backup Script in Linux platform

You can simply copy paste the script and change the location present in the script and save as shell script. It is also used to scheduled backup in crontab.

  1. Create Directory in Database
CREATE OR REPLACE DIRECTORY EXPDIR AS '/u01/backup/expdp';
GRANT READ, WRITE ON DIRECTORY EXPDIR TO SYSTEM;

Parameter Details for scripts:
DATE Parameter: is used for current date.
ORACLE_SID Parameter: is used to mention your oracle database SID.
EXP_ORADIR Parameter: is used to define the dump directory present in your database for backup.

Script for EXPDP in Oracle

#!/bin/bash

# Variables
DATE=$(date +%Y%m%d_%H%M%S)
USER=system
PASS=YourPassword
ORACLE_SID=orcl
DIR=EXPDIR
BACKUP_DIR=/u01/backup/expdp

export ORACLE_SID

# Create backup folder for this run
mkdir -p $BACKUP_DIR/$DATE

expdp $USER/$PASS \
   DIRECTORY=$DIR \
   DUMPFILE=full_db_$DATE.dmp \
   LOGFILE=expdp_full_$DATE.log \
   FULL=Y \
   COMPRESSION=ALL

# Move dump & log to timestamp folder
mv $BACKUP_DIR/full_db_$DATE.* $BACKUP_DIR/$DATE

echo "EXPDP completed: $BACKUP_DIR/$DATE"

IMPDP Script is used to import the dump file generated by EXPDP backup utility.
You simply copy paste the following script change the parameter as needed and save it as shell script

Scripts for IMPDP datapump process in Oracle

#!/bin/bash

# Variables
USER=system
PASS=YourPassword
ORACLE_SID=orcl
DIR=EXPDIR
DUMPFILE=full_db_20241206_120000.dmp   # Change this
LOGFILE=impdp_full.log

export ORACLE_SID

impdp $USER/$PASS \
  DIRECTORY=$DIR \
  DUMPFILE=$DUMPFILE \
  LOGFILE=$LOGFILE \
  FULL=Y \
  TABLE_EXISTS_ACTION=REPLACE \
  REMAP_TABLESPACE=OLD_TS:NEW_TS

echo "IMPDP completed."

Schema Level Export (Example)

Expdp schema backup:

expdp system/YourPassword \
  DIRECTORY=EXPDIR \
  DUMPFILE=schema_$(date +%Y%m%d).dmp \
  LOGFILE=schema_export.log \
  SCHEMAS=HR \
  COMPRESSION=ALL

Import Schema:

impdp system/YourPassword \
  DIRECTORY=EXPDIR \
  DUMPFILE=schema_20241206.dmp \
  LOGFILE=schema_import.log \
  SCHEMAS=HR \
  TABLE_EXISTS_ACTION=APPEND