refresh_tool/
├── config
│ ├── variables.env # User-provided configurations
│ └── db_credentials.env # Source/Target DB details
├── scripts
│ ├── refresh_schemas.sh # Main script
│ └── schema_functions.sh # Comparison/Validation functions
└── logs/ # Auto-created log directory
SCHEMAS="HR,OE,SH"
NAS_DIR="/dba_nas/refresh"
DIRECTORY_NAME="DATA_PUMP_DIR"
#!/bin/bash
compare_schema_objects() {
local schema=$1
local source_conn=$2
local target_conn=$3
sqlplus -s /nolog << EOF | tee -a $LOG_FILE
connect $source_conn
set pagesize 0 feedback off
select object_type || ':' || count(*)
from dba_objects
where owner = upper('$schema')
group by object_type;
exit;
EOF
sqlplus -s /nolog << EOF | tee -a $LOG_FILE
connect $target_conn
set pagesize 0 feedback off
select object_type || ':' || count(*)
from dba_objects
where owner = upper('$schema')
group by object_type;
exit;
EOF
}
check_invalid_objects() {
local schema=$1
local conn=$2
invalid_count=$(sqlplus -s /nolog << EOF
connect $conn
set heading off feedback off
select count(*)
from dba_objects
where owner = upper('$schema')
and status != 'VALID';
exit;
EOF
)
[ $invalid_count -gt 0 ] && return 1 || return 0
}
compare_row_counts() {
local schema=$1
local source_conn=$2
local target_conn=$3
sqlplus -s /nolog << EOF | tee -a $LOG_FILE
connect $source_conn
set pagesize 0 feedback off
execute dbms_output.put_line('=== Source DB Row Counts ===');
select table_name || ':' || num_rows
from dba_tables
where owner = upper('$schema');
exit;
EOF
sqlplus -s /nolog << EOF | tee -a $LOG_FILE
connect $target_conn
set pagesize 0 feedback off
execute dbms_output.put_line('=== Target DB Row Counts ===');
select table_name || ':' || num_rows
from dba_tables
where owner = upper('$schema');
exit;
EOF
}
#!/bin/bash
CURRENT_DIR=$(dirname "$0")
LOG_DIR="${CURRENT_DIR}/../logs"
LOG_FILE="${LOG_DIR}/refresh_$(date +%Y%m%d%H%M).log"
mkdir -p $LOG_DIR
# Load configurations
source "${CURRENT_DIR}/../config/variables.env"
source "${CURRENT_DIR}/../config/db_credentials.env"
source "${CURRENT_DIR}/schema_functions.sh"
# Database Connections
SOURCE_CONN="sys/${SYS_PWD}@${SOURCE_DB} as sysdba"
TARGET_CONN="sys/${SYS_PWD}@${TARGET_DB} as sysdba"
# Export parameters
EXPORT_FILE="export_$(date +%Y%m%d).dmp"
EXPORT_LOG="export_$(date +%Y%m%d).log"
# Import parameters
IMPORT_LOG="import_$(date +%Y%m%d).log"
check_schema_existence() {
local schema=$1
exists=$(sqlplus -s /nolog << EOF
connect $TARGET_CONN
set heading off feedback off
select count(*) from dba_users where username = upper('$schema');
exit;
EOF
)
[ $exists -gt 0 ] && return 0 || return 1
}
drop_schema_objects() {
local schema=$1
sqlplus -s /nolog << EOF | tee -a $LOG_FILE
connect $TARGET_CONN
set serveroutput on
begin
for obj in (select object_name, object_type
from dba_objects
where owner = upper('$schema'))
loop
execute immediate 'drop ' || obj.object_type || ' ' || obj.object_name;
end loop;
for syn in (select synonym_name
from dba_synonyms
where table_owner = upper('$schema')
and owner = 'PUBLIC')
loop
execute immediate 'drop public synonym ' || syn.synonym_name;
end loop;
end;
/
exit;
EOF
}
perform_export() {
echo "Starting Export..." | tee -a $LOG_FILE
expdp $SOURCE_CONN directory=$DIRECTORY_NAME \
schemas=$SCHEMAS \
dumpfile=$EXPORT_FILE \
logfile=$EXPORT_LOG \
parallel=4 \
compression=ALL \
reuse_dumpfiles=YES
}
perform_import() {
echo "Starting Import..." | tee -a $LOG_FILE
impdp $TARGET_CONN directory=$DIRECTORY_NAME \
schemas=$SCHEMAS \
dumpfile=$EXPORT_FILE \
logfile=$IMPORT_LOG \
remap_schema=$SCHEMAS \
transform=OID:N \
parallel=4
}
main() {
# Perform export from source
perform_export | tee -a $LOG_FILE
# Process each schema
IFS=',' read -ra SCHEMA_ARRAY <<< "$SCHEMAS"
for schema in "${SCHEMA_ARRAY[@]}"; do
schema=$(echo $schema | xargs) # Trim whitespace
if check_schema_existence $schema; then
echo "Dropping objects in $schema..." | tee -a $LOG_FILE
drop_schema_objects $schema
fi
perform_import | tee -a $LOG_FILE
# Post-import validation
compare_schema_objects $schema $SOURCE_CONN $TARGET_CONN
check_invalid_objects $schema $TARGET_CONN || echo "Invalid objects found!"
compare_row_counts $schema $SOURCE_CONN $TARGET_CONN
done
echo "Refresh completed. Log file: $LOG_FILE"
}
main
#!/bin/bash
# Email notification function
send_alert() {
local subject=$1
local body=$2
local attachment=$3
if [ "$EMAIL_ENABLED" = "true" ]; then
if [ "$USE_SSL" = "true" ]; then
ssl_option="-S smtp-use-starttls -S ssl-verify=ignore"
fi
echo "$body" | s-nail -s "$subject" \
-S smtp="$SMTP_SERVER" \
-S smtp-auth=login \
-S smtp-auth-user="$EMAIL_USER" \
-S smtp-auth-password="$EMAIL_PASSWORD" \
$ssl_option \
-a "$attachment" \
"$EMAIL_TO"
fi
}
# Checksum verification
generate_checksum() {
local dump_file=$1
sha256sum $dump_file > ${dump_file}.sha256
}
verify_checksum() {
local dump_file=$1
sha256sum -c ${dump_file}.sha256 || return 1
}
# Tablespace verification
check_tablespace() {
local schema=$1
local conn=$2
local target_conn=$3
# Get schema's tablespace usage from source
source_usage=$(sqlplus -s /nolog << EOF
connect $conn
set pagesize 0 feedback off
select sum(bytes) from dba_segments
where owner = upper('$schema');
exit;
EOF
)
# Get target tablespace free space
target_free=$(sqlplus -s /nolog << EOF
connect $target_conn
set pagesize 0 feedback off
select sum(bytes) from dba_free_space
where tablespace_name = (
select default_tablespace
from dba_users
where username = upper('$schema')
);
exit;
EOF
)
if [ $source_usage -gt $target_free ]; then
echo "ERROR: Insufficient tablespace for $schema"
return 1
fi
}
# Retry with backoff
retry_operation() {
local cmd=$@
local max_retries=3
local delay=60
for ((i=1; i<=$max_retries; i++)); do
$cmd && return 0
echo "Attempt $i failed. Retrying in $delay seconds..."
sleep $delay
done
return 1
}
# Rollback functions
create_rollback() {
local schema=$1
local timestamp=$(date +%Y%m%d%H%M)
local dump_file="rollback_${schema}_${timestamp}.dmp"
echo "Creating rollback for $schema..." | tee -a $LOG_FILE
expdp $TARGET_CONN directory=$DIRECTORY_NAME \
schemas=$schema \
dumpfile=$dump_file \
logfile=rollback_${schema}.log
echo $dump_file
}
restore_rollback() {
local schema=$1
local dump_file=$2
echo "Restoring from rollback $dump_file..." | tee -a $LOG_FILE
impdp $TARGET_CONN directory=$DIRECTORY_NAME \
schemas=$schema \
dumpfile=$dump_file \
logfile=restore_${schema}.log \
table_exists_action=replace
}
#!/bin/bash
# ... [Keep previous header and config loading]
# Additional config loading
source "${CURRENT_DIR}/../config/email_settings.env"
# Enhanced connection handling
if [ "$USE_WALLET" = "true" ]; then
SOURCE_CONN="/@${SOURCE_DB} as sysdba"
TARGET_CONN="/@${TARGET_DB} as sysdba"
else
SOURCE_CONN="sys/${SYS_PWD}@${SOURCE_DB} as sysdba"
TARGET_CONN="sys/${SYS_PWD}@${TARGET_DB} as sysdba"
fi
# Enhanced main function with error handling
main() {
trap "handle_error" ERR
start_time=$(date +%s)
send_alert "Schema Refresh Started" "Refresh process initiated for schemas: $SCHEMAS"
# Pre-flight checks
verify_checksum $NAS_DIR/$EXPORT_FILE 2>/dev/null || {
echo "Performing fresh export..."
retry_operation perform_export | tee -a $LOG_FILE
}
generate_checksum $NAS_DIR/$EXPORT_FILE
IFS=',' read -ra SCHEMA_ARRAY <<< "$SCHEMAS"
for schema in "${SCHEMA_ARRAY[@]}"; do
schema=$(echo $schema | xargs)
local rollback_file=""
# Tablespace check
check_tablespace $schema $SOURCE_CONN $TARGET_CONN || {
send_alert "Refresh Failed" "Tablespace check failed for $schema" $LOG_FILE
exit 1
}
if check_schema_existence $schema; then
rollback_file=$(create_rollback $schema)
drop_schema_objects $schema
fi
if ! retry_operation perform_import; then
if [ -n "$rollback_file" ]; then
restore_rollback $schema $rollback_file || {
send_alert "Critical Failure" "Both import and rollback failed for $schema" $LOG_FILE
exit 1
}
fi
send_alert "Refresh Failed" "Import failed for $schema" $LOG_FILE
exit 1
fi
# Post-import validation
validate_refresh $schema
done
send_alert "Refresh Completed" "Successfully refreshed schemas: $SCHEMAS" $LOG_FILE
}
handle_error() {
error_time=$(date +%s)
duration=$((error_time - start_time))
echo "Error occurred after $duration seconds" | tee -a $LOG_FILE
send_alert "Refresh Failed" "Error during schema refresh process" $LOG_FILE
exit 1
}
validate_refresh() {
local schema=$1
# Object comparison
compare_schema_objects $schema $SOURCE_CONN $TARGET_CONN
# Invalid objects check
check_invalid_objects $schema $TARGET_CONN || {
send_alert "Validation Warning" "Invalid objects found in $schema" $LOG_FILE
}
# Row count comparison
row_diff=$(compare_row_counts $schema $SOURCE_CONN $TARGET_CONN | grep -c 'DIFFERENCE')
[ $row_diff -gt 0 ] && {
send_alert "Validation Warning" "Row count mismatches in $schema" $LOG_FILE
}
}
# ... [Keep other functions but add retry_operation calls]
perform_export() {
echo "Starting Export..." | tee -a $LOG_FILE
expdp $SOURCE_CONN directory=$DIRECTORY_NAME \
schemas=$SCHEMAS \
dumpfile=$EXPORT_FILE \
logfile=$EXPORT_LOG \
parallel=4 \
compression=ALL \
reuse_dumpfiles=YES \
encryption=ALL \
encryption_password=$SYS_PWD
}
perform_import() {
echo "Starting Import..." | tee -a $LOG_FILE
verify_checksum $NAS_DIR/$EXPORT_FILE || return 1
impdp $TARGET_CONN directory=$DIRECTORY_NAME \
schemas=$SCHEMAS \
dumpfile=$EXPORT_FILE \
logfile=$IMPORT_LOG \
remap_schema=$SCHEMAS \
transform=OID:N \
parallel=4 \
encryption_password=$SYS_PWD
}
# ... [Keep remaining functions]
#!/bin/bash
generate_html_report() {
local schema=$1
local source_conn=$2
local target_conn=$3
local report_file="${NAS_DIR}/reports/${schema}_comparison_$(date +%Y%m%d%H%M).html"
mkdir -p "${NAS_DIR}/reports"
# Get object counts from source
source_objects=$(sqlplus -s /nolog << EOF
connect $source_conn
set pagesize 0 feedback off
select object_type || ':' || count(*)
from dba_objects
where owner = upper('$schema')
group by object_type
order by object_type;
exit;
EOF
)
# Get object counts from target
target_objects=$(sqlplus -s /nolog << EOF
connect $target_conn
set pagesize 0 feedback off
select object_type || ':' || count(*)
from dba_objects
where owner = upper('$schema')
group by object_type
order by object_type;
exit;
EOF
)
# Get invalid objects
source_invalid=$(get_invalid_count "$schema" "$source_conn")
target_invalid=$(get_invalid_count "$schema" "$target_conn")
# Get index status
source_indexes=$(get_index_status "$schema" "$source_conn")
target_indexes=$(get_index_status "$schema" "$target_conn")
# Generate HTML report
cat << HTML > "$report_file"
Schema Comparison Report: $schema
Summary
Schema Name: $schema
Report Date: $(date +"%Y-%m-%d %H:%M:%S")
Object Comparison
| Object Type |
Source DB Count |
Target DB Count |
Status |
$(join -t: -a1 -a2 -o 0,1.2,2.2 -e "0" \
<(echo "$source_objects" | sort) \
<(echo "$target_objects" | sort) | \
awk -F: '{printf "| %s | %s | %s | %s
\n", \
$1, $2, $3, \
($2 == $3 ? "Match | " : "Mismatch | ")}')
Invalid Objects
| Database |
Invalid Count |
| Source DB |
$source_invalid
| Target DB |
$target_invalid
Index Status
| Database |
Invalid Indexes |
| Source DB |
$(echo "$source_indexes" | wc -l) |
| Target DB |
$(echo "$target_indexes" | wc -l) |
Table Row Count Comparison
| Table Name |
Source Rows |
Target Rows |
Status |
$(compare_row_counts $schema $source_conn $target_conn | \
awk -F: '/:/ {printf "| %s | %s | %s | %s
\n", \
$1, $2, $3, \
($2 == $3 ? "Match | " : "Mismatch | ")}')
HTML
echo "Generated report: $report_file"
echo "
View Comparison Report" | tee -a $LOG_FILE
}
get_invalid_count() {
local schema=$1
local conn=$2
sqlplus -s /nolog << EOF
connect $conn
set heading off feedback off
select count(*)
from dba_objects
where owner = upper('$schema')
and status != 'VALID';
exit;
EOF
}
get_index_status() {
local schema=$1
local conn=$2
sqlplus -s /nolog << EOF
connect $conn
set heading off feedback off
select index_name
from dba_indexes
where owner = upper('$schema')
and status not in ('VALID', 'USABLE');
exit;
EOF
}
get_index_status() {
local schema=$1
local conn=$2
sqlplus -s /nolog << EOF
connect $conn
set heading off feedback off
-- Check both non-partitioned and partitioned indexes
with index_status as (
/* Non-partitioned indexes */
select
i.index_name,
i.status as index_status,
null as partition_status
from dba_indexes i
where i.owner = upper('$schema')
and i.partitioned = 'NO'
and i.status not in ('VALID', 'USABLE')
union all
/* Partitioned indexes with bad partitions */
select distinct
i.index_name,
i.status as index_status,
p.status as partition_status
from dba_indexes i
join dba_ind_partitions p
on i.owner = p.index_owner
and i.index_name = p.index_name
where i.owner = upper('$schema')
and i.partitioned = 'YES'
and (i.status not in ('VALID', 'USABLE')
or p.status != 'USABLE')
)
select
index_name || ':' ||
nvl2(partition_status,
'PARTITION=' || partition_status,
'INDEX=' || index_status)
from index_status
order by 1;
exit;
EOF
}
generate_html_report() {
# ... [keep previous variable declarations]
# New: Get index status for both databases
source_indexes=$(get_index_status "$schema" "$source_conn")
target_indexes=$(get_index_status "$schema" "$target_conn")
# Generate HTML report with side-by-side comparison
cat << HTML > "$report_file"
Schema Comparison: $schema
Object Type Counts
$(join -t: -a1 -a2 -o 0,1.2,2.2 -e "0" \
<(echo "$source_objects" | sort) \
<(echo "$target_objects" | sort) | \
awk -F: '{printf "| %s | %s | %s | %s
\n",
$1, $2, $3,
($2 == $3 ? "✅ Match | " : "❌ Mismatch | ")}')
Invalid Objects
| Source DB |
$source_invalid |
| Target DB |
$target_invalid |
Index Status
$(awk -F: '
BEGIN {
# Load source indexes
while(getline < "source_idx.tmp") {
split($0, parts, ":");
src_idx[parts[1]] = parts[2];
}
# Load target indexes
while(getline < "target_idx.tmp") {
split($0, parts, ":");
tgt_idx[parts[1]] = parts[2];
}
# Print combined results
for (idx in src_idx) {
print "| " idx " | " src_idx[idx] " | " (tgt_idx[idx] ? tgt_idx[idx] : "Valid") " |
";
delete tgt_idx[idx];
}
for (idx in tgt_idx) {
print "| " idx " | Valid | " tgt_idx[idx] " |
";
}
}' <<< "")
Table Row Counts
$(compare_row_counts $schema $source_conn $target_conn | \
awk -F: '/:/ {printf "| %s | %s | %s | %s
\n",
$1, $2, $3,
($2 == $3 ? "✅ Match | " : "❌ Mismatch | ")}')
HTML
# Cleanup temporary files
rm -f source_idx.tmp target_idx.tmp
echo "Generated report: $report_file"
}
compile_invalid_objects() {
local schema=$1
local conn=$2
local log_file=$3
echo "Compiling invalid objects for $schema..." | tee -a $log_file
# Generate compilation script
sqlplus -s /nolog << EOF > compile_$$.sql
connect $conn
set feedback off verify off pagesize 0 linesize 200
spool compile_temp_$$.sql
select case
when object_type = 'PACKAGE BODY' then
'alter package "' || owner || '"."' || object_name || '" compile body;'
else
'alter ' || object_type || ' "' || owner || '"."' || object_name || '" compile;'
end as ddl
from dba_objects
where owner = upper('$schema')
and status = 'INVALID'
and object_type in (
'FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY',
'TRIGGER','VIEW','MATERIALIZED VIEW','TYPE','TYPE BODY'
)
order by object_type, object_name;
spool off
exit;
EOF
# Execute compilation
sqlplus -s /nolog << EOF | tee -a $log_file
connect $conn
set feedback on echo on
@compile_$$.sql
@compile_temp_$$.sql
exit;
EOF
# Verify remaining invalid objects
invalid_count=$(sqlplus -s /nolog << EOF
connect $conn
set heading off feedback off
select count(*)
from dba_objects
where owner = upper('$schema')
and status = 'INVALID'
and object_type in (
'FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY',
'TRIGGER','VIEW','MATERIALIZED VIEW','TYPE','TYPE BODY'
);
exit;
EOF
)
# Cleanup temporary files
rm -f compile_$$.sql compile_temp_$$.sql
if [ $invalid_count -gt 0 ]; then
echo "Warning: $invalid_count invalid objects remaining after compilation" | tee -a $log_file
return 1
else
echo "All objects compiled successfully" | tee -a $log_file
return 0
fi
}
generate_invalid_object_report() {
local schema=$1
local conn=$2
local report_file="${NAS_DIR}/reports/${schema}_invalid_objects_$(date +%Y%m%d%H%M).html"
sqlplus -s /nolog << EOF > $report_file
connect $conn
set markup html on
set pagesize 50000
set feedback off
select
object_name as "Object Name",
object_type as "Object Type",
status as "Status",
created as "Created",
last_ddl_time as "Last DDL Time"
from dba_objects
where owner = upper('$schema')
and status = 'INVALID'
order by object_type, object_name;
exit;
EOF
echo "Generated invalid object report: $report_file" | tee -a $LOG_FILE
}
