Tablespace Usage Scripts

set lines 300 pages 300;
select     a.TABLESPACE_NAME,
     a.BYTES bytes_used_GB,
     b.BYTES bytes_free_GB,
     b.largest,
     round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
     (
           select     TABLESPACE_NAME,
                sum(BYTES)/1024/1024/1024 BYTES
           from dba_data_files
           group      by TABLESPACE_NAME
     )
     a,
     (
           select     TABLESPACE_NAME,
                sum(BYTES)/1024/1024/1024 BYTES ,
                max(BYTES) largest
           from dba_free_space
           group      by TABLESPACE_NAME
     )
     b
where      a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;



Tablespace Monitoring
*********************
column "PCT Used" format 9999
break on report
compute sum of TOTAL on report
COMPUTE SUM Of USED on report
COMPUTE SUM Of FREE on report
select
a.tablespace_name,
round(b.bytes/(1024*1024)) as TOTAL,
round(b.bytes/(1024*1024)-a.bytes/(1024*1024)) as USED,
round(a.bytes/(1024*1024)) "FREE" ,
round((round(b.bytes/(1024*1024)-a.bytes/(1024*1024))/(b.bytes/(1024*1024)))*100) as "PCT Used" ,'%'
fromsys.sm$ts_free a, sys.sm$ts_avail b where a.tablespace_name=b.tablespace_name order by 5

singletablespace monitoring

set pages 999
set linesize 80
column tablespace_name for a15
SELECT * FROM (
SELECT c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used, ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used FROM (SELECT tablespace_name,
SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
Where a.tablespace_name='&tbs'
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+) AND a.file_id = b.file_id (+) GROUP BY a.tablespace_name) b, sys.DBA_TABLESPACES c WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name
) WHERE tot_Pct_Used>=0
ORDER BY tablespace_name;

List information about tablespace to which datafiles belong
***********************************************************

SELECT file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024,INCREMENT_BY FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='APPS_TS';
TO check in asm space:
--------------------------
selectname,total_mb,free_mb,(free_mb/total_mb)*100 from v$asm_diskgroup;

alter tablespace APPS_TS add datafile '+DATA' size 2000m



and this is for temp files
you can view the tempfile in dba_temp_files only
***********************

SELECT file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024,INCREMENT_BY FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP_STD_TBL';

********************************************************************************************************************************************

altertablespace TEMP_STD_TBL add tempfile '+DATA1' size 10000m;
***************



