#dr_check_db_ERPGF.sh
#!/bin/bash
#run ./dr_check_db_ERPGF.sh | tee dr_ERPGF.out
echo -e "\n"
echo -e "+++++ Start Check Database Oracle EBS (ERP-GF) Date $(date +"%Y-%m-%d %H:%M:%S") +++++\n"
echo -e "\n"
echo "============================================================="
echo "== Process : Start Service (If Not Auto Start) "
echo "============================================================="
# Start the database
sqlplus / as sysdba << EOF
startup;
exit;
EOF
# Start the listener (optional, but recommended if not already running)
lsnrctl start sso1cdb
#wait
echo -e "\n"
echo -e "\n"
echo -e "**Command lsnrctl status \n"
lsnrctl status sso1cdb
echo -e "\n"
echo -e "\n"
echo -e "**Command netstat -nat | grep 1521\n"
netstat -nat | grep 1551 | grep ESTABLISHED
cd /u03/ORCL/db/19.0.0
. ./ORCL_erpapp.env
# Oracle connection details (replace with your actual values)
DB_USER="apps"
DB_PASSWORD="apps" # Consider using environment variables or secure methods for passwords
ORACLE_SID="ORCL" # e.g., ORCL, XE
# Query the database name from V$DATABASE
HOST_NAME=$(sqlplus -S "${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}" <<EOF
SET PAGESIZE 0 FEEDBACK OFF HEADING OFF VERIFY OFF
SELECT HOST_NAME FROM V\$INSTANCE;
EXIT;
EOF
)
DB_NAME=$(sqlplus -S "${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}" <<EOF
SET PAGESIZE 0 FEEDBACK OFF HEADING OFF VERIFY OFF
SELECT NAME FROM V\$DATABASE;
EXIT;
EOF
)
echo -e "\n"
echo -e "\n"
echo -e "\n"
echo "Host Name: $HOST_NAME"
echo -e "Oracle Database Name: $DB_NAME\n"
echo -e "\n"
echo "============================================================="
echo "== Process : Check Database & Service Status "
echo -e "=============================================================\n"
DB_STATUS=$(echo "SET LINESIZE 1000;
column INSTANCE_NAME FORMAT A15;
column HOST_NAME FORMAT A20;
column STARTUP_TIME FORMAT A20;
SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS, DATABASE_STATUS, EDITION FROM V\$INSTANCE;" | sqlplus -S "${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}")
echo "$DB_STATUS"
# Check if sqlplus is available
if ! command -v sqlplus &> /dev/null
then
echo "Error: sqlplus command not found. Ensure Oracle client is installed and configured in PATH."
exit 1
fi
echo -e "\n"
echo "============================================================="
echo "== Process : Tablespace Status "
echo -e "=============================================================\n"
echo "***** Table Space Status at $(date +"%Y-%m-%d %H:%M:%S") *****"
echo -e "**Command : select tablespace_name, status from dba_tablespaces order by tablespace_name \n"
TBS_STATUS=$(echo "select tablespace_name, status from dba_tablespaces order by tablespace_name;" | sqlplus -S "${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}")
TBD_STATUS=$(echo "SET LINESIZE 1000;
column TableSpaceName FORMAT A20;
select a.TABLESPACE_NAME "TableSpaceName",
round(a.BYTES / 1024 / 1024) "MBAllocated",
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MBUsed",
nvl(round(b.BYTES / 1024 / 1024), 0) "MBFree",
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) "PctUsed",
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "PctFee"
from (select TABLESPACE_NAME, sum(BYTES) BYTES
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES) BYTES
from sys.dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;" | sqlplus -S "${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}")
TBS_COUNT=$(echo "@$HOME/CountTB.sql" | sqlplus -S "${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}")
# Check if the query returned a database name
if [ -z "$DB_NAME" ]; then
echo "Error: Could not retrieve Oracle database name. Check connection details and database status."
else
echo "$TBS_STATUS"
echo -e "$TBD_STATUS\n"
echo -e "\n"
echo "$TBS_COUNT"
fi
# Start the database
#sqlplus apps/apps@ORCL << EOF
#@$ORACLE_HOME\CountTB.sql
#exit;
#EOF
