DBHeights: September 2015

Wednesday, 23 September 2015

html mail to mailx from linux boxes.

mailx -s "Test HTML output in outlook / GMAIL
MIME-Version: 1.0
Content-Type: text/html" -r noreply@prologis.com zafrulla.khan@xyz.com <<-EOF
<pre>
<b>
<h1>
`cat /etc/group`
</h1>
</b>
</pre>
EOF

Check DATABASE Login before proceeding

########## Check to login before proceeding ##################

DBCHECK=/tmp/${ORACLE_SIDD}_check.log
SQL1="SELECT SYSDATE FROM DUAL";
sqlplus -s "$USERID/$PASSWORD@$CONNECT" <<-EOT > ${DBCHECK}
set heading off
set feedback off
${SQL1};
exit;
EOT

if [ $(cat ${DBCHECK}|grep "ORA-"|wc -l) -gt 0 ]; then
echo "Sorry Login issues with the database ${ORACLE_SIDD}".

message="[CRITICAL] [IB Messages Monitor: ${ORACLE_SID} DATABASE LOGIN FAILED] [${HOST_NAME}]"
/bin/mailx -s "$message" -r noreply@prologis.com ${MAILLIST} <<-EOFL

Hi PSFT ADMIN Team,

asynchronous_error_monitor.sh script for Production

Sorry, there was a problem in logging to the ${ORACLE_SIDD} Database. So, can not continue to check for asynchronous errors.
Please do fix this issue on Priority. Escalate to the DBA Manager for any serious concerns.

______________________________________________________________________________________________
Oracle Database Availability Status Report: ps -efx|grep pmon
----------------------------------------------------------------------------------------------
$(ps -efx|grep pmon)
______________________________________________________________________________________________

______________________________________________________________________________________________
Database Login ERROR Logs
----------------------------------------------------------------------------------------------
$(cat ${DBCHECK})
______________________________________________________________________________________________

______________________________________________________________________________________________
Please check the following for user ID: ${USERID}
----------------------------------------------------------------------------------------------
1) Please check login credentials
2) Check If the user account has been locked / expired
2) Check DB Wallets are open and allowing the logins
3) Check if the listeners and database are up and available.
______________________________________________________________________________________________

Note: Ignore this email if the DBA has intentionally stopped the ${ORACLE_SIDD} database for maintenance activities.

If you don't want to see this alert email again. Please remove the entry from the crontab on ${HOST_NAME} host.
Only do this once you have necessary approvals from the DBA Manager.

==============================================================================
Asynchronous error Monitoring script
==============================================================================
This script is located in the directory $(cd $(dirname $0);echo $PWD)
Host Name : $HOST_NAME
Script Name : "${0##*/}"
Version : 2.0
Script Author : "PwC Env Team"
===============================================================================

Thanks
I am back on Job.

EOFL
exit 1;

else
echo "I am good. The database is up and available";
fi
######################################

how to add one second to the date/time stamp in oracle

how to add one second to the date/time stamp in oracle

SELECT to_char('24-AUG-2015 13:00:00'/(24*60*60),'DD-MON-YYYY HH24:MI:SS') from dual;
SELECT to_char(to_date('24-AUG-2015 23:59:59','DD-MON-YYYY HH24:MI:SS')+1 /(24*60*60),'DD-MON-YYYY HH24:MI:SS') from dual;

Tuesday, 22 September 2015

asynchronous_error_monitor script to monitor async messages for any errors

#! /bin/bash
set -vx
##########################################################################################
# Name : asynchronous_error_monitor
# Author : Zafrulla Khan
# Date : Sep/23/2015
# Usage : asynchronous_error_monitor.ksh <ORACLE_SID>
# Deployed: Sep/23/2015
##########################################################################################

. $HOME/.bash_profile
ORACLE_SID=$1
HOST_NAME=$(hostname | awk -F_ '{ print $1}')
#UNIX_NODE=$(echo $HOST_NAME|tr 'a-z' 'A-Z')
MAILLIST=$(cat /home/oracle/scripts/ibmessages/DBA_EMAIL_IDS.txt)

# Get access Details from the file

INSTANCEFILE=/home/oracle/scripts/ibmessages/accessdetails.lis

export USERID=$(cat $INSTANCEFILE|grep "${HOST_NAME}"|awk -F: '{print $3}' -)
export PASSWORD=$(cat $INSTANCEFILE|grep "${HOST_NAME}"|awk -F: '{print $4}' -)
export CONNECT=$(cat $INSTANCEFILE|grep "${HOST_NAME}"|awk -F: '{print $1}' -)

echo $ORACLE_SID
echo $USERID
echo $PASSWORD
echo $CONNECT

