Soumya's Database Blog

October 11, 2023

batch script to send email notification once if any service goes down and sends mail once service is up

Hi Everyone,

Recently I came across an issue where oracle service on windows was getting stopped due to some reason. Since customer was not using any monitoring tool, it was very hard to track when the service was going down.

To mitigate the problem , I wrote a batch script which will monitor oracleservice and listener service on windows . The script will send a mail once notifying service has gone down and once service comes back it will send another notification on email with service is running message.

Save the following script in .bat format and schedule it as per your requirement in task scheduler.

@echo off

setlocal enabledelayedexpansion

set my_date=%date%

set my_time=%TIME%

set my_hours=%my_time:~0,2%

set my_minutes=%my_time:~3,2%

set my_seconds=%my_time:~6,2%

rem Configure email settings

set TO_EMAIL=soumya.das@email.com

set FROM_EMAIL=noreply@alerts.com

set SMTP_SERVER=smtp.gmail.com

set SMTP_PORT=587

set SMTP_USER=noreply@alerts.com

set SMTP_PASSWORD=Password

set SUBJECT="Service Monitor Alert"

rem List of services to monitor (add more as needed)

set "SERVICES=OracleServiceORCL OracleOraDB19Home1TNSListener"

rem Create a temporary file to store service status

set STATUS_FILE=status.txt

rem Loop through the services and check their status using PowerShell