SELECT A.tablespace_nametablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
         (
         SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM v$tablespace B, v$tempfile C
         WHERE B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;



Temp tablepace job failure\



selectFILE_NAME,TABLESPACE_NAME,bytes/1024/1024 from DBA_TEMP_FILES;
SELECT A.tablespace_nametablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
         (
         SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM v$tablespace B, v$tempfile C
         WHERE B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;



df -h|grep /dbs

Tablespace IPF_STD_TBL is 80 percent full


select tablespace_name
       , count(*) as no_of_data_files
       , sum(maxblocks) as max_size
from dba_data_files
group by tablespace_name;

select tablespace_name, max_size
from dba_tablespaces
/

select tablespace_name
       , initial_extent + (next_extent * (max_extents-1)) as calc_max_size
from dba_tablespaces
/

Shortname.sql

column program_name format a20 heading 'Program Name'
column program_id format 9999999 heading 'Program ID'
column user_program format a50 heading 'User Program Name'
column executable_id format 9999999 heading 'Executable ID'
select concurrent_program_name program_name, concurrent_program_id program_id,
       user_concurrent_program_name user_program, executable_id
from apps.fnd_concurrent_programs_vl
where user_concurrent_program_name like '%&user_concurrent_program_name%'
/

OC4J Password Reset

1. Shut down the Application Server Console
$ORACLE_HOME/bin/emctl stop iasconsole

You need to stop the OC4J instances before performing the password change:


$ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=OC4J

2.Locate and backup the system-jazn-data.xml (10.1.3) file:

$ORACLE_HOME/j2ee/home/config/system-jazn-data.xml

3. Edit the system-jazn-data.xml file in a text editor and
locate the line that defines the credentials property for the ias_admin user:

 Replace the existing encrypted password with the new password.
Be sure to prefix the password with an exclamation point (!). For example:


Ex:                             <name>oc4jadmin</name>
                                <display-name>OC4J Administrator</display-name>
                                <guid>C70BD6109B3111E19F769F511903AE79</guid>
                                <description>OC4J Administrator</description>
                                <credentials>{903}YVtqGjfAaDWCitUeUkz7lFxC21KowukG</credentials>



change the password in <credentials> column like below and save it.
<credentials>!mynewpassword123</credentials>


4.Delete cached password data by deleting the contents of the following directory:

Do not delete the Directory,just delete the contents of the securestore Directory.

$ORACLE_HOME/j2ee/home/persistence/ascontrol/ascontrol/securestore

5. Start the Application Server Console:


$ORACLE_HOME/bin/emctl start iasconsole

$ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=OC4J

OPP Issue

If Service Manager for the node is not running.  Possible cause might be service manager definition is missing under

Concurrent ->Manager ->Define form. If the Service Manager is not present/defined for a particular node,then this causes all the services provided by Service Manager like OPP,WF etc.. not to work.

1. Shutdown all the services.

——Below Step 2 will create  Service Manager “FNDSM”——-

2. Log in as applmgr
cd to $FND_TOP/patch/115/sql
Run the script: afdcm037.sql

3. Relink FNDSM and FNDLIBR executables as mentioned below:

$ adrelink.sh force=y link_debug=y “fnd FNDLIBR”
$ adrelink.sh force=y link_debug=y “fnd FNDSM”

4. Run cmclean.sql
5. Start up the managers/services

Opatch

Opatch
Link for this document:

http://www.comp.dit.ie/btierney/oracle11gdoc/em.111/b31207/oui7_opatch.htm#insertedID9

OPatch is the Oracle database's Interim (one-off) Patch Installer.
If OPatch is not installed into your Oracle Home ($ORACLE_HOME/OPatch), you may need to download it from Metalink and install it yourself.
[edit] Syntax
Syntax and options can be displayed using the -help option:
$ cd $ORACLE_HOME/OPatch
$ opatch -help
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


 Usage: opatch [ -help ] [ -r[eport] ] [ command ]

            command := apply
                       lsinventory
                       napply
                       nrollback
                       rollback
                       query
                       version
                       prereq
                       util

 <global_arguments> := -help       Displays the help message for the command.
                      -report     Print the actions without executing.

 example:
   'opatch -help'
   'opatch apply -help'
   'opatch lsinventory -help'
   'opatch napply -help'
   'opatch nrollback -help'
   'opatch rollback -help'
   'opatch prereq -help'
   'opatch util -help'

OPatch succeeded.


 [edit] Listing installed patches
All patches that are installed with Oracle's OPatch Utility can be listed by invoking the opatch command with the lsinventory option. Here is an example:
$ cd $ORACLE_HOME/OPatch

$ opatch lsinventory
Invoking OPatch 10.2.0.1.0

Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..
...
Installed Top-level Products (1):

Oracle Database 10g                                           10.2.0.1.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
OPatch succeeded.




Requirements for OPatch:

1.JRE version 1.4 or higher, Java commands for Windows, and ar, cp, fuser, and make commands for UNIX must be made available
2.The library path must be set correctly for Oracle Real Application Clusters environments. OPatch uses some APIs to detect if the system is a Real Application Clusters system. Ensure that the library path is set correctly as follows:

For Solaris:
LD_LIBRARY_PATH = $ORACLE_HOME/lib32:$ORACLE_HOME/lib

For HP-UX:
SHLIB_PATH=$ORACLE_HOME/lib32:/usr/lib

Link for Oracle support

http://www.oracle.com/support/metalink/index.html

############Prerequisite Checks for OPatch

1. Checks for Single Instances and Real Application Clusters
2. Check ORACLE_HOME and Environment Variable

3. Check for JRE

OPatch requires JRE version 1.4 or higher to work properly.
4. Check for System Space

When OPatch processes the script for the installation of a patch, it simultaneously generates a Rollback script and
saves a copy of every file edited or deleted during the patching. OPatch also backs up the inventory information.
 Consequently, Oracle recommends that you have sufficient system space to accommodate the patch and the backup
information.
5. Check for Oracle Universal Installer and OPatch Version Compatibility

OPatch 11.1 requires Oracle Universal Installer 11.1 or higher to work properly. If the Oracle Universal Installer version is less than what OPatch requires, OPatch errors out.
6.Check for Patch Applicable on Operating System

OPatch detects if a particular patch is applicable for an operating system. If it is not applicable, OPatch displays an error message.

7. Check for System Commands

OPatch supports a set of properties used for various software operations. You can use these properties to control the internal operations of OPatch. By default, OPatch uses the standard Java property format to specify the properties. The following list shows the default properties and their values:

fuser=/sbin:/usr/sbin

ar=/usr/ccs/bin/

make=/usr/bin
You can specify OPatch properties in the following ways:

By using the default OPatch properties.

By specifying the location of the user-defined properties file.

By using the command line. The syntax is as follows:

PROPERTY_NAME=VALUE
Example: fuser=/sbin:/usr/sbin

8. Additional Checks for Real Application Clusters
For Real Application Clusters, ensure that you perform the following prerequisite checks besides the other checks listed in the preceding section.

Check for User Equivalence
You must ensure that the cluster machines have user equivalence set for the user installing Oracle Clusterware/ Real Application Clusters. On UNIX, this means rsh or ssh or both should be set up on the cluster machines. On Windows, this means the same <domain>\<user> should have administrative privileges on all the cluster machines, and the machines should be a member of the <domain>.

If the user equivalence is set properly, the following command will work properly:

$ rsh <nodename> date

9. Check for OPatch Lsinventory

opatch lsinventory -detail
Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

.....
.....
.....
.....
.....
Patch Location in Storage area:
     /scratch/userid/oracle/product/11.1.0/db_1/.patch_storage/300500_Nov_07_2006_04_57_14

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

OPatch succeeded.

10. Backup and Recovery Considerations for Patching

Note:

It is highly recommended that you back up the ORACLE_HOME before any patch operation.
You can back up the ORACLE_HOME using your preferred method. You can use any method such as zip, cp -r, tar, and cpio
 to compress the ORACLE_HOME.

If the ORACLE_HOME does not appear when you execute the opatch lsinventory -detail command, the ORACLE_HOME might be missing from the Central Inventory, or the Central Inventory itself could be missing or corrupted.

If the ORACLE_HOME is listed when you execute the opatch lsinventory -detail command, but the products and components within the ORACLE_HOME are not listed, the inventory within the ORACLE_HOME (local inventory) might be missing or corrupted.

If the local inventory is corrupted or lost for some reason, you can simply restore the ORACLE_HOME/inventory if it was backed up. If a backup does not exist, you may have to reinstall the software.


12. OPatch Utility for OUI-based Oracle Homes

You can run the OPatch utility, located in the <Path_to_Oracle_Home>/OPatch directory, with various commands and options.
The following string shows the syntax for the OPatch utility:


<Path_to_OPatch>/opatch [-help] [-r[eport]] [command] [-option]
Table 7-1 OPatch OUI-based Commands

Command Description
apply
 Installs an interim patch. See "Apply Command for OUI-based Oracle Homes" for more information.

napply
 Installs n number of patches (hence napply) See "Napply Command for OUI-based Oracle Homes" for more information.

lsinventory
 Lists what is currently installed on the system. See "Lsinventory Command for OUI-based Oracle Homes" for more information.

query
 Queries a given patch for specific details. See "Query Command for OUI-based Oracle Homes" for more information.

rollback
 Removes an interim patch. See "Rollback Command for OUI-based Oracle Homes" for more information.

nrollback
 Removes n number of patches (hence nrollback). See "Nrollback Command for OUI-based Oracle Homes" for more information.

version
 Prints the current version of the patch tool. See "Version Command for OUI-based Oracle Homes" for more information.

13.
Apply Command for OUI-based Oracle Homes
This command applies an interim patch to an Oracle home from the current directory. The ORACLE_HOME environment variable must be set to the Oracle home to be patched.

Syntax

Use the following syntax for this command:

opatch apply  [-delay <value> ] [ -force ]              [-invPtrLoc <Path to oraInst.loc> ]              [-jre <LOC> ] [-local ] [-minimize_downtime ]              [-no_bug_superset ] [-no_inventory ]              [-oh <ORACLE_HOME> ] [-retry <value> ] [-silent ]              [-verbose ]  [-no_relink] [-pre <parameters for the              pre script in escaped double quotes> [-opatch_pre_end] ]              [-post <parameters for the post script in escaped              quotes> [-opatch_post_end] ] [-no_sysmod]              [-property_file <Path to property file>]               [-local_node <Local node name>]              [-remote_nodes <List of remote nodes (node1,node2)>]              [-connectString  <List of connect strings>]               [-runSql] [-sqlScript <path of the sql file>]              [-ptlSchema <portal schema>] [-ptlPassword <portal password>]              [-ptlConnect <portal connect string>]              [-init <parameters for the init script in escaped double               quotes> [-opatch_init_end] ] [-report]
              [<Patch Location>]

14.
Rollback Command for OUI-based Oracle Homes
This command removes an existing one-off patch from the appropriate Oracle home directory indicated by the reference ID.

Syntax

Use the following syntax for this command:

opatch rollback -id <ID> [-ph <Patch Location>]  [-delay <value>]


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

Real Application Clusters Patching:

A Real Application Clusters environment enables active instances to concurrently execute transactions on a shared database. Patching in a Real Application Clusters environment is slightly different compared to patching a single node.

Interim Patching using OPatch follows a similar approach as that performed by Oracle Universal Installer to detect Oracle home and nodes of a cluster. OPatch interacts with the Oracle Universal Installer inventory through the Oracle Universal Installer Java SDK. If OPatch detects a cluster, it queries the inventory through Oracle Universal Installer to find the local node name and node list. If your node list is not updated, you can update it by using the -updateNodeList flag of Oracle Universal Installer. You can bypass remote actions using the -local flag, as shown below:

$ORACLE_HOME/oui/bin/<runInstaller or setup.exe> -updateNodeList ORACLE_ HOME=<oracle home
 location> CLUSTER_NODES=<commaseparated nodelist for example:node1,node2,node3>
-noClusterEnabled


If you want to specify the local node or remote nodes of a Real Application Clusters setup to OPatch, you can use the LOCAL_NODE or REMOTE_NODES session variable and specify the node name(s), as shown below:

$ORACLE_HOME/oui/bin/<runInstaller or setup.exe> ORACLE_HOME=<oracle home location>
REMOTE_NODES <commaseparated nodelist for example:node1,node2,node3> LOCAL_NODE=<nodelist
for example:node1>

If OPatch does not automatically detect Real Application Clusters or its nodes, you need to investigate the contents of the inventory and ensure that it is complete.

You can patch Real Application Clusters in three different ways:

All Node Patching

Rolling Patching

Minimum Downtime Patching

All Node Patching

Systems A, B, and C are nodes in this cluster. When you perform All Node Patching in this cluster,
you bring down systems A, B, and C, apply patches to all these nodes, then bring systems A, B, and C back up again.



Rolling Patching:

In Rolling Patching, you shut down each node, apply the patch, then bring up each node again.
You do this separately for each node until you patch all nodes in the cluster. This is the most efficient method of
applying an interim patch to a Real Application Clusters setup, because there is absolutely no downtime during the
application of patches, as only one system is brought down at any given time. Only some patches can be applied in this
mode. The type is generally specified in the patch metadata

When you perform Rolling Patching in this cluster, the patches are applied in a rolling fashion. You initially bring down
system A, apply a patch to it, then bring it back up. You do the same thing for systems B and C.


Minimum Downtime Patching:

In Minimum Downtime Patching, the nodes are divided into sets. Initially, you shut down the first set and apply a patch to
 it. After this, you shut down the second set. You then bring up the first set and apply a patch to the second set. You
now bring up the second set. All the nodes in the cluster are now patched. This method leads to less downtime for the Real
 Application Clusters when both sets are brought down. This mode is executed by using -minimize_downtime command line option.
 You can also activate this option from the response file.

Figure 7–3 shows a basic example of Minimum Downtime Patching.

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

About Patch Conflicts:

All patches may not be compatible with one another. For example, if you apply a patch, all the bugs the patch fixes could reappear after you apply another patch. This is called a conflict situation.
OPatch detects such situations and raises an error when it detects a conflict.

Types of Conflicts
OPatch can detect the following types of conflicts.

Superset:

If all the bugs fixed by a patch in the system are also fixed by the patch to be applied, this patch (the patch to be
applied) is considered a superset of the patch already applied. If a bug superset condition is detected, it is not
considered an error situation. All the subset patches are removed from the system and the new patch is applied.

Example

Consider the following scenario:

Patch A, installed in the Oracle home, fixed bugs 1, 2, and 3.

Patch B, installed in the Oracle home, fixed bugs 10, 11, and 12.

Patch C, to be installed, fixes bugs 1, 2, 3, and 4.

Patch C is considered a superset of Patch A.

Using the -no bug superset Flag

If you want OPatch to error out if the current patch bugs-to-fix is a superset or the same as an installed patch bugs-fixed in the Oracle home directory, you can use the -no_bug_superset flag:

$ OPatch/opatch apply -no_bug_superset <Path_To_Patch>

The following example output shows the message you would see when you use the -no_bug_superset flag:

Oracle interim Patch Installer version 11.2.0.0.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved..
Oracle Home       : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from           : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version    : 11.1.0.0.0
OUI version       : 11.1.0.0.0
OUI location      : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_TEST/product/11.1.0/db
_1/cfgtoollogs/opatch/opatch-2007_May_25_14-03-33-IST_Wed.log
ApplySession applying interim patch '111000' to OH '/home/oracle
_TEST/product/11.1.0/db_1'
Apply Session failed: ApplySession failed to prepare the system. Interim patch
 111000 is a superset of the patch(es) [ 111000 ] in OH /home/oracle
_TEST/product/11.1.0/db_1
System intact, OPatch will not attempt to restore the system
OPatch failed with error code 73


Subset:

Patches to be applied can be subsets of other patches installed in the Oracle home.

Example

Consider the following scenario:

Patch A, installed in the Oracle home, fixed bugs 1, 2, and 3.

Patch B, installed in the Oracle home, fixed bugs 10, 11, and 12.

Patch D, to be installed, fixes bugs 1 and 2.

Patch D is a subset of Patch A.

Using the skip_subset Option

When you want to skip patches formerly applied in the Oracle home that are now subsets of other patches you want to
apply now, you can use the skip_subset option of napply. For example, if you used napply yesterday for patch A that fixed
bugs 1 and 2, then you use napply today with the skip_subset option for patch B that fixes bug 1 and patch C that fixes
bugs 1, 2, and 3, then subset patch A is skipped, and patch C then becomes a superset of patch A.

Example 7-1

opatch napply <patch_location> -skip_subset -skip_duplicate


Example 7-2 applies patches 1, 2, and 3 that are under the <patch_location> directory. OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).

Example 7-2
opatch napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate
See the description for the skip_subset option in Table 7-3 for more information.


Duplicate:
A duplicate patch fixes the same set of bugs fixed by another patch. For example, if you applied Patch A that fixed
 bugs 1, 2 and 3, and now apply Patch B that also fixes bugs 1, 2 and 3, then Patch B is a duplicate of Patch A. A patch
is always a duplicate of itself.




Using the skip_duplicate Option

If you specify this option, OPatch removes duplicate patches from the list of patches to be applied. For example,
if you used napply yesterday for Patch A discussed above, then use napply today with the -skip_duplicate option for Patch
 A and other patches, duplicate Patch A is skipped.


Bug Conflict:

A bug conflict occurs if a set of bugs to be fixed by the current interim patch intersects with some bugs already fixed by one or more previously installed interim patches. You must remove the bug conflict before you proceed with the patching by using the apply command with the -force flag, which rolls back the conflicting patches before applying the new one.

Example

Consider the following scenario:

Patch A, installed in the Oracle home, fixed bugs 1, 2, and 3.

Patch B, installed in the Oracle home, fixed bugs 10, 11, and 12.

Patch E, to be installed, fixes bugs 3 and 4.

Patch E conflicts with Patch A.

Patch Conflict Behavior for Apply and Napply
The expected behavior for the Apply and Napply commands is listed in Table 7-22.

Table 7-22 Expected Behavior for Apply and Napply Commands

Command Superset Subset Duplicate File Conflict or Bug Conflict Patch
Apply
 OPatch performs an automatic rollback, then an apply.
 After the merge request, OPatch performs an automatic rollback, then performs an apply.
 OPatch performs an automatic rollback, then performs a reapply.
 OPatch reports the conflict. After the merge request, OPatch performs an automatic rollback, then an apply.

Napply
 OPatch performs an automatic rollback, then an apply.
 OPatch reports the subset and skips the subset patch. It then continues and applies the other patches.
 OPatch performs an automatic rollback, then a reapply.
 OPatch reports the conflict, then asks you to run again without applying a bug conflict patch.

You can use the -force option to instruct OPatch to automatically roll back the conflicting patch, then apply the new patch.

Patch Conflict Detection and Resolution:
OPatch detects and reports any conflicts encountered when applying an Interim patch with a previously applied patch.
The patch application fails in case of conflicts. You can use the -force option of OPatch to override this failure. If
you use this option, the installer first rolls back any conflicting patches and then proceeds with the installation of
the desired interim patch.

You may encounter a bug conflict and might want to remove the conflicting patch. This process is known as patch rollback.
During patch installation, OPatch saves copies of all the files the new patch replaced before the new versions of these
files are loaded and stores them in $ORACLE_HOME/.patch_storage. These saved files are called Rollback files and are the
key to making patch rollback possible. When you roll back a patch, these Rollback files are restored to the system. You
should only override the default behavior by using the -force flag if you completely understand the patch Rollback process.
To roll back a patch, execute the following command:

$ OPatch/opatch rollback -id <Patch_ID>




Problem Resolution:

The following sections provide information and instructions on the following tasks to resolve problems:

Using logs and traces

Recovering from a failed patching session

Resolving OPatch application errors

Logging and Tracing
Logging and tracing is a common aid for debugging. OPatch maintains logs for all Apply, Rollback, and Lsinventory
operations. Each time you execute OPatch, a new log file is created. The log files are located in the
<ORACLE_HOME>/cfgtoollogs/opatch directory. Each log file is tagged with the timestamp of the operation.
Log files are named as opatch_<date mm-dd-yyyy>_<time hh-mm-ss>.log.

For example, if a log file is created on May 17th, 2007 at 11.55 PM, it will be named as follows:

opatch_05-17-2007_23-55-00.log

Note:

You can set OPatch to debug mode by setting the environment variable OPATCH_DEBUG to TRUE.



Command Index
OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt
file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory. An example of the history.txt file is as follows:

Date & Time : Tue Apr 26 23:00:55 PDT 2007
Oracle Home : /private/oracle/product/11.1.0/db_1/
OPatch Ver. : 11.1.0.0.0
Current Dir : /scratch/oui/OPatch
Command     : lsinventory
Log File    :
/private/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch-2007_Apr_26_23-00-55-PDT_Tue.log



Levels of Logging
OPatch follows the Oracle Diagnostic Logging (ODL) guidelines. You can set the log level by using the -logLevel <level> option available. This controls the amount of logging OPatch performs, according to the ODL guidelines.

OPatch supports the following log levels:

SEVERE

WARNING

INFO

CONFIG

FINE

FINER

FINEST

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

Recovering from a Failed Patching Session:

During patching, updates can occur in two phases:

System Update — In this phase, the files are replaced in the Oracle home.

Inventory Update — In this phase, the details of the patch applied is recorded in the inventory.

The following scenarios for single instance setups and Real Application Clusters setups explain how you can recover from a failed patching session.

Single Instance Setup:

When you apply or roll back a patch, an interim inventory update exception occurs.
Cause: This occurs when the files on the system are patched, but the inventory update has failed. A corrupted inventory may cause this problem.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command.

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt
When you apply or roll back a patch, an OiiOneoffException occurs.
Cause: This occurs when the files on the system are patched, but the inventory update has failed. This may occur because the base component of the interim patch may not be present in the inventory.
Action: OPatch tries to restore the Oracle home automatically and displays a message for the same. If OPatch does not display a message stating that it has restored the Oracle home, perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command.

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt
When you apply a patch and execute opatch lsinventory, it returns nothing.
Cause: This may occur because all the patches applied before the application of the current patch are lost, or the patches might not have been updated in the inventory.
Action: Perform the following steps:
Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command:

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt
If the files are properly patched, but the information is not updated in the inventory, execute the following command:

$ORACLE_HOME/OPatch/opatch apply -no_sysmod <Path_To_Patch>
Ensure that the patch has been applied and recorded properly in the inventory by executing the following command:

$ORACLE_HOME/OPatch/opatch lsinventory -detail
If the files are still not patched properly, but you are able to see the patch in the lsinventory flag, you need to reapply the patch using the no_inventory flag:

$ORACLE_HOME/OPatch/opatch apply -no_inventory <Path_To_Patch>
When you apply a patch and execute opatch lsinventory, it does not return the details of the patch applied.
Cause: OPatch may not have recorded the details of this patch in the inventory.
Action: Perform the following steps:
Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command:

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt
If the files are properly patched, but the information is not updated in the inventory, execute the following command:

$ORACLE_HOME/OPatch/opatch apply -no_sysmod <Path_To_Patch>
Ensure that the patch has been applied and recorded properly in the inventory by executing the following command:

$ORACLE_HOME/OPatch/opatch lsinventory -detail
When you press Ctrl + C during the application or roll back of a patch and execute opatch lsinventory, it does not return the details of the patch applied or rolled back.
Cause: This may be because OPatch might have stopped the application or rollback of the patch on pressing Ctrl+c.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command if it is available.

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt
When you apply a patch, you quit when OPatch failed to relink and prompted to continue.
Cause: This may occur because of a relink failure.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command.

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
Resolve the relink failure issue by ensuring that you are able to invoke make manually on a UNIX shell. After this, apply the patch again.

Real Application Clusters Setup
When I apply a patch on a Real Application Clusters setup and execute 'opatch lsinventory' on the local node, the patch is not listed.
Cause: This may occur if OPatch failed to update the inventory.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly in all the nodes of the cluster.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory of each node in the cluster and execute the Restore command as follows:

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) in each node of the cluster as follows:

