ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ISSUE:

Unable to recreate the index due to below error.


SQL> create index ICX.XX_ICX_CAT_ITEMS on ICX.ICX_CAT_ITEMS_CTX
(INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,SEQUENCE) online compute statistics parallel 8 nologging   2
  3  ;
create index ICX.XX_ICX_CAT_ITEMS on ICX.ICX_CAT_ITEMS_CTX
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop index ICX.XX_ICX_CAT_ITEMS;
drop index ICX.XX_ICX_CAT_ITEMS
               *
ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Solution:

We have noticed that this error has occurred due to above object is being used by Some concurrent request.

And also observed that same session is shoing in BLOCKING SESSIONS.


1. Wait until that concurrent request gets completed.

OR


2. Cancel that perticular request and kill the the SID of that program.


Now try to drop that index and create it

ADOP Patching failed with JTF user lock error

Issue:

We faced JTF user lock issue while applying the patch using ADOP.

==> We checked in dba_users table and noticed that JTF user got locked and expired

==> We have unlocked and reset the password of JTF uing FNDCPASS.

==> Initiated the patch again with restart=yes

adop phase=apply restart=yes

==> But we faced JTF user lock issue again

Solution:

Give abandon=yes and initiate the patch again

adop phase=apply abandon=yes

ORA-38881: Cannot drop tablespace TABLESPACE_STG on primary database due to guaranteed restore points.

Issue:

Unable to drop a tablespace due to below error.

SQL> drop tablespace TABLESPACE_STG including contents and datafiles;

ORA-38881: Cannot drop tablespace TABLESPACE_STG on primary database due to guaranteed restore points.

Soloution:

We need to set below underscore parameter as "true"

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE

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

_allow_drop_ts_with_grp              boolean     FALSE

SQL>

FIX:

SQL> alter system set "_allow_drop_ts_with_grp"=true;

System altered.

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE

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

_allow_drop_ts_with_grp              boolean     TRUE

SQL>

SQL> drop tablespace TABLESPACE_STG including contents and datafiles;

Tablespace dropped.

Revert the Changes :
-------------------------

You can revert the underscore parameter again.

alter system set "_allow_drop_ts_with_grp"=false;

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_drop_ts_with_grp              boolean     FALSE
SQL>

How to restart workers when patching using adpatch

How to restart the workers in adpatch

Sometimes when patching E-Business Suite some workers might get stuck. The patch is applied via workers and these workers are controlled by utility called as adctrl ( ad control ). 

Open another session & login as application O.S. user (like applmgr ) load apps enviroment and run adctrl , you will see option 1 to see status of worker . Check which worker is showing as failed state and then go to that worker log file in same location i.e. APPL_TOP/admin/TWO_TASK or SID /log you will see log file like a dwork001.log adwork002.log ….adwork021.log so if your worker 12 failed open log file adwork012.log & fix the problem then again in adctrl select option restart failed worked & then give worker number you want to restart . 

If you want to skip a worker , this option is not visible & do it at your own risk there is option 8 ( depending on your AD version , it might vary so if adctrl is displaying 7 options skip worker will be option 8 & if adctrl is showing 6 options skip worker will be option 7) 

You can select individual worker or a group of worker separated by - like 2-5 will select worker from 2 to 5.

Oracle Apps R12.2 Log Files Locations

This post covers Log File Location in Oracle E-Business Suite  R12.2 that every Oracle Apps DBA must know. Log File helps in troubleshooting and is first thing you must look when looking at any issue .

1. R12.2 Start/Stop Log Files:

a) Startup/Shutdown error message text files like adstrtal.txt, adstpall, adapcctl.txt, adcmctl.txt…for services like oacore, forms, apache, opmn, weblogic admin server/node manager etc

$INST_TOP/logs/appl/admin/log

Log files for start/stop of services from $ADMIN_SCRIPTS_HOME

Here is an image representing EBS R12.2 Service control (start/stop) logs:

Green colour boxes indicates location of log files 

 

 2. Patching Log files:

a) The Online Patching (ADOP) log files are located on the non-editioned file system (fs_ne), under :

$NE_BASE/ EBSapps/ log/adop/<adop_session_id>/<phase>_<date>_<time>/<context_name>/log

Note: fs1 and fs2 are dual file system for software where as fs_ne contains non editioned objects 

This log directory will contain patch logs, patch worker logs and other patch related log files created for specific purposes.
Also, some patch tasks may create separate log files in the same directory from where patch executable is started

3. Log files for concurrent programs/managers

$NE_BASE/ inst/<CONTEXT_NAME>/logs/appl/conc/log

 Output files for concurrent programs/managers

$NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/out

Here is an image representing EBS R12.2 Non-Editioned file system Logs:

Patching Log file Location:

4. Cloning related Log Files:

a) Pre-clone log files in source instance

     I. Database Tier

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

    II. Application Tier

$INST_TOP/admin/log/ (StageAppsTier_MMDDHHMM.log)

b) Clone log files in target instance

    I. Database Tier

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_<time>.log

    II. Apps Tier

$INST_TOP/admin/log/ApplyAppsTier_<time>.log

If your clone on DB Tier fails while running txkConfigDBOcm.pl (Check metalink note – 415020.1)
During clone step on DB Tier it prompts for “Target System base directory for source homes” and during this you have to give like /base_install_dir like ../../r12 and not oracle home like ../../r12/db/tech_st_10.2.0

5. AutoConfig log file:

Autoconfig can be run on Application Tier or Database Tier

a) Autoconfig Log Location on Application tier :

$INST_TOP/admin/log/$MMDDHHMM/adconfig.log

b) Autoconfig Log Location on Database tier:

$ORACLE_HOME/ appsutil/ log/ $CONTEXT_NAME/ <MMDDHHMM>/  *.log

6.  Installation Related Logs:

There are four main phases (in context of logs) during R12.2 installation, here is log location of all four phases .

a) The Rapidwiz Configuration File is saved in below locations:

$TMP/<MMDDHHMM>/conf_<SID>.txt
$INST_TOP/conf_<SID>.txt
<RDBMS ORACLE_HOME>/ appsutil/ conf_<SID>.txt

b) Pre-install system check logs:

$TMP/<MMDDHHMM>/<MMDDHHMM>.log
$TMP/dbPreInstCheck.xxxxx.log
$TMP/wtprechk.xxxx/wtprechk.xxxx.log
<Global Inventory>/logs/installActions<timestamp>.log

c) Database tier

I. Main Installation log:

RDBMS $ORACLE_HOME/ appsutil/ log/ $CONTEXT_NAME/ <MMDDHHMM>.log

II. ORACLE HOME installation logs:

RDBMS $ORACLE_HOME/ temp/ $CONTEXT_NAME/ logs/ *.log
<Global Inventory>/logs/*.log

d) Application tier

Both primary(Run) and Secondary Edition (Patch) file systems will each contain these logs:

    I. Main Installation Log:

$INST_TOP/logs/<MMDDHHMM>.log

    II. FMW and OHS TechStack Installation/Patching logs:

$APPL_TOP/admin/$CONTEXT_NAME/log/*
<Global Inventory>/logs/*.log

    III. Forms Oracle Home installation logs:

$APPL_TOP/admin/$CONTEXT_NAME/log/*.log

Here is an image representing the R12.2 Installation logs

 

6. Fusion Middleware (FMW_HOME) Log Files:

Oracle 12.2 includes Fusion Middleware (WebLogic & OHS) as new components 

 a) Log files for OPMN and OHS processes: Below directory contains log files related OPMN process(opmn.log), OPMN Debug logs(debug.log), HTTP Transaction logs (access.log), security settings related logs.

$IAS_ORACLE_HOME/ instances/ <ohs_instance>/diagnostics/logs

b) Log files for weblogic node manager: Log file is generated by Node Manager and contains data for all domains that are controlled by Node Manager on a given physical machine.

$FMW_HOME/ wlserver_10.3/ common/ nodemanager/ nmHome1/ nodemanager.log

c) Log files for weblogic Oracle Management Service log file: Initial settings AdminServer and Domain level information is written in this log file.

$EBS_DOMAIN_HOME/sysman/log

d) Log files for server processes initiated through weblogic: Stdout and stderr messages generated by the server instance (server instances like forms, oafm, oacore etc) at NOTICE severity level or higher are written by Weblogic Node Manager to below directory.

$EBS_DOMAIN_HOME/ servers/ <server_name>/ logs/ <server_name>.out

Here is an image representing EBS R12.2 Fusion Middleware Log files:

Figuring Out What EXPDP and IMPDP are Doing Right Now

Starting Oracle 10g, the old export/import tools were replaced by a newer, more sophisticated tool: the data pump.
This new tool had some advantages over the older tool – it was able to run in parallel, it has a better interface and it can be ran from PL/SQL code easily. It can even be restarted or resumed if needed. On the con side, it uses the database DIRECTORY object which means the file we’re exporting to or importing from must reside on the server.

The problem start when we try to diagnose what is going on when there is a performance issue. Since the process we use for exporting might be forked into multiple processes, we can’t really know what is going on behind the scenes.

So, how can we resolve that?

OS Level

First level: how to we recognize the processes that are related to the expdp command?

Answer:

/app01/oracle 17:54> expdp zelkayam2 directory=HISTORY_DIR dumpfile=zelkayam%u.dmp full=y parallel=2 /app01/oracle 17:54> ps -ef | grep expdp   oracle 27854 27567 0 17:54 pts/0 00:00:00 expdp directory=HISTORY_DIR dumpfile=zelkayam%u.dmp full=y parallel=2

Session Level

Level two: How do recognize the sessions within the database?

Answer 1: getting the current jobs – the DATAPUMP_SESSIONS and ATTACHED_SESSIONS columns will show us the current used SIDs:

1	select * from DBA_DATAPUMP_JOBS;

Answer 2: if we actually want to see the current processes and session information, we can join the DBA_DATAPUMP_JOBS view to the V$SESSION and V$PROCCESS views like this:

1 2 3 4 5 6 7 8 9 10 11

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr;   DATE                PROGRAM                                    SID STATUS   USERNAME   JOB_NAME                       SPID    SERIAL#     PID ------------------- -------------------------------------- ------- -------- ---------- ------------------------------ ------- ------- ------- 2010-11-11 17:54:45 ude@tali (TNS V1-V3)                        40 ACTIVE   ZELKAYAM2  SYS_EXPORT_FULL_01             28906     38890      26 2010-11-11 17:54:45 oracle@tali (DM00)                          37 ACTIVE   ZELKAYAM2  SYS_EXPORT_FULL_01             28908      1939      30 2010-11-11 17:54:45 oracle@tali (DW01)                          80 ACTIVE   ZELKAYAM2  SYS_EXPORT_FULL_01             28911     19847      31 2010-11-11 17:54:45 oracle@tali (DW02)                         126 ACTIVE   ZELKAYAM2  SYS_EXPORT_FULL_01             28915      3684      12

Tracing the Sessions

Level 3: How do we trace the actual session?

Okay, so this is a bit tricky. Let’s say we have an export that is running a bit slow and we want to trace the session. We know that the regular trace won’t really help us. We need to start the trace the moment the session start – but we can’t really control this since the export coordinator is doing that for us. How do we trace the sessions?

The answer is that the expdp and impdp has a built in mechanism to trace executions. We can add the (hidden) expdp parameter “trace” so we can mark the command to output a useful trace file. Unfortunately, this parameter is hidden – meaning it does not appear in the expdp help=yes and we still need to figure out how to use it.

The trace parameter is being set using an hexadecimal number with 7 digits. There is no need to add 0x at the beginning of the string – it will not accept decimal numbers or binary inputs. The number must be written with a lower case. We will also need the privileges to run trace on the session, and obviously, the permissions to export or import.

Once we figure out all of these, we can use it to run the traced execution:

/app01/oracle 18:19> expdp zelkayam2 directory=HISTORY_DIR dumpfile=zelkayam2%u.dmp full=y parallel=2 trace=480300

There are 2 kinds of trace files that will be created in our background dump dest (diag/rdbms/trace):

1. One trace file for the master processes – file names SID>_dm_.trc>
2. One or multiple trace files for the worker processes – file names SID>_dw_.trc>

How do we set what to trace?

In order to know what and how to trace, we can use this helpful table. If we want to add more than one trace levels, we need to add the trace levels together (make sure you use hex calculator if you do – since 40300 + 80300 in hex is not the same for dec):

-- Summary of Data Pump trace levels: -- ==================================     Trace   DM   DW  ORA  Lines   level  trc  trc  trc     in   (hex) file file file  trace                                         Purpose ------- ---- ---- ---- ------ -----------------------------------------------   10300    x    x    x  SHDW: To trace the Shadow process (API) (expdp/impdp)   20300    x    x    x  KUPV: To trace Fixed table   40300    x    x    x  'div' To trace Process services   80300    x            KUPM: To trace Master Control Process (MCP)      (DM)  100300    x    x       KUPF: To trace File Manager  200300    x    x    x  KUPC: To trace Queue services  400300         x       KUPW: To trace Worker process(es)                (DW)  800300         x       KUPD: To trace Data Package 1000300         x       META: To trace Metadata Package --- + 1FF0300    x    x    x  'all' To trace all components          (full tracing)

A common trace level is 480300 which monitors both the master and the workers.
I hope to write more about this (if I’ll see a demand for that) – in the meantime, you can read the MOS note that explains how to read the trace here: note 286496.1.