Shell script to find out super user list in AWS Redshift

 ################################################################################################

# purpose     :script used to collect PROD  Redshift super user list for audit

# Author      : Bala P

# Developed   :19-sep-2022 V1

################################################################################################

#!/usr/bin/bash

export PGHOST=XXXX.redshift.amazonaws.com

export PGPORT='5454'

export PGDATABASE=RS_DB1

export PGUSER=rsdbadmin

DBA=balamani@abc.com

export DBA

export PGPASSWORD='XXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

psql  -f $wdir/super_user_list.sql -o $logdir/super_user_list.sql_output.log

mailx -s "Super user list from Redshift PROD cluster " $DBA  < $logdir/super_user_list.sql_output.log

************************************

 super_user_list.sql

\qecho

select usename,usesuper FROM pg_catalog.pg_user_info where usesuper='true';

check_tbl_ownr_compl.sql

select  schemaname,tablename ,tableowner from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and tableowner not in ('rsdbadmin') and schemaname not like 'pg_%';

 chk_tbl_owner_public.sql

select schemaname,tablename,tableowner  from pg_catalog.pg_tables  where schemaname='public';

************************************

shell script to find out long running session details in AWS Redshift database

 Here is the shell script to monitor the Long running session details in AWS redshift, we can schedule it .

##################################################################################################

### purpose     :script used to collect Redshift long running session details                     #

### Author      : Bala P                                                                          #

### Developed   :10-july-2023 V1                                                                  #

###################################################################################################

##

##!/usr/bin/bash

##

export PGDATABASE=$1

export PGUSER=rsdbadmin

export PGHOST=XXXXXXXXXX.redshift.amazonaws.com

export PGPORT='5439'

export PGPASSWORD='XXXXXXXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

 query_result=$(psql -tA -c "select SPLIT_PART(SUBSTRING(user_name, 5), '@', 1) as user_name, DATE_TRUNC('second',starttime) as start_time,status,pid,duration/ (1000000.0 * 60 * 60) AS exec_time_hoursi,query  from stv_recents where user_name not in ('rdsdb') and duration/(1000000.0 * 60 * 60) > 3;")

#

 if [[ -n "$query_result" ]]; then

recipient="balamani@abc.com"

       subject="Queries with Execution Time > 3 hours @ $PGDATABASE in PROD"

       body="user_name|starttime |state|pid|exec_time_hours, queries  execution time greater than 3 hours PROD:\n\n$query_result"

     echo -e "$body" | mailx -s "$subject" "$recipient"

            fi

shell script for defragmentation in oracle Database Tables

 Here is the shell script to do de-fragmentation activities for a table in Oracle database. 

You can feed the table name and schema name and run this script in back ground 

#!/bin/ksh

export ORACLE_HOME=/oracle/TESTDB5/1924

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=TESTDB5

TMP_LOG=/oracle/scripts/logs/trans_table_redef.log

echo > $TMP_LOG

sqlplus -s /nolog <<EOF >> $TMP_LOG

whenever sqlerror exit 1

conn / as sysdba

SET SERVEROUTPUT ON;

ALTER SESSION FORCE PARALLEL DML PARALLEL 2;

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('<schema_name>','<Table_name>', DBMS_REDEFINITION.CONS_USE_ROWID);

exit

EOF

# Check if SQL execution was successful

if [ $? -eq 0 ]; then

    SUBJECT="Table Redefinition Completed Successfully"

else

    SUBJECT="Table Redefinition Failed"

fi

# Send the log file via email

mailx -s "$SUBJECT" Balamani@abc.com < $TMP_LOG

shell script to build oracle database with easy interactive mode

 Here is the full  script which will get few input and build the oracle database and configure the listener,tnsnames.ora.pfile/spfile and convert the database to archive log 

#########################################################################################################

#purpose       : Script used to build oracle DB,pass requested info                                     #

#Input needed  :DB_NAME,ORACLE_HOME,PORT#,DB CHARSET,sys,system,PDB pws,Redlog size in MB               #

#script will do: create DB,convert archive mode,setup listener& tns and start,add /etc/oratab,BCT       #

#Developed by  :Bala P  3March2025  v2                                                                  #

#########################################################################################################

#!/bin/sh

# Prompt for database name and convert to uppercase