/bin/sh make.txt
Apply the patch in each node in the cluster using the local flag:

$ORACLE_HOME/OPatch/opatch apply -local <Path_To_Patch>
Note:

Ensure that all the nodes use the same OPatch version.
When I apply a patch on a Real Application Clusters setup and execute 'opatch lsinventory' on the local node, it returns nothing.
Cause: You might have lost all the patches applied earlier.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly in each node in the cluster.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command in each node in the cluster.

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) in each node as follows:

/bin/sh make.txt
Apply the patch in each node using the local flag:

$ORACLE_HOME/OPatch/opatch apply -local <Path_To_Patch>
Note:

Ensure that all the nodes use the same OPatch version.
When I roll back a patch on a Real Application Clusters setup, and execute 'opatch lsinventory' on the local node, it shows that the patch was not removed.
Cause: This may occur if OPatch failed to update the inventory.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly in each node in the cluster.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory in each node in the cluster and execute the restore command as follows:

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) in each node in the cluster as follows:

/bin/sh make.txt
Roll back the patch in all the nodes in the cluster using the local flag:

$ORACLE_HOME/OPatch/opatch rollback -local -id <Patch_ID>
Note:

Ensure that all the nodes use the same OPatch version.
When I roll back a patch on a Real Application Clusters setup and execute 'opatch lsinventory' on the local node, it returns nothing.
Cause: You might have lost all the patches applied earlier.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly in each node in the cluster.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command in each node in the cluster:

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt
Roll back the patch in the local node using the local flag:

