Oracle DBA: September 2014

Friday, September 12, 2014

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;

Query To find free space, used space in a tablespace individual datafile

SELECT TO_CHAR(sysdate, 'DD/MM/YY') Today
, TO_CHAR(sysdate, 'hh24:mi:ss') Time
, df.tablespace_name "Tablespace"
, df.file_name "File_name"
, count(*) "Extents"
, NVL(df.bytes,0)/1024 "KBTotal"
, (NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024 "KBUsed"
, SUM(NVL(fs.bytes,0))/1024 "KBFree"
, (((NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024)*100)/((NVL(df.bytes,0)
/1024)) "%Used"
, ((SUM(NVL(fs.bytes,0))/1024)*100) / (NVL(df.bytes,0)/1024) "%Free"
, MAX(NVL(fs.bytes,0))/1024 "MaxExtentKB"
FROM dba_data_files df
, dba_free_space fs
WHERE df.file_id= fs.file_id(+)
and df.tablespace_name='MVD_LARGE'
GROUP BY df.tablespace_name
, df.file_name
, df.bytes
ORDER BY df.tablespace_name

FNDCPASS

In Oracle Applications, we have an FND functionality for changing the passwords for application users,
product schema passwords, and “APPS and APPLSYS.” The location of FNDCPASS utility is $FND_TOP/bin directory.

FNDCPASS Usage:

FNDCPASS logon 0 Y system/password mode username new_password
where logon is username/password[@connect]
system/password is password of the system account of that database
mode is SYSTEM/USER/ORACLE
username is the username where you want to change its password
new_password is the new password in unencrypted format
example FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

If you just type FNDCPASS and press enter, it will give you these details.

The first usage

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
is for changing the password for apps and applsys. These are the database schema users (most important for application to work).
Password for both these users should be in synch. You can change the password of these users using this command. Note that this is
the only way to change the password for apps and applsys. Please do not try any other method for changing apps and applsys password.
Oracle recomends using FNDCPASS only to change apps and applsys password. Also note that using this command will change the password for both apps and applsys.

Following activities will take place

(1) applsys validation. (make sure APPLSYS name is correct)
(2) re-encrypt all password in FND_USER
(3) re-encrypt all password in FND_ORACLE_USERID
(4) update applsys’s password in FND_ORACLE_USERID table.
(5) Update apps password in FND_ORACLE_USERID table.

Also changes are made in DBA_USERS table.

The second usage

FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
is for changing password for any other product schema like MSC, GL etc.
Following activities will take place

(1) update GL’s password in FND_ORACLE_USERID table. The new password is re-encrypted with the current applsys password.

If GL does not exists, step (2) below does not happen. Message for invalid oracle user is written in the log file.

(2) alter user to change GL’s password.

The third usage

FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME
is for changing the application level passwords like sysadmin etc used for logging into application.

Following activities will take place

(1) update VISION’s password in FND_USER table. The new password is re-encrypted with the current applsys password.

If VISION does not exist, message for invalid application user is written in the log file.
No products affected by the patch

When you run FNDCPASS command it will check the integrity of all schema password in the application.
If any of the password is corrupt then this will through and error and will not change the password.

The tables that it uses is FND_USER and FND_ORACLE_USERID. All the application passwords and schema passwords are stored in these two tables.
Ofcourse DBA_USERS will have the schema users and password stored as well.

When we run FNDCPASS it will update all the above 3 tables.

Best usage of FNDCPASS

Before using FNDCPASS:

1) Always keep the back of tables FND_USER and FND_ORACLE_USERID. You can take back of these tables using CREATE TABLE — AS SELECT * FROM —.
You must have backup of these tables before running FNDCPASS. In case if FNDCPASS fails then it might corrupt the passwords of your application
and worst can happen that the application wont come up. So always be cautions about this command.

2) If possible also keep an export dump of these two tables.

3) verify each arguement you are providing to FNDCPASS. Like verify that apps and system passwords you are providing is correct.