SCRIPT_DIR=/home/oracle/scripts/ibmessages/queues/
LOG_DIR=/home/oracle/scripts/ibmessages/logs
OUTPUT_FILE=$SCRIPT_DIR/LOGS/sync_errors.log
LAST_CHECKED_DATE_TIME_FILE=${SCRIPT_DIR}/last_date_time_checked.lis
CURRENT_TIMESTAMP=$(/bin/date +%d-%b-%Y" "%H:%M:%S|tr '[:lower:]' '[:upper:]'); echo $CURRENT_TIMESTAMP

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus -s "$USERID/$PASSWORD@$CONNECT" <<EOT > /home/oracle/scripts/ibmessages/logs/IB_OPERATIONNAME.log
set heading off
set feedback off
select distinct IB_OPERATIONNAME from PSAPMSGSUBCON;
exit;
EOT

cat /home/oracle/scripts/ibmessages/logs/IB_OPERATIONNAME.log|grep -v ^$ | while read LAST_CHECKED_DATE_TIME_FILE
do
if [ ! -f ${SCRIPT_DIR}${LAST_CHECKED_DATE_TIME_FILE} ]
then echo "01-JAN-2015 00:00:00" > ${SCRIPT_DIR}${LAST_CHECKED_DATE_TIME_FILE}
fi

LAST_RUN_TIMESTAMP=$(cat ${SCRIPT_DIR}$LAST_CHECKED_DATE_TIME_FILE|grep -v ^$)

RETVAL_SQL=0;
RETVAL_SQL1=0;
RETVAL_SQL=$(
sqlplus -s $USERID/$PASSWORD@$CONNECT <<-EOS
set echo off heading off pagesize 1000 feedback off linesize 150 serveroutput on
select count(*) from PSAPMSGSUBCON where CREATEDTTM between TO_DATE('${LAST_RUN_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('${CURRENT_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND IB_OPERATIONNAME='${LAST_CHECKED_DATE_TIME_FILE}' and STATUSSTRING='ERROR';
EOS
)

RETVAL_SQL1=$(echo $RETVAL_SQL|grep -v ^$)

if [ $RETVAL_SQL1 != 0 ]
then
/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus -s $USERID/$PASSWORD@$CONNECT <<-EOS
spool /home/oracle/scripts/ibmessages/logs/async_errors.log
set echo off heading on pagesize 100 feedback on linesize 200 serveroutput on;
col LASTUPDDTTM format a32;
col CREATEDTTM format a32;
select IBTRANSACTIONID, IB_OPERATIONNAME, CREATEDTTM,LASTUPDDTTM, STATUSSTRING from PSAPMSGSUBCON where CREATEDTTM between TO_DATE('${LAST_RUN_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('${CURRENT_TIMESTAMP}','DD-MON-YYYY HH24:MI:SS') AND IB_OPERATIONNAME='${LAST_CHECKED_DATE_TIME_FILE}' and STATUSSTRING='ERROR';
spool off;
EOS

/bin/mailx -s " $ORACLE_SID - [ERROR ASYNC IB MESSAGE] - ${LAST_CHECKED_DATE_TIME_FILE} ERRORS = $RETVAL_SQL1" -r noreply@prologis.com $MAILLIST <<-EOF
Hello PSFT ADMIN Team,

Asynchronous error monitoring script for Production

Found $RETVAL_SQL1 synchronous error(s) in ${LAST_CHECKED_DATE_TIME_FILE} queue

==============================================================================
Between DateTime Interval
------------------------------------------------------------------------------
From : ${LAST_RUN_TIMESTAMP}
To : ${CURRENT_TIMESTAMP}

`cat /home/oracle/scripts/ibmessages/logs/async_errors.log`

==============================================================================

==============================================================================
Asynchronous error Monitoring script
==============================================================================
This script is located in the directory $(cd $(dirname $0);echo $PWD)
Host Name : $UNIX_NODE
Script Name : "${0##*/}"
Version : 2.0
Script Author : "PwC Env Team"
===============================================================================

I'm back on Job.
synchronous error monitor script version 2.0

EOF

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus -s $USERID/$PASSWORD@$CONNECT <<-EOS > ${SCRIPT_DIR}${LAST_CHECKED_DATE_TIME_FILE}
set echo off heading off feedback off;
SELECT TO_CHAR(max(CREATEDTTM)+1/(24*60*60),'DD-MON-YYYY HH24:MI:SS') from PSAPMSGSUBCON where IB_OPERATIONNAME='${LAST_CHECKED_DATE_TIME_FILE}' and STATUSSTRING='ERROR';
EOS

fi

done

Monday, 7 September 2015

Authentication token is no longer valid; new one required You (psadm2) are not allowed to access to (crontab) because of pam configuration.

solution.

[root@localhost ~]# cat /etc/cron.allow
psadm2

passwd psadm2 (Set a new password)

[psadm2@localhost ~]$ crontab -l
10 07 * * * nohup sh /home/psadm2/bounce_script.sh &

[psadm2@localhost ~]$ cat /home/psadm2/bounce_script.sh
#!/bin/bash

. /home/psadm2/.bash_profile

export LOG_FILE="/tmp/APPDOM_`date '+%d-%h-%Y'`"

echo "======================`date '+%d-%h-%Y'`======================" > $LOG_FILE 2>&1
echo "Bouncing Appserver PUM APPDOM" >> $LOG_FILE 2>&1
echo "================================================" >> $LOG_FILE 2>&1

# Stop AppServers
psadmin -c shutdown! -d APPDOM >> $LOG_FILE 2>&1
psadmin -c cleanipc -d APPDOM >> $LOG_FILE 2>&1
rm -rf /home/psadm2/psft/pt/8.54/appserv/APPDOM/CACHE >> $LOG_FILE 2>&1
psadmin -c configure -d APPDOM >> $LOG_FILE 2>&1

# Start Appsrevers
psadmin -c boot -d APPDOM >> $LOG_FILE 2>&1

echo "================================================" >> $LOG_FILE 2>&1

Oracle EL Repositories for PUM Images. EL5 Only.

[root@localhost yum.repos.d]# pwd
/etc/yum.repos.d
[root@localhost yum.repos.d]#

public-yum-el5.repo

[el5_latest]

name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/latest/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

[el5_ga_base]
name=Oracle Linux $releasever GA installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/0/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_u1_base]
name=Enterprise Linux $releasever Update 1 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/1/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_u2_base]
name=Enterprise Linux $releasever Update 2 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/2/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_u3_base]
name=Enterprise Linux $releasever Update 3 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/3/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_u4_base]
name=Enterprise Linux $releasever Update 4 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/4/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_u5_base]
name=Enterprise Linux $releasever Update 5 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/5/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u5_base]
name=Oracle Linux $releasever Update 5 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/5/base/x86_64/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u6_base]
name=Oracle Linux $releasever Update 6 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/6/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u7_base]
name=Oracle Linux $releasever Update 7 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/7/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u8_base]
name=Oracle Linux $releasever Update 8 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/8/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u9_base]
name=Oracle Linux $releasever Update 9 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/9/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u10_base]
name=Oracle Linux $releasever Update 10 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/10/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_u11_base]
name=Oracle Linux $releasever Update 11 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/11/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_addons]
name=Enterprise Linux $releasever Add ons ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/addons/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_oracle_addons]
name=Oracle Software addons for Enterprise Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/oracle_addons/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_UEK_latest]
name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/UEK/latest/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

