.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration with insert client table :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-expdp-backup-daily-database.html

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 backuplogduration (
    host VARCHAR2(255 CHAR) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start_time TIMESTAMP NOT NULL,  -- Mengganti "start" karena bisa jadi kata kunci
    finish_time TIMESTAMP NOT NULL,
    duration VARCHAR2(255 CHAR) 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. create table client

CREATE TABLE tbl_clients (
    client_id VARCHAR2(20) PRIMARY KEY,  -- ID unik dalam format id-client-XX
    client_name VARCHAR2(255) NOT NULL,  -- Nama client
    dbname VARCHAR2(255) NOT NULL UNIQUE,  -- Nama database client
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Timestamp pembuatan data
);

5. insert table client

INSERT INTO tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-01', 'Client A', 'ADMIN');

INSERT INTO tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-02', 'Client B', 'ADVWORKS');

INSERT INTO tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-03', 'Client C', 'AISYAH');

INSERT INTO tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-22', 'Client Teguh', 'DBATOOLS');

commit;
 

6. if delete

delete from tbl_clients;
delete from BACKUPLOGDURATION;
commit;

7. run script
 

8. check file
 

9. check record
select * from tbl_clients;
select * from BACKUPLOGDURATION order by backuptime desc;
 

10. script

[oracle@teguhth test]$ pwd
/home/oracle/script/test
[oracle@teguhth test]$
[oracle@teguhth test]$ cat backupdb_record_to_table_duration_insert.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);

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

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 dbatools/dbatools@//10.10.10.19:1521/tgh <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT username FROM dba_users WHERE username IN (SELECT dbname FROM tbl_clients);
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"

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

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

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

# Menghitung selisih waktu dalam detik
finish=$((epoch_endf - epoch_startf))

# Menghitung hari, jam, menit, dan detik
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))

# Format output
formatted_time=$(printf "%d:%02d:%02d:%02d" "$days" "$hours" "$minutes" "$seconds")

echo "Duration Process"
echo "Duration : $formatted_time"
echo ""


    # Masukkan data backup ke dalam tabel backuplogs
    sqlplus -s "$vuser" <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
INSERT INTO backuplogduration (host, backuptime, start_time, finish_time, duration,schemadb, backup, size_mb,size_b, filename)
VALUES ('$Server', TO_TIMESTAMP('$backuptime', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('$startbackup', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('$finishbackup', 'YYYY-MM-DD HH24:MI:SS'),'$formatted_time', '$listdb', 'EXPDP FULL', $FILE_SIZEFM,$FILE_SIZEFB, '$infofile');

COMMIT;
select * FROM backuplogduration ORDER BY backuptime 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]$