Hướng dẫn tạo Backup DB oracle trên máy chủ Linux
Bài việt đang hướng dẫn backup viện thanhba.
Bước 1 – tạo thư mục backup (tùy nhu cầu muốn lưu backup ở đâu thì tạo ở thư mục đó)
mkdir /u04/HIS
chown -R oracle.oinstall /u04/HIS
Bước 2: change thư mục Backup trong oracle
su – oracle
sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY backup_dir AS ‘/u04/HIS’;
GRANT READ, WRITE ON DIRECTORY backup_dir TO system;
Bước 3: Tạo script backup , thay đổi các thông tin uid, versoin, thư mục… hoặc script có thể tham khảo từ đây:
https://github.com/JohnTrung/HIS_scripts/tree/main/dump/linux
===================================================
#!/bin/bash
# Author: TrungHT
# Team: IT DevOps Team
# Oracle environment variables
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export ORACLE_SID=thanhba
# Directories
BACKUP_TEMP=”/backups”
TARGET_ROOT=”/u04/HIS”
TODAY=$(date +”%d_%m_%Y”)
TARGET_DIR=”${TARGET_ROOT}/HIS_${TODAY}”
# Create local backup directory
echo “Creating backup directory: $TARGET_DIR”
mkdir -p “$TARGET_DIR” || { echo “Failed to create directory: $TARGET_DIR”; exit 1; }
# Get the current timestamp
TIMESTAMP=$(date +”%Y%m%d_%H%M%S”)
# List of schemas to back up
SCHEMAS=(“ACS_RS” “HIS_RS” “LIS_RS” “QCU_RS” “SAR_RS” “SDA_RS”)
# Step 1: Change system password
echo “Changing system password…”
sqlplus / as sysdba <<EOF
ALTER USER system IDENTIFIED BY orcl123;
EXIT;
EOF
if [[ $? -ne 0 ]]; then
echo “Failed to change system password!”
exit 1
fi
echo “System password changed successfully.”
# Step 2: Backup tablespace structure
TABLESPACE_BACKUP_FILE=”${BACKUP_TEMP}/tablespace_backup_${TIMESTAMP}.sql”
echo “Backing up tablespace structure to: $TABLESPACE_BACKUP_FILE”
sqlplus / as sysdba <<EOF > “${TABLESPACE_BACKUP_FILE}.log”
SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 200;
SPOOL ${TABLESPACE_BACKUP_FILE};
SELECT dbms_metadata.get_ddl(‘TABLESPACE’, tablespace_name)
FROM dba_tablespaces
WHERE tablespace_name IN (‘ACS_RS’, ‘HIS_RS’, ‘LIS_RS’, ‘QCU_RS’, ‘SAR_RS’, ‘SDA_RS’);
SPOOL OFF;
EXIT;
EOF
if [[ ! -f “$TABLESPACE_BACKUP_FILE” ]]; then
echo “Tablespace backup failed!”
exit 1
fi
mv “$TABLESPACE_BACKUP_FILE” “$TARGET_DIR”
mv “${TABLESPACE_BACKUP_FILE}.log” “$TARGET_DIR”
# Step 3: Backup each schema
for SCHEMA in “${SCHEMAS[@]}”; do
DUMP_FILE=”${TARGET_ROOT}/${SCHEMA}_backup_${TIMESTAMP}.dmp”
LOG_FILE=”${TARGET_ROOT}/${SCHEMA}_backup_${TIMESTAMP}.log”
echo “$(date +”%Y-%m-%d %H:%M:%S”) – Backing up schema: $SCHEMA”
expdp system/orcl123 schemas=$SCHEMA directory=backup_dir \
dumpfile=$(basename “$DUMP_FILE”) logfile=$(basename “$LOG_FILE”)
if [[ $? -ne 0 ]]; then
echo “Backup of $SCHEMA failed!”
exit 1
fi
done
# Step 4: Backup EMR_RS with exclusion
EMR_RS_DUMP_FILE=”${TARGET_ROOT}/EMR_RS_${TODAY}.dmp”
EMR_RS_LOG_FILE=”${TARGET_ROOT}/EMR_RS_${TODAY}.log”
echo “$(date +”%Y-%m-%d %H:%M:%S”) – Backing up schema: EMR_RS (excluding EMR_DOCUMENT_CONTENT)”
expdp system/orcl123 schemas=EMR_RS EXCLUDE=TABLE:\”=\’EMR_DOCUMENT_CONTENT\’\” \
directory=backup_dir \
dumpfile=$(basename “$EMR_RS_DUMP_FILE”) \
logfile=$(basename “$EMR_RS_LOG_FILE”)
if [[ $? -ne 0 ]]; then
echo “Backup of EMR_RS failed!”
exit 1
fi
# Step 5: Backup EMR_FINAL
EMR_FINAL_DUMP_FILE=”${TARGET_ROOT}/EMR_FINAL_${TODAY}.dmp”
EMR_FINAL_LOG_FILE=”${TARGET_ROOT}/EMR_FINAL_${TODAY}.log”
echo “$(date +”%Y-%m-%d %H:%M:%S”) – Backing up schema: EMR_FINAL”
expdp system/orcl123 schemas=EMR_FINAL \
directory=backup_dir \
dumpfile=$(basename “$EMR_FINAL_DUMP_FILE”) \
logfile=$(basename “$EMR_FINAL_LOG_FILE”)
if [[ $? -ne 0 ]]; then
echo “Backup of EMR_FINAL failed!”
exit 1
fi
# Step 6: Move all .dmp and .log files from ${TARGET_ROOT} to the target backup directory
echo “Moving .dmp and .log files from ${TARGET_ROOT} to $TARGET_DIR”
mv “${TARGET_ROOT}”/*.dmp “$TARGET_DIR”
mv “${TARGET_ROOT}”/*.log “$TARGET_DIR”
if [[ $? -ne 0 ]]; then
echo “Failed to move files to $TARGET_DIR!”
exit 1
fi
echo “Files successfully moved to: $TARGET_DIR”
# Step 7: Sync backup to secondary location
#echo “Syncing backup to /u03/HIS”
#rsync -rav –delete /HIS/ /u03/HIS/
#
#if [[ $? -ne 0 ]]; then
# echo “Sync failed!”
# exit 1
#fi
#
#echo “Backup sync to /u03/HIS completed successfully.”
============================================
