#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
Sunday, February 26, 2023
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"
Tuesday, February 14, 2023
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
Sunday, February 5, 2023
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
Saturday, February 4, 2023
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
Friday, February 3, 2023
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"