4) Never update apps, applsys or any schema password directly from database using the alter command. Always use FNDCPASS.
System password can be set directly using ALTER command in database.

Issue with APPLSYS and APPS password

Issue 1:

As you know that apps and applsys password should be in synch and should be changed using FNDCPASS.

There can be situation where a novice user changes applsys password from the backend database.
In that case when you try to start the services it will show following error

APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.

You can even reproduce this issue (ofcourse after taking the backup of FND_USER and FND_ORACLE_USERID table) using the following steps

1. Use the ALTER USER command to change the APPLSYS password

2. Try to run the adstrall.sh script to start Apps services.

3. You will get an error “Cannot complete applications logon. You may have entered an invalid applications password,
or there may have been a database connect error.”

4. Then try FNDCPASS to fix password and you will get the error the APP-FND-01496 error.

If this situation happens then you cannot access the application. Infact the services even wont start.

Resolution to such problem is to rollback the 2 tables FND_USER and FND_ORACLE_USERID. Once you rollback the tables,
apps and applsys passwords will be in synch and password will be older one. You can then run FNDCPASS and change the password.

Issue 2:

Some times when you run FNDCPASS, you get following error

APP-FND-01502: Cannot encrypt application ORACLE password
Cause: Application Object Library was unable encrypt your ORACLE password.
Action: Contact your support representative. (ORACLEUSER=APPS_SERV)

The error comes because the table fnd_oracle_userid contain rows for schemas that does not exist. Those rows must be deleted from the table.

Use the following query to get the details of the schema that doest not exists

select * from fnd_oracle_userid
where oracle_username not in
(select username from all_users);

The rows returned by this query can be deleted from FND_ORACLE_USERID table. This will resolve this issue.

Issue 3:

There can be situation where users has update APPLSYS password using ALTER command in database directly and also you dont have backup of those tables.
Under such situation, it is very difficult to recover the application and make it working. Still following methodology is proposed which might help you
to restore the password back and make your application work fine.

For this to work you should have some other application (may be debug or UAT) which is having the same passwords or default passwords for schemas.
If you have such application the following the below steps in the application which is affected by password mismatch.

This method is for resetting apps and applsys passwords. Below are the SQL statements that will help you reset the APPS and APPLSYS passwords to APPS,
the APPLSYSPUB password to PUB, and the SYSADMIN password to SYSADMIN.

WARNING: This procedure will cause all user passwords to become invalid. ALL users passwords will need to be reset through the sysadmin responsibility.

Step 1) Reset the Oracle User IDs

Open a SQL*Plus as SYSTEM and reset the passwords for the APPS, APPLSYS, and the APPLSYSPUB Oracle user ID:

ALTER USER apps IDENTIFIED BY apps;
ALTER USER applsys IDENTIFIED BY apps;
ALTER USER applsyspub IDENTIFIED BY pub;

Step 2) Backup the FND_ORACLE_USERID and FND_USER tables (even though these tables are right now corrupted, do take a backup.
You can restore the same when ever you want).

Open a SQL*Plus session as APPLSYS and backup the tables:

create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);

create table FND_USER_BAK as (select * from FND_USER);

Step 3) Reset the APPS and APPLSYS application encrypted passwords

Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.

update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8′
where ORACLE_USERNAME in (‘APPS’, ‘APPLSYS’);
commit;

This encrypted string we are updating is the default encrypted string for apps. So if your application is having apps password
the encrypted string will look like this. We are updating this encrypted string here directly.

Verify the table update:

select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME IN (‘APPS’, ‘APPLSYS’);

Step 4) Reset the APPLSYSPUB application encrypted password

Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.

update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZG31EC3DD2BD7FB8AD2628CE87DDDF148C1D2F248BE88BE987FDF82830228A88EF44BC78BC7A9FAD4BFB8F09DAD49DF7280E’
where ORACLE_USERNAME = (‘APPLSYSPUB’);
commit;

