Total Pageviews

74564

Tuesday, 30 August 2016

Wrong fonts in PDF reports in R12

Edit the Environment file
$ vi $SID_Hostname.env
Add the following lines:
IX_PRINTING=$FND_TOP/resource/ixlib.cfg
export IX_PRINTING

IX_RENDERING=$FND_TOP/resource/pasta.cfg
export IX_RENDERING

Edit pasta.cfg file
$ cd $FND_TOP/resource
$ vi pasta.cfg
Add the following lines:
Font.Default.Plain=$FND_TOP/resource/ADUO.ttf
Font.Default.Bold=$FND_TOP)/resource/ADUOB.ttf
Edit ixlib.cfg file
$ vi ixlib.cfg
Add the following lines:
Font.Default.Plain=$FND_TOP/resource/ALBANYWT.ttf
Font.Default.Bold=(Full Path of $FND_TOP)/resource/ALBANYWT.ttf
Re-login as the appstier OS user(equivalent applmgr) and bounce the Concurrent manager

Sunday, 31 July 2016

FNLOAD OPTION

Concurrent Program

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XXCUST_CP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Lookups

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXCUST_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XXCUST_LKP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct  XXCUST_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XXCUST_MESG%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Request Set and Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_RS.ldt REQ_SET REQUEST_SET_NAME='XXCUST_RS'

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'


FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Form Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt FUNCTION FUNC_APP_SHORT_NAME='XXCUST' FUNCTION_NAME='XXCUST_FUNC'

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt

Profile

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_PROF.ldt PROFILE PROFILE_NAME="XXCUST_PROFILE" APPLICATION_SHORT_NAME="XXCUST"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_FUNC.ldt

Menu

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt

Data Definition

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXCUST_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXCUST DATA_SOURCE_CODE=XXCUST_DS

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_DD.ldt

Tuesday, 26 July 2016

ld.so.1: rwconverter: fatal: libXm.so.4: open failed: No such file or directory

Applies to

Oracle® Applications Manager - Version 12.1.1 to 12.1.3 [Release 12.1]
Oracle Applications DBA - Version 12.1.1 to 12.1.3 [Release 12.1]
Oracle Solaris on SPARC (64-bit)

Symptoms

When attempting to "Generate Report Files" using adadmin utility or relinking 10.1.2 Oracle® Home using "adlnktools.sh",
the following error occurs.
ld.so.1: rwconverter: fatal: libXm.so.4: open failed: No such file or directory

Cause

motif package is not installed. This package provides the mising library "/usr/dt/lib/libXm.so" for Solaris 11 platform. The same can be checked by running below command.
Connect to root :
# pkg info -r motif
  Name: library/motif
  Summary: Motif toolkit
  Description: Motif 2.1 libraries, headers, xmbind and bindings
  Category: System/X11
  State: Not installed
  Publisher: solaris
  Version: 0.5.11
 Build Release: 5.11
  Branch: 0.175.0.0.0.0.0
Packaging Date: Tue Sep DD HH:MM:SS YYYY
  Size: 12.94 MB
  FMRI: pkg://solaris/library/motif@0.5.11,5.11-0.175.0.0.0.0.0:20110927T192002Z

Solution

1. Install motif package following below steps :
# pkg info -r motif
This will confirm whether motif is installed or not.
# pkg install motif
This will install the motif package.
2. Now the file "/usr/dt/lib/libXm.so" should present.

3. Relink the Oracle® Homes as per below steps :

Connect to appsTier : putty session
$ cd $IAS_ORACLE_HOME/appsutil/clone
$ sh adlnkweboh.sh

$ cd $ORACLE_HOME/appsutil/clone
$ sh adlnktools.sh
Both should be successful.

4. Run adadmin utility to "Generate report files" to retest the issue.

5. Migrate the solution as appropriate to other environments.

Can't load 'Sys/Hostname/Hostname.so' for module Sys::Hostname: wrong ELF class: ELFCLASS64

Can't load 'Sys/Hostname/Hostname.so' for module Sys::Hostname: wrong ELF class: ELFCLASS64
Version:

Oracle R12 version 12.1.3 in OEL 64 bit

Issue:

Error messages were found in the adformsctl.sh start.

adformsctl.sh: check the logfile /u01/E-BIZ/inst/apps/dmw24rep_slce20cn14/logs/appl/admin/log/adformsctl.txt for more information ...

.end std out.
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u01/E-BIZ/inst/apps/dmw24rep_slce20cn14/logs/appl/rgf/TXK/txkChkFormsDeployment_Mon_Feb_2_20_31_21_2015/txkChkFormsDeployment_Mon_Feb_2_20_31_21_2015.log



Error:

On checking the logfile, it is understood that the issue is with PERL Paths in the context file.

Can't load '/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/auto/Sys/Hostname/Hostname.so' for module Sys::Hostname: /usr/lib64/perl5/5.8.8/x86_64-linux-thread
-multi/auto/Sys/Hostname/Hostname.so: wrong ELF class: ELFCLASS64 at /usr/lib64/
perl5/5.8.8/x86_64-linux-thread-multi/XSLoader.pm line 70.
 at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Sys/Hostname.pm line 23
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u01/E-BIZ/inst/apps//r12env_host2/logs/appl
/rgf/TXK/txkChkFormsDeployment_Mon_Feb_2_19_58_53_2015/txkChkFormsDeployment_Mon
_Feb_2_19_58_53_2015.log

Reason:

The environment is created as shared storage multi node. The second node was cloned from the first, but in the context file, the PERL related paths were not identical to that in first node

The paths in failed node

<ADPERLPRG oa_var="s_adperlprg" osd="unix">/usr/bin/perl</ADPERLPRG>

<PERL5LIB oa_var="s_perl5lib" osd="LINUX_X86-64">/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi:/usr/lib/perl5/site_perl/5.8.8:/usr/lib/perl5/site_perl:/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi:/usr/lib/perl5/vendor_perl/5.8.8:/usr/lib/perl5/vendor_perl:/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi:/usr/lib/perl5/5.8.8:.:/u01/E-BIZ/apps/apps_st/appl/au/12.0.0/perl:/u01/E-BIZ/apps/apps_st/appl/au/12.0.0/perl</PERL5LIB>

Fix:

Edited the perl paths (ADPERLPRG  and PERL5LIB) in the context file and made it identical to that in first node and restarted the services.

Paths after editing:

<ADPERLPRG oa_var="s_adperlprg" osd="unix">/u01/E-BIZ/apps/tech_st/10.1.3/perl/bin/perl</ADPERLPRG>

<PERL5LIB oa_var="s_perl5lib" osd="LINUX_X86-64">/u01/E-BIZ/apps/tech_st/10.1.3/perl/lib/5.8.3:/u01/E-BIZ/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3:/u01/E-BIZ/apps/apps_st/appl/au/12.0.0/perl:/u01/E-BIZ/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi</PERL5LIB>

Restart service in second node after this.

This can be implemented on solaris as well.

Ref:
http://satishvchandran.blogspot.com/2015/02/cant-load-usrlib64perl5588x8664-linux.html

Sunday, 15 May 2016

Script – Check RMAN Backup Status

Script – Check RMAN Backup Status
===============================
Scripts to check backup status and timings of database backups –

This script will be run in the database, not the catalog database.

Login as sysdba –

This script will report on all backups – full, incremental and archivelog backups –
=============================================================

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


This script will report all on full and incremental backups, not archivelog backups –
==============================================================
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

Friday, 6 May 2016

Compile Jsp’s in R12 & 11i

Compile Jsp’s in R12 & 11i
=====================

$cd $ADMIN_SCRIPTS_HOME

$adopmnctl.sh stopall — to shutdown opmn managed services viz., opmn, forms, oacore & oc4j

 $ cd $COMMON_TOP/_pages

$ mv _pages _pages_old

$mkdir _pages

 $ cd $FND_TOP/patch/115/bin or export PATH=$FND_TOP/patch/115/bin:$PATH

