Friday, March 23, 2018

Script To Backup & Delete the Database logs

# This script Backup & Delete the database logs.
# To be run by ORACLE user
# ###########
# Description:
# ###########
echo
echo "================================================"
echo "This script Backs up & Deletes the database logs ..."
echo "================================================"
echo
sleep 1

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe "|" as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB="\-MGMTDB|ASM"                           #Excluded INSTANCES [Will not get reported offline].

# ###########################
# Listing Available Instances:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
 then
   echo No Database Running !
   exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
 then
   export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
 then
    echo
    echo "Select the Instance You Want To Backup & Delete It's Logs:[Enter the Number]"
    echo "----------------------------------------------------------"
    select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
     do
if [ -z "${REPLY##[0-9]*}" ]
then
          export ORACLE_SID=$DB_ID
  echo Selected Instance:
  echo "********"
  echo $DB_ID
          echo "********"
  break
else
  export ORACLE_SID=${REPLY}
  break
fi
     done

fi
# Exit if the user selected a Non Listed Number:
if [ -z "${ORACLE_SID}" ]
then
  echo "You've Entered An INVALID ORACLE_SID"
  exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
  PMON_PID=`pgrep  -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
  export PMON_PID
  ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
  export ORACLE_HOME
#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
## If OS is Linux:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME
fi
#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
  ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
  export ORACLE_HOME
#echo "ORACLE_HOME from environment  is ${ORACLE_HOME}"
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
  export ORACLE_HOME
#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
  ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
  export ORACLE_HOME
#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
  echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
  echo "e.g."
  echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
exit
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
  echo ""
  echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
  echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
  echo "Script Terminated!"
  exit
fi

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

        if [ -f ./login.sql ]
         then
mv ./login.sql   ./login.sql_NeutralizedBy${SCRIPT_NAME}
        fi

        if [ -f ${USR_ORA_HOME}/login.sql ]
         then
mv ${USR_ORA_HOME}/login.sql   ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME}
        fi

# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo $VAL1| perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
DB_NAME_LOWER=$( echo "$DB_NAME_UPPER" | tr -s  '[:upper:]' '[:lower:]' )
export DB_NAME_UPPER
export DB_NAME_LOWER

# DB_NAME is Uppercase or Lowercase?:

     if [ -f $ORACLE_HOME/diagnostics/${DB_NAME_UPPER} ]
        then
                DB_NAME=$DB_NAME_UPPER
export DB_NAME
        else
                DB_NAME=$DB_NAME_LOWER
                export DB_NAME
     fi

# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user's profile if not set:

if [ -z "${ORACLE_BASE}" ]
 then
   ORACLE_BASE=`grep 'ORACLE_BASE=\/' $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
   export ORACLE_BASE
fi

# #########################
# Getting ALERTLOG path:
# #########################
VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <set pages 0 feedback off;
prompt
SELECT value from v\$parameter where NAME='background_dump_dest';
exit;
EOF
)
BDUMP=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
export BDUMP
DUMP=`echo ${BDUMP} | sed 's/\/trace//g'`
export DUMP
CDUMP=${DUMP}/cdump
export CDUMP
ALERTDB=${BDUMP}/alert_${ORACLE_SID}.log
export ALERTDB

# #########
# Variables:
# #########
echo ""
echo "Please Enter The Full Path of Backup Location: [/tmp]"
echo "============================================="
read LOC1
# Check if No location provided:
if [ -z ${LOC1} ]; then
          LOC1=/tmp
          export LOC1
          echo "Database Logs Backup Will Be Saved Under: ${LOC1}"
else
  export LOC1
  echo "Database Logs Backup Will Be Saved Under: ${LOC1}"
fi
# Check if provided location path is not exist:
        if [ ! -d ${LOC1} ]; then
  echo ""
  echo "Location Path \"${LOC1}\" is NOT EXIST!"
          echo "Script Terminated!"
  exit
        fi


# Setting a Verifier:
echo ""
echo "Are You SURE to Backup & Remove the logs of Database \"${ORACLE_SID}\" and its Listener: [Y|N] Y"
echo "================================================================================="
while read ANS
  do
        case $ANS in
        ""|y|Y|yes|YES|Yes) echo;echo "Backing up & removing DB & Listener Logs ...";sleep 1;echo;break ;;
        n|N|NO|no|No) echo; echo "Script Terminated !";echo; exit; break ;;
        *) echo;echo "Please enter a VALID answer [Y|N]" ;;
        esac
  done

