Saturday, April 12, 2025
#!/bin/bash
# Usage: ./list_flashback_points.sh
# Input file format (single line):
# hostname db_name
# Check input file
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
CONN_HOST=$(awk '{print $1}' "$INPUT_FILE")
CONN_DB=$(awk '{print $2}' "$INPUT_FILE")
REPORT_FILE="flashback_report_$(date +%Y%m%d_%H%M).txt"
# Validate input
if [[ -z "$CONN_HOST" || -z "$CONN_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "hostname db_name"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${CONN_HOST}/${CONN_DB}: " SYS_PASSWORD
echo
# Function to check database role
get_db_role() {
local host="$1"
local db="$2"
sqlplus -S "sys/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
}
# Function to get standby databases
get_standbys() {
local host="$1"
local db="$2"
sqlplus -S "sys/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT UPPER(destination) FROM v\$archive_dest
WHERE target='STANDBY' AND status='VALID' AND destination IS NOT NULL;
EXIT;
EOF
}
# Function to get primary database
get_primary() {
local host="$1"
local db="$2"
sqlplus -S "sys/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT UPPER(destination) FROM v\$archive_dest
WHERE target='PRIMARY' AND status='VALID' AND destination IS NOT NULL;
EXIT;
EOF
}
# Function to list restore points
list_restore_points() {
local host="$1"
local db="$2"
echo -e "\n=== Restore Points for ${db} (Host: ${host}) ==="
sqlplus -S "sys/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF
SET LINESIZE 100
SET PAGESIZE 100
COLUMN name FORMAT A30
COLUMN time FORMAT A25
COLUMN scn FORMAT 99999999999999
SELECT
name,
TO_CHAR(time, 'YYYY-MM-DD HH24:MI:SS') AS time,
scn,
guarantee_flashback_database AS guaranteed
FROM v\$restore_point
ORDER BY scn;
EXIT;
EOF
}
# Main execution
echo -e "\nInitializing database checks..."
ROLE=$(get_db_role "$CONN_HOST" "$CONN_DB" | tr -d '\n')
if [[ -z "$ROLE" ]]; then
echo "Error connecting to ${CONN_HOST}/${CONN_DB}"
exit 1
fi
declare -a DBS_TO_CHECK=("${CONN_HOST}:${CONN_DB}")
if [[ "$ROLE" == "PRIMARY" ]]; then
echo "Primary database detected. Finding standbys..."
STANDBYS=$(get_standbys "$CONN_HOST" "$CONN_DB")
for sb in $STANDBYS; do
DBS_TO_CHECK+=("${CONN_HOST}:${sb}")
done
elif [[ "$ROLE" == "PHYSICAL STANDBY" ]]; then
echo "Standby database detected. Finding primary..."
PRIMARY=$(get_primary "$CONN_HOST" "$CONN_DB")
if [[ -n "$PRIMARY" ]]; then
DBS_TO_CHECK+=("${CONN_HOST}:${PRIMARY}")
fi
fi
# Generate report
echo -e "\nGenerating flashback report..."
{
echo "Flashback Restore Point Report"
echo "Generated at: $(date)"
echo "======================================"
for conn in "${DBS_TO_CHECK[@]}"; do
IFS=':' read -r host db <<< "$conn"
list_restore_points "$host" "$db"
done
} | tee "$REPORT_FILE"
echo -e "\nReport generated: ${REPORT_FILE}"
###
#!/bin/bash
# Usage: ./drop_flashback_points.sh
# Input file format (single line):
# primary_host primary_db
# Check input file
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
PRIMARY_HOST=$(awk '{print $1}' "$INPUT_FILE")
PRIMARY_DB=$(awk '{print $2}' "$INPUT_FILE")
DG_USER="sys"
LOG_FILE="drop_restore_points_$(date +%Y%m%d_%H%M).log"
# Validate input
if [[ -z "$PRIMARY_HOST" || -z "$PRIMARY_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "primary_host primary_db"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${PRIMARY_HOST}/${PRIMARY_DB}: " SYS_PASSWORD
echo
# Function to log messages
log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
# Verify primary database role
verify_primary() {
log "Verifying primary database role..."
role=$(sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
)
if [[ "$role" != "PRIMARY" ]]; then
log "Error: ${PRIMARY_HOST}/${PRIMARY_DB} is not a primary database (Role: ${role})"
exit 1
fi
log "Primary database confirmed"
}
# Get standby databases from broker configuration
get_standbys() {
log "Retrieving standby databases from broker configuration..."
standby_list=$(echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nshow configuration" | dgmgrl / | \
awk '/Physical standby database/ {print $1}')
if [ -z "$standby_list" ]; then
log "No standby databases found in broker configuration"
exit 0
fi
log "Found standby databases: ${standby_list}"
echo "$standby_list"
}
# Stop MRP on standby
stop_mrp() {
local standby_db=$1
log "Stopping MRP on ${standby_db}..."
echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nedit database ${standby_db} set state='APPLY-OFF';" | dgmgrl / >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log "Failed to stop MRP on ${standby_db}"
exit 1
fi
log "MRP stopped successfully on ${standby_db}"
}
# Start MRP on standby
start_mrp() {
local standby_db=$1
log "Starting MRP on ${standby_db}..."
echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nedit database ${standby_db} set state='APPLY-ON';" | dgmgrl / >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log "Failed to start MRP on ${standby_db}"
exit 1
fi
log "MRP started successfully on ${standby_db}"
}
# Drop restore points
drop_restore_points() {
local host=$1
local db=$2
log "Dropping restore points on ${db}..."
sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF >> "$LOG_FILE" 2>&1
SET SERVEROUTPUT ON
DECLARE
CURSOR c_rp IS SELECT name FROM v\$restore_point;
BEGIN
FOR rp IN c_rp LOOP
EXECUTE IMMEDIATE 'DROP RESTORE POINT ' || rp.name;
DBMS_OUTPUT.PUT_LINE('Dropped restore point: ' || rp.name);
END LOOP;
END;
/
EXIT;
EOF
if grep -q "ORA-" "$LOG_FILE"; then
log "Error dropping restore points on ${db}"
return 1
fi
log "All restore points dropped successfully on ${db}"
}
# Main execution
{
verify_primary
standbys=$(get_standbys)
# Process standbys
for standby in $standbys; do
log "Processing standby database: ${standby}"
stop_mrp "$standby"
drop_restore_points "$PRIMARY_HOST" "$standby"
start_mrp "$standby"
done
# Process primary
log "Processing primary database: ${PRIMARY_DB}"
drop_restore_points "$PRIMARY_HOST" "$PRIMARY_DB"
log "Operation completed successfully"
} | tee -a "$LOG_FILE"
echo "Detailed log saved to: ${LOG_FILE}"
###
#!/bin/bash
# Usage: ./create_dg_restore_point.sh
# Input file format (single line):
# primary_host primary_db
# Check input file
if [ $# -ne 2 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
RESTORE_PREFIX="$2"
PRIMARY_HOST=$(awk '{print $1}' "$INPUT_FILE")
PRIMARY_DB=$(awk '{print $2}' "$INPUT_FILE")
DG_USER="sys"
LOG_FILE="create_restore_point_$(date +%Y%m%d_%H%M).log"
RESTORE_NAME="${RESTORE_PREFIX}_$(date +%Y%m%d_%H%M%S)"
# Validate input
if [[ -z "$PRIMARY_HOST" || -z "$PRIMARY_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "primary_host primary_db"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${PRIMARY_HOST}/${PRIMARY_DB}: " SYS_PASSWORD
echo
# Function to log messages
log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
# Verify primary database role
verify_primary() {
log "Verifying primary database role..."
role=$(sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
)
if [[ "$role" != "PRIMARY" ]]; then
log "Error: ${PRIMARY_HOST}/${PRIMARY_DB} is not a primary database (Role: ${role})"
exit 1
fi
log "Primary database confirmed"
}
# Get standby databases from broker configuration
get_standbys() {
log "Retrieving standby databases from broker configuration..."
standby_list=$(echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nshow configuration" | dgmgrl / | \
awk '/Physical standby database/ {print $1}')
if [ -z "$standby_list" ]; then
log "No standby databases found in broker configuration"
exit 0
fi
log "Found standby databases: ${standby_list}"
echo "$standby_list"
}
# Stop MRP on standby
stop_mrp() {
local standby_db=$1
log "Stopping MRP on ${standby_db}..."
echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nedit database ${standby_db} set state='APPLY-OFF';" | dgmgrl / >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log "Failed to stop MRP on ${standby_db}"
exit 1
fi
log "MRP stopped successfully on ${standby_db}"
}
# Start MRP on standby
start_mrp() {
local standby_db=$1
log "Starting MRP on ${standby_db}..."
echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nedit database ${standby_db} set state='APPLY-ON';" | dgmgrl / >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log "Failed to start MRP on ${standby_db}"
exit 1
fi
log "MRP started successfully on ${standby_db}"
}
# Create guaranteed restore point
create_restore_point() {
local host=$1
local db=$2
log "Creating guaranteed restore point ${RESTORE_NAME} on ${db}..."
sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF >> "$LOG_FILE" 2>&1
CREATE RESTORE POINT ${RESTORE_NAME} GUARANTEE FLASHBACK DATABASE;
EXIT;
EOF
if grep -q "ORA-" "$LOG_FILE"; then
log "Error creating restore point on ${db}"
return 1
fi
log "Restore point ${RESTORE_NAME} created successfully on ${db}"
}
# Verify DG synchronization
verify_dg_sync() {
local standby_db=$1
log "Verifying Data Guard synchronization for ${standby_db}..."
sync_status=$(echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\nshow database ${standby_db}" | dgmgrl / | \
awk '/Apply Lag/ {lag=$3" "$4} /Transport Lag/ {transport=$3" "$4} END {print "Apply Lag: "lag", Transport Lag: "transport}')
log "Synchronization status for ${standby_db}: ${sync_status}"
}
# Main execution
{
log "Starting Data Guard restore point creation process"
verify_primary
standbys=$(get_standbys)
# Process standbys
for standby in $standbys; do
log "Processing standby database: ${standby}"
stop_mrp "$standby"
create_restore_point "$PRIMARY_HOST" "$standby"
start_mrp "$standby"
verify_dg_sync "$standby"
done
# Process primary
log "Processing primary database: ${PRIMARY_DB}"
create_restore_point "$PRIMARY_HOST" "$PRIMARY_DB"
log "Operation completed successfully"
log "Restore point name: ${RESTORE_NAME}"
} | tee -a "$LOG_FILE"
echo "Detailed log saved to: ${LOG_FILE}"
