Database mode check

 Standard Operating Procedure (SOP) for Database Read-Only Mode Validation Automation



---


1. Purpose


This SOP outlines the automated process for validating the OPEN_MODE of multiple Oracle databases using a predefined list. The script identifies databases that are not in READ ONLY mode and sends an email notification for further review.



---


2. Scope


Applies to Oracle databases running across multiple hosts.


Uses a common tnsnames.ora file for connectivity.


Executes SQL queries to check the database mode and uptime in that mode.


Notifies the DBA team if any database is not in READ ONLY mode.




---


3. Prerequisites


Ensure the Oracle environment variables are set correctly:


export TNS_ADMIN=/auto/your/tns/location

export ORACLE_HOME=/auto/your/oracle/home

export PATH=$ORACLE_HOME/bin:$PATH


A valid tnsnames.ora file containing the TNS entries for all databases.


A list of target databases stored in a file (db_list.txt).


A static username and password for database authentication.




---


4. Script Execution Workflow


Step 1: Read Database List


The script reads each database name from db_list.txt.



Step 2: Connect to Each Database


It establishes a SQL*Plus session using the format:


sqlplus -s "username/password@DB_NAME"


Retrieves the OPEN_MODE and calculates the number of days in the current mode.



Step 3: Validate Database Mode


The script checks if the database is in READ ONLY mode.


If not, it logs the entry and prepares an email notification.



Step 4: Generate Reports


Saves results in /tmp/database_mode.log.


Stores databases not in READ ONLY mode in /tmp/email_body.txt.



Step 5: Send Email Notification


If any database is not in READ ONLY mode, an email alert is sent to the DBA team.




---


5. Key Features


✅ Automation


Fully automated execution with no manual intervention.



✅ Multi-Database Validation


Works across multiple hosts using TNS-based connectivity.



✅ Error Handling


Captures SQL errors and skips databases that fail to connect.


Logs failures for troubleshooting.



✅ Logging & Reporting


Detailed logs are stored for audit and review.


Generates a formatted report with database mode details.



✅ Email Alerts


Sends a summary email listing databases that are not in READ ONLY mode.




---


6. Example Output


Log File (/tmp/database_mode.log)


Processing database: testdb1

testdb1 READ WRITE 45 days

Processing database: testdb2

testdb2 READ ONLY 30 days

Processing database: testdb3

testdb3 MOUNTED 2 days


Email Notification (/tmp/email_body.txt)


Database: testdb1 is in mode: READ WRITE (Not READ ONLY)

Database: testdb3 is in mode: MOUNTED (Not READ ONLY)



---


7. Troubleshooting



---


8. Execution Steps


1. Ensure Oracle environment variables are set:


export TNS_ADMIN=/auto/your/tns/location

export ORACLE_HOME=/auto/your/oracle/home

export PATH=$ORACLE_HOME/bin:$PATH



2. Create or update db_list.txt with target databases.



3. Run the script:


./database_mode_check.sh



4. Check logs for results:


cat /tmp/database_mode.log



5. If email is expected, verify mail command execution:


mail -s "Databases Not in READ ONLY Mode" your_email@example.com < /tmp/email_body.txt





---


9. Conclusion


This automated script efficiently monitors Oracle databases for READ ONLY mode compliance, logs the results, and notifies DBAs for immediate action if any database is in an unexpected state.


Would you like any add

itional enhancements, such as integration with monitoring tools or scheduled execution using cron jobs?


Comments

Post a Comment

Popular posts from this blog

Database growth

#!/bin/bash # Log file LOGFILE="db_growth_check.log" echo "Checking yearly database growth on all running Oracle databases..." > $LOGFILE echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE echo "---------------------------------" >> $LOGFILE # Find running Oracle instances (excluding ASM and APX) ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID do     if [[ -n "$ORACLE_SID" ]]; then         echo "Processing database: $ORACLE_SID"         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check yearly database growth         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF; SET FEEDBACK OFF; SELECT '$ORACLE_SID' AS DATABASE_NAME,  ...
Read more

P2

#!/bin/bash # Ensure ORACLE_SID is passed as an argument if [[ -z "$1" ]]; then     echo "Usage: $0 <ORACLE_SID>"     exit 1 fi export ORACLE_SID=$1 export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / as sysdba" # Standard password (static) STANDARD_PWD="xyxyxyxyxytsts" # Fetch cloakware password dynamically NEWPSWD=$(/path/to/pwEcho.exe $ORACLE_SID WALLET) # Function to execute SQL and get database info get_db_info() {     SQL_FILE="/tmp/sql_output.txt"     $SQLPLUS <<EOF > $SQL_FILE SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 500 TRIMSPOOL ON WHENEVER SQLERROR EXIT SQL.SQLCODE SELECT name FROM v\$database; SELECT status FROM v\$encryption_wallet; SELECT NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') FROM dual; SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' E...
Read more

SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database] username=your_username password=your_password database=your_database [files] csv_file=/path/to/your/file.csv log_file=/path/to/your/log_file.log inserted_rows_file=/path/to/your/inserted_rows.log updated_rows_file=/path/to/your/updated_rows.log ********** #!/bin/bash # Load configuration source config.cfg # Variables CSV_FILE=$csv_file USERNAME=$username PASSWORD=$password DATABASE=$database MASTER_TABLE="master_table" CHILD_TABLE="child_table" LOG_FILE=$log_file INSERTED_ROWS_FILE=$inserted_rows_file UPDATED_ROWS_FILE=$updated_rows_file # Logging function log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE } log "Starting data load process." # SQL Loader Control File for child table CONTROL_FILE="control_file.ctl" # Create control file for child table cat ...
Read more