Tasks

Category,Prompt Incident Management,"Monitor incident queue and prioritize issues based on impact and urgency." Incident Management,"Log and document each incident, including root cause and resolution steps." Incident Management,"Communicate with stakeholders during high-impact incidents to provide status updates." Incident Management,"Perform immediate troubleshooting steps for high-severity incidents." Incident Management,"Escalate unresolved incidents to higher-level support as needed." Incident Management,"Analyze recurring incidents to identify potential root causes." Incident Management,"Implement quick fixes or workarounds to restore service during incidents." Incident Management,"Ensure incident management documentation is up to date." Incident Management,"Schedule post-incident reviews to improve response strategies." Incident Management,"Set up alerts to detect incidents before they impact users." Performance Monitoring,"Check database performance metrics regularly, such as CPU, memory, and I/O usage." Performance Monitoring,"Review top SQL queries by execution time to identify bottlenecks." Performance Monitoring,"Monitor resource usage patterns and adjust as necessary." Performance Monitoring,"Set up performance baselines and track changes over time." Performance Monitoring,"Configure alerts for unusual performance spikes or resource saturation." Performance Monitoring,"Work with application teams to optimize resource-intensive queries." Performance Monitoring,"Analyze wait events to identify areas causing latency." Performance Monitoring,"Document all performance tuning activities and results." Performance Monitoring,"Review historical performance data for trend analysis." Performance Monitoring,"Conduct regular health checks to ensure optimal database performance." Backup and Recovery,"Ensure that scheduled backups complete successfully each day." Backup and Recovery,"Verify the integrity of backup files to confirm restorability." Backup and Recovery,"Conduct periodic backup restoration tests in a non-production environment." Backup and Recovery,"Review backup retention policies to meet business requirements." Backup and Recovery,"Monitor backup storage to ensure sufficient capacity for future backups." Backup and Recovery,"Document backup and recovery procedures and update regularly." Backup and Recovery,"Set up alerts for backup failures or missed schedules." Backup and Recovery,"Coordinate with teams for additional backups before major changes." Backup and Recovery,"Review disaster recovery plans and align backup schedules accordingly." Backup and Recovery,"Train team members on backup and recovery processes." Security Management,"Apply the latest security patches to protect against vulnerabilities." Security Management,"Regularly review and update database user roles and permissions." Security Management,"Conduct regular audits of database access and privileges." Security Management,"Ensure all databases are configured with encryption for sensitive data." Security Management,"Monitor for suspicious activity or unauthorized access attempts." Security Management,"Document all security policies and updates for compliance." Security Management,"Set up multi-factor authentication for database access if possible." Security Management,"Collaborate with the security team to align with organizational policies." Security Management,"Regularly rotate passwords for high-privilege accounts." Security Management,"Review security settings to comply with industry regulations." Capacity Planning,"Monitor database growth trends to anticipate resource needs." Capacity Planning,"Analyze CPU, memory, and storage usage to adjust capacity as needed." Capacity Planning,"Set up alerts for approaching capacity limits on critical resources." Capacity Planning,"Conduct regular capacity reviews to adjust resources proactively." Capacity Planning,"Collaborate with business units to understand future workload requirements." Capacity Planning,"Evaluate storage solutions for backup retention and archive needs." Capacity Planning,"Document capacity changes and decisions for future reference." Capacity Planning,"Forecast database growth based on historical usage patterns." Capacity Planning,"Review cloud options to manage and scale capacity effectively." Capacity Planning,"Create a capacity upgrade plan for high-demand periods." Database Maintenance,"Schedule regular index rebuilds to improve query performance." Database Maintenance,"Perform statistics gathering to maintain data distribution accuracy." Database Maintenance,"Review and delete unused or outdated data to free up space." Database Maintenance,"Optimize database parameters based on performance reviews." Database Maintenance,"Conduct regular database defragmentation to improve efficiency." Database Maintenance,"Update maintenance documentation after each task." Database Maintenance,"Coordinate with application teams to schedule maintenance windows." Database Maintenance,"Monitor disk usage and clean up log files regularly." Database Maintenance,"Run integrity checks to ensure database consistency." Database Maintenance,"Set up automated maintenance jobs where possible." Change Management,"Document all proposed changes to database configurations." Change Management,"Review and approve change requests based on impact assessment." Change Management,"Coordinate with stakeholders to schedule changes during low-usage times." Change Management,"Create a rollback plan for each change to mitigate risks." Change Management,"Communicate scheduled changes to affected teams and stakeholders." Change Management,"Ensure all changes are tested in a non-production environment." Change Management,"Monitor the database closely after implementing changes." Change Management,"Log changes in a change management system for future reference." Change Management,"Review the impact of recent changes on database performance." Change Management,"Conduct a post-change review to address any issues." Disaster Recovery,"Document a comprehensive disaster recovery (DR) plan." Disaster Recovery,"Test DR plans regularly to ensure they are effective." Disaster Recovery,"Set up a standby database for quick failover in case of disasters." Disaster Recovery,"Train team members on DR procedures and responsibilities." Disaster Recovery,"Schedule DR drills to improve response times." Disaster Recovery,"Monitor standby databases for synchronization and health." Disaster Recovery,"Create checklists for DR processes for quick access." Disaster Recovery,"Coordinate with infrastructure teams to ensure DR readiness." Disaster Recovery,"Document DR tests and results for compliance." Disaster Recovery,"Update the DR plan annually to address new threats." Patch Management,"Create a regular patch schedule for all databases." Patch Management,"Test patches in a staging environment before production deployment." Patch Management,"Coordinate with application teams to minimize downtime during patching." Patch Management,"Document the patching process for future reference." Patch Management,"Monitor database performance post-patch to detect issues." Patch Management,"Establish a rollback plan for each patch application." Patch Management,"Track patch history to ensure compliance and update records." Patch Management,"Communicate patch schedules and impacts with stakeholders." Patch Management,"Set up alerts for new critical patches from Oracle." Patch Management,"Review patch notes for potential impacts on custom configurations." **How to Spend the 9-Hour Shift:** ```csv Time Slot,Activity First Hour,"Review critical incidents and check overnight alerts." Second Hour,"Monitor database health metrics and review backup logs." Third Hour,"Handle any ongoing incident resolutions and document updates." Fourth Hour,"Conduct routine security checks and monitor user access logs." Fifth Hour,"Focus on performance monitoring and optimization tasks." Sixth Hour,"Attend team meetings and collaborate on current issues." Seventh Hour,"Perform maintenance tasks or database health checks." Eighth Hour,"Work on documentation updates and change management reviews." Last Hour,"Prepare and review end-of-day status reports and updates."

vb

' VBScript to convert a text file to a CSV file

' Create a FileSystemObject to handle the file operations

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open the input text file for reading (Make sure the text file path is correct)

Set objTextFile = objFSO.OpenTextFile("C:\path\to\your\input.txt", 1)

' Create or overwrite the output CSV file

Set objCSVFile = objFSO.CreateTextFile("C:\path\to\your\output.csv", True)

' Read the file line by line

Do Until objTextFile.AtEndOfStream

    strLine = objTextFile.ReadLine

    ' Assuming columns are separated by a tab, space, or comma, adjust this if needed

    ' Here we use tabs (vbTab) as an example

    arrValues = Split(strLine, vbTab)

    

    ' If your text file uses spaces or commas, use this:

    ' arrValues = Split(strLine, " ") for space-separated columns

    ' arrValues = Split(strLine, ",") for comma-separated columns

    ' Convert the array to a CSV format by joining with commas

    strCSVLine = Join(arrValues, ",")

    ' Write the CSV line to the output file

    objCSVFile.WriteLine strCSVLine

Loop

' Close the files

objTextFile.Close

objCSVFile.Close

' Notify the user

MsgBox "Conversion to CSV completed successfully!"

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