Thursday, November 13, 2025
#!/bin/bash
################################################################################
# Oracle 19c RAC Database Administration - Common Utility Functions
# Description: Common utility functions used across all administration tasks
# Includes integrated Data Guard common functions
# Created: 2025-11-02
# Updated: 2025-11-10 - Integrated DG common functions
# Updated: 2025-11-14 - DATABASE LOADING FIX APPLIED
################################################################################
################################################################################
# CRITICAL: Global Database Arrays - MUST BE DECLARED BEFORE ANY FUNCTIONS
# These arrays store loaded database configurations for menu display
################################################################################
declare -g -a DB_NAMES=()
declare -g -a DB_HOSTS=()
declare -g -a DB_SERVICES=()
# Global variables for selected database
declare -g SELECTED_DB_NAME=""
declare -g SELECTED_DB_HOST=""
declare -g SELECTED_DB_SERVICE=""
################################################################################
# LOGGING AND UTILITY FUNCTIONS
################################################################################
################################################################################
# Function: log_message
# Description: Logs messages with timestamp and log level
# Parameters: $1 - Log Level (INFO, WARN, ERROR, DEBUG)
# $2 - Message
################################################################################
log_message() {
local log_level=$1
local message=$2
local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
local log_file="${LOG_BASE_DIR}/oracle_admin_$(date '+%Y%m%d').log"
# Create log directory if it doesn't exist
mkdir -p "${LOG_BASE_DIR}"
# Log message
echo "[${timestamp}] [${log_level}] ${message}" >> "${log_file}"
# Also print to console if not silent mode
if [[ "${SILENT_MODE}" != "YES" ]]; then
case ${log_level} in
ERROR)
echo -e "\033[0;31m[${timestamp}] [${log_level}] ${message}\033[0m"
;;
WARN)
echo -e "\033[0;33m[${timestamp}] [${log_level}] ${message}\033[0m"
;;
INFO)
echo -e "\033[0;32m[${timestamp}] [${log_level}] ${message}\033[0m"
;;
*)
echo "[${timestamp}] [${log_level}] ${message}"
;;
esac
fi
# Audit logging
if [[ "${ENABLE_AUDIT_LOG}" == "YES" ]] && [[ "${log_level}" == "ERROR" || "${log_level}" == "WARN" ]]; then
echo "[${timestamp}] [${log_level}] [${USER}] ${message}" >> "${AUDIT_LOG_FILE}"
fi
}
################################################################################
# Function: validate_prerequisites
# Description: Validates all prerequisites before executing any task
# Returns: 0 if success, 1 if failure
################################################################################
validate_prerequisites() {
log_message "INFO" "Validating prerequisites..."
# Check if Oracle Home is set
if [[ -z "${ORACLE_HOME}" ]]; then
log_message "ERROR" "ORACLE_HOME is not set"
return 1
fi
# Check if Oracle binaries exist
if [[ ! -f "${ORACLE_HOME}/bin/sqlplus" ]]; then
log_message "ERROR" "sqlplus binary not found in ${ORACLE_HOME}/bin"
return 1
fi
# Check if configuration file exists
if [[ ! -f "${CONFIG_FILE}" ]]; then
log_message "ERROR" "Configuration file not found: ${CONFIG_FILE}"
return 1
fi
# Check if database list file exists
if [[ ! -f "${DATABASE_LIST_FILE}" ]]; then
log_message "ERROR" "Database list file not found: ${DATABASE_LIST_FILE}"
return 1
fi
# Create required directories
mkdir -p "${LOG_BASE_DIR}" "${REPORT_BASE_DIR}" "${BACKUP_DIR}"
log_message "INFO" "Prerequisites validated successfully"
return 0
}
################################################################################
# DATABASE CONNECTION AND VALIDATION FUNCTIONS
################################################################################
################################################################################
# Function: load_database_list
# Description: Loads database configuration from database_list.txt
# Parameters: $1 - Database name (or "ALL" for all databases)
# Returns: Database configuration line(s) in format: DB_NAME|SCAN|SERVICE
# Empty if database not found
# Exit: Exits script if database not found or validation fails
################################################################################
load_database_list() {
local db_name=$1
log_message "INFO" "Loading database list for: ${db_name}"
# Validate database list file exists and is readable
if [[ ! -f "${DATABASE_LIST_FILE}" ]]; then
log_message "ERROR" "Database list file not found: ${DATABASE_LIST_FILE}"
echo ""
return 1
fi
if [[ ! -r "${DATABASE_LIST_FILE}" ]]; then
log_message "ERROR" "Database list file is not readable: ${DATABASE_LIST_FILE}"
echo ""
return 1
fi
# Check if file is empty
if [[ ! -s "${DATABASE_LIST_FILE}" ]]; then
log_message "ERROR" "Database list file is empty: ${DATABASE_LIST_FILE}"
echo ""
return 1
fi
local result=""
local found=0
# Read file line by line
while IFS= read -r line || [[ -n "$line" ]]; do
# Skip empty lines and comments
[[ -z "$line" || "$line" =~ ^[[:space:]]*# ]] && continue
# Validate line format (should have exactly 3 fields separated by |)
local field_count=$(echo "$line" | awk -F'|' '{print NF}')
if [[ $field_count -ne 3 ]]; then
log_message "WARN" "Invalid line format (expected 3 fields): $line"
continue
fi
# Parse the line
IFS='|' read -r db scan service <<< "$line"
# Trim whitespace
db=$(echo "$db" | xargs)
scan=$(echo "$scan" | xargs)
service=$(echo "$service" | xargs)
# Validate each field is not empty
if [[ -z "$db" || -z "$scan" || -z "$service" ]]; then
log_message "WARN" "Line has empty fields: $line"
continue
fi
# Check if this is the database we're looking for or if we want all
if [[ "${db_name}" == "ALL" ]] || [[ "${db}" == "${db_name}" ]]; then
if [[ -n "$result" ]]; then
result="${result}\n${db}|${scan}|${service}"
else
result="${db}|${scan}|${service}"
fi
found=1
log_message "INFO" "Found database: ${db} -> ${scan}/${service}"
# If looking for specific database, we can stop here
if [[ "${db_name}" != "ALL" ]]; then
break
fi
fi
done < "${DATABASE_LIST_FILE}"
# Check if we found any databases
if [[ $found -eq 0 ]]; then
if [[ "${db_name}" == "ALL" ]]; then
log_message "ERROR" "No valid database entries found in ${DATABASE_LIST_FILE}"
else
log_message "ERROR" "Database '${db_name}' not found in ${DATABASE_LIST_FILE}"
fi
echo ""
return 1
fi
# Return the result
echo -e "$result"
return 0
}
################################################################################
# Function: load_database_list_to_arrays
# Description: Load ALL databases from configuration file into global arrays
# This function is for menu display and database selection
# Usage: load_database_list_to_arrays
# Returns: 0 on success, 1 on failure
# Populates: DB_NAMES[], DB_HOSTS[], DB_SERVICES[] global arrays
################################################################################
load_database_list_to_arrays() {
local db_list_file="${DATABASE_LIST_FILE}"
# Clear existing arrays
DB_NAMES=()
DB_HOSTS=()
DB_SERVICES=()
# Check if file exists
if [[ ! -f "$db_list_file" ]]; then
log_message "ERROR" "Database list file not found: $db_list_file"
echo "ERROR: Database list file not found: $db_list_file"
return 1
fi
# Check if file is readable
if [[ ! -r "$db_list_file" ]]; then
log_message "ERROR" "Database list file not readable: $db_list_file"
echo "ERROR: Database list file not readable: $db_list_file"
return 1
fi
log_message "INFO" "Loading database list into arrays from: $db_list_file"
local line_num=0
local valid_entries=0
local skipped_lines=0
# Read file line by line - preserve exact line content with IFS=
while IFS= read -r line || [[ -n "$line" ]]; do
((line_num++))
# Skip empty lines
[[ -z "$line" ]] && continue
# Skip comment lines
[[ "$line" =~ ^[[:space:]]*# ]] && continue
# Parse using awk for reliable field separation
local db_name=$(echo "$line" | awk -F'|' '{print $1}' | xargs)
local scan_host=$(echo "$line" | awk -F'|' '{print $2}' | xargs)
local service_name=$(echo "$line" | awk -F'|' '{print $3}' | xargs)
# Validate that all three fields are present and non-empty
if [[ -z "$db_name" || -z "$scan_host" || -z "$service_name" ]]; then
log_message "WARN" "Line $line_num: Incomplete entry (missing fields) - skipping: $line"
((skipped_lines++))
continue
fi
# Validate field count (should be exactly 3 fields)
local field_count=$(echo "$line" | awk -F'|' '{print NF}')
if [[ $field_count -ne 3 ]]; then
log_message "WARN" "Line $line_num: Invalid format (expected 3 fields, got $field_count) - skipping: $line"
((skipped_lines++))
continue
fi
# Add to global arrays
DB_NAMES+=("$db_name")
DB_HOSTS+=("$scan_host")
DB_SERVICES+=("$service_name")
((valid_entries++))
log_message "DEBUG" "Loaded database entry $valid_entries: $db_name | $scan_host | $service_name"
done < "$db_list_file"
# Report results
if [[ $valid_entries -eq 0 ]]; then
log_message "ERROR" "No valid database entries found in $db_list_file"
echo ""
echo "ERROR: No valid database entries found!"
echo "Please check the format of $db_list_file"
echo "Expected format: DB_NAME|SCAN_HOST|SERVICE_NAME"
echo ""
return 1
fi
log_message "INFO" "Successfully loaded $valid_entries database entries from $db_list_file"
if [[ $skipped_lines -gt 0 ]]; then
log_message "WARN" "Skipped $skipped_lines invalid lines"
fi
return 0
}
################################################################################
# Function: display_database_menu
# Description: Display database selection menu from loaded arrays
# Usage: display_database_menu "Menu Title"
# Returns: 0 on success, 1 if no databases loaded
################################################################################
display_database_menu() {
local title="${1:-Select Database}"
echo ""
echo "=========================================="
echo "$title"
echo "=========================================="
# Check if databases are loaded
if [[ ${#DB_NAMES[@]} -eq 0 ]]; then
echo ""
echo "ERROR: No databases available!"
echo "Please check your database_list.txt file."
echo ""
return 1
fi
# Display database options with connection info
for i in "${!DB_NAMES[@]}"; do
local menu_num=$((i + 1))
printf "%2d. %-15s (%s)\n" "$menu_num" "${DB_NAMES[$i]}" "${DB_HOSTS[$i]}"
done
echo ""
printf "%2d. Return to Main Menu\n" 0
echo "=========================================="
echo ""
return 0
}
################################################################################
# Function: select_database_from_menu
# Description: Prompt user to select a database from the loaded arrays
# Usage: select_database_from_menu "Menu Title"
# Returns: Selected database index (0-based) in $? or 255 for cancel
################################################################################
select_database_from_menu() {
local title="${1:-Select Database}"
# Display the menu
if ! display_database_menu "$title"; then
return 255 # Menu display failed
fi
local choice
read -p "Enter your choice [0-${#DB_NAMES[@]}]: " choice
# Validate input is a number
if ! [[ "$choice" =~ ^[0-9]+$ ]]; then
echo ""
echo "ERROR: Invalid input - please enter a number"
echo ""
return 255
fi
# Check for cancel (0)
if [[ $choice -eq 0 ]]; then
log_message "INFO" "User cancelled database selection"
return 255
fi
# Convert menu choice to 0-based array index
local index=$((choice - 1))
# Validate index is within array bounds
if [[ $index -lt 0 || $index -ge ${#DB_NAMES[@]} ]]; then
echo ""
echo "ERROR: Invalid selection"
echo "Please choose a number between 1 and ${#DB_NAMES[@]}, or 0 to cancel"
echo ""
return 255
fi
# Valid selection
log_message "INFO" "User selected database index $index: ${DB_NAMES[$index]}"
return $index
}
################################################################################
# Function: get_database_info_by_index
# Description: Get database information by index and set global variables
# Usage: get_database_info_by_index
# Sets: SELECTED_DB_NAME, SELECTED_DB_HOST, SELECTED_DB_SERVICE
# Returns: 0 on success, 1 on failure
################################################################################
get_database_info_by_index() {
local index=$1
# Validate index
if [[ -z "$index" ]]; then
log_message "ERROR" "get_database_info_by_index: No index provided"
return 1
fi
if ! [[ "$index" =~ ^[0-9]+$ ]]; then
log_message "ERROR" "get_database_info_by_index: Invalid index (not a number): $index"
return 1
fi
if [[ $index -lt 0 || $index -ge ${#DB_NAMES[@]} ]]; then
log_message "ERROR" "get_database_info_by_index: Index out of range: $index (valid: 0-$((${#DB_NAMES[@]} - 1)))"
return 1
fi
# Set global variables
SELECTED_DB_NAME="${DB_NAMES[$index]}"
SELECTED_DB_HOST="${DB_HOSTS[$index]}"
SELECTED_DB_SERVICE="${DB_SERVICES[$index]}"
log_message "INFO" "Selected database: $SELECTED_DB_NAME @ $SELECTED_DB_HOST ($SELECTED_DB_SERVICE)"
return 0
}
################################################################################
# Function: display_selected_database_info
# Description: Display information about the currently selected database
# Usage: display_selected_database_info
################################################################################
display_selected_database_info() {
echo ""
echo "Selected Database Information:"
echo " Database Name: $SELECTED_DB_NAME"
echo " SCAN Host: $SELECTED_DB_HOST"
echo " Service Name: $SELECTED_DB_SERVICE"
echo ""
}
################################################################################
# Function: test_db_connection
# Description: Tests database connectivity using sqlplus
# Parameters: $1 - SCAN address
# $2 - Service name
# Returns: 0 if connection successful, 1 if failed
# Output: Prints error message on failure
################################################################################
test_db_connection() {
local scan=$1
local service=$2
local connect_string="${SYS_USER}/${SYS_PASSWORD}@${scan}/${service} as sysdba"
log_message "INFO" "Testing connection to ${scan}/${service}..."
# Test connection with a simple query
local test_result=$(${ORACLE_HOME}/bin/sqlplus -S /nolog << EOF
whenever sqlerror exit 1
whenever oserror exit 1
connect ${connect_string}
set heading off feedback off pagesize 0
select 'CONNECTION_SUCCESS' from dual;
exit;
EOF
)
local exit_code=$?
if [[ $exit_code -eq 0 ]] && echo "${test_result}" | grep -q "CONNECTION_SUCCESS"; then
log_message "INFO" "Connection successful to ${scan}/${service}"
return 0
else
log_message "ERROR" "Connection failed to ${scan}/${service}"
log_message "ERROR" "Error details: ${test_result}"
return 1
fi
}
################################################################################
# Function: validate_database_connection
# Description: Validates database connection and exits if it fails
# Parameters: $1 - Database name
# $2 - SCAN address
# $3 - Service name
# Exit: Exits script with error code 1 if connection fails
################################################################################
validate_database_connection() {
local db_name=$1
local scan=$2
local service=$3
log_message "INFO" "Validating connection to database: ${db_name}"
if ! test_db_connection "${scan}" "${service}"; then
log_message "ERROR" "Cannot connect to database ${db_name}"
log_message "ERROR" "SCAN: ${scan}, Service: ${service}"
log_message "ERROR" "Script execution stopped due to connection failure"
echo ""
echo "╔════════════════════════════════════════════════════════════════╗"
echo "║ DATABASE CONNECTION VALIDATION FAILED ║"
echo "╠════════════════════════════════════════════════════════════════╣"
echo "║ Database: ${db_name}"
echo "║ SCAN: ${scan}"
echo "║ Service: ${service}"
echo "║"
echo "║ Possible causes:"
echo "║ 1. Database is down"
echo "║ 2. Listener is not running"
echo "║ 3. Network connectivity issues"
echo "║ 4. Invalid credentials in oracle_admin.conf"
echo "║ 5. Service name is incorrect"
echo "║"
echo "║ Action: Fix the connection issue before retrying"
echo "╚════════════════════════════════════════════════════════════════╝"
echo ""
exit 1
fi
log_message "INFO" "Database connection validated successfully: ${db_name}"
return 0
}
################################################################################
# Function: get_database_role
# Description: Gets the database role (PRIMARY/PHYSICAL STANDBY)
# Parameters: $1 - SCAN address
# $2 - Service name
# Returns: Prints database role
################################################################################
get_database_role() {
local scan=$1
local service=$2
local role=$(${ORACLE_HOME}/bin/sqlplus -S /nolog << EOF
connect ${SYS_USER}/${SYS_PASSWORD}@${scan}/${service} as sysdba
set heading off feedback off pagesize 0
select database_role from v\$database;
exit;
EOF
)
echo "$role" | xargs
}
################################################################################
# EMAIL FUNCTIONS
################################################################################
################################################################################
# Function: send_email_report
# Description: Sends email with report content
# Parameters: $1 - Subject
# $2 - Report file path
# $3 - Email recipients (comma-separated)
################################################################################
send_email_report() {
local subject=$1
local report_file=$2
local recipients=$3
log_message "INFO" "Sending email report: ${subject}"
if [[ ! -f "${report_file}" ]]; then
log_message "ERROR" "Report file not found: ${report_file}"
return 1
fi
# Send email using sendmail or mail command
if command -v sendmail &> /dev/null; then
{
echo "Subject: ${subject}"
echo "From: ${EMAIL_FROM}"
echo "To: ${recipients}"
echo "Content-Type: text/html; charset=UTF-8"
echo ""
cat "${report_file}"
} | sendmail -t
log_message "INFO" "Email sent successfully to ${recipients}"
elif command -v mail &> /dev/null; then
cat "${report_file}" | mail -s "${subject}" -a "Content-Type: text/html" "${recipients}"
log_message "INFO" "Email sent successfully to ${recipients}"
else
log_message "ERROR" "No email command available (sendmail or mail)"
return 1
fi
return 0
}
################################################################################
# FILE MANAGEMENT FUNCTIONS
################################################################################
################################################################################
# Function: cleanup_old_files
# Description: Cleans up old files based on retention policy
# Parameters: $1 - Directory path
# $2 - Retention days
################################################################################
cleanup_old_files() {
local directory=$1
local retention_days=$2
log_message "INFO" "Cleaning up files older than ${retention_days} days in ${directory}"
if [[ -d "${directory}" ]]; then
find "${directory}" -type f -mtime +${retention_days} -delete
log_message "INFO" "Cleanup completed"
else
log_message "WARN" "Directory not found: ${directory}"
fi
}
################################################################################
# Function: format_bytes
# Description: Formats bytes to human-readable format
# Parameters: $1 - Bytes value
################################################################################
format_bytes() {
local bytes=$1
if [[ $bytes -lt 1024 ]]; then
echo "${bytes} B"
elif [[ $bytes -lt 1048576 ]]; then
echo "$(echo "scale=2; $bytes / 1024" | bc) KB"
elif [[ $bytes -lt 1073741824 ]]; then
echo "$(echo "scale=2; $bytes / 1048576" | bc) MB"
else
echo "$(echo "scale=2; $bytes / 1073741824" | bc) GB"
fi
}
################################################################################
# DATA GUARD (DGMGRL) CORE FUNCTIONS
################################################################################
################################################################################
# Function: get_dg_broker_configuration
# Description: Gets Data Guard Broker configuration name
# Parameters: $1 - Database name
# Returns: DG_CONFIG_NAME, DG_CONNECT_STRING (global variables)
################################################################################
get_dg_broker_configuration() {
local db_name=$1
log_message "INFO" "Checking for Data Guard Broker configuration..."
local selected_config=$(load_database_list "${db_name}")
if [[ -z "${selected_config}" ]]; then
log_message "ERROR" "Database '${db_name}' not found in configuration"
return 1
fi
IFS='|' read -r db scan service <<< "${selected_config}"
if ! test_db_connection "${scan}" "${service}" 2>/dev/null; then
log_message "ERROR" "Cannot connect to database ${db}"
return 1
fi
DG_CONNECT_STRING="${SYS_USER}/${SYS_PASSWORD}@${scan}/${service}"
local dg_config_output=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show configuration;
exit;
EOF
)
if echo "${dg_config_output}" | grep -qi "ORA-\|configuration does not exist"; then
log_message "WARN" "Not part of Data Guard Broker configuration"
return 1
fi
DG_CONFIG_NAME=$(echo "${dg_config_output}" | grep -i "Configuration -" | sed 's/.*Configuration - \(.*\)/\1/' | xargs)
if [[ -z "${DG_CONFIG_NAME}" ]]; then
log_message "ERROR" "Could not determine Data Guard configuration name"
return 1
fi
log_message "INFO" "Found Data Guard configuration: ${DG_CONFIG_NAME}"
return 0
}
################################################################################
# Function: get_primary_database_dgmgrl
# Description: Gets primary database unique name from DGMGRL
# Returns: PRIMARY_DB_UNIQUE_NAME (global variable)
################################################################################
get_primary_database_dgmgrl() {
log_message "INFO" "Identifying primary database..."
if [[ -z "${DG_CONNECT_STRING}" ]]; then
log_message "ERROR" "Data Guard configuration not initialized"
return 1
fi
local dg_show_config=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show configuration verbose;
exit;
EOF
)
local primary_line=$(echo "${dg_show_config}" | grep -i "Primary database" | head -1)
if [[ -z "${primary_line}" ]]; then
log_message "ERROR" "Could not identify primary database"
return 1
fi
PRIMARY_DB_UNIQUE_NAME=$(echo "${primary_line}" | sed 's/.*Primary database is \(.*\)/\1/' | xargs)
log_message "INFO" "Primary database: ${PRIMARY_DB_UNIQUE_NAME}"
return 0
}
################################################################################
# Function: get_all_standby_databases_dgmgrl
# Description: Gets all standby database unique names from DGMGRL
# Returns: STANDBY_DBS_ARRAY (array of unique names)
################################################################################
get_all_standby_databases_dgmgrl() {
log_message "INFO" "Getting all standby databases..."
if [[ -z "${DG_CONNECT_STRING}" ]]; then
log_message "ERROR" "Data Guard configuration not initialized"
return 1
fi
local dg_show_config=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show configuration verbose;
exit;
EOF
)
STANDBY_DBS_ARRAY=()
while IFS= read -r line; do
if echo "${line}" | grep -qi "Physical standby database"; then
# Extract database name - use awk for first field, remove all whitespace
local standby_db=$(echo "${line}" | awk '{print $1}' | xargs)
standby_db=$(echo "${standby_db}" | tr -d '[:space:]')
if [[ -n "${standby_db}" ]]; then
STANDBY_DBS_ARRAY+=("${standby_db}")
log_message "INFO" "Found standby: '${standby_db}' (length: ${#standby_db})"
fi
fi
done <<< "${dg_show_config}"
log_message "INFO" "Found ${#STANDBY_DBS_ARRAY[@]} standby database(s)"
# Debug: show array contents
for idx in "${!STANDBY_DBS_ARRAY[@]}"; do
log_message "INFO" "STANDBY_DBS_ARRAY[${idx}] = '${STANDBY_DBS_ARRAY[$idx]}'"
done
return 0
}
################################################################################
# Function: get_database_connection_from_dgmgrl
# Description: Gets connection info (SCAN/SERVICE) for a database from DGMGRL
# Parameters: $1 - Database unique name
# Returns: Prints "scan|service" or empty if not found
################################################################################
get_database_connection_from_dgmgrl() {
local db_unique_name=$1
log_message "INFO" "Getting connection info for ${db_unique_name} from DGMGRL..."
local db_info=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show database ${db_unique_name};
exit;
EOF
)
# Extract DGConnectIdentifier from output
# Format: DGConnectIdentifier = 'hostname:port/service_name'
local connect_id=$(echo "${db_info}" | grep -i "DGConnectIdentifier" | head -1 | sed "s/.*DGConnectIdentifier[[:space:]]*=[[:space:]]*'\([^']*\)'.*/\1/" | xargs)
if [[ -z "${connect_id}" ]]; then
log_message "ERROR" "Could not extract connection identifier for ${db_unique_name}"
return 1
fi
# Parse connect identifier
# Format can be: hostname:port/service or scan:port/service or //hostname/service
local scan=""
local service=""
# Remove leading // if present
connect_id=$(echo "${connect_id}" | sed 's|^//||')
# Extract hostname/scan and service
if [[ "${connect_id}" =~ ^([^/:]+)(:[0-9]+)?/(.+)$ ]]; then
scan="${BASH_REMATCH[1]}"
service="${BASH_REMATCH[3]}"
else
log_message "ERROR" "Could not parse connection identifier: ${connect_id}"
return 1
fi
log_message "INFO" "Connection for ${db_unique_name}: ${scan}/${service}"
echo "${scan}|${service}"
return 0
}
################################################################################
# Function: get_primary_and_standbys_dgmgrl
# Description: Gets primary and all standbys with connection info from DGMGRL
# Returns: PRIMARY_DB (db|scan|service), STANDBY_DBS_ARRAY (array of db|scan|service)
################################################################################
get_primary_and_standbys_dgmgrl() {
log_message "INFO" "Identifying primary and standby databases from DGMGRL..."
if [[ -z "${DG_CONNECT_STRING}" ]]; then
log_message "ERROR" "Data Guard configuration not initialized"
return 1
fi
local dg_show_config=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show configuration verbose;
exit;
EOF
)
# Find primary
local primary_line=$(echo "${dg_show_config}" | grep -i "Primary database" | head -1)
local primary_unique_name=$(echo "${primary_line}" | sed 's/.*Primary database is \(.*\)/\1/' | xargs)
if [[ -n "${primary_unique_name}" ]]; then
# Get connection info from DGMGRL
local primary_conn=$(get_database_connection_from_dgmgrl "${primary_unique_name}")
if [[ -n "${primary_conn}" ]]; then
PRIMARY_DB="${primary_unique_name}|${primary_conn}"
log_message "INFO" "Primary database: ${primary_unique_name}"
else
log_message "ERROR" "Could not get connection info for primary ${primary_unique_name}"
return 1
fi
fi
# Find standbys
STANDBY_DBS_ARRAY=()
while IFS= read -r line; do
if echo "${line}" | grep -qi "Physical standby database"; then
local standby_db=$(echo "${line}" | awk '{print $1}' | xargs)
standby_db=$(echo "${standby_db}" | tr -d '[:space:]')
if [[ -n "${standby_db}" ]]; then
# Get connection info from DGMGRL
local standby_conn=$(get_database_connection_from_dgmgrl "${standby_db}")
if [[ -n "${standby_conn}" ]]; then
STANDBY_DBS_ARRAY+=("${standby_db}|${standby_conn}")
log_message "INFO" "Found standby: ${standby_db}"
else
log_message "WARN" "Could not get connection info for standby ${standby_db}"
fi
fi
fi
done <<< "${dg_show_config}"
log_message "INFO" "Found ${#STANDBY_DBS_ARRAY[@]} standby database(s)"
return 0
}
################################################################################
# DATA GUARD CONTROL FUNCTIONS
################################################################################
################################################################################
# Function: stop_apply_on_standby
# Description: Stops apply process on standby using DGMGRL
# Parameters: $1 - Standby database unique name
# Returns: 0 if successful, 1 if failed
################################################################################
stop_apply_on_standby() {
local standby_unique_name=$1
log_message "INFO" "Stopping apply on ${standby_unique_name}..."
local result=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
edit database ${standby_unique_name} set state='APPLY-OFF';
exit;
EOF
)
if echo "${result}" | grep -qi "succeed"; then
log_message "INFO" "Apply stopped on ${standby_unique_name}"
return 0
else
log_message "ERROR" "Failed to stop apply on ${standby_unique_name}"
echo "${result}"
return 1
fi
}
################################################################################
# Function: start_apply_on_standby
# Description: Starts apply process on standby using DGMGRL
# Parameters: $1 - Standby database unique name
# Returns: 0 if successful, 1 if failed
################################################################################
start_apply_on_standby() {
local standby_unique_name=$1
log_message "INFO" "Starting apply on ${standby_unique_name}..."
local result=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
edit database ${standby_unique_name} set state='APPLY-ON';
exit;
EOF
)
if echo "${result}" | grep -qi "succeed"; then
log_message "INFO" "Apply started on ${standby_unique_name}"
return 0
else
log_message "ERROR" "Failed to start apply on ${standby_unique_name}"
echo "${result}"
return 1
fi
}
################################################################################
# DATA GUARD STATUS FUNCTIONS
################################################################################
################################################################################
# Function: check_dg_status_dgmgrl
# Description: Gets overall Data Guard configuration status
# Returns: DG_STATUS_OUTPUT, DG_OVERALL_STATUS (global variables)
################################################################################
check_dg_status_dgmgrl() {
log_message "INFO" "Checking Data Guard configuration status..."
if [[ -z "${DG_CONNECT_STRING}" ]]; then
log_message "ERROR" "Data Guard configuration not initialized"
return 1
fi
DG_STATUS_OUTPUT=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show configuration;
exit;
EOF
)
if echo "${DG_STATUS_OUTPUT}" | grep -qi "SUCCESS"; then
DG_OVERALL_STATUS="SUCCESS"
elif echo "${DG_STATUS_OUTPUT}" | grep -qi "WARNING"; then
DG_OVERALL_STATUS="WARNING"
elif echo "${DG_STATUS_OUTPUT}" | grep -qi "ERROR"; then
DG_OVERALL_STATUS="ERROR"
else
DG_OVERALL_STATUS="UNKNOWN"
fi
log_message "INFO" "Data Guard status: ${DG_OVERALL_STATUS}"
return 0
}
################################################################################
# Function: check_database_status_dgmgrl
# Description: Gets detailed status for a specific database
# Parameters: $1 - Database unique name
# Returns: DB_STATUS_OUTPUT (global variable)
################################################################################
check_database_status_dgmgrl() {
local db_unique_name=$1
log_message "INFO" "Checking status for database: ${db_unique_name}"
DB_STATUS_OUTPUT=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show database ${db_unique_name};
exit;
EOF
)
return 0
}
################################################################################
# Function: check_standby_lag_dgmgrl
# Description: Gets transport and apply lag for a standby database
# Parameters: $1 - Standby database unique name
# Returns: LAG_TRANSPORT, LAG_APPLY (global variables)
################################################################################
check_standby_lag_dgmgrl() {
local standby_unique_name=$1
log_message "INFO" "Checking lag for standby: ${standby_unique_name}"
local lag_output=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show database ${standby_unique_name};
exit;
EOF
)
# Extract transport lag
LAG_TRANSPORT=$(echo "${lag_output}" | grep -i "Transport Lag:" | sed 's/.*Transport Lag:[[:space:]]*\(.*\)/\1/' | xargs)
if [[ -z "${LAG_TRANSPORT}" ]]; then
LAG_TRANSPORT="Unknown"
fi
# Extract apply lag
LAG_APPLY=$(echo "${lag_output}" | grep -i "Apply Lag:" | sed 's/.*Apply Lag:[[:space:]]*\(.*\)/\1/' | xargs)
if [[ -z "${LAG_APPLY}" ]]; then
LAG_APPLY="Unknown"
fi
log_message "INFO" "Standby ${standby_unique_name} - Transport Lag: ${LAG_TRANSPORT}, Apply Lag: ${LAG_APPLY}"
return 0
}
################################################################################
# Function: get_database_property_dgmgrl
# Description: Gets a specific property value for a database
# Parameters: $1 - Database unique name
# $2 - Property name
# Returns: Prints property value
################################################################################
get_database_property_dgmgrl() {
local db_unique_name=$1
local property_name=$2
local db_info=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show database ${db_unique_name};
exit;
EOF
)
local property_value=$(echo "${db_info}" | grep -i "${property_name}" | sed "s/.*${property_name}[[:space:]]*:[[:space:]]*\(.*\)/\1/" | xargs)
echo "${property_value}"
return 0
}
################################################################################
# DATA GUARD VALIDATION FUNCTIONS
################################################################################
################################################################################
# Function: validate_dgmgrl_connection
# Description: Validates DGMGRL connection is working
# Returns: 0 if valid, 1 if not
################################################################################
validate_dgmgrl_connection() {
log_message "INFO" "Validating DGMGRL connection..."
if [[ -z "${DG_CONNECT_STRING}" ]]; then
log_message "ERROR" "DG_CONNECT_STRING not set"
return 1
fi
local test_output=$(${ORACLE_HOME}/bin/dgmgrl -silent << EOF
connect ${DG_CONNECT_STRING}
show configuration;
exit;
EOF
)
if echo "${test_output}" | grep -qi "ORA-"; then
log_message "ERROR" "DGMGRL connection failed"
return 1
fi
log_message "INFO" "DGMGRL connection validated"
return 0
}
################################################################################
# End of functions_common.sh
################################################################################
