Find EBS Profile Modified Details

select

n.user_profile_option_name "PROFILE NAME",

decode(v.level_id, 10001,'Site',10002,'Application',10003,'Responsibility',10004,'User',10005,'Server',10006,'Organization','UnDef') "LEVEL",

v.profile_option_value VALUE,

to_char(v.LAST_UPDATE_DATE,'DD-MON-YYYY:hh24:mi:ss') "Update Time",

fu.user_name "USER"

from apps.fnd_profile_options p,

apps.fnd_profile_option_values v,

apps.fnd_profile_options_tl n,

apps.fnd_user fu

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

AND v.last_updated_by=fu.user_id

and upper(n.user_profile_option_name) like upper('&profile_name%') 

--and v.level_id= 10004 ;

How to find password of a User in Oracle Apps R12?

To achieve this you need to create a small package and run a query which I wrote below

Package Specification:

CREATE OR REPLACE PACKAGE get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

Package Body:

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;

/

Query:

SELECT usr.user_name,

       get_pwd.decrypt

          ((SELECT (SELECT get_pwd.decrypt

                              (fnd_web_sec.get_guest_username_pwd,

                               usertable.encrypted_foundation_password

                              )

                      FROM DUAL) AS apps_password

              FROM fnd_user usertable

             WHERE usertable.user_name =

                      (SELECT SUBSTR

                                  (fnd_web_sec.get_guest_username_pwd,

                                   1,

                                     INSTR

                                          (fnd_web_sec.get_guest_username_pwd,

                                           '/'

                                          )

                                   - 1

                                  )

                         FROM DUAL)),

           usr.encrypted_user_password

          ) PASSWORD

  FROM fnd_user usr

 WHERE usr.user_name = ':USER_NAME';

Kill LOCAL=NO Process in Oracle Database

kill -9 `ps -ef | grep LOCAL=NO | grep AFZ | grep -v grep | awk '{print $2}'`

Kill Particular User Session in R12

select * from dba_blockers;

select * from v$session where client_identifier like 'SYSADMIN%' order by logon_time desc;

ALTER SYSTEM KILL SESSION '101,25576';

Find the APPLSYSPUB password in R12

 Source the application env file.

1. echo $GWYUID

APPLSYSPUB/PUB

2. grep -i applsyspub $FND_SECURE/$TWO_TASK.dbc

GWYUID=APPLSYSPUB/PUB

3. grep -i s_gwyuid_pass $CONTEXT_FILE

password oa_var=”s_gwyuid_pass”-- PUB -- password

FRM-92095 oracle jinitiator version too low. Please install version 1.1.8.2 o higher

 Solution:

Add a OS Parameter: JAVA_TOOL_OPTIONS, and parameter value: -Djava.vendor=”Sun Microsystems Inc.” as Environment Variables

1. Open Advanced system settings

Start Menu › Control Panel › System and Security › System

2. Click Environment Variables

3. Add a System variables

Variable name: JAVA_TOOL_OPTIONS

Variable value: -Djava.vendor="Sun Microsystems Inc."

4. Re-log or reboot your system

R12.1 Installation Issues on Linux 6

 Issue 1: 

./rapidwiz: /u01/R12_stage/startCD/Disk1/rapidwiz/bin/runWizard.sh: /bin/sh^M: bad interpreter: No such file or directory

Cause:

The file format is dos and it is not supporting for the linux environment.

Solution:

To check the file format in linux servers, open file -> :set ff 

[root@uatdb rapidwiz]# which dos2unix

/usr/bin/dos2unix

[root@uatdb rapidwiz]# cp -p -r /u01/R12_stage/startCD/Disk1/rapidwiz/bin/runWizard.sh /u01/R12_stage/startCD/Disk1/rapidwiz/bin/runWizard.sh_ORIG

[root@uatdb rapidwiz]# /usr/bin/dos2unix /u01/R12_stage/startCD/Disk1/rapidwiz/bin/runWizard.sh

