adautocfg.sh and adcfglonce.pl dbconfig – fails with ORA-01157 Lock Datafile

When executed Auto config  and Clone Config after RMAN Duplicate, following error occured.

ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file
ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file
ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.

 

[orajzuat@usuatsrvdbd1bin]$ export PATH=/usr/bin:$PATH
[orajzuat@usuatsrvdbd1bin]$ perl adcfgclone.pl dbconfig /us9001/oracle/11.2.0/appsutil/bpijzuat_usuatsrvdbd1.xml

Beginning dbconfig Apply – Fri Jan 29 14:53:12 2016
…….
  |     30% completed       ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file
ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file
ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
        at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java:249)
        at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchronizer.java:328)
        at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(FileSysDBCtxMerge.java:721)
        at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:226)
        at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1690)
        at oracle.apps.ad.clone.ApplyDatabase.runCVM(ApplyDatabase.java:3058)
        at oracle.apps.ad.clone.ApplyDatabase.runCVMAndAutoConfig(ApplyDatabase.java:3003)
        at oracle.apps.ad.clone.ApplyDatabase.doConf(ApplyDatabase.java:649)
        at oracle.apps.ad.clone.ApplyDatabase.doApply(ApplyDatabase.java:473)
        at oracle.apps.ad.clone.ApplyDatabase.<init>(ApplyDatabase.java:366)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:501)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
        at java.lang.Thread.run(Thread.java:637)
  –     30% completed
Completed Apply…
Fri Jan 29 14:54:23 2016
Starting database listener for bpijzuat:
Running:
/us9001/oracle/11.2.0/appsutil/scripts/bpijzuat_usuatsrvdbd1/addlnctl.sh start bpijzuat
Logfile: /us9001/oracle/11.2.0/appsutil/log/bpijzuat_usuatsrvdbd1/addlnctl.txt
You are running addlnctl.sh version 120.1.12010000.4
Starting listener process bpijzuat …
Listener bpijzuat has already been started.
addlnctl.sh: exiting with status 0
addlnctl.sh: check the logfile /us9001/oracle/11.2.0/appsutil/log/bpijzuat_usuatsrvdbd1/addlnctl.txt for more information …
[orajzuat@usuatsrvdbd1bin]$

Cause: This error occurs when there is missing or corrupted TEMP tablespace tempfile

Solution:  Drop and Re-create Tempfile.

Current Temp Tablespace:

select file#,name,ts# from v$tempfile;

FILE# NAME  TS#
1     +USUAT_TMP/usuat/tempfile/temp3.257.902419859   292
2     +USUAT_TMP/usuat/tempfile/temp4.256.902419677   393

select name from v$tablespace where ts# in (292,393)

NAME
Temp1
Temp2

We can see the same error when trying to select the size of the tempfile

select file_name,bytes/1024/1024/1024 from dba_temp_files where tablespace_name=’TEMP1′;

select file_name,bytes/1024/1024/1024 from dba_temp_files where tablespace_name=’TEMP2′;

–ORA-01157: cannot identify/lock data file 513 – see DBWR trace file
–ORA-01110: data file 513: ‘+USUAT_DATA’

 Note: Same error as the adconfig

Create temporary tablespace TEMP3 tempfile ‘+USUAT_TMP’ size 20G;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;

drop tablespace TEMP1;

drop tablespace TEMP2;

Now run adautocfg.sh….SUCCESS…. 

 

 

RMAN-11003 and ORA-32010 during cloning

During a recent clone using RMAN Duplicate, RMAN failed with RMAN-11093 and ERROR: ORA-32010: cannot find entry to delete in SPFILE .

–Tail from RMAN Duplicate logfile with the Errors1

Cause:

Looking at MOS Doc ORA-32010: cannot find entry to delete in SPFILE (Doc ID 1384262.1), identifies the cause to “Due to some comments in the pfile, and/or invalid Entries for example missing a single quote to enclose values for parameters as set in pfile/spfile.

