Learn AppsDBA & RAC

Script to find user having particular responsibility

2015-07-26T00:31:00.001-07:00

SELECT fu.user_id, fu.user_name, fu.email_address
FROM fnd_user_resp_groups_direct furg,
fnd_user fu,
fnd_responsibility_tl fr
WHERE UPPER (fr.responsibility_name) = UPPER ('&Enter_Resp_Name')
AND fr.responsibility_id = furg.responsibility_id
AND furg.user_id = fu.user_id
AND furg.end_date IS NULL
AND fu.end_date IS NULL
AND fr.language = USERENV ('LANG')
ORDER BY fu.user_name;

Script to find scheduled concurrent requests

2015-07-20T16:44:00.002-07:00

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

Sample output:

REQUEST_ID

CONC_PROG

REQUESTOR

ARGUMENTS

NEXT_RUN

LAST_RUN

ON_HOLD

INCREMENT_DATES

SCHEDULE_TYPE

SCHEDULE

START_DATE

END_DATE

CLASS_INFO

7092598

Gather Schema Statistics

SYSADMIN

ALL, 100, , NOBACKUP, , LASTRUN, GATHER AUTO, , N

20-JUL-15

Periodic

Repeat every 1 days from the start of the prior run

08-MAY-15

1:D:S

7092617

Gather Schema Statistics

SYSADMIN

XXCMP, 100, , NOBACKUP, , LASTRUN, GATHER AUTO, , N

20-JUL-15

Periodic

Repeat every 1 days from the start of the prior run

11-MAY-15

1:D:S

7087809

Gather Schema Statistics

SYSADMIN

QP, 100, , NOBACKUP, , LASTRUN, GATHER, , Y

25-JUL-15

20-JUL-15

Periodic

Repeat every 7 days from the start of the prior run

28-MAR-15

7:D:S

7087957

Gather Schema Statistics

SYSADMIN

ONT, 100, , NOBACKUP, , LASTRUN, GATHER, , Y

25-JUL-15

20-JUL-15

Periodic

Repeat every 7 days from the start of the prior run

28-MAR-15

7:D:S

7100121

Gather Schema Statistics

SYSADMIN

ALL, , , NOBACKUP, , LASTRUN, GATHER, , Y

26-JUL-15

20-JUL-15

On Specific Days

Days of week: Su

26-JUL-15

000000000000000000000000000000001000000

Scrpt to find products installed information in oracle applications

2015-07-17T13:17:00.002-07:00

select substr(FND_APPLICATION.APPLICATION_ID,1,10) Appl_id,
substr(FND_APPLICATION.APPLICATION_SHORT_NAME,1,10) Shortname,
substr(FND_APPLICATION_TL.APPLICATION_NAME,1,40) Longname,
substr(FND_APPLICATION_TL.LANGUAGE,1,4) Language,
substr(PATCH_LEVEL,1,15) Patchlevel,
substr(STATUS,1,5) Inst
from FND_APPLICATION,
FND_APPLICATION_TL,
FND_PRODUCT_INSTALLATIONS
WHERE FND_APPLICATION.APPLICATION_ID = FND_APPLICATION_TL.APPLICATION_ID
and FND_APPLICATION.APPLICATION_ID =
FND_PRODUCT_INSTALLATIONS.APPLICATION_ID(+)
and language = 'US'
order by FND_APPLICATION.APPLICATION_SHORT_NAME, FND_APPLICATION_TL.LANGUAGE;

