directories

 SELECT 

    DIRECTORY_PATH, 

    LISTAGG(DIRECTORY_NAME, ', ') WITHIN GROUP (ORDER BY DIRECTORY_NAME) AS DIRECTORY_NAMES,

    COUNT(*) AS COUNT

FROM 

    DBA_DIRECTORIES

GROUP BY 

    DIRECTORY_PATH

HAVING 

    COUNT(*) > 1

ORDER BY 

    COUNT DESC, DIRECTORY_PATH;

new script

 #!/bin/bash

# Create a unique folder under /tmp/

folder="/tmp/oracle_pfile_$(date +%Y%m%d_%H%M%S)"

mkdir -p $folder

echo "Created directory: $folder"

# Dynamically get spfile location

spfile_location=$(sqlplus -s / as sysdba <<EOF

SET HEADING OFF;

SET FEEDBACK OFF;

SELECT value FROM v\$parameter WHERE name = 'spfile';

EXIT;

EOF

)

# Trim white spaces from spfile_location

spfile_location=$(echo $spfile_location | xargs)

# Define pfile location

pfile_location=$folder/init.ora

# Create pfile from spfile

sqlplus / as sysdba <<EOF

CREATE PFILE='$pfile_location' FROM SPFILE='$spfile_location';

exit;

EOF

echo "Pfile created at: $pfile_location"

# Email details

recipient="your_email@example.com"

subject="Oracle Parameters Report"

email_body="<h2>Oracle Parameters Report</h2><br>"

email_body+="<p>Created directory: $folder</p>"

email_body+="<p>Pfile created at: $pfile_location</p><br>"

# Mandatory parameters

mandatory_parameters=(

"compatible = 19.0.0 #MP"

"optimizer_adaptive_plans = TRUE #MP"

"optimizer_adaptive_statistics = FALSE #MP"

"pga_aggregate_limit = 0 #MP"

"temp_undo_enabled = FALSE"

"_pdb_name_case_sensitive = TRUE #MP"

"event='10946 trace name context forever, level 8454144' #MP"

"_optimizer_gather_stats_on_conventional_dml = FALSE #MP"

"_optimizer_use_stats_on_conventional_dml = FALSE #MP"

"optimizer_real_time_statistics = FALSE #MP"

)

# Check for mandatory parameters

email_body+="<h3>Missing Mandatory Parameters:</h3><ul>"

for param in "${mandatory_parameters[@]}"; do

    if ! grep -q "$param" $pfile_location; then

        email_body+="<li>$param</li>"

    fi

done

email_body+="</ul><br>"

# Removal list

removal_list=(

"_kks_use_mutex_pin"

"_shared_pool_reserved_min_alloc"

"_sqlexec_progression_cost"

"exafusion_enabled"

"exclude_seed_cdb_view"

"global_context_pool_size"

"max_enabled_roles"

"o7_dictionary_accessibility"

"olap_page_pool_size"

"optimizer_adaptive_features"

"optimizer_features_enable"

"parallel_automatic_tuning"

"parallel_degree_level"

"parallel_io_cap_enabled"

"parallel_server"

"parallel_server_instances"

"plsql_compiler_flags"

"plsql_native_library_dir"

"plsql_native_library_subdir_count"

"plsql_optimize_level"

"standby_archive_dest"

"timed_statistics"

"use_indirect_data_buffers"

"utl_file_dir"

)

# Check for removal parameters

email_body+="<h3>Parameters to be Removed:</h3><ul>"

for param in "${removal_list[@]}"; do

    if grep -q "$param" $pfile_location; then

        email_body+="<li>$param</li>"

        sed -i "/$param/d" $pfile_location

    fi

done

email_body+="</ul><br>"

# Send email using mailx

echo "Content-Type: text/html" > /tmp/mail_header.txt

echo "$email_body" | mailx -s "$subject" -r "your_email@example.com" -q /tmp/mail_header.txt $recipient

# Clean up temporary header file

rm /tmp/mail_header.txt

# Done

echo "Script finished."

Parameters validation

 #!/bin/bash

# Define ORACLE environment variables, adjust as needed

export ORACLE_HOME=/path/to/oracle_home

export ORACLE_SID=your_sid

# Create a unique folder under /tmp/

folder="/tmp/oracle_pfile_$(date +%Y%m%d_%H%M%S)"

mkdir -p $folder

echo "Created directory: $folder"

# Define file paths

spfile_location=$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora

pfile_location=$folder/init$ORACLE_SID.ora

# Create pfile from spfile

sqlplus / as sysdba <<EOF

CREATE PFILE='$pfile_location' FROM SPFILE='$spfile_location';

exit;

EOF

echo "Pfile created at: $pfile_location"

# Email details

recipient="your_email@example.com"

subject="Oracle Parameters Report"

email_body="<h2>Oracle Parameters Report</h2><br>"

email_body+="<p>Created directory: $folder</p>"

email_body+="<p>Pfile created at: $pfile_location</p><br>"

# Mandatory parameters

mandatory_parameters=(

"compatible = 19.0.0 #MP"

"optimizer_adaptive_plans = TRUE #MP"

"optimizer_adaptive_statistics = FALSE #MP"

"pga_aggregate_limit = 0 #MP"

"temp_undo_enabled = FALSE"

"_pdb_name_case_sensitive = TRUE #MP"

"event='10946 trace name context forever, level 8454144' #MP"

"_optimizer_gather_stats_on_conventional_dml = FALSE #MP"

"_optimizer_use_stats_on_conventional_dml = FALSE #MP"

"optimizer_real_time_statistics = FALSE #MP"

)

# Check for mandatory parameters

email_body+="<h3>Missing Mandatory Parameters:</h3><ul>"

missing_parameters_flag=0

for param in "${mandatory_parameters[@]}"; do

    if ! grep -q "$param" $pfile_location; then

        email_body+="<li>$param</li>"

        missing_parameters_flag=1

    fi

done

if [ $missing_parameters_flag -eq 0 ]; then

    email_body+="<li>None</li>"

fi

email_body+="</ul><br>"

# Removal list

removal_list=(

"_kks_use_mutex_pin"

"_shared_pool_reserved_min_alloc"

"_sqlexec_progression_cost"

"exafusion_enabled"

"exclude_seed_cdb_view"

"global_context_pool_size"

"max_enabled_roles"

"o7_dictionary_accessibility"

"olap_page_pool_size"

"optimizer_adaptive_features"

"optimizer_features_enable"

"parallel_automatic_tuning"

"parallel_degree_level"

"parallel_io_cap_enabled"

"parallel_server"

"parallel_server_instances"

"plsql_compiler_flags"

"plsql_native_library_dir"

"plsql_native_library_subdir_count"

"plsql_optimize_level"

"standby_archive_dest"

"timed_statistics"

"use_indirect_data_buffers"

"utl_file_dir"

)

# Check for removal parameters

email_body+="<h3>Parameters to be Removed:</h3><ul>"

remove_parameters_flag=0

for param in "${removal_list[@]}"; do

    if grep -q "$param" $pfile_location; then

        email_body+="<li>$param</li>"

        sed -i "/$param/d" $pfile_location

        remove_parameters_flag=1

    fi

done

if [ $remove_parameters_flag -eq 0 ]; then

    email_body+="<li>None</li>"

fi

email_body+="</ul><br>"

# Send email using mailx

echo "$email_body" | mailx -s "$subject" -a "Content-Type: text/html" $recipient

# Done

echo "Script finished."

workflow

 #!/bin/bash

# Oracle DB Connection Details

USERNAME="your_oracle_username"

PASSWORD="your_oracle_password"

DATABASE="your_oracle_sid"

# Email Details

SUBJECT="Oracle DB Query Results"

RECIPIENT="recipient_email@example.com"

SENDER="sender_email@example.com"

# Execute the first SQL query

