Posts

Showing posts from February, 2025

Db open mode

 #!/bin/bash LOGFILE="/tmp/database_mode.log" EMAIL_RECIPIENT="your_email@example.com" SUBJECT="Databases Not in READ ONLY Mode" EMAIL_BODY="/tmp/email_body.txt" # Clear log files > $LOGFILE > $EMAIL_BODY # 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" | tee -a $LOGFILE         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check OPEN_MODE and days in that mode         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF; SET FEEDBACK OFF; SET PAGESIZE 0; SELECT '$ORACLE_SID' AS DATABASE_NAME, OPEN_MOD...
Post a Comment
Read more

Wallet final

#!/bin/bash # ------------------------------------------------------------------------------------- # Script Name: wallet_validation.sh # Purpose: Validate Oracle TDE wallet configuration for a given DB instance. # - Checks if the Oracle SID is listed in oratab. # - Checks if the DB is running (via SMON process). # - Retrieves DB version and wallet information. # - Backs up the wallet if the wallet path contains the DB name. # - Validates the wallet password (tries cloakware first, then standard). # - Generates a final report in a single padded line. # # Usage: ./wallet_validation.sh <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION> # # Manager Highlights: # - Completed: Instance verification from /etc/oratab. # - Completed: DB running check using SMON process (ps -ef). # - Completed: Retrieval of DB version and wallet details via SQLPlus. # - Completed: Wallet backup if DB name is pr...
Post a Comment
Read more

