Oracle 表空间自动扩展脚本

#!/bin/bash
#
# Oracle Tablespace Auto Extension Script
# Version: 2.1
#
# Supported:
#   - Oracle 11g Non-CDB
#   - Oracle 19c Non-CDB
#   - Oracle 19c CDB/PDB
#
# Trigger:
#   Used >= 90%
#   AND
#   Free + Growable < 30GB
#
# Action:
#   Add ONE datafile for each matched permanent tablespace:
#     SIZE 100M
#     AUTOEXTEND ON NEXT 100M
#     MAXSIZE 30G
#
# Usage:
#   bash tbs_auto_add_dbf.sh
#   bash tbs_auto_add_dbf.sh --check-only
#

###############################################################################
# Configuration
###############################################################################

PROFILE="/home/oracle/.bash_profile"
BASE_DIR="/home/oracle/jobs"
LOG_DIR="${BASE_DIR}/logs"
EMAIL_RECIPIENTS=""

INITIAL_SIZE="100M"
NEXT_SIZE="100M"
MAX_SIZE="30G"

LOCK_FILE="/tmp/oracle_tablespace_autoextend.lock"

###############################################################################
# Load Oracle environment
###############################################################################

if [[ ! -f "${PROFILE}" ]]; then
    echo "ERROR: Oracle profile not found: ${PROFILE}"
    exit 1
fi

source "${PROFILE}"

HOST_NAME=$(hostname)

mkdir -p "${LOG_DIR}"
LOG_FILE="${LOG_DIR}/pdb_auto_add_dbf_$(date +%Y%m%d).log"

###############################################################################
# Arguments
###############################################################################

CHECK_ONLY=false

case "${1:-}" in
    "")
        ;;
    --check-only)
        CHECK_ONLY=true
        ;;
    *)
        echo "Usage:"
        echo "  $0"
        echo "  $0 --check-only"
        exit 1
        ;;
esac

###############################################################################
# Logging
###############################################################################

log()
{
    local LEVEL="$1"
    shift
    echo "$(date '+%Y-%m-%d %H:%M:%S') [${LEVEL}] $*" | tee -a "${LOG_FILE}"
}

###############################################################################
# Environment validation
###############################################################################

if [[ -z "${ORACLE_SID:-}" ]]; then
    log ERROR "ORACLE_SID is not configured."
    exit 1
fi

if [[ -z "${ORACLE_HOME:-}" ]]; then
    log ERROR "ORACLE_HOME is not configured."
    exit 1
fi

SQLPLUS="${ORACLE_HOME}/bin/sqlplus"

if [[ ! -x "${SQLPLUS}" ]]; then
    log ERROR "sqlplus not found: ${SQLPLUS}"
    exit 1
fi

###############################################################################
# Prevent concurrent execution
###############################################################################

if command -v flock >/dev/null 2>&1; then
    exec 200>"${LOCK_FILE}"
    if ! flock -n 200; then
        log WARN "Another instance of this script is already running."
        exit 0
    fi
else
    log WARN "flock command not found; concurrent execution protection disabled."
fi

###############################################################################
# Email
###############################################################################

MAIL_FILE="/tmp/oracle_tablespace_autoextend_mail_$$.txt"
> "${MAIL_FILE}"

NEED_MAIL=false

send_mail()
{
    if [[ "${NEED_MAIL}" == "true" && -n "${EMAIL_RECIPIENTS}" ]]; then
        if command -v mailx >/dev/null 2>&1; then
            mailx -s "Oracle Tablespace Auto Extension - ${HOST_NAME}" \
                "${EMAIL_RECIPIENTS}" < "${MAIL_FILE}"
            MAIL_RC=$?
            if [[ ${MAIL_RC} -ne 0 ]]; then
                log WARN "mailx returned error code ${MAIL_RC}."
            fi
        else
            log WARN "mailx not found; email notification skipped."
        fi
    fi
    rm -f "${MAIL_FILE}"
}

trap send_mail EXIT

###############################################################################
# Detect Oracle version
###############################################################################

DB_VERSION_OUTPUT=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
SELECT version FROM v\$instance;
EXIT;
EOF
)

SQL_RC=$?

if [[ ${SQL_RC} -ne 0 ]]; then
    log ERROR "Unable to determine Oracle version."
    log ERROR "SQLPlus output: ${DB_VERSION_OUTPUT}"
    exit 1
fi