for %%s in (%SERVICES%) do (

sc query "%%s" | find "STATE" | find "RUNNING" >nul

if errorlevel 1 (

echo %%s service is not running.

if not exist !STATUS_FILE! (

echo Sending email notification.

powershell -command "Send-MailMessage -From '%FROM_EMAIL%' -To '%TO_EMAIL%' -Subject '%SUBJECT%' -Body 'The %%s service is not running.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))"

rem Create the status file to indicate that an alert has been sent

echo DOWN > !STATUS_FILE!

)

) else (

echo %%s service is running.

if exist !STATUS_FILE! (

echo Sending email notification.

powershell -command "Send-MailMessage -From '%FROM_EMAIL%' -To '%TO_EMAIL%' -Subject 'Service Monitor Alert' -Body 'The %%s service is now running.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))"

rem Delete the status file to indicate that the service is running

del !STATUS_FILE!

)

endlocal

April 24, 2023

Shell script to convert physical standby database into snapshot standby and vice versa

Today I will show a scenario where a customer had a requirement to refresh their snapshot standby database. The setup was 1 primary database and 2 physical standby databases among which one standby database was always kept in physical standby mode but the other one was used by the developer and a daily refresh was the requirement.

Since they wanted to convert snapshot standby into physical standby for sync and later convert it back to snapshot standby I prepared two shell script to perform the entire activity.

· Shell script to convert physical standby database into snapshot standby database

#!/bin/bash

# Set environment variables

export ORACLE_HOME=/u01/oracle/19.3.0

export ORACLE_SID=PROD

export PATH=$ORACLE_HOME/bin:$PATH

# Define database names

PRIMARY_DB_NAME=PROD

STANDBY_DB_NAME=SBDB

# Define log file path

rm $LOG_FILE

LOG_FILE=/u01/snapshot_convert_logfile.log

# Define maximum number of retries

MAX_RETRIES=2

# Function to log messages to log file

log() {

echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE

}

# Function to perform conversion

perform_conversion() {

log "Performing conversion of $STANDBY_DB_NAME to snapshot standby..."

dgmgrl <<EOF

connect /

convert database $STANDBY_DB_NAME to snapshot standby;

EOF

} >> $LOG_FILE

# Retry loop

retries=0

while [ $retries -lt $MAX_RETRIES ]; do

perform_conversion

if grep -q "ORA-" $LOG_FILE; then

log "Error occurred during conversion. Retrying..."

((retries++))

sleep 5

else

log "Conversion completed successfully. Standby database is now a snapshot standby."

break

done

# Check for maximum retries

if [ $retries -eq $MAX_RETRIES ]; then

log "Maximum retries reached. Conversion failed. Please check the log for details."

· Shell script to convert snapshot standby database into physical standby database

#!/bin/bash

# Set environment variables

export ORACLE_HOME=/u01/oracle/19.3.0

export ORACLE_SID=PROD

export PATH=$ORACLE_HOME/bin:$PATH

# Define database names

PRIMARY_DB_NAME=PROD

STANDBY_DB_NAME=SBDB

# Define log file path

rm $LOG_FILE

LOG_FILE=/u01/physical_convert_logfile.log

# Define maximum number of retries

MAX_RETRIES=2

# Function to log messages to log file

log() {

echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE

}

# Function to perform conversion

perform_conversion() {

log "Performing conversion of $STANDBY_DB_NAME to physical standby..."

dgmgrl sys/Prodpassword@prod<<EOF

convert database $STANDBY_DB_NAME to physical standby;

EOF

} >> $LOG_FILE

# Retry loop

retries=0

while [ $retries -lt $MAX_RETRIES ]; do

perform_conversion

if grep -q "ORA-" $LOG_FILE; then

log "Error occurred during conversion. Retrying..."

((retries++))

sleep 5

else

log "Conversion completed successfully. Standby database is now a physical standby."

break

done

# Check for maximum retries

if [ $retries -eq $MAX_RETRIES ]; then

log "Maximum retries reached. Conversion failed. Please check the log for details."

Once the snapshot standby is converted to physical standby, give it time to synchronize with primary database.

April 17, 2023

How to purge dba_recyclebin older than 30 days in oracle database 19c

In the following post I will show you how to purge dba_recyclebin older than X number of days.

For e.g I want to purge recyclebin data older than 30 days

Oracle recommends not to use delete statement on dba_recyclebin as this doesn’t allow to delete data older than 7 days

SQL> Delete from dba_recylebin where droptime <sysdate -30;

ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table

Rather run the following script to generate a select sql and execute it to purge dba_recyclebin

SQL>set head off

SQL>spool purge.sql

SQL> select 'purge table '||owner||'."'||OBJECT_NAME||'";'

from dba_recyclebin where type='TABLE'

and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

SQL>spool off;

This will create a sql with output like below

purge table JHO."BIN$xXn88xp3GejgU/GEF6wbcA==$0";

purge table JHO."BIN$xwxOc+5mLK7gU/GEF6yizA==$0";

purge table JHO."BIN$w4MOItowfC/gU/GEF6xrHA==$0";

purge table JHO."BIN$x4UA4M5zTZTgU/OEF6xB8w==$0";

Execute purge.sql file to purge all the tables for older than 30 days in recylebin

SQL>@purge.sql

Table purged.

…

April 10, 2023

Handy Oracle Data guard Broker Commands Part-II

Following command will validate the configuration of the specified database.

DGMGRL> validate database 'db_name';

DGMGRL> validate database proddr;

Database Role: Physical standby database

Primary Database: prod

Ready for Switchover: Yes

Ready for Failover: Yes (Primary Running)

Flashback Database Status:

prod : On

proddr: Off

Managed by Clusterware:

prod : NO

proddr: NO

Validating static connect identifier for the primary database prod...

The static connect identifier allows for a connection to database "prod".

DGMGRL>

Following command will display the current Fast-Start Failover configuration.

DGMGRL> show fast_start failover

Fast-Start Failover: Disabled

Protection Mode: MaxPerformance

Lag Limit: 30 seconds

Threshold: 30 seconds

Active Target: (none)

Potential Targets: (none)

Observer: (none)

Shutdown Primary: TRUE

Auto-reinstate: TRUE

Observer Reconnect: (none)

Observer Override: FALSE

Configurable Failover Conditions

Health Conditions:

Corrupted Controlfile YES

Corrupted Dictionary YES

Inaccessible Logfile NO

Stuck Archiver NO

Datafile Write Errors YES

Oracle Error Conditions:

(none)

The following command is used to convert database to snapshot standby.

DGMGRL> convert database 'db_name' to snapshot standby;

DGMGRL> convert database sbdb to Snapshot standby;

Converting database "sbdb" to a Snapshot Standby database, please wait...

Database "sbdb" converted successfully

The following command is used to convert database to physical standby.

DGMGRL> convert database 'db_name' to physical standby;

DGMGRL> convert database sbdb to Physical standby;

Converting database "sbdb" to a Physical Standby database, please wait...

Operation requires shut down of instance "PROD" on database "sbdb"

Shutting down instance "PROD"...

Connected to "SBDB"

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "PROD" on database "sbdb"

Starting instance "PROD"...

Connected to an idle instance.

ORACLE instance started.

Connected to "SBDB"

Database mounted.

Connected to "SBDB"

Database "sbdb" converted successfully

The following command validates static connect identifier for given db_name

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR SBDB;

Oracle Clusterware is not configured on database "sbdb".

Connecting to database "sbdb" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=az-psdb1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...

Succeeded.

The static connect identifier allows for a connection to database "sbdb".

DGMGRL>

Execute sql statement from DGMGRL prompt. Select command doesn’t work from this prompt.

DGMGRL> sql "alter system switch logfile"

Succeeded.

DGMGRL>

April 3, 2023

Shell script to purge diagnostic files using ADRCI utility

Using following shell script we can easily purge diagnostic files such as trace, alert, cdump etc.

$ vi /home/oracle/adrci_purge.sh

#!/usr/bin/ksh

# Description

# Shell script to purge ADR contents using ADRCI utility.

###

LOCKFILE=/tmp/adrci_purge_.lck

###

######

# Start Of Functions

# tidyup . common fuction called if error has occured

tidyup () {

rm -f ${LOCKFILE}

echo "ERROR: Purge aborted at `date` with exit code ${ERR}"

exit ${ERR}

}

######

# End Of Functions

### Main Program

# Check user is oracle

USERID=`/usr/bin/id -u -nr`

if [ $? -ne 0 ]

then

echo "ERROR: unable to determine uid"

exit 99

#if [ "${USERID}" != "oracle" ]

#then

# echo "ERROR: This script must be run as oracle"

# exit 98

#fi

echo "INFO: Purge started at `date`"

# Check if lockfile exists

if [ -f ${LOCKFILE} ]

then

echo "ERROR: Lock file already exists"

echo " Purge already active or incorrectly terminated"

echo " If you are sure tidy isn.t active, please remove "

echo " ${LOCKFILE}"

#rm -f ${LOCKFILE}

exit 97

# Create lock file

touch ${LOCKFILE} 2>/dev/null

if [ $? -ne 0 ]

then

echo "ERROR: Unable to create lock file"

exit 96

# Purge ADR contents

echo "INFO: adrci purge started at `date`"

ALL_DATABASES=`cat /etc/oratab|grep -v "^#"| grep -Eo '^[^ ]+' |cut -f1 -d: -s`

for DB in $ALL_DATABASES

unset TWO_TASK

export ORACLE_SID=$DB

export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`

export PATH=$ORACLE_HOME/bin:$PATH

echo "---> Database $ORACLE_SID, using home $ORACLE_HOME"

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;show homes"|grep -v : | while read file_line

echo "INFO: adrci purging diagnostic destination " $file_line

echo "INFO: purging ALERT older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type ALERT"

echo "INFO: purging INCIDENT older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type INCIDENT"

echo "INFO: purging TRACE older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type TRACE"

echo "INFO: purging CDUMP older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type CDUMP"

echo "INFO: purging HM older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type HM"

echo ""

done

# All completed

rm -f ${LOCKFILE}

echo "SUCC: Purge completed successfully at `date`"

exit 0

# crontab -e

0 1 * * * /orabackup/scripts/adrci_purge.sh

March 27, 2023

How to execute sql tuning advisor for SQL ID in 19c

Description: - SQL Tuning Advisor is a program provided by sql that comes along with oracle installation. This advisor can be executed against a sql id and it generates a report with recommendations which generally includes

· Collection of object statistics

· Creation of indexes

· Rewriting SQL statements

· Creation of SQL profiles

· Creation of SQL plan baselines

The objective to use this program is to avoid tedious manual tuning which DBA’s generally do to fix a bad query.

Note:-As mentioned earlier, this advisor comes along with default oracle installation but It requires Diagnostic and Tuning packs to use the SQL Tuning Advisor.

Let me demonstrate how to execute SQL tuning advisor against a SQL id

Step 1.

To run the advisor we need to create tuning task ,

But before that , we need to have the sql id against which we would run the advisor.

For e.g. let us assume the sql id is – 8d5cry63fwerg65

SET serveroutput ON

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '&&my_sql_id',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 120000,

task_name => 'sql_tuning_task_&&my_sql_id',

description => 'Tuning task for statement &&my_sql_id.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

Enter value for my_sql_id: 8d5cry63fwerg65

Step 2.

Check the task status.

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

TASK_NAME STATUS

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

sql_tuning_task_8d5cry63fwerg65 INITIAL

Step 3.

Execute the sql tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

Step 4.

Check the task status again

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

TASK_NAME STATUS

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

sql_tuning_task_8d5cry63fwerg65 COMPLETED

Step 5.

Review the recommendations by SQL Tuning Advisor

SET LINES 222

SET pages 30000

SET long 999999

SET longc 99999999

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

Step 5.

Implement the Recommendations only if you find the recommendations are satisfactory.

execute dbms_sqltune.accept_sql_profile(task_name => ‘sql_tuning_task_8d5cry63fwerg65', task_owner => 'SYS', replace => TRUE);

Stpe 6.

To drop the tuning task

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');

END;

Step 7.

Verify the task status

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

March 20, 2023

Unexpected error occurred during authentication in ODA

Issue:- oda-admin credential is not working

So, few days ago while trying to login ODA management url using its credential, it gave me following message

Solution:-

This issue happens when there is some problem with the credential of the user or maybe the password is expired.

To reset the password,

[root@server1 ~]# /opt/oracle/dcs/bin/odacli-adm set-credential --username oda-admin

Enter user password:

Retype user password:

More info:
The password length (minimum:9 maximum:30) characters
The password should start with an alpha character (a-z, A-Z)
The password should contain ALL of the following:

a) At least two uppercase letters
b) At least two lowercase letters
c) At least two numbers
d) At least two special characters, valid characters are # _ -

Once, password is reset try to login the console. It should work.