Compile all jsps.
$perl ojspCompile.pl –compile –flush -p 2 (or) ojspCompile.pl --compile --fast --quiet
(-p represents parallel threads, if you have enough horse power on your server, you can increase it to 8 or 10)

$cd $ADMIN_SCRIPTS_HOME

Another Option
==============
STEP1.

Login as applmgr user and source the environment by running the script:


/oracle/apps/apps_st/appl/APPS[SID]_[host].env

STEP2:  Run the commands

In Linux/Unix

cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 2

In Windows

cd %FND_TOP%\patch\115\bin
perl -x ojspCompile.pl –compile –flush

STEP3:

Restart the Apache services or Middleware services.
How to compile individual JSP manually in R12:

STEP1:

cd $COMMON_TOP/_pages

STEP2:

Delete the class files (abcdef.class) for the JSP that you want to compile manually.

Eg. JSP File abcdef_12.jsp
Class File: abcdef_12.class ------------- Delete this file.

STEP3:

Navigate to the OA_HTML (Location for all the Jsp pages). if the JSP pages do not exist, copy the JSP to the location & run the below command.

$FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'abcdef_12.jsp' --flush

STEP4:

Restart the Apache services or Middle-ware services.

STEP5:

Check whether the respective class files have been created under

cd $COMMON_TOP/_pages

To Compile JSP’s at runtime in Oracle Apps R12
===============================================
To compile the jsp as soon as you access jsp page :

1. Change s_jsp_main_mode in R12 context file (XML file)

  <jsp_debug_parameters oa_var=”s_jsp_main_mode”>justrun</jsp_debug_parameters>

change it to

  <jsp_debug_parameters oa_var=”s_jsp_main_mode”>recompile</jsp_debug_parameters>

2.Run Autoconfig (adautocfg.sh)

Autoconfig will update file
$INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml

        <param-name>main_mode</param-name>
         <param-value>justrun</param-value>

.

How to compile JSP manually

perl ojspCompile.pl –compile –flush  (Unix)
or
perl ojspCompile.pl –compile –flush -p 2 (Unix where -p means parallel execution)

perl -x ojspCompile.pl –compile –flush (Windows)

ojspCompile.pl is under $FND_TOP/patch/115/bin/

Note* JSP’s are compiled automatically after patching (whenever patch updates one or more jsp it calls ojspCompile.pl to compile JSP)

.

JSP Compilation logs

In 11i, all jsp compilation errors are logged in $IAS_ORACLE_HOME/ Apache/ Jserv/ logs/ mod_jserv.log

In R12, all jsp compilation errors are logged in $LOG_HOME/ ora/ 10.1.3/ j2ee/ oacore/ oacore_default_group_1/ application.log

458338.1  How to Enable Automatic Compilation of JSP pages in R12 Environment

433386.1  JSP Pages Hanging in R12 After Removing Cached Class Files in _pages

783094.1  Compile jsp files at Application R12 at Windows

Node ID does Not Exist for the Current Application Server ID Errors

Node ID  does not exist for the current application server id.
 The URL on which the above error occurs is as follows:

http://myhost.domain:port/dev60cgi/f60cgi

This error also occurs at the time of starting / stopping Apache.

adapcctl.sh start

adapcctl.sh stop

$INST_TOP/admin/install/adgendbc.sh

SOLUTION

1. Do a select on the server_id in the fnd_nodes table.  Here is an example of what it could look like:
server_id=EA99227ED75CFE4EE030149077C4515496138833635529486962342698410274
2. Do a search on APPL_SERVER_ID in the .dbc file.  It could show something like:
APPL_SERVER_ID=EA99220924EF823FE030149077C450C096138833635529486962342698410274
Please manually comment out the APPL_SERVER_ID in the .dbc file.
3. Then re-run adgendbc.sh and it will  seed the node and get a good  application id.
NOTE: If the server_id is missing from the FND_NODES table, ie. if the above SQL does not return anything
To get the FND_NODES table populated with the correct server ID, one may have to use:
Note 260887.1 ->Steps to Clean Nonexistent Nodes or IP Addresses from   FND_NODES

