.::: Script Daily Backup Database Oracle using expdp :::.

 

1. create dba directory

https://teguhth.blogspot.com/2024/12/how-to-backup-and-restore-table.html

su - oracle
mkdir -p /home/oracle/backup
mkdir -p  /u01/backup/daily

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ADMIN;

CREATE OR REPLACE DIRECTORY BACKUP_DAILY AS '/u01/backup/daily';
GRANT READ, WRITE ON DIRECTORY BACKUP_DAILY TO aisyah;
 

SELECT directory_name, directory_path
FROM dba_directories

 

2. run script

[oracle@teguhth script]$ sh  /home/oracle/script/backup_expdp_all.sh
 

3. check backup result
 

4. result
 

5. this script backup

 

[oracle@teguhth script]$ cat backup_expdp_all.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date +%Y""%m""%d"_"%H""%M""%S"");
dbversion=$(sqlplus /NOLOG < /dev/null | grep Release | cut -d' ' -f3);

echo ".::: Backup Database Oracle $dbversion using EXPDB in '$Server' with $ip_address :::."
echo ".::: Backup Database Oracle using EXPDB in '$Server $dbversion' with $ip_address :::." >> ${Server}_logdbbackup_${infotgl}.txt
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
date=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
datedir=$(date +"%Y%m%d")

#

vlogin=$'\"sys/oracle as sysdba\"'


#vschema=$(
#sqlplus -s aisyah/hanin@//10.10.10.19:1521/tgh <<EOF
#SET HEADING OFF
#SET FEEDBACK OFF
#SET PAGESIZE 0
#SELECT username FROM dba_users WHERE account_status = 'OPEN' ORDER BY username;
#EXIT;
#EOF
#)


vschsql=$(
sqlplus -s aisyah/hanin@//10.10.10.19:1521/tgh <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT username FROM dba_users WHERE account_status = 'OPEN' ORDER BY username;
EXIT;
EOF
)

# Menggabungkan hasil menjadi satu baris


#vschema=$("$(echo "$vschsql" | tr '\n' ' ')")

vschema=$vschsql

#vschema='aisyah,admin'
echo ""
#echo "schemanya $vschema"
echo "Backup Schema for $(echo "$vschema" | tr '\n' ' ')"
echo "Backup Schema for $(echo "$vschema" | tr '\n' ' ')" >> ${Server}_logdbbackup_${infotgl}.txt
echo ""
#for i in $vschemas; do
for listdb in $vschema ;
do

datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");

echo "$datex --->>> Start Backup SchemaDB $listdb"
echo "$datex --->>> Start Backup SchemaDB $listdb" >> ${Server}_logdbbackup_${infotgl}.txt

expdp $vlogin DIRECTORY=DATA_PUMP_DIR content=all DUMPFILE=${listdb}_${infotgl}.dmp LOGFILE=${listdb}_${infotgl}.log SCHEMAS=$listdb REUSE_DUMPFILES=YES

datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");

echo "$datex --->>> Finished Backup SchemaDB $listdb"
echo "$datex --->>> Finished Backup SchemaDB $listdb" >> ${Server}_logdbbackup_${infotgl}.txt
echo ""
done

datez=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");

echo "Backup All Schema Finished from $date to $datez"
echo "Backup All Schema Finished from $date to $datez" >> ${Server}_logdbbackup_${infotgl}.txt

#datefnh=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");

#echo "Backup All SchemaDB is done at $datefnh"

mkdir -p /home/oracle/backup/$datedir
#mv /home/oracle/backup/* /home/oracle/backup/$datedir
find /home/oracle/backup -maxdepth 1 -type f -exec mv {} /home/oracle/backup/$datedir \;
datefile='/home/oracle/backup/'
echo "Check File Backup in $datefile$datedir"
echo "Check File Backup in $datefile$datedir"  >> ${Server}_logdbbackup_${infotgl}.txt
ls -lh /home/oracle/backup/$datedir
ls -lh /home/oracle/backup/$datedir >> ${Server}_logdbbackup_${infotgl}.txt
echo ""

echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"

echo "Copyright by           : Teguh Triharto" >> ${Server}_logdbbackup_${infotgl}.txt
echo "Website                : https://www.linkedin.com/in/teguhth" >> ${Server}_logdbbackup_${infotgl}.txt

cat ${Server}_logdbbackup_${infotgl}.txt
echo ""

echo ""
[oracle@teguhth script]$