dos2unix: converting file /u01/R12_stage/startCD/Disk1/rapidwiz/bin/runWizard.sh to UNIX format

- Retry the issue.

Issue 2:

/libjvm.so'.01/R12_stage/startCD/Disk1/rapidwiz/jre/Linux_x64/1.6.0/lib/i386/server

Cause:

Lower JRE Version on R12 stage directories

Solution:

Download the latest JRE from the below link and replace the JRE directory to the stage directory location.

http://www.oracle.com/technetwork/java/javase/downloads/server-jre8-downloads-2133154.html

$ cd /u01/R12_stage/startCD/Disk1/jre/Linux_x64

$ mv 1.6.0 1.6.0_old

$ cp /mnt/hgfs/shared/server-jre-8u251-linux-x64.tar.gz /u01/R12_stage/startCD/Disk1/jre/Linux_x64

$ tar -xvf server-jre-8u251-linux-x64.tar.gz

$ mv jdk1.8.0_251 1.6.0

- Retry the issue.

Issue 3:

/u01/R12_stage/startCD/Disk1/rapidwiz/jre/Linux/1.6.0//bin/java -mx512M -classpath /u01/R12_stage/startCD/Disk1/rapidwiz/jlib/java:/u01/R12_stage/startCD/Disk1/rapidwiz/jlib/xmlparserv2.jar:/u01/R12_stage/startCD/Disk1/rapidwiz/jlib/ojdbc14.jar:/u01/R12_stage/startCD/Disk1/rapidwiz/jlib/oui/OraInstaller.jar:/u01/R12_stage/startCD/Disk1/rapidwiz/jlib/oui/ewt3.jar:/u01/R12_stage/startCD/Disk1/rapidwiz/jlib/oui/share.jar:/u01/R12_stage/startCD/Disk1/rapidwiz/jlib/oui/srvm.jar oracle.apps.ad.rapidwiz.util.InstantiateFile -e /u01/orauat/UAT/db/tech_st/11.1.0/appsutil/UAT_uatdb.xml -d /u01/R12_stage/startCD/Disk1/rapidwiz/driver/dbts/driver/gdb111.drv -log /u01/orauat/UAT/db/tech_st/11.1.0/appsutil/log/UAT_uatdb/dbInstall.log -nthreads 5 -verbose -pwd nopasswordhere

Cannot execute Install for database ORACLE_HOME

RW-50010: Error: - script has returned an error:   4

RW-50004: Error code received when running external process.  Check log file for details.

Running Database Install Driver for UAT instance

Cause: 

Lower JRE Version on R12 stage directories

Solution:

Download the latest JRE from the below link and replace the JRE directory to the stage directory location.

http://www.oracle.com/technetwork/java/javase/downloads/server-jre8-downloads-2133154.html

$ cd /u01/R12_stage/startCD/Disk1/rapidwiz/jre/Linux/

$ mv 1.6.0 1.6.0_old

$ cp /mnt/hgfs/shared/server-jre-8u251-linux-x64.tar.gz /u01/R12_stage/startCD/Disk1/rapidwiz/jre/Linux/

$ tar -xvf server-jre-8u251-linux-x64.tar.gz

$ mv jdk1.8.0_251 1.6.0

- Retry the issue.

Issue 4: Post Steps:

RW-50011: Error: - Database ORACLE_HOME connection test has returned an error:  126   command: su appluat -c "/u01/R12_stage/startCD/Disk1/rapidwiz/bin/riwTDBup.sh /u01/appluat/UAT/inst/apps/UAT_uatdb/ora/10.1.2/UAT_uatdb.env APPS/APPS"

   bash: /u01/R12_stage/startCD/Disk1/rapidwiz/bin/riwTDBup.sh: /bin/sh^M: bad interpreter: No such file or directory

Cause:

The file format is dos and it is not supporting for the linux environment.

Solution:

To check the file format in linux servers, open file -> :set ff 

[root@uatdb u01]# cp -rf /u01/R12_stage/startCD/Disk1/rapidwiz/bin/riwTDBup.sh /u01/R12_stage/startCD/Disk1/rapidwiz/bin/riwTDBup.sh_06May2021