Please refer the Note 274476.1 and Note 363609.1 to have a detailed view to change the IP Address of the Database Server.
5) As the change is made at the OS (Operating System) level.Please ensure that the IP address gets reflected in the DNS server and also in the local host file.

REFERENCES



Correct Method of Changing the IP Address in 11i & R12 (Doc ID 751328.1)
pool fnd_nodes 
set pagesize 50 
col node_name format a15 
col server_id format a8 
col server_address format a15 
col platform_code format a4 
col webhost format a12 
col domain format a20 
col virtual_ip format a12 
set linesize 132 
select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F, 
support_web W, node_name, server_id, server_address, domain, webhost, virtual_ip 
from fnd_nodes 
order by node_id;

Thursday, 28 April 2016

Script to Move Datafiles

Move datafile script (unix or windows)




ARCHIVE LOGGING (UNIX):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select file_id, file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database datafile '||file_id||' offline;'
from dba_data_files
where file_name like '%disk8%'
/
select '!mv '||file_name||' '||file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database rename file "'||file_name||"' to "'||file_name||"';'
from dba_data_files
where file_name like '%disk8%'
/
select 'recover datafile '||file_id||';'
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database datafile '||file_id||' online;'
from dba_data_files
where file_name like '%disk8%'
/
select 'select status from dba_data_files;' from dual;

NO ARCHIVE LOGGING (UNIX):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select 'shutdown immediate' from dual;
select file_id, file_name
from dba_data_files
where file_name like '%disk8%'
/
select '!mv '||file_name||' '||file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'startup mount' from dual;
select 'alter database rename file "'||file_name||"' to "'||file_name||"';'
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database open;' from dual;
select 'select status from dba_data_files;' from dual;

ARCHIVE LOGGING (WINDOWS):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select file_id, file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database datafile '||file_id||' offline;'
from dba_data_files
where file_name like '%disk8%'
/
select 'host move '||file_name||' '||file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database rename file "'||file_name||"' to "'||file_name||"';'
from dba_data_files
where file_name like '%disk8%'
/
select 'recover datafile '||file_id||';'
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database datafile '||file_id||' online;'
from dba_data_files
where file_name like '%disk8%'
/
select 'select status from dba_data_files;' from dual;

NO ARCHIVE LOGGING (WINDOWS):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select 'shutdown immediate' from dual;
select file_id, file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'host move '||file_name||' '||file_name
from dba_data_files
where file_name like '%disk8%'
/
select 'startup mount' from dual;
select 'alter database rename file "'||file_name||"' to "'||file_name||"';'
from dba_data_files
where file_name like '%disk8%'
/
select 'alter database open;' from dual;
select 'select status from dba_data_files;' from dual;


Another Script
============

