my-orcl@Areef DBA, my-orcl@Arif DBA: Oracle EBS Basics

List of Topics (Linux, Database, RAC, EBS)

Oracle Apps Basic Stuff

Query to check modified Profile options:

--to check modifide profile options at site,resp, & User, can filter US & AR Language Level

select opt.PROFILE_OPTION_NAME "SHORT NAME",tl.user_profile_option_name "PROFILE_OPTION_NAME",tl.SOURCE_LANG,

decode(val.level_id,

10001, 'Site',

10002, 'Application',

10003, 'Responsibility',

10004, 'User',

10005, 'Server',

10006, 'Organization',

10007, 'Server+Resp',

'No idea, boss') "OPTION_LEVEL",

val.profile_option_value "MODIFIED_VALUE",

val.last_update_date "MODIFIED_DATE",

usr.user_name "MODIFIED_USER"

from fnd_profile_options opt,

fnd_profile_option_values val,

fnd_profile_options_tl tl,

fnd_user usr

where opt.profile_option_id = val.profile_option_id

and opt.profile_option_name = tl.profile_option_name

and usr.user_id = val.last_updated_by

--and tl.SOURCE_LANG in('US')-- only for English profile options

order by val.last_update_date desc

================================================================================================================

Important View Request output of other users

configure as show in below link

https://abhayappssolution.blogspot.com/2012/11/view-other-users-request-output.html

================================================================================================================

Important and useful system profiles

Create profile new/view by code like
Method-1 from java forms --> Application Developer --> Profile --> NAME
Method-2 from console --> Navigate to --> Functional Administrator --> Core Services --> Profiles --> Code = ICX_SESSION_COOKIE_DOMAIN

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

To update Iconic view for All EBS Users in 12.2.9 EBS:

set the following Profile option on Site level:

How to Personalize the E-Business Suite Home Page (Doc ID 2449016.1)

For more details, please refer to below blog post

https://blogs.oracle.com/ebstech/new-simplified-home-page-in-ebs-1224

Profile Name: Self Service Personal Home Page mode 

Code name: APPLICATIONS_HOME_PAGE

Value: Framework Simplified

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

Changing parameter “Select the Number of Days to View” Request in EBS

E-Business Suite Concurrent Processing Steps To Change The Default Settings For "Select the Number of Days to View" When Copying A Concurrent Request (Doc ID 2423274.1)

Change System Profile Options

 profile decides the default value shown in "Select the Number of Days to View" field after you choose View->Requests

This profile option specifies the default number of days to view requests.

Profile name: FND: Default Request Days

Code name: FND_DEFAULT_REQUEST_DAYS

-----------------------------------------
Auto Refresh

Auto Refresh Checkbox On Requests Form Does Not Seem To Do Anything (Doc ID 2481411.1)

Profile name: Auto-refresh View Request Timer (secs)

Code name: AUTO_REFRESH_REQUESTS

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

Export to Excel from JAVA option for User: (MALI, SAMIRM, JSALIM)

Profile name: Export MIME type

Code name: FND_EXPORT_MIME_TYPE

User value: application/vnd.ms-excel

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

Profile name: Applications Start page

Code name: APPLICATIONS_START_PAGE

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

Date format change at user level: (16-11-2021 abdi mail)

Profile name: ICX: Date format mask

Code name: ICX_DATE_FORMAT_MASK

User Value: 31.12.1999

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

Readonly access to atifarbab for HR Responisbility (23-05-2022 ramez)

Profile name: HR:Query Only Mode

Code name: PER_QUERY_ONLY_MODE

Responsibility: AHC_HRMS_PAYROLL_GROUP

User Value: Yes

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

Hijri Calendar Support in Oracle E-Business Suite Release 12

Hijrah and Thai Calendar Support in Oracle E-Business Suite Release 12.1 and 12.2 (Doc ID 807393.1)

https://appsdbastuff.blogspot.com/2013/12/hijrah-calendar-support-in-oracle-e.html?view=flipcard

Profile name: XDO: Calendar Type

Code name: XDO_CALENDAR_TYPE

Profile name: FND: Calendar Week Start Day

Code name: FND_CALENDAR_WEEK_START_DAY

Profile name: FND: Forms User Calendar

Code name: FND_FORMS_USER_CALENDAR

Profile name: FND: Tooltip Calendar

Code name: FND_TOOLTIP_CALENDAR

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

FND Debug Mode generation FND Log Steps:

R12: How To Generate FND Debug Log File? (Doc ID 2225305.1)

How to Generate FND Log For HCM Issues (Doc ID 2321999.1)

1.Set the following profiles only at the USER level:

FND: Debug Log Enabled= 'Yes'

FND: Debug Log Level = 'Statement' (set this only to debug)

FND: Debug Log Filename <empty>

FND: Debug Log Module = %

FND: Debug Log Mode Asynchronous with Cross-Tier Sequencing

Log out of Applications and log in again

2.Then run the following SQLs serially to get the debug information:

SELECT max (log_sequence) from fnd_log_messages;

3.Submit the program

Example - Transfer Journal Entries to GL program from Receivables responsibility with END_DATE = 30-NOV-2020a

4.Provide the FND debug log using the following query:

-- Query 1 for Seq

SELECT module, log_sequence, message_text, timestamp

FROM fnd_log_messages

WHERE log_sequence > [result from first query]

order by log_sequence;

-- Query 2 for Seq

SELECT * FROM fnd_log_messages

WHERE log_sequence between &before_seq and &after_seq

--and user_id = &userid_value_from_step1

ORDER BY log_sequence;

5. Please reset the profiles back to normal.

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

https://appsdbastuff.blogspot.com/2013/12/hijrah-calendar-support-in-oracle-e.html?view=flipcard

================================================================================================================

If concurrent status is showing as following then

Check for the patch (12410716). if it is not applied earlier, then apply and check again.