$ORACLE_HOME/OPatch/opatch rollback -local -id <Patch_ID>
Roll back the patch on the other nodes also using the local flag.

Note:

Ensure that all the nodes use the same OPatch version.
When I apply a patch on a Real Application Clusters setup, the patching in one node is fine (both the files and the inventory are fine), but I am not sure about the other nodes.
Cause: This may occur because of a failed system or inventory update.
Action: Perform the following steps:
Copy the Oracle home from the node that is fine to the other nodes.

After copying the Oracle home, make sure that the ORACLE_HOME/inventory/ContentsXML/comps.xml file has the latest timestamp.

Note:

On Unix, use touch to change the timestamp.
Update the nodes of the cluster. For more information on updating the nodes of the cluster, see "Updating the Nodes of a Cluster".

Ensure that all the prerequisite checks pass that are listed in the section "Prerequisite Checks for OPatch".

When I apply a patch on a Real Application Clusters setup, the patching in one node is fine, but when I execute 'opatch lsinventory' on the other nodes, the patch is not listed.
Cause: This may occur because of a failed system or inventory update.
Action: Perform the following steps:
Copy the ORACLE_HOME /inventory directory from the node that is fine to the other nodes.

After copying the ORACLE_HOME /inventory directory, make sure that the ORACLE_HOME/inventory/ContentsXML/comps.xml file has the latest timestamp.