[ol5_UEK_base]
name=Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/UEK/base/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[el5_unsupported]
name=Productivity Applications for Enterprise Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/unsupported/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

[ol5_spacewalk20_client]
name=Spacewalk Client 2.0 for Oracle Linux 5 ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/spacewalk20/client/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=0

Sunday, 6 September 2015

Error code: ssl_error_weak_server_ephemeral_dh_key (PeopleSoft Weblogic)

PeopleSoft Client Browser Problem : After Chrome and Firefox Update.

Fix:

1. Go to directory <PS_HOME>/webserv/<DOMAIN_NAME>/config/

2. Make a backup copy of config.xml.

3. Edit config.xml
a. Locate the SSL configuration portion. It begins with:
<SSL>
b. Add the cipher suites after the 'enabled' parameter. Below is an example:
<ssl>
<name>PIA</name>
<enabled>true</enabled>
<ciphersuite>TLS_RSA_WITH_RC4_128_SHA</ciphersuite>
<ciphersuite>TLS_RSA_WITH_RC4_128_MD5</ciphersuite>
<ciphersuite>TLS_RSA_WITH_AES_128_CBC_SHA</ciphersuite>
<listen-port>443</listen-port>

Note: it is important that you add the 'ciphersuite' entries between the 'enabled' entry and the 'listen-port' entry within the 'ssl' section.

4. Restart WebLogic to pick up the changes (If using a WebLogic multi-server domain, first restart the WebLogicAdmin server, then the PIA).
Now WebLogic will only begin SSL connections with browsers that can support 128 bit encryption or higher, using the above cipher suites.
Note: The WebLogic logs do not indicate which cipher suites are being used. So if you want to validate, we suggest using a third party auditing tool.

Wednesday, 2 September 2015

Reverse Proxy Setting using Apache 2.2

Reverse Proxy Setting using Apache 2.2

LoadModule weblogic_module /etc/httpd/modules/lib/mod_wl.so

<IfModule mod_weblogic.c>
WebLogicHost localhost.localdomain
WebLogicPort 8000
MatchExpression /
WLLogFile /tmp/wl-proxy.log
</IfModule>

<VirtualHost *:8080>
<IfModule mod_weblogic.c>
WebLogicHost 192.168.56.101
WebLogicPort 8000
MatchExpression /
WLLogFile /tmp/wl-proxy.log
</IfModule>
</VirtualHost>

<VirtualHost *:8090>
<IfModule mod_weblogic.c>
WebLogicHost 192.168.56.103
WebLogicPort 8000
MatchExpression /
WLLogFile /tmp/wl-proxy.log
</IfModule>