The above encrypted string is the encrypted string for password pub. If your applsyspub password is pub then the encrypted string
in FND_ORACLE_USERID will look like this.

Verify the table update:

select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME = ‘APPLSYSPUB’;

Once these updates are done, try your luck by running FNDCPASS and it should work fine.

References

Metalink note ID 445153.1

Metalink note ID 429244

find the SQL Text

SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = &SID;

please supply SID, you will get HASH Value

SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (&HASH) ORDER BY PIECE;

Please supply HASH Value, you will get SQL text which is consuming more cpu load.

SQL> SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = &SID;
Enter value for sid: 1526
old 1: SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = &SID
new 1: SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = 1526

HASH
----------
1784552006

SQL> SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (&HASH) ORDER BY PIECE;
Enter value for hash: 1784552006
old 1: SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (&HASH) ORDER BY PIECE
new 1: SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (1784552006) ORDER BY PIECE

SQL_TEXT
----------------------------------------------------------------
select null from wf_notifications where notification_id = :1 for
update nowait

SQL> set lines 400
set pages 200
col MODULE for a20
col ACTION for a20
col MACHINE for a20
col OSUSER for a20
select sid,serial#,STATUS,SQL_HASH_VALUE,MODULE,ACTION,LAST_CALL_ET/60,PROCESS,PADDR,OSUSER,MACHINE from v$session
where sid=SQL> SQL> SQL> SQL> SQL> SQL> 2 1526;

How to find a product version in Oracle Applications

1.Go to $AD_TOP/sql
2.Connect to sqlplus as apps/apps
3.Run (@adutconf.sql)
4.Output will be in same directory in the name of adutconf.lst

file version check

strings -a <file name> | grep '$Header'

Enabling Archivelog mode in RAC environment

########### Do this for all nodes##########################################

ALTER SYSTEM SET log_archive_dest_1='location=<Archive dest>/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

srvctl stop database -d <db name>

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE;

srvctl start database -d <db name>

DR Sync check script

Select al.thrd "Thread", almax "Last Seq Generated", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

Details regarding sessions which are causing high waits and cpu

select nvl(ss.USERNAME,'ORACLE PROC') username,
ss.SID, ss.serial#,ss.sql_hash_value,sa.executions,VALUE cpu_usage from v$session ss,
v$sesstat se, v$sqlarea sa, v$statname sn where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.sql_hash_value = sa.hash_value
and value > 0
order by VALUE desc;

DB Uptime

SELECT host_name,
instance_name,
TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,
FLOOR(sysdate-startup_time) days
FROM sys.v_$instance;

DB Size

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "Total DB Size in GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) d;

DB Links

Execute this command as sysdba

SQL> select owner||’,’||db_link||’,’||username||’,’||host||’,’||created from dba_db_links;

After that you can check the status of DB links to know which DB links are working/not working

SQL>Select sysdate from dual@< DB_LINK> ;

Custom Forms Registration

forms compilation in R12

$ frmcmp_batch module=<path to the fmb file> userid=apps/apps output_file=<path to the fmx file> compile_all=special batch=yes

forms compilation in 11i

$ f60gen module=<formname>.fmb userid=apps/<apps_pwd> output_file=/forms/US/<formname>.fmx

1) Log into the forms tier.
2) Set the applications environment
3) Ensure that the $FORMS_PATH includes $AU_TOP/resource and $AU_TOP/resource/stub, for example
echo $FORMS_PATH
/u01/oracle/DEV/apps/apps_st/appl/au/12.0.0/resource:
/u01/oracle/DEV/apps/apps_st/appl/au/12.0.0/resource/stub
4) Compile the form
a)- If you are using forms customizations (CUSTOM.pll) then run the command below to compile the form.

frmcmp_batch.sh module=<path to fmb file> userid=APPS/APPS output_file=<full path to fmx output file> module_type=form compile_all=special