Note:

On Unix, use touch to change the timestamp.
Update the nodes of the cluster. For more information on updating the nodes of the cluster, see "Updating the Nodes of a Cluster".

Ensure that all the prerequisite checks pass that are listed in the section "Prerequisite Checks for OPatch".

When I apply or roll back a patch on a Real Application Clusters setup, I am not able to apply or roll back the patch on all nodes.
Cause: This may occur if the nodes are not properly updated.
Action: Perform any one or more of the following:
Ensure that all the nodes in the cluster are up-to-date. If they are not, update the nodes of the cluster. For more information on updating the nodes of the cluster, see "Updating the Nodes of a Cluster".

Execute the appropriate command on all nodes of the cluster as follows:

opatch apply -local [patch_location]

opatch rollback -local [patch_location]
Execute the appropriate command on the local node of the cluster as follows:

opatch apply [-local_node (node_name)] [-remote_nodes (comma separated node_names)]

opatch rollback [-local_node (node_name)] [-remote_nodes (comma separated node_names)]
Resolving OPatch Application Errors
This section provides solutions to the following errors that may occur during patch application:

Not a valid patch area

Opatch cannot find system commands like fuser, make

Unable to remove a partially-installed interim patch

Not a valid patch area
Cause: The directory that the OPatch utility is using to do the patch does not match the template for what it is checking. This can also occur when you run the utility from an invalid shiphome directory.
Action: When starting the OPatch utility, the directory needs the following:
/etc directory that has the metadata files.

