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}"
#!/bin/bash
# Usage: ./primary_standby_param_compare.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")
REPORT_FILE="param_diff_${PRIMARY_DB}_$(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
# Temporary files
PRIMARY_TMP=$(mktemp)
STANDBY_TMP=$(mktemp)
# Function to get all parameters (including hidden)
get_all_parameters() {
local host="$1"
local db="$2"
local out_file="$3"
sqlplus -S "sys/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF > "$out_file" 2>&1
SET FEEDBACK OFF HEADING OFF PAGESIZE 0 LINESIZE 1000
SELECT pi.ksppinm || '=' || cv.ksppstvl
FROM x\$ksppi pi
JOIN x\$ksppcv cv ON pi.indx = cv.indx
UNION
SELECT name || '=' || value
FROM v\$parameter
ORDER BY 1;
EXIT
EOF
if grep -q "ORA-" "$out_file"; then
echo "Error connecting to ${host}/${db}"
return 1
fi
}
# Verify primary and get standbys
echo -n "Checking primary database status... "
ROLE=$(sqlplus -S "sys/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF
SET FEEDBACK OFF HEADING OFF PAGESIZE 0
SELECT DATABASE_ROLE FROM v\$database;
EXIT;
EOF
)
if [[ "$ROLE" != "PRIMARY" ]]; then
echo -e "\nError: ${PRIMARY_HOST}/${PRIMARY_DB} is not a primary database (Role: ${ROLE})"
exit 1
fi
echo "OK (Primary confirmed)"
# Get standby databases
echo -n "Discovering standby databases... "
STANDBY_LIST=$(sqlplus -S "sys/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF
SET FEEDBACK OFF HEADING OFF PAGESIZE 0
SELECT DISTINCT UPPER(destination)
FROM v\$archive_dest
WHERE target='STANDBY' AND status='VALID' AND destination IS NOT NULL;
EXIT;
EOF
)
if [ -z "$STANDBY_LIST" ]; then
echo -e "\nNo standby databases found"
exit 0
fi
echo "Found: $(echo "$STANDBY_LIST" | wc -l)"
# Get primary parameters
echo -e "\nCollecting parameters from primary (${PRIMARY_DB})..."
get_all_parameters "$PRIMARY_HOST" "$PRIMARY_DB" "$PRIMARY_TMP"
# Compare with each standby
for STANDBY in $STANDBY_LIST; do
echo -e "\nProcessing standby: ${STANDBY}"
# Get standby parameters
get_all_parameters "$PRIMARY_HOST" "$STANDBY" "$STANDBY_TMP"
# Compare and save results
echo -e "\n=== Differences between ${PRIMARY_DB} (Primary) and ${STANDBY} (Standby) ===" | tee -a "$REPORT_FILE"
diff --side-by-side --suppress-common-lines "$PRIMARY_TMP" "$STANDBY_TMP" | tee -a "$REPORT_FILE"
# Cleanup standby temp file
rm -f "$STANDBY_TMP"
done
# Final cleanup and report
rm -f "$PRIMARY_TMP"
echo -e "\nComparison complete. Report saved to: ${REPORT_FILE}"
#!/bin/bash
# Usage: ./compare_db_params_hidden.sh
# Input file format (two lines):
# source_host source_db
# target_host target_db
# Check if input file is provided
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE=$1
# Read source and target details from input file
SRC_HOST=$(sed -n 1p $INPUT_FILE | awk '{print $1}')
SRC_DB=$(sed -n 1p $INPUT_FILE | awk '{print $2}')
TGT_HOST=$(sed -n 2p $INPUT_FILE | awk '{print $1}')
TGT_DB=$(sed -n 2p $INPUT_FILE | awk '{print $2}')
# Validate input file content
if [[ -z "$SRC_HOST" || -z "$SRC_DB" || -z "$TGT_HOST" || -z "$TGT_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "source_host source_db"
echo "target_host target_db"
exit 1
fi
# Temporary files for parameters
SRC_TMP=$(mktemp)
TGT_TMP=$(mktemp)
# Function to fetch ALL parameters (including hidden)
fetch_params() {
local host=$1
local db=$2
local tmp_file=$3
read -s -p "Enter SYS password for $host/$db: " password
echo
echo "Fetching parameters (including hidden) from $host/$db..."
sqlplus -S "sys/${password}@//${host}:1521/${db} as sysdba" << EOF > "$tmp_file" 2>&1
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SELECT pi.ksppinm || '=' || cv.ksppstvl
FROM x\$ksppi pi
JOIN x\$ksppcv cv ON pi.indx = cv.indx
ORDER BY pi.ksppinm;
EXIT
EOF
# Check for errors
if grep -q "ORA-" "$tmp_file"; then
echo "Failed to connect to $host/$db. Check credentials/networking."
rm -f "$tmp_file"
exit 1
fi
}
# Fetch parameters for source and target
fetch_params "$SRC_HOST" "$SRC_DB" "$SRC_TMP"
fetch_params "$TGT_HOST" "$TGT_DB" "$TGT_TMP"
# Compare parameters (including hidden)
echo "Comparing parameters (including hidden) between $SRC_DB and $TGT_DB..."
diff --side-by-side --suppress-common-lines "$SRC_TMP" "$TGT_TMP"
# Cleanup
rm -f "$SRC_TMP" "$TGT_TMP"
##
#!/bin/bash
# Usage: ./rac_health_scan.sh
# Input file format (single line):
# scan_host db_name
# Check input file
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
SCAN_HOST=$(awk '{print $1}' "$INPUT_FILE")
DB_NAME=$(awk '{print $2}' "$INPUT_FILE")
REPORT_FILE="rac_health_$(date +%Y%m%d_%H%M).html"
# Validate input
if [[ -z "$SCAN_HOST" || -z "$DB_NAME" ]]; then
echo "Invalid input file format. Expected:"
echo "scan_host db_name"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${SCAN_HOST}/${DB_NAME}: " SYS_PASSWORD
echo
# HTML Header
cat > "$REPORT_FILE" << EOF
RAC Health Check: ${DB_NAME}
"; print "
" }
' >> "$REPORT_FILE"
SET FEEDBACK OFF HEADING OFF PAGESIZE 0 LINESIZE 1000
${query}
EXIT
EOF
}
# RAC Node Status
run_sql_to_html "Cluster Node Status" "
SELECT 'Node ' || instance_number || ': ' ||
instance_name || ' (' || host_name || ')' || '=' ||
status || ' | Version: ' || version || ' | Startup: ' ||
TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI')
FROM gv\$instance;
"
# Wait Events Analysis
run_sql_to_html "Top Wait Events (Non-Idle)" "
SELECT event || '=' ||
ROUND(time_waited_micro/1000000,1) || 's (Waits: ' ||
total_waits || ', Avg: ' ||
ROUND(time_waited_micro/total_waits/1000,2) || 'ms)'
FROM (
SELECT event, total_waits, time_waited_micro
FROM gv\$system_event
WHERE wait_class NOT IN ('Idle', 'System I/O')
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY
);
"
# Global Cache Statistics
run_sql_to_html "Global Cache Performance" "
SELECT
'Global Cache CR Block Receive Time (ms)' || '=' ||
ROUND((SUM(CASE name WHEN 'gc cr block receive time' THEN value END) /
SUM(CASE name WHEN 'gc cr blocks received' THEN value END)) * 10,2)
|| 'ms [CR] | ' ||
ROUND((SUM(CASE name WHEN 'gc current block receive time' THEN value END) /
SUM(CASE name WHEN 'gc current blocks received' THEN value END)) * 10,2)
|| 'ms [Current]'
FROM gv\$sysstat
WHERE name IN (
'gc cr block receive time', 'gc cr blocks received',
'gc current block receive time', 'gc current blocks received'
);
"
# Tablespace Usage
run_sql_to_html "Tablespace Usage" "
SELECT tablespace_name || '=' ||
ROUND(used_percent,1) || '% used | ' ||
CASE WHEN used_percent > 90 THEN 'CRITICAL'
WHEN used_percent > 80 THEN 'WARNING'
ELSE 'OK' END
FROM (
SELECT a.tablespace_name,
(a.bytes_alloc - nvl(b.bytes_free,0))/a.bytes_alloc*100 used_percent
FROM (
SELECT tablespace_name, SUM(bytes) bytes_alloc
FROM dba_data_files GROUP BY tablespace_name
) a,
(
SELECT tablespace_name, SUM(bytes) bytes_free
FROM dba_free_space GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name(+)
)
ORDER BY used_percent DESC;
"
# Cluster Interconnect
run_sql_to_html "Interconnect Health" "
SELECT
'Network Latency (' || name || ')' || '=' ||
ROUND(value/100,2) || 'ms' ||
CASE WHEN value/100 > 2 THEN ' CRITICAL'
WHEN value/100 > 1 THEN ' WARNING' ELSE '' END
FROM gv\$sysmetric
WHERE metric_name = 'Network Latency'
AND group_id = 2
AND value > 0;
"
# HTML Footer
cat >> "$REPORT_FILE" << EOF
EOF
echo -e "\nReport generated: ${REPORT_FILE}"
Oracle RAC Health Report: ${DB_NAME}
Generated at: $(date)
SCAN Name: ${SCAN_HOST}
EOF # Function to run SQL and format as HTML table run_sql_to_html() { local title="$1" local query="$2" local critical="$3" echo "${title}
" >> "$REPORT_FILE" sqlplus -S "sys/${SYS_PASSWORD}@//${SCAN_HOST}:1521/${DB_NAME} as sysdba" << EOF | awk ' BEGIN { print "| Metric | Value |
|---|---|
| " $0 " | |
| " arr[1] " | " arr[2] " |
Saturday, March 15, 2025
1) drop
#!/bin/bash
# Oracle Schema Migration Script
# Description: Exports a schema from Source DB (A) and imports into Target DB (B)
# Pre-requisites:
# - Oracle Data Pump directories must be configured on both databases
# - User must have necessary privileges (EXP_FULL_DATABASE, IMP_FULL_DATABASE)
# - Target schema user must exist in Database B
# Configuration
SOURCE_DB="SOURCE_USER/SOURCE_PASSWORD@DATABASE_A"
TARGET_DB="TARGET_USER/TARGET_PASSWORD@DATABASE_B"
SCHEMA_NAME="YOUR_SCHEMA_NAME"
DATA_PUMP_DIR="DATA_PUMP_DIR_NAME" # Oracle directory object name
DUMPFILE="${SCHEMA_NAME}_export.dmp"
LOGFILE_EXPORT="${SCHEMA_NAME}_export.log"
LOGFILE_IMPORT="${SCHEMA_NAME}_import.log"
# Function to drop all objects in target schema
drop_target_objects() {
echo "Dropping all objects in target schema: ${SCHEMA_NAME}"
sqlplus -S /nolog <" > $REPORT_FILE
echo "Schema Comparison Report " >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "
" >> $REPORT_FILE
rm temp_missing_objects.txt
# Step 4: Compare row counts for all tables
echo "
" >> $REPORT_FILE
rm temp_row_counts.txt
# Step 5: Finish HTML Report
echo "
Schema Comparison Report
" >> $REPORT_FILE # Step 3: Compare missing objects (tables, views, indexes, sequences, procedures) echo "Missing Objects
" >> $REPORT_FILE echo "| Object Type | Object Name |
|---|---|
| $object_type | $object_name |
Row Count Differences
" >> $REPORT_FILE echo "| Table Name | Source Count | Target Count | Status |
|---|---|---|---|
| $table_name | $src_count | $tgt_count | $status |