For eg:-
frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXX.fmb userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXX.fmx module_type=form compile_all=special

b) If you are NOT using forms customizations, then run the command below to compile the form.

frmcmp_batch.sh module=<path to fmb file> userid=APPS/APPS output_file=<full path to fmx output file> module_type=form

For example..

frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXX.fmb userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXX.fmx module_type=form

Registering the customized forms in to oracle Applications

Registering a form

Navigation--Application developer -- Application -- Form

Form Name-- .fmx file name without extension
Application Name -- GL/AP/XXX
User Form Name -- from name when we selection a form using the function window
Description-- Form Description

Registering Form Function

NAvigation -- Application Developer --Application -- Function

CPU Usage details

Cpu Usage with PID:
=========================

set header off
set pages 0
select
'spid, Sid, Serial# : '|| p.spid ||' , '|| s.sid||','||s.serial# a,
'DB User / OS User : '||s.username||' / '||s.osuser a,
'Login Time : '||TO_CHAR(s.logon_time,'mm-dd-yyyy::hh24:mi:ss'),
'Module : '||s.module "Module Name" ,
'Clients Process : '||s.process ,
'Action : '||s.action,
'Program : '||s.program,
'Clients Machine Name : '||s.machine,
'Terminal : '||P.terminal,
'last call et : '||s.last_call_et ,
'Event : '||s.event,
'Status : '||s.status
from v$session s, v$process p
where s.paddr = p.addr
and p.spid like '%&Unix_PID%';

with sid:-
==================

select sql_text from v$sqltext_with_newlines t,v$session s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.sid = &SID
order by t.piece ;

select sid,serial#,status,LAST_CALL_ET/60,SQL_HASH_VALUE,module,action from v$session where sid='&sid';

Finding PID with SID:
--------------------

set header off
set pages 0
select
'spid, Sid, Serial# : '|| p.spid ||' , '|| s.sid||','||s.serial# a,
'DB User / OS User : '||s.username||' / '||s.osuser a,
'Login Time : '||TO_CHAR(s.logon_time,'mm-dd-yyyy::hh24:mi:ss'),
'Module : '||s.module "Module Name" ,
'Clients Process : '||s.process ,
'Action : '||s.action,
'Program : '||s.program,
'Clients Machine Name : '||s.machine,
'Terminal : '||P.terminal,
'last call et : '||s.last_call_et ,
'Event : '||s.event,
'Status : '||s.status
from gv$session s, gv$process p
where s.paddr = p.addr
and s.sid like '%&sid';

Concurent Request check with sid:
----------------------------------

select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status
from v$process v, v$session s, applsys.fnd_concurrent_requests f
where s.paddr=v.addr
and f.oracle_process_id=v.spid
and trunc(f.request_date)=trunc(sysdate)
and sid=&SID;

Backup issue

1.- Check which is the wrong entry on control file. To find the row, connect to this database with sqlplus and execute:

SELECT * FROM V$BACKUP_DATAFILE WHERE CHECKPOINT_TIME IS NULL;
SELECT * FROM V$DATAFILE_COPY WHERE CHECKPOINT_TIME IS NULL;

2.- Check the handle of the backup or copy

SELECT * FROM V$BACKUP_DATAFILE WHERE CHECKPOINT_TIME IS NULL;
SELECT NAME,CREATION_TIME,CHECKPOINT_CHANGE#,COMPLETION_TIME
FROM V$DATAFILE_COPY WHERE CHECKPOINT_TIME IS NULL;

For control file copy, NAME will show the handle for deleting.

For control file backup we need to find the backup piece handle as follows:

SELECT * FROM V$BACKUP_SET
WHERE SET_STAMP=<SET_STAMP> AND SET_COUNT=<SET_COUNT>;
SELECT * FROM V$BACKUP_PIECE
WHERE SET_STAMP=<SET_STAMP> AND SET_COUNT=<SET_COUNT>;
Where <SET_STAMP> and <SET_COUNT> are from above query output
V$BACKUP_DATAFILE;