/files directory that has the payload files.

/etc/config/inventory file and the actions file under the same directory.

If you did not start the OPatch utility from the patch_id directory, you can use the following command:

opatch apply /<Patch_Shiphome>
OPatch cannot find system commands like fuser, make
Cause: The OPatch utility uses fuser on UNIX systems to check for active Oracle instances. On certain hp-ux systems, only a super-user can run fuser.
Action: Perform these steps to resolve this problem:
Set /tmp in your PATH.

For more information, see "Checks for Single Instances and Real Application Clusters".

Create an empty file named fuser.

Shut down the Oracle instances.

Run the OPatch utility.

Caution:

Another way to resolve this problem is to give executable permission to other users for fuser. However, this exposes a potential security issue in the system, and is not recommended.
Unable to remove a partially-installed interim patch
Cause: Interruption in the patching process potentially causes this problem. This may occur if you press Ctrl+c during the patching process. If the error is the one that OPatch detects, it automatically resolves it.
Action: Perform the following steps:
Ensure that the environment variable ORACLE_HOME is set properly.

Navigate to the $ORACLE_HOME/.patch_storage/<patch-id_timestamp> directory and execute the Restore command as follows:

For UNIX:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

For Windows:
$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.bat
On UNIX, source $ORACLE_HOME/.patch_storage/<patch-id_timestamp>/make.txt file (if available) as follows:

/bin/sh make.txt

MWA Services in Oracle Applications

Login as applmgr user :
Go to $ADMIN_SCRIPTS_HOME

Stop Mobile Application Services :

./mwactl.sh -login apps/apps_password stop_force
./mwactlwrpr.sh stop apps/apps_password

Start Mobile Application Services :

./mwactl.sh -login apps/apps_password start
./mwactlwrpr.sh start apps/apps_password

Check Mobile Services are up and Runing:

ps -ef | grep mwa

Find port Number of Mobile Services :

grep mwa $CONTEXT_FILE

Connect to Mobile Services :
telnet hostname.domainname portnumber(mobile application service port_number)
++++++++++++++++++++++++++++++++++++
Hot to configure Mobile applications on EBS – 11i
++++++++++++++++++++++++++++++++++++
1. Stop the current servers.
a. $ cd $MWA_TOP/bin
b. $ nohup mwactl.sh -login SYSADMIN/sysadmin1 stop 10260
    $ netstat -na  | grep 10260
c. $ ./mwactl.sh stop_dispatcher
2. Verify that MWA servers are stopped.
a. $ ps -ef | grep mwa
b. No processes should be found.
3. Locate the mwa.cfg file in the $MWA_TOP/secure directory.
4. Edit the mwa.cfg file and configure following parameters
a. Set mwa.DbcFolder=full directory of folder containing the .dbc file ending with “/”:
mwa.DbcFolder=/d01/oracle/SVRSUPTappl/fnd/11.5.0/secure/SVRSUPT_svrsuptsql1
b. Set mwa.DbcFile=dbc file name with extension:
mwa.DbcFile=SVRSUPT
c. Set mwa.logdir=full directory of log file without the trailing “/”:
mwa.logdir=/d01/oracle/SVRSUPTcomn/admin/log/SVRSUPT_svrsuptsql1
d. Set mwa.LogLevel=error or trace:
mwa.LogLevel=error
e. Set mwa.TelnetPortNumber=port number
mwa.TelnetPortNumber=10240
f. Set mwa.DropConnectionTimeout= no. of minutes:
mwa.DropConnectionTimeout=5
g. Set mwa.StaleSessionTimeout= no. of minutes:
mwa.StaleSessionTimeout=60
h. Specify the port number and machine for running the dispatcher:
mwa.Dispatcher=svrsuptsql1.d-rco.com:10328
i. Specify the server manager telnet server information
mwa.TelnetServer=svrsuptsql1.d-rco.com:10260;10262
5. Start the MWA telnet servers
a. $ cd $MWA_TOP/bin
b. $ nohup ./mwactl.sh start 10260 &
6. Start the dispatcher
a. $ nohup ./mwactl.sh start_dispatcher &

++++++++++++++++++++++++++++++++
how to check it is working or not:
Start  à  Run à cmd à telnet hostame:portnumber  ß press enter  , it will show 4 options select ,
 appropriate option & give username & password.

History of CR error out

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;