Friday, November 14, 2025
#!/bin/bash
################################################################################
# Oracle 19c RAC Database Administration - Restore Point Management Functions
# Description: Guaranteed restore point management for Data Guard environments
# Created: 2025-11-02
# Updated: 2025-11-14 - Integrated with database loading fix
################################################################################
################################################################################
# Function: manage_restore_points
# Description: Interactive restore point management menu
# Parameters: $1 - Database name
# Returns: 0 on success, 1 on failure
################################################################################
manage_restore_points() {
local db_name=$1
log_message "INFO" "Starting restore point management for $db_name"
while true; do
clear
echo "=========================================="
echo "Restore Point Management"
echo "Database: $db_name"
echo "=========================================="
echo ""
echo "1. List Restore Points"
echo "2. Create Restore Point"
echo "3. Create Guaranteed Restore Point"
echo "4. Drop Restore Point"
echo "5. Flashback Database to Restore Point"
echo "6. Propagate Restore Point to Standby"
echo "7. View Restore Point Details"
echo "0. Return to Main Menu"
echo ""
echo "=========================================="
echo ""
read -p "Enter your choice [0-7]: " choice
case $choice in
1)
list_restore_points "$db_name"
;;
2)
create_restore_point "$db_name" "NO"
;;
3)
create_restore_point "$db_name" "YES"
;;
4)
drop_restore_point "$db_name"
;;
5)
flashback_to_restore_point "$db_name"
;;
6)
propagate_restore_point_to_standby "$db_name"
;;
7)
view_restore_point_details "$db_name"
;;
0)
log_message "INFO" "Exiting restore point management"
return 0
;;
*)
echo ""
echo "ERROR: Invalid choice"
sleep 2
;;
esac
if [[ $choice -ne 0 ]]; then
echo ""
read -p "Press Enter to continue..."
fi
done
}
################################################################################
# Function: list_restore_points
# Description: List all restore points
################################################################################
list_restore_points() {
local db_name=$1
echo ""
echo "=========================================="
echo "Restore Points for $db_name"
echo "=========================================="
echo ""
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
COLUMN name FORMAT A30
COLUMN scn FORMAT 999999999999999
COLUMN time FORMAT A20
COLUMN guarantee_flashback_database FORMAT A10 HEADING "GUARANTEED"
COLUMN storage_size FORMAT 999,999,999,999 HEADING "STORAGE_MB"
SELECT
name,
scn,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') as time,
guarantee_flashback_database,
ROUND(storage_size/1024/1024, 2) as storage_size
FROM v\$restore_point
ORDER BY time DESC;
EXIT;
EOF
log_message "INFO" "Listed restore points for $db_name"
}
################################################################################
# Function: create_restore_point
# Description: Create a restore point
# Parameters: $1 - Database name
# $2 - Guarantee flashback (YES/NO)
################################################################################
create_restore_point() {
local db_name=$1
local guarantee=$2
echo ""
echo "=========================================="
echo "Create Restore Point"
echo "=========================================="
echo ""
# Check if flashback is enabled
local flashback_on=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT flashback_on FROM v\$database;
EXIT;
EOF
)
flashback_on=$(echo "$flashback_on" | xargs)
if [[ "$flashback_on" != "YES" ]] && [[ "$guarantee" == "YES" ]]; then
echo "ERROR: Flashback Database is not enabled"
echo "Cannot create guaranteed restore point without flashback database"
log_message "ERROR" "Flashback database not enabled on $db_name"
return 1
fi
# Get restore point name
local default_name="RP_$(date '+%Y%m%d_%H%M%S')"
echo "Default name: $default_name"
read -p "Enter restore point name [default: $default_name]: " rp_name
if [[ -z "$rp_name" ]]; then
rp_name=$default_name
fi
# Confirm creation
echo ""
echo "Restore Point Details:"
echo " Name: $rp_name"
echo " Guaranteed: $guarantee"
echo " Database: $db_name"
echo ""
read -p "Create this restore point? (yes/no): " confirm
if [[ ! "$confirm" =~ ^[Yy][Ee][Ss]$ ]]; then
echo "Restore point creation cancelled"
return 0
fi
echo ""
echo "Creating restore point..."
# Create restore point
if [[ "$guarantee" == "YES" ]]; then
sqlplus -s / as sysdba << EOF
WHENEVER SQLERROR EXIT 1
CREATE RESTORE POINT ${rp_name} GUARANTEE FLASHBACK DATABASE;
EXIT;
EOF
else
sqlplus -s / as sysdba << EOF
WHENEVER SQLERROR EXIT 1
CREATE RESTORE POINT ${rp_name};
EXIT;
EOF
fi
if [[ $? -eq 0 ]]; then
echo "✓ Restore point created successfully: $rp_name"
log_message "SUCCESS" "Restore point created: $rp_name (guaranteed: $guarantee)"
# Show details
echo ""
echo "Restore Point Details:"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SELECT
name,
scn,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') as time,
guarantee_flashback_database
FROM v\$restore_point
WHERE name = '${rp_name}';
EXIT;
EOF
else
echo "✗ Failed to create restore point"
log_message "ERROR" "Failed to create restore point: $rp_name"
return 1
fi
return 0
}
################################################################################
# Function: drop_restore_point
# Description: Drop a restore point
################################################################################
drop_restore_point() {
local db_name=$1
echo ""
echo "=========================================="
echo "Drop Restore Point"
echo "=========================================="
echo ""
# List existing restore points
echo "Existing Restore Points:"
echo "------------------------"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
COLUMN name FORMAT A30
SELECT
name,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') as time,
guarantee_flashback_database as guaranteed
FROM v\$restore_point
ORDER BY time DESC;
EXIT;
EOF
echo ""
read -p "Enter restore point name to drop: " rp_name
if [[ -z "$rp_name" ]]; then
echo "No restore point name provided"
return 0
fi
# Verify restore point exists
local exists=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM v\$restore_point WHERE name = '${rp_name}';
EXIT;
EOF
)
exists=$(echo "$exists" | xargs)
if [[ $exists -eq 0 ]]; then
echo "ERROR: Restore point '$rp_name' not found"
return 1
fi
# Confirm deletion
echo ""
echo "WARNING: This will permanently remove the restore point: $rp_name"
read -p "Are you sure? (yes/no): " confirm
if [[ ! "$confirm" =~ ^[Yy][Ee][Ss]$ ]]; then
echo "Operation cancelled"
return 0
fi
echo ""
echo "Dropping restore point..."
sqlplus -s / as sysdba << EOF
WHENEVER SQLERROR EXIT 1
DROP RESTORE POINT ${rp_name};
EXIT;
EOF
if [[ $? -eq 0 ]]; then
echo "✓ Restore point dropped successfully: $rp_name"
log_message "SUCCESS" "Restore point dropped: $rp_name"
else
echo "✗ Failed to drop restore point"
log_message "ERROR" "Failed to drop restore point: $rp_name"
return 1
fi
return 0
}
################################################################################
# Function: flashback_to_restore_point
# Description: Flashback database to a restore point
################################################################################
flashback_to_restore_point() {
local db_name=$1
echo ""
echo "=========================================="
echo "Flashback Database to Restore Point"
echo "=========================================="
echo ""
echo "⚠ WARNING: This is a critical operation!"
echo ""
# Check database role
local db_role=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
)
db_role=$(echo "$db_role" | xargs)
echo "Current database role: $db_role"
echo ""
if [[ "$db_role" == "PRIMARY" ]]; then
echo "⚠ WARNING: Flashback on PRIMARY database affects entire Data Guard configuration"
echo ""
fi
# List restore points
echo "Available Restore Points:"
echo "-------------------------"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SELECT
name,
scn,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') as time,
guarantee_flashback_database
FROM v\$restore_point
ORDER BY time DESC;
EXIT;
EOF
echo ""
read -p "Enter restore point name: " rp_name
if [[ -z "$rp_name" ]]; then
echo "No restore point name provided"
return 0
fi
# Verify restore point exists
local exists=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM v\$restore_point WHERE name = '${rp_name}';
EXIT;
EOF
)
exists=$(echo "$exists" | xargs)
if [[ $exists -eq 0 ]]; then
echo "ERROR: Restore point '$rp_name' not found"
return 1
fi
# Final confirmation
echo ""
echo "=========================================="
echo "FLASHBACK CONFIRMATION"
echo "=========================================="
echo "Database: $db_name"
echo "Restore Point: $rp_name"
echo ""
echo "This operation will:"
echo " 1. Shutdown the database"
echo " 2. Mount the database"
echo " 3. Flashback to restore point"
echo " 4. Open database with RESETLOGS"
echo ""
echo "⚠ All data changes after the restore point will be LOST!"
echo ""
read -p "Type 'FLASHBACK' to proceed: " confirm
if [[ "$confirm" != "FLASHBACK" ]]; then
echo "Operation cancelled"
return 0
fi
echo ""
echo "Performing flashback operation..."
log_message "INFO" "Starting flashback database to $rp_name"
# Execute flashback
sqlplus -s / as sysdba << EOF
WHENEVER SQLERROR EXIT 1
PROMPT Shutting down database...
SHUTDOWN IMMEDIATE;
PROMPT Starting database in MOUNT mode...
STARTUP MOUNT;
PROMPT Executing flashback database...
FLASHBACK DATABASE TO RESTORE POINT ${rp_name};
PROMPT Opening database with RESETLOGS...
ALTER DATABASE OPEN RESETLOGS;
EXIT;
EOF
if [[ $? -eq 0 ]]; then
echo ""
echo "✓ Flashback completed successfully"
echo ""
echo "Database has been restored to restore point: $rp_name"
echo ""
if [[ "$db_role" == "PRIMARY" ]]; then
echo "IMPORTANT: Reinstate standby databases in Data Guard configuration"
echo ""
echo "For each standby database, execute:"
echo " 1. Connect to standby"
echo " 2. SHUTDOWN IMMEDIATE;"
echo " 3. STARTUP MOUNT;"
echo " 4. FLASHBACK DATABASE TO RESTORE POINT $rp_name;"
echo " 5. ALTER DATABASE OPEN;"
echo " 6. Restart MRP: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;"
echo ""
fi
log_message "SUCCESS" "Flashback database completed: $rp_name"
else
echo "✗ Flashback operation failed"
log_message "ERROR" "Flashback database failed: $rp_name"
return 1
fi
return 0
}
################################################################################
# Function: propagate_restore_point_to_standby
# Description: Ensure restore point is propagated to standby databases
################################################################################
propagate_restore_point_to_standby() {
local db_name=$1
echo ""
echo "=========================================="
echo "Propagate Restore Point to Standby"
echo "=========================================="
echo ""
# Check database role
local db_role=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
)
db_role=$(echo "$db_role" | xargs)
if [[ "$db_role" != "PRIMARY" ]]; then
echo "ERROR: This operation must be run on PRIMARY database"
echo "Current role: $db_role"
return 1
fi
# List restore points
echo "Available Restore Points:"
echo "-------------------------"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SELECT
name,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') as time
FROM v\$restore_point
ORDER BY time DESC;
EXIT;
EOF
echo ""
read -p "Enter restore point name to propagate: " rp_name
if [[ -z "$rp_name" ]]; then
echo "No restore point name provided"
return 0
fi
echo ""
echo "Checking restore point status..."
# Get restore point SCN
local rp_scn=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT scn FROM v\$restore_point WHERE name = '${rp_name}';
EXIT;
EOF
)
rp_scn=$(echo "$rp_scn" | xargs)
if [[ -z "$rp_scn" ]]; then
echo "ERROR: Restore point '$rp_name' not found"
return 1
fi
echo "Restore point SCN: $rp_scn"
echo ""
echo "To verify on standby database:"
echo " 1. Connect to standby database"
echo " 2. SELECT * FROM v\$restore_point WHERE name = '${rp_name}';"
echo ""
echo "If not present, ensure:"
echo " - Redo transport is working"
echo " - MRP is applying redo"
echo " - There are no archive log gaps"
echo ""
# Check Data Guard status
echo "Data Guard Transport Status:"
echo "----------------------------"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SELECT
dest_name,
status,
error
FROM v\$archive_dest_status
WHERE dest_name LIKE 'LOG_ARCHIVE_DEST_%'
AND status != 'INACTIVE';
EXIT;
EOF
log_message "INFO" "Checked restore point propagation for $rp_name"
}
################################################################################
# Function: view_restore_point_details
# Description: View detailed information about restore points
################################################################################
view_restore_point_details() {
local db_name=$1
echo ""
echo "=========================================="
echo "Restore Point Details"
echo "=========================================="
echo ""
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
COLUMN name FORMAT A30
COLUMN scn FORMAT 999999999999999
COLUMN time FORMAT A20
COLUMN database_incarnation# FORMAT 9999 HEADING "INCARN"
COLUMN guarantee_flashback_database FORMAT A10 HEADING "GUARANTEED"
COLUMN storage_size FORMAT 999,999,999,999 HEADING "STORAGE_MB"
SELECT
name,
scn,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') as time,
database_incarnation#,
guarantee_flashback_database,
ROUND(storage_size/1024/1024, 2) as storage_size
FROM v\$restore_point
ORDER BY time DESC;
PROMPT
PROMPT Flashback Database Status:
PROMPT ==========================
SELECT
flashback_on,
log_mode,
TO_CHAR(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') as oldest_flashback_time,
TO_CHAR(oldest_flashback_scn) as oldest_flashback_scn
FROM v\$database;
PROMPT
PROMPT Fast Recovery Area Usage:
PROMPT =========================
SELECT
space_limit/1024/1024/1024 as space_limit_gb,
space_used/1024/1024/1024 as space_used_gb,
space_reclaimable/1024/1024/1024 as space_reclaimable_gb,
number_of_files,
ROUND((space_used/space_limit)*100, 2) as percent_used
FROM v\$recovery_file_dest;
EXIT;
EOF
log_message "INFO" "Viewed restore point details for $db_name"
}
################################################################################
# Function: cleanup_old_restore_points
# Description: Clean up old restore points
################################################################################
cleanup_old_restore_points() {
local db_name=$1
local days_old=${2:-7}
echo ""
echo "=========================================="
echo "Cleanup Old Restore Points"
echo "=========================================="
echo ""
echo "Removing restore points older than $days_old days..."
echo ""
# List old restore points
local old_rps=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT name
FROM v\$restore_point
WHERE time < SYSDATE - $days_old
AND guarantee_flashback_database = 'YES';
EXIT;
EOF
)
if [[ -z "$old_rps" ]]; then
echo "No old restore points found"
return 0
fi
echo "Old restore points to be removed:"
echo "$old_rps"
echo ""
read -p "Proceed with cleanup? (yes/no): " confirm
if [[ ! "$confirm" =~ ^[Yy][Ee][Ss]$ ]]; then
echo "Cleanup cancelled"
return 0
fi
# Drop each restore point
while IFS= read -r rp_name; do
rp_name=$(echo "$rp_name" | xargs)
if [[ -n "$rp_name" ]]; then
echo "Dropping: $rp_name"
sqlplus -s / as sysdba << EOF
DROP RESTORE POINT ${rp_name};
EXIT;
EOF
if [[ $? -eq 0 ]]; then
echo " ✓ Dropped: $rp_name"
log_message "INFO" "Dropped old restore point: $rp_name"
else
echo " ✗ Failed: $rp_name"
log_message "ERROR" "Failed to drop restore point: $rp_name"
fi
fi
done <<< "$old_rps"
echo ""
echo "Cleanup completed"
}
################################################################################
# Export functions
################################################################################
export -f manage_restore_points
export -f list_restore_points
export -f create_restore_point
export -f drop_restore_point
export -f flashback_to_restore_point
export -f cleanup_old_restore_points
log_message "INFO" "Restore point management functions loaded"