Once we know the wrong entry we have the following options to fix:

1.- Connect to RMAN without catalog and delete the backuppiece

2.- If deleting the backup or copy of the control file is not possible for whatever reason,. Then another option to fix the issue is reset the control file section. This willcleanup the whole control file section.

This will avoid the issue, but all the rows on v$backup_datafile or v$datafile_copy will be deleted. This means that the information regarding backups or file copies that won't be in RMAN catalog database will be lost.

The steps to follow are:

2.1 Connect to target database with sqlplus with SYS user as SYSDBA

sqlplus /nolog
sql>connect sys/<password> as sysdba
sql> select rownum-1, type from v$controlfile_record_section;
- and look for 'BACKUP DATAFILE'. or 'DATAFILE COPY' section

Then clear that section using -
sql>execute sys.dbms_backup_restore.resetCfileSection(<Section_id#>);

NOTE that this option might end up on ORA-600 in which case the section cannot be cleared

3.- Another option is to recreate the controlfile, in this case all the information in the controlfile that won't be in RMAN catalog will also be lost. The steps are in following MOS notes:

How to Recreate a Controlfile Doc ID <<735106.1>>
Recreating the Controlfile in RAC Doc ID <<118931.1>>

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

Configure EBS on OEM

Metalink note 1434392.1 (R11i / R12.0 / R12.1 : Getting Started with Oracle Application Management Pack (AMP)
for Oracle E-Business Suite, Release 12.1.0.1.0)

Metalink note 1532970.1 (R11i/R12.0/R12.1/R12.2 : Getting Started with Oracle Application Management Pack for
Oracle E-Business Suite (AMP) Release 12.1.0.2.0). This contains latest information related the latest version
of Oracle Application Management Pack for Oracle E-Business Suite (AMP) Release 12.1.0.2.0.

As per Metalink note 1532970.1 (R11i/R12.0/R12.1/R12.2 : Getting Started with Oracle Application Management Pack
for Oracle E-Business Suite (AMP) Release 12.1.0.2.0):

<<
The following Oracle Enterprise Manager Cloud Control releases, Oracle E-Business Suite releases,
and platforms are supported:

Oracle Enterprise Manager Cloud Control 12c Release 3 (12.1.0.3.0)
Oracle E-Business Suite:
12.2: Releases 12.2.2 and 12.2.3
12.1: Release 12.1 with R12.ATG_PF.B.delta.3
12.0: Release 12.0.4 with R12.ATG_PF.A.delta.6
11i: Release 11.5.10 CU2 with ATG_PF.H RUP6 or higher

Verified the document.

The below patches are applied on application server.

SQL> select bug_number,creation_date from ad_bugs where bug_number='&n';

BUG_NUMBER CREATION_
------------------------------ ---------
9535311 12-JUN-11

BUG_NUMBER CREATION_
------------------------------ ---------
9171650 17-MAR-13

BUG_NUMBER CREATION_
------------------------------ ---------
7648877 17-JAN-10

1. Please download PatchLevelATG11i.sql script from the SR, run it on the source instance using SQLPlus
(connected as APPS user) and upload the output file (ptch_level11i.txt).

2. Please run on the source instance and provide the results of the following OS commands:

-for each apps tier's node:

adident Header $AD_TOP/bin/adconfig.pl
adident Header $COMMON_TOP/admin/scripts/$CONTEXT_NAME/adpreclone.pl
adident Header $JAVA_TOP/oracle/apps/ad/clone/ApplyDatabase.class
adident Header $COMMON_TOP/clone/jlib/java/oracle/apps/ad/clone/util/OracleHomeCloner.class
adident Header $JAVA_TOP/oracle/apps/ad/clone/util/OracleHomeCloner.class

-for each db tier's node:

grep -i '$Header' <RDBMS ORACLE_HOME>/appsutil/bin/adconfig.pl
grep -i '$Header' <RDBMS ORACLE_HOME>/appsutil/scripts/$CONTEXT_NAME/adpreclone.pl
strings -a <RDBMS ORACLE_HOME>/appsutil/java/oracle/apps/ad/clone/ApplyDatabase.class | grep -i 'Header'
strings -a <RDBMS ORACLE_HOME>/appsutil/java/oracle/apps/ad/clone/util/OracleHomeCloner.class | grep -i 'Header'
strings -a <RDBMS ORACLE_HOME>/appsutil/clone/jlib/java/oracle/apps/ad/clone/util/OracleHomeCloner.class | grep -i 'Header'

where <RDBMS ORACLE_HOME> should be replaced with your RDBMS ORACLE_HOME folder

Please follow Metalink note 1537431.1 (Troubleshooting Oracle E-Business Plug-in 12.1.0.1),
sections 2 (Verify Installed Plug-ins in the Oracle E-Business Suite nodes) & 3 (Check the hostname information)
and provide results to ensure that agents are deployed correctly on all EBS nodes.

Kindly refer to the below document to deploy the EBS plugin:

How to Deploy the Latest Oracle Fusion Middleware Plug-in on OMS and Agent in Enterprise Manager
Cloud Control 12c (Doc ID 1401284.1)

The document is specifically for Middleware Plug-in, instead we need to download and apply the EBS Plugin,
but all the steps remains the same.

Concurrent Request Status

select phase_code,status_code from fnd_concurrent_requests where request_id='&RequestID'

Concurrent Managers

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2.Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';

3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

4.To find sid,serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';

5.To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/

7. To find child requests

set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';

8. Cancelling Concurrent request :

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

9. Kill sessions program wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';

10 .Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

12. Oracle Concurrent Request Error Script (requests which were error ed 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;

13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;

14.Concurrent Program enable with trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID

Concurrent Request Hold and Unhold

Holding Concurrent Request:
===============================

update applsys.fnd_concurrent_requests
set hold_flag = 'Y',last_update_date =sysdate, last_updated_by = -1
where phase_code = 'P' and hold_flag<>'Y';

Unholding Concurrent Request:
=================================

update applsys.fnd_concurrent_requests a
set a.hold_flag = 'N' ,
last_update_date = sysdate,
last_updated_by = -1
where phase_code = 'P'
and a.hold_flag = 'Y'
and last_updated_by = -1
and last_update_date > sysdate-1;

Concurrent Program Past Runs

select fcp.user_concurrent_program_name
,fcr.request_date
,fu.user_name
,fcr.actual_start_date
,fcr.actual_completion_date
,fcr.phase_code
,fcr.status_code
,fcr.argument1
,fcr.argument2
,fcr.argument3
from fnd_concurrent_programs_vl fcp
,fnd_concurrent_requests fcr
,fnd_user fu
where fcp.user_concurrent_program_name like '%Ship Set%'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.requested_by = fu.user_id
order by fcr.actual_completion_date desc

Common Oracle recovery scenarios

1. Loss of data file(s) of SYSTEM tablespace
============================================
Solution:
SQL> SHUTDOWN IMMEDIATE;
SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)
SQL> CONNECT / AS SYSDBA
SQL> STARTUP MOUNT
SQL> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN;

2. Loss of data file(s) of Non-System tablespace.
=================================================
Solution:
>Don’t need to shutdown database.
SQL> ALTER TABLESPACE … OFFLINE;
SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)
SQL> RECOVER TABLESPACE your_tablespace_name;
SQL> ALTER TABLESPACE your_tablespace_name ONLINE;
Or recover by using datafile(s).
SQL> ALTER DATAFILE ‘/u01/…’ OFFLINE;
SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)
SQL> RECOVER DATAFILE ‘/u01…’;
SQL> ALTER DATAFIL ‘/u01/…’ ONLINE;
Or shutdown database.
SQL> SHUTDOWN IMMDIATE;
SQL> (Restore your data backup files from backup to related loss system’s datafiles)
SQL> STARTUP MOUNT;
SQL> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN;