[root@uatdb u01]# /usr/bin/dos2unix /u01/R12_stage/startCD/Disk1/rapidwiz/bin/riwTDBup.sh

dos2unix: converting file /u01/R12_stage/startCD/Disk1/rapidwiz/bin/riwTDBup.sh to UNIX format ...

Issue 4:

Creating the key

Successfully created the key

Error while running keytool

adgenjky.sh finished at Sun May  9 10:55:44 GST 2021

adgenjky.sh exiting with status 1

Log file is located at : /u01/appluat/UAT/inst/apps/UAT_uatap/admin/log/UAT_uatapjkey.log

ERRORCODE = 1 ERRORCODE_END

.end std out.

Log file: /u01/appluat/UAT/inst/apps/UAT_uatap/admin/log/UAT_uatapjkey.log

/u01/appluat/UAT/apps/apps_st/appl/admin/applprod.txt

The file has format '%%% applprod file format 12.0.A^M'

and this version of  requires format '%%% applprod file format 12.0.A'

Cause:

Incorrect file format

Solution:

[appluat@uatap bin]$ /usr/bin/dos2unix /u01/appluat/UAT/apps/apps_st/appl/admin/applprod.txt

dos2unix: converting file /u01/appluat/UAT/apps/apps_st/appl/admin/applprod.txt to UNIX format ...

Find the size of an Oracle Database

 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p;

Find which user having SYSADMIN Responsibility in R12

SELECT fu.*

FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu

WHERE furgd.responsibility_id = frvl.responsibility_id

AND fu.user_id = furgd.user_id

AND(to_char(furgd.end_date) is null

OR furgd.end_date > sysdate)

AND frvl.end_date is null

AND fu.end_date is null

AND frvl.responsibility_name = 'System Administrator';

Copy Only New File to the Destination in Linux

Copy only when the SOURCE file is newer than the destination file or when the destination file is missing

cp -R -u -p /source /destination

Unable to change the password for fnd_user sysadmin in EBS

 Issue:

After Clone while changing the APPS user password using FNDCPASS command getting the following error messages in log file.

FNDCPASS apps/apps 0 Y system/manager USER sysadmin sysadmin

Working...

Unable to change the password for fnd_user sysadmin.

Cause:

Signon Password policy is enabled in ERP application, so it will not allow to repeat the username in passwords.

Solution:

It should not contain the username in passwords. ie. sysadmin/sysadmin123

FNDCPASS apps/apps 0 Y system/manager USER sysadmin admin123

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

Issue: 

While running the RMAN backup command getting the following error,

cannot make a snapshot control file

released channel: c1

released channel: c2

released channel: c3

released channel: c4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on c1 channel at 02/28/2020 20:15:58

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

RMAN>  Recovery Manager complete.

(or)

RMAN backup log file shows below while running

waiting for snapshot control file enqueue

waiting for snapshot control file enqueue

waiting for snapshot control file enqueue

waiting for snapshot control file enqueue

waiting for snapshot control file enqueue

Solution:

Check any other RMAN backup process running in server level and kill the os process

$ ps -ef|grep rman

$ kill -9 pid

after killing the OS process also getting same snapshot error. Please follow the below,

Execute the following query to determine which job is causing the wait:

SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,

       ACTION, LOGON_TIME "Logon", l.*

FROM V$SESSION s, V$ENQUEUE_LOCK l

WHERE l.SID = s.SID

AND l.TYPE = 'CF'

AND l.ID1 = 0

AND l.ID2 = 2;

You should see output similar to the following (the output in this example has been truncated):

SID User Program              Module                    Action           Logon

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

  785 SYS  rman@exad (TNS V1-V3) backup full datafile: ch5  0000007 STARTED  24-OCT-16

SQL> select sid,serial#,username,osuser,machine,event,program,sql_id,blocking_session,logon_time from gv$session where status like 'ACTIVE' and SID='785';

