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