QUERY1_OUTPUT=$(sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF

SET PAGESIZE 500

SET LINESIZE 200

SELECT component_name, component_status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

EXIT;

EOF

)

# Execute the second SQL query

QUERY2_OUTPUT=$(sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF

SET LINESIZE 155

SET PAGESIZE 200

SET VERIFY OFF

COLUMN MANAGER FORMAT A15

COLUMN MEANING FORMAT A15

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name 

FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup 

WHERE concurrent_queue_name IN ('WFMLRSVC') 

AND fcq.concurrent_queue_id = fcp.concurrent_queue_id 

AND fcq.application_id = fcp.queue_application_id 

AND flkup.lookup_code=fcp.process_status_code 

AND lookup_type ='CP_PROCESS_STATUS_CODE' 

AND meaning='Active';

EXIT;

EOF

)

# Compose the email body

EMAIL_BODY="First Query Result:\n$QUERY1_OUTPUT\n\nSecond Query Result:\n$QUERY2_OUTPUT"

# Send the email

echo "$EMAIL_BODY" | mailx -s "$SUBJECT" -r "$SENDER" "$RECIPIENT"

echo "Script executed and email sent successfully."

again

 #!/bin/bash

# Email details

RECIPIENT="recipient@example.com"

SENDER="sender@example.com"

EMAIL_SUBJECT="Query Results"

# SQL script files

SQL_FILES=("query1.sql" "query2.sql" "query3.sql")

# Execute the SQL scripts and store results in HTML format

OUTPUT_FILES=()