echo "Enter the database name:"

read DB_NAME

DB_NAME=$(echo $DB_NAME | tr '[:lower:]' '[:upper:]')

# Prompt the user for   port number

read -p "Enter the port number: " PORT_NUMBER

# Prompt the user for   Redolog size in MB

read -p "Enter the Redolog size in MB: " RLOG

# Prompt for Oracle DB Home

echo "Enter the Oracle DB Home full Path:"

read  ORACLE_HOME

# Check if sqlplus /oracle_home status

if [ ! -x "$ORACLE_HOME/bin/sqlplus" ]; then

echo " sqlplus not found at $ORACLE_HOME/bin. Please check ORACLE_HOME."

exit 1

else

  echo "Oracle_Home FOUND ,proceeding with next steps"

fi

echo -e "\n \n"

#Prompt for  DB CHARACTER SET

echo "Enter the DB  CHARACTER SET"

read charset

# Prompt for passwords

echo "Enter new password for SYS:"

read -s sysPassword

echo "Enter new password for SYSTEM:"

read -s systemPassword

echo "Enter new password for PDBADMIN:"

read -s pdbAdminPassword

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

# Create necessary directories

OLD_UMASK=`umask`

umask 0027

mkdir -p /oracle/${DB_NAME}

mkdir -p $ORACLE_HOME/dbs

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/adump

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/dpdump

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/pfile

mkdir -p /oracle/${DB_NAME}/audit

mkdir -p /oracle/${DB_NAME}/cfgtoollogs/dbca/${DB_NAME}

mkdir -p /oracle/${DB_NAME}/control1

mkdir -p /oracle/${DB_NAME}/control2

mkdir -p /oracle/${DB_NAME}/control3

mkdir -p /oracle/${DB_NAME}/mirrlogA

mkdir -p /oracle/${DB_NAME}/mirrlogB

mkdir -p /oracle/${DB_NAME}/origlogA

mkdir -p /oracle/${DB_NAME}/origlogB

mkdir -p /oracle/${DB_NAME}/data1

mkdir -p /oracle/${DB_NAME}/SystemTBS

mkdir -p /oracle/${DB_NAME}/UndoTBS

mkdir -p /oracle/${DB_NAME}/tempTBS

mkdir -p /oracle/${DB_NAME}/admin/scripts

umask ${OLD_UMASK}

# Create init<DB_NAME>.ora file

rm -f /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora

cat <<EOL > /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora

${DB_NAME}.__oracle_base='/oracle/${DB_NAME}'#ORACLE_BASE set from environment

${DB_NAME}.__db_cache_size=1451700

${DB_NAME}.__java_pool_size=16686

${DB_NAME}.__large_pool_size=16686

${DB_NAME}.__sga_target=1067918334

${DB_NAME}.__shared_io_pool_size=216920

${DB_NAME}.__shared_pool_size=2552992

${DB_NAME}.__data_transfer_cache_size=0

${DB_NAME}.__streams_pool_size=0

*.audit_file_dest='/oracle/${DB_NAME}/admin/${DB_NAME}/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/${DB_NAME}/control1/control01.ctl','/oracle/${DB_NAME}/control2/control02.ctl','/oracle/${DB_NAME}/control3/control03.ctl'

*.db_block_size=8192

*.db_domain='abc.domain.com'

*.db_files=200

*.db_name='${DB_NAME}'

*.diagnostic_dest='/oracle/${DB_NAME}'

*.filesystemio_options='SETALL'

*.log_archive_dest='/oracle/${DB_NAME}/oraarch/${DB_NAME}arch'

*.log_archive_format='%t_%s_%r.arc'

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=3000

*.parallel_execution_message_size=16384

*.parallel_max_servers=20

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=675m

*.processes=500

*.replication_dependency_tracking=FALSE

*.sessions=1000

*.sga_target=1g

*.undo_management='AUTO'

*.undo_retention=21600

*.undo_tablespace='UNDOTBS'

_disable_directory_link_check=TRUE

CONTROL_MANAGEMENT_PACK_ACCESS = "NONE"

EOL

# Create Oracle password file

rm -f $ORACLE_HOME/dbs/orapw${DB_NAME}

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw${DB_NAME} force=y format=12

# Run SQL scripts for DB creation and configuration

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

