Database Technology

Tuesday, December 2, 2025

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"

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"

# 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"

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"

# 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"

# 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"

# 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"

# 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

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"

done

if [ "$ERRORS_FOUND" = false ]; then

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

#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

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

# 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

exit 0

Thursday, July 17, 2025

SAP Oracle Database table migration to new tablespace using brtools

issue:

we have requirement to move the table from tablespace A1 to B1 using brtools.

solution:

The below script and steps will take care the table movement to new tablespace without confirmation from the user --brspace -u / -c force,it will force to continue.

brspace -u / -c force -f tbreorg -s PSAPSR3 -o SAPSR3 -t /BIC/AZD_PUR8200 -n PSAPSR3TEST -p 4

-f tbreorg ----> function name

-s PSAPSR3 ----> source schema tablespace name

-o SAPSR3 ----> source schema tablespace name

-t /BIC/AZD_PUR8200 ----> tables name that needs to be migrated

-n PSAPSR3TEST ----> new tablespace name

-p 4 ---->parallel option

<server_name>:orab1p 67> brspace -u / -c force -f tbreorg -s PSAPSR3 -o SAPSR3 -t /BIC/AZD_PUR8200 -n PSAPSR3TEST -p 4

BR1001I BRSPACE 7.40 (47)

BR1002I Start of BRSPACE processing: sfrnodks.tbr 2025-07-17 11:22:10

BR0484I BRSPACE log file: /oracle/B1P/sapreorg/sfrnodks.tbr

BR0280I BRSPACE time stamp: 2025-07-17 11:22:11

BR1009I Name of database instance: B1P

BR1010I BRSPACE action ID: sfrnodks

BR1011I BRSPACE function ID: tbr

BR1012I BRSPACE function: tbreorg

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRSPACE time stamp: 2025-07-17 11:22:13

BR0813I Schema owners found in database B1P: SAPSR3*

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR0657I Input menu 353 # please enter/check input values

-------------------------------------------------------------------------------

Options for reorganization of tables: SAPSR3./BIC/AZD_PUR8200 (degree 1)

1 * Reorganization action (action) ............ [reorg]

2 - Reorganization mode (mode) ................ [online]

3 - Create DDL statements (ddl) ............... [yes]

4 ~ New destination tablespace (newts) ........ [PSAPSR3TEST]

5 ~ Separate index tablespace (indts) ......... []

6 - Parallel threads (parallel) ............... [4]

7 ~ Table/index parallel degree (degree) ...... []

8 ~ Category of initial extent size (initial) . []

9 ~ Sort by fields of index (sortind) ......... []

10 # Index for IOT conversion (iotind) ......... [FIRST]

11 - Compression action (compress) ............. [none]

12 # LOB compression degree (lobcompr) ......... [medium]

Standard keys: c - cont, b - back, s - stop, r - refr

-------------------------------------------------------------------------------

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR0134I Unattended mode with 'force' active - continuing processing with default reply 'cont'

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR1108I Checking tables for reorganization...

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR1112I Number of tables selected/skipped for reorganization: 1/0

BR0370I Directory /oracle/B1P/sapreorg/sfrnodks created

BR0280I BRSPACE time stamp: 2025-07-17 11:22:15

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2025-07-17 11:22:15

BR1124I Starting 'online' reorganization of table SAPSR3./BIC/AZD_PUR8200 ...

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1105I Table SAPSR3./BIC/AZD_PUR8200 reorganized successfully

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1141I 1 of 1 table processed - 23691300 of 23691300 rows done

BR0204I Percentage done: 100.00%, estimated end time: 11:24

BR0001I **************************************************

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1102I Number of tables reorganized successfully: 1

BR0280I BRSPACE time stamp: 2025-07-17 11:24:37

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbreorg' completed

BR1008I End of BRSPACE processing: sfrnodks.tbr 2025-07-17 11:24:37

BR0280I BRSPACE time stamp: 2025-07-17 11:24:38

BR1005I BRSPACE completed successfully

Tuesday, July 15, 2025