DB_VERSION=$(echo "${DB_VERSION_OUTPUT}" | xargs)
DB_MAJOR_VERSION=$(echo "${DB_VERSION}" | cut -d'.' -f1)

if ! [[ "${DB_MAJOR_VERSION}" =~ ^[0-9]+$ ]]; then
    log ERROR "Invalid Oracle version returned: ${DB_VERSION}"
    exit 1
fi

log INFO "Oracle version: ${DB_VERSION}"

###############################################################################
# Detect CDB / Non-CDB
###############################################################################

IS_CDB="NO"

if [[ ${DB_MAJOR_VERSION} -ge 12 ]]; then
    CDB_OUTPUT=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
SELECT cdb FROM v\$database;
EXIT;
EOF
)
    SQL_RC=$?
    if [[ ${SQL_RC} -ne 0 ]]; then
        log ERROR "Unable to determine CDB architecture."
        log ERROR "SQLPlus output: ${CDB_OUTPUT}"
        exit 1
    fi
    IS_CDB=$(echo "${CDB_OUTPUT}" | xargs)
fi

log INFO "Database architecture: CDB=${IS_CDB}"

###############################################################################
# Check one database/PDB
###############################################################################

check_tablespaces()
{
    local CONTAINER_NAME="$1"
    local CONTAINER_SQL=""

    log INFO "Checking container: ${CONTAINER_NAME}"

    if [[ "${IS_CDB}" == "YES" ]]; then
        CONTAINER_SQL="ALTER SESSION SET CONTAINER=${CONTAINER_NAME};"
    fi

    # No blank lines are used inside SQL statements below. This deliberately
    # avoids dependence on SQL*Plus SQLBLANKLINES behavior.

    TS_LIST=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 LINES 500 TRIMSPOOL ON
${CONTAINER_SQL}
WITH df AS (
SELECT tablespace_name,
       SUM(user_bytes)/1024/1024 allocated_mb,
       SUM(CASE WHEN autoextensible='YES' THEN GREATEST(maxbytes-user_bytes,0) ELSE 0 END)/1024/1024 growable_mb
FROM dba_data_files
GROUP BY tablespace_name
),
fs AS (
SELECT tablespace_name,
       SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name
)
SELECT df.tablespace_name
FROM df
LEFT JOIN fs ON fs.tablespace_name=df.tablespace_name
WHERE ROUND((df.allocated_mb-NVL(fs.free_mb,0))/NULLIF(df.allocated_mb+df.growable_mb,0)*100,2) >= 90
AND (NVL(fs.free_mb,0)+df.growable_mb) < 30720
AND df.tablespace_name NOT IN ('SYSTEM','SYSAUX')
AND df.tablespace_name NOT LIKE 'UNDO%'
ORDER BY df.tablespace_name;
EXIT;
EOF
)

    SQL_RC=$?

    if [[ ${SQL_RC} -ne 0 ]]; then
        log ERROR "Failed to query tablespace usage in ${CONTAINER_NAME}"
        log ERROR "SQLPlus output: ${TS_LIST}"
        NEED_MAIL=true
        cat >> "${MAIL_FILE}" <<EOF