BKP_BASE=${LOC1}
export BKP_BASE
BKP_LOC_DB=$BKP_BASE/${ORACLE_SID}_logs/`uname -n`/`date '+%b_%Y'`
export BKP_LOC_DB
DB=${DB_NAME}
export DB
INS=${ORACLE_SID}
export INS

# ######################
# Getting Listener name:
# ######################
LSNR_COUNT=$( ps -ef|grep tnslsnr|grep -v grep|wc -l )

if [ ${LSNR_COUNT} -eq 1 ]
then
   LSNR_NAME=$( ps -ef|grep tnslsnr|grep -v grep|awk '{print $(9)}' )
else
           LSNR_NAME=$( ps -ef|grep tnslsnr|grep -i "${ORACLE_SID} "|grep -v grep|awk '{print $(9)}' )
fi

        if [ -z "${LSNR_NAME}" ]
         then
           LSNR_NAME=LISTENER
        fi

LISTENER_NAME=${LSNR_NAME}

# Creating folder holds the logs:
mkdir -p ${BKP_LOC_DB}

# Backup & Delete DB logs:
# #######################
        if [ ! -d ${DUMP} ]
         then
          echo "The Parent Log Dump location cannot be Found!"
  exit
        fi

tail -1000 ${ALERTDB} > ${BKP_LOC_DB}/alert_${INS}.log.keep
gzip -f9 ${BDUMP}/alert_${INS}.log
mv ${BDUMP}/alert_${INS}.log.gz    ${BKP_LOC_DB}
mv ${BKP_LOC_DB}/alert_${INS}.log.keep ${BDUMP}/alert_${INS}.log
#tar zcvfP ${BKP_LOC_DB}/${INS}-dump-logs.tar.gz ${DUMP}
find ${DUMP} -name '*' -print > ${BKP_LOC_DB}/dump_files_list.txt
tar zcvfP ${BKP_LOC_DB}/${INS}-dump-logs.tar.gz --files-from ${BKP_LOC_DB}/dump_files_list.txt


# Delete DB logs older than 5 days:
find ${BDUMP}         -type f -name '*.trc' -o -name '*.trm' -o -name '*.log' -mtime +5 -exec rm -f {} \;
find ${DUMP}/alert    -type f -name '*.xml'                                   -mtime +5 -exec rm -f {} \;
find ${DUMP}/incident -type f -name '*.trc' -o -name '*.trm' -o -name '*.log' -mtime +5 -exec rm -f {} \;
find ${CDUMP}         -type f -name '*.trc' -o -name '*.trm' -o -name '*.log' -mtime +5 -exec rm -f {} \;

# Backup & Delete listener's logs:
# ################################
#LISTENER_HOME=`ps -ef|grep -v grep|grep tnslsnr|grep -i ${LSNR_NAME}|awk '{print $(NF-2)}' |sed -e 's/\/bin\/tnslsnr//g'|grep -v sed|grep -v "s///g"|head -1`
LISTENER_HOME=`ps -ef|grep -v grep|grep tnslsnr|grep "${LSNR_NAME} "|awk '{print $(8)}' |sed -e 's/\/bin\/tnslsnr//g'|grep -v sed|grep -v "s///g"|head -1`
TNS_ADMIN=${LISTENER_HOME}/network/admin
export TNS_ADMIN
LSNLOGDR=`${LISTENER_HOME}/bin/lsnrctl status ${LISTENER_NAME}|grep "Listener Log File"| awk '{print $NF}'| sed -e 's/\/alert\/log.xml//g'`
LISTENER_LOG=${LSNLOGDR}/trace/${LISTENER_NAME}.log
echo LISTENER_HOME: $LISTENER_HOME
echo TNS_ADMIN: $TNS_ADMIN
echo LISTENER_LOG: $LISTENER_LOG

# Determine if the listener name is in Upper/Lower case:
        if [ -f ${LISTENER_LOG} ]
         then
          # Listner_name is Uppercase:
          LISTENER_NAME=$( echo ${LISTENER_NAME} | perl -lpe'$_ = reverse' |perl -lpe'$_ = reverse' )
          LISTENER_LOG=${LSNLOGDR}/trace/${LISTENER_NAME}.log
         else
          # Listener_name is Lowercase:
          LISTENER_NAME=$( echo "${LISTENER_NAME}" | tr -s  '[:upper:]' '[:lower:]' )
          LISTENER_LOG=${LSNLOGDR}/trace/${LISTENER_NAME}.log
        fi

if [ ! -d ${LSNLOGDR} ]
then
          echo 'Listener Logs Location Cannot be Found!'
        fi