script to list the oracle database ORA- errors from oracle Database views

script to list the oracle database ORA- errors

COLUMN ORIGINATING_TIMESTAMP FORMAT A40

COLUMN message_text FORMAT A100

set linesize 300

set pages 300

SELECT ORIGINATING_TIMESTAMP , message_text

FROM X$DBGALERTEXT

WHERE originating_timestamp > (SYSDATE - 5)

AND message_text LIKE '%ORA-%'

ORDER BY originating_timestamp;

ORIGINATING_TIMESTAMP MESSAGE_TEXT

---------------------------------------- ----------------------------------------------------------------------------------------------------

14-JUL-25 11.25.14.115 AM +01:00 ORA-1100 signalled during: alter database mount...

15-JUL-25 01.52.59.280 PM +01:00 Patch Description: ORA-00800 SOFT EXTERNAL ERROR, ARGUMENTS [SET PRIORITY FAILED], [VKTM] , DISM(1

Script to find out user query running which priority in AWS Redshift Database

Script to find out user query running which priority in AWS Redshift Database:

SELECT w.query, i.userid, w.service_class, w.state,w.query_priority

FROM stv_wlm_query_state w

JOIN stv_inflight i ON w.query = i.query

WHERE w.state = 'Running' and i.userid in ('219','384','105');

Redshift_TEST_DB=# SELECT w.query, i.userid, w.service_class, w.state,w.query_priority

Redshift_TEST_DB-# FROM stv_wlm_query_state w

Redshift_TEST_DB-# JOIN stv_inflight i ON w.query = i.query

Redshift_TEST_DB-# WHERE w.state = 'Running' and i.userid in ('219','384','105');

query | userid | service_class | state | query_priority

-----------+--------+---------------+------------------+----------------------

129306353 | 105 | 100 | Running | High

129302344 | 105 | 100 | Running | High

(2 rows)

script to find out assumerole granted to user in AWS redshift Database.

script to find out assumerole granted to user in AWS redshift.

select username, iam_role, cmd FROM pg_get_iam_role_by_user('TESTUSER@abc.domain.com') res_iam_role(username text, iam_role text, cmd text);

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle 19.24.0.0

Issue:

we had ORA-800 popping up after the DB creation and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.

Error:

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle document and MOS notes] [] [] [] [] [] [] [] []

[00]: dbgexExplicitEndInc [diag_dde]

[01]: dbgeEndDDEInvocationImpl [diag_dde]

[02]: ksbsethighpri_int [background_proc]<-- Signaling

[03]: ksbrdp [background_proc]

[04]: opirip [OPI]

solution :

we had ORA-800 popping up after the DB creation and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.Then we applied the below Patch and issue resolved.

p34672698_1924000DBRU_Linux-x86-64.zip

no more ORA-800 on the alert log.

Step -by Step Applying the Patch:

**********************bug patch apply on standalone server************************

(I) Prerequisites

--------------------

Before you install or deinstall the patch, ensure that you meet the following requirements:

1. Ensure that the Oracle home on which you are installing the patch or from which you are rolling back the patch is Oracle Database 19 Release 19.24.0.0.240716DBRU.

2. Ensure that 19 Release 19.24.0.0.240716DBRU Patch Set Update (PSU) 36582781 is already applied on the Oracle Database.

3. Oracle recommends you to use the latest version available for 19 Release 19.24.0.0.240716DBRU. If you do not have OPatch 19 Release 19.24.0.0.240716DBRU, then download it from patch# 6880880 for 19.24.0.0.240716DBRU release.

4. Ensure environment variable ORACLE_HOME is set correctly.

5. Ensure that the $PATH definition has the following executables: make, ar, ld and nm. The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin.

6. Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.

% $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

Note:

- If this command succeeds, it lists the Oracle components that are installed in the home.

- Save the output so you have the status prior to the patch apply.

- If this command fails, contact Oracle Support Services for assistance.

7. (Only for Installation) Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>. Extract the contents of the patch ZIP file to the location (PATCH_TOP_DIR) you have created above. To do so, run the following command:

$ unzip -d <PATCH_TOP_DIR> p34672698_1924000DBRU_Linux-x86-64.zip

8. (Only for Installation) Determine whether any currently installed interim patches conflict with this patch 34672698 as shown below:

$ cd <PATCH_TOP_DIR>/34672698

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

The report will indicate the patches that conflict with this patch and the patches for which the current 34672698 is a superset.

Note:

Upon execution of the Opatch command, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME. OPatch categorizes conflicts into the following types:

- Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply - In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

- Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.

(II) Installation

-----------------

To install the patch, follow these steps:

As root user, change oradism permissions

Pre-install //these make the existing oradism owned by grid home owner and replaceable

a.ls -ld $GI_HOME/bin/oradism

i.-rwsr-x--- 1 root dba <GI_HOME>/bin/oradism

2. Apply Patch to DB home(s) as DB home owner :

Set your current directory to the directory where the patch is located

and then run the OPatch utility by entering the following commands:

As root user, change oradism permissions:

$ cd <PATCH_TOP_DIR>/34672698

Pre-install //these make the existing oradism owned by "oracle" user and replaceable

a.ls -ld $ORACLE_HOME/bin/oradism

i.-rwsr-x--- 1 root dba <ORACLE_HOME>/bin/oradism

b.change ownership to the DB home owner (i.e oracle)

i.chown oracle $ORACLE_HOME/bin/oradism

ii.chmod 0750 $ORACLE_HOME/bin/oradism

c.ls -ld $ORACLE_HOME/bin/oradism

As DB home owner, apply the patch:

make sure NO DB & listener process running

---> /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

<ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <PATCH_TOP_DIR>/34672698

3. Verify whether the patch has been successfully installed by running the following command:

As the Oracle Database home owner, run the following command:

$ opatch lsinventory -oh <ORACLE_HOME>

3b. Post-install

These make the new oradism binary owned by root with setuid, and executable by "oracle" user

3b.2 - For DB home(s) as root user

a.ls -ld $ORACLE_HOME/bin/oradism

b.change owner to 'root' and enable setuid bit

i.chown root $ORACLE_HOME/bin/oradism

ii.chmod 4750 $ORACLE_HOME/bin/oradism

c.ls -ld $ORACLE_HOME/bin/oradism

----when I delete the patch ,it generate the error : when I apply the patch ,it stops the error

Starting background process VKTM

2025-07-03T11:25:14.278411+01:00

Errors in file /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/trace/TESTDB78_vktm_276905.trc (incident=28045):

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []

Incident details in: /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/incident/incdir_28045/TESTDB78_vktm_276905_i28045.trc

2025-07-03T11:25:14.279793+01:00

----Stop the DB services

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

----pre-checks

<server_name>:/oracle/TESTDB78/19.0.0/34672698> /oracle/TESTDB78/19.0.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.46

PREREQ session

Oracle Home : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

from : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.46

OUI version : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-15_13-28-23PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Version 19.24.0.0.0

[oracle@<server_name> 34672698]$ ls -ld $ORACLE_HOME/bin/oradism

-rwxr-x--- 1 oracle dba 147848 Jul 3 11:14 /oracle/TESTDB78/19.0.0/bin/oradism

[oracle@<server_name> 34672698]$ /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

Oracle Interim Patch Installer version 12.2.0.1.46

Oracle Home : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

from : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.46

OUI version : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-42-39AM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------

Start OOP by Prereq process.

Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.46

Oracle Home : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

from : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.46

OUI version : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 34672698

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/oracle/TESTDB78/19.0.0')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '34672698' to OH '/oracle/TESTDB78/19.0.0'

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 34672698 successfully applied.

Log file location: /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

OPatch succeeded.

Thursday, March 27, 2025

script to check if the schema part of the data share or not in aws Redshift database

script to check if the schema part of the data share or not in aws Redshift database:

SELECT * FROM SVV_DATASHARE_OBJECTS where object_type='schema' and object_name='<schema_name>';

----------+-----------------+-----------+-----------+----------------+--------------------