I did make a change to PFILE to convert the files from ASM to EXT on the source node. (As below).                *.db_file_name_convert=’+EBSPROD_ASM/’,’/u10/orc/data/’,’+EBSPROD_ASM/ebsprd/datafile/’,/u10/orc/data/’

Looking at it again, clearly shows the extra tick.

        SQL>  show parameter db_file_name_convert
                    NAME                                 TYPE            VALUE
                   ——————————
                   db_file_name_convert   string     +EBSPRD_ASM/, /u20/oracle/data/,                                                        +EBSPRD_ASM/ebsprd/datafile/, /u20/oracle/data/’

          SQL>

Solution: Manually complete the RMAN Duplicate from this point.

1) BACKUP CONTROL FILE FROM PROD

SQL> alter database backup control file to trace as ‘/HOME/ORACLE/RG_cntrfile.sql’;

2) EDIT THE CONTROL FILE (“SET DATABASE”, change the data files with path of target( you can obtain path from the failed RMAN log file when it laid out the files or manually modifying it, change the DB name). Save the file on clone database node (/home/affebsdev/rg_cntrfile.sql)

–SEE BELOW – contents of rg_cntrfile.sql

 

CREATE CONTROLFILE REUSE SET DATABASE “AFFEBSDEV” RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607
LOGFILE
GROUP 1 (‘/u10/oracle/data/log01a.dbf’,’/u10/oracle/data/log01b.dbf’) size 1048576512,
GROUP 2 (‘/u10/oracle/data/log02a.dbf’,’/u10/oracle/data/log02b.dbf’) size 1048576512
DATAFILE
‘/u10/oracle/data/system01.dbf’,
‘/u10/oracle/data/system02.dbf’,
‘/u10/oracle/data/system03.dbf’,
‘/u10/oracle/data/system04.dbf’,
‘/u10/oracle/data/system05.dbf’,
‘/u10/oracle/data/ctxd01.dbf’,
‘/u10/oracle/data/owad01.dbf’,
‘/u10/oracle/data/a_queue02.dbf’,
‘/u10/oracle/data/odm.dbf’,
‘/u10/oracle/data/olap.dbf’,
‘/u10/oracle/data/sysaux01.dbf’,
….
….

CHARACTER SET UTF8
;

3) SHUTDOWN THE CLONE DATABASE INSTANCE

 SQL>Shutdown Immediate;

4) FIX THE PFILE WITH CORRECT VALUE, then create SPFILE from PFILE.

Vi initaffebdev.ora
Fix the value and save the init file.

5) CREATE SPFILE FROM PFILE

  SQL>sqlplus / as sysdba
            SQL>create spfile from pfile=’ initaffebdev.ora’;

6) STARTUP NOMOUNT;
7) Execute the Controlfile on the Target Node as SYSDBA

SQL> sqlplus / as sysdba @rg_cntrfile.sql

       Control file created.

8) Mount the database

SQL> alter database Mount;
ORA-01100: database already mounted

9) Database can now be opened with reset logs.

SQL> alter database open resetlogs;
Database altered.

11) Re-create temp table space as below

ALTER TABLESPACE TEMP1 ADD TEMPFILE ‘/u10/oracle/data/temp01.dbf’ SIZE 6120M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP1 ADD TEMPFILE ‘/u10/oracle/data/temp01a.dbf’ SIZE 6120M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP2 ADD TEMPFILE ‘/u10/oracle/data/temp02.dbf’ SIZE 6120M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP2 ADD TEMPFILE ‘/u10/oracle/data/temp02a.dbf’ SIZE 6120M REUSE AUTOEXTEND OFF;

12) Verify the status

sqlplus ” / as sysdba”
SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

Now continue with cloning/post clone steps J

 

Deconfigure/Reconfigure GI/ASM/OHAS after Host Name/IP Change.