FAILED
Host       : ${HOST_NAME}
Database   : ${ORACLE_SID}
Container  : ${CONTAINER_NAME}
Action     : Query tablespace usage
Error:
${TS_LIST}
Time       : $(date '+%Y-%m-%d %H:%M:%S')
------------------------------------------------------------
EOF
        return 1
    fi

    TS_LIST=$(echo "${TS_LIST}" | sed '/^[[:space:]]*$/d')

    if [[ -z "${TS_LIST}" ]]; then
        log INFO "No tablespace requires extension in ${CONTAINER_NAME}"
        return 0
    fi

    while IFS= read -r TS_NAME
    do
        TS_NAME=$(echo "${TS_NAME}" | xargs)
        [[ -z "${TS_NAME}" ]] && continue

        # Tablespace names are read from DBA_DATA_FILES, not external input.
        # Restrict to ordinary unquoted Oracle identifier characters before DDL.
        if ! [[ "${TS_NAME}" =~ ^[A-Za-z0-9_\$#]+$ ]]; then
            log ERROR "Unsafe tablespace name returned: ${TS_NAME}"
            NEED_MAIL=true
            continue
        fi

        log WARN "Threshold reached: ${CONTAINER_NAME}.${TS_NAME}"

        BEFORE_INFO=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 LINES 1000
${CONTAINER_SQL}
WITH df AS (
SELECT tablespace_name,
       SUM(user_bytes)/1024/1024 allocated_mb,
       SUM(CASE WHEN autoextensible='YES' THEN GREATEST(maxbytes-user_bytes,0) ELSE 0 END)/1024/1024 growable_mb
FROM dba_data_files
WHERE tablespace_name=UPPER('${TS_NAME}')
GROUP BY tablespace_name
),
fs AS (
SELECT tablespace_name,
       SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
WHERE tablespace_name=UPPER('${TS_NAME}')
GROUP BY tablespace_name
)
SELECT 'ALLOCATED_MB='||ROUND(df.allocated_mb)||
       ' USED_MB='||ROUND(df.allocated_mb-NVL(fs.free_mb,0))||
       ' FREE_MB='||ROUND(NVL(fs.free_mb,0))||
       ' GROWABLE_MB='||ROUND(df.growable_mb)||
       ' AVAILABLE_MB='||ROUND(NVL(fs.free_mb,0)+df.growable_mb)||
       ' USED_PCT='||ROUND((df.allocated_mb-NVL(fs.free_mb,0))/NULLIF(df.allocated_mb+df.growable_mb,0)*100,2)
FROM df
LEFT JOIN fs ON fs.tablespace_name=df.tablespace_name;
EXIT;
EOF
)

        INFO_RC=$?

        if [[ ${INFO_RC} -ne 0 ]]; then
            log ERROR "Unable to query details for ${CONTAINER_NAME}.${TS_NAME}"
            log ERROR "SQLPlus output: ${BEFORE_INFO}"
            NEED_MAIL=true
            continue
        fi

        BEFORE_INFO=$(echo "${BEFORE_INFO}" | xargs)
        log WARN "${CONTAINER_NAME}.${TS_NAME}: ${BEFORE_INFO}"

        if [[ "${CHECK_ONLY}" == "true" ]]; then
            log WARN "CHECK-ONLY: ${CONTAINER_NAME}.${TS_NAME} would be extended."
            continue
        fi

        OMF_OUTPUT=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
${CONTAINER_SQL}
SELECT value FROM v\$parameter WHERE name='db_create_file_dest';
EXIT;
EOF
)

        OMF_RC=$?

        if [[ ${OMF_RC} -ne 0 ]]; then
            log ERROR "Unable to detect OMF for ${CONTAINER_NAME}"
            log ERROR "SQLPlus output: ${OMF_OUTPUT}"
            NEED_MAIL=true
            continue
        fi

        OMF_DEST=$(echo "${OMF_OUTPUT}" | xargs)

        if [[ -n "${OMF_DEST}" ]]; then
            MODE="OMF"
            NEW_FILE="OMF"
            ADD_SQL="ALTER TABLESPACE ${TS_NAME} ADD DATAFILE SIZE ${INITIAL_SIZE} AUTOEXTEND ON NEXT ${NEXT_SIZE} MAXSIZE ${MAX_SIZE};"
        else
            MODE="NON-OMF"

            DATAFILE_DIR_OUTPUT=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
${CONTAINER_SQL}
SELECT SUBSTR(file_name,1,INSTR(file_name,'/',-1))
FROM dba_data_files
WHERE tablespace_name=UPPER('${TS_NAME}')
AND ROWNUM=1;
EXIT;
EOF
)

            DIR_RC=$?

            if [[ ${DIR_RC} -ne 0 ]]; then
                log ERROR "Unable to determine datafile directory for ${CONTAINER_NAME}.${TS_NAME}"
                log ERROR "SQLPlus output: ${DATAFILE_DIR_OUTPUT}"
                NEED_MAIL=true
                continue
            fi

            DATAFILE_DIR=$(echo "${DATAFILE_DIR_OUTPUT}" | xargs)

            if [[ -z "${DATAFILE_DIR}" ]]; then
                log ERROR "Empty datafile directory returned for ${CONTAINER_NAME}.${TS_NAME}"
                NEED_MAIL=true
                continue
            fi

            NEW_FILE="${DATAFILE_DIR}${TS_NAME}_$(date +%Y%m%d_%H%M%S).dbf"
            ADD_SQL="ALTER TABLESPACE ${TS_NAME} ADD DATAFILE '${NEW_FILE}' SIZE ${INITIAL_SIZE} AUTOEXTEND ON NEXT ${NEXT_SIZE} MAXSIZE ${MAX_SIZE};"
        fi

        ADD_RESULT=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK ON VERIFY OFF ECHO OFF