3. Loss of data file(s) of rollback OR undo segments tablespace.
================================================================
A datafile in a rollback segment tablespace has been lost or damaged to a point that
Oracle cannot recognize it anymore.
If you try to startup the database will result in ORA-1157, ORA-1110, and possibly
an operating system level error such as ORA-7360. Trying to shut down the database
in normal or immediate mode will result in ORA-1116, ORA-1110, and possibly
an operating system level error such as ORA-7368.
PLEASE NOTE that before using _corrupted_rollback_segments parameter, you should
explore all other options. This will require extra caution. Call Oracle Customer Support
if you have any questions or need any assistance.
The Database is down:
Assuming that your database was cleanly shutdown (check your alert.log)
SQL> SHUTDOWN IMMEDIATE/NORMAL
SQL> STARTUP RESTRICT MOUNT
SQL> ALTER DATABASE DATAFILE '' OFFLINE DROP;
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE INCLUDING CONTENTS;
SQL> -- Recreate the rollback tablespace with all its rollback segments.
Remember to bring the rollbacks online after you create them.
Assuming that you cann't open your database and you get the following error
messages: ORA-604, ORA-376, and ORA-1110.
-- Comment out the ROLLBACK_SEGMENTS parameter and add the following line:
_corrupted_rollback_segments = ( ,...., )
with exact the same rollbacks.
WARNING: Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as
instructed by Oracle Customer Support.
SQL> STARTUP RESTRICT
SQL> DROP TABLESPACE INCLUDING CONTENTS;
SQL> Recreate the rollback tablespace with all its rollback segments.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
-- put back your ROLLBACK_SEGMENTS parameter in the init.ora file.
-- remove _CORRUPTED_ROLLBACK_SEGMENTS PARAMETER
SQL> -- Rebuild the database.
Assuming that database was not cleanly shutdown:
-- Restore the lost file from a backup.
SQL> STARTUP MOUNT
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
SQL> ALTER DATABASE DATAFILE '' ONLINE;
(if datafile is offline)
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;
SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
If NOARCHIVELOG Mode and the CHANGE# is GREATER than the minimum FIRST_CHANGE#
of your logs, the datafile can be recovered.
SQL> RECOVER DATAFILE '';
SQL> ALTER DATABASE OPEN;
Assuming that the database is up and it is in ARCHIVELOG mode:
SQL> ALTER DATABASE DATAFILE '' OFFLINE;
SQL> -- Restore it from your backup.
SQL> RECOVER DATAFILE '';
SQL> ALTER DATABASE DATAFILE '' ONLINE;
-OR-
SQL> ALTER SYSTEM KILL SESSION ' , ' -- The transaction in RBS involved.
SQL> ALTER ROLLBACK SEGMENT OFFLINE;
SQL> DROP ROLLBACK SEGMENT ;
SQL> DROP TABLESPACE INCLUDING CONTENTS;
SQL> CREATE TABLESPACE rbs ...;
SQL> CREATE ROLLBACK SEGMENT rbs01 ...;
SQL> ALTER ROLLBACK SEGMENT ... ONLINE;
In this case to access to data you will get a message like this:
ORA-00376: file 20 connot be read at this time
File 2 happens to be one of the data files that belongs to the rollback segment tablespace.
Solution:
SQL> select segment_name, status from dba_rollback_segs;
(If status are in “NEED RECOVERY”)
SQL> SHOW PARAMETER undo
SQL> CREATE UNDO TABLESPACE myundotbs DATAFILE ‘/u01/…’ SIZE 50M;
SQL> ALTER SYSTEM SET undo_tablespace=’myundotbs’;
SQL> DROP TABLESPACE undotbs;
(Remove all related OS datafiles)

