Sunday, November 9, 2025
#!/bin/bash
################################################################################
# Oracle 19c Restore Point Management Functions
# Description: Functions for managing restore points in RAC/DG environments
# Version: 2.0 (Integrated)
# Created: 2025-11-02
# Updated: 2025-11-10 - Removed duplicate functions (now in functions_common.sh)
################################################################################
# NOTE: This file assumes functions_common.sh has already been sourced
# All DG control functions (stop_apply_on_standby, start_apply_on_standby,
# get_primary_and_standbys_dgmgrl, etc.) are now in functions_common.sh
################################################################################
# Function: create_restore_point_primary
# Description: Creates restore point on primary database only
# Parameters: $1 - Database name
# $2 - SCAN address
# $3 - Service name
# $4 - Restore point name
################################################################################
create_restore_point_primary() {
local db_name=$1
local scan=$2
local service=$3
local rp_name=$4
log_message "INFO" "Creating restore point '${rp_name}' on primary database ${db_name}"
# Validate connection
validate_database_connection "${db_name}" "${scan}" "${service}"
local connect_string="${SYS_USER}/${SYS_PASSWORD}@${scan}/${service} as sysdba"
# Create restore point
local result=$(${ORACLE_HOME}/bin/sqlplus -S /nolog << EOF
connect ${connect_string}
set feedback off heading off pagesize 0
whenever sqlerror exit 1
CREATE RESTORE POINT ${rp_name} GUARANTEE FLASHBACK DATABASE;
select 'Restore point created successfully' from dual;
exit;
EOF
)
if echo "${result}" | grep -q "successfully"; then
log_message "INFO" "Restore point '${rp_name}' created on ${db_name}"
echo "✓ Restore point '${rp_name}' created successfully on ${db_name}"
return 0
else
log_message "ERROR" "Failed to create restore point on ${db_name}"
echo "✗ Failed to create restore point: ${result}"
return 1
fi
}
################################################################################
# Function: create_restore_point_all_dg_members
# Description: Creates restore point on all DG members (standby first)
# Parameters: $1 - Database name (will auto-detect primary/standbys)
# $2 - Restore point name
################################################################################
create_restore_point_all_dg_members() {
local db_name=$1
local rp_name=$2
log_message "INFO" "Creating restore point '${rp_name}' on all DG members"
# Get DG configuration
if ! get_dg_broker_configuration "${db_name}"; then
log_message "ERROR" "Failed to get DG Broker configuration"
echo "ERROR: Not part of Data Guard configuration"
return 1
fi
# Get primary and standbys with connection info
if ! get_primary_and_standbys_dgmgrl; then
log_message "ERROR" "Failed to get DG topology"
return 1
fi
local all_success=0
# Parse primary info
IFS='|' read -r primary_db primary_conn <<< "${PRIMARY_DB}"
IFS='|' read -r primary_scan primary_service <<< "${primary_conn}"
echo ""
echo "==================================================================="
echo " Creating Restore Point: ${rp_name}"
echo " on all Data Guard members"
echo "==================================================================="
echo ""
# Step 1: Stop apply on all standbys
if [[ ${#STANDBY_DBS_ARRAY[@]} -gt 0 ]]; then
echo "Step 1: Stopping MRP on standby databases..."
for standby_entry in "${STANDBY_DBS_ARRAY[@]}"; do
IFS='|' read -r standby_db standby_conn <<< "${standby_entry}"
IFS='|' read -r standby_scan standby_service <<< "${standby_conn}"
echo " Stopping MRP on ${standby_db}..."
if stop_apply_on_standby "${standby_db}"; then
echo " ✓ MRP stopped on ${standby_db}"
else
echo " ✗ Failed to stop MRP on ${standby_db}"
all_success=1
fi
done
echo ""
fi
# Step 2: Create restore point on all standbys first
if [[ ${#STANDBY_DBS_ARRAY[@]} -gt 0 ]]; then
echo "Step 2: Creating restore point on standby databases..."
for standby_entry in "${STANDBY_DBS_ARRAY[@]}"; do
IFS='|' read -r standby_db standby_conn <<< "${standby_entry}"
IFS='|' read -r standby_scan standby_service <<< "${standby_conn}"
echo " Creating restore point on ${standby_db}..."
if create_restore_point_primary "${standby_db}" "${standby_scan}" "${standby_service}" "${rp_name}"; then
echo " ✓ Restore point created on ${standby_db}"
else
echo " ✗ Failed to create restore point on ${standby_db}"
all_success=1
fi
done
echo ""
fi
# Step 3: Create restore point on primary
echo "Step 3: Creating restore point on primary database..."
echo " Creating restore point on ${primary_db}..."
if create_restore_point_primary "${primary_db}" "${primary_scan}" "${primary_service}" "${rp_name}"; then
echo " ✓ Restore point created on ${primary_db}"
else
echo " ✗ Failed to create restore point on ${primary_db}"
all_success=1
fi
echo ""
# Step 4: Restart apply on all standbys
if [[ ${#STANDBY_DBS_ARRAY[@]} -gt 0 ]]; then
echo "Step 4: Restarting MRP on standby databases..."
for standby_entry in "${STANDBY_DBS_ARRAY[@]}"; do
IFS='|' read -r standby_db standby_conn <<< "${standby_entry}"
echo " Starting MRP on ${standby_db}..."
if start_apply_on_standby "${standby_db}"; then
echo " ✓ MRP started on ${standby_db}"
else
echo " ✗ Failed to start MRP on ${standby_db}"
all_success=1
fi
done
echo ""
fi
echo "==================================================================="
if [[ ${all_success} -eq 0 ]]; then
echo " Restore Point Creation: SUCCESS"
log_message "INFO" "Restore point '${rp_name}' created on all DG members"
else
echo " Restore Point Creation: COMPLETED WITH ERRORS"
log_message "WARN" "Restore point creation had some failures"
fi
echo "==================================================================="
return ${all_success}
}
################################################################################
# Function: list_restore_points
# Description: Lists all restore points on a database
# Parameters: $1 - Database name
# $2 - SCAN address
# $3 - Service name
################################################################################
list_restore_points() {
local db_name=$1
local scan=$2
local service=$3
log_message "INFO" "Listing restore points for ${db_name}"
# Validate connection
validate_database_connection "${db_name}" "${scan}" "${service}"
local connect_string="${SYS_USER}/${SYS_PASSWORD}@${scan}/${service} as sysdba"
echo ""
echo "==================================================================="
echo " Restore Points on ${db_name}"
echo "==================================================================="
${ORACLE_HOME}/bin/sqlplus -S /nolog << EOF
connect ${connect_string}
set linesize 200 pagesize 100
column name format a30
column scn format 999999999999999
column time format a30
column guarantee_flashback_database format a10 heading "GUARANTEED"
column storage_size format 999,999,999,999 heading "SIZE (BYTES)"
select
name,
scn,
to_char(time, 'YYYY-MM-DD HH24:MI:SS') as time,
guarantee_flashback_database,
storage_size
from v\$restore_point
order by time desc;
exit;
EOF
echo "==================================================================="
return 0
}
################################################################################
# Function: drop_restore_point_primary
# Description: Drops restore point from primary database
# Parameters: $1 - Database name
# $2 - SCAN address
# $3 - Service name
# $4 - Restore point name
################################################################################
drop_restore_point_primary() {
local db_name=$1
local scan=$2
local service=$3
local rp_name=$4
log_message "INFO" "Dropping restore point '${rp_name}' from ${db_name}"
# Validate connection
validate_database_connection "${db_name}" "${scan}" "${service}"
local connect_string="${SYS_USER}/${SYS_PASSWORD}@${scan}/${service} as sysdba"
# Drop restore point
local result=$(${ORACLE_HOME}/bin/sqlplus -S /nolog << EOF
connect ${connect_string}
set feedback off heading off pagesize 0
whenever sqlerror exit 1
DROP RESTORE POINT ${rp_name};
select 'Restore point dropped successfully' from dual;
exit;
EOF
)
if echo "${result}" | grep -q "successfully"; then
log_message "INFO" "Restore point '${rp_name}' dropped from ${db_name}"
echo "✓ Restore point '${rp_name}' dropped successfully from ${db_name}"
return 0
else
log_message "ERROR" "Failed to drop restore point from ${db_name}"
echo "✗ Failed to drop restore point: ${result}"
return 1
fi
}
################################################################################
# Function: drop_restore_point_all_dg_members
# Description: Drops restore point from all DG members
# Parameters: $1 - Database name (will auto-detect primary/standbys)
# $2 - Restore point name
################################################################################
drop_restore_point_all_dg_members() {
local db_name=$1
local rp_name=$2
log_message "INFO" "Dropping restore point '${rp_name}' from all DG members"
# Get DG configuration
if ! get_dg_broker_configuration "${db_name}"; then
log_message "ERROR" "Failed to get DG Broker configuration"
echo "ERROR: Not part of Data Guard configuration"
return 1
fi
# Get primary and standbys with connection info
if ! get_primary_and_standbys_dgmgrl; then
log_message "ERROR" "Failed to get DG topology"
return 1
fi
local all_success=0
# Parse primary info
IFS='|' read -r primary_db primary_conn <<< "${PRIMARY_DB}"
IFS='|' read -r primary_scan primary_service <<< "${primary_conn}"
echo ""
echo "==================================================================="
echo " Dropping Restore Point: ${rp_name}"
echo " from all Data Guard members"
echo "==================================================================="
echo ""
# Drop from primary first
echo "Dropping restore point from primary database..."
echo " Dropping from ${primary_db}..."
if drop_restore_point_primary "${primary_db}" "${primary_scan}" "${primary_service}" "${rp_name}"; then
echo " ✓ Restore point dropped from ${primary_db}"
else
echo " ✗ Failed to drop restore point from ${primary_db}"
all_success=1
fi
echo ""
# Drop from all standbys
if [[ ${#STANDBY_DBS_ARRAY[@]} -gt 0 ]]; then
echo "Dropping restore point from standby databases..."
for standby_entry in "${STANDBY_DBS_ARRAY[@]}"; do
IFS='|' read -r standby_db standby_conn <<< "${standby_entry}"
IFS='|' read -r standby_scan standby_service <<< "${standby_conn}"
echo " Dropping from ${standby_db}..."
if drop_restore_point_primary "${standby_db}" "${standby_scan}" "${standby_service}" "${rp_name}"; then
echo " ✓ Restore point dropped from ${standby_db}"
else
echo " ✗ Failed to drop restore point from ${standby_db}"
all_success=1
fi
done
echo ""
fi
echo "==================================================================="
if [[ ${all_success} -eq 0 ]]; then
echo " Restore Point Deletion: SUCCESS"
log_message "INFO" "Restore point '${rp_name}' dropped from all DG members"
else
echo " Restore Point Deletion: COMPLETED WITH ERRORS"
log_message "WARN" "Restore point deletion had some failures"
fi
echo "==================================================================="
return ${all_success}
}
################################################################################
# End of functions_restore_point.sh
################################################################################
