.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

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

2. create schema (dbatools)

CREATE USER dbatools IDENTIFIED BY dbatools;
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;
ALTER USER dbatools QUOTA UNLIMITED ON USERS;

-- Memberikan hak akses dasar kepada user dbatools
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;
 3. create table;

CREATE TABLE backuplogs (
    host VARCHAR2(255 CHAR) NOT NULL,
    backupdate TIMESTAMP NOT NULL,
    schemadb VARCHAR2(255 CHAR) NOT NULL,
    backup VARCHAR2(255 CHAR) NOT NULL,
    size_mb NUMBER(19, 0) NOT NULL,
    size_b NUMBER(19, 0) NOT NULL,
    filename VARCHAR2(255 CHAR) NOT NULL
);

4. run script

sh backupdb_record_to_table.sh

[oracle@teguhth test]$ sh backupdb_record_to_table.sh
 

 

5. check info from table backuplogs

select * from backuplogs;
select host,backupdate,schemadb,backup,size_mb,size_b,filename,(select banner from v$version) as version from backuplogs order by backupdate desc
 

B. script backup using record table

[oracle@teguhth test]$ cat backupdb_record_to_table.sh
##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\"'

vuser="dbatools/dbatools@//10.10.10.19:1521/tgh"

#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 ""

backuppath="/home/oracle/backup"
mkdir -p /home/oracle/backup/$datedir

#path="$backuppath/$year$month$day/$listdb"


#for i in $vschemas; do
for listdb in $vschema; do
    datex=$(date +%Y-%m-%d" "%H:%M:%S)
    dumpfile="${listdb}_${infotgl}.dmp"
    logfile="${listdb}_${infotgl}.log"
    filepath="$backuppath/$dumpfile"

    echo "$datex --->>> Start Backup SchemaDB $listdb"

    # Jalankan expdp untuk backup
    expdp $vlogin DIRECTORY=DATA_PUMP_DIR DUMPFILE=$dumpfile LOGFILE=$logfile SCHEMAS=$listdb REUSE_DUMPFILES=YES

    # Hitung ukuran file backup
    FILE_SIZEFB=$(stat -c%s "$filepath")
    FILE_SIZEFM=$(echo "scale=2; $FILE_SIZEFB / 1024 / 1024" | bc)

    echo "FILEF $filepath"
    echo "FILEFX $dumpfile"
    echo "FILE_SIZEFB $FILE_SIZEFB"
    echo "FILE_SIZEFM $FILE_SIZEFM"
    infofile="/home/oracle/backup/$datedir/${listdb}_${infotgl}.dmp"
    echo "infofile $infofile"

    # Masukkan data backup ke dalam tabel backuplogs
    sqlplus -s "$vuser" <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
INSERT INTO backuplogs (host, backupdate, schemadb, backup, size_mb,size_b, filename)
VALUES ('$Server', TO_TIMESTAMP('$datex', 'YYYY-MM-DD HH24:MI:SS'), '$listdb', 'EXPDP FULL', $FILE_SIZEFM,$FILE_SIZEFB, '$infofile');
COMMIT;
select * FROM backuplogs ORDER BY backupdate DESC FETCH FIRST 1 ROWS ONLY;
EXIT;
EOF

    echo "$datex --->>> Finished Backup SchemaDB $listdb"
    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 test]$