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