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."