OEM target property update 5

 #!/bin/bash


##########################################

# Part 1: Fetch Missing/Incorrect Properties

##########################################


# Define SQL query to fetch missing/incorrect properties

SQL_QUERY_MISSING="

SELECT 

  h.target_name,

  tp.property_name,

  tp.property_value,

  cd.support_group,

  cd.install_status,

  cd.oracle_home,

  cd.u_cmdb_ci_used_for,

  cd.u_cmdb_owner_group,

  cd.name,

  cd.correlation_id,

  cd.version

FROM 

  sysman.mgmt\$target h

  LEFT JOIN mgmt\$target_properties tp 

    ON h.target_name = tp.target_name

    AND tp.property_name IN ('udtp_2', 'orcl_gtp_contact', 'orcl_gtp_lifecycle_status')

  LEFT JOIN cmDAILY cd 

    ON h.target_name = cd.name

WHERE 

  (tp.property_value IS NULL OR tp.property_name IS NULL)

  OR (tp.property_name = 'orcl_gtp_contact' AND tp.property_value != cd.support_group)

  OR (tp.property_name = 'orcl_gtp_lifecycle_status' AND tp.property_value != cd.install_status)

  OR (tp.property_name = 'udtp_2' AND tp.property_value IS NULL)

  OR cd.oracle_home IS NULL;

"


# Execute SQL query and store the output in a temporary file

sqlplus -S user/password@your_db <<EOF > /tmp/missing_properties.txt

$SQL_QUERY_MISSING

EOF


##########################################

# Part 2: Update Support Group and Lifecycle Properties

##########################################


# Step 1: Fetch distinct host and support group for updating 'orcl_gtp_contact'

SQL_QUERY_SUPPORT_GROUP="SELECT DISTINCT SUBSTR(name, INSTR(name, '@') + 1) AS host_name, support_group 

                         FROM O_ADMIN.CmDAILY;"


# Execute SQL query for support group and store in a file

sqlplus -S user/password@your_db <<EOF > /tmp/hosts_support_groups.txt

$SQL_QUERY_SUPPORT_GROUP

EOF


# Step 2: Fetch distinct child and lifecycle status for updating 'orcl_gtp_lifecycle_status'

SQL_QUERY_LIFECYCLE="SELECT DISTINCT child, child_u_cmdb_ci_used_for 

                     FROM N.C_DB_host_DAILY;"


# Execute SQL query for lifecycle and store in a file

sqlplus -S user/password@your_db <<EOF > /tmp/child_lifecycle.txt

$SQL_QUERY_LIFECYCLE

EOF


# Loop through each result from the missing properties query and update

while IFS=" " read -r target_name property_name property_value support_group install_status oracle_home; do

  # Skip header or empty lines

  if [[ "$target_name" == "TARGET_NAME" || -z "$target_name" ]]; then

    continue

  fi


  # SQL query to fetch associated target types (oracle_database, rac_database, host, agent, listener) for the target

  SQL_TARGETS="SELECT assoc_target_name, target_type 

               FROM sysman.mgmt\$target_associations 

               WHERE assoc_target_name = '$target_name';"

  

  # Execute SQL to get associated targets

  sqlplus -S user/password@your_db <<EOF > /tmp/associated_targets.txt

  $SQL_TARGETS

EOF


  # Loop through each associated target and generate the appropriate emcli command

  while IFS=" " read -r assoc_target_name target_type; do

    # Skip header or empty lines

    if [[ "$assoc_target_name" == "ASSOC_TARGET_NAME" || -z "$assoc_target_name" ]]; then

      continue

    fi


    # Generate the appropriate emcli command based on the missing/incorrect property

    if [[ "$property_name" == "orcl_gtp_contact" && "$property_value" != "$support_group" ]]; then

      emcli modify_target -name="$assoc_target_name" \

                          -type="$target_type" \

                          -properties="orcl_gtp_contact->$support_group" \

                          -separator-properties="|" \

                          -subseparator-properties="->" \

                          -on_agent

      echo "Updated orcl_gtp_contact for $assoc_target_name ($target_type) to $support_group"


    elif [[ "$property_name" == "orcl_gtp_lifecycle_status" && "$property_value" != "$install_status" ]]; then

      emcli modify_target -name="$assoc_target_name" \

                          -type="$target_type" \

                          -properties="orcl_gtp_lifecycle_status->$install_status" \

                          -separator-properties="|" \

                          -subseparator-properties="->" \

                          -on_agent

      echo "Updated orcl_gtp_lifecycle_status for $assoc_target_name ($target_type) to $install_status"


    elif [[ "$property_name" == "udtp_2" && -z "$property_value" ]]; then

      hosting_value="correct_value" # Replace with actual logic for the correct value

      emcli modify_target -name="$assoc_target_name" \

                          -type="$target_type" \

                          -properties="udtp_2->$hosting_value" \

                          -separator-properties="|" \

                          -subseparator-properties="->" \

                          -on_agent

      echo "Updated udtp_2 for $assoc_target_name ($target_type) to $hosting_value"

    fi


  done < /tmp/associated_targets.txt