GI, ASM, Listener and Database will be down after changing Host Name and/or IP address. Will get an error when starting ASM or trying to query the Disk Group. At this point, we have to deconfigure the GI on the new machine with the different host name.

Below are the mandatory steps that need to be executed after the hostname and/or IP address was updated/changed/modified.

Reference: How to change Host name / IP for a Grid Infrastructure Oracle Restart Standalone Configuration (SIHA) (Doc ID 1552810.1)

Note: CRS processes in the RAC installation is not installed for the single instance installations.For the single instance installations, there is the HAS processes stands for “High Availability Services” and covers the cssd and diskmon processes.

For the example below, it will be for Single Instance.

1) Configure the CSS & OHAS services as root user as follows:

Source the ENV File.

[oracle@usdbvmsvr115~]$ . grid.env

Deconfigure the GI on the new machine as Root or Sudo

[oracle@usdbvmsvr115~]$ sudo /grid/11.2.0/grid/crs/install/roothas.pl -deconfig –force

Using configuration parameter file: /grid/11.2.0/grid/crs/install/crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CLSU-00100: Operating System function: opendir failed with error data: 2
CLSU-00101: Operating System error message: No such file or directory
CLSU-00103: error location: scrsearch1
CLSU-00104: additional error information: cant open scr home dir scls_scr_getval
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Successfully deconfigured Oracle Restart stack

To configure the GI new you have to do the following steps as Root or Sudo

[oracle@usdbvmsvr115~]$ sudo /grid/11.2.0/grid/crs/install/roothas.pl

Using configuration parameter file: /grid/11.2.0/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node usdbvmsvr115successfully pinned.
Adding Clusterware entries to upstart
usdbvmsvr115    2015/11/19 10:38:54     /grid/11.2.0/grid/cdata/usdbvmsvr115/backup_20151119_103854.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

2) Please perform the next steps as oracle or grid OS user (as the Grid Infrastructure OS owner):

[oracle@usdbvmsvr115~]$ $ORACLE_HOME/bin/crsctl modify resource “ora.cssd” -attr “AUTO_START=1”

3) Restart the OHAS stack as grid or oracle OS user (as the Grid Infrastructure OS owner):

[oracle@usdbvmsvr115~]$ /grid/11.2.0/grid/bin/crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘usdbvmsvr115’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘usdbvmsvr115’
CRS-2677: Stop of ‘ora.evmd’ on ‘usdbvmsvr115’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘usdbvmsvr115’ has completed
CRS-4133: Oracle High Availability Services has been stopped.

[oracle@usdbvmsvr115~]$ /grid/11.2.0/grid/bin/crsctl start has

CRS-4123: Oracle High Availability Services has been started.

4) Check the CSS & OHAS state as grid or oracle OS user (as the Grid Infrastructure OS owner):

[oracle@usdbvmsvr115~]$ /grid/11.2.0/grid/bin/crsctl check has

CRS-4638: Oracle High Availability Services is online
[oracle@usdbvmsvr115~]$ /grid/11.2.0/grid/bin/crsctl check css
CRS-4529: Cluster Synchronization Services is online
[oracle@usdbvmsvr115~]$ $ORACLE_HOME/bin/crsctl stat res -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.ons
OFFLINE OFFLINE      usdbvmsvr115
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1        ONLINE  ONLINE       usdbvmsvr115
ora.diskmon
1        OFFLINE OFFLINE
ora.evmd
1        ONLINE  ONLINE       usdbvmsvr115

[oracle@usdbvmsvr115~]$ $ORACLE_HOME/bin/crsctl stat resource

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on usdbvmsvr115
NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.evmd
TYPE=ora.evm.type
TARGET=ONLINE
STATE=ONLINE on usdbvmsvr115
NAME=ora.ons
TYPE=ora.ons.type
TARGET=OFFLINE
STATE=OFFLINE