Wallet output alignment

 gen_report_header() {     # Check if the header already exists in the report file (look for a line starting with "DB_NAME")     if ! grep -q "^DB_NAME" "$REPORT_FILE"; then         # Print a formatted header line with fixed-width columns.         # %-8s : DB_NAME (8 characters, left aligned)         # %-10s : Version (10 characters)         # %-12s : WRL_STATUS (12 characters)         # %-10s : WL_TYPE (10 characters)         # %-8s : WRL_PRE (8 characters)         # %-8s : TBS (8 characters)         # %-10s : WL_BKUP (10 characters)         # %-8s : CLK_PWD (8 characters)         # %-10s : PWD_MATCH (10 characters)         # %-20s : WRL_PATH (20 characters)         # %-8s : RUN_DT (8 characters) — you can adjust the ord...
Post a Comment
Read more

Wallet 8

 #!/bin/bash # Check if correct arguments are passed if [[ $# -ne 3 ]]; then     echo "Usage: $0 <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>"     exit 1 fi # Accepting input arguments export ORACLE_SID=$1 export DEFAULT_LOCATION=$2 export LOG_LOCATION=$3 # Setting Oracle environment variables 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) [[ -z "$NEWPSWD" ]] && NEWPSWD="NA" # Create necessary directories RUN_DATE=$(date '+%Y%m%d') mkdir -p "$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID" mkdir -p "$LOG_LOCATION/encryption/wallet_backup/logs" LOG_FILE="$LOG_LOCATION/encryption/wallet_backup/logs/wallet_validate.log" REPORT_FILE="$DEFAUL...
Post a Comment
Read more

Wallet:7

#!/bin/bash # Check if correct arguments are passed if [[ $# -ne 3 ]]; then     echo "Usage: $0 <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>"     exit 1 fi # Accepting input arguments export ORACLE_SID=$1 export DEFAULT_LOCATION=$2 export LOG_LOCATION=$3 # Setting Oracle environment variables 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) [[ -z "$NEWPSWD" ]] && NEWPSWD="NA" # Create necessary directories RUN_DATE=$(date '+%Y%m%d') mkdir -p "$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID" mkdir -p "$LOG_LOCATION/encryption/wallet_backup/logs" LOG_FILE="$LOG_LOCATION/encryption/wallet_backup/logs/wallet_validate.log" REPORT_FILE="$DEFAULT...
Post a Comment
Read more

Wallet v6

 #!/bin/bash # Ensure ORACLE_SID and DEFAULT_LOCATION are passed as arguments if [[ -z "$1" || -z "$2" ]]; then     echo "Usage: $0 <ORACLE_SID> <DEFAULT_LOCATION>"     exit 1 fi export ORACLE_SID=$1 export DEFAULT_LOCATION=$2 export LOG_LOCATION="$DEFAULT_LOCATION/encryption/wallet_backup/logs" export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / as sysdba" # Ensure required directories exist mkdir -p "$LOG_LOCATION" # Standard password (static) STANDARD_PWD="xyxyxyxyxytsts" # Fetch cloakware password dynamically NEWPSWD=$(/path/to/pwEcho.exe "$ORACLE_SID" WALLET) [[ -z "$NEWPSWD" ]] && NEWPSWD="NA" # Function to fetch DB details get_db_info() {     $SQLPLUS <<EOF SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 200 SELECT      name || '|' || status || '|' ||      NVL((SEL...
Post a Comment
Read more

Wallet v5

#!/bin/bash # Check if correct arguments are passed if [[ $# -ne 3 ]]; then     echo "Usage: $0 <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>"     exit 1 fi # Accepting input arguments export ORACLE_SID=$1 export DEFAULT_LOCATION=$2 export LOG_LOCATION=$3 # Setting Oracle environment variables 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) [[ -z "$NEWPSWD" ]] && NEWPSWD="NA" # Create necessary directories RUN_DATE=$(date '+%Y%m%d') mkdir -p "$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID" mkdir -p "$LOG_LOCATION/encryption/wallet_backup/logs" LOG_FILE="$LOG_LOCATION/encryption/wallet_backup/logs/wallet_validate.log" REPORT_FILE="$DEFAULT...
Post a Comment
Read more

Wallet V4

 #!/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 and Cloakware passwords STANDARD_PWD="xyxyxyxyxytsts" NEWPSWD=$(/path/to/pwEcho.exe "$ORACLE_SID" WALLET) # Fetch cloakware password dynamically # If Cloakware password is missing, set it to empty if [[ "$NEWPSWD" == "NA" ]]; then     echo "Cloakware password not found."     NEWPSWD="" fi # Function to fetch database and wallet information get_db_info() {     $SQLPLUS <<EOF SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100 SELECT      name || '|' ||      status || '|' ||      NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet           WHERE inst_i...
Post a Comment
Read more

Wallet V3

 #!/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 and Cloakware passwords STANDARD_PWD="xyxyxyxyxytsts" NEWPSWD=$(/path/to/pwEcho.exe $ORACLE_SID WALLET) # Fetch cloakware password dynamically # Function to fetch database and wallet information get_db_info() {     $SQLPLUS <<EOF SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100 SELECT      name || '|' ||      status || '|' ||      NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet           WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') || '|' ||     (SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END       FROM...
Post a Comment
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...
Post a Comment
Read more

Wallet v1

 #!/bin/bash echo "===== TDE Wallet Check - Version 1 =====" # ===== PHASE 1: Validate Input and Set Environment ===== echo "PHASE 1: Setting up Oracle Environment" if [[ -z "$1" ]]; then     echo "ERROR: ORACLE_SID is required!"     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" echo "ORACLE_SID set to: $ORACLE_SID" echo "ORACLE_HOME: $ORACLE_HOME" # ===== PHASE 2: Fetch Database Information ===== echo "PHASE 2: Fetching database and wallet details" get_db_info() {     $SQLPLUS <<EOF SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100 SELECT      name || '|' ||      status || '|' ||      NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet           WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$insta...
Post a Comment
Read more

Wallet v0

#!/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 return multiple values in a single block get_db_info() {     $SQLPLUS <<EOF SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100 SELECT      name || '|' ||      status || '|' ||      NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet           WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') || '|' ||     (SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END      ...
Post a Comment
Read more

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,  ...
Post a Comment
Read more

SQL compare object

 SELECT owner, object_type, COUNT(*) AS object_count FROM dba_objects WHERE owner NOT IN ('SYS', 'SYSTEM', 'ORDDATA', 'APEX_050000', 'CTXSYS', 'XDB', 'OUTLN', 'DBSNMP', 'ORDDATA', 'MDSYS', 'OLAPSYS') -- Exclude system schemas GROUP BY owner, object_type ORDER BY owner, object_type;
Post a Comment
Read more

Apex installed status

#!/bin/bash # Log file LOGFILE="apex_check.log" echo "Checking APEX installation on all running Oracle databases..." > $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" | tee -a $LOGFILE         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check APEX installation         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET PAGES 1000; SET HEADING OFF; SET FEEDBACK OFF; COL comp_id FOR A12; COL comp_name FOR A35; COL version FOR A12; COL status FOR A12; SELECT comp_id, comp_name, version, modified, status  FR...
Post a Comment
Read more

Email alert sql

 #!/bin/bash # Oracle Environment Variables export ORACLE_HOME=/path/to/oracle_home export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=your_db_sid # Database Credentials DB_USER="your_username" DB_PASS="your_password" DB_SVC="your_service_name" # SQL Query SQL_QUERY="SELECT * FROM your_table WHERE rownum <= 10;" # Output File OUTPUT_FILE="/tmp/sql_output.txt" # Execute SQL Query and Format Output sqlplus -s "$DB_USER/$DB_PASS@$DB_SVC" <<EOF > $OUTPUT_FILE SET PAGESIZE 100 SET LINESIZE 200 SET FEEDBACK OFF SET HEADING ON SET ECHO OFF SET COLSEP '|' $SQL_QUERY; EXIT; EOF # Email Configuration EMAIL_TO="your_email@example.com" SUBJECT="Oracle SQL Query Output" # Send Email mail -s "$SUBJECT" "$EMAIL_TO" < $OUTPUT_FILE
Post a Comment
Read more