tar zcvfP ${BKP_LOC_DB}/${LISTENER_NAME}_trace.tar.gz  ${LSNLOGDR}/trace
tar zcvfP ${BKP_LOC_DB}/${LISTENER_NAME}_alert.tar.gz  ${LSNLOGDR}/alert
tail -10000 ${LSNLOGDR}/trace/${LISTENER_NAME}.log > ${BKP_LOC_DB}/${LISTENER_NAME}.log.keep
find ${LSNLOGDR}/trace -type f -name '*.trc' -o -name '*.trm' -o -name '*.log' -exec rm -f {} \;
find ${LSNLOGDR}/alert -type f -name '*.xml'                                   -exec rm -f {} \;
mv ${BKP_LOC_DB}/${LISTENER_NAME}.log.keep   ${LSNLOGDR}/trace/${LISTENER_NAME}.log

# ############################
# Backup & Delete AUDIT logs:
# ############################
# Getting Audit Files Location:
# ############################
VAL_AUD=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <set pages 0 feedback off;
prompt
SELECT value from v\$parameter where NAME='audit_file_dest';
exit;
EOF
)
AUD_LOC=`echo ${VAL_AUD} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

        if [ ! -d ${AUD_LOC} ]
         then
          echo 'Audit Files Location Cannot be Found!'
  exit
        fi

#tar zcvfP ${BKP_LOC_DB}/audit_files.tar.gz ${AUD_LOC}/${ORACLE_SID}_*
find ${AUD_LOC} -type f -name '${ORACLE_SID}_*' -print > ${BKP_LOC_DB}/audit_files_list.txt
tar zcvfP ${BKP_LOC_DB}/${INS}-audit-logs.tar.gz --files-from ${BKP_LOC_DB}/audit_files_list.txt


# Delete Audit logs older than 5 days
#find ${AUD_LOC}/${ORACLE_SID}_* -type f -mtime +5 -exec rm {} \;
find ${AUD_LOC} -name '${ORACLE_SID}_*' -type f -mtime +5 -exec rm -f {} \;

echo ""
echo "------------------------------------"
echo "Old logs have been backed up under: ${BKP_LOC_DB}"
echo "The Last 5 Days Logs have been KEPT."
echo "CLEANUP COMPLETE."
echo "------------------------------------"
echo

# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
        if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
         then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME}  ./login.sql
        fi

        if [ -f ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ]
         then
mv ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME}  ${USR_ORA_HOME}/login.sql
        fi

# #############
# END OF SCRIPT
# #############

Wednesday, February 26, 2014

TNSnames and LISTENER.ora


Oracle Networking – TNSnames , Listener.ora

        UNDERSTANDING NETWORK CONFIGURATION
Oracle Net is a software component that resides on the client and the Oracle database server Or Oracle DBA Machine. It is responsible for establishing and maintaining the connection between the client application and the server, as well as exchanging messages between them.
For the client application and a database to communicate, the client application must specify location details for the database it wants to connect to and the database must provide some sort of identification, or address.
        WHAT IS AN ORACLE DATABASE LISTENER?
On the database server side,an Oracle Database Listener is an Oracle Database Process which "listen" for for users (clients) connecting to the database. The listener process, either creates a dedicated server process for each user or to a shared server process that handles many users.
It is configured in a file named listener.ora, with a protocol address that identifies the database.
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=my-server) (PORT=1521)))This example shows a TCP/IP protocol address that specifies the host machine of the listener and a port number.

Here is an example of $ORACLE_HOME/network/admin/listener.ora:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\Oracle_10g)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = cata1)(PORT = 1521))
)
)
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = cata1)(PORT = 1522))
)
)
Here 2 listeners are configured for this server.
        CONFIGURE THE CLIENT
When a client configured with the same protocol address broadcasts a request for a connection, the listener on the server machine or Oracle DBAmachine brokers the request and forwards it to the Oracle database.
The client uses a CONNECT DESCRIPTOR to specify the database it wants to connect to. This connect descriptor contains-- a protocol address and --a database service name.
A database can have multiple services defined, so a specific one must be specified for the connection. In the case of the preconfigured database that you installed, there is only one service, the name of which defaults to the global database name.The following example shows a CONNECT DESCRIPTOR that enables client to connect to a database service called live.
SCOTT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cata1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = live)
    )
  )


Connection Requests:-
Users initiate a connection request by providing a connect string. A CONNECT STRING includes a username and password, along with a connect identifier.
This connect identifier can be the connect descriptor itself, or a name that resolves to the connect descriptor. One of the most common connect identifiers is a net service name, a simple name for a service.
The following example shows a connect string that uses net service name live as the connect identifier
CONNECT sagar/verma@live



        SOME QUESTIONS:-
Q) If I configured many listeners on my server, how could I see which is used ?
Ans)Using LSNRCTL> show current listener command.
       Other useful commands:-
       lsnrctl status       lsnrctl start       lsnrctl stop

Q)How could I see if the listener is running ?

AWR and ADDM Reports

AWR and ADDM - best explanation
The AUTOMATIC WORKLOAD repository is a source of information for several other Oracle 10g features including:-Automatic Database Diagnostic Monitor-SQL Tuning Advisor-Undo Advisor-Segment Advisor
ADDM analyze snapshots taken by AWR.
Automatic database diagnostic monitoring is enabled by default and is controlled by the CONTROL_MANAGEMENT_PACK_ACCESS and the STATISTICS_LEVEL initialization parameters.
The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter should be set to DIAGNOSTIC+TUNING
The STATISTICS_LEVEL initialization parameter should be set to the TYPICAL (default) or ALL to enable automatic database diagnostic monitoring.
Setting the DBIO_EXPECTED Parameter:::ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTEDThe value of DBIO_EXPECTED is the average time it takes to read a single database block, in microseconds
Set the value one time for all subsequent ADDM executions::SQL>EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 8000);
Managing AWR Snapshots::::By default, the Automatic Workload Repository (AWR) generates snapshots of performance data once every hour,and retains the statistics in the workload repository for 8 days.You can change the default values for both the snapshot interval and the retention period.The data in the snapshot interval is analyzed by ADDM. ADDM compares the difference between snapshots.
Modifying Snapshot Settings::::By default, AWR generates snapshots of performance data once every hour. Alternatively,you can modify the default values of both the interval between snapshots and their retention period.
EXAMPLE:--- This causes the repository to refresh every 15 minutes-- and retain all data for 2 weeks i.e 20160 min.sql>Exec dbms_workload_repository.modify_snapshot_settings(retention=>20160, interval=> 15);now,seesql> select DBID,SNAP_INTERVAL,RETENTION from dba_hist_wr_control;sql>@E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awwrpti.sql
ADDM report has following sections:
     Problem: Finding that describe the root cause of database performance issue.
     Symptom: Finding that contain information that often lead to one or more problem finding.
     Information: Findings that are used to report non-problem area of the system.
     Recommendations: Composed of action & rationales.
SQL> Execute DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM','DBIO_EXPECTED',8000);SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sqlsql>desc DBA_ADVISOR_FINDINGS:This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.
DBA_ADVISOR_RECOMMENDATIONS:This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

Oracle responsibility not visible in R12

From 12.1.1 you do not have to run the Synchronize WF Local Tables any more - all of the user roles and changes via the functional process - is processed by a business event in WF Java Deferred. The Workflow Agent listeners process them and all of the user role information is stored in the Workflow Directory Services tables now.

Execute the concurrent program “Workflow Directory Services User/Role Validation” with below parameters:
p_BatchSize – 10000 (Default Value 10000)
p_Check_Dangling – Yes (Default value No)
Add missing user/role assignments – Yes (Default Value No)
Update WHO columns in WF tables – No (Default Value No)
This concurrent program syncs all the user and role related WF tables with latest information viz., WF_LOCAL_ROLES, WF_LOCAL_USER_ROLES, WF_USER_ROLE_ASSIGNMENTS etc..
If after that the problem is not solved yet, then it’s possible to use the System Administrator responsibility: Workflow -> Oracle Applications Manager -> Workflow Manager
Selecting ‘Service Components’ and starting the following listeners:- Workflow Deferred Notification Agent Listener
- Workflow Error Agent Listener
- Workflow Java Deferred Agent Listener
- Workflow Java Error Agent Listener

How to Enable Automatic Compilation of JSP pages in R12

1. Login into E-Business suite and select System Administrator responsibility

2. Select function AutoConfig (under Oracle Applications Manager)
For each web tier server perform the following:
Click on pencil icon under Edit Parameters
Select tab System
Expand section jtff_server

3. Change value for the entry s_jsp_main_mode from justrun to recompile
Confirm the change by clicking Save button

4. Run AutoConfig to propagate the changes to the configuration files
Verify that the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml  has the following:

Check param-name "main_mode" under init-param variables
Its changed into "recompile"

5. Restart the web tier services

Clear Cache in R12

There are two ways to clear cache in R12

1. Functional level (individual product/all product cache)
Functional Administrator -> Core Services -> Caching Framework -> Global Configuration -> "Go to specific cache or Clear All Cache"


2. Server level
In R12 apache bounce (which is actually opmn services here) takes a little more time then 11i, and we too have to take care of few things.
Basic steps for a clear cache bounce is as below:-
Set you application environment and go to $ADMIN_SCRIPTS_HOME

  1. ./adopmnctl.sh stopall
  2. wait for command to complete
  3. ./adopmnctl.sh status -l
  4. cd $COMMON_TOP/_pages
  5. ls -l|wc -l (note it down)
  6. rm -rf * (to remove all files)
    Note:- For step 4 to 6, better to take backup of _pages by renaming it, and create a new directory as _pages at the same place.
  7. ls -l|wc -l (it should be 0 now)
  8. cd $FND_TOP/patch/115/bin
  9. which perl (should come from $IAS_ORACLE_HOME/perl/bin)
  10. ojspCompile.pl --compile --flush -p 10 (to compile all JSPs,-p is for parallel and value (i.e. 10 here) can be varied accourding to your server)
  11. wait for successful completion
  12. cd $COMMON_TOP/_pages
  13. ls -l| wc -l (check whether count is same or more than before (pre-remove) - sometimes a little difference may be there)
  14. Go to $ADMIN_SCRIPTS_HOME again
  15. ./adopmnctl.sh startall
  16. wait for proper services startup
  17. ./adopmnctl.sh status -l
  18. check front-end and JSPs and FORMs should open up fine

You can avoid the step of regenrating the jsps like 11i but for that we need to make below changes in R12 context file (XML file):

parameter s_jsp_main_mode - you will find it justrun by default.

change it to - recompile

and run autoconfig to make the changes take place.

This will generate the jsps at run time saving bounce time, but obviously affect the performance.

Oracle Apps R12 Forms Not Opening

The forms for Oracle Apps EBS may not open for many reasons. Follow below steps to find out:-

For a particular Form:-
  1. It displays some message which can help you finding out the cause.
  2. Most of the time its related to some database objects or the particular form executable.
    a. Compile all ( Mainly Apps owned ) db objects.
    b. Compile particular form executable.
For All forms:-
  1. Always check the DB, and alert log.
  2. Check file system space.
  3. Check Apache and Forms log. $LOG_HOME/10.1.3/Apache/Apache/logs and$LOG_HOME/10.1.2/forms/logs
  4. If you are using R12 with IE8 then check Metalink doc 1069497.1, for Cross Site Scripting(XSS) settings (make it to relevant tab of your internet options). Also check 389422.1 for recommended browsers and settings.
  5. Check timestamp for files like $AU_TOP/resource/CUSTOM.* , this will tell you if someone has modified the pll and then the changes should be reverted back.
  6. Try checking the form sessions from your server as below
    ps -ef | grep frm
    This will show the frmsrv and frmweb processes. If it doesn't then the issue is in spawning form session.
    You can check the timestamp for frmsrv/frmweb executable, if this has got changed. You can relink this by below.
    a. Source the Application environment
    b. from location $ORACLE_HOME/forms/lib
    run :- make -f ins_forms.mk install
  7. In other case if from sessions are there and you are not able to open the form, then check the direct form login as below.
    http://server:port/forms/frmservlet - for servlet mode
    http://server:port/OA_HTML/frmservlet - for socket mode
  8. If above works then you can go to adadmin and compile jsps, or can have clear cache bounce.
  9. If step 6 doesn't work then check for dbc file present at $FND_SECURE.
    a. The file should have correct entries for Application Server Id, as it is in FND_NODES table.
    b. All the passwords guest,applsyspub etc are ok, or change the guest password using FNDCPASS
    c. You can correct the dbc file manually, or regenerate using  $COMMON_TOP/admin/install/adgendbc.sh
  10. Bounce forms server and retry.

Monday, March 25, 2013

Oracle 11.2.0.3 Upgrade from 10.2.0.4 Database

Oracle 11.2.0.3 Upgrade from 10.2.0.4 Database


1. Upgrade Procedure


      1. Current database version is 10.2.0.4.0 64 Bit 

      2. Install 11.2.0.3 Software on current server

      3. Direct upgrade to 11.2.0.3 Patch Set 2


2. Download 11.2.0.3 software


Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

Platform: Linux x86-64

Size 5 G


p10404530_112030_Linux-x86-64_1of7.zip 1.3 GB

p10404530_112030_Linux-x86-64_2of7.zip 1.1 GB

p10404530_112030_Linux-x86-64_3of7.zip 933.8 MB

p10404530_112030_Linux-x86-64_4of7.zip 628.7 MB

p10404530_112030_Linux-x86-64_5of7.zip 587.9 MB

p10404530_112030_Linux-x86-64_6of7.zip 457.7 MB

p10404530_112030_Linux-x86-64_7of7.zip 108.6 MB


Download above software from Oracle Support and keep in a staging location


3. Check Kernel version & OS Version


2.6.32 or later

uname –a (Use this command to check Kernel version and OS version


4. Create new ORACLE_HOME location


mkdir -p /u01/oracle/UAT12/11.2.0.3


4. Set Oracle Inventory


Edit /etc/oraInst.loc to point to /u01/oracle/UAT12/oraInventory

mkdir -p /u01/oracle/UAT12/oraInventory


5. Install 11.2.0.3 Enterprise edition Oracle software


Enable VNC, Enter following command on VNC to start OUI for installation

/u01/stage/11203_sw/runInstaller


Use new ORACLE_HOME for 11.2.0.3 as /u01/oracle/UAT12/11.2.0.3


6. Install Oracle Database 11g Products from the 11g Examples CD


Enable VNC, Enter following command on VNC to start Example Installer

/u01/stage/11203_sw/examples/runInstaller


7. Set the environment


export ORACLE_HOME = /u01/oracle/UAT12/11.2.0.3

export PATH  = $ORACLE_HOME/bin: $ORACLE_HOME/perl/bin:$PATH 

export LD_LIBRARY_PATH=$ORACLE_HOME/lib. 

export PERL5LIB= $ORACLE_HOME/perl/lib: $ORACLE_HOME/perl/lib/site_perl


8. Create nls/data/9idata directory


$ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.


After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.


9. Apply recommended Patches for 11.2.0.3 ORACLE_HOME


Patch 14275605: DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)


Use Opatch to apply above patch


10. Apply 11.2.0.3 Performance patches:


Patch:14267798 Merge patch fixing the following two bugs:

• Document:13004894.8 Wrong results with SQL_TRACE / 10046

• Document:13743357.8 Wrong results on first execution

Patch:11072246 Wrong Cardinality estimations for columns with DESC indexes

11. Set the environment to Existing 10.2.0.4 Environment


Set SID,ORACLE_HOME,PATH,LD_LIBRARY_PATH

login to UAT12 database


12. Run Pre-Upgrade Information Tool in 10.2.0.4 database


sqlplus '/ as sysdba'

spool pre_upg_11203_info.log

@/u01/oracle/UAT12/11.2.0.3/rdbms/admin/utlu112i.sql

spool off


Check the output of the Pre-Upgrade Information Tool in upgrade_info.log


Run utlrp.sql to fix invalid objects or components issue


Important: If you see a warning about the presence of the release 10g DMSYS schema in the database, then you must drop the DMSYS schema before proceeding with the upgrade.


13.  Check timezone version of 10.2.0.4 database and 11.2.0.3 and compare if both are same


SQL> SELECT version FROM v$timezone_file;


   VERSION

   ---------

         4


B.3b) When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3: (1358166.1)


* For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3

Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.


* (recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) using "14" as () in that note. When going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home.


14. Upgrade 11.2.0.1 to 11.2.0.3 using Manual upgrade method


15. Get DBA_REGISTRY information


sqlplus '/ as sysdba'

select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;


16.  Purge Recycle bin


PURGE DBA_RECYCLEBIN;


17. Gather Dictionary stats


EXECUTE dbms_stats.gather_dictionary_stats;


18.  Shutdown UAT12 database listener and database


shut immediate

lsnrctl stop UAT12


19. Copy of parameter files and edit for upgrade




Copy the init parameter file from 10.2.0.4 ORACLE_HOME/dbs into 11.2.0.3 ORACLE_HOME/dbs



Adjust the parameter file for the upgrade



Adjust initialization parameters that might cause upgrade problems.


• Remove obsolete initialization parameters based on pre-upgrade tool output

• Set the COMPATIBLE parameter if not already explicitly set


Adjust the parameter file for the upgrade


Adjust initialization parameters that might cause upgrade problems.


• Remove obsolete initialization parameters based on pre-upgrade tool output

• The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST

• Set the COMPATIBLE parameter if not already explicitly set


If you are upgrading from 10.1.0.x or 10.2.0.x then you can leave the COMPATIBLE parameter set to it's current value until the upgrade has been completed successfully. This will avoid any unnecessary ORA-942 errors from being reported in SMON trace files during the upgrade (because the upgrade is looking for 10.2 objects that have not yet been created).


grep -i comp initUAT12.ora 

*.compatible = 10.2.0.4.0


20. Prepare Oracle environment


export ORACLE_HOME = /u01/oracle/UAT12/11.2.0.3

export PATH  = $ORACLE_HOME/bin: $ORACLE_HOME/perl/bin:$PATH 

export LD_LIBRARY_PATH=$ORACLE_HOME/lib. 

export ORACLE_BASE=/u01/oracle/UAT12/


21. Verify /etc/oratab


UAT12 should point to /u01/oracle/UAT12/11.2.0.3 as ORACLE_HOME


22. Run upgrade scripts in VNC session


cd $ORACLE_HOME/rdbms/admin

sqlplus '/ as sysdba'

STARTUP UPGRADE


Monitor the alert log file and check for any errors.


SPOOL upgrade_uat12_11203.log

@catupgrd.sql


The output is at:

/u01/oracle/UAT12/11.2.0.3_upgrade_uat12_11203.log


STARTUP


Post-Upgrade Status Tool.

@utlu112s.sql


Verify the results, and if there any errors, must be resolved at this point.


@catuppst.sql


Identifying Invalid Objects

Any invalid SYS/SYSTEM objects found before upgrading the database are stored in

the table named registry$sys_inv_objs. Any invalid non-SYS/SYSTEM objects

found before upgrading the database are stored in registry$nonsys_inv_objs.


To identify any new invalid objects due to the upgrade


After the upgrade, run ORACLE_HOME/rdbms/admin/utluiobj.sql


Compile invalids


@utlrp.sql


23. Post Upgrade Steps


24. Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]


A) Applying 11.2.0.3 on 11.2.0.2 or 11.2.0.1


 Check the current version of the 11.2.0.2 or 11.2.0.1 Oracle RDBMS time zone definitions - this needs to be done for ALL databases in the 11.2.0.2 or 11.2.0.1 home:


SQL> conn / as sysdba

Connected.


SQL>SELECT version FROM v$timezone_file;


VERSION

----------

14


A.1)  The result is lower than 14 for all databases (typically 11 or 14)


Note : Every database in 11.2.0.1 home needs to be checked, if one or more has a result higher than 14 you need to follow step A.3)


If your current RDBMS time timezone version is  lower than 14 ,  install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.3

You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.3

The 11.2.0.3 RDBMS DST version after the update to 11.2.0.3 will be the same as your 11.2.0.2 or 11.2.0.1 system.


After the upgrade to 11.2.0.3 you can


* (recommended) update to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) onwards, when going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home


* (optional) update to a higher DST than DSTv14 version if this is needed.

The latest available DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches


A.2)  The result is 14 for all databases

Note : Every database in 11.2.0.2 or 11.2.0.1 home needs to be checked, if one or more has a result higher than 14 you need to follow step A.3)


If your current RDBMS time timezone version is 14 , install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.2.


You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.3


The 11.2.0.3 RDBMS DST version after the update to 11.2.0.3 will be 14.


(optionally) After the upgrade to 11.2.0.3 you can check if there is a newer DST version out and , if needed, update every database to this DST version. The latest available DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches


A.3)  The result is higher than 14 for one or more databases

If your current RDBMS time timezone version is higher then 14, you need to re-apply the same RDBMS DST version as found in step A) after installing the 11.2.0.3 software . Or in other words, you need to apply the 11.2.0.3 RDBMS DST patch for the same DST version before opening the database in the new 11.2.0.3 software home.


We suggest to apply the 11.2.0.3 RDBMS DST patch after installing the 11.2.0.3 software with the Oracle Universal installer.


If you also need to patch the OJVM then we suggest to apply the latest DST OJVM fix for 11.2.0.3, even if your current RDBMS DST patch is lower. This is best done right after installing the RDBMS DST patch, no need to follow the OJVM DST patch readme instructions as there is no database yet.


For the actual OJVM and RDBMS DST patches for your 11.2.0.3 release please see Note 412160.1 Updated Time Zones in Oracle Time Zone File patches


After installing the same RDBMS (and the OJVM - which may be the lastest/higher than the RDBMS DST version) DST patch in the 11.2.0.3 ORACLE_HOME upgrade to 11.2.0.3 without any action on TSLTZ or TSTZ data. Follow the upgrade or patchset apply instructions, you can skip any DST related sections.


The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.2 will be the same DST version as used in the 11.2.0.2 or 11.2.0.1 Oracle RDBMS.


(optionally) After the upgrade to 11.2.0.3 you can check if there is a newer DST version out and , if needed, update every database to this DST version. The latest available DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches


25. Do the actual RDBMS DST version update of the database using DBMS_DST:


conn / as sysdba

shutdown immediate;

startup upgrade;

set serveroutput on


-- check if previous prepare window is ended


SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;


-- output should be

-- PROPERTY_NAME VALUE

-- ---------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE


-- If DST_UPGRADE_STATE is "PREPARE" then you did not ended 

-- the prepare window in step 3)


-- If there are objects containing TSTZ data in recycle bin, 

-- please purge the bin now. 

-- Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin".


purge dba_recyclebin;


-- clean used tables


TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;


-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691


alter session set "_with_subquery"=materialize;


-- to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;


-- start upgrade window


EXEC DBMS_DST.BEGIN_UPGRADE(14);


-- the message

-- "An upgrade window has been successfully started."

-- will be seen


-- check if this select


SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;


-- gives this output:

-- PROPERTY_NAME VALUE

-- --------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION

-- DST_SECONDARY_TT_VERSION

-- DST_UPGRADE_STATE UPGRADE


-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE

-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property. 

-- even if this select gives no rows you still need to do to the rest of the steps

-- it simply gives an indication of how many user objects need to processed in the later steps

-- some oracle provided users may be listed here, that is normal


SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';


-- restart the database


shutdown immediate

startup


alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;


-- now upgrade the tables who need action


set serveroutput on

VAR numfail number

BEGIN

DBMS_DST.UPGRADE_DATABASE(:numfail,

parallel => TRUE,

log_errors => TRUE,

log_errors_table => 'SYS.DST$ERROR_TABLE',

log_triggers_table => 'SYS.DST$TRIGGER_TABLE',

error_on_overlap_time => FALSE,

error_on_nonexisting_time => FALSE);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

END;

/


-- ouput of this will be a list of tables like:


-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S

-- Number of failures: 0

-- ....

-- Table list: SYSMAN.MGMT_PROV_ASSIGNMENT

-- Number of failures: 0

-- Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES

-- Number of failures: 0

-- Failures:0


-- if there where no failures then end the upgrade.


VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

/


-- output that will be seen:

-- An upgrade window has been successfully ended.

-- Failures:0


-- last checks


SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;


-- needed output:

-- PROPERTY_NAME VALUE

-- ---------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE



SELECT * FROM v$timezone_file;


-- needed output:

-- FILENAME VERSION

-- ------------------ ----------

-- timezlrg_.dat


-- if registry$database exists then update this static table also with the new DST version

-- this table is used during upgrade and contians the DST version from before the upgrade

-- this update is mainly to avoid confusion when people notice this has a lower version


select TZ_VERSION from registry$database;


update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

commit;


26. Configuring Fine-Granined Access to External Network services After upgrading Oracle database


Create ACLs as per note id: 958129.1:


SQL> SELECT OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES

WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')

AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');   2    3


exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(); 

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE();

exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(); 


COMMIT;


SQL> select * from dba_network_acls;


27. Upgrade Oracle Text - None


After an upgrade to the new Oracle Database 11g release, copy the following files from the previous Oracle home to the new Oracle home:


■ Stemming user-dictionary files

■ User-modified KOREAN_MORPH_LEXER dictionary files

■ USER_FILTER executables


These files affect all databases installed in the given Oracle home.


You can obtain a list of these files by doing the following:

1. Looking at $ORACLE_HOME/ctx/admin/ctxf102.txt

2. Executing $ORACLE_HOME/ctx/admin/ctxf102.sql as database user SYS,SYSTEM, or CTXSYS


28. Configure and Start new database listener


Get your listener.ora and tnsnames.ora files from Old Home to New home

Set the TNS_ADMIN environment variable to the directory where you got your listener.ora and tnsnames.ora files.


Start listener


29. Document References


1. 11.2.0.3 Patch Set - Availability and Known Issues [ID 1348336.1]

2. ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]

3. http://docs.oracle.com/cd/E11882_01/server.112/e23633.pdf -- Upgrade guide

4. http://docs.oracle.com/cd/E11882_01/install.112/e24326.pdf - Quick Installation Guide

5. Oracle Recommended Patches -- Oracle Database [ID 756671.1]

6. Quick Reference to Patchset Patch Numbers [ID 753736.1]