[oracle@usdbvmsvr115~]$ $ORACLE_HOME/bin/crsctl stat res -t

——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.ons
OFFLINE OFFLINE      usdbvmsvr115
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1        ONLINE  ONLINE       usdbvmsvr115
ora.diskmon
1        OFFLINE OFFLINE
ora.evmd
1        ONLINE  ONLINE       usdbvmsvr115

Note: If the CSS & OHAS service did NOT start, then you will need to reboot the Linux/unix box and check them again.

5) Recreate the default listener (LISTENER) using port 1521 (or using your desired port), thru the NETCA GUI located on the new Grid Infrastructure Oracle Home (or manually if you do not have graphical access) as grid or oracle OS user (as the Grid Infrastructure OS owner):

[oracle@usdbvmsvr115~]$ srvctl add listener
[oracle@usdbvmsvr115~]$ srvctl start listener

6) Please create the init+ASM.ora file on the <11.2 Grid Infrastructure Oracle Home>/dbs directory with the next parameters:

Already had the init file…no changes were made. This step can be skipped for ASM to choose the default init file.

[oracle@usdbvmsvr115dbs]$ more init+ASM.ora
+ASM.__oracle_base=’/grid/11.2.0’#ORACLE_BASE set from environment
+ASM.asm_diskgroups=’EBSPRDDG_ASM’
*.asm_diskgroups=’EBSPRDDG_ASM’
*.asm_power_limit=4
*.diagnostic_dest=’/grid/11.2.0/grid’
*.instance_type=’asm’
*.large_pool_size=400M
#*.local_listener=’LISTENER’
*.processes=400
*.remote_login_passwordfile=’SHARED’
*.shared_pool_size=450M

7) Add the ASM instance as grid or oracle OS user (as the Grid Infrastructure OS owner):

[oracle@usdbvmsvr115dbs]$ /grid/11.2.0/grid/bin/srvctl add asm

8) Enable ASM instance Auto Start as grid or oracle OS user (as the Grid Infrastructure OS owner) as follow:

[oracle@usdbvmsvr115dbs]$ /grid/11.2.0/grid/bin/crsctl modify resource “ora.asm” -attr “AUTO_START=1”

9) Make sure the disks are discovered by kfod as grid or oracle OS user (as the Grid Infrastructure OS owner) as follow:

$> <11.2 Grid Infrastructure Oracle Home>/bin/kfod asm_diskstring='<full path ASM disks location>/*’ disks=all

OR

[oracle@usdbvmsvr115dbs]$ /grid/11.2.0/grid/bin/kfod asm_diskstring=’ORCL:*’ disks=all

——————————————————————————–
Disk          Size Path                                     User     Group
================================================================================
1:     524308 Mb ORCL:EBSPRDDATA01                        <unknown> <unknown>
2:     524308 Mb ORCL:EBSPRDDATA02                        <unknown> unknown>
3:     524308 Mb ORCL:EBSPRDDATA03                        <unknown> unknown>
4:     524308 Mb ORCL:EBSPRDDATA04                        <unknown> unknown>
5:     524308 Mb ORCL:EBSPRDDATA05                        <unknown> unknown>
KFOD-00313: No ASM instances available. CSS group services were successfully initilized by kgxgncin

10) If so, then startup the ASM instance as grid or oracle OS user (as the Grid Infrastructure OS owner) as follow:

[oracle@usdbvmsvr115dbs]$ export ORACLE_SID=+ASM
[oracle@usdbvmsvr115dbs]$ sqlplus ” /as sysasm”

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 19 10:00:13 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile=init+ASM.ora
ASM instance started
Total System Global Area 1002127360 bytes
Fixed Size                  2234560 bytes
Variable Size             974726976 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL>

SQL> show parameter asm

NAME                                 TYPE        VALUE
———————————— ———– ——————————
asm_diskgroups                       string      EBSPRDDG_ASM
asm_diskstring                       string
asm_power_limit                      integer     4
asm_preferred_read_failure_groups    string
SQL>