done < /tmp/missing_properties.txt


# Process Support Group from the file and generate emcli commands

while IFS=" " read -r host_name support_group; do

  # SQL query to fetch associated target types (oracle_database, rac_database, host, agent, listener) for the host

  SQL_TARGETS="SELECT assoc_target_name, target_type 

               FROM sysman.mgmt\$target_associations 

               WHERE assoc_target_name = '$host_name';"

  

  # Execute SQL to get associated targets

  sqlplus -S user/password@your_db <<EOF > /tmp/associated_targets.txt

  $SQL_TARGETS

EOF


  # Loop through each associated target and update 'orcl_gtp_contact'

  while IFS=" " read -r assoc_target_name target_type; do

    emcli modify_target -name="$assoc_target_name" \

                        -type="$target_type" \

                        -properties="orcl_gtp_contact->$support_group" \

                        -separator-properties="|" \

                        -subseparator-properties="->" \

                        -on_agent

    echo "Updated orcl_gtp_contact for $assoc_target_name ($target_type) to $support_group"

  done < /tmp/associated_targets.txt

  

done < /tmp/hosts_support_groups.txt


# Process Lifecycle status from the file and generate emcli commands

while IFS=" " read -r child child_u_cmdb_ci_used_for; do

  # SQL query to fetch associated target types for the child

  SQL_TARGETS="SELECT assoc_target_name, target_type 

               FROM sysman.mgmt\$target_associations 

               WHERE assoc_target_name = '$child';"

  

  # Execute SQL to get associated targets

  sqlplus -S user/password@your_db <<EOF > /tmp/associated_targets.txt

  $SQL_TARGETS

EOF


  # Loop through each associated target and update 'orcl_gtp_lifecycle_status'

  while IFS=" " read -r assoc_target_name target_type; do

    emcli modify_target -name="$assoc_target_name" \

                        -type="$target_type" \

                        -properties="orcl_gtp_lifecycle_status->$child_u_cmdb_ci_used_for" \

                        -separator-properties="|" \

                        -subseparator-properties="->" \

                        -on_agent

    echo "Updated orcl_gtp_lifecycle_status for $assoc_target_name ($target_type) to $child_u_cmdb_ci_used_for"

  done < /tmp/associated_targets.txt


done < /tmp/child_lifecycle.txt

Comments

Post a Comment

Popular posts from this blog

SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database] username=your_username password=your_password database=your_database [files] csv_file=/path/to/your/file.csv log_file=/path/to/your/log_file.log inserted_rows_file=/path/to/your/inserted_rows.log updated_rows_file=/path/to/your/updated_rows.log ********** #!/bin/bash # Load configuration source config.cfg # Variables CSV_FILE=$csv_file USERNAME=$username PASSWORD=$password DATABASE=$database MASTER_TABLE="master_table" CHILD_TABLE="child_table" LOG_FILE=$log_file INSERTED_ROWS_FILE=$inserted_rows_file UPDATED_ROWS_FILE=$updated_rows_file # Logging function log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE } log "Starting data load process." # SQL Loader Control File for child table CONTROL_FILE="control_file.ctl" # Create control file for child table cat ...
Read more

Db Unix

-- Insert new records into master table INSERT INTO $master_table (     parent,     parent_sys_class_name,     child_sys_class_name,     child_u_cmdb_ci_used_for,     parent_correlation_id,     type,     child_correlation_id,     child,     modified_date ) SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.parent_correlation_id,     c.type,     c.child_correlation_id,     c.child,     c.modified_date FROM $child_table c LEFT JOIN $master_table m ON c.child_correlation_id = m.child_correlation_id WHERE m.child_correlation_id IS NULL; -- Log inserted rows SPOOL $inserted_rows_file SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.pare...
Read more

Standby redo log add

********* #!/bin/bash # Function to run SQL*Plus commands run_sql() {     sqlplus -s / as sysdba <<EOF     SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF     $1     EXIT EOF } # Get the max group, max thread, and max size from the v$log and v$logfile views redo_info=$(run_sql "SELECT MAX(l.group#), MAX(l.thread#), MAX(l.bytes/1024/1024)  FROM v\$log l, v\$logfile f  WHERE f.group# = l.group#;") # Extract the max group, max thread, and max size values max_group=$(echo "$redo_info" | awk '{print $1}') max_thread=$(echo "$redo_info" | awk '{print $2}') max_size=$(echo "$redo_info" | awk '{print $3}') # Get the number of redo groups per thread groups_per_thread=$(run_sql "SELECT COUNT(DISTINCT group#) FROM v\$log WHERE thread#=1;") # Get the recovery file destination reco_dest=$(run_sql "SHOW PARAMETER db_recovery_file_dest") reco_dest=$(echo "$reco_dest" | awk 'NR==2 {print $3}' |...
Read more