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 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
-----------------------------------------
Login with direct java access:Export
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
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.
-----------------------------------------
================================================================================================================
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]$
Log in to Sysadmin Responsibility
Go to ->Concurrent -> Manager -> Administer (Actual(0) =/= target(1))
Now Check ICM log(location):
Log in to Sysadmin Responsibility
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):
Log in to Sysadmin Responsibility
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 ~]$ 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 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');
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 NODE_NAME for a25;
col HOW_MANY_USER_SESSIONS for a35;
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
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 or
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:
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
Copyright (c) 1982, 2008, Oracle. All
rights reserved.
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>
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
Copyright (c) 2002 Oracle Corporation
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(*)
----------
34
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(*)
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
Copyright (c) 1982, 2005, Oracle. All
rights reserved.
Enter user-name: apps
Enter
password: (apps2030)
Connected to:
Step 6 :
[applprod@RUH-ERPAP01 ~]$ adstrtal.sh apps/apps2030
================================================================================================================
Version 12.0.
If you gave wrong apps password while running
adpreclone.:
[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$
[oraprod@RUH-ERPDB01 PROD_ruh-erpdb01]$ adpreclone.pl dbTier
Copyright (c) 2002 Oracle Corporation
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
Copyright (c) 1982, 2008, Oracle. All
rights reserved.
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
Copyright (c) 1982, 2008, Oracle. All
rights reserved.
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
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications Rapid Clone
adpreclone Version 120.20.12010000.2
Enter the APPS User Password: CORRECT 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:21:14 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_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)
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:~$
==============================================================================================================
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
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 |
| R1229 M7 Clone | ||||
| RAC DataGuard | Pluggable DB Clone | |||
| appsutil for DB | ||||
| JDK JRE upgrade | ||||
| Add EBS Node | ||||
No comments:
Post a Comment