ALTER SYSTEM KILL SESSION '785,37463' IMMEDIATE;

Warning!!! Due to high volume of data, got out of memory exception.

 Error:

When submitting the Account Analysis Report, XLAAARPT (navigation: Reports::Request::Standard), the report errors on every run with the following error message in the log file:

***Warning!!! Due to high volume of data, got out of memory exception...***

****Please retry with scalable option or modify the Data template to run in scalable mode...****

Solution:

Increase the Memory for the Concurrent Program

1. Go to "System Administrator" responsibility.

2. Navigate to Concurrent - > Program - > Define.

3. Search for the report (example Account Analysis Report), "XLAAARPT" shortname in Concurrent Process window.

4. Set the Options field to -Xmx2048M (make sure you DONT MISS OUT the "-" before the X).

5. Save.

6. Bounce the Concurrent Manager for the effects to take place.

adcfgclone.pl dbconfig RC-20200: fatal: could not find unzip

 While cloning database Tier

Error :

RC-20200: Fatal: Could not find Unzip. At this time only Native UnZip 5.X is supported.

Please make sure you have UnZip 5.X in your path and try again…

ERROR while running Apply…

ERROR: Failed to execute /clone/oracle/product/11.2.0/appsutil/clone/bin/adclone.pl

Please check logfile.

Solution 1

Which unzip should point to $ORACLE_HOME/bin/unzip

Take the backup of existing unzip from $ORACLE_HOME/bin

cd $ORACLE_HOME/bin

mv unzip unzip_bkp

cp /usr/bin/unzip to $ORACLE_HOME/bin

Make sure the unzip utility under /usr/bin should be of version 5.X and not 6

Solution 2:

Download the unzip file from below link 

https://oss.oracle.com/el4/unzip/unzip.html

Take the backup of existing unzip from $ORACLE_HOME/bin

Untar donwloaded file into the $ORACLE_HOME/bin/

Retest the issue.

Find Toad Connections in Oracle Database

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr

and s.MODULE like ('%TOAD%')

Order by last_call_et;

ADOP Cutover Failed

Before starting the ADOP patch cycle,

Run File system: FS1

Patch File System: Fs2

Issue:

Prepare : Completed

Apply : Completed

Finalize: Completed

Cutover: Failed --> After file system switch over completed and while starting Middle Tier it is failed.

set pagesize 200;

set linesize 160;

col PREPARE_STATUS format a15

col APPLY_STATUS format a15

col CUTOVER_STATUS format a15

col ABORT_STATUS format a15

col STATUS format a15

select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS  ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS 

from AD_ADOP_SESSIONS

order by ADOP_SESSION_ID;

ADOP_SESSION_ID P A C C A S

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

12 Y Y 6 N X F

12 Y Y 5 N X F 

Cutover Statuses:

cutover_status='Y' 'COMPLETED'

cutover_status not in ('N','Y','X') and status='F' 'FAILED'

cutover_status='0' 'CUTOVER STARTED'

cutover_status='1' 'SERVICES SHUTDOWN COMPLETED'

cutover_status='3' 'DB CUTOVER COMPLETED'

cutover_status='D' 'FLIP SNAPSHOTS COMPLETED'

cutover_status='4' 'FS CUTOVER COMPLETED'

cutover_status='5' 'ADMIN STARTUP COMPLETED'  --> Issue occured here

cutover_status='6' 'SERVICES STARTUP COMPLETED'

cutover_status='N' 'NOT STARTED'

cutover_status='X' 'NOT APPLICABLE'

So Cutover Failed, But File system is already switched i.e patch edition (FS2) become run edition and run edition (FS1) become patch.

Run File System: FS2

Patch File System: FS1

Solution:

1. Source the Run edition Env file (FS2 is Run edition here)

2. Shutdown the application services and start only admin server.

3. Run the cutover again, this cutover will not bringdown and brought up any services and also will not flip filesystem again,  It will complete quickly.

adop phase=cutover mtrestart=no

4. Restart tha application services on all nodes.