Sample output (order by INST)

































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































         APPL_ID   SHORTNAME   LONGNAME   LANGUAGE   PATCHLEVEL   INST  
  869   ASP   Oracle Sales for Handhelds   US   R12.ASP.B.3   I  
  178   ICX   Oracle iProcurement   US   R12.ICX.B.3   I  
  697   ASO   Order Capture   US   R12.ASO.B.3   I  
  660   ONT   Order Management   US   R12.ONT.B.3   I  
  200   SQLAP   Payables   US   R12.AP.B.3   I  
  673   IBY   Payments   US   R12.IBY.B.3   I  
  555   GMF   Process Manufacturing Financials   US   R12.GMF.B.3   I  
  558   PMI   Process Manufacturing Intelligence   US   R12.PMI.B.3   I  
  551   GMI   Process Manufacturing Inventory   US   R12.GMI.B.3   I  
  556   GML   Process Manufacturing Logistics   US   R12.GML.B.3   I  
  553   GME   Process Manufacturing Process Execution   US   R12.GME.B.3   I  
  554   GMP   Process Manufacturing Process Planning   US   R12.GMP.B.3   I  
  552   GMD   Process Manufacturing Product Developmen   US   R12.GMD.B.3   I  
  557   GR   Process Manufacturing Regulatory Managem   US   R12.GR.B.3   I  
  550   GMA   Process Manufacturing Systems   US   R12.GMA.B.3   I  
  455   ENI   Product Intelligence   US   R12.ENI.B.3   I  
  1292   PJI   Project Intelligence   US   R12.PJI.B.3   I  
  712   PJM   Project Manufacturing   US   R12.PJM.B.3   I  
  275   PA   Projects   US   R12.PA.B.3   I  
  240   PN   Property Manager   US   R12.PN.B.3   I  
  8401   PSB   Public Sector Budgeting   US   R12.PSB.B.3   I  
  8450   PSA   Public Sector Financials   US   R12.PSA.B.3   I  
  8400   IGI   Public Sector Financials International   US   R12.IGI.B.3   I  
  8302   PQH   Public Sector HR   US   R12.PQH.B.3   I  
  201   PO   Purchasing   US   R12.PO.B.3   I  
  452   POA   Purchasing Intelligence   US   R12.POA.B.3   I  
  250   QA   Quality   US   R12.QA.B.3   I  
  880   QOT   Quoting   US   R12.QOT.B.3   I  
  222   AR   Receivables   US   R12.AR.B.3   I  
  265   FRM   Report Manager   US   R12.FRM.B.3   I  
  804   SSP   SSP   US   R12.SSP.B.3   I  
  280   ASN   Sales   US   R12.ASN.B.3   I  
  279   AS   Sales Foundation   US   R12.AS.B.3   I  
  676   BIL   Sales Intelligence   US   R12.BIL.B.3   I  
  544   ASL   Sales Offline   US   R12.ASL.B.3   I  
  522   ASF   Sales Online   US   R12.ASF.B.3   I  
  519   IES   Scripting   US   R12.IES.B.3   I  
  661   QP   Advanced Pricing   US   R12.QP.B.3   I  
  724   MSC   Advanced Supply Chain Planning   US   R12.MSC.B.3   I  
  160   ALR   Alert   US   R12.ALR.B.3   I  
  190   AZ   Application Implementation   US   R12.AZ.B.3   I  
  0   FND   Application Object Library   US   R12.FND.B.3   I  
  168   RG   Application Report Generator   US   R12.RG.B.3   I  
  191   BIS   Applications BIS   US   R12.BIS.B.3   I  
  873   CSE   Asset Tracking   US   R12.CSE.B.3   I  
  140   OFA   Assets   US   R12.FA.B.3   I  
  702   BOM   Bills of Material   US   R12.BOM.C.3   I  
  690   JTF   CRM Foundation   US   R12.JTF.B.3   I  
  689   ASG   CRM Gateway for Mobile Devices   US   R12.ASG.B.3   I  
  705   CRP   Capacity   US   R12.CRP.B.3   I  
  260   CE   Cash Management   US   R12.CE.B.3   I  
  866   CUG   Citizen Interaction Center   US   R12.CUG.B.3   I  
  601   AK   Common Modules-AK   US   R12.AK.B.3   I  
  708   CZ   Configurator   US   R12.CZ.B.3   I  
  723   MSO   Constraint Based Optimization   US   R12.MSO.B.3   I  
  549   IBC   Content Manager   US   R12.IBC.B.3   I  
  510   OKC   Contracts Core   US   R12.OKC.B.3   I  
  524   OKX   Contracts Integration   US   R12.OKX.B.3   I  
  870   OKI   Contracts Intelligence   US   R12.OKI.B.3   I  
  511   CSC   Customer Care   US   R12.CSC.B.3   I  
  518   BIC   Customer Intelligence (obsolete)   US   R12.BIC.B.3   I  
  722   MSD   Demand Planning   US   R12.MSD.B.3   I  
  512   CSD   Depot Repair   US   R12.CSD.B.3   I  
  235   ZX   E-Business Tax   US   R12.ZX.B.3   I  
  709   EDR   E-Records   US   R12.EDR.B.3   I  
  703   ENG   Engineering   US   R12.ENG.C.3   I  
  8901   FV   Federal Financials   US   R12.FV.B.3   I  
  513   CSF   Field Service   US   R12.CSF.B.3   I  
  450   FII   Financial Intelligence   US   R12.FII.B.3   I  
  101   SQLGL   General Ledger   US   R12.GL.B.3   I  
  600   AX   Global Accounting Engine   US   11i.AX.J   I  
  283   CN   Incentive Compensation   US   R12.CN.B.3   I  
  542   CSI   Install Base   US   R12.CSI.B.3   I  
  539   IEO   Interaction Center Technology   US   R12.IEO.B.3   I  
  170   CS   Service   US   R12.CS.B.3   I  
  515   OKS   Service Contracts   US   R12.OKS.B.3   I  
  862   BIV   Service Intelligence   US   R12.BIV.B.3   I  
  665   WSH   Shipping Execution   US   R12.WSH.B.3   I  
  202   CHV   Supplier Scheduling   US   R12.CHV.B.3   I  
  454   ISC   Supply Chain Intelligence   US   R12.ISC.B.3   I  
  1   SYSADMIN   System Administration   US     I  
  521   AST   TeleSales   US   R12.AST.B.3   I  
  8301   GHR   US Federal Human Resources   US   R12.GHR.B.3   I  
  696   IEU   Universal Work Queue   US   R12.IEU.B.3   I  
  385   WMS   Warehouse Management   US   R12.WMS.B.3   I  
  231   BNE   Web Applications Desktop Integrator   US   R12.BNE.B.4   I  
  706   WIP   Work in Process   US   R12.WIP.B.3   I  
  174   ECX   XML Gateway   US   R12.ECX.B.3   I  
  603   XDO   XML Publisher   US   R12.XDO.B.3   I  
  175   EC   e-Commerce Gateway   US   R12.EC.B.3   I  
  205   IA   iAssets   US   R12.IA.B.3   I  
  177   POS   iSupplier Portal   US   R12.POS.B.3   I  
  401   INV   Inventory   US   R12.INV.B.3   I  
  530   AMS   Marketing   US   R12.AMS.B.3   I  
  517   BIM   Marketing Intelligence   US   R12.BIM.B.3   I  
  704   MRP   Master Scheduling/MRP   US   R12.MRP.B.3   I  
  874   JTM   Mobile Application Foundation   US   R12.JTM.B.3   I  
  405   MWA   Mobile Applications   US   R12.MWA.B.3   I  
  534   XNP   Number Portability   US   R12.XNP.B.3   I  
  451   OPI   Operations Intelligence   US   R12.OPI.B.3   I  
  278   IZU   Oracle E-Business Suite Diagnostics   US   R12.IZU.B.3   I  
  545   IEC   Advanced Outbound Telephony   US   R12.IEC.B.3   N  
  431   EGO   Advanced Product Catalog   US   R12.EGO.C.3   N  
  203   AME   Approvals Management   US   R12.AME.B.3   N  
  7000   JA   Asia/Pacific Localizations   US   R12.JA.B.3   N  
  663   VEA   Automotive   US   R12.VEA.B.3   N  
  271   BSC   Balanced Scorecard   US   R12.BSC.B.3   N  
  674   IBP   Bill Presentment & Payment   US     N  
  430   DDD   CADView-3D   US   R12.DDD.C.3   N  
  875   JTS   CRM Self Service Administration   US   11i.JTS.B   N  
  8724   CUF   Capital Resource Logistics - Financials   US     N  
  8721   IPA   Capital Resource Logistics - Projects   US   R12.IPA.B.3   N  
  695   IEX   Collections   US   R12.IEX.B.3   N  
  867   AHL   Complex Maintenance Repair and Overhaul   US   R12.AHL.B.3   N  
  8407   IGC   Contract Commitment   US     N  
  879   IMC   Customers Online   US   R12.IMC.B.3   N  
  9003   DDR   Demand Signal Repository   US   R12.DDR.B.3   N  
  429   DNA   Development   US   R12.DNA.C.3   N  
  432   DOM   Document Managment and Collaboration   US   R12.DOM.B.3   N  
  426   EAM   Enterprise Asset Management   US   R12.EAM.B.3   N  
  274   FEM   Enterprise Performance Foundation   US   R12.FEM.B.3   N  
  210   ZPB   Enterprise Planning and Budgeting   US   R12.ZPB.B.3   N  
  7002   JE   European Localizations   US   R12.JE.B.3   N  
  298   POM   Exchange   US   R12.POM.B.3   N  
  868   CSL   Field Service/Laptop   US   R12.CSL.B.3   N  
  883   CSM   Field Service/Palm   US   R12.CSM.B.3   N  
  8406   IGF   Financial Aid   US     N  
  505   ZFA   Financial Analyzer   US     N  
  266   GCS   Financial Consolidation Hub   US   R12.GCS.B.3   N  
  435   FUN   Financials Common Modules   US   R12.FUN.B.3   N  
  8402   GMS   Grants Accounting   US   R12.GMS.I   N  
  8404   IGW   Grants Proposal   US   R12.IGW.B.3   N  
  438   ITA   Information Technology Audit   US   R12.ITA.B.3   N  
  699   IEB   Interaction Blending   US   R12.IEB.B.3   N  
  242   AMW   Internal Controls Manager   US   R12.AMW.B.3   N  
  230   ITG   Internet Procurement Enterprise Connecto   US   R12.ITG.B.3   N  
  410   WSM   Shop Floor Management   US   R12.WSM.B.3   N  
  718   RRS   Site Management   US   R12.RRS.B.3   N  
  396   PON   Sourcing   US   R12.PON.B.3   N  
  8405   IGS   Student System   US   R12.IGS.A   N  
  778   JMF   Supply Chain Localizations   US   R12.JMF.B.3   N  
  701   CLN   Supply Chain Trading Connector for Roset   US   R12.CLN.C.3   N  
  808   HXT   Time and Labor   US   R12.HXT.B.3   N  
  809   HXC   Time and Labor Engine   US   11i.HXC.C   N  
  682   OZF   Trade Management   US   R12.OZF.B.3   N  
  508   FTP   Transfer Pricing   US   R12.FTP.B.3   N  
  716   FTE   Transportation Execution   US   R12.FTE.B   N  
  390   MST   Transportation Planning   US   R12.MST.B   N  
  185   XTR   Treasury   US   R12.XTR.B.3   N  
  821   IRC   iRecruitment   US   R12.IRC.B.3   N  
  672   IBU   iSupport   US   R12.IBU.B.3   N  
  726   MSR   Inventory Optimization   US   R12.MSR.B.3   N  
  7004   JL   Latin America Localizations   US   R12.JL.B.3   N  
  810   OTA   Learning Management   US   R12.OTA.B.3   N  
  540   OKL   Lease and Finance Management   US   R12.OKL.B.3   N  
  204   XLE   Legal Entity Configurator   US   R12.XLE.B.3   N  
  206   LNS   Loans   US   R12.LNS.B.3   N  
  560   GMO   Manufacturing Execution System for Proce   US   R12.GMO.B.3   N  
  388   WPS   Manufacturing Scheduling   US   R12.WPS.B.3   N  
  8727   CUS   Network Logistics   US     N  
  8722   CUI   Network Logistics - Inventory   US     N  
  8723   CUP   Network Logistics - Purchasing   US     N  
  667   QPR   Oracle Deal Management   US   R12.QPR.C.3   N  
  207   IPM   Oracle Imaging Process Management   US     N  
  9004   INL   Oracle Landed Cost Management   US   R12.INL.B.3   N  
  9001   MTH   Oracle Manufacturing Operations Center   US   R12.MTH.B.3   N  
  9000   DPP   Oracle Price Protection   US   R12.DPP.B.3   N  
  507   PFT   Oracle Profitability Manager   US   R12.PFT.B.3   N  
  881   XNB   Oracle Telecommunications Billing Integr   US   R12.XNB.B.3   N  
  666   IBW   Oracle Web Analytics   US   R12.IBW.B.3   N  
  300   OE   Order Entry   US   R12.OE.B.3   N  
  691   PV   Partner Management   US   R12.PV.B.3   N  
  777   OKE   Project Contracts   US   R12.OKE.H   N  
  440   FPA   Project Portfolio Analysis   US   R12.FPA.B.3   N  
  694   PRP   Proposals   US   R12.PRP.B.3   N  
  8303   PQP   Public Sector Payroll   US   R12.PQP.B.3   N  
  7003   JG   Regional Localizations   US   R12.JG.B.3   N  
  662   RLM   Release Management   US   R12.RLM.B.3   N  
  186   QRM   Risk Management   US   R12.QRM.B.3   N  
  506   ZSA   Sales Analyzer   US     N  
  698   CSR   Scheduler   US   R12.CSR.B.3   N  
  805   BEN   Advanced Benefits   US   R12.BEN.B.3   S  
  535   XDP   Provisioning   US   R12.XDP.B.3   S  
  50   AD   Applications DBA   US   R12.AD.B.3   S  
  60   SHT   Applications Shared Technology   US   R12.SHT.B.3   S  
  8731   CUA   Capital Resource Logistics - Assets   US     S  
  803   DT   DateTrack   US   R12.DT.B.3   S  
  680   IEM   Email Center   US   R12.IEM.B.3   S  
  802   FF   FastFormula   US   R12.FF.B.3   S  
  714   FLM   Flow Manufacturing   US   R12.FLM.B.3   S  
  800   PER   Human Resources   US   R12.PER.B.3   S  
  453   HRI   Human Resources Intelligence   US   R12.HRI.B.3   S  
  677   BIX   Interaction Center Intelligence   US   R12.BIX.B.3   S  
  523   CSP   Spares Management   US   R12.CSP.B.3   S  
  602   XLA   Subledger Accounting   US   R12.XLA.B.3   S  
  172   CCT   Telephony Manager   US   R12.CCT.B.3   S  
  671   IBE   iStore   US   R12.IBE.B.3   S  
  8403   PSP   Labor Distribution   US   R12.PSP.B.3   S  
  700   MFG   Manufacturing   US   R12.MFG.B.3   S  
  520   AMV   Marketing Encyclopedia System   US   R12.AMV.B.3   S  
  801   PAY   Payroll   US   R12.PAY.B.3   S  
  3   AU   Application Utilities   US     S  
  41   PTX   Patch Tracking System   US      
  20003   XPIP   Pip Application   US      
  20023   XXPPS   Custom Applications   US      
  548   IEP   Predictive   US      
  559   GMW   Process Manufacturing Portal   US      
  420   IPD   Product Development (obsolete)   US      
  20139   PSR   Public Sector Receivables   US      
  437   RCM   Regulatory Capital Manager (obsolete)   US      
  710   RLA   Release Management Integration Kit (Obso   US      
  719   RRC   Retail Core   US      
  8726   CUC   Revenue Accounting   US      
  509   RMG   Risk Manager   US      
  877   OKT   Royalty Management   US      
  270   EAA   SEM Exchange (obsolete)   US      
  501   AN   Sales Analysis   US      
  532   XNC   Sales for Communications (Obsolete)   US      
  13017   CLA   APAC Consulting Localizations   US      
  272   ABM   Activity Based Management (Obsolete)   US      
  725   RHX   Advanced Planning Foundation(obsolete)   US      
  20043   BPA   Automate   US      
  711   VEH   Automotive Integration Kit (Obsolete)   US      
  538   FPT   Banking Center (obsolete)   US      
  543   CUE   Billing Connect (obsolete)   US      
  502   PBR   Budgeting and Planning   US      
  20160   OKC_REP_TX   Build/syncronize Contracts Repository Te   US      
  20090   CPGC   CPG - CDOA   US      
  20180   CSN   Call Center   US      
  547   IET   Call Center Connectors   US      
  3002   CTB   Clinical Transaction Base   US      
  679   BIN   Communications Intelligence   US      
  876   OKP   Contracts for Procurement (Obsolete)   US      
  541   OKR   Contracts for Rights (Obsolete)   US      
  871   OKO   Contracts for Sales (Obsolete)   US      
  865   OKB   Contracts for Subscriptions (Obsolete)   US      
  516   ME   Controlled Availability Product(Obsolete   US      
  707   CST   Cost Management   US      
  525   ODQ   Data Query   US      
  878   IAM   Digital Asset Management   US      
  13014   CLE   EMEA Consulting Localizations   US      
  40   EMS   Environment Management System   US      
  882   AMF   Fulfillment Services (Obsolete)   US      
  747   GNI   Genealogy Intelligence   US      
  3004   HCA   Healthcare   US      
  3003   HCP   Healthcare Intelligence   US      
  3005   HCT   Healthcare Terminology Server   US      
  864   AHM   Hosting Manager(Obsolete)   US      
  546   IEV   IVR Integrator   US      
  872   XNI   Install Base Intelligence (Obsolete)   US      
  536   XNA   Service Assurance for Communications   US      
  533   XNS   Service for Communications (obsolete)   US      
  514   CSS   Support (obsolete)   US      
  863   BIY   Systems Intelligence   US      
  20200   DEM01   Team 01 Order Entry Demo   US      
  537   XNT   TeleBusiness for Telecom/Utilities   US      
  681   OZP   Trade Planning (Obsolete)   US      
  20114   OUC   University Curriculum   US      
  436   BLC   Utility Billing   US      
  273   EVM   Value Based Management   US      
  678   BIE   eCommerce Intelligence   US      
  675   IBT   iAuction   US      
  683   OZS   iClaims (Obsolete)   US      
  3000   HCC   iHCConnect   US      
  3001   HCN   iHCIntegrate   US      
  670   IBA   iMarketing (Obsolete)   US      
  861   IMT   iMeeting (obsolete)   US      
  415   ISX   iSettlement   US      
  20159   TEST   test   US      
  13016   CLJ   Japan Consulting Localizations   US      
  13015   CLL   LAD Consulting Localizations   US      
  407   WMA   Manufacturing Mobile Applications   US      
  531   XNM   Marketing for Communications (Obsolete)   US      
  8725   CUR   Mass Market Receivables for Comms   US      
  860   MIV   Media Interactive   US      
  403   MIA   Mobile Applications for Inventory Manage   US      
  425   MQA   Mobile Quality Applications   US      
  8729   CUN   Network Logistics - NATS (obsolete)   US      
  1776   DUMMY_GMO   Obsolete Process Operations   US      
  20161   OKC_REP_TX   Optimize Contracts Repository Text index   US      
  99   OAM   Oracle Applications Manager   US      
  3006   CDR   Oracle Clinical Data Repository   US

Script to find session details for a given request id

2015-07-09T14:46:00.004-07:00

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id = d.audsid
AND a.request_id =&request_id;

command to cancel a request at database level

2015-07-09T14:46:00.000-07:00

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&n

command to monitor copy (cp command) progress

2015-06-25T16:57:00.000-07:00

command to monitor copy (cp command) progress

while true; do sourcesize=1020 destdir=/OraStage/PROD_HOT_BACKUP_STBY copyprogress="$(export | du -sh $destdir | awk '{print $1}' | sed 's/[^0-9.]*//g')"; echo "scale=3; $copyprogress / $sourcesize*100" | bc | xargs echo -n ; echo % completed; sleep 20;done

Sample output:
80.000% completed
80.200% completed
80.400% completed
80.700% completed
81.000% completed
81.200% completed
81.500% completed
81.800% completed
82.000% completed
82.300% completed
82.600% completed
82.800% completed
83.100% completed
83.300% completed
83.600% completed
83.800% completed
84.000% completed
84.200% completed

85.500% completed

85.900% completed
86.200% completed

Advanced Query Tuning

2015-06-23T22:47:00.001-07:00

These referenced steps provide the guidelines for dealing with query tuning problems.

Queries can run slowly for any number of reasons

1. Identify the query which causing the problem

We can identify the query in various methods (e.g OEM, STATSPACK, sql query)

1. a) Find resource hungry SQL interactively as follows

SELECT address, SUBSTR(sql_text,1,20) Text, buffer_gets, executions,
           buffer_gets/executions AVG
    FROM   v$sqlarea
    WHERE  executions  > 0
    AND    buffer_gets > 100000
    ORDER BY 5;

Note: the 'buffer_gets' value of > 100000 needs to be varied for the individual system being tuned

Sample OUTPUT:

ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG

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

000000010514C048 BEGIN DBMS_SCHEDULER 4135073 9 459452.556

000000016641A1E8 DELETE FROM PLANT_SC 6513536 14 465252.571

000000017D292A08 call Sync_Notificati 34439016 65 529831.015

00000000EA47E060 BEGIN DBMS_SCHEDULER 15370793 29 530027.345

000000013872CB68 SELECT T.COI, 67165717 78 861098.936

00000000DCDEB490 /* MV_REFRESH (INS) 15187589 15 1012505.93

00000000EA38CD78 MERGE INTO PLANT_SCH 17829484 17 1048793.18

0000000105821DF0 call xxsap.Dashboard 25739712 17 1514100.71

000000017D8952B0 SELECT T.COI, 28207138 14 2014795.57

0000000157FD77B8 select st.coi, 57612995 28 2057606.96

00000000EA233F18 select st.coi, p.cci 4117702 2 2058851

The ADDRESS value retrieved above can then be used to lookup the whole statement in the v$sqltext view:

SELECT sql_text FROM v$sqltext WHERE address = '00000000EA233F18' ORDER BY piece;

Sample OUTPUT

SQL_TEXT

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

select st.coi, p.ccid, sts.code AS STATUS, d.last_name || ', ' |

| d.first_name || ' ' || d.middle_name AS PROVIDER, aph.name AS

APH_SITE, inf.name AS INF_SITE, plant.name As PLANT, plant.i

d AS PLANT_ID, CAST(aac.scheduled_start AS DATE) AS APH_COL_SCH_

START, CAST(aac.actual_start AS DATE) AS APH_COL_ACT_START, a

ac.start_tzd AS APH_COL_START_TZD, CAST(ar.scheduled_finish AS D

ATE) AS APH_RINSEBACK_SCH_FINISH, CAST(ar.actual_finish AS DA

TE) AS APH_RINSEBACK_ACT_FINISH, ar.finish_tzd AS APH_RINSEBACK_

FINISH_TZD, CAST(ait.scheduled_start AS DATE) AS IN_TRANS_SCH

_START, CAST(ait.actual_start AS DATE) AS IN_TRANS_ACT_START,

ait.start_tzd AS IN_TRANS_START_TZD, CAST(ait.scheduled_finish

Note: Once query is identified we can tune to reduce the resource usage

1. b) If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits.

SELECT name ,statistic#
    FROM   v$statname
    WHERE  name LIKE '%CPU%session';

Sample OUTPUT

NAME STATISTIC#

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

CPU used by this session 13

IPC CPU used by this session 48

global enqueue CPU used by this session 51

gc CPU used by this session 169

Then determine which session using most of the cpu.

SELECT * FROM v$sesstat WHERE statistic# = 13;

Sample OUTPUT

SID STATISTIC# VALUE

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

1489 13 0

1490 13 3595

1493 13 0

1494 13 0

1496 13 717

1505 13 8138

1507 13 1989

1510 13 1278

1516 13 577

1517 13 1228

1518 13 0

Look up details for the session

SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,
           buffer_gets/executions AVG
    FROM   v$sqlarea a, v$session s
    WHERE  sid = 16
    AND    s.sql_address = a.address
    AND    executions > 0
    ORDER BY 5;

Use the v$sqltext to extract whole SQL text

Use autotrace to determine access paths

The AUTOTRACE system variable.

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.

SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path.

SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.

SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

Sample OUTPUT

SQL> SET AUTOTRACE ON

SQL> /

DNAME ENAME SAL JOB

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

ACCOUNTING CLARK 2450 MANAGER

ACCOUNTING KING 5000 PRESIDENT

ACCOUNTING MILLER 1300 CLERK

RESEARCH SMITH 800 CLERK

RESEARCH ADAMS 1100 CLERK

RESEARCH FORD 3000 ANALYST

RESEARCH SCOTT 3000 ANALYST

RESEARCH JONES 2975 MANAGER

SALES ALLEN 1600 SALESMAN

SALES BLAKE 2850 MANAGER

SALES MARTIN 1250 SALESMAN

SALES JAMES 950 CLERK

SALES TURNER 1500 SALESMAN

SALES WARD 1250 SALESMAN

14 rows selected.

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 TABLE ACCESS (FULL) OF 'DEPT'

4 1 SORT (JOIN)

5 4 TABLE ACCESS (FULL) OF 'EMP'

Statistics

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

148 recursive calls

4 db block gets

24 consistent gets

6 physical reads

43 redo size

591 bytes sent via SQL*Net to client

256 bytes received via SQL*Net from client

33 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed

1. c) Poor disk contention/performance

Use STATSPACK/AWR - focusing on “TBS IO stats” / “OS IO reports” are able to capture the statement activity

We can generate AWR report from OEM for selected snaps if we know the performance lagging time frame

We can monitor IO by setting 10046 level 8 trace to capture all the waits for a particular session

alter session set events '10046 trace name context forever, level 8';

Excessing i/o can be found by examining the resultant trace file and looking for i/o related waits such as

'db file sequential read' (Single-Block i/o - Index, Rollback Segment or Sort)
'db file scattered read' (Multi-Block i/o - Full table Scan).

Note: set TIMED_STATISTICS = TRUE to capture the timing information otherwise comparisons will be meaningless.

1. d) Unnecessary sorting:

The first question to ask is “Does the DATA really need to be sorted” if sorting really needed try to allocate enough memory to prevent the sorts from spilling disks causing an IO problems

Sorting is very expensive operation

- High CPU usage

- Potentially large disk usage

Notes:

- Indexes may be used to provided presorted data

- Sort merge joins inherently need to be sort

- Some sorts doesn’t needed sorts, in this case explain plan should show NOSORT for this operation

- Increase the sort area size to promote in memory sorts

- Modify query to process less rows (less to sort)

- Use sort_direct_writes to avoid flooding the buffer cache with sort blocks

- If Pro*C use release_cursor=yes to free up temporary segments held open

1. e) Over parsing:

Over parsing implies that cursors are not being shared – if statements are referenced multiple times share rather than fill up the shared pool with the multiple copies of essentially the same statement

Parsing is two types:

o Hard parsing – if a new sql statement is issued which doesn’t exist in the shared pool, then this has to be parsed fully , Oracle has to allocate memory for the statement from shared pool to check syntactically and semantically, it is expensive in terms of CPU usage and in the number of latch gets

o Soft Parsing- If a session issues a sql statement which is already in the shared pool and it can be existed version that statement

Shared pool:

Oracle keeps sql statements, packages, object information and many other items in an area of SGA called shared pool; this sharable area of memory is managed as a sophisticated cache and heap manager rolled into one. It has 3 fundamental problems to over come

o The unit of memory allocation is not constant – memory allocation from the pool can be bytes to kilobytes

o Not all memory can be “freed” when a user finishes with it as the aim of the shared pool is to maximize the share ability of information, - the information in the memory may be useful to another session – Oracle cannot know in advance if the items will be use to anyone else or not

o There is no disk area to page out to so that it is not like a traditional cache where there is a file backing store, only re creatable information can be discarded from the cache and it has to be re created when next needed.

We can reduce the load on shared pool by below

Parse once execute many	This approach to use in OLTP type applications is to parse a statement only once and hold the curser open
Eliminating Literal SQL
Avoid Invalidations
CURSOR_SHARING parameter set to FORCE
SESSION_CACHED_CURSOR=nnn	Can be set at instance or session level
CURSOR_SPACE_FOR_TIME parameter
CLOSE_CACHED_OPEN_CURSORS
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_RESERVED_MIN_ALLOC
SHARED_POOL_SIZE
_SQLEXEC_PROGRESSION_COST	Set it to 0 to avoid SQL sharing issues
Precompiler HOLD_CURSOR and RELEASE_CURSOR options
Pinning cursors in the shared pool
DBMS_SHARED_POOL.KEEP	Objects should be KEPT immediately after instance startup
Flushing the SHARED POOL
DBMS_SHARED_POOL.PURGE

Diagnostics:

Once the statement has been determined then the next stage is to the reason for the query running slow

Gather the explain plan for both slow and not slow queries and the best way of getting explain plan is to use AUTO TRACE

n Obtaining an Explain plan

- We can collect explain plan in manual method or using SQLT (SQLTXPLAIN)

èManual methods of displaying the plan

Note: Oracle doesn’t support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables, with bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan

a) Get SQL_ID and HASH_VALUE of the SQL (we can find the SQL_ID of a statement from AWR or ASH report or select using V$SQL view)

SELECT /* TARGET SQL */ * FROM dual;

SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text

FROM v$sql

WHERE sql_text like 'SELECT /* TARGET SQL */%'

Example:

SQL_ID SQL_TEXT

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

0xzhrtn5gkpjs SELECT /* TARGET SQL */ * FROM dual

b) Last executed SQL: we can pull execution plans from the library cache if the SQL has already been executed, get the plan of the last executed SQL by following sql

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