11) Validate that the candidate disks are being discovered:

SQL> select  path  from v$asm_disk;

PATH
——————————————————————————–
ORCL:EBSPRDDATA01
ORCL:EBSPRDDATA02
ORCL:EBSPRDDATA03
ORCL:EBSPRDDATA04
ORCL:EBSPRDDATA05

12) Create a new ASM instance spfile:

SQL> create spfile from pfile;
File created.

13) Add the new ASM spfile and listener to the new ASM instance resource:

[oracle@usdbvmsvr115dbs]$ /grid/11.2.0/grid/bin/srvctl modify asm -l LISTENER

14) Validate the OHAS

[oracle@usdbvmsvr115dbs]$ crs_stat -t

Name           Type           Target    State     Host
————————————————————
ora…._ASM.dg ora….up.type ONLINE    ONLINE    usdbvmsvr115
ora….ER.lsnr ora….er.type ONLINE    ONLINE    usdbvmsvr115
ora.asm        ora.asm.type   ONLINE    ONLINE    usdbvmsvr115
ora.cssd       ora.cssd.type  ONLINE    ONLINE    usdbvmsvr115
ora.diskmon    ora….on.type OFFLINE   OFFLINE
ora.evmd       ora.evm.type   ONLINE    ONLINE    usdbvmsvr115
ora.ons        ora.ons.type   OFFLINE   OFFLINE

[oracle@usdbvmsvr115dbs]$ asmcmd lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   2621540   808716                0          808716              0             N  EBSPRDDG_ASM/

Now the ASM is up and Disk Group is mounted. Proceed to start up of the Database,

 

Enable Trace for Concurrent Programs in Oracle Apps R12

Tracing the Concurrent program is very good option to Debug any issue while running the concurrent program.

Goal :
To enable the Trace for Concurrent Programs
To Debug the issues in Concurrent Programs or its dependent objects.

Solution :
1. Goto Concurrent Program Definition

Application Developer –> Concurrent –> Program –> check Enable Trace

Query your concurrent program and check the Enable Trace check box at the bottom of the screen.

2. Set the Profile Concurrent: Allow Debugging to YES.

3. Navigate to the Responsibility from where you are running the program.

4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options click on that button.

5. In the Debug Options select the SQL Trace and enable Trace with binds.

6. Submit the Concurrent Program and note down the Request id.

7.Trace file name will always be post fixed with oracle_process_id.To get the oracle_process_id use the below SQL
Select oracle_process_id from fnd_concurrent_requests where request_id= &Request_id;

8.And the Trace file path can be derived using the below SQL
Select * from v$parameter where name=’user_dump_dest’;

9. Get the trace file to your local machine.Understanding the raw trace is very complex so use TKPROF utility to make the Trace file readable.

10. Open the Command Prompt and run the below command
TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

11.A new outfile will be generated with the name given in the above command.Analyse the Output file to know the answers for your problem.

Sql queries to find the log files by Request id
select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr
where  req. concurrent_program_id = prog.concurrent_program_id
and req.requested_by = usr.user_id
–and request_id like ‘6013239’
and dest.name= ‘user_dump_dest’
and request_id like & Request_id;
Sql query find the log files by username
select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date,  req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest 
from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr
where  req. concurrent_program_id = prog.concurrent_program_id
and req.requested_by = usr.user_id
–and request_id like ‘6013239’
and dest.name= ‘user_dump_dest’
and usr.user_name like ‘Ramesh%G%’
order by request_date desc
Select oracle_process_id from fnd_concurrent_requests where request_id = &Request_id;
 
select * from apps. fnd_concurrent_requests req,gv$parameter dest , apps .fnd_concurrent_programs_vl prog where  req. concurrent_program_id = prog.concurrent_program_id and request_id like ‘601300%’
and dest.name= ‘user_dump_dest’

