#!/bin/bash

clear

cat <<'EOF'

 ██████╗██╗   ██╗██╗     ██╗      █████╗      ██████╗ ██████╗  ██████╗ ██╗   ██╗██████╗
██╔════╝╚██╗ ██╔╝██║     ██║     ██╔══██╗    ██╔════╝ ██╔══██╗██╔═══██╗██║   ██║██╔══██╗
██║      ╚████╔╝ ██║     ██║     ███████║    ██║  ███╗██████╔╝██║   ██║██║   ██║██████╔╝
██║       ╚██╔╝  ██║     ██║     ██╔══██║    ██║   ██║██╔══██╗██║   ██║██║   ██║██╔═══╝
╚██████╗   ██║   ███████╗███████╗██║  ██║    ╚██████╔╝██║  ██║╚██████╔╝╚██████╔╝██║
 ╚═════╝   ╚═╝   ╚══════╝╚══════╝╚═╝  ╚═╝     ╚═════╝ ╚═╝  ╚═╝ ╚═════╝  ╚═════╝ ╚═╝

                DBVISIT GAP AUTO-RECOVERY TOOL

 Author      : Leonardo Lopes
 Position    : CEO
 Company     : Cylla Group
 Website     : https://cylla.group
 Blog        : https://blog.dbahero.com
 Version     : 1.0

 Purpose:
 Automatic detection and recovery of Archive Log Gaps
 using Oracle RMAN and Dbvisit Standby.

EOF

echo

CONFIG="cylla"
ORACLE_SID="cylla"
export ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1"
export DBVISIT_HOME=/usr/dbvisit/standbymp
export PATH=$ORACLE_HOME/bin:$PATH:$DBVISIT_HOME/bin
 
ORAENV_ASK=NO
. oraenv >> /dev/null

START_TIME=$(date '+%Y-%m-%d %H:%M:%S')

echo "===================================================="
echo "Dbvisit Gap Auto-Recovery"
echo "Started at          : $START_TIME"
echo "Configuration       : $CONFIG"
echo "Oracle SID          : $ORACLE_SID"
echo "===================================================="

DB_ROLE=$(sqlplus -s / as sysdba <<EOF
set heading off feedback off verify off echo off pagesize 0
select database_role from v\$database;
exit
EOF
)

DB_ROLE=$(echo "$DB_ROLE" | tr -d '[:space:]')

echo "Database role       : $DB_ROLE"

if [ "$DB_ROLE" != "PRIMARY" ]; then
  echo "Status              : Database is not PRIMARY. Skipping execution."

  END_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "Finished at         : $END_TIME"
  echo "===================================================="
  exit 0
fi

OUTPUT=$(dbvcli -d "$CONFIG" -t oracle lgr --no-prompt)

CURRENT_SEQ=$(echo "$OUTPUT" | awk -F: '/Current source sequence/ {print $2}' | tr -cd '0-9')
ARCHIVED_SEQ=$(echo "$OUTPUT" | awk -F: '/Archived source sequence/ {print $2}' | tr -cd '0-9')
NEXT_REQUIRED_SEQ=$(echo "$OUTPUT" | awk -F: '/Next required destination recovery sequence/ {print $2}' | tr -cd '0-9')
ARCHIVE_GAP=$(echo "$OUTPUT" | awk -F: '/Archive log gap/ {print $2}' | tr -cd '0-9')

echo "Current sequence    : $CURRENT_SEQ"
echo "Archived sequence   : $ARCHIVED_SEQ"
echo "Next required seq   : $NEXT_REQUIRED_SEQ"
echo "Archive gap         : $ARCHIVE_GAP"

if [ -z "$ARCHIVE_GAP" ] || [ "$ARCHIVE_GAP" -eq 0 ]; then
  echo "Status              : No archive gap detected."

  END_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "Finished at         : $END_TIME"
  echo "===================================================="
  exit 0
fi

FROM_SEQ="$NEXT_REQUIRED_SEQ"
TO_SEQ="$CURRENT_SEQ"

if ! [[ "$FROM_SEQ" =~ ^[0-9]+$ ]] || ! [[ "$TO_SEQ" =~ ^[0-9]+$ ]]; then
  echo "ERROR               : Invalid sequence values."
  echo "FROM_SEQ            : [$FROM_SEQ]"
  echo "TO_SEQ              : [$TO_SEQ]"

  END_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "Finished at         : $END_TIME"
  echo "===================================================="
  exit 1
fi

echo "Missing sequences   : $FROM_SEQ to $TO_SEQ"
echo "Action              : Restoring archivelogs from RMAN backup..."

RMAN_CMD="/tmp/restore_arch_${CONFIG}.rman"

cat > "$RMAN_CMD" <<EOF
RUN {
  RESTORE ARCHIVELOG FROM SEQUENCE ${FROM_SEQ} UNTIL SEQUENCE ${TO_SEQ};
}
EOF

echo "RMAN command file   : $RMAN_CMD"
cat "$RMAN_CMD"

rman target / cmdfile="$RMAN_CMD"

if [ $? -ne 0 ]; then
  echo "ERROR               : RMAN restore failed."

  END_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "Finished at         : $END_TIME"
  echo "===================================================="
  exit 1
fi

echo "Status              : RMAN restore completed."

echo "Action              : Sending restored logs with Dbvisit..."
dbvcli -d "$CONFIG" -t oracle send-logs --no-prompt

if [ $? -ne 0 ]; then
  echo "ERROR               : Dbvisit send-logs failed."

  END_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "Finished at         : $END_TIME"
  echo "===================================================="
  exit 1
fi

echo "Status              : Dbvisit send-logs completed."

echo "Action              : Applying logs on standby..."
dbvcli -d "$CONFIG" -t oracle apply-logs --no-prompt

if [ $? -ne 0 ]; then
  echo "ERROR               : Dbvisit apply-logs failed."

  END_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "Finished at         : $END_TIME"
  echo "===================================================="
  exit 1
fi

echo "Status              : Dbvisit apply-logs completed."

echo "Final log gap report:"
dbvcli -d "$CONFIG" -t oracle lgr --no-prompt

END_TIME=$(date '+%Y-%m-%d %H:%M:%S')

echo "===================================================="
echo "Finished at         : $END_TIME"
echo "Status              : Completed successfully."
echo "===================================================="

exit 0