c) Gather_plan_statistics hint

SQL> select /*+ gather_plan_statistics */ col1, col2 etc.....

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))

d) Plan from memory

For SQL ID :
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));

For SQL ID, Child Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));

For SQL Text :
select t.*
from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t
where s.sql_text like '%&querystring%';

e) From AWR

For SQL ID :
select * from table(dbms_xplan.display_awr('&sql_id')) ;
select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in the current database :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in a different database ID :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ;

For SQL ID that was executed during a certain period :
select t.*
from (select distinct sql_id, plan_hash_value, dbid
from dba_hist_sqlstat
where sql_id = '&sql_id'
and snap_id between &begin_snap and &end_snap) s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

For SQL Text :
select t.*
from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid
from dba_hist_sqltext q, dba_hist_sqlstat r
where q.sql_id = r.sql_id
and q.sql_text like '%&querystring%') s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

f) From SQL plan Baseline

For SQL Handle :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle'));

For SQL Handle, Plan Name :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle', '&plan_name', 'ALL'));

For SQL Text :
select t.*
from (select distinct sql_handle, plan_name
from dba_sql_plan_baselines
where sql_text like '%&querystring%') s,
table(dbms_xplan.display_sql_plan_baseline(s.sql_handle, s.plan_name, 'ALL')) t;

èUsing SQLT Diagnostic tool

There are 4 main ways to use SQLT

XPLAIN - Generates Explain Plan, SQL is not executed

XTRACT- Generates Explain Plan for SQL that is already in memory or has been captured by AWR

XECUTE -SQL is executed, then Execution Plan is obtained

XTRXEC -This method combines the features of XTRACT and XECUTE.

èUsing enhanced explain plan script

Enhanced Explain Plan Script (Doc ID 104824.1)

Once we have explain plan, review alongside the query text looking for anything unusual, the goal to identify any problem areas or anything that could be modified to run more efficiently.

- Identify operations that may cause problems in system , these are general hints

Online Transaction Processing (OLTP) system	Full Table Scans Hash or Sort merge joins Index fast full scans or (large) index range scans Parallel query Nested loop joins with high cardinality outer table
Batch/Warehouse system	Nested loop joins Index lookups

- Identify expensive operations: if the explain plan has steps that show large cardinality or cost figures then these may be areas where the biggest benefits of tuning can be reaped

2. Statistics and analysis:

The CBO requires accurate statistics to enable it to work efficiently, the NUM_ROWS and LAST_ANALYZED columns in DBA_TABLES can be used to determine the statistics information on objects.

Recommendations for gathering CBO statistics: Different systems need different levels of statistical analysis due to differences in data

o The accuracy of the stats depends on the sample size

o The frequency of the gather statistics really depends on how frequently the objects involved in change and statistics have become inaccurate

o Determine the best sample size by gathering statistics using different sample sizes (5% sample is generally sufficient for most of the tables although if possible 100% is suggested)

o Gather statistics on indexes with compute only, since in index data already been sorted , compute on table is not acceptable

o When considering when to gather statistics choose quite a period as possible, although gathering will work, if you gather statistics on objects that are actively being updated and inserted into, you are much more likely to encounter contention

o It is recommended that users should store critical statistics to allow them to revert back to a working configuration in the event of a statistics change that significantly affects application performance (from 10g onwards database retains 31 days statistics, if gathering statistics causes critical statements to perform badly you can revert to the pre-analyze stats - by default 31 days)

o In DSS/ warehouse environment, queries are generally not predictable so there is no stable environment/ query set to upset by gathering statistics

3. Query text:

Review the query looking for any constructs which may cause you problems, these are few areas we can look for

o Large INlists / statements

o Outer joins –

o Set operators (UNION etc) – incorrect usage of set operations can be very inefficient

o No where clause or missing join predicates

o Aggregate functions – cpu intensive functions

o Sorting issues

o Implicit type conversion

o Any other strange or unexpected constructs

o Views, inline views or sub queries

4. Finding an acceptable plan:

Examination of an explain plan for the query can only give information on the query as it running currently.

Trace statistics:

I/O and timing stats (elapsed and cpu) can be very useful in determining which part of a query is the root cause of the problem.

Below are some potential causes for excessive I/O:

. Full table scans and Index Fast Full Scans

. Index range or Full scans

.Join order

.Excessive sort areas

Below are potential areas which cause high CPU:

Ø Index range full scan

Ø Join order

Ø Excessive sort/hash areas

Ø Nested Loops

5. Break Query down into components parts:

Break the query into its constituent parts and tune

6. Application Knowledge:

Good to gather the information about how a particular query should run based on application design

Ref: Diagnostics for Query Tuning Problems (Doc ID 68735.1)

Script to find current running wokflow service components location

2015-06-17T12:34:00.006-07:00

select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

Sample output:

MEANING P DECODE(FCQ.CONCURRENT_QUEUE_NA CONCURRENT_PROCESS_ID OS_PROCESS LOGFILE_NAME

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

Active A listener container 42605 21254 /data07/erp/oracle/comn/admin/log/FNDCPGSC42605.txt

Active A maile r container 42607 21270 /data07/erp/oracle/comn/admin/log/FNDCPGSC42607.txt

Utility script to display configuration of Applications

2015-06-08T11:42:00.000-07:00

We can find a script $AD_TOP/sql/adutconf.sql to generate Oracle Applications Database Configuration Report

Steps to create/manage PGP/GPG keys in Linux

2015-05-21T15:42:00.001-07:00

GnuPG is a complete and free implementation of the OpenPGP standard as defined by RFC4880(also known as PGP). GnuPG allows to encrypt and sign your data and communication, features a versatile key management system as well as access modules for all kinds of public key directories. GnuPG, also known as GPG, is a command line tool with features for easy integration with other applications.

Command to lista keys

gpg --list-keys

Output

gnupg]$ gpg --list-keys

/data01/home/applval/.gnupg/pubring.gpg

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

pub 2048R/4BAA00B5 2011-02-07

uid XYZ <accounts@XYZ.com>

sub 2048R/F42A6267 2011-02-07

Command to generate key

gpg --gen-key

[appldev@oracle-app01 ~]$ gpg --gen-key

This program comes with ABSOLUTELY NO WARRANTY.

This is free software, and you are welcome to redistribute it

under certain conditions. See the file COPYING for details.

gpg: directory `/home/appldev/.gnupg' created

gpg: new configuration file `/home/appldev/.gnupg/gpg.conf' created

gpg: WARNING: options in `/home/appldev/.gnupg/gpg.conf' are not yet active during this run

gpg: keyring `/home/appldev/.gnupg/secring.gpg' created