${CONTAINER_SQL}
${ADD_SQL}
EXIT;
EOF
)

        ADD_RC=$?

        if [[ ${ADD_RC} -eq 0 ]]; then
            log INFO "Datafile added successfully: ${CONTAINER_NAME}.${TS_NAME}"
            if [[ "${MODE}" == "OMF" ]]; then
                log INFO "Mode=OMF, SIZE=${INITIAL_SIZE}, NEXT=${NEXT_SIZE}, MAXSIZE=${MAX_SIZE}"
            else
                log INFO "Mode=NON-OMF, File=${NEW_FILE}"
            fi
            NEED_MAIL=true
            cat >> "${MAIL_FILE}" <<EOF
SUCCESS
Host       : ${HOST_NAME}
Database   : ${ORACLE_SID}
Container  : ${CONTAINER_NAME}
Tablespace : ${TS_NAME}
Mode       : ${MODE}
Before     : ${BEFORE_INFO}
File       : ${NEW_FILE}
Initial    : ${INITIAL_SIZE}
Next       : ${NEXT_SIZE}
Max Size   : ${MAX_SIZE}
Time       : $(date '+%Y-%m-%d %H:%M:%S')
------------------------------------------------------------
EOF
        else
            log ERROR "Failed to extend ${CONTAINER_NAME}.${TS_NAME}"
            log ERROR "SQLPlus output: ${ADD_RESULT}"
            NEED_MAIL=true
            cat >> "${MAIL_FILE}" <<EOF
FAILED
Host       : ${HOST_NAME}
Database   : ${ORACLE_SID}
Container  : ${CONTAINER_NAME}
Tablespace : ${TS_NAME}
Mode       : ${MODE}
Before     : ${BEFORE_INFO}
Error:
${ADD_RESULT}
Time       : $(date '+%Y-%m-%d %H:%M:%S')
------------------------------------------------------------
EOF
        fi
    done <<< "${TS_LIST}"

    return 0
}

###############################################################################
# Main
###############################################################################

log INFO "============================================================"
log INFO "Oracle tablespace auto-extension check started."
log INFO "Host=${HOST_NAME}"
log INFO "ORACLE_SID=${ORACLE_SID}"
log INFO "Oracle=${DB_VERSION}"
log INFO "CDB=${IS_CDB}"
log INFO "Threshold: Used >= 90%, Available < 30GB"

if [[ "${CHECK_ONLY}" == "true" ]]; then
    log INFO "Mode: CHECK-ONLY"
else
    log INFO "Mode: AUTO-EXTEND"
fi

log INFO "============================================================"

if [[ "${IS_CDB}" == "YES" ]]; then
    PDB_LIST=$(
"${SQLPLUS}" -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 LINES 500 TRIMSPOOL ON
SELECT name
FROM v\$pdbs
WHERE open_mode='READ WRITE'
ORDER BY con_id;
EXIT;
EOF
)

    SQL_RC=$?

    if [[ ${SQL_RC} -ne 0 ]]; then
        log ERROR "Unable to obtain READ WRITE PDB list."
        log ERROR "SQLPlus output: ${PDB_LIST}"
        NEED_MAIL=true
        cat >> "${MAIL_FILE}" <<EOF
FAILED
Host       : ${HOST_NAME}
Database   : ${ORACLE_SID}
Action     : Get READ WRITE PDB list
Error:
${PDB_LIST}
Time       : $(date '+%Y-%m-%d %H:%M:%S')
------------------------------------------------------------
EOF
        exit 1
    fi

    PDB_LIST=$(echo "${PDB_LIST}" | sed '/^[[:space:]]*$/d')

    if [[ -z "${PDB_LIST}" ]]; then
        log WARN "No READ WRITE PDB found."
    else
        while IFS= read -r PDB_NAME
        do
            PDB_NAME=$(echo "${PDB_NAME}" | xargs)
            [[ -z "${PDB_NAME}" ]] && continue

            if ! [[ "${PDB_NAME}" =~ ^[A-Za-z0-9_\$#]+$ ]]; then
                log ERROR "Unsafe PDB name returned: ${PDB_NAME}"
                NEED_MAIL=true
                continue
            fi

            check_tablespaces "${PDB_NAME}"
        done <<< "${PDB_LIST}"
    fi
else
    check_tablespaces "NON-CDB"
fi

log INFO "============================================================"
log INFO "Oracle tablespace auto-extension check completed."
log INFO "============================================================"

exit 0