echo -e "\n \n creating the DB...."

sqlplus /nolog <<EOF

CONNECT SYS/${sysPassword} AS SYSDBA

conn / as sysdba

SET VERIFY OFF

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDB.log

STARTUP NOMOUNT PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';

CREATE DATABASE "${DB_NAME}"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1000

DATAFILE '/oracle/${DB_NAME}/SystemTBS/system01.dbf' SIZE 2072M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/oracle/${DB_NAME}/SystemTBS/sysaux01.dbf' SIZE 2192M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/${DB_NAME}/tempTBS/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE  '/oracle/${DB_NAME}/UndoTBS/undotbs01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CHARACTER SET "${charset}"

NATIONAL CHARACTER SET AL16UTF16

LOGFILE

GROUP 1(

    '/oracle/${DB_NAME}/origlogA/redo_g1m1.log',

    '/oracle/${DB_NAME}/mirrlogA/redo_g1m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 2 (

    '/oracle/${DB_NAME}/origlogB/redo_g2m1.log',

    '/oracle/${DB_NAME}/mirrlogB/redo_g2m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 3 (

    '/oracle/${DB_NAME}/origlogB/redo_g3m1.log',

    '/oracle/${DB_NAME}/mirrlogB/redo_g3m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 4 (

    '/oracle/${DB_NAME}/origlogA/redo_g4m1.log',

    '/oracle/${DB_NAME}/mirrlogA/redo_g4m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512

USER SYS IDENTIFIED BY "${sysPassword}" USER SYSTEM IDENTIFIED BY "${systemPassword}";

SPOOL OFF

----creating the needed tablespaces--------

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBFiles.log

CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/oracle/${DB_NAME}/data1/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;

ALTER DATABASE DEFAULT TABLESPACE "USERS";

SPOOL OFF

------loading oracle binaries--------------

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBCatalog.log

@$ORACLE_HOME/rdbms/admin/catalog.sql;

@$ORACLE_HOME/rdbms/admin/catproc.sql;

@$ORACLE_HOME/rdbms/admin/catoctk.sql;

@$ORACLE_HOME/rdbms/admin/owminst.plb;

CONNECT SYSTEM/${systemPassword}

@$ORACLE_HOME/sqlplus/admin/pupbld.sql;

CONNECT SYS/${sysPassword} AS SYSDBA

@$ORACLE_HOME/sqlplus/admin/pupdel.sql;

CONNECT SYSTEM/${systemPassword};

SPOOL /oracle/${DB_NAME}/admin/scripts/sqlPlusHelp.log

@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/JServer.log

@$ORACLE_HOME/javavm/install/initjvm.sql;

@$ORACLE_HOME/xdk/admin/initxml.sql;

@$ORACLE_HOME/xdk/admin/xmlja.sql;

@$ORACLE_HOME/rdbms/admin/catjava.sql;

@$ORACLE_HOME/rdbms/admin/catxdbj.sql;

SPOOL OFF

---checked above  this

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/context.log

@$ORACLE_HOME/ctx/admin/catctx.sql Xbkfsdcdf1ggh_123 SYSAUX TEMP LOCK; --no path

ALTER USER CTXSYS ACCOUNT UNLOCK IDENTIFIED BY "CTXSYS";

CONNECT CTXSYS/CTXSYS

@$ORACLE_HOME/ctx/admin/defaults/dr0defin.sql "AMERICAN";

CONNECT SYS/${sysPassword} AS SYSDBA

conn / as sysdba

ALTER USER CTXSYS PASSWORD EXPIRE ACCOUNT LOCK;

@$ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/ordinst.log

@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/interMedia.log

@$ORACLE_HOME/ord/im/admin/iminst.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/cwmlite.log

@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/spatial.log

@$ORACLE_HOME/md/admin/mdinst.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/apex.log APPEND

@$ORACLE_HOME/apex/catapx.sql Xbkfsdcdf1ggh_123 SYSAUX SYSAUX TEMP /i/ NONE;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/postDBCreation.log

host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db ${DB_NAME};

CREATE SPFILE='$ORACLE_HOME/dbs/spfile${DB_NAME}.ora' FROM PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';

SELECT 'utlrp_begin: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

@$ORACLE_HOME/rdbms/admin/utlrp.sql;

SELECT 'utlrp_end: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

SELECT comp_id, status FROM dba_registry;

SHUTDOWN IMMEDIATE;

STARTUP;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/lockAccount.log

BEGIN

FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (

'SYS','SYSTEM') )