gpg: keyring `/home/appldev/.gnupg/pubring.gpg' created

Please select what kind of key you want:

(1) DSA and Elgamal (default)

(2) DSA (sign only)

(5) RSA (sign only)

Your selection? 1

DSA keypair will have 1024 bits.

ELG-E keys may be between 1024 and 4096 bits long.

What keysize do you want? (2048)

Requested keysize is 2048 bits

Please specify how long the key should be valid.

0 = key does not expire

<n> = key expires in n days

<n>w = key expires in n weeks

<n>m = key expires in n months

<n>y = key expires in n years

Key is valid for? (0)

Key does not expire at all

Is this correct? (y/N) y

You need a user ID to identify your key; the software constructs the user ID

from the Real Name, Comment and Email Address in this form:

"Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"

Real name: Venkat

You selected this USER-ID:

"Venkat (V) <venkat@xyz.com>"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O

You need a Passphrase to protect your secret key.

You don't want a passphrase - this is probably a *bad* idea!

I will do it anyway. You can change your passphrase at any time,

using this program with the option "--edit-key".

We need to generate a lot of random bytes. It is a good idea to perform

some other action (type on the keyboard, move the mouse, utilize the

disks) during the prime generation; this gives the random number

generator a better chance to gain enough entropy.

.+++++.++++++++++.+++++++++++++++.+++++++++++++++++++++++++++++++++++.++++++++++.++++++++++++++++++++++++++++++..+++++++++++++++.++++++++++>++++++++++..............>+++++.....................................+++++

Not enough random bytes available. Please do some other work to give

the OS a chance to collect more entropy! (Need 284 more bytes)

We need to generate a lot of random bytes. It is a good idea to perform

some other action (type on the keyboard, move the mouse, utilize the

disks) during the prime generation; this gives the random number

generator a better chance to gain enough entropy.

++++++++++.++++++++++....++++++++++++++++++++++++++++++++++++++++.++++++++++..++++++++++.+++++.+++++.+++++.++++++++++++++++++++...+++++++++++++++.+++++.+++++.++++++++++>+++++.......>+++++<+++++......................................+++++^^^^^^^^^^^

gpg: /home/appldev/.gnupg/trustdb.gpg: trustdb created

gpg: key EB4CC5EF marked as ultimately trusted

public and secret key created and signed.

gpg: checking the trustdb

gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model

gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u

pub 1024D/EB4CC5EF 2015-05-21

Key fingerprint = F591 367B 159C 1F14 81BB B4A0 FC81 E135 EB4C C5EF

uid Venkat (V) <venkat@xyz.com>

sub 2048g/39A1302A 2015-05-21

These files will be generated

[appldev@oracle-app01 ~]$ cd .gnupg

[appldev@oracle-app01 .gnupg]$ ls -la

total 44

drwx------ 2 appldev dba 4096 May 21 12:01 .

drwx------ 14 appldev dba 4096 May 21 11:54 ..

-rw------- 1 appldev dba 9207 May 21 11:54 gpg.conf

-rw------- 1 appldev dba 1165 May 21 12:01 pubring.gpg

-rw------- 1 appldev dba 1165 May 21 12:01 pubring.gpg~

-rw------- 1 appldev dba 600 May 21 12:01 random_seed

-rw------- 1 appldev dba 1238 May 21 12:01 secring.gpg

-rw------- 1 appldev dba 1280 May 21 12:01 trustdb.gpg

List Keys

[appldev@oracle-app01 .gnupg]$ gpg --list-keys

/home/appldev/.gnupg/pubring.gpg

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

pub 1024D/EB4CC5EF 2015-05-21

uid Venkat (V) <venkat@xyz.com>

sub 2048g/39A1302A 2015-05-21

Here is the description of “pub 1024D/EB4CC5EF 2015-05-21”

pub	Public key
1024D	The number of bits in the key
EB4CC5EF	The key ID
2015-05-21	The date of key creation

Command to list the secret keys

gpg --list-secret-keys

[appldev@oracle-app01 .gnupg]$ gpg --list-secret-keys

/home/appldev/.gnupg/secring.gpg

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

sec 1024D/EB4CC5EF 2015-05-21

uid Venkat (V) <venkat@xyz.com>

ssb 2048g/39A1302A 2015-05-21

Command to exports public key to a file

gpg --export -u useranme -a -o gpg.public

[appldev@oracle-app01 .gnupg]$ pwd

/home/appldev/.gnupg

[appldev@oracle-app01 .gnupg]$ gpg --export -u appldev -a -o gpg.public

[appldev@oracle-app01 .gnupg]$ ls -ltr

total 36

-rw------- 1 appldev dba 9207 May 21 11:54 gpg.conf

-rw------- 1 appldev dba 1280 May 21 12:01 trustdb.gpg

-rw------- 1 appldev dba 1238 May 21 12:01 secring.gpg

-rw------- 1 appldev dba 600 May 21 12:01 random_seed

-rw------- 1 appldev dba 1165 May 21 12:01 pubring.gpg~

-rw------- 1 appldev dba 1165 May 21 12:01 pubring.gpg

-rw-r--r-- 1 appldev dba 1682 May 21 12:24 gpg.public

Public key entry will display as below

[appldev@oracle-app01 .gnupg]$ cat gpg.public

-----BEGIN PGP PUBLIC KEY BLOCK-----

Version: GnuPG v1.4.5 (GNU/Linux)

mQGiBFVeK28RBACgbdfw0XMk+yHQERSjQkFNAX6nHYffKQrt/X0xLA/Ob0pUCoYm

+sMtVD8upOpFxfZMRFroyI87dO9Bv5fR2Q2MwVsOBPDrUmg60Dd011+F7mD+WDwi

kI8eOak82noyOMVtugAJc/5MxD8JVj2zDkMLThOlFPMXuGScwPky1OUKhwCgp3mZ

XDgPtR9wcwniei2cYw1VozUD/A8dCzuunXIxptcQdfFCrwjX8BNnYzhHbUIIncdR

/VVVcCcZGTfPNIENtx7OAHFg6WntXj0v27LMy1WCqJrr2dCXwnIPBUoWeJpt3rdH

lUYmoAIjUHRCuxb5HK7qXfhKS9LCgStr019nP0htEMG0XIX5RCLtxmF2jeg6g1AV

LalCA/44vGVi5sPpbclBUVEhOj/ZqdFtPALyZVqbG9jzo97cF5YuwVp1yybTiK6L

XWheqEuWOTDZRqv0Ldmu76Nw2wLr+G/hkjF7GQh0PZO368Fq9u8AnQFJO0+07HoF

9r4vinRbl1FrQsmzGjnNoHmq4kwuC96m+2NMcBV8hc1rOZSuzrQhVmVua2F0IChW

KSA8dnNpcmljaWxsYUBvc2l1cy5jb20+iGAEExECACAFAlVeK28CGwMGCwkIBwMC

BBUCCAMEFgIDAQIeAQIXgAAKCRD8geE160zF7/RxAJ4yNUFN0NxGCk7+nKvuIpsN

rNHvtACgm/P7/x8WkOOcTknIxaG2nhzGbPS5Ag0EVV4rdhAIAIl82OjeXof86yTR

pxnRaTY9TrauAxyPVulyoHAely+aDho1guYYoY/mZ+WW2Dg5WcrCTxDxf0Uge7qA

mzeKY+EWd5dEdEmYMXiG2oqwdyr2rnS6gaTPdOFxqNpB2wLI7dxvvXEkQOveQtAF

U63sjlZPCSH1ikv2vd04X5XUp8vSbeABZ80etJEVvo+/aubLrnYaNHNvi+X1h8P4

+8reSr+92y7GjH5riaIS0CLCNF6yDRKZF9FgbHsxTJlyFP/VcWhHw+JLSvrwvFAz

A3DlgIUoKcCyV7DZeMJgo/B1hqrdjRDYBIevnK79BllaoHWt6wdSnP4pSFdjNZK4

xkGA8ccABA0H/172EURK9xqd7zCxTYaRBIiWIdjJJQ/037T0aEzCcgb4Yd14cjYv

LdsON55KHC587BYuPP6kv8M5Aj7EUAz/bmvk1bkdU0s0JtRCnI5lDnzTXnnd7MmZ

4bmFDrLk4VU/n2csOh7Abn5ZpjrKS1zOeTtdqBAnEgSXwRp/7eSHM1nrczQmrcv0

h/moWUadgrfnLDlujYZnw6jFfJxhah5QTanE4F+jh7AEqJCOExkMveh9KZAd2wfe

A5te4+zOj0HVu57pQ2G8ikIIqoZ7RJ6xggwVcfVtX0Q80mJOqkf4tfeSqFEINluX

5UJxfgokDAPwW/A1sSA4zX0yH6O//72/EVuISQQYEQIACQUCVV4rdgIbDAAKCRD8

geE160zF78BFAJ9TgcwY3FhQjaD/A88hKSz0cB3VjwCfav7SOtT+lIFxCThUagin

K4lIapc=

=oTJ7

-----END PGP PUBLIC KEY BLOCK-----

Command to exports private key to a file

gpg --export-secret-keys -u appldev -a -o gpg.priv

[appldev@oracle-app01 .gnupg]$ gpg --export-secret-keys -u appldev -a -o gpg.priv

[appldev@oracle-app01 .gnupg]$ ls -ltr

total 40

-rw------- 1 appldev dba 9207 May 21 11:54 gpg.conf

-rw------- 1 appldev dba 1280 May 21 12:01 trustdb.gpg

-rw------- 1 appldev dba 1238 May 21 12:01 secring.gpg

-rw------- 1 appldev dba 600 May 21 12:01 random_seed

-rw------- 1 appldev dba 1165 May 21 12:01 pubring.gpg~

-rw------- 1 appldev dba 1165 May 21 12:01 pubring.gpg

-rw-r--r-- 1 appldev dba 1682 May 21 12:24 gpg.public

-rw-r--r-- 1 appldev dba 1781 May 21 12:24 gpg.priv

Private Key block

[appldev@oracle-app01 .gnupg]$ cat gpg.priv

-----BEGIN PGP PRIVATE KEY BLOCK-----

Version: GnuPG v1.4.5 (GNU/Linux)

lQG7BFVeK28RBACgbdfw0XMk+yHQERSjQkFNAX6nHYffKQrt/X0xLA/Ob0pUCoYm

+sMtVD8upOpFxfZMRFroyI87dO9Bv5fR2Q2MwVsOBPDrUmg60Dd011+F7mD+WDwi

kI8eOak82noyOMVtugAJc/5MxD8JVj2zDkMLThOlFPMXuGScwPky1OUKhwCgp3mZ

XDgPtR9wcwniei2cYw1VozUD/A8dCzuunXIxptcQdfFCrwjX8BNnYzhHbUIIncdR

/VVVcCcZGTfPNIENtx7OAHFg6WntXj0v27LMy1WCqJrr2dCXwnIPBUoWeJpt3rdH

lUYmoAIjUHRCuxb5HK7qXfhKS9LCgStr019nP0htEMG0XIX5RCLtxmF2jeg6g1AV

LalCA/44vGVi5sPpbclBUVEhOj/ZqdFtPALyZVqbG9jzo97cF5YuwVp1yybTiK6L

XWheqEuWOTDZRqv0Ldmu76Nw2wLr+G/hkjF7GQh0PZO368Fq9u8AnQFJO0+07HoF

9r4vinRbl1FrQsmzGjnNoHmq4kwuC96m+2NMcBV8hc1rOZSuzgAAoIMEn0suA7Hf

MsFOViq4YUj0SWrGCWG0IVZlbmthdCAoVikgPHZzaXJpY2lsbGFAb3NpdXMuY29t

PohgBBMRAgAgBQJVXitvAhsDBgsJCAcDAgQVAggDBBYCAwECHgECF4AACgkQ/IHh

NetMxe/0cQCeMjVBTdDcRgpO/pyr7iKbDazR77QAoJvz+/8fFpDjnE5JyMWhtp4c

xmz0nQI9BFVeK3YQCACJfNjo3l6H/Osk0acZ0Wk2PU62rgMcj1bpcqBwHpcvmg4a

NYLmGKGP5mflltg4OVnKwk8Q8X9FIHu6gJs3imPhFneXRHRJmDF4htqKsHcq9q50

uoGkz3ThcajaQdsCyO3cb71xJEDr3kLQBVOt7I5WTwkh9YpL9r3dOF+V1KfL0m3g

AWfNHrSRFb6Pv2rmy652GjRzb4vl9YfD+PvK3kq/vdsuxox+a4miEtAiwjResg0S

mRfRYGx7MUyZchT/1XFoR8PiS0r68LxQMwNw5YCFKCnAslew2XjCYKPwdYaq3Y0Q

2ASHr5yu/QZZWqB1resHUpz+KUhXYzWSuMZBgPHHAAQNB/9e9hFESvcane8wsU2G

kQSIliHYySUP9N+09GhMwnIG+GHdeHI2Ly3bDjeeShwufOwWLjz+pL/DOQI+xFAM

/25r5NW5HVNLNCbUQpyOZQ5801553ezJmeG5hQ6y5OFVP59nLDoewG5+WaY6yktc

znk7XagQJxIEl8Eaf+3khzNZ63M0Jq3L9If5qFlGnYK35yw5bo2GZ8OoxXycYWoe

UE2pxOBfo4ewBKiQjhMZDL3ofSmQHdsH3gObXuPszo9B1bue6UNhvIpCCKqGe0Se

sYIMFXH1bV9EPNJiTqpH+LX3kqhRCDZbl+VCcX4KJAwD8FvwNbEgOM19Mh+jv/+9

vxFbAAFUCH8ZSpd4MCNlSFNSw4GRm7VlubzcSBDcqdQY2JlhLbRGB0ABjVlyj+E/

5xPGiEkEGBECAAkFAlVeK3YCGwwACgkQ/IHhNetMxe/ARQCgj//H7ywOPVMHzREV

oMzGvdw9se0An3gXMOrUiw4ES7MVMG223oNUrwFY

=wGjB

-----END PGP PRIVATE KEY BLOCK-----

Command to import Keys to existed Key

gpg –import filename

[appldev@oracle-app01 .gnupg]$ gpg --import /tmp/gpg.public

gpg: key B4F13C76: public key " ABC23 <info@abc23.com>" imported

gpg: Total number processed: 1

gpg: imported: 1

List keys

[appldev@oracle-app01 .gnupg]$ gpg --list-keys

/home/appldev/.gnupg/pubring.gpg

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

pub 1024D/45727953 2015-05-21

uid Venkat (V) <Venkat@xyz>

sub 2048g/60F984DF 2015-05-21

pub 1024D/B4F13C76 2003-07-14 [expires: 2018-05-13]

uid ABC23 <info@abc23>

sub 2048g/00A0C61E 2003-07-14 [expires: 2018-05-13]

Commands HELP

[appldev@oracle-app01 .gnupg]$ gpg --help

gpg (GnuPG) 1.4.5

This program comes with ABSOLUTELY NO WARRANTY.

This is free software, and you are welcome to redistribute it

under certain conditions. See the file COPYING for details.

Home: ~/.gnupg

Supported algorithms:

Pubkey: RSA, RSA-E, RSA-S, ELG-E, DSA

Cipher: 3DES, CAST5, BLOWFISH, AES, AES192, AES256, TWOFISH

Hash: MD5, SHA1, RIPEMD160, SHA256, SHA384, SHA512, SHA224

Compression: Uncompressed, ZIP, ZLIB, BZIP2

Syntax: gpg [options] [files]

sign, check, encrypt or decrypt

default operation depends on the input data

Commands:

-s, --sign [file] make a signature

--clearsign [file] make a clear text signature

-b, --detach-sign make a detached signature

-e, --encrypt encrypt data

-c, --symmetric encryption only with symmetric cipher

-d, --decrypt decrypt data (default)

--verify verify a signature

--list-keys list keys

--list-sigs list keys and signatures

--check-sigs list and check key signatures

--fingerprint list keys and fingerprints

-K, --list-secret-keys list secret keys

--gen-key generate a new key pair

--delete-keys remove keys from the public keyring

--delete-secret-keys remove keys from the secret keyring

--sign-key sign a key

--lsign-key sign a key locally

--edit-key sign or edit a key

--gen-revoke generate a revocation certificate

--export export keys

--send-keys export keys to a key server

--recv-keys import keys from a key server

--search-keys search for keys on a key server

--refresh-keys update all keys from a keyserver

--import import/merge keys

--card-status print the card status

--card-edit change data on a card

--change-pin change a card's PIN

--update-trustdb update the trust database

--print-md algo [files] print message digests

Options:

-a, --armor create ascii armored output

-r, --recipient NAME encrypt for NAME

-u, --local-user use this user-id to sign or decrypt

-z N set compress level N (0 disables)

--textmode use canonical text mode

-o, --output use as output file

-v, --verbose verbose

-n, --dry-run do not make any changes

-i, --interactive prompt before overwriting

--openpgp use strict OpenPGP behavior

--pgp2 generate PGP 2.x compatible messages

(See the man page for a complete listing of all commands and options)

Examples:

-se -r Bob [file] sign and encrypt for user Bob

--clearsign [file] make a clear text signature

--detach-sign [file] make a detached signature

--list-keys [names] show keys

--fingerprint [names] show fingerprints

Please report bugs to <gnupg-bugs@gnu.org>.

References:

http://docs.oracle.com/cd/E39820_01/doc.11121/gateway_docs/content/general_certificates.html

How to change/restore user password in 11G database

2015-05-12T13:57:00.004-07:00

The change to DBA_USERS is the result of a security enhancement, it was no longer appropriate to show the password hashes in the DBA_USERS view as it may cause undesired exposure when access to this view is needed by 'unprivileged' users. This feature coincides with the introduction of the new hash algorithm, which is stored differently as compared to the visible hash in earlier releases anyway

In 11g if you query password field, it will return NULL.

SQL> select USERNAME,PASSWORD,PASSWORD_VERSIONS from dba_users where USERNAME in ('SYSTEM');

USERNAME PASSWORD PASSWORD
------------------------------ ------------------------------ --------
SYSTEM 10G 11G

Here are the steps to change/rollback user passwords in 11G

SQL> select spare4 from user$ where name='SYSTEM';

SPARE4
--------------------------------------------------------------------------------
S:FEB2B0DFDF35B910BBEB312E86A2460C23FF77317DB57D16BB564A67D717

SQL> alter user SYSTEM identified by manager;

User altered.

SQL> conn SYSTEM/manager;
Connected.
SQL>
SQL> alter user SYSTEM identified by values 'S:FEB2B0DFDF35B910BBEB312E86A2460C23FF77317DB57D16BB564A67D717';

User altered.

SQL> conn SYSTEM/manager;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn SYSTEM/Syssoa;
Connected.

Reference : User Passwords Are No Longer Visible In DBA_USERS As Of 11g (Doc ID 735651.1)

Script to find concurrent request id from known SID

2015-05-07T14:16:00.001-07:00

select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status
from gv$process v, gv$session s, applsys.fnd_concurrent_requests f
where 1=1
and s.paddr=v.addr
and f.oracle_process_id=v.spid
and trunc(f.request_date)=trunc(sysdate)
and sid=&SID

Script to find any profile option in Oracle applications

2015-04-30T14:34:00.002-07:00

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name
, substr(fpot.user_profile_option_name,1,60) profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value
, fpov.profile_option_value profile_value
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, fnd_responsibility_tl fr
, fnd_user fu
WHERE (fpo.profile_option_name like nvl('X',fpo.profile_option_name) -- Replace X with the profile short name, ie 'PRINTER'
or fpot.user_profile_option_name like nvl('Y',fpot.user_profile_option_name)) -- Replace Y with profile user name, ie 'Printer%'
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value;

Reference:
Script To List The Values Of A Profile Option At All Levels (Doc ID 803587.1)
How To Check If a Profile Option Is Set In Oracle Applications? (Doc ID 470102.1)
How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus (Doc ID 201945.1)
How to Search all of the Profile Options for a Specific Value (Doc ID 282382.1)
How To Find All Users With A Particular Profile Option Set? (Doc ID 367926.1)

Script to find responsibilities assigned for an user in EBS

2015-04-25T18:47:00.003-07:00

Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name in ('&USERNAME');

Script to get assigned responsibilities for a concurrent program

2015-04-25T18:45:00.000-07:00

SELECT DISTINCT *
FROM apps.fnd_responsibility_tl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE request_group_id IN (
SELECT request_group_id
FROM apps.fnd_request_group_units
WHERE request_unit_id =
(SELECT DISTINCT concurrent_program_id
FROM Apps.fnd_concurrent_programs_tl
WHERE user_concurrent_program_name like '%&Program Name%'))
AND end_date IS NULL)
AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name;

Steps to configure printer in Oracle apps 11i/R12

2015-04-15T11:16:00.000-07:00

Please follow the below Steps:

1. Setup the printer at the OS level
2. Add a valid entry in the hosts file (Printer Name and the IP Address), if ip address is resolved by DNS this step can be ignored
3. Login to System Administrator responsibility
4. Navigate to Install > Printer > Register
5. Define a new printer by entering the Printer Name
6. Save
7. Bounce the Concurrent Manager
8. Submit any standard concurrent request

References:
Step By Step Guide to Set Up a Printer in Oracle Applications (Doc ID 60936.1)
How to Implement Printing for Oracle Applications: Getting Started (Doc ID 269129.1)

ORA-00955: name is already used by an existing object & ORA-06512: at "SYS.UTL_RECOMP", line 662

2014-10-21T11:36:00.000-07:00

Issue:

When I ran utlrp.sql to compile the INVALID objects in database, I see it error out due to

DOC>#
DECLARE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.UTL_RECOMP", line 662
ORA-06512: at line 4

Cause:

Looks like known bug#9881143 in Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.5 [Release 10.2] where an index is left from a previous failed execution of utl_recomp.

SQL> /

OWNER                          SEGMENT_NAME                                  SEGMENT_TYPE
------------------------------ --------------------------------------------- ------------------
SYS                            UTL_RECOMP_COMP_IDX1                          INDEX

Solution: Drop the index utl_recomp_comp_idx1 left by utl_recomp and then rerun the package.

SQL> drop index UTL_RECOMP_COMP_IDX1;

Index dropped.

Compile script completed successfully:
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2014-10-21 11:14:12

ERRORS DURING RECOMPILATION
---------------------------
                          0

PL/SQL procedure successfully completed.

Reference: Ora-955 Error Running Utl_Recomp.Recomp_Serial (Doc ID 1274504.1)

APP-FND-01926: The custom event WHEN-LOGON-CHANGED raised unhandled exception: ORA-06508: PL/SQL: could not find program unit being called

2014-09-12T11:22:00.000-07:00

This issue has been observed under the following circumstances:

CUSTOM.plx file are of different date than the conversion date (last compilation date), the likeky reason for this is that CUSTOM.pll has not been properly compiled.
For example:
-rwxr-xr-x 1 oradev dba 24576 Aug 24 2009 CUSTOM.pll
-rwxr-xr-x 1 oradev dba 20480 Mar 31 12:36 CUSTOM.plx

To implement the solution, please do the following:
1. Take a backup of both files CUSTOM.PLL and CUSTOM.PLX
2. Get a working copy of the custom.pll file.
3. Compile the custom.pll using f60gen under the environment which creates the file custom.plx, where the last access date shows the current date.

f60gen \
module=$AU_TOP/resource/CUSTOM.pll \
userid=APPS/xxx \
output_file=$AU_TOP/resource/CUSTOM.plx \
module_type=library batch=yes compile_all=special

4. The $FORMS60_PATH needs to be in sync with the value set for the s_f60path variable in the Context.xml file.
i.e >/oracle/apps/test10appl/au/11.5.0/resource:/oracle/apps/test10appl/au/11.5.0/resource/stub</FORMS60_PATH>

5. Run Autoconfig to instantiate the changes.

Ref:11i: "APP-FND-01926: The custom event WHEN-LOGON-CHANGED raised unhandled exception" Error When Attempting to Launch Forms (Doc ID 1080160.1)

afadmprf.sh and afcpctx.sh failed during autoconfig

2014-07-16T17:22:00.000-07:00

Error:

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
      <filename> <phase> <return code where appropriate>

[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
    Directory: /data02/erpval/oracle/valcomn/admin/install/VAL_s603914se2sl196
      afadmprf.sh             INSTE8_PRF         1
      afcpctx.sh              INSTE8_PRF         1

AutoConfig is exiting with status 2

Solution: followed below steps

1. removed the log files from $APPLCSF

[applval@S603914SE2SL196 log]$ find . -type f -mtime +7 -exec rm -rf {} \;

2. cd $APPL_TOP

3. Source the APPSORA.env file.

4. Run relink as follows:
adrelink.sh force=y ranlib=y "AD all"

4a. Then run the following:
adrelink.sh force=y ranlib=y "fnd FNDCPUCF"

5. Run adadmin

6. Select option 2 "Maintain Applications Files Menu".

7. Select option 2 "Relink Applications Programs".

8. Relink all modules' programs.

9.then re-run the autoconfig and update the status

Reference: Autoconfig Failing On Concurrent Node At Afadmprf.sh & Afcpctx.sh INSTE8_PRF 1 (Doc ID 1634846.1)

After Appling 11i.ATG_PF.H.Delta.7 Error On afadmprf.sh & afcpctx.sh (Doc ID 943614.1)

Concurrent services not coming up after services restart/server reboot

2014-07-16T17:11:00.000-07:00

Concurrent services not coming up after services restart/server reboot

Error in ICM log file:
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /data02/erpval/oracle/valappl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager process. Contact your system admin : 16-JUL-2014 13:27:05
Starting STANDARD Concurrent Manager               : 16-JUL-2014 13:27:05
Service Manager FNDSM_S603914SE2SL196_VAL could not initialize. The Service Manager has been unable to verify its environment and create its log file.
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /data02/erpval/oracle/valappl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager process. Contact your system admin : 16-JUL-2014 13:27:06
Starting STANDARD Concurrent Manager               : 16-JUL-2014 13:27:06
Service Manager FNDSM_S603914SE2SL196_VAL could not initialize. The Service Manager has been unable to verify its environment and create its log file.
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /data02/erpval/oracle/valappl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager process. Contact your system admin : 16-JUL-2014 13:27:06
Starting STANDARD Concurrent Manager               : 16-JUL-2014 13:27:06
Service Manager FNDSM_S603914SE2SL196_VAL could not initialize. The Service Manager has been unable to verify its environment and create its log file.
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /data02/erpval/oracle/valappl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager process. Contact your system admin : 16-JUL-2014 13:27:06

Solution: followed the below steps as part of resolutions.

1.ran cmclean.sql
2.exec FND_CONC_CLONE.SETUP_CLEAN;
3. execute autoconfig on db nodes
4. execute autoconfig on apps nodes
5. started the services

Steps for configuring OCM

2014-05-11T00:49:00.000-07:00

[orauser@erp1 ~]$ grep -i jdk $CONTEXT_FILE

<JDK_TOP oa_var="s_jdktop">/data/d02/orauser/usertest/db/tech_st/11.2.0/jdk/jre</JDK_TOP>

<JRE_TOP oa_var="s_jretop">/data/d02/orauser/usertest/db/tech_st/11.2.0/jdk/jre</JRE_TOP>

<ADJVAPRG oa_var="s_adjvaprg" osd="UNIX">/data/d02/orauser/usertest/db/tech_st/11.2.0/jdk/jre/bin/java</ADJVAPRG>

<JAVA_HOME oa_var="s_db_java">/data/d02/orauser/usertest/db/tech_st/11.2.0/jdk/jre</JAVA_HOME>

[orauser@erp1 ~]$ export JAVA_HOME=/data/d02/orauser/usertest/db/tech_st/11.2.0/jdk/jre

[orauser@erp1 ~]$ cd $ORACLE_HOME/ccr/bin

[orauser@erp1 bin]$ ./setupCCR -s 123456 xyz.abc@doman.com

** Installing base package **

Deploying core - Version 10.3.4.0.0

** Registering installation with Oracle Configuration Manager server(s) **

Deploying engines - Version 10.2.7.1.0

Deploying metricdata - Version 10.2.4.0.3

Deploying scripts - Version 10.3.4.0.0

** Getting package updates from ContentServer **

Deploying core - Version 12.0.0.0.1

Deploying engines - Version 12.0.0.0.0

Deploying metricdata - Version 12.0.0.0.0

Deploying ocmcert - Version 12.0.0.0.1

Deploying scripts - Version 12.0.0.0.1

** Starting the Oracle Configuration Manager Scheduler **

Oracle Configuration Manager - Release: 12.0.0.0.1 - Production

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

Starting Oracle Configuration Manager...

Waiting for status from Oracle Configuration Manager....

Start Date 10-May-2014 23:51:27

Last Collection Time -

Next Collection Time 11-May-2014 23:49:00

Collection Frequency Daily at 23:49

Collection Status scheduled collection running

Log Directory /data/d02/orauser/usertest/db/tech_st/11.2.0/ccr/hosts/erp1.com/log

Registered At 10-May-2014 23:49:25

Automatic Update On

Collector Mode Connected

Oracle Configuration Manager successfully started.

Oracle Configuration Manager has been configured in connected mode. If the

target ORACLE_HOME is running a database, please refer to the

"Post-installation Database Configuration" section of the OCM Installation

and Administration Guide

(http://www.oracle.com/technology/documentation/ocm.html) to complete the

installation.

View configuration data reports and access valuable configuration best

practices by going to My Oracle Support.

[orauser@erp1 bin]$

Steps to drop OCM:

[orauser@erp1 bin]$ ./emCCR stop

Oracle Configuration Manager - Release: 12.0.0.0.1 - Production

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

Oracle Configuration Manager Stopped...

[orauser@erp1 bin]$

[orauser@erp1 scripts]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 11 00:00:29 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

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

SQL> @dropocm.sql

SQL> SET serveroutput on

SQL> set verify off

SQL>

SQL> -- handle 11+ databases (ACLs)

SQL> @@dropocm11plus

SQL> Rem

SQL> Rem dropocm11plus.sql

SQL> Rem

SQL> Rem NAME

SQL> Rem dropocm11plus.sql - <one-line expansion of the name>

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem <short description of component this file declares/defines>

SQL> Rem

SQL> Rem NOTES

SQL> Rem <other useful comments, qualifications, etc.>

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem imunusam 09/16/13 - To fix bug 17461614

SQL> Rem jsutton 09/03/13 - make more tolerant of no xmldb

SQL> Rem jsutton 09/16/11 - put 11+ operations in separate file

SQL> Rem jsutton 09/16/11 - Created

SQL> Rem

SQL>

SQL> SET serveroutput on

SQL> set verify off

SQL>

SQL> DECLARE

2 l_instvers VARCHAR2(100);

3 l_comp_cnt NUMBER;

4 l_regexp_match NUMBER;

5 TYPE p_acl_ids_type IS TABLE OF VARCHAR2(4000);

6 TYPE p_acl_priv_type IS TABLE OF NUMBER;

7 l_acl_ids p_acl_ids_type;

8 l_acl_privs p_acl_priv_type;

9 l_acl_cnt NUMBER;

11 BEGIN

12 -- Drop the Network ACL if the ACL was created.

13 select LPAD(version,10,'0') into l_instvers from v$instance;

14 IF l_instvers >= '11.0.0.0.0' THEN

15 -- check for XML DB installed

16 EXECUTE IMMEDIATE

17 'select count(*) from dba_registry where COMP_NAME = ''Oracle XML Database'' and STATUS = ''VALID''' into l_comp_cnt;

18 -- XML DB installed, proceed

19 IF l_comp_cnt > 0 THEN

20 -- drop the entire ACL if it is the one we created

21 EXECUTE IMMEDIATE

22 'select count(*) from dba_network_acls where regexp_like(acl,''.oracle-sysman-ocm-Resolve-Access.xml$'')' into l_regexp_match;

23 IF (l_regexp_match > 0) THEN

24 EXECUTE IMMEDIATE

25 'BEGIN ' ||

26 ' DBMS_NETWORK_ACL_ADMIN.DROP_ACL(''oracle-sysman-ocm-Resolve-Access.xml''); ' ||

27 ' COMMIT;' ||

28 'END;';

29 END IF;

30 -- check for resolve privilege for OCM user

31 -- get all ACLIDs and determine whether ORACLE_OCM has resolve privilege through each

32 BEGIN

33 EXECUTE IMMEDIATE

34 'SELECT acl, DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, ''ORACLE_OCM'', ''resolve'') acl_priv ' ||

35 ' FROM dba_network_acls' BULK COLLECT INTO l_acl_ids, l_acl_privs;

36 EXCEPTION

37 WHEN OTHERS THEN NULL;

38 END;

39 -- walk the collection

40 FOR i IN 1..l_acl_ids.count LOOP

41 BEGIN

42 IF (l_acl_privs(i) IS NOT NULL AND l_acl_privs(i) > 0) THEN

43 -- remove resolve privilege

44 EXECUTE IMMEDIATE

45 'BEGIN ' ||

46 ' DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(acl => ' || l_acl_ids(i) || ',principal => ''ORACLE_OCM'');' ||

47 ' COMMIT;' ||

48 'END;' ;

49 END IF;

50 EXCEPTION

51 WHEN OTHERS THEN NULL;

52 END;

53 END LOOP;

54 END IF;

55 END IF;

56 END;

57 /

PL/SQL procedure successfully completed.

SQL>

SQL> DECLARE

2 l_user_dropped BOOLEAN;

4 /*

5 * Procedure to drop user.

6 * If p_throw_exp is TRUE, throw exception if user cannot be dropped.

7 */

8 PROCEDURE drop_user

9 (p_user IN VARCHAR2, p_dir_name IN VARCHAR2, p_dir_name2 IN VARCHAR2,

10 p_throw_user_drop_exp BOOLEAN := FALSE,o_user_dropped OUT BOOLEAN)

11 IS

12 l_ll_user_exists NUMBER;

13 l_ll_pkg_exists NUMBER;

14 l_vers v$instance.version%TYPE;

15 l_compat_vers v$parameter.value%TYPE;

16 l_dirobj_cnt NUMBER;

17 BEGIN

18 BEGIN

19 select count(*) into l_ll_user_exists from dba_users where username = p_user;

20 o_user_dropped := FALSE;

21 IF l_ll_user_exists = 1 THEN

22 SELECT count(*) into l_ll_pkg_exists FROM sys.user$ u, sys.obj$ o WHERE u.name = p_user AND o.name ='MGMT_DB_LL_METRICS' AND o.owner# = u.user# AND o.type# = 9 AND o.status LIKE '%' ;

23 IF l_ll_pkg_exists = 1 THEN

24 execute immediate 'drop user '|| p_user ||' cascade';

25 o_user_dropped := TRUE;

26 ELSIF p_throw_user_drop_exp THEN

27 RAISE_APPLICATION_ERROR(-20030,'Could not drop the OCM user ''' || p_user || '''. It does not contain OCM schema. Please drop this user to proceed.');

28 END IF;

29 END IF;

30 EXCEPTION

31 WHEN OTHERS THEN

32 IF p_throw_user_drop_exp THEN

33 raise_application_error(-20006,SQLERRM);

34 ELSE

35 NULL;

36 END IF;

37 END;

38 select substr(version,1,5) into l_vers from v$instance;

39 begin

40 select substr(value,1,5) into l_compat_vers from v$parameter where lower(name) = 'compatible';

41 exception

42 WHEN NO_DATA_FOUND THEN

43 l_compat_vers := l_vers;

44 end;

45 IF l_vers != '9.0.1' AND l_vers != '8.1.7' AND l_compat_vers != '8.1.7' THEN

46 select count(*) into l_dirobj_cnt from dba_directories where DIRECTORY_NAME = p_dir_name ;

47 IF l_dirobj_cnt = 1 THEN

48 execute immediate 'DROP DIRECTORY ' || p_dir_name;

49 END IF;

50 IF p_dir_name2 IS NOT NULL THEN

51 select count(*) into l_dirobj_cnt from dba_directories where DIRECTORY_NAME = p_dir_name2 ;

52 IF l_dirobj_cnt = 1 THEN

53 execute immediate 'DROP DIRECTORY ' || p_dir_name2;

54 END IF;

55 END IF;

56 END IF;

57 END drop_user;

59 BEGIN

60 -- Drop previous OCM user

61 -- discard the return value

62 drop_user('CCR','CCR_CONFIG_DIR',NULL,FALSE,l_user_dropped);

64 -- Drop current OCM user

65 drop_user('ORACLE_OCM','ORACLE_OCM_CONFIG_DIR','ORACLE_OCM_CONFIG_DIR2',TRUE,l_user_dropped);

66 if( l_user_dropped ) THEN

67 DBMS_OUTPUT.PUT_LINE('Dropped previous OCM schema successfully.');

68 ELSE

69 DBMS_OUTPUT.PUT_LINE('No previous OCM schema to drop.');

70 END IF;

71 END;

72 /

Dropped previous OCM schema successfully.

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

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

Ref:

OCM ( Oracle Configuration Manager ) and My Oracle Support : FAQ and Troubleshooting (Doc ID 369619.1)

Upgrading Oracle Configuration Manager (OCM) within E-Business Suite Release 12.0 (Doc ID 727157.1)

ORA-00245: control file backup operation failed , RMAN-03009

2014-05-06T15:28:00.000-07:00

Error:

RMAN-03009: failure of backup command on disk4 channel at 05/06/2014 13:35:20
ORA-00245: control file backup operation failed
continuing other job steps, job failed will not be re-run
channel disk3: finished piece 1 at 06-MAY-2014 13:35:21
piece handle=/RMAN/Hot/SOA/SOA_20140506/DB_20140506_0kp7jv4o_1_1 tag=FULL_20140506 comment=NONE
channel disk3: backup set complete, elapsed time: 00:00:01
channel disk1: finished piece 1 at 06-MAY-2014 13:35:55
piece handle=/RMAN/Hot/SOA/SOA_20140506/DB_20140506_0gp7jv4n_1_1 tag=FULL_20140506 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:36
channel disk2: finished piece 1 at 06-MAY-2014 13:35:55
piece handle=/RMAN/Hot/SOA/SOA_20140506/DB_20140506_0hp7jv4n_1_1 tag=FULL_20140506 comment=NONE
channel disk2: backup set complete, elapsed time: 00:00:36
released channel: disk1
released channel: disk2
released channel: disk3
released channel: disk4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 05/06/2014 13:35:55
RMAN-03009: failure of backup command on disk4 channel at 05/06/2014 13:35:20
ORA-00245: control file backup operation failed
RMAN> RMAN>
Recovery Manager complete.
2014/05/06 13:35:56 ERROR: RMAN FULL backup Failed

Symptoms:

In RAC environment any form of controlfile backup may fail with ORA-0245 if the location of the Snapshot Controlfile is not a shared location.

The backup of the controlfile actualy makes a backup of the SNAPSHOT controlfile. The Snapshot controlfile is created when the controlfile is about to be backed up.

The Snapshot controlfile is a read-consistent copy of the controlfile.

Solution:

This is a RAC specific 'configuration' issue and the correct configuration is as described below

It is changed behaviour which requires that the snapshot controlfile in a RAC environment, is on a shared location.

1. Check the snapshot controlfile location:

RMAN> show snapshot controlfile name;
RMAN configuration parameters for database with db_unique_name SOAPROD are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d06/soa/product/product/11.2.0/dbs/snapcf_SOA1.f'; # default

2. Configure the snapshot controlfile to a shared disk:

RMAN> RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/RMAN/Hot/SOA/snapcf_SOA1.f';

Or in case of ASM use

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+<DiskGroup>/snapcf_<DBNAME>.f';

Ref: ORA-245: In RAC environment from 11.2 onwards Backup Or Snapshot controlfile needs to be in shared location (Doc ID 1472171.1)

RMAN-06026: some targets not found - aborting restore & RMAN-03002 while restoring

2014-03-07T23:18:00.000-08:00

While doing restore database I got below errors:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/07/2014 22:17:33
RMAN-06026: some targets not found - aborting restore

Cause: I unexpectedly RMAN> catalog start with '/nfs/orafra/prod/PROD_C/backupset/2014_03_08/' noprompt; which is not yet available

It cataloged very old files as below

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /nfs/orafra/webdb0/STG3/archivelog/PROD/archivelog/2013_10_11/o1_mf_1_1_95kgmq5h_.arc
File Name: /nfs/orafra/webdb0/STG3/archivelog/PROD/autobackup/2013_10_11/o1_mf_n_828561497_95kgpty2_.bkp

searching for all files that match the pattern /nfs/orafra/prod/PROD_C/backupset/2014_03_08/
no files found to be unknown to the database

Trying to restore the data base using below commands and got mentioned errors

RUN {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
restore database;
recover database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;

}
exit;

Error:

released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/07/2014 22:17:33
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 79 found to restore
RMAN-06023: no backup or copy of datafile 78 found to restore

Solution Followed:

RMAN> list incarnation of database;

List of Database Incarnations
DB Key Inc Key DB Name DB ID            STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD 3555113110       PARENT 8242472798304 24-JUL-09
2       2       PROD 3555113110       PARENT 8242490168035 25-JUL-09
3       3     PROD 3555113110       PARENT 8506956585999 03-OCT-13
4       4     PROD 3555113110       CURRENT 8507435956232 11-OCT-13

RMAN> reset database to incarnation 3;

database reset to incarnation 3

RMAN> list incarnation of database;

List of Database Incarnations
DB Key Inc Key DB Name DB ID            STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1    PROD 3555113110       PARENT 8242472798304 24-JUL-09
2       2      PROD 3555113110       PARENT 8242490168035 25-JUL-09
3       3       PROD 3555113110       CURRENT 8506956585999 03-OCT-13
4       4     PROD 3555113110       ORPHAN 8507435956232 11-OCT-13

RMAN> exit
and initiated the restore with no issues

Scripts to find USED,FREE and total DataBase sizes

2013-12-12T16:25:00.003-08:00

--> Script to find Total DB size:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes)/1024/1024/1024,0) temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c;

--> Script to find Total and Free DB sizes:

col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 /1024) || ' GB' "Database Size"
, round(free.p / 1024 / 1024 /1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/

--> Script to find USED DB size:

SELECT round(SUM(bytes)/1024/1024/1024) "GB" FROM dba_segments;

--> Script to report true free space within the used portion of the TEMPFILE:

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

RC:crs services are not coming up after removal OS user - CRS-0184: Cannot communicate with the CRS daemon after reboot

2013-12-12T15:56:00.000-08:00

Steps performed in CRS startup issue and its resolution after OS user (ORATEST) removal:
--> Error message: the message lists the serverpool, listener and data type entries for user “ORATEST”
[SYSTEM.CRSD.TYPES.ora!database!type.TYPE_ACL.CONFIG]
ORATEXT : DEFAULT=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~FLAGS=_CONFIG~ID=1078~TYPE=_STRING~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost1!lsnr]
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost1!lsnr.CONFIG]
ORATEXT : ACL=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=/d06/ORATEST/product/11.2.0/bin/racgwrap%CRS_SCRIPT_SUFFIX%~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%~ALIAS_NAME=ora.%CRS_CSS_NODENAME_LOWER_CASE%.LISTENER_ORATEST_myhost1_%CRS_CSS_NODENAME_UPPER_CASE%.lsnr~AUTO_START=never~BASE_TYPE=ora.local_resource.type~CHECK_INTERVAL=60~CHECK_TIMEOUT=30~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=listener) PROPERTY(LISTENER_NAME=PARSE(%NAME%, ., 2))~DEGREE=1~DESCRIPTION=Oracle Listener resource~ENABLED=1~ENDPOINTS=TCP:1521~LOAD=1~LOGGING_LEVEL=1~NAME=ora.LISTENER_ORATEST_myhost1.lsnr~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/d06/ORATEST/product/11.2.0~PORT=1521~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60~START_DEPENDENCIES=hard(type:ora.cluster_vip_net1.type) pullup(type:ora.cluster_vip_net1.type)~START_TIMEOUT=180~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=0~TYPE=ora.listener.type~TYPE_ACL=owner:crstest:rwx,pgrp:oinstall:rwx,other::r--~TYPE_VERSION=1.2~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_OPI=false~VERSION=11.2.0.2.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

..
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost2!lsnr.CONFIG]
ORATEXT : ACL=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=/d06/ORATEST/product/11.2.0/bin/racgwrap%CRS_SCRIPT_SUFFIX%~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%~ALIAS_NAME=ora.%CRS_CSS_NODENAME_LOWER_CASE%.LISTENER_ORATEST_myhost2_%CRS_CSS_NODENAME_UPPER_CASE%.lsnr~AUTO_START=restore~BASE_TYPE=ora.local_resource.type~CHECK_INTERVAL=60~CHECK_TIMEOUT=30~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=listener) PROPERTY(LISTENER_NAME=PARSE(%NAME%, ., 2))~DEGREE=1~DESCRIPTION=Oracle Listener resource~ENABLED=1~ENDPOINTS=TCP:1521~LOAD=1~LOGGING_LEVEL=1~NAME=ora.LISTENER_ORATEST_myhost2.lsnr~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/d06/ORATEST/product/11.2.0~PORT=1521~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60~START_DEPENDENCIES=hard(type:ora.cluster_vip_net1.type) pullup(type:ora.cluster_vip_net1.type)~START_TIMEOUT=180~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=0~TYPE=ora.listener.type~TYPE_ACL=owner:crstest:rwx,pgrp:oinstall:rwx,other::r--~TYPE_VERSION=1.2~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_OPI=false~VERSION=11.2.0.2.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

--> The commands to stop and delete the server pool associated with ORATEST.

[root@myhost1 ~]# cd $GRID_HOME/bin
./crsctl stop resource ora.ORATEST.db
./crsctl delete resource ora.ORATEST.db
./crsctl delete serverpool ora.ORATEST
./crsctl getperm serverpool ora.ORATEST
./crsctl status serverpool

--> Commands to remove the ORATEST listeners
[root@myhost1 bin]# ./srvctl remove listener -l LISTENER_ORATEST_myhost1
[root@myhost1 bin]# ./srvctl remove listener -l LISTENER_ORATEST_myhost2
[root@myhost1 bin]# ./srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): myhost1,myhost2

--> Commands executed during action to delete data type

1. Check database type
# ./crsctl status type |grep database
TYPE_NAME=ora.database.type

2. Check/Remove database
cd $ORACLE_HOME/bin
$srvctl config database -d ORATEST
$srvctl remove database -d db_name

3. Delete database type resource
cd $GRID_HOME/bin
# ./crsctl delete type ora.database.type
# ./crsctl status type |grep database
/d01/crstest/product/11.2.0/bin/crsctl status type ora.database.type -p | grep DEFAULT_VALUE=owner
4. Get ocrdump With ROOT user
cd $GRID_HOME/bin
./ocrdump /tmp/ocrdump_after.log
# cat /tmp/ocrdump_after.log|grep -i ORATEST
5. Add database
Syntax --
$ ./srvctl add database -d <db_name> -p <Spfile> -o<Oracle_home>
$ ./srvctl add instance -d <db_name> -i <instance_name> -n <node>

Commands --
./srvctl add database -d VAL -o /d01/oraval/product/10.2.0
./srvctl add instance -d VAL -i VAL1 -n myhost1
./srvctl add instance -d VAL -i VAL2 -n myhost2

6. Get ocrdump.
./ocrdump /tmp/ocrdump_withdb.log
7. Stop/Start CRS (As OS user already deleted)
[root@myhost2 bin]# id ORATEST
id: ORATEST: No such user
#crsctl stop crs
#crsctl start crs
8. Stop crs/reboot node
#crsctl stop crs
9. Reboot nodes.
10. Check CRS services:
[root@myhost1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE myhost1
ONLINE ONLINE myhost2
ora.asm
OFFLINE OFFLINE myhost1
OFFLINE OFFLINE myhost2
ora.gsd
OFFLINE OFFLINE myhost1
OFFLINE OFFLINE myhost2
ora.net1.network
ONLINE ONLINE myhost1
ONLINE ONLINE myhost2
ora.ons
ONLINE ONLINE myhost1
ONLINE ONLINE myhost2
ora.registry.acfs
OFFLINE OFFLINE myhost1
OFFLINE OFFLINE myhost2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE myhost2
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE myhost1
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE myhost1
ora.VAL.VAL1.inst
1 ONLINE ONLINE myhost1
ora.VAL.VAL2.inst
1 ONLINE ONLINE myhost2
ora.VAL.db
1 ONLINE ONLINE myhost1
ora.cvu
1 ONLINE ONLINE myhost1
ora.oc4j
1 ONLINE ONLINE myhost1
ora.myhost1.LISTENER_VAL_myhost1.lsnr
1 ONLINE ONLINE myhost1
ora.myhost1.vip
1 ONLINE ONLINE myhost1
ora.myhost2.LISTENER_VAL_myhost2.lsnr
1 ONLINE ONLINE myhost2
ora.myhost2.vip
1 ONLINE ONLINE myhost2
ora.scan1.vip
1 ONLINE ONLINE myhost2
ora.scan2.vip
1 ONLINE ONLINE myhost1
ora.scan3.vip
1 ONLINE ONLINE myhost1

APPL_ID	SHORTNAME	LONGNAME	LANGUAGE	PATCHLEVEL	INST
869	ASP	Oracle Sales for Handhelds	US	R12.ASP.B.3	I
178	ICX	Oracle iProcurement	US	R12.ICX.B.3	I
697	ASO	Order Capture	US	R12.ASO.B.3	I
660	ONT	Order Management	US	R12.ONT.B.3	I
200	SQLAP	Payables	US	R12.AP.B.3	I
673	IBY	Payments	US	R12.IBY.B.3	I
555	GMF	Process Manufacturing Financials	US	R12.GMF.B.3	I
558	PMI	Process Manufacturing Intelligence	US	R12.PMI.B.3	I
551	GMI	Process Manufacturing Inventory	US	R12.GMI.B.3	I
556	GML	Process Manufacturing Logistics	US	R12.GML.B.3	I
553	GME	Process Manufacturing Process Execution	US	R12.GME.B.3	I
554	GMP	Process Manufacturing Process Planning	US	R12.GMP.B.3	I
552	GMD	Process Manufacturing Product Developmen	US	R12.GMD.B.3	I
557	GR	Process Manufacturing Regulatory Managem	US	R12.GR.B.3	I
550	GMA	Process Manufacturing Systems	US	R12.GMA.B.3	I
455	ENI	Product Intelligence	US	R12.ENI.B.3	I
1292	PJI	Project Intelligence	US	R12.PJI.B.3	I
712	PJM	Project Manufacturing	US	R12.PJM.B.3	I
275	PA	Projects	US	R12.PA.B.3	I
240	PN	Property Manager	US	R12.PN.B.3	I
8401	PSB	Public Sector Budgeting	US	R12.PSB.B.3	I
8450	PSA	Public Sector Financials	US	R12.PSA.B.3	I
8400	IGI	Public Sector Financials International	US	R12.IGI.B.3	I
8302	PQH	Public Sector HR	US	R12.PQH.B.3	I
201	PO	Purchasing	US	R12.PO.B.3	I
452	POA	Purchasing Intelligence	US	R12.POA.B.3	I
250	QA	Quality	US	R12.QA.B.3	I
880	QOT	Quoting	US	R12.QOT.B.3	I
222	AR	Receivables	US	R12.AR.B.3	I
265	FRM	Report Manager	US	R12.FRM.B.3	I
804	SSP	SSP	US	R12.SSP.B.3	I
280	ASN	Sales	US	R12.ASN.B.3	I
279	AS	Sales Foundation	US	R12.AS.B.3	I
676	BIL	Sales Intelligence	US	R12.BIL.B.3	I
544	ASL	Sales Offline	US	R12.ASL.B.3	I
522	ASF	Sales Online	US	R12.ASF.B.3	I
519	IES	Scripting	US	R12.IES.B.3	I
661	QP	Advanced Pricing	US	R12.QP.B.3	I
724	MSC	Advanced Supply Chain Planning	US	R12.MSC.B.3	I
160	ALR	Alert	US	R12.ALR.B.3	I
190	AZ	Application Implementation	US	R12.AZ.B.3	I
0	FND	Application Object Library	US	R12.FND.B.3	I
168	RG	Application Report Generator	US	R12.RG.B.3	I
191	BIS	Applications BIS	US	R12.BIS.B.3	I
873	CSE	Asset Tracking	US	R12.CSE.B.3	I
140	OFA	Assets	US	R12.FA.B.3	I
702	BOM	Bills of Material	US	R12.BOM.C.3	I
690	JTF	CRM Foundation	US	R12.JTF.B.3	I
689	ASG	CRM Gateway for Mobile Devices	US	R12.ASG.B.3	I
705	CRP	Capacity	US	R12.CRP.B.3	I
260	CE	Cash Management	US	R12.CE.B.3	I
866	CUG	Citizen Interaction Center	US	R12.CUG.B.3	I
601	AK	Common Modules-AK	US	R12.AK.B.3	I
708	CZ	Configurator	US	R12.CZ.B.3	I
723	MSO	Constraint Based Optimization	US	R12.MSO.B.3	I
549	IBC	Content Manager	US	R12.IBC.B.3	I
510	OKC	Contracts Core	US	R12.OKC.B.3	I
524	OKX	Contracts Integration	US	R12.OKX.B.3	I
870	OKI	Contracts Intelligence	US	R12.OKI.B.3	I
511	CSC	Customer Care	US	R12.CSC.B.3	I
518	BIC	Customer Intelligence (obsolete)	US	R12.BIC.B.3	I
722	MSD	Demand Planning	US	R12.MSD.B.3	I
512	CSD	Depot Repair	US	R12.CSD.B.3	I
235	ZX	E-Business Tax	US	R12.ZX.B.3	I
709	EDR	E-Records	US	R12.EDR.B.3	I
703	ENG	Engineering	US	R12.ENG.C.3	I
8901	FV	Federal Financials	US	R12.FV.B.3	I
513	CSF	Field Service	US	R12.CSF.B.3	I
450	FII	Financial Intelligence	US	R12.FII.B.3	I
101	SQLGL	General Ledger	US	R12.GL.B.3	I
600	AX	Global Accounting Engine	US	11i.AX.J	I
283	CN	Incentive Compensation	US	R12.CN.B.3	I
542	CSI	Install Base	US	R12.CSI.B.3	I
539	IEO	Interaction Center Technology	US	R12.IEO.B.3	I
170	CS	Service	US	R12.CS.B.3	I
515	OKS	Service Contracts	US	R12.OKS.B.3	I
862	BIV	Service Intelligence	US	R12.BIV.B.3	I
665	WSH	Shipping Execution	US	R12.WSH.B.3	I
202	CHV	Supplier Scheduling	US	R12.CHV.B.3	I
454	ISC	Supply Chain Intelligence	US	R12.ISC.B.3	I
1	SYSADMIN	System Administration	US		I
521	AST	TeleSales	US	R12.AST.B.3	I
8301	GHR	US Federal Human Resources	US	R12.GHR.B.3	I
696	IEU	Universal Work Queue	US	R12.IEU.B.3	I
385	WMS	Warehouse Management	US	R12.WMS.B.3	I
231	BNE	Web Applications Desktop Integrator	US	R12.BNE.B.4	I
706	WIP	Work in Process	US	R12.WIP.B.3	I
174	ECX	XML Gateway	US	R12.ECX.B.3	I
603	XDO	XML Publisher	US	R12.XDO.B.3	I
175	EC	e-Commerce Gateway	US	R12.EC.B.3	I
205	IA	iAssets	US	R12.IA.B.3	I
177	POS	iSupplier Portal	US	R12.POS.B.3	I
401	INV	Inventory	US	R12.INV.B.3	I
530	AMS	Marketing	US	R12.AMS.B.3	I
517	BIM	Marketing Intelligence	US	R12.BIM.B.3	I
704	MRP	Master Scheduling/MRP	US	R12.MRP.B.3	I
874	JTM	Mobile Application Foundation	US	R12.JTM.B.3	I
405	MWA	Mobile Applications	US	R12.MWA.B.3	I
534	XNP	Number Portability	US	R12.XNP.B.3	I
451	OPI	Operations Intelligence	US	R12.OPI.B.3	I
278	IZU	Oracle E-Business Suite Diagnostics	US	R12.IZU.B.3	I
545	IEC	Advanced Outbound Telephony	US	R12.IEC.B.3	N
431	EGO	Advanced Product Catalog	US	R12.EGO.C.3	N
203	AME	Approvals Management	US	R12.AME.B.3	N
7000	JA	Asia/Pacific Localizations	US	R12.JA.B.3	N
663	VEA	Automotive	US	R12.VEA.B.3	N
271	BSC	Balanced Scorecard	US	R12.BSC.B.3	N
674	IBP	Bill Presentment & Payment	US		N
430	DDD	CADView-3D	US	R12.DDD.C.3	N
875	JTS	CRM Self Service Administration	US	11i.JTS.B	N
8724	CUF	Capital Resource Logistics - Financials	US		N
8721	IPA	Capital Resource Logistics - Projects	US	R12.IPA.B.3	N
695	IEX	Collections	US	R12.IEX.B.3	N
867	AHL	Complex Maintenance Repair and Overhaul	US	R12.AHL.B.3	N
8407	IGC	Contract Commitment	US		N
879	IMC	Customers Online	US	R12.IMC.B.3	N
9003	DDR	Demand Signal Repository	US	R12.DDR.B.3	N
429	DNA	Development	US	R12.DNA.C.3	N
432	DOM	Document Managment and Collaboration	US	R12.DOM.B.3	N
426	EAM	Enterprise Asset Management	US	R12.EAM.B.3	N
274	FEM	Enterprise Performance Foundation	US	R12.FEM.B.3	N
210	ZPB	Enterprise Planning and Budgeting	US	R12.ZPB.B.3	N
7002	JE	European Localizations	US	R12.JE.B.3	N
298	POM	Exchange	US	R12.POM.B.3	N
868	CSL	Field Service/Laptop	US	R12.CSL.B.3	N
883	CSM	Field Service/Palm	US	R12.CSM.B.3	N
8406	IGF	Financial Aid	US		N
505	ZFA	Financial Analyzer	US		N
266	GCS	Financial Consolidation Hub	US	R12.GCS.B.3	N
435	FUN	Financials Common Modules	US	R12.FUN.B.3	N
8402	GMS	Grants Accounting	US	R12.GMS.I	N
8404	IGW	Grants Proposal	US	R12.IGW.B.3	N
438	ITA	Information Technology Audit	US	R12.ITA.B.3	N
699	IEB	Interaction Blending	US	R12.IEB.B.3	N
242	AMW	Internal Controls Manager	US	R12.AMW.B.3	N
230	ITG	Internet Procurement Enterprise Connecto	US	R12.ITG.B.3	N
410	WSM	Shop Floor Management	US	R12.WSM.B.3	N
718	RRS	Site Management	US	R12.RRS.B.3	N
396	PON	Sourcing	US	R12.PON.B.3	N
8405	IGS	Student System	US	R12.IGS.A	N
778	JMF	Supply Chain Localizations	US	R12.JMF.B.3	N
701	CLN	Supply Chain Trading Connector for Roset	US	R12.CLN.C.3	N
808	HXT	Time and Labor	US	R12.HXT.B.3	N
809	HXC	Time and Labor Engine	US	11i.HXC.C	N
682	OZF	Trade Management	US	R12.OZF.B.3	N
508	FTP	Transfer Pricing	US	R12.FTP.B.3	N
716	FTE	Transportation Execution	US	R12.FTE.B	N
390	MST	Transportation Planning	US	R12.MST.B	N
185	XTR	Treasury	US	R12.XTR.B.3	N
821	IRC	iRecruitment	US	R12.IRC.B.3	N
672	IBU	iSupport	US	R12.IBU.B.3	N
726	MSR	Inventory Optimization	US	R12.MSR.B.3	N
7004	JL	Latin America Localizations	US	R12.JL.B.3	N
810	OTA	Learning Management	US	R12.OTA.B.3	N
540	OKL	Lease and Finance Management	US	R12.OKL.B.3	N
204	XLE	Legal Entity Configurator	US	R12.XLE.B.3	N
206	LNS	Loans	US	R12.LNS.B.3	N
560	GMO	Manufacturing Execution System for Proce	US	R12.GMO.B.3	N
388	WPS	Manufacturing Scheduling	US	R12.WPS.B.3	N
8727	CUS	Network Logistics	US		N
8722	CUI	Network Logistics - Inventory	US		N
8723	CUP	Network Logistics - Purchasing	US		N
667	QPR	Oracle Deal Management	US	R12.QPR.C.3	N
207	IPM	Oracle Imaging Process Management	US		N
9004	INL	Oracle Landed Cost Management	US	R12.INL.B.3	N
9001	MTH	Oracle Manufacturing Operations Center	US	R12.MTH.B.3	N
9000	DPP	Oracle Price Protection	US	R12.DPP.B.3	N
507	PFT	Oracle Profitability Manager	US	R12.PFT.B.3	N
881	XNB	Oracle Telecommunications Billing Integr	US	R12.XNB.B.3	N
666	IBW	Oracle Web Analytics	US	R12.IBW.B.3	N
300	OE	Order Entry	US	R12.OE.B.3	N
691	PV	Partner Management	US	R12.PV.B.3	N
777	OKE	Project Contracts	US	R12.OKE.H	N
440	FPA	Project Portfolio Analysis	US	R12.FPA.B.3	N
694	PRP	Proposals	US	R12.PRP.B.3	N
8303	PQP	Public Sector Payroll	US	R12.PQP.B.3	N
7003	JG	Regional Localizations	US	R12.JG.B.3	N
662	RLM	Release Management	US	R12.RLM.B.3	N
186	QRM	Risk Management	US	R12.QRM.B.3	N
506	ZSA	Sales Analyzer	US		N
698	CSR	Scheduler	US	R12.CSR.B.3	N
805	BEN	Advanced Benefits	US	R12.BEN.B.3	S
535	XDP	Provisioning	US	R12.XDP.B.3	S
50	AD	Applications DBA	US	R12.AD.B.3	S
60	SHT	Applications Shared Technology	US	R12.SHT.B.3	S
8731	CUA	Capital Resource Logistics - Assets	US		S
803	DT	DateTrack	US	R12.DT.B.3	S
680	IEM	Email Center	US	R12.IEM.B.3	S
802	FF	FastFormula	US	R12.FF.B.3	S
714	FLM	Flow Manufacturing	US	R12.FLM.B.3	S
800	PER	Human Resources	US	R12.PER.B.3	S
453	HRI	Human Resources Intelligence	US	R12.HRI.B.3	S
677	BIX	Interaction Center Intelligence	US	R12.BIX.B.3	S
523	CSP	Spares Management	US	R12.CSP.B.3	S
602	XLA	Subledger Accounting	US	R12.XLA.B.3	S
172	CCT	Telephony Manager	US	R12.CCT.B.3	S
671	IBE	iStore	US	R12.IBE.B.3	S
8403	PSP	Labor Distribution	US	R12.PSP.B.3	S
700	MFG	Manufacturing	US	R12.MFG.B.3	S
520	AMV	Marketing Encyclopedia System	US	R12.AMV.B.3	S
801	PAY	Payroll	US	R12.PAY.B.3	S
3	AU	Application Utilities	US		S
41	PTX	Patch Tracking System	US
20003	XPIP	Pip Application	US
20023	XXPPS	Custom Applications	US
548	IEP	Predictive	US
559	GMW	Process Manufacturing Portal	US
420	IPD	Product Development (obsolete)	US
20139	PSR	Public Sector Receivables	US
437	RCM	Regulatory Capital Manager (obsolete)	US
710	RLA	Release Management Integration Kit (Obso	US
719	RRC	Retail Core	US
8726	CUC	Revenue Accounting	US
509	RMG	Risk Manager	US
877	OKT	Royalty Management	US
270	EAA	SEM Exchange (obsolete)	US
501	AN	Sales Analysis	US
532	XNC	Sales for Communications (Obsolete)	US
13017	CLA	APAC Consulting Localizations	US
272	ABM	Activity Based Management (Obsolete)	US
725	RHX	Advanced Planning Foundation(obsolete)	US
20043	BPA	Automate	US
711	VEH	Automotive Integration Kit (Obsolete)	US
538	FPT	Banking Center (obsolete)	US
543	CUE	Billing Connect (obsolete)	US
502	PBR	Budgeting and Planning	US
20160	OKC_REP_TX	Build/syncronize Contracts Repository Te	US
20090	CPGC	CPG - CDOA	US
20180	CSN	Call Center	US
547	IET	Call Center Connectors	US
3002	CTB	Clinical Transaction Base	US
679	BIN	Communications Intelligence	US
876	OKP	Contracts for Procurement (Obsolete)	US
541	OKR	Contracts for Rights (Obsolete)	US
871	OKO	Contracts for Sales (Obsolete)	US
865	OKB	Contracts for Subscriptions (Obsolete)	US
516	ME	Controlled Availability Product(Obsolete	US
707	CST	Cost Management	US
525	ODQ	Data Query	US
878	IAM	Digital Asset Management	US
13014	CLE	EMEA Consulting Localizations	US
40	EMS	Environment Management System	US
882	AMF	Fulfillment Services (Obsolete)	US
747	GNI	Genealogy Intelligence	US
3004	HCA	Healthcare	US
3003	HCP	Healthcare Intelligence	US
3005	HCT	Healthcare Terminology Server	US
864	AHM	Hosting Manager(Obsolete)	US
546	IEV	IVR Integrator	US
872	XNI	Install Base Intelligence (Obsolete)	US
536	XNA	Service Assurance for Communications	US
533	XNS	Service for Communications (obsolete)	US
514	CSS	Support (obsolete)	US
863	BIY	Systems Intelligence	US
20200	DEM01	Team 01 Order Entry Demo	US
537	XNT	TeleBusiness for Telecom/Utilities	US
681	OZP	Trade Planning (Obsolete)	US
20114	OUC	University Curriculum	US
436	BLC	Utility Billing	US
273	EVM	Value Based Management	US
678	BIE	eCommerce Intelligence	US
675	IBT	iAuction	US
683	OZS	iClaims (Obsolete)	US
3000	HCC	iHCConnect	US
3001	HCN	iHCIntegrate	US
670	IBA	iMarketing (Obsolete)	US
861	IMT	iMeeting (obsolete)	US
415	ISX	iSettlement	US
20159	TEST	test	US
13016	CLJ	Japan Consulting Localizations	US
13015	CLL	LAD Consulting Localizations	US
407	WMA	Manufacturing Mobile Applications	US
531	XNM	Marketing for Communications (Obsolete)	US
8725	CUR	Mass Market Receivables for Comms	US
860	MIV	Media Interactive	US
403	MIA	Mobile Applications for Inventory Manage	US
425	MQA	Mobile Quality Applications	US
8729	CUN	Network Logistics - NATS (obsolete)	US
1776	DUMMY_GMO	Obsolete Process Operations	US
20161	OKC_REP_TX	Optimize Contracts Repository Text index	US
99	OAM	Oracle Applications Manager	US
3006	CDR	Oracle Clinical Data Repository	US