Determine the OS version of a Linux Release/Kernel and Oracle Apps/DB

OS Commands:

[oracle@uswjusvr115 scripts]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)

[oracle@uswjusvr115 scripts]$ cat /etc/oracle-release
Oracle Linux Server release 6.6

[oracle@uswjusvr115 scripts]$ cat /proc/version
Linux version 2.6.39-400.17.1.el6uek.x86_64 (mockbuild@ca-build44.us.oracle.com) (gcc version 4.4.7 20120313              (Red Hat 4.4.7-3) (GCC) ) #1 SMP Fri Feb 22 18:16:18 PST 2013

*NOTE: uek = Unbreakable Enterprise Kernel 

[oracle@uswjusvr115 scripts]$ getconf LONG_BIT
             64

[oracle@uswjusvr115 scripts]$ uname -a
            Linux uswjusvr115.affiniagroup.net 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64               x86_64 x86_64 GNU/Linux

[oracle@uswjusvr115 scripts]$ uname -mrs
            Linux 2.6.39-400.17.1.el6uek.x86_64 x86_64

[oracle@uswjusvr115 scripts]$ cat /etc/issue
            Oracle Linux Server release 6.6
Kernel \r on an \m

SQL Command:

[oracle@uswjusvr115 scripts]$ sqlplus
           SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 27 10:15:01 2015
SQL> select banner from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

SQL> SELECT INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION FROM  gv$instance;
                       INSTANCE_NUMBER             INSTANCE_NAME HOST_NAME         VERSION
1              ebsprd   uswodsvr115.affiniagroup.net          11.2.0.3.0

SQL> conn apps/apps
                      Connected.
SQL> select release_name from apps.fnd_product_groups;
RELEASE_NAME
————————————————–
12.1.3

Enable Forms Diagnostics, Tracing in R12 using Profile Option

This document shows how to use Forms Runtime Diagnostics using Profile Option at user level.

  1. First verify if Forms using Socket or Servlet.
  • Login on to Application Server, and check the cfg file to verify the “serverURL=”And “connectMode=” settings.
  • In Servlet Mode:
    serverURL=/forms/lservlet
    connectionMode=servlet
  • In Socket Mode:
    serverURL=<blank>
    connectionMode=Socket

1.1

  1. Enable Forms Runtime Diagnostics (FRD)

FRD can be enabled in one of the following methods:

  • Option 1: Obtain FRD Trace Using Profile Options
  • Option 2: Obtain FRD Trace Using Appsweb.cfg
  • Option 3: Obtain FRD in an ADHOC way

We will be using method “Obtain FRD Trace Using Profile Options” in this example.

To start tracing, please modify Profile Option ‘ICX: Forms Launcher’ at USER level to include the Forms parameters required for FRD logging (Note: This should not set at SITE LEVEL)

2.1: Add the trace value “ ‘?record=collect’  “ to Apps URL for profile option ‘ICX: Forms Launcher’

In Servlet Mode:https://hostname.domain:port/forms/frmservlet?record=collect
in Socket Mode:https://hostname.domain:port/OA_HTML/frmservlet?record=collect

1.2

Copy the site level value for profile option ‘ICX: Forms Launcher’ and append values’?record=collect’. Past it at the user level.

Ex. Site Value: http://bpiebsprds.brakepartsinc.com:80/forms/frmservlet
Modified Value: http://bpiebsprds.brakepartsinc.com:80/forms/frmservlet?record=collect

Copy the modified value to the User level and Save.

1.5

  1. Login into Oracle Applications and launch forms via self-service. Navigate through the steps to capture trace.

Should get below prompt when you login to Form.

1.3

  1. Obtain the Trace File. It’s in $FORMS_TRACE_DIR, where<pid> is the process identifier.

Ex: $FORMS_TRACE_DIR/collect_6540

<Sample Output file>

1.4

Source:R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications (Doc ID 438652.1)