LOOP

IF item.AUTHENTICATION_TYPE='PASSWORD' THEN

  DBMS_OUTPUT.PUT_LINE('Locking and Expiring: ' || item.USERNAME);

  EXECUTE IMMEDIATE 'ALTER USER ' ||

         SYS.DBMS_ASSERT.ENQUOTE_NAME(

         SYS.DBMS_ASSERT.SCHEMA_NAME(

         item.USERNAME),FALSE) || ' PASSWORD EXPIRE ACCOUNT LOCK' ;

ELSE

  DBMS_OUTPUT.PUT_LINE('Locking: ' || item.USERNAME);

  EXECUTE IMMEDIATE 'ALTER USER ' ||

         SYS.DBMS_ASSERT.ENQUOTE_NAME(

         SYS.DBMS_ASSERT.SCHEMA_NAME(

         item.USERNAME),FALSE) || ' ACCOUNT LOCK' ;

END IF;

END LOOP;

END;

/

SPOOL OFF

EOF

# Set ORACLE_HOME

export ORACLE_HOME=$ORACLE_HOME

# Get the current hostname

SERVER_NAME=$(hostname)

# Define paths

TNS_PATH="$ORACLE_HOME/network/admin/tnsnames.ora"

LISTENER_PATH="$ORACLE_HOME/network/admin/listener.ora"

TNS_BACKUP_PATH="${TNS_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

LISTENER_BACKUP_PATH="${LISTENER_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

# Backup tnsnames.ora

if [ -f "$TNS_PATH" ]; then

  cp "$TNS_PATH" "$TNS_BACKUP_PATH"

  echo "Backup of tnsnames.ora created at $TNS_BACKUP_PATH"

fi

# Append to tnsnames.ora

cat <<EOF >> "$TNS_PATH"

${DB_NAME}.WORLD=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS =

        (PROTOCOL = TCP)

        (HOST = ${SERVER_NAME})

        (PORT = ${PORT_NUMBER})

      )

    )

    (CONNECT_DATA =

      (SID = ${DB_NAME})

      (GLOBAL_NAME = ${DB_NAME}.WORLD)

    )

  )

#for PROD

RMANCATPRD.WORLD=

  (DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = <catlog_server>)

          (PORT = 1591)

        )

    )

    (CONNECT_DATA =

       (SID = KRMANCATPRD)

       (GLOBAL_NAME = RMANCATPRD.WORLD)

    )

  )

#prod end

EOF

echo "New TNS entry for ${DB_NAME} added to $TNS_PATH"

# Backup listener.ora

if [ -f "$LISTENER_PATH" ]; then

  cp "$LISTENER_PATH" "$LISTENER_BACKUP_PATH"

  echo "Backup of listener.ora created at $LISTENER_BACKUP_PATH"

fi

# Create listener.ora

cat <<EOF > "$LISTENER_PATH"

LISTENER_${DB_NAME} =

  (ADDRESS_LIST =

    (ADDRESS =

      (PROTOCOL = IPC)

      (KEY = ${DB_NAME}.WORLD)

    )

    (ADDRESS =

      (PROTOCOL = IPC)

      (KEY = ${DB_NAME})

    )

    (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = ${SERVER_NAME})

      (PORT = ${PORT_NUMBER})

    )

  )

STARTUP_WAIT_TIME_LISTENER_${DB_NAME} = 0

CONNECT_TIMEOUT_LISTENER_${DB_NAME} = 10

TRACE_LEVEL_LISTENER_${DB_NAME} = OFF

SID_LIST_LISTENER_${DB_NAME} =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ${DB_NAME})

      (ORACLE_HOME = $ORACLE_HOME)

    )

  )

EOF

echo "New listener entry for ${DB_NAME} added to $LISTENER_PATH"

# Define paths for sqlnet.ora

SQLNET_PATH="$ORACLE_HOME/network/admin/sqlnet.ora"

SQLNET_BACKUP_PATH="${SQLNET_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

# Backup sqlnet.ora