for ((i = 0; i < ${#SQL_FILES[@]}; i++)); do

    SQL_FILE="${SQL_FILES[i]}"

    OUTPUT_FILE=$(date +%Y%m%d%H%M%S)_query$i.html

    OUTPUT_FILES+=("$OUTPUT_FILE")

    # Run the SQL script using SQL*Plus

    sqlplus -S app/apps@vision @"$SQL_FILE" > "$OUTPUT_FILE"

done

# Prepare the email body with results of each query

EMAIL_BODY=""

for ((i = 0; i < ${#OUTPUT_FILES[@]}; i++)); do

    OUTPUT_FILE="${OUTPUT_FILES[i]}"

    

    if [ -f "$OUTPUT_FILE" ]; then

        HTML_BODY=$(cat "$OUTPUT_FILE")

        # Append HTML content to the email body

        EMAIL_BODY+="\n\nQuery $((i+1)) Results:\n\n$HTML_BODY"

        # Remove the temporary HTML file

        rm "$OUTPUT_FILE"

    fi

done

# Send email using mailx

echo "$EMAIL_BODY" | mailx -s "$EMAIL_SUBJECT" -r "$SENDER" "$RECIPIENT"

echo "Email sent successfully."

Ad applied patches

 #!/bin/bash

# Source the environment file to load environment variables

source /opt/mis/SID/EBSapps.env RUN

# Database connection details

DB_USER="your_db_user"

DB_PASSWORD="your_db_password"

DB_HOST="your_db_host"

DB_PORT="your_db_port"

DB_SERVICE="your_db_service"

# Email details

RECIPIENT="recipient@example.com"

SENDER="sender@example.com"

EMAIL_SUBJECT="Query Results"

# SQL queries

QUERIES=(

    "set pagesize 200;

    set linesize 160;

    column adop_session_id format 999999999999;

    column bug_number format a15;

    column status format a15;

    column applied_file_system_base format a23;

    column patch_file_system_base format a23;

    column adpatch_options format a15;

    column node_name format a15;

    column end_date format a15;

    column clone_status format a15;

    SELECT ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS

    FROM ad_adop_session_patches

    ORDER BY end_date DESC;"

    "SELECT * FROM ad_adop_session_patches

    ORDER BY end_date DESC;"

    "SELECT * FROM ad_adop_session_patches

    ORDER BY end_date DESC;"

)

# Execute the SQL queries and store results in HTML format

OUTPUT_FILES=()

for ((i = 0; i < ${#QUERIES[@]}; i++)); do

    QUERY="${QUERIES[i]}"

    OUTPUT_FILE=$(date +%Y%m%d%H%M%S)_query$i.html

    OUTPUT_FILES+=("$OUTPUT_FILE")

    sqlplus -S "$DB_USER/$DB_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=$DB_PORT))(CONNECT_DATA=(SERVICE_NAME=$DB_SERVICE)))" << EOF

    $QUERY

EOF

done

# Prepare the email body with results of each query

EMAIL_BODY=""

for ((i = 0; i < ${#OUTPUT_FILES[@]}; i++)); do

    OUTPUT_FILE="${OUTPUT_FILES[i]}"

    HTML_BODY=$(cat "$OUTPUT_FILE")

    EMAIL_BODY+="\n\nQuery $((i+1)) Results:\n\n$HTML_BODY"

done

# Send email using mailx

echo -e "$EMAIL_BODY" | mailx -s "$EMAIL_SUBJECT" -r "$SENDER" "$RECIPIENT"

echo "Email sent successfully."

# Clean up the temporary HTML files

for OUTPUT_FILE in "${OUTPUT_FILES[@]}"; do

    if [ -f "$OUTPUT_FILE" ]; then

        rm "$OUTPUT_FILE"

    fi

done

Sample script

 #!/bin/bash

# Source the environment file to load environment variables

source /opt/mis/SID/EBSapps.env RUN

# Database connection details

DB_USER="your_db_user"

DB_PASSWORD="your_db_password"

DB_HOST="your_db_host"

DB_PORT="your_db_port"

DB_SERVICE="your_db_service"

# Email details

RECIPIENT="recipient@example.com"

SENDER="sender@example.com"

EMAIL_SUBJECT="Query Results"

# SQL query

QUERY=$(cat <<EOF

SET MARKUP HTML ON SPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SET TERMOUT OFF

COLUMN owner FORMAT a15

COLUMN object_name FORMAT a30

COLUMN object_type FORMAT a20

COLUMN status FORMAT a10

SELECT owner, object_name, object_type, status

FROM dba_objects

WHERE status = 'INVALID'

AND object_name NOT IN (SELECT object_name FROM invalid_1213 WHERE status = 'INVALID');

SPOOL OFF

SET MARKUP HTML OFF SPOOL OFF

SET ECHO ON

SET FEEDBACK ON

SET TERMOUT ON

EOF

)

# Generate unique file name

OUTPUT_FILE=$(date +%Y%m%d%H%M%S).html

# Execute the SQL query and store results in HTML format

sqlplus -S "$DB_USER/$DB_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=$DB_PORT))(CONNECT_DATA=(SERVICE_NAME=$DB_SERVICE)))" << EOF

$QUERY

EOF

if [ -s "$OUTPUT_FILE" ]; then

  # Read the HTML output from the file

  HTML_BODY=$(cat "$OUTPUT_FILE")

  # Send email using mailx

  echo -e "$HTML_BODY" | mailx -s "$EMAIL_SUBJECT" -r "$SENDER" "$RECIPIENT"

  echo "Email sent successfully."

else

  echo "No results found."

fi

# Clean up the temporary HTML file

if [ -f "$OUTPUT_FILE" ]; then

  rm "$OUTPUT_FILE"

fi

Review

 SELECT object_name, object_type, created, last_ddl_time

FROM dba_objects

WHERE created > (SELECT MAX(modified) FROM invalids_before_israel_loca)

ORDER BY created;

Latest - not tested

#!/bin/bash

# List of databases (replace these with your actual database names)

DATABASES=("CDB1" "CDB2" "CDB3")

# Constants

EMAIL_SUBJECT=""

EMAIL_RECIPIENT="root@localhost"

for CDB_NAME in "${DATABASES[@]}"; do

  PFILE_PATH="/tmp/pfile_${CDB_NAME}.ora"

  

  # Create a pfile from the spfile

  sqlplus / as sysdba << EOF

  create pfile='$PFILE_PATH' from spfile;

  EOF

  

  # Check for deprecated parameters in the pfile

  deprecated_params=$(egrep -i "(_kks_use_mutex_pin|_shared_pool_reserved_min_alloc|_sqlexec_progression_cost|exafusion_enabled|exclude_seed_cdb_view|global_context_pool_size|max_enabled_roles|o7_dictionary_accessibility|olap_page_pool_size|optimizer_adaptive_features|optimizer_features_enable|parallel_automatic_tuning|parallel_degree_level|parallel_io_cap_enabled|parallel_server|parallel_server_instances|plsql_compiler_flags|plsql_native_library_dir|plsql_native_library_subdir_count|plsql_optimize_level|standby_archive_dest|timed_statistics|use_indirect_data_buffers|utl_file_dir)" "$PFILE_PATH")

  

  # Send email for deprecated parameters if found

  if [ -n "$deprecated_params" ]; then

    EMAIL_SUBJECT="Deprecated parameters found in pfile"

    deprecated_params_message="The following deprecated parameters are found in the pfile for CDB $CDB_NAME: $deprecated_params"

  fi

  

  # Check for missing mandatory parameters in the pfile

  mandatory_params=$(egrep -i "(compatible|optimizer_adaptive_plans|optimizer_adaptive_statistics|pga_aggregate_limit|temp_undo_enabled|_pdb_name_case_sensitive|event|_optimizer_gather_stats_on_conventional_dml|_optimizer_use_stats_on_conventional_dml|optimizer_real_time_statistics)" "$PFILE_PATH")

  

  # Send email for missing mandatory parameters if found

  if [ -z "$mandatory_params" ]; then

    echo "All mandatory parameters are found in the pfile for CDB $CDB_NAME."

  else

    EMAIL_SUBJECT="Mandatory parameters missing from pfile"

    mandatory_params_message="The following mandatory parameters are missing from the pfile for CDB $CDB_NAME: $mandatory_params"

  fi

  

  # Send email if required

  if [ -n "$EMAIL_SUBJECT" ]; then

    email_message="$deprecated_params_message\n\n$mandatory_params_message\n\nPlease revisit the parameter file for CDB $CDB_NAME and make necessary changes."

    echo -e "$email_message" | mailx -s "$EMAIL_SUBJECT" $EMAIL_RECIPIENT

    echo "Email sent for CDB $CDB_NAME."

  fi

done

 

database parameters

#!/bin/bash

# Constants

PFILE_PATH="/tmp/pfile.ora"

EMAIL_SUBJECT=""

EMAIL_RECIPIENT="root@localhost"

# Create a pfile from the spfile

sqlplus / as sysdba << EOF

create pfile='$PFILE_PATH' from spfile;

EOF

# Check for deprecated parameters in the pfile

deprecated_params=$(egrep -i "(_kks_use_mutex_pin|_shared_pool_reserved_min_alloc|_sqlexec_progression_cost|exafusion_enabled|exclude_seed_cdb_view|global_context_pool_size|max_enabled_roles|o7_dictionary_accessibility|olap_page_pool_size|optimizer_adaptive_features|optimizer_features_enable|parallel_automatic_tuning|parallel_degree_level|parallel_io_cap_enabled|parallel_server|parallel_server_instances|plsql_compiler_flags|plsql_native_library_dir|plsql_native_library_subdir_count|plsql_optimize_level|standby_archive_dest|timed_statistics|use_indirect_data_buffers|utl_file_dir)" $PFILE_PATH)

# Send email for deprecated parameters if found

if [ -n "$deprecated_params" ]; then

  EMAIL_SUBJECT="Deprecated parameters found in pfile"

  deprecated_params_message="The following deprecated parameters are found in the pfile: $deprecated_params"

fi

# Check for missing mandatory parameters in the pfile

mandatory_params=$(egrep -i "(compatible|optimizer_adaptive_plans|optimizer_adaptive_statistics|pga_aggregate_limit|temp_undo_enabled|_pdb_name_case_sensitive|event|_optimizer_gather_stats_on_conventional_dml|_optimizer_use_stats_on_conventional_dml|optimizer_real_time_statistics)" $PFILE_PATH)

# Send email for missing mandatory parameters if found

if [ -z "$mandatory_params" ]; then

  echo "All mandatory parameters are found in the pfile."

else

  EMAIL_SUBJECT="Mandatory parameters missing from pfile"

  mandatory_params_message="The following mandatory parameters are missing from the pfile: $mandatory_params"

fi

# Send email if required

if [ -n "$EMAIL_SUBJECT" ]; then

  email_message="$deprecated_params_message\n\n$mandatory_params_message\n\nPlease revisit the parameter file and make necessary changes."

  echo -e "$email_message" | mailx -s "$EMAIL_SUBJECT" $EMAIL_RECIPIENT

  echo "Email sent."

fi

sample invalid script

#!/bin/bash

Set the variables

sql_query="set pagesize 0 set linesize 200 set feedback off column owner format A9; column object_name format A31; column object_type format A15; column last_ddl_time format A10; spool EBS_DB_INVALIDS.lst; select owner,object_type,COUNT(*) from dba_objects where status='INVALID' group by owner, object_type; select OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where status='INVALID' order by owner; spool off; exit;"

email_subject="EBS INVALID Objects" email_body="The following is a list of invalid objects in the EBS database. Please contact me if you have any questions."

Source the Oracle application environment

source /path/to/oracle/apps/env.sh

Run the SQL query and save the output to a file

echo "$sql_query" | sqlplus -S apps/apps > EBS_DB_INVALIDS.lst

Convert the output file to HTML format

awk -F'|' '{print "<tr><td>"$1"</td><td>"$2"</td><td>"$3"</td><td>"$4"</td></tr>"}' EBS_DB_INVALIDS.lst > EBS_DB_INVALIDS.html

Send the HTML content in the email body as MIME format

echo "$email_body" | mailx -s "$email_subject" your_email_address

patch applied shell

 #!/bin/bash

# Source the environment . /path/to/env_file # Prompt for user input read -p "Enter the patch number: " patch_number # Execute SQL query and save output to a CSV file sqlplus -S /nolog <<EOF connect username/password@database set heading off set feedback off set pagesize 0 set linesize 1000 set colsep ',' spool output.csv SELECT driver.driver_file_name, TO_CHAR(run.start_date, 'DD-Mon HH24:MI:SS') AS start_date, TO_CHAR(run.end_date, 'DD-Mon HH24:MI:SS') AS end_date, lang.language FROM ad_patch_runs run, ad_patch_driver_langs lang, ad_patch_drivers driver, ad_applied_patches applied WHERE run.patch_driver_id = driver.patch_driver_id AND driver.applied_patch_id = applied.applied_patch_id AND applied.patch_name = '$patch_number' AND lang.patch_driver_id = driver.patch_driver_id ORDER BY 1, 2, 3; spool off exit; EOF # Convert CSV to Excel format ssconvert output.csv output.xlsx # Send email with the Excel file as an attachment echo "Please find the attached Excel file." | mailx -s "Patch Report" -a output.xlsx user@example.com

version 1

 #!/usr/bin/ksh

# Script version

SCRIPT_VERSION="v1.0"

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.html"

RECIPIENT="user@example.com"

EMAIL_SUBJECT="Patch Log Errors - $LOG_FILE_NAME"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

}

# Function to send email with the output in the email body

send_email() {

    email_body="<p>Please find the patch log errors below:</p><pre>"

    # Append each line to the email body content with <br> tag for new lines

    while IFS= read -r line; do

        email_body+="$line<br>"

    done < "$OUTPUT_FILE"

    email_body+="</pre>"

    

    # Send the email with the email body content

    echo "Content-Type: text/html" | mailx -s "$EMAIL_SUBJECT" "$RECIPIENT" -M "text/html" <<< "$email_body"

    # Clean up the temporary file

    rm "$OUTPUT_FILE"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

# Display script version

echo "Script version: $SCRIPT_VERSION"

latest

 #!/usr/bin/ksh

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.html"

RECIPIENT="user@example.com"

EMAIL_SUBJECT="Patch Log Errors - $LOG_FILE_NAME"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

}

# Function to send email with the output file as attachment

send_email() {

    email_body="<p>Please find the patch log errors in the attachment.</p>"

    email_file="email_content.html"

    

    # Create the email content file with HTML tags

    {

        echo "<html>"

        echo "<body>"

        echo "$email_body"

        echo "</body>"

        echo "</html>"

    } > "$email_file"

    # Send the email with the HTML content file as attachment

    ( echo "Content-Type: text/html" ; uuencode "$OUTPUT_FILE" "$OUTPUT_FILE" ; uuencode "$email_file" "$email_file" ) | mailx -s "$EMAIL_SUBJECT" "$RECIPIENT"

    # Clean up the temporary files

    rm "$OUTPUT_FILE" "$email_file"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

Patch

 #!/usr/bin/ksh

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.txt"

RECIPIENT="user@example.com"

EMAIL_SUBJECT="Patch Log Errors - $LOG_FILE_NAME"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

}

# Function to send email with the output file as attachment

send_email() {

    ( echo "Please find the patch log errors attached." ; uuencode "$OUTPUT_FILE" "$OUTPUT_FILE" ) | mailx -s "$EMAIL_SUBJECT" "$RECIPIENT"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

mewss

#!/usr/bin/ksh

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.html"

RECIPIENT="user@example.com"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

    # Add a meaningful description about the log file being scanned

    echo "<p>Scanned log file: $LOG_FILE_LOCATION/$LOG_FILE_NAME</p>" > "$OUTPUT_FILE.tmp"

    # Convert the log errors to an HTML table

    echo "<table border='1'>" >> "$OUTPUT_FILE.tmp"

    echo "<tr><th>Error Type</th><th>Line</th><th>Error Message</th></tr>" >> "$OUTPUT_FILE.tmp"

    awk -F':' '{print "<tr><td>" $2 "</td><td>" $1 "</td><td>" $3 "</td></tr>"}' "$OUTPUT_FILE" >> "$OUTPUT_FILE.tmp"

    echo "</table>" >> "$OUTPUT_FILE.tmp"

    mv "$OUTPUT_FILE.tmp" "$OUTPUT_FILE"

}

# Function to send email using the MIME file

send_email() {

    email_subject="Patch Log Errors - $LOG_FILE_NAME"

    mime_file="email_content.mime"

    

    # Create the MIME file

    {

        echo "To: $RECIPIENT"

        echo "Subject: $email_subject"

        echo "Content-Type: text/html"

        echo "MIME-Version: 1.0"

        echo

        cat "$OUTPUT_FILE"

    } > "$mime_file"

    # Send the email using the created MIME file

    mailx -s "$email_subject" "$RECIPIENT" < "$mime_file"

    # Clean up the MIME file

    rm "$mime_file"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

NEW SCRIPT

#!/usr/bin/ksh

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.html"

RECIPIENT="user@example.com"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

    # Add a meaningful description about the log file being scanned

    echo "<p>Scanned log file: $LOG_FILE_LOCATION/$LOG_FILE_NAME</p>" > "$OUTPUT_FILE.tmp"

    # Convert the log errors to an HTML table

    echo "<table border='1'>" >> "$OUTPUT_FILE.tmp"

    echo "<tr><th>Error Type</th><th>Line</th><th>Error Message</th></tr>" >> "$OUTPUT_FILE.tmp"

    sed 's/<[^>]*>//g' "$OUTPUT_FILE" | awk -v OFS='\t' '{print $1, $2, $3}' | while IFS=$'\t' read -r error line message; do

        echo "<tr><td>$error</td><td>$line</td><td>$message</td></tr>" >> "$OUTPUT_FILE.tmp"

    done

    echo "</table>" >> "$OUTPUT_FILE.tmp"

    mv "$OUTPUT_FILE.tmp" "$OUTPUT_FILE"

}

# Function to send email using the MIME file

send_email() {

    email_subject="Patch Log Errors - $LOG_FILE_NAME"

    mime_file="email_content.mime"

    

    # Create the MIME file

    {

        echo "To: $RECIPIENT"

        echo "Subject: $email_subject"

        echo "Content-Type: text/html"

        echo "MIME-Version: 1.0"

        echo

        cat "$OUTPUT_FILE"

    } > "$mime_file"

    # Send the email using the created MIME file

    mailx -s "$email_subject" "$RECIPIENT" < "$mime_file"

    # Clean up the MIME file

    rm "$mime_file"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

New File

 #!/usr/bin/ksh

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.html"

RECIPIENT="user@example.com"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

    # Add a meaningful description about the log file being scanned

    echo "<p>Scanned log file: $LOG_FILE_LOCATION/$LOG_FILE_NAME</p>" > "$OUTPUT_FILE.tmp"

    # Convert the log errors to an HTML table

    echo "<table border='1'>" >> "$OUTPUT_FILE.tmp"

    echo "<tr><th>Error Type</th><th>Line</th><th>Error Message</th></tr>" >> "$OUTPUT_FILE.tmp"

    sed 's/<[^>]*>//g' "$OUTPUT_FILE" | awk -v OFS='\t' '{print $1, $2, $3}' | while IFS=$'\t' read -r error line message; do

        echo "<tr><td>$error</td><td>$line</td><td>$message</td></tr>" >> "$OUTPUT_FILE.tmp"

    done

    echo "</table>" >> "$OUTPUT_FILE.tmp"

    mv "$OUTPUT_FILE.tmp" "$OUTPUT_FILE"

}

# Send email function

send_email() {

    local email_subject="Patch Log Errors - $LOG_FILE_NAME"

    local mime_file="email_content.mime"

    

    # Create the MIME file

    {

        echo "To: $RECIPIENT"

        echo "Subject: $email_subject"

        echo "Content-Type: text/html"

        echo "MIME-Version: 1.0"

        echo

        cat "$OUTPUT_FILE"

    } > "$mime_file"

    # Send the email using the created MIME file

    /usr/lib/sendmail -t < "$mime_file"

    # Clean up the MIME file

    rm "$mime_file"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

Latest scan script -- ./script.sh /path/to/log/directory logfile.log

#!/bin/sh

# Check if both log file location and name are provided as arguments

if [ $# -ne 2 ]; then

    echo "Usage: $0 <log_file_location> <log_file_name>"

    exit 1

fi

# Set the input parameters to variables

LOG_FILE_LOCATION="$1"

LOG_FILE_NAME="$2"

# Define other variables

OUTPUT_FILE="patch_errors.html"

RECIPIENT="user@example.com"

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    egrep -i "error|warning|failed" "$LOG_FILE_LOCATION/$LOG_FILE_NAME" > "$OUTPUT_FILE"

    # Add a meaningful description about the log file being scanned

    echo "<p>Scanned log file: $LOG_FILE_LOCATION/$LOG_FILE_NAME</p>" > "$OUTPUT_FILE.tmp"

    # Convert the log errors to an HTML table

    echo "<table border='1'>" >> "$OUTPUT_FILE.tmp"

    echo "<tr><th>Error Type</th><th>Line</th><th>Error Message</th></tr>" >> "$OUTPUT_FILE.tmp"

    sed 's/<[^>]*>//g' "$OUTPUT_FILE" | awk -v OFS='\t' '{print $1, $2, $3}' | while IFS=$'\t' read -r error line message; do

        echo "<tr><td>$error</td><td>$line</td><td>$message</td></tr>" >> "$OUTPUT_FILE.tmp"

    done

    echo "</table>" >> "$OUTPUT_FILE.tmp"

    mv "$OUTPUT_FILE.tmp" "$OUTPUT_FILE"

}

# Send email function

send_email() {

    local email_subject="Patch Log Errors - $LOG_FILE_NAME"

    cat "$OUTPUT_FILE" | mailx -a "Content-Type: text/html" -s "$email_subject" "$RECIPIENT"

}

# Main script execution

scan_log_files

if [ -s "$OUTPUT_FILE" ]; then

    send_email

    echo "Email sent with the error details from the log file: $LOG_FILE_NAME."

else

    echo "No errors, warnings, or failures found in the log file: $LOG_FILE_NAME."

fi

+++

./script.sh /path/to/log/directory logfile.log

Scan patches

 #!/bin/sh

# Define variables

APPL_TOP="/path/to/APPL_TOP"

TWO_TASK="your_TWO_TASK_value"

LOG_DIR="$APPL_TOP/admin/$TWO_TASK/log"

OUTPUT_FILE="patch_errors.txt"

RECIPIENT="user@example.com"

EMAIL_SUBJECT="Patch Log Errors"

# Function to find the most recent patch log files

get_recent_log_files() {

    find "$LOG_DIR" -name "*.log" -o -name "*.lgi" -type f -exec stat -c "%Y %n" {} + | sort -r | head -n 1 | cut -d ' ' -f 2-

}

# Function to scan log files for errors, warnings, and failures

scan_log_files() {

    grep -E -i "error|warning|failed" $(get_recent_log_files) > $OUTPUT_FILE

}

# Send email function

send_email() {

    cat $OUTPUT_FILE | mailx -s "$EMAIL_SUBJECT" "$RECIPIENT"

}

# Main script execution

recent_log_files=$(get_recent_log_files)

if [ -n "$recent_log_files" ]; then

    scan_log_files

    if [ -s "$OUTPUT_FILE" ]; then

        send_email

        echo "Email sent with the error details from the most recent patch log."

    else

        echo "No errors, warnings, or failures found in the most recent patch log."

    fi

else

    echo "No patch log files found in the specified location."

fi

Invalid Objects script mew

#!/usr/bin/ksh

# Define variables

OUTPUT_FILE="invalids.html"

RECIPIENT="user@example.com"

# Write SQL query to script file

echo "SET MARKUP HTML ON

COLUMN OWNER FORMAT A9

COLUMN OBJECT_NAME FORMAT A31

COLUMN OBJECT_TYPE FORMAT A15

COLUMN LAST_DDL_TIME FORMAT A10

SPOOL $OUTPUT_FILE

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME

FROM DBA_OBJECTS

WHERE STATUS='INVALID'

ORDER BY OWNER;

SET MARKUP HTML OFF" > pmopt.sql

# Run SQL query and spool output to file

sqlplus -s username/password@database @pmopt.sql >> /dev/null

# Replace special characters with HTML tags

sed -i 's/</\&lt;/g' $OUTPUT_FILE

sed -i 's/>/\&gt;/g' $OUTPUT_FILE

# Modify HTML output to include only the table

awk 'NR>14' $OUTPUT_FILE | sed 's/INVALID/<span style="color:red;font-weight:bold;">INVALID<\/span>/g' > temp_file && mv temp_file $OUTPUT_FILE

# Send email with HTML-formatted output in the body

mailx -a "Content-Type: text/html" -s "Invalid Objects Report" $RECIPIENT < $OUTPUT_FILE

Validate22

#!/bin/bash # Path to the file file="$IAS_ORACLE_HOME/instances/EBS_web_OHS1/config/OHS/EBS_web_OAQCTSUP/mod_wl_ohs.conf" # Grep for "sup" in the file output=$(grep "sup" "$file") # Email subject and body subject="Grep Results for 'sup'" body="The following lines contain 'sup' in the file $file:\n\n$output" # Encode the output using uuencode encoded=$(echo "$body" | uuencode output.txt) # Send email with encoded output as attachment echo -e "Please find attached the output of the script." | mailx -s "$subject" [email protected] <<< "$encoded" # Remove encoded file rm output.txt

Vertx

 #!/bin/bash

# Get the current date and time curr_dt=$(date +"%Y-%m-%d-%H-%M-%S") # Connect to the apps schema and run the command output=$(sqlplus -S apps/[apps_password]@[database] << EOF set serveroutput on; exec VERRTEXOICTAXEXAMPLE; exit; EOF ) # Email subject and body subject="Output of VERRTEXOICTAXEXAMPLE" body="<html><body><p>Please find below the output of VERRTEXOICTAXEXAMPLE:</p><pre>$output</pre></body></html>" # Send email with output in the body echo "$body" | mailx -a "Content-Type: text/html" -s "$subject" [email protected] # Remove output log file rm vertex_validation_$curr_dt.log

validation script

#!/bin/bash # Get the current date and time curr_dt=$(date +"%Y-%m-%d-%H-%M-%S") # Connect to the apps schema and run the command sqlplus apps/[apps_password]@[database] << EOF > vertex_validation_$curr_dt.log set serveroutput on; exec VERRTEXOICTAXEXAMPLE; exit; EOF # Encode the log file using uuencode uuencode vertex_validation_$curr_dt.log vertex_validation_$curr_dt.log > vertex_validation_$curr_dt.txt # Email subject and body subject="Output of VERRTEXOICTAXEXAMPLE" body="Please find attached the output of VERRTEXOICTAXEXAMPLE." # Send email with output log file mailx -s "$subject" [email protected] < vertex_validation_$curr_dt.txt # Remove output log and encoded files rm vertex_validation_$curr_dt.log rm vertex_validation_$curr_dt.txt

r12preabdul.sh

 #!/bin/bash

# r12preabdul.sh - Preclone script for Oracle E-Business Suite R12

# Set the environment variables (replace with your values)
APPS_TOP="/path/to/your/APPS_TOP"
FND_TOP="/path/to/your/FND_TOP"
MWA_TOP="/path/to/your/MWA_TOP"
MSC_TOP="/path/to/your/MSC_TOP"
ORA_CONFIG_HOME="/path/to/your/10.1.3/oraconfig_home"

# Create necessary directories
mkdir -p clone_17apr2012/DT/{appl,10.1.2,10.1.3,db,inst}

# Copy files to clone_17apr2012/DT/appl directory
cd clone_17apr2012/DT/appl
cp $APPS_TOP/*.env .
cp $APPS_TOP/admin/adovars.env .
cp $APPS_TOP/admin/adpltfrm.txt .
cp $APPS_TOP/admin/topfile.txt .
cp $APPS_TOP/admin/*.env .
cp $FND_TOP/resource/wf* .
cp $FND_TOP/resource/WF* .
cp -pr $FND_TOP/secure/* .
cp $ORA_CONFIG_HOME/env.txt .
cp $APPS_TOP/admin/adconfig.txt .
cp -pr $MWA_TOP/secure/* .
mkdir oahtml fndtop msc
cp $FND_TOP/admin/template/appsweb*.cfg ./fndtop
cp $ORA_CONFIG_HOME/Apache/Apache/conf/appsweb*.cfg ./oahtml
cp $MSC_TOP/bin/MSCSLD* ./msc

# Copy files to clone_17apr2012/DT/10.1.2 directory
cd ../10.1.2
cp -rp $APPS_TOP/ora/10.1.2/network/admin/*.ora .
cp -rp $APPS_TOP/ora/10.1.2/network/admin/*.cfg .
cp -rp $APPS_TOP/ora/10.1.2/forms/server/*.env .
cp -rp $APPS_TOP/ora/10.1.2/forms/server/*.cfg .
cp -rp $APPS_TOP/ora/10.1.2/forms/server/*.sh  .

# Copy files to clone_17apr2012/DT/10.1.3 directory
cd ../10.1.3
cp -rp $APPS_TOP/ora/10.1.3/network/admin/*.ora .
cp -rp $ORA_CONFIG_HOME/Apache/Apache/conf/*.conf .
cp -rp $ORA_CONFIG_HOME/Apache/Apache/conf/osso .
cp -rp $APPS_TOP/ora/10.1.3/opmn/conf/*.xml .
cp -rp $APPS_TOP/ora/10.1.3/config/* .
cp -rp $APPS_TOP/ora/10.1.3/*.env .
cp -rp $APPS_TOP/ora/10.1.3/deconfig/* .
cp -rp $APPS_TOP/ora/10.1.3/j2ee/* .
cp -rp $APPS_TOP/ora/10.1.3/javacache/admin .

# Copy files to clone_17apr2012/DT/inst directory
cd ../inst
cp -rp /clone/erpapp/inst/* .

# Navigate back to the appl directory
cd ../appl

# FNDLOAD commands (update placeholders accordingly)
FNDLOAD apps/&&apps_pwd  0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct user_resp_clone.dat FND_USER
FNDLOAD apps/&&apps_pwd  0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct profile_clone.dat PROFILE

mode

 SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value",

       decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,

       decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE

FROM   x$ksppi a,

x$ksppcv b,

x$ksppsv c

WHERE  a.indx = b.indx

AND    a.indx = c.indx

AND    a.ksppinm LIKE '/_%actualization%' escape '/';

Finding Undocumented ( Hidden ) Parameter in pfile

egrep -i '__db_cache_size|__java_pool_size|__large_pool_size|__oracle_base|__pga_aggregate_target|__sga_target|__shared_io_pool_size|__shared_pool_size|__streams_pool_size|_always_anti_join|_always_semi_join|_b_tree_bitmap_plans|_bloom_filter_enabled|_bloom_pruning_enabled|_complex_view_merging|_convert_set_to_join|_cost_equality_semi_join|_cpu_to_io|_dimension_skip_null|_eliminate_common_subexpr|_enable_type_dep_selectivity|_fast_full_scan_enabled|_first_k_rows_dynamic_proration|_gby_hash_aggregation_enabled|_generalized_pruning_enabled|_globalindex_pnum_filter_enabled|_gs_anti_semi_join_allowed|_improved_outerjoin_card|_improved_row_length_enabled|_index_join_enabled|_ksb_restart_policy_times|_ksb_restart_policy_times|_ksb_restart_policy_times|_ksb_restart_policy_times|_left_nested_loops_random|_local_communication_costing_enabled|_minimal_stats_aggregation|_mmv_query_rewrite_enabled|_new_initial_join_orders|_new_sort_cost_estimate|_nlj_batching_enabled|_optim_adjust_for_part_skews|_optim_enhance_nnull_detection|_optim_new_default_join_sel|_optim_peek_user_binds|_optimizer_adaptive_cursor_sharing|_optimizer_better_inlist_costing|_optimizer_cbqt_no_size_restriction|_optimizer_complex_pred_selectivity|_optimizer_compute_index_stats|_optimizer_connect_by_combine_sw|_optimizer_connect_by_cost_based|_optimizer_correct_sq_selectivity|_optimizer_cost_based_transformation|_optimizer_cost_hjsmj_multimatch|_optimizer_cost_model|_optimizer_dim_subq_join_sel|_optimizer_distinct_elimination|_optimizer_enable_density_improvements|_optimizer_enable_extended_stats|_optimizer_enhanced_filter_push|_optimizer_extend_jppd_view_types|_optimizer_extended_cursor_sharing|_optimizer_extended_cursor_sharing_rel|_optimizer_extended_stats_usage_control|_optimizer_filter_pred_pullup|_optimizer_fkr_index_cost_bias|_optimizer_group_by_placement|_optimizer_improve_selectivity|_optimizer_join_elimination_enabled|_optimizer_join_order_control|_optimizer_join_sel_sanity_check|_optimizer_max_permutations|_optimizer_mode_force|_optimizer_multi_level_push_pred|_optimizer_native_full_outer_join|_optimizer_new_join_card_computation|_optimizer_null_aware_antijoin|_optimizer_or_expansion|_optimizer_order_by_elimination_enabled|_optimizer_outer_to_anti_enabled|_optimizer_push_down_distinct|_optimizer_push_pred_cost_based|_optimizer_rownum_bind_default|_optimizer_rownum_pred_based_fkr|_optimizer_skip_scan_enabled|_optimizer_sortmerge_join_inequality|_optimizer_squ_bottomup|_optimizer_star_tran_in_with_clause|_optimizer_system_stats_usage|_optimizer_transitivity_retain|_optimizer_undo_cost_change|_or_expand_nvl_predicate|_ordered_nested_loop|_parallel_broadcast_enabled|_partition_view_enabled|_pivot_implementation_method|_pre_rewrite_push_pred|_pred_move_around|_push_join_predicate|_push_join_union_view|_push_join_union_view2|_px_minus_intersect|_px_pwg_enabled|_px_ual_serial_input|_query_rewrite_setopgrw_enable|_remove_aggr_subquery|_right_outer_hash_enable|_selfjoin_mv_duplicates|_sql_model_unfold_forloops|_sqltune_category_parsed|_subquery_pruning_enabled|_subquery_pruning_mv_enabled|_table_scan_cost_plus_one|_union_rewrite_for_gs||_unnest_subquery|_use_column_stats_for_function' 

SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

adpatch

#!/bin/bash

# Set database connection details
db_user="APPS"
db_pass="yourpassword"
db_host="yourhostname"
db_port="1521"
db_sid="yoursid"

# Set email address
email="youremail@example.com"

# Query ad_applied_patches table
patches=$(sqlplus -s $db_user/$db_pass@$db_host:$db_port/$db_sid <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT patch_name || ' - ' || patch_type || ' - ' || creation_date FROM ad_applied_patches;
EXIT;
EOF)

# Format output as HTML table
output="<html><body><h1>List of applied patches:</h1><table><tr><th>Patch Name</th><th>Patch Type</th><th>Creation Date</th></tr>"
while read -r line; do
    IFS='-' read -ra arr <<< "$line"
    output+="<tr>"
    for i in "${arr[@]}"; do
        output+="<td>$i</td>"
    done
    output+="</tr>"
done <<< "$patches"
output+="</table></body></html>"

# Send email using mailx command with -S option instead of -a option
echo "$output" | mailx -S "Content-type: text/html;" -s "Patches Report" $email

Received message. If you're not getting the proper report using the current output format, you can try changing it to better suit your needs. Here's an example of how you could format the output as a table instead: ```bash #!/bin/bash # Set database connection details db_user="APPS" db_pass="yourpassword" db_host="yourhostname" db_port="1521" db_sid="yoursid" # Set email address email="youremail@example.com" # Query ad_applied_patches table patches=$(sqlplus -s $db_user/$db_pass@$db_host:$db_port/$db_sid <<EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT patch_name || ' - ' || patch_type || ' - ' || creation_date FROM ad_applied_patches; EXIT; EOF) # Format output as HTML table output="<html><body><h1>List of applied patches:</h1><table><tr><th>Patch Name</th><th>Patch Type</th><th>Creation Date</th></tr>" while read -r line; do IFS='-' read -ra arr <<< "$line" output+="<tr>" for i in "${arr[@]}"; do output+="<td>$i</td>" done output+="</tr>" done <<< "$patches" output+="</table></body></html>" # Send email using mailx command with -S option instead of -a option echo "$output" | mailx -S "Content-type: text/html;" -s "Patches Report" $email ``` Does this work

profile changes

 #Script used to monitor for application profile changes #Threshold is the number of days to query for profile changes #For example, if you set it to 7, all profile changes that #have occurred in the past 7 days will be displayed. THRESHOLD=$1 LOGFILE=/tmp/profile_changes_$ORACLE_SID.txt sqlplus -s apps/apps << EOF set heading off spool $LOGFILE select '$ORACLE_SID - Profile Changes Past '|| 'Threshold of $THRESHOLD days - '||count(1) from fnd_profile_option_values where last_update_date > (sysdate-$THRESHOLD) having count(1) > $THRESHOLD union select 'no rows' from fnd_profile_option_values where last_update_date <= (sysdate-$THRESHOLD) having count(1) <= $THRESHOLD; spool off exit EOF RETURN_CODE=`grep "Threshold" $LOGFILE | wc -l` if [ $RETURN_CODE -eq 0 ] then exit 0 else exit 1 fi

latest

 # Specify profile option names and expected values as arrays

PROFILE_OPTION_NAMES=(

  "POS_INTERNAL_URL"

  "POS_EXTERNAL_URL"

  "APPS_AUTH_AGENT"

)

PROFILE_OPTION_VALUES=(

  "http://internal.example.com"

  "https://external.example.com"

  "http://auth.example.com"

)

# Loop through profile option names and check their values

for (( i=0; i<${#PROFILE_OPTION_NAMES[@]}; i++ )); do

  PROFILE_OPTION_NAME="${PROFILE_OPTION_NAMES[i]}"

  EXPECTED_VALUE="${PROFILE_OPTION_VALUES[i]}"

  # Get current value of profile option

  CURRENT_VALUE=$(sqlplus -s username/password << EOF

  set heading off feedback off verify off

  select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_NAME='$PROFILE_OPTION_NAME';

  exit;

  EOF

  )

  # Check if the value matches the expected value

  if [[ "$CURRENT_VALUE" == "$EXPECTED_VALUE" ]]; then

    # Value is correct

    MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has the expected value '$EXPECTED_VALUE'<br>"

  else

    # Value is incorrect

    MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has an incorrect value. Expected '$EXPECTED_VALUE', but found '$CURRENT_VALUE'<br>"

    COLOR="red"

  fi

done

LATEST PROFLE OPTION

 #!/bin/bash

# Source the R12.2 application environment file

. /path/to/R12.2/envfile

# Specify profile option names and expected values as associative array

declare -A PROFILE_OPTIONS=(

  ["POS_INTERNAL_URL"]="http://internal.example.com"

  ["POS_EXTERNAL_URL"]="https://external.example.com"

  ["APPS_AUTH_AGENT"]="http://auth.example.com"

)

# Set email addresses and subject for report

EMAIL_RECIPIENT="youremail@example.com"

EMAIL_SUBJECT="Profile Option Validation Report"

# Set report file name and path

REPORT_FILE_NAME="profile_option_report.html"

REPORT_FILE_PATH="/path/to/report/directory/$REPORT_FILE_NAME"

# Initialize message and color variables

MESSAGE=""

COLOR="green"

# Loop through profile option names and check their values

for PROFILE_OPTION_NAME in "${!PROFILE_OPTIONS[@]}"; do

  EXPECTED_VALUE="${PROFILE_OPTIONS[$PROFILE_OPTION_NAME]}"

  # Get current value of profile option

  CURRENT_VALUE=$(sqlplus -s username/password << EOF

set heading off feedback off verify off

select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_NAME='$PROFILE_OPTION_NAME';

exit;

EOF

)

  # Check if the value matches the expected value

  if [[ "$CURRENT_VALUE" == "$EXPECTED_VALUE" ]]; then

    # Value is correct

    MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has the expected value '$EXPECTED_VALUE'<br>"

  else

    # Value is incorrect

    MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has an incorrect value. Expected '$EXPECTED_VALUE', but found '$CURRENT_VALUE'<br>"

    COLOR="red"

  fi

done

# Create HTML report

echo "<html><body><h2>$MESSAGE</h2></body></html>" > "$REPORT_FILE_PATH"

# Add CSS style to report based on color

echo "<style>h2 {color: $COLOR;}</style>" >> "$REPORT_FILE_PATH"

# Send report via email

mailx -a "Content-Type: text/html" -s "$EMAIL_SUBJECT" "$EMAIL_RECIPIENT" < "$REPORT_FILE_PATH"

Profile options validation script

 #!/bin/bash

# Source the R12.2 application environment file

. /path/to/R12.2/envfile

# Specify profile option name and expected value

PROFILE_OPTION_NAME="EXAMPLE_PROFILE_OPTION"

EXPECTED_VALUE="EXPECTED_VALUE"

# Set email addresses and subject for report

EMAIL_RECIPIENT="youremail@example.com"

EMAIL_SUBJECT="Profile Option Validation Report"

# Set report file name and path

REPORT_FILE_NAME="profile_option_report.html"

REPORT_FILE_PATH="/path/to/report/directory/$REPORT_FILE_NAME"

# Get current value of profile option

CURRENT_VALUE=$(sqlplus -s username/password << EOF

set heading off feedback off verify off

select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_NAME='$PROFILE_OPTION_NAME';

exit;

EOF

)

# Check if the value matches the expected value

if [[ "$CURRENT_VALUE" == "$EXPECTED_VALUE" ]]; then

  # Value is correct

  MESSAGE="Profile option '$PROFILE_OPTION_NAME' has the expected value '$EXPECTED_VALUE'"

  COLOR="green"

else

  # Value is incorrect

  MESSAGE="Profile option '$PROFILE_OPTION_NAME' has an incorrect value. Expected '$EXPECTED_VALUE', but found '$CURRENT_VALUE'"

  COLOR="red"

fi

# Create HTML report

echo "<html><body><h2>$MESSAGE</h2></body></html>" > "$REPORT_FILE_PATH"

# Add CSS style to report based on color

echo "<style>h2 {color: $COLOR;}</style>" >> "$REPORT_FILE_PATH"

# Send report via email

mailx -a "Content-Type: text/html" -s "$EMAIL_SUBJECT" "$EMAIL_RECIPIENT" < "$REPORT_FILE_PATH"

findout the errors from the log files

 #!/bin/bash

# Define the location of the log files

LOG_DIR=/var/log

# Define the recipient email address

EMAIL_ADDRESS=user@example.com

# Search for error, warning, and failed messages in log files

ERRORS=$(grep -rniE 'error|warning|failed' $LOG_DIR | grep -v 'grep' | awk -F ":" '{print $1 " - Line " $2 ": " $3}')

if [ -n "$ERRORS" ]; then

  # Send an email notification with the error messages

  subject="Log file scan - $(date +%F)"

  message="The following errors, warnings, or failures were found in log files:

$ERRORS"

  echo "$message" | mail -s "$subject" $EMAIL_ADDRESS

else

  # Send an email notification indicating no errors were found

  subject="Log file scan - $(date +%F)"

  message="No errors, warnings, or failures were found in log files."

  echo "$message" | mail -s "$subject" $EMAIL_ADDRESS

fi

GREP THE S_PARAMETERS FROM CONTEXT_FILE

 #!/bin/bash

. $HOME/EBSapps.env run

# Define the path to the directory containing the XML files

dir_path='$CONTEXT_FILE'

# Define the recipient email address

email_address='recipient@example.com'

# Create a temporary file to store the HTML output

output_file=$(mktemp)

# Write the HTML header to the output file

echo '<html>' > $output_file

echo '<head>' >> $output_file

echo '<style>' >> $output_file

echo 'table {' >> $output_file

echo '  border-collapse: collapse;' >> $output_file

echo '}' >> $output_file

echo 'th, td {' >> $output_file

echo '  border: 1px solid black;' >> $output_file

echo '  padding: 8px;' >> $output_file

echo '}' >> $output_file

echo '</style>' >> $output_file

echo '</head>' >> $output_file

echo '<body>' >> $output_file

# Write the table header to the output file

echo '<table>' >> $output_file

echo '<tr>' >> $output_file

echo '<th>File Name</th>' >> $output_file

echo '<th>Line Number</th>' >> $output_file

echo '<th>s_ Parameter</th>' >> $output_file

echo '</tr>' >> $output_file

# Loop through each XML file in the directory

for file in $dir_path/*.xml; do

  # Get the filename without the path

  filename=$(basename $file)

  # Search the file for lines containing "s_"

  lines=$(grep -n "s_" $file)

  # Loop through each line

  while read -r line; do

    # Get the line number and the contents of the line

    line_number=$(echo $line | cut -d ":" -f 1)

    contents=$(echo $line | cut -d ":" -f 2-)

    # Write a table row to the output file

    echo '<tr>' >> $output_file

    echo "<td>$filename</td>" >> $output_file

    echo "<td>$line_number</td>" >> $output_file

    echo "<td>$contents</td>" >> $output_file

    echo '</tr>' >> $output_file

  done <<< "$lines"

done

# Write the HTML footer to the output file

echo '</table>' >> $output_file

echo '</body>' >> $output_file

echo '</html>' >> $output_file

# Send the HTML output as an email

mail -a "Content-Type: text/html" -s "s_ Parameters from XML Files" $email_address < $output_file

# Clean up the temporary file

rm $output_file

concurrent manager status shell

 #!/bin/bash

# Define the SQL query

SQL_QUERY="SELECT cm.CONCURRENT_PROGRAM_NAME,

                  cmr.STATUS,

                  cmr.ACTUAL_START_DATE,

                  cmr.ACTUAL_COMPLETION_DATE

           FROM   fnd_concurrent_requests cmr,

                  fnd_concurrent_programs cm

           WHERE  cmr.CONCURRENT_PROGRAM_ID = cm.CONCURRENT_PROGRAM_ID

           AND    cmr.REQUEST_ID = (SELECT MAX(REQUEST_ID)

                                    FROM   fnd_concurrent_requests

                                    WHERE  concurrent_program_id = cm.concurrent_program_id);"

# Execute the SQL query and write the results to a file

sqlplus -s <username>/<password>@<database> << EOF > concurrent_manager_status.txt

SET HEADING OFF

SET FEEDBACK OFF

SET LINESIZE 1000

SET PAGESIZE 0

$SQL_QUERY

EOF

# Define the recipient email address

EMAIL_TO=<recipient_email_address>

# Define the subject and message for the email

SUBJECT="Concurrent Manager Status"

MESSAGE="Attached is the current status of the concurrent manager."

# Send the email with the attachment

mail -s "$SUBJECT" -a concurrent_manager_status.txt "$EMAIL_TO" <<< "$MESSAGE"

# Clean up the temporary file

rm concurrent_manager_status.txt

Invalid

 #!/bin/bash

# Connect to the database and retrieve the list of invalid objects

invalid_objects=$(sqlplus -S user/password@database <<EOF

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SELECT object_name, object_type, status FROM dba_objects WHERE status = 'INVALID';

EXIT;

EOF

)

# Create the HTML file

cat > invalid_objects.html <<EOF

<html>

<head>

<title>List of Invalid Objects in Oracle Database</title>

</head>

<body>

<h1>List of Invalid Objects in Oracle Database</h1>

<table border="1">

<tr>

<th>Object Name</th>

<th>Object Type</th>

<th>Status</th>

</tr>

$(echo "$invalid_objects" | while read line; do

    object_name=$(echo $line | awk '{print $1}')

    object_type=$(echo $line | awk '{print $2}')

    status=$(echo $line | awk '{print $3}')

    echo "<tr><td>$object_name</td><td>$object_type</td><td>$status</td></tr>"

done)

</table>

</body>

</html>

EOF

Validate Multiple log files

 #!/bin/bash

# Define the log file directory

log_file_dir="/path/to/log/files"

# Define the HTML file name and header

html_file="log_file_validation.html"

html_header="<html><head><title>Log File Validation Report</title></head><body>"

html_footer="</body></html>"

# Define the recipient email address

recipient_email="recipient@example.com"

# Initialize the HTML file

echo "$html_header" > "$html_file"

# Validate the log files

echo "<h2>Log File Validation Report</h2>" >> "$html_file"

echo "<table border='1'>" >> "$html_file"

echo "<tr><th>File Name</th><th>Result</th></tr>" >> "$html_file"

for log_file in "$log_file_dir"/*.log; do

  if [ -f "$log_file" ]; then

    log_file_size=$(wc -c "$log_file" | awk '{print $1}')

    if [ "$log_file_size" -gt 0 ]; then

      validate_result="Success"

    else

      validate_result="Failed"

    fi

  else

    validate_result="Failed"

  fi

  echo "<tr><td>$log_file</td><td>$validate_result</td></tr>" >> "$html_file"

done

echo "</table>" >> "$html_file"

# Add the HTML file footer

echo "$html_footer" >> "$html_file"

# Send the HTML file as an email attachment

mail -s "Log File Validation Report" -a "$html_file" "$recipient_email" <<EOF

Please find the attached log file validation report.

EOF

sanity part 2

 #!/bin/bash

# Define the target database details

target_db_name="TARGET_DB"

target_db_user="TARGET_USER"

target_db_pass="TARGET_PASS"

# Define the HTML file name and header

html_file="table_validation.html"

html_header="<html><head><title>Table Validation Report</title></head><body>"

html_footer="</body></html>"

# Define the recipient email address

recipient_email="recipient@example.com"

# Initialize the HTML file

echo "$html_header" > "$html_file"

# Validate tables in the target database

tables_to_validate=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF

set heading off;

set feedback off;

set verify off;

select table_name from user_tables;

exit;

EOF

)

# Write the table validation result to the HTML file

echo "<h2>Table Validation Report</h2>" >> "$html_file"

echo "<table border='1'>" >> "$html_file"

echo "<tr><th>Table Name</th><th>Result</th></tr>" >> "$html_file"

for table in $tables_to_validate; do

  validate_result=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF

set heading off;

set feedback off;

set verify off;

declare

  l_result varchar2(20);

begin

  execute immediate 'analyze table $table validate structure';

  l_result := 'Success';

exception

  when others then

    l_result := 'Failed';

end;

/

select l_result from dual;

exit;

EOF

)

  echo "<tr><td>$table</td><td>$validate_result</td></tr>" >> "$html_file"

done

echo "</table>" >> "$html_file"

# Add the HTML file footer

echo "$html_footer" >> "$html_file"

# Send the HTML file as an email attachment

mail -s "Table Validation Report" -a "$html_file" "$recipient_email" <<EOF

Please find the attached table validation report.

EOF

oracle database clone sanity in html output

#Here's a shell script that performs a basic clone sanity check for an Oracle database and outputs the result in an HTML file#

 #!/bin/bash

# Define the target database details

target_db_name="TARGET_DB"

target_db_user="TARGET_USER"

target_db_pass="TARGET_PASS"

# Define the HTML file name and header

html_file="clone_sanity_check.html"

html_header="<html><head><title>Clone Sanity Check Report</title></head><body>"

html_footer="</body></html>"

# Initialize the HTML file

echo "$html_header" > "$html_file"

# Perform the database clone sanity check

# Check the target database connection

target_conn_check=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF

set heading off;

set feedback off;

set verify off;

select 'Success' from dual;

exit;

EOF

)

# Write the target connection check result to the HTML file

echo "<h2>Target Database Connection Check</h2>" >> "$html_file"

if [ "$target_conn_check" == "Success" ]; then

  echo "<p style='color: green;'>Successful</p>" >> "$html_file"

else

  echo "<p style='color: red;'>Failed</p>" >> "$html_file"

fi

# Add the HTML file footer

echo "$html_footer" >> "$html_file"