select 'alter database rename file '''|| file_name ||''' to '''/u0/' || 
substr(file_name, 5) ||''';' from dba_data_files;

to:

select 'alter database rename file '''|| file_name ||''' to '||'''/u0/' || 
substr(file_name, 5) ||''';' from dba_data_files;


Another
=======
SQL>spool rename_datafile.sql
set line 140
set pagesize 2000
set heading off
select ‘alter database rename file ‘ ||””|| file_name || ”” || ‘ to ‘|| ””||
‘/oracle/IMPRD/data’ || substr(file_name, instr(file_name, ‘/’, -1)) ||”” || ‘;’
from dba_data_files
/
SQL>spool off
Spool to a file and then:
1- Shutdown immediate;
2- OS copy the files to the new location
3- Startup Mount;
4- Execute the spooled file ( SQL>@rename_datafile.sql )
5- Alter database open;

Another
=======
select 'alter database rename file '||chr(39)||name||chr(39)||' to
'||chr(39)||'F'||substr(name,2)||chr(39)||';'
from v$datafile;


Wednesday, 27 April 2016

APPS/SYSADMIN password in Oracle Apps R12

Step to Find APPS PASSWORD :

Step #1


sqlplus> sqlplus system/system_password
sqlplus / as sysdba

Step#2:  Create Function to decrypt the encrypted password

SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
Function created.

Step#3: Query for password

set linesize 200 long 300

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG379C5C883FF56235BE975D075A6FD2E7A749279B7BFCE331530B443E86F41A1966E3E4B1864519DB88BC07D58666AD837B

Step#4:  Get the Apps password using encrypted guest password

SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG379C5C883FF56235BE975D075A6FD2E7A749279B7BFCE331530B443E86F41A1966E3E4B1864519DB88BC07D58666AD837B') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG379C5C883FF56235BE975D075A6FD2E7A749279B
--------------------------------------------------------------------------------
APPS123

Step#5: Test apps password

SQL> conn apps/APPS123;
Connected.

Step to Find SYSADMIN PASSWORD :

Step# 1:

SQL> conn apps/APPS123;
Connected.

Step#2:

SQL> CREATE OR REPLACE PACKAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
Package created.

Step# 3:

SQL>
 CREATE OR REPLACE PACKAGE BODY get_pwd  AS
 FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
 RETURN VARCHAR2
 AS  LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd;
 /
Package body created.

Step# 4:

SQL> SELECT usr.user_name,
       get_pwd.decrypt
  2    3            ((SELECT (SELECT get_pwd.decrypt
  4                                (fnd_web_sec.get_guest_username_pwd,
  5                                 usertable.encrypted_foundation_password
  6                                )
  7                        FROM DUAL) AS apps_password
  8                FROM fnd_user usertable
  9               WHERE usertable.user_name =
 10                        (SELECT SUBSTR
 11                                    (fnd_web_sec.get_guest_username_pwd,
 12                                     1,
 13                                       INSTR
 14                                            (fnd_web_sec.get_guest_username_p
 15                                             '/'
 16                                            )
 17                                     - 1
 18                                    )
 19                           FROM DUAL)),
 20             usr.encrypted_user_password) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';
Enter value for user_name: SYSADMIN
old  23:  WHERE usr.user_name = '&USER_NAME'
new  23:  WHERE usr.user_name = 'SYSADMIN'


USER_NAME                                     PASSWORD
------------------------                        -----------------
SYSADMIN                                        SYSADMIN123

Step#5 :  Bellow sql will help you to validate SYSADMIN Password

SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123) from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
Y


Ref:
http://dbafix.blogspot.com/2014/04/find-out-appssysadmin-password-in.html

Monday, 25 April 2016

FRM-92050 Failed to Connect to Server


In our case the below actions fixed the issue.


Please execute following actions:
a. Copy fndfmxit.so from a backup.
b. Place it in $FND_TOP/bin.
c. Instead bounce services 

adapcctl.sh: exiting with status 150



adapcctl.sh: exiting with status 150
On E-Business Suite Release 12 environment, while starting midtier services using adstrtal.sh, below error occurs:

Executing service control script:
/oracle/apps/VIS/inst/apps/<CONTEXT>/admin/scripts/adapcctl.sh start
Timeout specified in context file: 100 second(s)
Script returned:
****************************************************
You are running adapcctl.sh version 120.7.12010000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
adapcctl.sh: exiting with status 150
adapcctl.sh: check the logfile /oracle/apps/VIS/inst/apps/<CONTEXT>/logs/appl/admin/log/adapcctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Same error for adoacorectl.sh, adoafmctl.sh, adformsctl.sh.

CAUSE:
There is a states file called .opmndat at /oracle/apps/VIS/inst/apps/<CONTEXT>/ora/10.1.3/opmn/logs/states which gets created when services are started.
Whenever services are started this file gets created or updated.
Unfortunately for some reason this file is not updated when services are started.

SOLUTION:
1. Shutdown all Middle tier services and ensure no defunct processes exist running the following from the operating system:

# ps -ef | grep <applmgr>

If one finds any, kill these processes.
2. Navigate to $INST_TOP/ora/10.1.3/opmn/logs/states directory. It contains hidden file .opmndat:

# ls -lrt .opmndat
3. Delete this file .opmndat after making a backup of it:

# rm .opmndat
4. Restart the services.

5. Re-test the issue.
- See more at: http://oracledbajourney.blogspot.com/2013/01/adapcctlsh-exiting-with-status-150.html#sthash.jGeENkex.dpuf

Ref:
http://oracledbajourney.blogspot.com/2013/01/adapcctlsh-exiting-with-status-150.html

Sunday, 24 April 2016

Scripts To monitor Oracle Database And Application Server

Check Archive log size and deletion
===========================
For last 20 days.
===========
SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_GB,
         SUM_ARCH_DEL.DELETED_GB,
         SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      GENERATED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      DELETED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
       AND TO_DATE (SUM_ARCH.DAY, 'DD/MM/YYYY') >sysdate-20
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

another
======
SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_GB,
         SUM_ARCH_DEL.DELETED_GB,
         SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      GENERATED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      DELETED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)

ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

ASMspacereport 
=============
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup ORDER BY name
/
exit

ASM disk usage in detail
===================
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup ORDER BY name
/
exit


Check tablespace free space and used
============================
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,       decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
               from     (select tablespace_name, sum(bytes)/1024/1024 used_mb
                from    dba_data_files group by tablespace_name union all
                        select  tablespace_name || '  **TEMP**'
                                ,       sum(bytes)/1024/1024 used_mb
                                        from    dba_temp_files group by tablespace_name) tsu
                                        ,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
                                                from    dba_free_space group by tablespace_name) tsf
                                                where   tsu.tablespace_name = tsf.tablespace_name (+)
                                                order   by 4
                                                /

Tablespace usage
=============
set lines 300
 SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024/1024) "Tot size GB",
 round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
 round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
 100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
 round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
 FROM (
 SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
 MAX(BYTES) LARGEST,COUNT(*) CHUNKS
 FROM SYS.DBA_FREE_SPACE A
 GROUP BY TABLESPACE_NAME
 UNION
 SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
 FROM SYS.DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
 where A.TABLESPACE_NAME='&TBS'
 GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
 order by 5
/

Redolog Size
==========
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

Datafile size
==========
set linesize 1000
set pagesize 1000
select a.file_name, a.tablespace_name, a.bytes/1024/1024 "Size",
to_char(b.creation_time, 'DD-MON-YYYY HH24:MI:SS') " Creation Date"
from dba_data_files a, v$datafile b where a.tablespace_name='&tbsp'
and a.file_id = b.file#
order by file_name;

Pending Concurrent request
=====================
select COUNT (distinct cwr.request_id) Pending_Requests FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name
in ( select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl);

User Connected to Database
====================
select sid, serial#, username, osuser, program from gv$session  where username is not null AND PROGRAM NOT LIKE 'FTE_TXN_MANAGER%'
AND PROGRAM NOT LIKE 'STANDARD%'
AND PROGRAM NOT LIKE 'FNDSCH%'
AND PROGRAM NOT LIKE 'RCVOLTM%'
AND PROGRAM NOT LIKE 'JDBC%'
AND PROGRAM NOT LIKE 'PODAMGR%'
AND PROGRAM NOT LIKE 'INVMGR%'
AND PROGRAM NOT LIKE 'frmweb%'
AND PROGRAM NOT LIKE 'OAMCOLMGR%'
AND PROGRAM NOT LIKE 'INVTMRPM%'
AND PROGRAM NOT LIKE 'frmbld%'
AND PROGRAM NOT LIKE 'CRM%'
AND PROGRAM NOT LIKE 'ICM%'
AND PROGRAM NOT LIKE 'IEU_SH_CS%'
AND PROGRAM NOT LIKE 'FNDSM%'
AND PROGRAM NOT LIKE 'CRPINQMGR%'
AND PROGRAM NOT LIKE 'PASMGR%'
AND PROGRAM NOT LIKE 'IEU_WL_CS%'
AND PROGRAM NOT LIKE 'oraagent.bin%'
AND PROGRAM NOT LIKE 'FNDIM%';

Check Managers status from Front end.
=============================
set pagesize 9999;
select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');


Query to check lock
================
set serveroutput on size 10000
declare
  cursor c1 is
    select * from v$lock where request != 0
      order by id1, id2;
  wid1            number := -999999;
  wid2            number := -999999;
  wholder_detail  varchar2(200);
  v_err_msg          varchar2(80);
  wsid            number(5);
  wstep           number(2);
  wtype           varchar2(10);
  wobject_name    varchar2(180);
  wobject_name1   varchar2(80);
  wlock_type      varchar2(50);
  w_lastcallet  varchar2(11);
  h_lastcallet  varchar2(11);
begin
  for c1_rec in c1 loop
    if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
       null;
    else
       wstep  := 10;
       select sid , type into wsid , wtype
         from v$lock
         where id1  = c1_rec.id1
           and id2  = c1_rec.id2
           and request = 0
           and lmode != 4;
      dbms_output.put_line('  ');
       wstep  := 20;
      select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                ' SID:' || s.sid || ' Status: ' || s.status  ||
                ' (' ||         floor(last_call_et/3600)||':'||
                                floor(mod(last_call_et,3600)/60)||':'||
                                mod(mod(last_call_et,3600),60) ||
                ') Module:'|| module ||
              ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
          into wholder_detail
          from v$session s, v$process p
          where s.sid= wsid
            and s.paddr = p.addr;
      dbms_output.put_line(wholder_detail);

      begin
        select decode(wtype,'TX', 'Transaction',
                            'DL', 'DDL Lock',
                            'MR', 'Media Recovery',
                            'RT', 'Redo Thread',
                            'UN', 'User Name',
                            'TX', 'Transaction',
                            'TM', 'DML',
                            'UL', 'PL/SQL User Lock',
                            'DX', 'Distributed Xaction',
                            'CF', 'Control File',
                            'IS', 'Instance State',
                            'FS', 'File Set',
                            'IR', 'Instance Recovery',
                            'ST', 'Disk Space Transaction',
                            'TS', 'Temp Segment',
                            'IV', 'Library Cache Invalida-tion',
                            'LS', 'Log Start or Switch',
                            'RW', 'Row Wait',
                            'SQ', 'Sequence Number',
                            'TE', 'Extend Table',
                            'TT', 'Temp Table',
                            'Un-Known Type of Lock')
                into wlock_type
                from dual;
        declare
          cursor c3 is
            select object_id from v$locked_object
              where session_id = wsid;
        begin
          wobject_name := '';
          for c3_rec in c3 loop
            select object_type||': '||owner||'.'||object_name
              into wobject_name
              from dba_objects
              where object_id = c3_rec.object_id;
            wobject_name := wobject_name ||' '||wobject_name1;
          end loop;
        exception
          when others then
            wobject_name := wobject_name ||' No Object Found';
        end;
        dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
      exception
        when no_data_found then
          dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
      end;
    end if;
       wstep  := 30;
    select '....   Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                ' SID:' || s.sid || ' Status: ' || s.status  ||
                ' (' ||         floor(last_call_et/3600)||':'||
                                floor(mod(last_call_et,3600)/60)||':'||
                                mod(mod(last_call_et,3600),60) ||
                ') Module:'|| module ||
              ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
          into wholder_detail
          from v$session s, v$process p
          where s.sid= c1_rec.sid
            and s.paddr = p.addr;
    dbms_output.put_line(wholder_detail);
    wid1  := c1_rec.id1;
    wid2  := c1_rec.id2;
  end loop;
  if wid1 = -999999 then
       wstep  := 40;
    dbms_output.put_line('No one requesting locks held by others');
  end if;
exception
  when others then
         v_err_msg := (sqlerrm ||'  '|| sqlcode||' step='||to_char(wstep));
    DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/

Check tbs
========
select  * from dba_tablespace_usage_metrics where used_percent >90;

To Check Asm mount point disk space
============================
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup
where name ='DATA_DEV' and round((1- (free_mb / total_mb))*100, 2)>80
ORDER BY name
/





other tbs scripts
===========
http://dbaahmed.blogspot.in/2015/01/tablespace-queries-col-tablespace-for.html