MOS Note : Adcmctl.Sh Script Is Unable To Determine The ICM Status (Doc ID 1320217.1

MOS Note : FNDSVCRG Status could not be Determined after Linux RHEL 5 Version OS Security Patch Updates (Doc ID 1399794.1)

======================

[oraclone@RUH-ERPAT01 scripts]$ adcmctl.sh status apps/apps

You are running adcmctl.sh version 120.17.12010000.5

Internal Concurrent Manager status could not be determined.

adcmctl.sh: exiting with status 0

adcmctl.sh: check the logfile /u01/PCLONE/inst/apps/pclone_ruh-erpat01/logs/appl/admin/log/adcmctl.txt for more information ...

[oraclone@RUH-ERPAT01 scripts]

======================

Check for the patch 12410716 wheater it is applied or not:

[oratest@RUH-ERPAT01 ~]$ adcmctl.sh status apps/apps

You are running adcmctl.sh version 120.17.12010000.5

Internal Concurrent Manager is Active.

adcmctl.sh: exiting with status 0

[oratest@RUH-ERPAT01 ~]$

================================================================================================================

Concurrent Managers log files and Troubleshoots Tips:

ls -ltrh $APPLCSF/log/$TWO_TASK*.mgr ##--ICM Manager log

ls -ltrh $APPLCSF/log/c*.mgr ##--CRM Manager log

ls -ltrh $APPLCSF/log/FNDOPP*.txt ##--OPP Manager log

ls -ltrh $APPLCSF/log/w*.mgr ##--Standard Manager log

ls -ltrh $APPLCSF/log/FNDCPGSC*.txt ##-- Workflow Mailer log

Query to check Administer Concurrent Managers from backend

SELECT b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"

,DECODE (b.control_code

,'D', 'Deactivating'

,'E', 'Deactivated'

,'N', 'Node unavai'

,'A', 'Activating'

,'X', 'Terminated'

,'T', 'Terminating'

,'V', 'Verifying'

,'O', 'Suspending'

,'P', 'Suspended'

,'Q', 'Resuming'

,'R', 'Restarting'

) status

FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b

WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.running_processes = a.max_processes

--and b.user_concurrent_queue_name='Standard Manager'

ORDER BY a.max_processes DESC;

Concurrent Managers Recovery for any CM issues #Note stop CM before:

- Using CM Recovery Wizard from EBS console

Concurrent Processing - Command-Line Utility OR Recovery Wizard (Doc ID 134007.1)

adstrtal.sh apps/apps (run on all internal nodes where FND are running)

Before Running CM recovery stop ALL CM on all nodes

adcmctl.sh stop apps/apps (run on all internal nodes where FND are running)

adcmctl.sh abort

ps -fu applmgr|grep -i FND|wc -l

ps -ef | grep -i fnd |wc -l

kills Only FNDLIBR of applmgr which auto restart by internal conc manager

ps -fu applmgr|grep FND | grep -v grep | awk '{print $2}' | xargs kill -9

- Applications Dashboard

- Site Map

- Diagnostics and Repair

- Concurrent Manager Recovery

- Run Wizard

-- If you get any ID pending concurrent ID then change status from R to C

select * from apps.fnd_concurrent_requests where phase_code='R'--check hang req which is still in Running state

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID'; --update hang req to complete state

- Using cpadmin utility

R12 E-Business Suite Concurrent Processing Script Cpadmin.sh Command Line Utility Which Consolidates Existing CP Tools Into A Single Menu-Based Shell Script (Doc ID 2319585.1)

ls -ltrh $INST_TOP/appl/fnd/12.0.0/secure/*.dbc

echo $TWO_TASK

grep twotask $CONTEXT_FILE

cpadmin.sh

1. Administer Concurrent Managers

Enter your choice: [E] : 1

2. Clean concurrent processing tables

- Ensure concurrent processing tables are cleaned and reset for ICM startup

Enter your choice: [R] : 2

Running command: Clean concurrent processing tables (120.0.12020000.5)

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

This utility should be used when the Internal Concurrent

Manager fails to start due to corrupted internal state

information.

The Internal Concurrent Manager must not be running at

this time. The utility will verify the Internal

Concurrent Manager is not running before making changes.

Continue (Y or N) Y

Executed select current status of manager processes.

All managers now shut down, press any key to continue.

Executed update fnd_concurrent_queues: 47 rows processed.

Press any key to continue.

Executed kill sessions of deactivated and terminated processes.

Press any key to continue.

Executed select to identify runaway processes.

No run-away processes, press any key to continue.

Executed unsubscribe AQ's for Generic Cartridge services..

Press any key to continue.

Executed select requests to reset for conflict resolution

No requests identified for conflict resolution, press any key to continue.

Executed select orphaned requests

No Orphaned Requests identified

Utility is now complete. You may attempt to restart the Internal Concurrent Manager

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

Press [Return] to continue...

1. How to start ICM manually from backend if it is down

1. First check the status of ICM

[oratest@RUH-ERPAT01 scripts]$

Go to ->Concurrent -> Manager -> Administer (Actual(0) =/= target(1))

Now Check ICM log(location):

Go to ->Concurrent -> Manager -> Administer -Processes -> Click button Internal Manager Log.(filename=PTEST_1009.mgr)

It will show current log file location

[oratest@RUH-ERPAT01 ~]$ ls -ltrh $APPLCSF/log/$TWO_TASK*.mgr

[oratest@RUH-ERPAT01 log]$

[oratest@RUH-ERPAT01 log]$tail -100f PTEST_1009.mgr

======================

To start ICM Manually

source Apps Env.

======================

[oratest@RUH-ERPAT01 ~]$ ls -ltrh $FND_TOP/bin/startmgr.sh

[oratest@RUH-ERPAT01 bin]$ pwd

/u01/PTEST/apps/apps_st/appl/fnd/12.0.0/bin

[oratest@RUH-ERPAT01 bin]$ sh startmgr.sh

Password:

Starting icm@pclone Internal Concurrent Manager

Default printer is

[oraclone@RUH-ERPAT01 bin]$

======================

Now Log in to Sysadmin Responsibility

Go to ->Concurrent -> Manager -> Administer (Actual(1) == target(1))

Now Check ICM log file (location):

Go to ->Concurrent -> Manager -> Administer -Processes -> Click button Internal Manager Log.(filename=icm.mgr)

It will show current log file location

[oratest@RUH-ERPAT01 ~]$ ls -ltrh $APPLCSF/log/$TWO_TASK*.mgr

[oratest@RUH-ERPAT01 ~]$ cat $APPLCSF/log/icm.mgr

-- Current ICM Log file location check

SELECT fcp.logfile_name as "ICM LOG FILE"from fnd_concurrent_processes fcp, fnd_concurrent_queues fcq

WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

AND fcp.queue_application_id = fcq.application_id

AND fcq.manager_type = '0' AND fcp.process_status_code = 'A'; -- Current ICM Log

======================

2. To check Conflict Resolution Manager Log:

source Apps Env.

======================

[oratest@RUH-ERPAT01 ~]$ ls -ltrh $APPLCSF/log/c*.mgr

======================

3. To check Output Post Process running PID in Linux

source Apps Env.

======================

[oratest@RUH-ERPAT01 ~]$ ps -ef |grep FNDOPP | grep -v grep

[oratest@RUH-ERPAT01 ~]$ ls -ltrh $APPLCSF/log/FNDOPP*.txt

SQL> select name from APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S; --Check current Running OPP Process

OPP are configured in internal Primary node (need to verify)

======================

4. To check Standard Manager Log:

source Apps Env.

======================

[oratest@RUH-ERPAT01 ~]$ ls -ltrh $APPLCSF/log/w*.mgr

================================================================================================================

Upload Context file to database (source)

Source the Patch FS (Upload the context file to Database)

$ . /u*/app/applmgr/<SID>/EBSapps.env patch (No need to run autoconfig, Just upload the context file)

select distinct(PATH) from FND_OAM_CONTEXT_FILES where NAME not in ('TEMPLATE','METADATA','config.txt') and CTX_TYPE='A' and (status is null or upper(status) in ('S','F')) and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') in ('run','patch');

[oratest@RUH-ERPAT01 ~]$ $ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=$CONTEXT_FILE logfile=~/patchctxupload.log

================================================================================================================

EBS Database CHECKS:

select * from dba_registry_sqlpatch;--last applied BUNDLE PATCH (BP) in EBS DB

select POLICY_GROUP, POLICY_NAME, OBJECT_NAME, PACKAGE, FUNCTION from DBA_POLICIES;--security policies in DB

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

EBS Database Locked Objects:

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,

b.object_id,substr(b.object_name,1,40) object_name,sysdate, c.process

from v$session a, dba_objects b, v$locked_object c

where a.sid = c.session_id

and b.object_id = c.object_id

SQL> ALTER SYSTEM KILL SESSION '2092,23455';

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

Basic EBS Queries:

[oratest@RUH-ERPAT01 ~]$ sqlplus apps/apps

EBS Application Active User List:

SQL> select * from fnd_user where end_date is null order by user_id

SQL> select count(*) from fnd_user u where u.END_DATE is null and u.description NOT like '%ORACLE12%' and u.description NOT like 'Sys%'

Query to get count of active users session/node during EBS hang issue

SQL> show user

USER is "APPS"

SQL>

col OWNER for a10;
col NODE_NAME for a25;
col HOW_MANY_USER_SESSIONS for a35;
select t.owner,t.last_analyzed,t.num_rows,t.blocks from dba_tables t where table_name like 'ICX_SESSIONS';
select min(first_connect), count(*) from icx.icx_sessions;
select a.node_name, 'Number of user sessions : '||count(distinct b.session_id) How_many_user_sessions from apps.fnd_nodes a,apps.icx_sessions b
where disabled_flag != 'Y' and PSEUDO_FLAG = 'N'
and (last_connect + decode(apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
NULL,limit_time, 0,limit_time,apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and a.node_id=b.node_id and counter < limit_connects group by a.node_name;

NODE_NAME HOW_MANY_USER_SESSIONS
------------------------- -----------------------------------
EBSPRODAPP3 Number of user sessions : 46
EBSPRODAPP4 Number of user sessions : 20
EBSPRODPUBAPP1 Number of user sessions : 11

Query to get count of todays connected user
-- list of todays connections with responsibility
select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility, ic.last_connect
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate
order by 3 desc
-- list of todays connections in descending order.
select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY')
order by 2 desc
--number of concurrent_users connected
select distinct d.user_name from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1);
-- Use this SQL statement to count number of users connected to Oracle Apps in the past 1 hour and day
select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;--last 15 mins
select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';-- last 1 hour
select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';-- last 1 day before

EBS Application Responsiblity id

SQL> select f.RESPONSIBILITY_ID, f.RESPONSIBILITY_NAME from fnd_responsibility_vl f where f.RESPONSIBILITY_NAME like 'AJ%' -- to get responsibility ID

Query to get list of FND users having particular responsibility (Sysadmin)

SQL> SELECT DISTINCT res.responsibility_name, usr.user_name, usr.description

FROM fnd_user usr,

fnd_user_resp_groups_direct en,

fnd_responsibility_vl res

WHERE usr.user_id = en.user_id

AND en.responsibility_id = res.responsibility_id

AND res.responsibility_name IN ('System Administrator') --Enter responsibility name here

AND usr.end_date IS NULL --For Active users

AND en.end_date IS NULL

ORDER BY responsibility_name;

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

Query to get list of FND users having list of Active responsibility:

SQL> select u.user_name,

pap.employee_number,
pap.full_name,

org.name ORGANIZATION_NAME,
bgp.name Business_Group,
frt.RESPONSIBILITY_NAME,
u.EMAIL_ADDRESS
from fnd_user u,
per_all_assignments_f paa,
per_all_people_f pap,
pay_all_payrolls_f pay,
hr_all_organization_units org,
per_business_groups bgp,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furg
where
u.description NOT like '%ORACLE12%'
and u.description NOT like 'Sys%'
and u.end_date is null
and paa.effective_end_date(+)>sysdate
and pap.effective_end_date(+)>sysdate
and paa.person_id(+) = u.employee_id
and paa.person_id = pap.person_id(+)
and pay.payroll_id(+) = paa.payroll_id
and pay.effective_end_date(+)>sysdate
and org.organization_id(+) =paa.organization_id
and bgp.business_group_id=paa.business_group_id
and furg.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID
and furg.END_DATE is null
and u.user_id=furg.user_id
and frt.language='US'
--and u.user_name ='MAREEF'
order by pay.payroll_name, org.name, pap.employee_number

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

EBS Application Custom Table:

SQL> select * from tab where tname like 'XX%'

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

EBS Application Database Users:

SQL> select * from dba_users where account_status = 'OPEN'

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

EBS Application Custom Objects:

SQL> select * from dba_objects where owner = 'APPS' and object_name like 'XX%'

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

Query to check all locked objects in EBS and kill specific session one by one:

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,

b.object_id,substr(b.object_name,1,40) object_name,sysdate, c.process

from v$session a, dba_objects b, v$locked_object c

where a.sid = c.session_id

and b.object_id = c.object_id

SQL> ALTER SYSTEM KILL SESSION '2092,23455';

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

Query to kill all session of particular user "USER_LIVE" on database:

BEGIN

FOR r IN (select sid,serial# from v$session where username='USER_LIVE')

LOOP

EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ','

|| r.serial# || ''' immediate';

END LOOP;

END;

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

Active EBS User Query Customized to check based on (Creation Date, Company, User Description)

--select * from fnd_user u where u.end_date is null and u.description NOT like '%ORACLE12%' and u.description NOT like 'Sys%'-- Total Count of Active Users

select u.user_name,

pap.employee_number,

ppt.USER_PERSON_TYPE,-- to chek emp type(ex-emp, emp)

pap.person_id,

pap.full_name, --pap.LAST_NAME, pap.FIRST_NAME,

pay.payroll_name,

org.name, u.description,

u.last_logon_date,

paa.BUSINESS_GROUP_ID,

u.EMAIL_ADDRESS,-- from fnd users

pap.EMAIL_ADDRESS, -- from hr table

u.USER_ID

from fnd_user u,

per_all_assignments_f paa,

per_all_people_f pap,

pay_all_payrolls_f pay,

hr_all_organization_units org,

per_person_types ppt

where

u.description not like '%ORACLE12%' -- 10 user (Internal user ORACLE12.9.0 for release 12)

and u.description not like 'Sys%' -- 7 sys users

and u.end_date is null --to check emp no in all business groups

--and u.user_name not in ('ITSUPPORT','SCHEDULER')

--and pap.employee_number in ('638','1475')--1475 --Password_Reset/Suspended/Terminated Emp Check

--and u.ENCRYPTED_USER_PASSWORD='INVALID' -- to check EBS User ACCOUNT LOCKED STATUS

-- and u.description like '%SSHR%'

--and u.description like 'AJ%'

--and u.user_name = 'MAREEF'

--and u.creation_date>>'25-AUG-2019'

--and pap.employee_number is null

--and pay.payroll_name is null -- for Global payroll Emp. List

and ppt.PERSON_TYPE_ID(+)=pap.PERSON_TYPE_ID -- even if they do not have person type

and paa.effective_end_date(+)>sysdate

and pap.effective_end_date(+)>sysdate

-- and sysdate between paa.EFFECTIVE_START_DATE and paa.effective_end_date -- to check user with emp number example user: MOHAMMED.ALJISHI

--and sysdate between pap.EFFECTIVE_START_DATE and pap.effective_end_date -- to check user with emp number

and paa.person_id(+) = u.employee_id

and paa.person_id = pap.person_id(+)

and pay.payroll_id(+) = paa.payroll_id

and pay.effective_end_date(+)>sysdate

and org.organization_id(+) =paa.organization_id

order by pay.payroll_name, org.name, pap.employee_number

--select * from fnd_user u where u.end_date is null and u.description NOT like '%ORACLE12%' and u.description NOT like 'Sys%'-- Total Count of Active Users

--select * from fnd_user u where u.end_date is null and u.description like '%ORACLE12%' and u.description like 'Sys%'-- Total Count of Active Users

--select * from fnd_user u --where u.END_DATE is null --and u.DESCRIPTION is null --to check active users with missing user Discription

--select p.FULL_NAME,p.EMAIL_ADDRESS,p.NATIONALITY,p.BUSINESS_GROUP_ID,p.PERSON_ID from per_all_people_f p where p.EMPLOYEE_NUMBER=1475; --to check emp without oracle user

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

-- --- ALL Active Emp with/without ebs account as per payroll file group (07-07-2022)

-- select u.USER_NAME,a.EMPLOYEE_NUMBER,ppt.USER_PERSON_TYPE ,a.FULL_NAME ,b.PAYROLL_ID ,a.PERSON_TYPE_ID,a.BUSINESS_GROUP_ID

-- , (select pay.PAYROLL_NAME

-- from pay_all_payrolls_f pay

-- where pay.PAYROLL_ID = b.PAYROLL_ID

-- and sysdate between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE) as payroll_name

-- from per_all_people_f a, per_all_assignments_f b,fnd_user u, per_person_types ppt

-- where sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE

-- and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE

-- and a.PERSON_ID = b.PERSON_ID

-- and u.EMPLOYEE_ID(+)=a.PERSON_ID

-- and ppt.PERSON_TYPE_ID=a.PERSON_TYPE_ID

-- --and ppt.USER_PERSON_TYPE='Employee' -- change condition to Ex-emp, emp as you like

-- -- and a.BUSINESS_GROUP_ID not like 0 --- to eliminate global users

-- --and ppt.BUSINESS_GROUP_ID=a.BUSINESS_GROUP_ID

-- -- --and a.PERSON_TYPE_ID in (146,268,243) --- for perticular bussiness group

-- -- and a.BUSINESS_GROUP_ID in (343,129,821) --comment this Line

-- --and a.EMPLOYEE_NUMBER in ('584','804') --comment this Line

order by b.PAYROLL_ID

-- --order by 8

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

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

================================================================================================================

EBS 12.2 Weblogic: IF unable to access Weblogic Console page then delete the deny rule line form following file and stop start Application:

applmgr@rac2z:~$ vi $EBS_DOMAIN_HOME/config/config.xml

. . . . .

<connection-filter-rule>0.0.0.0/0 * * deny</connection-filter-rule>

. . . . .

Save the file and the stop start Application:

================================================================================================================

Error When DP_POLL Failed For /dev/poll - Operation Not Applicable With Oracle Access Manager 11g R2PS3 (OAM 11.1.2.3) (Doc ID 2442363.1)(Doc ID 2092448.1)

- DPPOLL, JAVA_OPTIONS

applmgr@rac2z:~$ vi $EBS_DOMAIN_HOME/bin/setDomainEnv.sh

. . . . .

JAVA_OPTIONS="${JAVA_OPTIONS} ${enableHotswapFlag} -ea -da:com.bea... -da:javelin... -da:weblogic... -ea:com.bea.wli... -ea:com.bea.broker... -ea:com.bea.sbconsole..."

-Dweblogic.DevPollDisabled=true

export JAVA_OPTIONS

. . . . .

================================================================================================================

Flexfeild error in EBS 12.2, EBS User unable to check List of values as it throw the below error:

FRM-41830: List of Values contains no entries.

MOS Doc(Doc ID 1612727.1): Flexfield Value Sets Do Not Appear in List of Values (LOV) in Oracle E-Business Suite Release 12.2 on Segment Values Form FNDFFMSV (Doc ID 1612727.1)

Solution: Assign The following Role to EBS user:

You can Search by Name & Code:

Roles and Responsibilities: Flexfield Value Set Security: All privileges

Code: UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE

Login EBS: User Management---> Users---> EBS user---> update---> Assign Roles---> Flex%

================================================================================================================

How to Cancel/Delete the Workflow Notification for Self Service

How to Remove a Self Service Human Resources (SSHR) Pending Transaction (Doc ID 783156.1)

What is the Meaning of Different Status Codes in Hr_api_transactions for iRecruitment? (Doc ID 734420.1)

HR Defunct Process Does Not Remove Transactions With Status Cancelled (Doc ID 343401.1)

Navigate to Status Monitor-->Query for the transaction-->Click the Activity History-->Click on cancel workflow.

Step 1: Get the person_id of the person who submitted the SSHR transaction.

SELECT DISTINCT person_id,last_name, first_name

FROM per_all_people_f

WHERE last_name = '<last_name>'

AND first_name = '<first_name>'

AND effective_end_date > SYSDATE;

Step 2: Get transaction id to be removed, use the person_id from Step1. If you do not find any rows then either the creator_person_id is wrong or the transaction is already complete.

SELECT *

FROM hr_api_transactions

WHERE creator_person_id = '<person_id>';

Step 3: Call API to remove the hanging transaction using transaction_id from Step 2, This API can only be used if the workflow transaction is not complete. If the transaction is complete then the record will NOT be in hr_api_transactions table. If you have multiple transaction submitted by the same person then verify and get the transaction_id which you want to remove.

Replace <transaction_id> with transaction_id to be removed.

BEGIN

apps.hr_transaction_api.rollback_transaction( p_transaction_id => <transaction_id> ,p_validate => false );

END;

Step 4: Repeat Step 2 you should not see any rows from the SQL

COMMIT;

Step 5: Logout of the Self-service application and retest the issue.

================================================================================================================

Interview Questions:

1- How to skip a failed worker using adctrl utility?

Error:

FAILED: file mth_pre_upgrade.sql on worker 1. (if you are running adpatch for second time u will get this errror).

Solution:

1. Start adctrl utility (see more info here How to use adctrl utility to restart failed worker in patch application?)

2. You will be shown 7 options in the main menu. But there is an 8th option that is not shown.

3. Use option 8 to skip a failed worker. Simply type 8 and hit enter key. The worker will be skipped.

4. Now resume your patch application

2- I am applying a patch, can I open another session and run adpatch

Solution:

Answer "NO" because, FND_INSTALL_PROCESSES is a table that is created by RAPID INSTALL, ADPATCH and ADADMIN utilities. Normally the above utilities will remove this table after they complete. This table might exist if you are running any of the above processes. So, if you are also applying another application patch at the same time, the table might exist for the other patch. Dropping the FND_INSTALL_PROCESSES table should only be done if you are NOT in the middle of running ADADMIN or applying another patch. Consider this scenario: You are running the install and you get instructions to obtain a patch (usually this would be an FND patch) and you attempt to use ADPATCH to install the patch. If you were to drop FND_INSTALL_PROCESSES at this point, you would have to start your install over. However, you could backup FND_INSTALL_PROCESSES and the restart files. Then run adpatch to install the patch. Then restore FND_INSTALL_PROCESSES and the restart files, and continue the installation. If you want to drop the table, you will need to login as the owner of the FND tables. Normally this is APPLSYS. Then type the following:

drop table fnd_install_processes;

================================================================================================================

Apps Password in EBS 12.2

applsc@rac1:~$ cd $APPL_TOP_NE/ad/custom/

applsc@rac1:...2/fs_ne/EBSapps/appl/ad/custom$ pwd

/u01/SC/APPS122/fs_ne/EBSapps/appl/ad/custom

applsc@rac1:~$ vi $APPL_TOP_NE/ad/custom/adop_sync.drv

. . . . .

#Scripts Start

#Sample syntax for custom scripts.

#The scripts could be a shell OR CMD or perl OR WLST script

#For Unix Platforms

#sh /u01/SC/APPS122/fs1/EBSapps/appl/ad/12.0.0/bin/custom.pl -contextfile=<CONTEXT_FILE_PATH>|STDIN_ARGS={scsctestm7, %s_wlsPwd%}

#sh /u01/SC/APPS122/fs1/FMW_Home/wlserver_10.3/common/bin/wlst.sh custom.py -appspwd scsctestm7

#For Windows Platform

#/u01/SC/APPS122/fs1\EBSapps\appl\ad\12.0.0\bin\custom.pl -contextfile=<CONTEXT_FILE_PATH>|STDIN_ARGS={scsctestm7, %s_wlsPwd%}

#/u01/SC/APPS122/fs1\FMW_Home\wlserver_10.3\common\bin\wlst.cmd custom.py -appspwd scsctestm7

#Scripts End

#SQL Starts

#Sample SQL script

#For Unix Platforms

#/u01/SC/APPS122/fs1/EBSapps/10.1.2/bin/sqlplus APPS/scsctestm7 @test.sql

#For Windows Platform

#/u01/SC/APPS122/fs1\EBSapps\10.1.2\bin\sqlplus APPS/scsctestm7 @test.sql

#SQL ends

#Forms Start

#Sample to compile custom forms.

#Copy the generated fmx into <FORMS_PATH>

#For Unix Platforms

#/u01/SC/APPS122/fs1/EBSapps/10.1.2/bin/frmcmp <some Location>/custom.fmb applsc/scsctestm7

#For Windows Platform

#/u01/SC/APPS122/fs1\EBSapps\10.1.2\bin\frmcmp <some Location>/custom.fmb applsc/scsctestm7

#Froms End

#End Customization

================================================================================================================

Log file location for ADPATCH

[oratest@RUH-ERPAT01 ~]$

[oratest@RUH-ERPAT01 ~]$ cd $APPL_TOP/admin/$TWO_TASK/log

[oratest@RUH-ERPAT01 ~]$

Concurrent Request Log File Location

[oratest@RUH-ERPAT01 ~]$

[oratest@RUH-ERPAT01 ~]$ cd $APPLCSF

[oratest@RUH-ERPAT01 conc]$ pwd

/u01/PTEST/inst/apps/SID_ruh-erpat01/logs/appl/conc

[oratest@RUH-ERPAT01 conc]$ ls

inbound log out outbound

[oratest@RUH-ERPAT01 conc]$

================================================================================================================

User wise Concurrent Count for user applmgr

Only FNDLIBR processes with user applmgr- Prod 225

ps -fu applmgr|grep -i FNDLIBR|wc -l

If you have multiple instances on the same server, you can use the below command to kill FNDLIBR processes only of a particular user.

kills Only FNDLIBR of applmgr which auto restart by internal conc manager

[root@rac1 ~]$ ps -fu applmgr|grep FNDLIBR | grep -v grep | awk '{print $2}' | xargs kill -9

kills Only Remote DB Connection (do not execute from root insted, use with OS user)

All CM actual=target will set slowly slowly

ERROR: ORA-00020: maximum number of processes (200) exceeded

[oramgr@rac1 ~]$ ps -ef | grep LOCAL=NO | awk '{print $2}' | xargs kill -9

Only Java with user applmgr – Prod 33

ps -fu applmgr|grep -i java|wc -l

Java & Weblogic with user applmgr - Prod 266

ps -fu applmgr|grep -i FND|wc -l

All FNDLIBR Processes Running on Server with applar, appltest, etc.

ps -ef | grep FNDLIBR| wc -l

All Background Process Running with user applmgr - Prod 384

ps -ef|grep -i applmgr|wc -l

ps -ef|grep -i applmgr

All Running listener list:

ps -ef|grep tns

Pkill do not execute from ROOT,

it kills all background process of multiple OS User/Instances

pkill -9 FNDLIBR ß To kill all multiple users Concurrent running on Server

pkill -9 FND ß To kill Weblogic and Java Connections

pkill -u applmgr ß To kill All BP’s running with user except current window

================================================================================================================

Watch Command for FND Process

[oratest@RUH-ERPAT01 ~]$

[oratest@RUH-ERPAT01 ~]$ watch -n 1 'ps -fu oratest|grep -i fnd|wc -l'

================================================================================================================

URL find for EBS using grep

[root@rac1 ~]#

[root@rac1 ~]# su - oraerp

[oraerp@rac1 ~]$

[oraerp@rac1 ~]$. /u01/EBS_APPS/PROD/fs1/EBSapps/appl/APPSPROD_rac1.env

[oraerp@rac1 ~]$

[oraerp@rac1 scripts]$ grep login $CONTEXT_FILE

<login_page oa_var="s_login_page">http://rac1.dell.com:8012/OA_HTML/AppsLogin</login_page>

[oraerp@rac1 scripts]$

Weblogic Port check

[oraerp@rac1 scripts]$ grep wls_admin $CONTEXT_FILE

<wls_adminport oa_var="s_wls_adminport" oa_type="PORT" base="7001" step="1" range="-1" label="WLS Admin Server Port">7013</wls_adminport>

Weblogic Console Url: http://rac1.dell.com:7013/console

Enterprise Manager Console Url: http://rac1.dell.com:7013/em

Weblogic Version Check

[oraerp@rac1 scripts]$ grep wls_version $CONTEXT_FILE

<wls_version oa_var="s_wls_version">10.3.6.0</wls_version>

================================================================================================================

Port number Check for Oracle EBS

[root@RUH-ERPAT01 ~]#

[root@RUH-ERPAT01 ~]# netstat -an |grep 8012

tcp 0 0 0.0.0.0:8012 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:22269 127.0.0.1:8012 TIME_WAIT

tcp 0 0 127.0.0.1:22248 127.0.0.1:8012 TIME_WAIT

tcp 0 0 127.0.0.1:22260 127.0.0.1:8012 TIME_WAIT

[root@RUH-ERPAT01 ~]#

================================================================================================================

To check the file versions in EBS, run

Source your apps environment and run following command:

adident Header $ALR_TOP/bin/ALECDC

adident Header $ALR_TOP/bin/ALPPWF

strings $ALR_TOP/bin/ALECDC | grep Header | grep alssmn.lc
strings $ALR_TOP/bin/ALPPWF | grep Header | grep aluapp.lc

================================================================================================================

Context File Check EBS R12.2

Database Node Context File

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ . CLONE_rac1.env

[oracle@rac1 ~]$

[oracle@rac1 ~]$ env | grep .xml

CONTEXT_FILE=/u01/db_CLONE/12.1.0/appsutil/CLONE_rac1.xml

[oracle@rac1 ~]$

Application File System-1

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ . fs1.env

[oracle@rac1 ~]$

[oracle@rac1 ~]$ env | grep .xml

CONTEXT_FILE=/u01/apps/CLONE/fs1/inst/apps/CLONE_rac1/appl/admin/CLONE_rac1.xml

[oracle@rac1 ~]$

Application File System-2

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ . fs2.env

[oracle@rac1 ~]$

[oracle@rac1 ~]$ env | grep .xml

CONTEXT_FILE=/u01/apps/CLONE/fs2/inst/apps/CLONE_rac1/appl/admin/CLONE_rac1.xml

[oracle@rac1 ~]$

================================================================================================================

Default Port for SMTP & IMAP Mail Server:

Simple Mail Transfer Protocol (SMTP) is the standard protocol for sending emails across the Internet.

By default, the SMTP protocol works on Port 25

The Internet Message Access Protocol (IMAP) is a mail protocol used for accessing email on a remote web server from a local client.

By default, the IMAP protocol works on Port 143

================================================================================================================

Workflow Mailer & Alert log Files Location in EBS R12.2:

Bursting configuration with cloud email

Log files are created $APPLCSF/log/FNDCPGSC*.txt i.e. the log file for the Active process for Workflow Mailer Service and Agent Listener services.

To retrieve the last 2 log files for Workflow Mailer and Agent Listener services, run the following command:

applahc@rac1 :~$

applahc@rac1 :~$ cd $APPLCSF/log/

applahc@arc1 :/u01/APPS122/fs_ne/inst/DELL/logs/appl/conc/log$ ls -ltr FNDCPGSC*.txt

-rw-r--r-- 1 applahc dba 55341661 Jan 26 22:05 FNDCPGSC2000356.txt

-rw-r--r-- 1 applahc dba 11154907 Jan 26 22:05 FNDCPGSC2000348.txt

-rw-r--r-- 1 applahc dba 68203469 Jan 26 22:05 FNDCPGSC2000347.txt

-rw-r--r-- 1 applahc dba 9751185 Feb 6 11:02 FNDCPGSC2001151.txt

-rw-r--r-- 1 applahc dba 59392337 Feb 6 11:03 FNDCPGSC2001148.txt

-rw-r--r-- 1 applahc dba 83730180 Feb 6 11:03 FNDCPGSC2001146.txt

applahc@ rac1/logs/appl/conc/log$

applahc@rac1 /logs/appl/conc/log$ pwd

/u01/APPS122/fs_ne/inst/DELL/logs/appl/conc/log

applahc@rac1 /logs/appl/conc/log$ tail -f FNDCPGSC2001146.txt

================================================================================================================

Compiling Apps Schema from adadmin (Options: 3-->1)

Use number of worker's for compiling apps schema is 25:

[root@RUH-ERPDB01 ~]#

[root@RUH-ERPDB01 ~]# su - oraprod

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 8 15:28:16 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> show user

USER is "SYS"

SQL>

Query to find Total Count of Invalid Objects:

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

136

SQL>

Query to find User wise Total Count of Invalid Objects:

SQL>

SQL> column owner format A12

column object_type format A13

select owner,object_type,COUNT(*)

from dba_objects

where status='INVALID'

group by owner, object_type;

OWNER OBJECT_TYPE COUNT(*)

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

APPS PACKAGE BODY 131

XXHRCUSTOM PACKAGE BODY 2

APPS PROCEDURE 1

APPS VIEW 1

XXHRCUSTOM SYNONYM 1

SQL>

Compiling CUSTOM.pll

>find $AU_TOP/resource -iname CUSTOM.pl*

cd $AU_TOP/resource

mv $AU_TOP/resource/CUSTOM.pll $AU_TOP/resource/CUSTOM.pll_bkp

mv $AU_TOP/resource/CUSTOM.plx $AU_TOP/resource/CUSTOM.plx_bkp

frmcmp_batch module=CUSTOM.pll userid=apps/appsr12_2_9 output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Compiling Forms

frmcmp_batch module=XXTIMEPREPARE.fmb userid=apps/upgtestm7 output_file=XXTIMEPREPARE.fmx module_type=FORM batch=yes compile_all=special

Compiling Forms in OCI

frmcmp_batch module=CUSTOM.pll userid=apps/appsr12@devpdb output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Query to find User wise Object_Name and Object_Type of Invalid Objects:

SQL>

SQL> column owner format A11

column object_name format A31
column object_type format A15
column last_ddl_time format A10
spool invalids.lst
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
from dba_objects where status='INVALID'
order by owner;

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_T

WNER OBJECT_NAME OBJECT_TYPE LAST_DDL_T

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

APPS MSC_CL_PULL PACKAGE BODY 08-MAR-18

APPS MAL_TEST_ALERT1 PROCEDURE 08-MAR-18

APPS FV_FACTS_TRANSACTIONS PACKAGE BODY 08-MAR-18

APPS FV_FACTS_TBAL_TRANSACTIONS PACKAGE BODY 08-MAR-18

APPS FV_BUDGET_DISTRIBUTION_HDR_V VIEW 08-MAR-18

APPS ASO_OPP_QTE_PUB PACKAGE BODY 08-MAR-18

APPS FII_AP_INV_ON_HOLD_F_C PACKAGE BODY 03-AUG-15

APPS FII_AP_HOLD_DATA_F_C PACKAGE BODY 03-AUG-15

APPS FII_AP_INV_PAYMTS_F_C PACKAGE BODY 03-AUG-15

APPS FII_AP_INV_LINES_F_C PACKAGE BODY 03-AUG-15

APPS FII_AP_SCH_PAYMTS_F_C PACKAGE BODY 03-AUG-15

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_T

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

APPS FII_PA_COST_F_C PACKAGE BODY 03-AUG-15

APPS FII_PA_REVENUE_F_C PACKAGE BODY 03-AUG-15

APPS FII_PA_BUDGET_F_C PACKAGE BODY 03-AUG-15

APPS ISC_EDW_BOOK_SUM1_F_C PACKAGE BODY 02-APR-09

APPS OPI_EDW_COGS_F_C PACKAGE BODY 03-AUG-15

APPS OPI_EDW_OPMINV_DAILY_STAT_F_C PACKAGE BODY 02-APR-09

APPS ASO_SECURITY_INT PACKAGE BODY 08-MAR-18

APPS ASO_QUOTE_PUB_W PACKAGE BODY 08-MAR-18

APPS FV_SF133_NOYEAR PACKAGE BODY 08-MAR-18

APPS FV_SF133_ONEYEAR PACKAGE BODY 08-MAR-18

APPS FV_FACTS_TBAL_TRX PACKAGE BODY 08-MAR-18

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_T

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

APPS FV_FACTS_TRX_REGISTER PACKAGE BODY 08-MAR-18

APPS ITG_SYNCSUPPLIERINBOUND_PVT PACKAGE BODY 08-MAR-18

APPS AP_APXHRUPD_XMLP_PKG PACKAGE BODY 08-MAR-18

APPS XXX_HR_USER_HOOKS PACKAGE BODY 08-MAR-18

APPS FV_SLA_AR_PROCESSING_PKG PACKAGE BODY 08-MAR-18

APPS FV_SLA_CST_PROCESSING_PKG PACKAGE BODY 08-MAR-18

APPS FV_SLA_PO_PROCESSING_PKG PACKAGE BODY 08-MAR-18

APPS ASO_QUOTE_HEADERS_PVT PACKAGE BODY 08-MAR-18

APPS XXX_HR_GENERAL_PKG PACKAGE BODY 08-MAR-18

XXHRCUSTOM HR_CONTACT_REL_BK1 PACKAGE BODY 08-MAR-18

XXHRCUSTOM HR_PERSON_EXTRA_INFO_BK1 PACKAGE BODY 08-MAR-18

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_T

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

XXHRCUSTOM FV_BUDGET_DISTRIBUTION_HDR_V SYNONYM 08-MAR-18

136 rows selected.

SQL>

Now Compile Apps Schema using Application Server:

All the possible “INVALID” Objects becomes “VALID” by complinig apps Schema:

[root@RUH-ERPAP01 ~]#

[root@RUH-ERPAP01 ~]# su - applprod

[applprod@RUH-ERPAP01 scripts]$

[applprod@RUH-ERPAP01 scripts]$ adadmin

Redwood Shores, California, USA

Oracle Applications AD Administration

Version 12.0.0

NOTE: You may not use this utility for custom development

unless you have written permission from Oracle Corporation.

AD Administration Main Menu

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

1. Generate Applications Files menu

2. Maintain Applications Files menu

3. Compile/Reload Applications Database Entities menu

4. Maintain Applications Database Entities menu

5. Change Maintenance Mode

6. Exit AD Administration

Enter your choice [6] : 3

Compile/Reload Applications Database Entities

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

1. Compile APPS schema

2. Compile menu information

3. Compile flexfields

4. Reload JAR files to database

5. Return to Main Menu

SQL> show user

USER is "SYS"

SQL>

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

SQL>

SQL> column owner format A12

column object_type format A13
select owner,object_type,COUNT(*)
from dba_objects
where status='INVALID'
group by owner, object_type;

OWNER OBJECT_TYPE COUNT(*)

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

APPS PACKAGE BODY 29

XXHRCUSTOM PACKAGE BODY 2

APPS PROCEDURE 1

APPS VIEW 1

XXHRCUSTOM SYNONYM 1

SQL>

================================================================================================================

Apps Password Change:

Step 1 : shut Application and Services

Step 2 : cd $FND_TOP/bin

Step 3 : FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS apps2030

Step 4 : Run Autoconfig on DBTIER with new password (apps2030)

Step 5 : Run Auto Confif on Apps TIER with new password (apps2030)

Step 6 : start Apps Services with new pasword

======================

Step 1 :

[applprod@RUH-ERPAP01 ~]$ . APPSPROD_RUH-ERPAP01.env

[applprod@RUH-ERPAP01 ~]$

[applprod@RUH-ERPAP01 ~]$ adstpall.sh apps/apps

[applprod@RUH-ERPAP01 ~]$

[applprod@RUH-ERPAP01 ~]$ cd $FND_TOP/bin

Step 3 :

[applprod@RUH-ERPAP01 bin]$ FNDCPASS

[applprod@RUH-ERPAP01 bin]$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS apps2030

Step 4 :

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ ./adautocfg.sh

...

Updating rdbms version in Context file to db111

Updating rdbms type in Context file to 64 bits

Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

Step 5 :

[applprod@RUH-ERPAP01 ~]$ ./adautocfg.sh

...

Configuring OZF_TOP.......COMPLETED

Configuring IES_TOP.......COMPLETED

Configuring CSD_TOP.......COMPLETED

Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.

[applprod@RUH-ERPAP01 ~]$ sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Dec 28 09:43:39 2016

Enter user-name: apps

Enter password: (apps2030)

Connected to:

Step 6 :

[applprod@RUH-ERPAP01 ~]$ adstrtal.sh apps/apps2030

================================================================================================================

If you gave wrong apps password while running adpreclone.:

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ adpreclone.pl dbTier

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adpreclone Version 120.20.12010000.2

Enter the APPS User Password: WRONG PASSWORD FOR APPS

Running:

perl /u02/oracle/PROD/db/tech_st/11.1.0/appsutil/bin/adclone.pl java=/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/jre mode=stage stage=/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/PROD_ruh-erpdb01.xml showProgress

APPS Password :

Beginning database tier Stage - Sat Apr 8 10:05:53 2017

/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=false -Doracle.installer.oui_loc=/u02/oracle/PROD/db/tech_st/11.1.0/oui -classpath /u02/oracle/PROD/db/tech_st/11.1.0/lib/xmlparserv2.jar:/u02/oracle/PROD/db/tech_st/11.1.0/jdbc/lib/ojdbc6.jar:/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/java:/u02/oracle/PROD/db/tech_st/11.1.0/oui/jlib/OraInstaller.jar:/u02/oracle/PROD/db/tech_st/11.1.0/oui/jlib/ewt3.jar:/u02/oracle/PROD/db/tech_st/11.1.0/oui/jlib/share.jar:/u02/oracle/PROD/db/tech_st/11.1.0/oui/jlib/srvm.jar:/u02/oracle/PROD/db/tech_st/11.1.0/jlib/ojmisc.jar oracle.apps.ad.clone.StageDBTier -e /u02/oracle/PROD/db/tech_st/11.1.0/appsutil/PROD_ruh-erpdb01.xml -stage /u02/oracle/PROD/db/tech_st/11.1.0/appsutil/clone -tmp /tmp -method CUSTOM -showProgress

APPS Password :

Log file located at /u02/oracle/PROD/db/tech_st/11.1.0/appsutil/log/PROD_ruh-erpdb01/StageDBTier_04081005.log

AutoClone needs to start the database up to generate cloning information

- 50% completed RC-40000: Fatal: Unable to start up the database

ERROR while running Stage...

Sat Apr 8 10:06:19 2017

ERROR while running perl /u02/oracle/PROD/db/tech_st/11.1.0/appsutil/bin/adclone.pl java=/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/jre mode=stage stage=/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/u02/oracle/PROD/db/tech_st/11.1.0/appsutil/PROD_ruh-erpdb01.xml showProgress ...

Sat Apr 8 10:06:19 2017

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$

adpreclone log file messag

Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------

RC-40000: Unable to start up the database

Raised by oracle.apps.ad.clone.StageDatabase

RC-40000: Unable to start up the database

Raised by oracle.apps.ad.clone.StageDatabase

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ sqlplus / as sysdb

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Apr 8 10:14:50 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect apps/apps

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SQL>

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Apr 8 10:15:33 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select username, account_status from dba_users;

USERNAME ACCOUNT_STATUS

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

XXHRCUSTOM OPEN

PRP OPEN

EDR OPEN

......

SYS OPEN

USERNAME ACCOUNT_STATUS

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

APPS LOCKED(TIMED)

AD_MONITOR EXPIRED & LOCKED

SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED

SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED

ORACLE_OCM EXPIRED & LOCKED

XS$NULL EXPIRED & LOCKED

EM_MONITOR EXPIRED & LOCKED

MDDATA EXPIRED & LOCKED

SI_INFORMTN_SCHEMA EXPIRED & LOCKED

ANONYMOUS EXPIRED & LOCKED

XDB EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS

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

TSMSYS EXPIRED & LOCKED

DMSYS EXPIRED & LOCKED

DIP EXPIRED & LOCKED

245 rows selected.

SQL> alter user apps account unlock;

User altered.

SQL>

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ adpreclone.pl dbTier

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.

adpreclone Version 120.20.12010000.2

Enter the APPS User Password: CORRECT PASSWORD FOR APPS

Running:

APPS Password :

Beginning database tier Stage - Sat Apr 8 10:21:14 2017

APPS Password :

Log file located at /u02/oracle/PROD/db/tech_st/11.1.0/appsutil/log/PROD_ruh-erpdb01/StageDBTier_04081021.log

| 0% completed

Completed Stage...

Sat Apr 8 10:21:26 2017

[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$

==============================================================================================================

How to check Product Installation Status and patch level/code level oracle apps:

R11i/R12 : How to check if certain Oracle Applications product/module is implemented? (Doc ID 443699.1)

Below script to know the patch level/code level of oracle apps.

Method 1 Front end:

EBS à System Administrator à Oracle Applications Manager à Dashboard à Site Map à Maintenance à Codelevels Summary à “Search the product”

Method 2 Back end:

[oratest@RUH-ERPAT01 ~]$ sqlplus apps/apps

Database Query from Table 1, lists all the products of EBS:

SQL> select * from ad_trackable_entities --where abbreviation in('atg_pf','fnd','ad','txk','fwk','au','gl','per');

Database Query from Table 2:

SQL> col APP_SHORT_NAME for a14;

SELECT app_short_name, MAX(patch_level)

FROM apps.ad_patch_driver_minipks

GROUP BY app_short_name order by app_short_name;

APP_SHORT_NAME MAX(PATCH_LEVEL)

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

PSP R12.PSP.B.3

AS R12.AS.B.3

FTE R12.FTE.B

AK R12.AK.B.3

CHV R12.CHV.B.3

IEC R12.IEC.B.3

XDO R12.XDO.B.3

XNP R12.XNP.B.3

OKS R12.OKS.B.3

IPA R12.IPA.B.3

POS R12.POS.B.3

SQL>

Database Query from Table 3:

SQL>

SELECT fa.APPLICATION_SHORT_NAME

,fat.application_name
,fa.application_id
,fpi.patch_level
,decode(fpi.STATUS,'I','Installed', 'N','Inactive','S','Shared','Undetermined') STATUS
,fpi.LAST_UPDATE_DATE
FROM fnd_product_installations fpi
,fnd_application fa
,fnd_application_tl fat
WHERE fpi.application_id = fa.application_id
AND fat.application_id = fa.application_id
AND fat.LANGUAGE = 'US';

Method 3 Back end:

[oratest@RUH-ERPAT01 ~]$ sqlplus apps/apps

Database Table 1, lists all the products of EBS:

SQL> select * from ad_trackable_entities; --where abbreviation='fwk';

--> Product Installation Status, Version Info and Patch Level:

Below script gathers all the Application and Database Status

SQL>

SQL> @$AD_TOP/sql/adutconf.sql

[oratest@RUH-ERPAT01 ~]$

==============================================================================================================

Oracle Bursting Program for Pay Slip with attached .pdf in Email-->

-XML Pub. Admin--> Home--> Data Definition->Code (Executable Name): XXXSC_PAY_SLIP_XML

XXXSC_PAY_CASH_SLIP_XML

-Bursting Control File: template, Location of (.rtf) file for any update.

-Conc.Program: XSC Employee Salary Bank Payment Slip(XML)

applxsc@xscsc1zioclient010301:~$ echo $APPLPTMP

/u01/XSC/inst/apps/XSCRYD11_xscsc1zioclient010301/appltmp

applxsc@xscsc1zioclient010301:~$

applxsc@xscsc1zioclient010301:~$ ls -ltr $APPLTMP/Salary_Template_Design.rtf

-rw-r--r--  
1 applxsc  dba      1278143 Jul 23 11:13 /u01/XSC/fs1/inst/apps/XSCRYD11_xscsc1zioclient010301/appltmp/Salary_Cash_Template_Design.rtf

applxsc@xscsc1zioclient010301:~$

applxsc@xscsc1zioclient010301:~$ ls -ltr $APPLTMP/Salary_Cash_Template_Design.rtf

-rw-r--r--  
1 applxsc  dba      1278143 Jul 23 11:13 /u01/XSC/fs1/inst/apps/XSCRYD11_xscsc1zioclient010301/appltmp/Salary_Cash_Template_Design.rtf

applxsc@xscsc1zioclient010301:~$

--Please check the below standard query to get all bursting file in system.

SELECT *

FROM (SELECT DSDEFINITIONSVLEO.APPLICATION_SHORT_NAME,

DSDEFINITIONSVLEO.DATA_SOURCE_CODE,

DSDEFINITIONSVLEO.DATA_SOURCE_STATUS,

DSDEFINITIONSVLEO.START_DATE,

DSDEFINITIONSVLEO.END_DATE,

DSDEFINITIONSVLEO.DATA_SOURCE_NAME,

DSDEFINITIONSVLEO.DESCRIPTION,

DSDEFINITIONSVLEO.CREATED_BY,

NULL AS SELECTEDFLAG,

(SELECT APPLICATION_NAME

FROM FND_APPLICATION_VL

WHERE APPLICATION_SHORT_NAME =

DSDEFINITIONSVLEO.APPLICATION_SHORT_NAME)

APPLICATION_NAME,

(SELECT 'Active'

FROM DUAL

WHERE ( DSDEFINITIONSVLEO.START_DATE <= SYSDATE

AND DSDEFINITIONSVLEO.END_DATE >= SYSDATE)

OR (DSDEFINITIONSVLEO.END_DATE IS NULL))

|| (SELECT 'Inactive'

FROM DUAL

WHERE ( DSDEFINITIONSVLEO.START_DATE > SYSDATE

AND DSDEFINITIONSVLEO.END_DATE < SYSDATE))

STATUS,

XDOLOBSEO1.FILE_NAME SCHEMA_FILE_NAME,

XDOLOBSEO2.FILE_NAME SAMPLE_FILE_NAME,

(CASE

WHEN DSDEFINITIONSVLEO.DATA_SOURCE_STATUS = 'L'

OR DSDEFINITIONSVLEO.CREATED_BY = '1'

OR DSDEFINITIONSVLEO.CREATED_BY = '2'

THEN

'UpdateDisabled'

ELSE

'UpdateEnabled'

END)

AS UPDATE_SWITCHER,

XDOLOBSEO3.FILE_NAME DATA_TEMPLATE_FILE_NAME,

XDOLOBSEO4.FILE_NAME BURSTING_FILE_NAME

FROM XDO_DS_DEFINITIONS_VL DSDEFINITIONSVLEO,

XDO_LOBS XDOLOBSEO1,

XDO_LOBS XDOLOBSEO2,

XDO_LOBS XDOLOBSEO3,

XDO_LOBS XDOLOBSEO4

WHERE XDOLOBSEO1.APPLICATION_SHORT_NAME(+) =

DSDEFINITIONSVLEO.APPLICATION_SHORT_NAME

AND XDOLOBSEO1.LOB_CODE(+) =

DSDEFINITIONSVLEO.DATA_SOURCE_CODE

AND XDOLOBSEO1.LOB_TYPE(+) = 'XML_SCHEMA'

AND XDOLOBSEO2.APPLICATION_SHORT_NAME(+) =

DSDEFINITIONSVLEO.APPLICATION_SHORT_NAME

AND XDOLOBSEO2.LOB_CODE(+) =

DSDEFINITIONSVLEO.DATA_SOURCE_CODE

AND XDOLOBSEO2.LOB_TYPE(+) = 'XML_SAMPLE'

AND XDOLOBSEO3.APPLICATION_SHORT_NAME(+) =

DSDEFINITIONSVLEO.APPLICATION_SHORT_NAME

AND XDOLOBSEO3.LOB_CODE(+) =

DSDEFINITIONSVLEO.DATA_SOURCE_CODE

AND XDOLOBSEO3.LOB_TYPE(+) = 'DATA_TEMPLATE'

AND XDOLOBSEO4.APPLICATION_SHORT_NAME(+) =

DSDEFINITIONSVLEO.APPLICATION_SHORT_NAME

AND XDOLOBSEO4.LOB_CODE(+) =

DSDEFINITIONSVLEO.DATA_SOURCE_CODE

AND XDOLOBSEO4.LOB_TYPE(+) = 'BURSTING_FILE') QRSLT

-- WHERE (DATA_SOURCE_CODE LIKE :1 AND APPLICATION_SHORT_NAME LIKE :2)

WHERE STATUS = 'Active' --AND DATA_SOURCE_CODE = 'XXX_HR_EMP_SAL_TRANSFER'

AND BURSTING_FILE_NAME IS NOT NULL

==============================================================================================================

Application tar backup in solaris 11: 

root@scapps:~# cat /apptarbkp

root@scapps:~# tar Ecf - /u01/SC/fs2/EBSapps | gzip -3 - > /u01/BKP/EBSapps.tar.gz

for untar application:

root@scapps:~# gunzip EBSapps.tar.gz

root@scapps:~# tar -xvf EBSapps.tar

==============================================================================================================

Data patch query to update plsql function after applying PSU,SRU,DBBP

set linesize 200;

col DESCRIPTION for a80;

col ACTION_TIME for a30;

col PATCH_DESCRIPTOR for a45;

col BUNDLE_SERIES for a15;

col VERSION for a10;

select * from dba_registry_sqlpatch;

SQL> select PATCH_ID,DESCRIPTION,BUNDLE_SERIES,VERSION,ACTION,STATUS,ACTION_TIME from dba_registry_sqlpatch;

PATCH_ID DESCRIPTION BUNDLE_SERIES VERSION ACTION STATUS ACTION_TIME

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

18793246 12.1.0.2 APPLY SUCCESS 30-JUL-20 05.39.32.292292 AM

28507324 MSC_ST_REGIONS_F1 FCET FAILS ON EMPTY TBL WITH ORA-01410 INVALID ROWID 12.1.0.2 APPLY SUCCESS 30-JUL-20 05.39.32.298368 AM

29943372 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.190716 FOR BUGS 20476776 22229581 12.1.0.2 APPLY SUCCESS 30-JUL-20 05.39.35.923262 AM

28507324 MSC_ST_REGIONS_F1 FCET FAILS ON EMPTY TBL WITH ORA-01410 INVALID ROWID 12.1.0.2 ROLLBACK SUCCESS 15-NOV-20 05.15.42.455807 PM

29943372 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.190716 FOR BUGS 20476776 22229581 12.1.0.2 ROLLBACK SUCCESS 15-NOV-20 05.15.46.222967 PM

30691015 DATABASE BUNDLE PATCH 12.1.0.2.200414 DBBP 12.1.0.2 APPLY WITH ERRORS 15-NOV-20 05.15.49.033356 PM

28507324 MSC_ST_REGIONS_F1 FCET FAILS ON EMPTY TBL WITH ORA-01410 INVALID ROWID 12.1.0.2 APPLY SUCCESS 15-NOV-20 05.15.49.769590 PM

31191635 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.200414 FOR BUGS 20476776 22229581 12.1.0.2 APPLY SUCCESS 15-NOV-20 05.15.53.556999 PM

30691015 DATABASE BUNDLE PATCH 12.1.0.2.200414 DBBP 12.1.0.2 APPLY SUCCESS 15-NOV-20 11.59.32.777459 PM

20 rows selected.

SQL>

==============================================================================================================

Thanks for Reading

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	12102 RAC INSTAL	R1229 M7 Clone
DNS in Linux 7	WIN-LINUX MIGRATE	RMAN DB CLONE	RAC DataGuard	Pluggable DB Clone
lpadmin cups	ASMCMD ASM Migrt	RMAN ASM Migrt	RAC DB Clone	appsutil for DB
	11204 M-Upgrade		RAC - RAC Clone	JDK JRE upgrade
	12102 M-Upgrade		12C-19C RAC UPGRADE	Add EBS Node
	Flashback DB			ADOP in R12.2.9
				12102 EBS UPGRADE
Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics

Thursday, March 8, 2018

Oracle EBS Basics

No comments:

Post a Comment