if [ -f "$SQLNET_PATH" ]; then

  cp "$SQLNET_PATH" "$SQLNET_BACKUP_PATH"

  echo "Backup of sqlnet.ora created at $SQLNET_BACKUP_PATH"

fi

# Append to sqlnet.ora

cat <<EOF >> "$SQLNET_PATH"

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

NAME.DEFAULT_ZONE = WORLD

SQLNET.EXPIRE_TIME = 0

TCP.NODELAY=YES

EOF

#start the listener

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

lsnrctl start LISTENER_${DB_NAME}

TNSPING_LOG="tnsping_${DB_NAME}.log"

# Run tnsping and log the output

tnsping ${DB_NAME}.WORLD > "$TNSPING_LOG" 2>&1

# Check tnsping result

if grep -q "OK" "$TNSPING_LOG"; then

  echo "TNSPING successful: This is good"

else

  echo "TNSPING failed: Please check the configuration"

fi

# Check logs for errors or warnings

LOG_FILES="/oracle/${DB_NAME}/admin/scripts/*.log"

OUTPUT_FILE="/oracle/${DB_NAME}/admin/scripts/error_check.log"

ERRORS_FOUND=false

> "$OUTPUT_FILE"  # Clear previous output

for file in $LOG_FILES

do

  if grep -iq "ORA-" "$file" ; then

    ERRORS_FOUND=true

    ERR_FILE="${file}_err"

    # Extract relevant lines and save to .log_err file

    grep -iE "ORA-" "$file" > "$ERR_FILE"

    echo "Issues found in: $(basename "$file") $(basename "$ERR_FILE")" >> "$OUTPUT_FILE"

  fi

done

if [ "$ERRORS_FOUND" = false ]; then

  echo "Everything is good with Db creation." > "$OUTPUT_FILE"

fi

#Backup /etc/oratab with timestamp

timestamp=$(date +%Y%m%d_%H%M%S)

cp /etc/oratab /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp

# Append entry to /etc/oratab

echo "${DB_NAME}:${ORACLE_HOME}:Y" >> /etc/oratab

echo "Entry added to /etc/oratab and backup created as /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp"

# Set environment variables arch mode conversion

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

# Check if DB is already in ARCHIVELOG mode

archive_mode=$(sqlplus -s / as sysdba <<'EOF'

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SELECT log_mode FROM v$database;

EXIT

EOF

)

archive_mode=$(echo "$archive_mode" | xargs)  # Trim whitespace

if [ "$archive_mode" == "ARCHIVELOG" ]; then

  echo "Database is already in ARCHIVELOG mode. Skipping conversion."

else

  echo "Converting database to ARCHIVELOG mode..."

  sqlplus / as sysdba <<EOF

  SPOOL convert_db_archive_mode_bct.log

  SHOW PARAMETER spfile;

  CREATE PFILE='$ORACLE_HOME/pfile_before_arch.ora' FROM SPFILE;

  SHUTDOWN IMMEDIATE;

  STARTUP MOUNT;

  ALTER DATABASE ARCHIVELOG;

  ALTER DATABASE OPEN;

  ARCHIVE LOG LIST;

  alter database enable block change tracking using file '/oracle/${DB_NAME}/SystemTBS/${DB_NAME}_block_change.dbf';

  SPOOL OFF

  EXIT

EOF

fi

echo "exporting Db variables final check"

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

EMAIL="BALAmani@abc.com"

# Run SQL to get DB name and open mode

DB_STATUS=$(sqlplus -s / as sysdba <<'EOF'

SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF

SELECT open_mode FROM v$database;

EXIT;

EOF

)

# Check if SQL*Plus ran successfully

if [[ $? -ne 0 ]]; then

    echo "SQL*Plus execution failed."

    exit 1

fi

# Trim whitespace

DB_STATUS=$(echo "$DB_STATUS" | xargs)

# Log the status

echo "Database status returned: $DB_STATUS"

# Check if status is "READ WRITE"

if [[ "$DB_STATUS" == "READ WRITE" ]]; then

    echo "DB creation completed successfully." | mailx -s "DB $DB_NAME build completed, proceed with next steps" "$EMAIL"

else

    echo "Database is not in READ WRITE mode. Current status: $DB_STATUS"

    exit 1

fi

exit 0