4. Loss of Rollback segments while there were transaction activities;
=====================================================================
For example:
SQL> CREATE TABLE mytest (col1 varchar2(20));
SQL> BEGIN
FOR this IN 1..100 LOOP
INSERT INTO mytest VALUSE (this);
END LOOP;
END;
/
SQL> /* Notice that we didn’t commit any of above transaction */
SQL> /* shutdown abort or any crash on rollback datafiles will cause
The following error messages if we start the database. */
ORA-01157: cannot identify/lock data file 20 – see DBWR trace file
ORA-01110: data file 20: ‘/u01/…’

Solution:
If you shutdown the server then you will get a message like this:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'C:\MYUNDOTBS_01.DBF'
SQL> STARTUP MOUNT
SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;
SQL> ALTER DATABASE OPEN;
SQL> /* remember you can not access to that table with transaction */
SQL> SELECT segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)
SQL> /* replace your backup file and recover the tablespace */
SQL> RECOVER TABLESPACE your_tablespace_name;
SQL> ALTER TABLESPACE your_tablespace_name ONLINE;
SQL> ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)
If you are not shutdown:
SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;
SQL> /* remember you can not access to that table with transaction */
SQL> SELECT tablespace_name, segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)
SQL> /* replace your backup file and recover the tablespace */
SQL> RECOVER TABLESPACE your_tablespace_name;
SQL> ALTER TABLESPACE your_tablespace_name ONLINE;
SQL> ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)

5. Loss of an UN-archived Online log file.
==========================================
Solution:
SQL> /* Your database is done. */
SQL> /* Replace all your datafiles and online redo logfile, but not controlfiles. */
SQL> host cp … …
SQL> STARTUP MOUNT
SQL> RECOVER DATABASE UNTIL CANCEL; -- UNTIL TIME …
SQL> ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.
SQL> SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */

Loss of your Online Redo-log files.
==================================
Solution:
SQL> /* If you loss your online redo-log files, your instance will hung or crash.
If not, try to shutdown abort. Restore all files from your cold backup
with exception of your control files. */
SQL> STARTUP MOUNT
SQL> /* Now do an incomplete recovery recover using CANCEL or TIME option */
SQL> RECOVER DATABASE UNTIL CANCEL;
/* you should apply up to your last archive and then cancel the recovery process */
SQL> ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.
SQL> SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */

6. Loss of your control file.
============================
Solution:
SQL> /* Assuming you have already backup controlfile to trace */
SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;
SQL> /* modify and run your trace file and your control file is up to date */
SQL> /* if not, then copy a old control file */
SQL> STARTUP MOUNT;
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
SQL> ALTER DATABASE OPEN;

7. Lost everything Disaster recovery RAC.
=========================================
$ export ORACLE_SID=biifsprd

$rman target /

RMAN> restore spfile from '/u01/backups/RMAN/biifsprd/rman_BIIFSPRD_c-2568250686-20111022-00_full.autoCtrlBkup';

SQL> create pfile from spfile

SQL> shutdown immediate;

modify init parameter file for single instance;

SQL> startup nomount pfile='?/dbs/initbiifsprd.ora';

RMAN> restore controlfile from '/u01/backups/RMAN/biifsprd/rman_BIIFSPRD_c-2568250686-20111022-00_full.autoCtrlBkup';

SQL> alter database mount;

RMAN> restore database;

SQL> recover database;

SQL> alter database open resetlogs;

SQL> create SPFILE='+DATA/biifsprd/spfilebiifsprd.ora' from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initbiifsprd.ora.bak';

modify controlfile location
controlfiles='+DATA/biifsprd/controlfile/current.283.765417223','+RECOVERY/biifsprd/controlfile/current.268.765417223'

SQL> create SPFILE='+DATA/biifsprd/spfilebiifsprd.ora' from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initbiifsprd.ora.bak';

convert to RAC

set ORACLE_SID to correct one.

SQL> startup;

SQL> shutdown immediate;

$ srvctl start database -d biifsprd