Tracing in Oracle 10g

Trace in Oracle 10g


SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');

SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running', -
> waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');

Updated Metalink Notes

Updated Metalink Note:

NOTE.74660.1 : Resolving Invalid Objects in Oracle Applications
NOTE.338879.1 : Landscape1309 - Linux Landscape Quick Reference
NOTE.341782.1 : Linux Quick Reference
NOTE.312572.1 : About Oracle Applications Technology Updates for Release 11.
NOTE.316806.1 : Oracle Applications Installation Update Notes, Release 11i
NOTE.370274.1 : New Features in Oracle Application 11i
NOTE.275734.1 : India Localization
NOTE.47837.1 : Applications Utilities FAQ
NOTE.189487.1 : System Administration FAQ's
NOTE.289786.1 : Installing Oracle Applications: A Guide to Using Rapid
NOTE.245079.1 : Steps to clone a 11i RAC environment to a non-RAC
NOTE.243693.1 : Rapid Clone Coredumps when Running Adcfgclone.pl
NOTE.231701.1 : How to Find Patching History (10.7, 11.0, 11i)
NOTE.139684.1 : Oracle Applications Current Patchset Comparison Utility -
NOTE.331746.1 : Oracle Accelerator FAQ for Global Product Support (Internal
NOTE.144751.1 : Applications Patching - Time Saving Techniques
NOTE.232833.1 : Oracle Applications Release Notes, Release 11i (11.5.9)
NOTE.110372.1 : 11i: How to Clean up the UNIX Environment After Install
NOTE.156219.1 : Net8i/9i Basic configuration of LISTENER.ORA and TNSNAMES.
NOTE.69725.1 : Configuring TNSNAMES.ORA, SQLNET.ORA,and LISTENER.ORA in
NOTE.356878.1 : How to relink the whole Applications 11i Installation
NOTE.233428.1 : Sharing the Application Tier File System in Oracle
NOTE.130686.1 : How to Generate Form, Library and Menu for Oracle
NOTE.246105.1 : Upgrading to J2SE 1.4.2 with Oracle Applications 11i
NOTE.139949.1 : NEED TO CLEAR APACHE, BROWSER OR JINITIATOR CACHE FOR
NOTE.133972.1 : How to Reset the APPS and APPLSYS Passwords in Release 11.5
NOTE.216980.1 : FNDLOAD Fails with PLS-306 in call to LOAD_ROW
NOTE.333785.1 : Oracle Applications Internationalization Guide
NOTE.222663.1 : Internationalization Update Notes for the Oracle E-Business Suite 11i
NOTE.372952.1 : Customer Translations
NOTE.168330.1 : Reload Applications Related Java Objects
NOTE.287176.1 : DMZ Configuration with Oracle E-Business Suite 11i
NOTE.233436.1 : Installing Oracle Application Server 10g with Oracle E-
NOTE.231701.1 : How to Find Patching History (10.7, 11.0, 11i)
NOTE.139684.1 : Oracle Applications Current Patchset Comparison Utility -
NOTE.238276.1 : Migrating to Linux with Oracle Applications Release 11i
NOTE.242480.1 : Using a Staged Applications 11i System to Reduce Patching
NOTE.1076329.6 : SQL*Plus Errors - SP1.MSB Not Found
NOTE.248857.1 : Oracle Applications Tablespace Model Release 11i -
NOTE.269293.1 : Oracle Applications Tablespace Model FAQs
NOTE.258330.1 : About Oracle Applications Manager Minipack 11i.OAM.H
NOTE.268837.1 : Gathering Debug Weboam Log
NOTE.342332.1 : Troubleshooting Login Problems in Oracle Applications 11i
NOTE.112577.1 : How to customize reports at runtime using XML - simple
NOTE.144689.1 : How to Generate a Report (.rdf) File from the UNIX Command
NOTE.211424.1 : How to Enable a Large SGA(over 1.7GB) on RedHat Advanced
NOTE.345145.1 : Is There A Way To Automate The Prompts For Adcfgclone.Pl?
NOTE.1812.1 : TECH : Getting a Stack Trace from a CORE file
NOTE.144599.1 : How to Generate a .pll Library File for Applications 11i
NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g
NOTE.208375.1 : How To Convert A Single Instance Database To RAC In A
NOTE.279956.1 : Oracle E-Business Suite Release 11i with 9i RAC :
NOTE.312731.1 : Configuring Oracle Applications Release 11i with 10g RAC
NOTE.362135.1 : Configuring Oracle Applications Release 11i with 10g R2 RAC
NOTE.1067473.6 : Custom Forms do not Show up in FNDSCMON Form
NOTE.232834.1 : Oracle Applications Release 11.5.9 Maintenance Pack
NOTE.216550.1 : Oracle Applications Release 11i with Oracle9i Release 2 (9.
NOTE.362203.1 : Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
NOTE.233038.1 : AD Command Line Options
NOTE.186125.1 : Applications 11i and Standby Databases
NOTE.260887.1 : Steps to Clean Nonexistent Nodes or IP Addresses from
NOTE.342459.1 : Diagnostics Overview
NOTE.394615.1 : Adaddnode.Pl failed with : ORA-00001 : unique constraint
NOTE.338003.1 : How to change the hostname and/or port of the Database Tier
NOTE.341322.1 : How to change the hostname of an Applications Tier using AutoConfig
NOTE.333785.1 : Oracle Applications Internationalization Guide
NOTE.211708.1 : Detailed Explanations of How NLS/MLS is Being Handled in 11i
NOTE.73352.1 : NLS/MLS Frequently Asked Questions
NOTE.72324.1 : Guidelines on Resolving NLS/MLS issues
NOTE.316365.1 : Oracle Applications Release 11.5.10.2 Maintenance Pack
NOTE.230627.1 : 9i Export/Import Process for Oracle Applications Release 11i
NOTE.331221.1 : 10g Export/Import Process for Oracle Applications Release 11i
NOTE.362205.1 : 10g Release 2 Export/Import Process for Oracle Applications Release 11i
NOTE.259552.1 : 11.5.9 Category 3 Preupgrade Instructions for 9.2.0.4
NOTE.341281.1 : How to disable the language selection option available in AppsLocalLogin.jsp
NOTE.389472.1 : OATM Migration fails with ORA-22853 for LOB objects
NOTE.174605.1 : bde_chk_cbo.sql - Reports Database Initialization
NOTE.333785.1 : Oracle Applications Internationalization Guide
NOTE.124721.1 : Migrating an Applications Installation to a New Character
NOTE.66320.1 : Changing the Database Character Set or the Database
NOTE.119164.1 : Changing Database Character Set - Valid Superset Definitions
NOTE.123670.1 : Use Scanner Utility before Altering the Database Character
NOTE.213015.1 : SYS.METASTYLESHEET marked as having convertible data (ORA-
NOTE.258895.1 : SYS.RULE$ marked as having convertible data (ORA-12716 when
NOTE.258902.1 : SYS.JOB$ marked as having convertible data
NOTE.43208.1 : Certified Compilers
NOTE.270806.1 : 11.5.9 : Invalid Objects - IES Java Classes
NOTE.165114.1 : Resolving Invalid Java Classes in Oracle Applications
NOTE.343253.1 : Tns-12555, Tns-12560, Tns-00525, Linux Error : 1 Starting
NOTE.150168.1 : Obtaining Forms Runtime Diagnostics (FRD) In Oracle
NOTE.210193.1 : Use of Multiple Organizations In Oracle Applications
NOTE.165035.1 : Current Issues - Multiple Organizations Architecture
NOTE.208267.1 : Improved Convert to Multi-Org Process In Oracle
NOTE.226456.1 : Multiple Organizations in Oracle Applications R11i, March
NOTE.259546.1 : Setting Up Multiple Organizations in Oracle HRMS
NOTE.131081.1 : How can I find which version of Portal I am running ?
NOTE.262125.1 : Sun.io.MalformedInputException For OraSCV.asc,emreadme.txt,
NOTE.216205.1 : Database Initialization Parameters for Oracle Applications
NOTE.213015.1 : SYS.METASTYLESHEET marked as having convertible data (ORA-
NOTE.283225.1 : How to Recreate the Listener for Event and Listener for
NOTE.153960.1 : FAQ : X Server testing and troubleshooting
NOTE.146468.1 : Installing and Upgrading Oracle9i Application Server with
NOTE.233428.1 : Sharing the Application Tier File System in Oracle
NOTE.351283.1 : Interoperability Notes : Oracle Applications Release 11i
NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for
NOTE.317226.1 : Concurrent Managers Do Not Start With GSM Profile Option
NOTE.311015.1 : Adgendbc.sh Errors Running AutoConfig
NOTE.337937.1 : Step By Step - 10gR2 RAC with ASM install on Linux(x86) -
NOTE.266043.1 : Support of Linux and Oracle Products on Linux
NOTE.224302.1 : Raw Devices on Linux
NOTE.134395.1 : Cannot Connect to Personal Home Page : Your Session is no
NOTE.351646.1 : Configuring Oracle E-Business Suite Release 11i with 10g
NOTE.230688.1 : Basic ApacheJServ Troubleshooting with IsItWorking.class
NOTE.70276.1 : HOW TO INTEGRATE APPLICATIONS RELEASE 11 WITH CUSTOM
NOTE.273449.1 : Diagnosing Login Problems with Apps 11.5.9 (FND.G)
NOTE.139863.1 : Configuring and Troubleshooting the Self Service Framework
NOTE.342332.1 : Troubleshooting Login Problems in Oracle Applications 11i
NOTE.233428.1 : Sharing the Application Tier File System in Oracle
NOTE.252422.1 : Requesting Translation Synchronization Patches
NOTE.287176.1 : DMZ Configuration with Oracle E-Business Suite 11i
NOTE.68713.1 : Troubleshooting Self-Service Web Applications Login
NOTE.304489.1 : Using Oracle Applications with a Split Configuration
NOTE.225074.1 : 11i AOL : Cannot login to PHP after DB upgrade to 9.2.0.2
NOTE.358140.1 : Troubleshooting Unix coredumps and obtaining stack traces
NOTE.1007808.6 : HOW TO HANDLE CORE DUMPS ON UNIX
NOTE.1812.1 :
NOTE.169706.1 : Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64
NOTE.197031.1 : 32-bit/64-bit Certification/Conversion Issues on Oracle
NOTE.300172.1 : Obsolescence of KOREAN_LEXER Lexer Type
NOTE.225456.1 : Troubleshooting Guide for Cloning Issues
NOTE.161474.1 : Oracle Applications Remote Diagnostics Agent (APPS_RDA)
NOTE.111383.1 : The Basics About Report Review Agent (FNDFS) on 11i
NOTE.210062.1 : Generic Service Management (GSM) in Oracle Applications 11i
NOTE.316447.1 : About Oracle XML Publisher Release 5.5
NOTE.295036.1 : About XML Publisher Release 5.0
NOTE.130091.1 : Upgrading Oracle Applications 11i to use JDK 1.3
NOTE.292424.1 : Cleaning your Windows System After a Failed Oracle
NOTE.124606.1 : Upgrading JInitiator with Oracle Applications 11i
NOTE.316806.1 : Oracle Applications Installation Update Notes, Release 11i
NOTE.177183.1 : Succesfully Installing NLS/MLS in 11i
NOTE.134007.1 : CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
NOTE.1010501.7 : FAQ : Licensing and de-licensing a product, country-specific
NOTE.217368.1 : Advanced Configurations and Topologies for Enterprise
NOTE.287176.1 : DMZ Configuration with Oracle E-Business Suite 11i
NOTE.302738.1 : Using Virtual Hostnames with Oracle Applications Release 11i
NOTE.403311.1 : United States Time Zone Changes 2007 : E-Business Suite
NOTE.333785.1 : Oracle Applications Internationalization Guide
NOTE.232313.1 : Information on Previous Versions of Developer 6i Patchsets
NOTE.240862.1 : Install Oracle Unicode Fonts for Dynamic Image Generation
NOTE.60966.1 : Getting Rid Of Those Pesky Invalid Objects In Oracle
NOTE.400830.1 : How to Render Non ASCII Characters in Personalized
NOTE.285218.1 : Recommended Browsers for Oracle Applications 11i
NOTE.403311.1 : United States & Canada 2007 Daylight Saving Time (DST)
NOTE.289788.1 : Upgrading Oracle Applications
NOTE.316804.1 : Oracle Applications NLS Release Notes, Release 11i (11.5.10.
NOTE.189867.1 : Troubleshooting FRM-92050, FRM-92100, FRM-92101, FRM-92102
NOTE.253763.1 : After Cloning on Target instance FRM-92050 Failed to
NOTE.274783.1 :

Amol's Bookmark
*********************************************************************
NOTE.167000.1 : eBusiness Suite Support - Oracle Diagnostics Support Pack
NOTE.235307.1 : OSS Application Diagnostics Tools : FAQ and Troubleshooting
NOTE.231142.1 : About Oracle Diagnostics version 2.1
NOTE.262006.1 : About Oracle Diagnostics 2.2
NOTE.300976.1 : Support Diagnostics Newsletter for Applications Core
NOTE.357223.1 : "This test can be executed only after logging into
NOTE.363759.1 : How To Add Diagnostic Tools To Responsibility Menu

Installation
*********

NOTE.215868.1 : 11i Release Notes
NOTE.77219.1 : Applications Release 11.0.3 One-Hour Install for Unix
NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for
NOTE.292424.1 : Cleaning your Windows System After a Failed Oracle
NOTE.110372.1 : 11i : How to Clean up the UNIX Environment After Install
NOTE.169402.1 : How To Cleanup After A Failed Applications Installation On
NOTE.48602.1 : How do I determine which products are fully installed, and
NOTE.124353.1 : WIN : Manually Removing all Oracle Components on Microsoft
NOTE.275493.1 : Removing 10g Database and Software from AIX, HP-UX, Linux,
NOTE.279519.1 : How to completely remove 8i / 9i Database and Software from
NOTE.232831.1. : Oracle Applications NLS Release Notes 11.5.9 - B10846-01
NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for
NOTE.187240.1 : Applications AOL Scenarios : Is It Supported ?
NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for
NOTE.316806.1 : Oracle Applications Installation Update Notes, Rel 11.5.10.2
NOTE.360079.1 : Global and Local Inventory explained
NOTE.295185.1 : How to Recreate the Global oraInventory
NOTE.197028.1 : Software Requirements and Tools for Oracle Applications
NOTE.201392.1 : Visual C++ Requirement for Release 11i on Windows
NOTE.268776.1 : GNU Make Requirement for Release 11i For Windows
NOTE.181244.1 : Configuring VNC Or XVFB As The X Server For Applications 11i
NOTE.153960.1 : FAQ : X Server testing and troubleshooting

Patching
*******

NOTE.174436.1 : Oracle Applications Patching FAQ
NOTE.175485.1 : How to Apply an 11i Patch When adpatch is Already Running
NOTE.231701.1 : How to Find Patching History (10.7, 11.0, 11i)
NOTE.252422.1 : Requesting Translation Synchronization Patches in Release
NOTE.316366.1 : 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)
NOTE.259484.1 : Release 11.5.10 Maintenance Pack Installation Instructions
NOTE.316365.1 : Oracle Applications Release 11.5.10.2 Maintenance Pack
NOTE.139684.1 : Oracle Applications Current Patchset Comparison Utility -
NOTE.181665.1 : Release 11i Adpatch Basics
NOTE.358247.1 : ORA-00001 : unique constraint violated error while applying
NOTE.358417.1 : Unable to Start Concurrent Managers After Applying 11i.
NOTE.353414.1 : Application Technology Group (ATG) Patching Policy
NOTE.359198.1 : XDF files fail to load on HP-UX, Memory Fault(Coredump)
NOTE.232834.1 : Oracle Applications Release 11.5.9 Maintenance Pack
NOTE.337274.1 : About Oracle Applications Technology 11i.ATG_PF.H Rollup 3
NOTE.244040.1 : Oracle E-Business Suite Recommended Performance Patches
NOTE.367756.1 : Uploading Java Objects To Patch History Tables Fails While
NOTE.76708.1 : Using ADSPLICE To Add Products To The APPL_TOP In

Cloning
******

NOTE.230672.1 : Cloning Oracle Applications Release 11i with Rapid Clone
NOTE.216664.1 : FAQ : Cloning Oracle Applications Release 11i
NOTE.242123.1 : Create new middle tier node in existing Apps 11i
NOTE.238276.1 : Migrating to Linux with Oracle Applications Release 11i
NOTE.364565.1 : Troubleshoot RapidClone or OAM Clone issues

Upgrade
*******

NOTE.200963.1 : R11.5.3/R11.5.4/R11.5.5 Upgrade And Install Issues
NOTE.289765.1 : Oracle Applications Release 11i (11.5.10) Upgrade Assistant
NOTE.289788.1 : Upgrading Oracle Applications

Autoconfig
**********

NOTE.165195.1 : Using AutoConfig to Manage System Configurations with
NOTE.218089.1 : Autoconfig FAQ
NOTE.270519.1 : Customizing an AutoConfig Environment
NOTE.260887.1 : Steps to Clean Nonexistent Nodes or IP Addresses from
NOTE.341322.1 : How to change the hostname of an Applications Tier using
NOTE.338003.1 : How to change the hostname and/or port of the Database Tier
NOTE.108865.1 : How To Create a Database Connection(DBC) File and

AD Utilities
**********

NOTE.233038.1 : AD Command Line Options

Relinking
********

NOTE.69798.1 : Basics of Relinking an Executable or Binary in an Oracle
NOTE.1009722.6 : How to relink Oracle Concurrent Program Executables on Unix

OAM
****

NOTE.210062.1 : Generic Service Management (GSM) in Oracle Applications 11i
NOTE.117264.1 : FAQ (Oracle Applications Manager)
NOTE.177089.1 : OAM11i Standalone Mode Setup and Configuration
NOTE.214962.1 : How To Determine The Version Of OAM (Oracle Application
NOTE.258330.1 : About Oracle Applications Manager Minipack 11i.OAM.H
NOTE.166115.1 : Oracle Applications Manager 11i integrated with Oracle
NOTE.185431.1 : Troubleshooting Oracle Applications Manager OAM 2.0 for 11i
NOTE.73959.1 : Installation and Configuration of Oracle Application Manager
NOTE.225024.1 : Oracle Applications Manager 11i Documentation Roadmap
NOTE.338317.1 : Basic Information on Cloning An 11.5.10 System Using OAM

NLS/MLS
*******

NOTE.15389.1 : NLS DEBUGGING SUCCESS GUIDE ** INTERNAL ONLY **
NOTE.227332.1 : NLS considerations in Import/Export - Frequently Asked
NOTE.15095.1 : Export/Import and NLS Considerations
NOTE.175300.1 : A Guide to Debugging Globalization (NLS) Support Issues
NOTE.124721.1 : Migrating an Applications Installation to a New Character

Forms
*****

NOTE.130686.1 : How to Generate Form, Library and Menu for Oracle
NOTE.177610.1 : Oracle Forms in Applications FAQ
NOTE.141012.1 : How to Manually Generate an 11.5 Form on Unix

Apps Database
*************

NOTE.174605.1 : bde_chk_cbo.sql - Reports Database Initialization
NOTE.186125.1 : Applications 11i and Standby Databases
NOTE.248857.1 : Oracle Applications Tablespace Model Release 11i -
NOTE.216205.1 : Database Initialization Parameters for Oracle Applications
NOTE.285267.1 : Oracle E-Business Suite 11i and Database FAQ
NOTE.230627.1 : 9i Export/Import Process for Oracle Applications Release 11i
NOTE.183078.1 : Recreating Applications 11i JAVA objects in the database
NOTE.216212.1 : Business Continuity for Oracle Applications Release 11i,
NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g
NOTE.340859.1 : Upgrading Oracle Applications 11i Database to 10g with
NOTE.174605.1 : bde_chk_cbo.sql - Reports Database Initialization
NOTE.216211.1. : Nologging in the E-Business Suite
NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g

RAC / Apps RAC
**************

NOTE.220970.1 : RAC : Frequently Asked Questions
NOTE.312731.1 : Configuring Oracle Applications Release 11i with 10g RAC
NOTE.270901.1 : How to Dynamically Add a New Node to an Existing 9.2.0 RAC
NOTE.294652.1 : E-Business Suite 11i on RAC : Configuring Database Load balancing & Failover
NOTE.312731.1 : Configuring Oracle Applications Release 11i with 10g RAC
NOTE.279956.1 : Oracle E-Business Suite Release 11i with 9i RAC :
NOTE.277825.1 : How to setup Tnsnames.ora (806) for 11i and RAC

Database
********

NOTE.1012933.6 : General Information : Alert Logs and Trace Files
NOTE.209870.1 : How to Reload the JVM in 9.2.0.X
NOTE.175472.1 : How to Reload the JVM in 8.1.7.X
NOTE.159143.1 : Separating Multiple 8i or 9i Oracle Versions to Avoid
NOTE.307349.1 : OUI tips for Oracle RDBMS 10.1.X on OpenVMS
NOTE.130814.1 : How to move LOB Data to Another Tablespace

System Administration
*******************

NOTE.189457.1 : Oracle Applications Systems Administration Setup and Usage
NOTE.290525.1 : Oracle User Management FAQ
NOTE.316277.1 : Unable To Change Guest User Password In Oracle Applications
NOTE.311552.1 : How to optimize the purge process in a high transaction
NOTE.154850.1 : How to Run the Purge Concurrent Request and/or Manager Data

Jserver
******

NOTE.295484.1 : Clear Server Cache and Bounce Apache (Web Server)
NOTE.220188.1 : Oracle Applications Release 11i apps.zip Re-architect
NOTE.230688.1 : Basic ApacheJServ Troubleshooting with IsItWorking.class

Invalid Objects
*************

NOTE.60558.1 : Troubleshooting the Source of Invalid Objects
NOTE.266910.1 : How To Resolve IES Invalid Java Classes After Loading JAR
NOTE.113947.1 : Step by Step Troubleshooting Guide to Solve APPS Invalid
UNIX / LINUX / WINDOWS
=======================
NOTE.1007808.6 : HOW TO HANDLE CORE DUMPS ON UNIX
NOTE.1812.1 : TECH : Getting a Stack Trace from a CORE file
NOTE.28588.1 : TECH : Using Truss / Trace on Unix
Advanced Configuration:
======================
NOTE.217368.1 : Advanced Configurations and Topologies for Enterprise
NOTE.226880.1 : Configuration of Load Balancing and Transparent Application
NOTE.97926.1 : Failover Issues and Limitations [Connect-time failover and
NOTE.123718.1 : 11i : A Guide to Understanding and Implementing SSL for
Techstack:
=========

NOTE.162488.1 : Complete Guide to JInitiator 1.1.8 Setup & Troubleshooting
NOTE.312572.1 : About Oracle Applications Technology Updates for Release 11.
NOTE.246105.1 : Upgrading to J2SE 1.4.2 with Oracle Applications 11i
NOTE.94091.1 : Example : Identifying Connection String Problems in JDBC

Workflow:
========

NOTE.298550.1 : Troubleshooting Workflow Data Growth Issues

General:
=======

NOTE.184977.1 : Certify FAQ, Product Navigation & User's Guide
NOTE.224882.1 : Common Commands asked by Oracle Support for Troubleshooting
NOTE.110415.1 : Payables MRC Frequently Asked Questions
NOTE.285218.1 : Recommended Browsers for Oracle Applications 11i
NOTE.257650.1 : Resolving Problems with Connection Idle Timeout
NOTE.270523.1 : How To Find the Oracle Applications Framework and Rollup
NOTE.301504.1 : "Cannot Complete Applications Logon" Error After Selecting
NOTE.315094.1 : JTF Login Page - an operational assessment for CRM
NOTE.242490.1 : How To Configure The "/etc/hosts" File On Linux
NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g Release 1
NOTE.342861.1 : Java Version Conflict When Migrating Platforms
NOTE.209999.1 : Oracle Applications Manager in Oracle Applications 11.5.8
NOTE.198160.1 : Summary note to LOB's/BLOB's/CLOB's/NCLOB's and BFILES
NOTE.159244.1 : How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix
NOTE.186981.1 : Oracle Application Server with Oracle E-Business Suite
NOTE.333436.1 : Oracle E-Business Suite 11i on Windows Server 2003 Service
NOTE.356878.1 : How to relink the whole Applications 11i Installation
NOTE.197031.1 : 32-bit/64-bit Certification/Conversion Issues on Oracle
NOTE.158577.1 : NLS_LANG Explained (How does Client-Server Character
NOTE.227331.1 : Setting NLS Parameters - Frequently Asked Questions
NOTE.124721.1 : Migrating an Applications Installation to a New Character
NOTE.43208.1 : Certified Compilers
NOTE.66320.1 : Changing the Database Character Set or the Database
NOTE.279956.1 : Oracle E-Business Suite Release 11i with 9i RAC :
NOTE.260393.1 : Java Mailer and Other 11.5.9/OWF G Current Issues in
NOTE.232313.1 : Information on Previous Versions of Developer 6i Patchsets
NOTE.125767.1 : Upgrading Developer 6i with Oracle Applications 11i
NOTE.371438.1 : adpreclone.pl dbTier fails with RC-50409 : Topology
NOTE.135949.1 : Troubleshooting the Personal Home Page Login Problems in 11.
NOTE.164317.1 : Upgrading JDBC drivers with Oracle Applications 11i
NOTE.302035.1 : How to Test a JDBC Thin Driver Connection From the SSO
NOTE.365735.1 : How to use Digital Certificates for 11i Applications
NOTE.357922.1 : Autoconfig Reverts to old Context File Values.
NOTE.206511.1 : How to Find a JAR File Which Contains a Particular JAVA
NOTE.68839.1 : 8i Using loadjava and dropjava to Load and Unload Java
NOTE.165123.1 : JAVA CLASS - ORA-29534 : referenced object
NOTE.165114.1 : Resolving Invalid Java Classes in Oracle Applications
NOTE.271218.1 : Yellow bar's and Java security error , The daddy of all
NOTE.294932.1 : Recommendations to Install Oracle Applications 11i
NOTE.169706.1 : Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64
NOTE.342442.1 : Cloning Multi-Node to Single-Node Oracle Applications
NOTE.233428.1 :
NOTE.302738.1 : Using Virtual Hostnames with Oracle Applications Release 11i
NOTE.356433.1 : Using Oracle Applications Release 11i with Virtual
NOTE.362135.1 : Configuring Oracle Applications Release 11i with 10g R2 RAC
NOTE.159270.1 : How To Use FNDCPASS to Change The Oracle Users, APPS,
NOTE.303237.1 : Migrating Red Hat Linux 2.1 or 3.0 to Red Hat Linux 4.0
NOTE.91985.1 :
NOTE.200963.1 : R11.5.3/R11.5.4/R11.5.5 Upgrade And Install Issues
NOTE.272789.1 : Post Clone Problem : Login/Portal Server Installation May
NOTE.339664.1 : Analyst Crib Sheet for NLS/MLS issues in Oracle
NOTE.211708.1 : Detailed Explanations of How NLS/MLS is Being Handled in 11i
NOTE.245079.1 : Steps to clone a 11i RAC environment to a non-RAC
NOTE.362135.1 : Configuring Oracle Applications Release 11i with 10g R2 RAC
NOTE.362135.1 : Configuring Oracle Applications Release 11i with 10g R2 RAC
NOTE.312731.1 : Configuring Oracle Applications Release 11i with 10g RAC
NOTE.131321.1 : How to Relink Oracle Database Software on UNIX
NOTE.231876.1 : Windows OS Upgrade for 11i E-Business Suite
NOTE.184977.1 : Certify FAQ, Product Navigation & User's Guide
NOTE.209810.1 : How to Relink Oracle Applications 11i Programs After
NOTE.361428.1 : Using Linux Desktop Clients with Oracle Applications 11i
NOTE.296559.1 :
NOTE.275734.1 : India Localization
NOTE.364439.1 : Tips and Queries for Troubleshooting Advanced Topologies
NOTE.372928.1 : Oracle Critical Patch Update July 2006 Documentation Map
NOTE.372931.1 : E-Business Suite Critical Patch Update Note
NOTE.268837.1 : Gathering Debug Weboam Log
NOTE.292996.1 : How to Re-Install Intermedia on an 11i Environment
NOTE.160121.1 : Introduction to Sun Cluster v3
NOTE.188135.1 : Documentation Index for Real Application Clusters
NOTE.251351.1 : How to Change the Characterset in a Standby Database in 9i
NOTE.362203.1 : Oracle Applications Release 11i with Oracle 10g Release 2
NOTE.362205.1 : 10g Release 2 Export/Import Process for Oracle Applications
NOTE.1009718.6 : HOW TO SOLVE UNDEFINED SYMBOL ERRORS ON UNIX AND VMS
NOTE.316889.1 : Complete checklist for manual upgrades to 10gR2
NOTE.223721.1 : How to Install XVFB on Linux for dynamic image generartion
NOTE.130091.1 : Upgrading Oracle Applications 11i to use JDK 1.3
NOTE : 163400.1 : Release Content Documents and Features Summary Matrices
NOTE.253918.1 : Autopatch Fails with "Unable to call adppdepRunFndLoad
NOTE.166650.1 : Working Effectively With Oracle Support Services
NOTE.333785.1 : Oracle Applications Internationalization Guide
NOTE.119164.1 : Changing Database Character Set - Valid Superset Definitions
NOTE.17210.1 : Supported NLS Character Sets
NOTE.179133.1 : The correct NLS_LANG in a Windows Environment
NOTE.226565.1 : 9iRAC Useful Views and Statistics (INTERNAL ONLY)
NOTE.387046.1 : RCONFIG : Frequently Asked Questions
NOTE.200340.1 : RAC : Cache Fusion
NOTE.265253.1 : 10g Recyclebin Features And How To Disable it( _recyclebin )
NOTE.312594.1 : Get Warning Messages Trying To Generate Jar Files From
NOTE.242480.1 : Using a Staged Applications 11i System to Reduce Patching
NOTE.134007.1 : CMCLEAN.SQL - Non Destructive Script to Clean Concurrent
NOTE.365228.1 : About Oracle Applications Technology 11i.ATG_PF.H Rollup 4
NOTE.371016.1 : How To License A New Product.
NOTE.151654.1 : How To Start the License Manager After Installing Oracle
NOTE.279430.1 : How To Change Project Installation From Shared To Full
NOTE.123891.1 : How to deactivate a language in e-Business Suite
NOTE.216550.1 : Oracle Applications Release 11i with Oracle9i Release 2 (9.
NOTE.341437.1 : Business Continuity for Oracle Applications Release 11i
NOTE.246105.1 : Upgrading to J2SE 1.4.2 with Oracle Applications 11i
NOTE.304748.1 : Internal : E-Business Suite 11i with Database FAQ
NOTE.308320.1 : How to install the 10.1.0 Enterprise Manager Grid Control
NOTE.291901.1 : Maintenance Mode - A New Feature in 11.5.10
NOTE.134527.1 : TNS-00516 Starting TNS Listener
NOTE.300482.1 : Overview of Using Java with Oracle E-Business Suite Release
NOTE.208256.1 : WIN : How to Remove a Single ORACLE_HOME and Its Traces on
NOTE.363827.1 : Rebaselined Oracle Applications Technology Components for
NOTE.373611.1 : How to move Concurrent Processing Server from one node to
NOTE.240818.1. : Concurrent Processing: Transaction Manager Setup and Configuration Requirement in an 11i RAC Environment
NOTE.69660.1 : Understanding Data Auditing in Oracle Application Tables
NOTE.60828.1 : Overview of Oracle Applications AuditTrails
NOTE.134949.1 : Release 11.0.3 and Oracle 8i Release 8.1.6 Interoperability
NOTE.342332.1 : Troubleshooting Login Problems in Oracle Applications 11i
NOTE.189256.1 : UNIX : Script to Verify Installation Requirements for Oracle
NOTE.296559.1 : FAQ : Common Tracing Techniques within the Oracle
NOTE.177183.1 : Succesfully Installing NLS/MLS in 11i
NOTE.73352.1 : NLS/MLS Frequently Asked Questions
NOTE.399789.1 : NLS Frequently Asked Questions
NOTE.339664.1 : Analyst Crib Sheet for NLS/MLS issues in Oracle
NOTE.227331.1 : Setting NLS Parameters - Frequently Asked Questions
NOTE.110849.1 : Installing and Relinking Oracle Developer on UNIX Platforms
NOTE.76535.1 : Troubleshooting Issues Which Arise During the Application of Release 11 Patches
NOTE.316804.1 : Oracle Applications NLS Release Notes, Release 11i (11.5.10.
NOTE.287176.1 : DMZ Configuration with Oracle E-Business Suite 11i
NOTE.310840.1 : AFPCAL Received Failure Code While Parsing or Running
NOTE.371434.1 : Using Openfiler iSCSI with an Oracle database
NOTE.233040.1 : When Oracle Applications Automatic Patch Prerequisite
NOTE.236469.1 : Using Distributed AD in Applications Release 11.5.
NOTE.233043.1 : 11.5.9 Oracle E-Business Suite Consolidated Update 2
NOTE.108185.1 : Oracle Applications Object Library SQL scripts
NOTE.201662.1 : How To Manually Relink on Windows NT or Windows 2000
NOTE.306906.1 : How to create missing DB objects from xdf files
NOTE.396708.1 : Guidelines to Determine whether a Bug or SR is a LinuxOS
NOTE.166762.1 : Oracle Applications Manager 11i Availability
NOTE.60966.1 : Getting Rid Of Those Pesky Invalid Objects In Oracle
NOTE.216589.1 : Step By Step Guide to Creating a Custom Application in
NOTE.105127.1 : FAQ (Customization)
NOTE.243880.1 : Shared APPL_TOP FAQ
NOTE.409045.1 : How to clone from Rac to Non-rac in Oracle Applications 11i.
NOTE.373611.1 : How to move Concurrent Processing Server from one node to
NOTE.342442.1 : Cloning Multi-Node to Single-Node Oracle Applications
NOTE.261428.1 : Setting up 11i E-Business suite using a hardware load
NOTE.224875.1 : Installation, Patching & Upgrade Frequently Asked Questions
NOTE.278816.1 : How to Setup Parallel Concurrent Processing using Shared
NOTE.286506.1 : Sharing Middle-tier Oracle Home in E-Business Suite 11i
NOTE.105133.1 : Concurrent Manager Questions and Answers Relating to
NOTE.69336.1 : Basic information about Concurrent Managers
NOTE.169706.1 : Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64
NOTE.345145.1 : Is There A Way To Automate The Prompts For Adcfgclone.Pl?
*********************************************************************
NOTE.416338.1 : How To Upgrade JDK / J2SE 1.4 Sub-Versions
NOTE.225165.1 : Patching Best Practices and Reducing Downtime
NOTE.368628.1 : Is The 'Personal Home Page' Mode Supported In Oracle 11i Applications?
NOTE.387859.1 : Using AutoConfig to Manage System Configurations in Oracle
NOTE.402306.1 : Oracle Applications Installation and Upgrade Notes Release
NOTE.160214.1 : How to change the oracle users, APPS, APPLSYS and
NOTE.159244.1 : How To Use FNDCPASS to Change The Oracle Users, APPS,
NOTE.335515.1 : Relink fails for Oracle 9.2.0.7.0 on LINUX x86 server on
NOTE.372800.1 : How to Implement an SSL CA Root Certificate in JInitiator
NOTE.402312.1 : Oracle Applications Installation and Upgrade Notes Release
NOTE.394692.1 : Oracle Applications Documentation Resources, Release 12
NOTE.603104.1 : Troubleshooting RapidClone issues with Oracle Applications R12
NOTE.384248.1 : Sharing The Application Tier File System in Oracle E-Business Suite Release 12
NOTE.388577.1 : Configuring Oracle Applications Release 12 with 10g R2 RAC
NOTE.559518.1 : Cloning Oracle Applications Release 12 with Rapid Clone on RAC Enabled Systems
NOTE.393861.1 : Globalization Guide for Oracle Applications Release 12
NOTE.372800.1 : How to Implement an SSL CA Root Certificate in JInitiator
NOTE.184876.1 : Oracle Application Object Library Middle (Web) Tier Setup
NOTE.403385.1 : Duplicate Responsibilities Created On Sysadmin Login And
NOTE.380483.1 : Oracle E-Business Suite Release 12 Additional Configuration
NOTE.148903.1 : Interoperability Notes Oracle Applications Release 11i with
NOTE.364704.1 : A Guide to Configure, Maintain & Troubleshoot JDBC Buffers
NOTE.148902.1 : Interoperability Notes Oracle Applications Release 11.0
NOTE.337762.1 : How to Remove an Oracle Applications 11i node
NOTE.399362.1 : Oracle Applications Release 12 Upgrade Sizing and Best
NOTE.104457.1 : Invalid Objects In Oracle Applications FAQs
NOTE.132604.1 : Upgrading OJSP with Oracle Applications 11i
NOTE.215268.1 : Implementing and Using the JSP Precompiler
NOTE.316900.1 : ALERT : Oracle 10g Release 2 (10.2) Support Status and Alerts
NOTE.189908.1 : ALERT : Oracle9i Release 2 (9.2) Support Status and Alerts
NOTE.380490.1 : Oracle E-Business Suite R12 Configuration in a DMZ
NOTE.269291.1 : Oracle Applications Tablespace Migration Utility User
NOTE.206511.1 : How to Find a JAR File Which Contains a Particular JAVA
NOTE.406982.1 : Cloning Oracle Applications Release 12 with Rapid Clone
NOTE.262125.1 : Sun.io.MalformedInputException For OraSCV.asc,emreadme.txt,
NOTE.419839.1 : How to enable Apache, OC4J and OPMN logging in Oracle
NOTE.422419.1 : How To Enable and Collect Debug for HTTP, OC4J and OPMN in
NOTE.15390.1 : How to Determine and Change DB_NAME or ORACLE_SID
NOTE.375682.1 : About Oracle Applications Technology ATG_PF.H Rollup 5
NOTE.135715.1. : Diagnostic Steps for Intermittent FRM-99999 & FRM-92100
NOTE.185489.1 : Setting Up Parallel Concurrent Processing On Unix Server
NOTE.431496.1 : Java In The Database For Oracle Applications : Introduction
NOTE.183408.1 : Raw Devices and Cluster Filesystems With Real Application
NOTE.277366.1 : Technology Validation Utility for Oracle Applications
NOTE.394448.1 : Getting Started with the Application Management Pack for
NOTE.412044.1 : Application Management Pack for Oracle E-Business Suite
NOTE.375113.1 : Oracle Diagnostics 2.4
NOTE.405425.1 : Oracle Diagnostics 2.5
NOTE.201340.1 : Using Forms Listener Servlet with Oracle Applications 11i
NOTE.160337.1 : How To Manually Change The APPS, APPLSYS and APPLSYSPUB
NOTE.372322.1 : HP Tru64 UNIX Migration Strategy for Oracle E-Business
NOTE.303709.1 : Reclaiming unused space in APPLSYSD tablespace
NOTE.130183.1 : How to Get Log Files from Various Programs for Oracle
NOTE.269129.1 : How to Implement Printing for Oracle Applications : Getting
NOTE.297522.1 : How to investigate printing issues and work towards its resolution ?
NOTE.215527.1 : Maintenance Wizard Overview
NOTE.452120.1 : How to locate the log files and troubleshoot RapidWiz for
NOTE.312640.1 : Oracle Text : Re-installation of Applications 11i (11.5.10)
**************
*** iSetup ***
**************
NOTE.402785.1 : iSetup dependency with Deinstall and Reinstall of XMLDB
NOTE.368670.1 : About Oracle iSetup Minipack 11i.AZ.H
NOTE.243554.1 : How to Deinstall and Reinstall XML Database (XDB)
NOTE.244523.1 : Security Alert #57 : Buffer Overflows in EXTPROC of Oracle
NOTE.433435.1 : Japanese characters are displayed like square boxes in
NOTE.455366.1 : Investigating NoClassDefFoundError in eBusiness 11i when
NOTE.197409.1 : Error Opening Oracle*Terminal File fmrweb.res
NOTE.312553.1 : How To Use the Original Forms for Reprint Instead of the
NOTE.438086.1 : Platform Migration with Oracle Applications Release 12
NOTE.458452.1 : Complying with Daylight Saving Time (DST) and Time Zone
NOTE.189367.1 : Best Practices for Securing the E-Business Suite
NOTE.300969.1 : Troubleshooting SSL with Oracle Applications 11i
NOTE.444524.1 : About Oracle Applications Technology ATG_PF.H Rollup 6
NOTE.265253.1 : 10g Recyclebin Features And How To Disable it( _recyclebin )
NOTE.428262.1 : How to identify the form name attached to an f60webmx
NOTE.68839.1 : 8i Using loadjava and dropjava to Load and Unload Java
NOTE.405521.1 : Oracle Enterprise Manager Grid Control Release Notes for
NOTE.187905.1 : bde_imt_index_status.sql - List all interMedia Text indexes
NOTE.388577.1 : Configuring Oracle Applications Release 12 with 10g R2 RAC
NOTE.345106.1 : Login Links On New Rapid Install Homepage Do Not Function
NOTE.443521.1 : Enterprise Manager Grid Control Plug-in for Oracle Applications, Version 1.0/1.2
NOTE.398412.1 : Workflow Queues Creation Scripts
NOTE.77483.1 : External Support FTP site : Information Sheet
NOTE.122452.1 : Global Customer Services Policy Regarding Customizations
NOTE.257911.1 : How To Use Rotatelogs In 9iAS Release 1 (1.0.2.x)
NOTE.276845.1 : Apache Web Server Hangs Every Other Time Running Adapcctl.
NOTE.218893.1 : How to Create The Service Manager 'FNDSM' on Oracle
NOTE.437878.1 : Upgrading Forms and Reports 10g in Oracle Applications
NOTE.290807.1 : Upgrading Sun JRE with Oracle Applications 11i
NOTE.280167.1 : AS10g with Apps 11i - Summary of Login process
NOTE.357218.1 : Troubleshooting JDeveloper setup for Oracle Applications
NOTE.403339.1 : Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite Release 12 Upgrade
NOTE.463249.1 : After Clone Forms Is Trying To Connect To Target Instance
NOTE.386374.1 : How to enable/disable/change password of the listeners for Oracle Applications 11i
NOTE.454750.1 : Oracle Apps Release 12 with Oracle Database 10.2.0 interoperability notes
NOTE.428503.1 : Integrating Oracle E-Business Suite Release 11i with Oracle Database Vault 10.2.0.3
NOTE.443761.1 : How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?
NOTE.291783.1 : Getting Started with the Oracle Grid Control Plug-in for Oracle Applications in Release 11i (AMP v1)
NOTE.468980.1 : How to Remove the Language Icons in the AppsLocalLogin.jsp
NOTE.398619.1 : Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)
NOTE.295606.1 : Oracle Application Server 10g with Oracle E-Business Suite Release 11i Troubleshooting
NOTE.169706.1 : Oracle® Database on AIX®,HP-UX®,Linux®,Mac OS® X,
Solaris®,Tru64 Unix® Operating Systems Installation and
Configuration Requirements Quick Reference (8.0.5 to 11.1)
NOTE.109665.1 : Organization Setup Frequently Asked Questions - FAQ
NOTE.434395.1 : ATG Service Request Creation
NOTE.438086.1 : Platform Migration with Oracle Applications Release 12
NOTE.469213.1 : How To Encrypt The Apps Password In Wdbsvr.App
NOTE.293369.1 : OPatch documentation list
NOTE.275379.1 : Script To Check What Workflow Related Patches Are Installed
NOTE.388577.1 : Configuring Oracle Applications Release 12 with 10g R2 RAC
NOTE.264157.1 : The correct NLS_LANG setting in Unix Environments
NOTE.91985.1 : Step by Step on Cloning the ORACLE_HOME (Including DB) and
NOTE.74838.1 : Migrating Apps Release 11.0 from UNIX Host To A Second
NOTE.396009.1 : Database Initialization Parameters for Oracle Applications Release 12
NOTE.391406.1 : How to get a clean Autoconfig Environment
NOTE.560719.1 : How to troubleshoot iSetup issues
NOTE.406376.1 : Oracle E-Business Tax Release 12 Known Issues
NOTE.577713.1 : On Windows, After 10g Upgrade, Running Autoconfig on Apps
NOTE.343917.1 : Frequently Asked Questions : Oracle E-Business Suite Support
NOTE.553831.1 : java.lang.ArrayIndexOutOfBoundsException Error when
NOTE.119319.1 : How to Replace Oracle Logo with Company Logo on Applications 11i Sign-On Screen
NOTE.554336.1 : How Do You Manually Generate A Form In Release 12 (frmcmp)
NOTE.444286.1 : How to manually generate a R12 report on Unix
NOTE.356433.1 : Using Oracle Applications Release 11i with Virtual Hostnames and Business Continuity
NOTE.555081.1 : Concurrent Manager Does Not Start if the Profile Option "Concurrent : GSM Enabled" is Set "Y" at Site Level
NOTE.149124.1 : Creating a StatsPack performance report
NOTE.471566.1 : Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit
NOTE.397757.1 : How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data
NOTE.375127.1 : How to restart the adworker having status fixed,restart,wait
NOTE.353150.1 : OPatch Failing Validation Phase When Archiving Really Did Not Fail
NOTE.215527.1 : Maintenance Wizard Overview
NOTE.564465.1 : Sysadmin And User Responsibility Not Available
NOTE.219968.1 : SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance
NOTE.417122.1 : Resolving the Library Cache Locks
NOTE.419475.1 : Removing Credentials from a Cloned EBS Production Database

Important Wait Events In Oracle Database SQL Execution Level

Here are some of the most common wait events, and what they mean:

enqueue

The process is waiting on an enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.

library cache pin

The process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view.

library cache load lock

The process is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)

latch free

The process is waiting for a latch held by another process. (This wait event does not apply to processes that are spinning while waiting for a latch; when a process is spinning, it is not waiting.)

buffer busy waits

The process wants to access a data block that is currently not in memory, but another process has already issued an I/O request to read the block into memory. (The process is waiting for the other process to finish bringing the block into memory.)

control file sequential read

The process is waiting for blocks to be read from a control file.

control file parallel write

The process has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.

log buffer space

The process is waiting for space to become available in the log buffer (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.

log file sequential read

The process is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.

log file parallel write

The process is waiting for blocks to be written to all online redo log members in one group. LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.

log file sync

The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction. (A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)

db file scattered read

The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.

db file sequential read

The process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!

db file parallel read

The process has issued multiple I/O requests in parallel to read blocks from data files into memory, and is waiting for all requests to complete. The documentation says this wait event occurs only during recovery, but in fact it also occurs during regular activity when a process batches many single block I/O requests together and issues them in parallel. (In spite of the name, you will not see this wait event during parallel query or parallel DML. In those cases wait events with PX in their names occur instead.)

db file parallel write

The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

direct path read, direct path write

The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.

There are several wait events that we call "idle events" because each of these wait events typically occurs when the Oracle process has nothing to do and is waiting for somebody to give it a task. Idle events are usually not very interesting from a tuning standpoint, so we usually overlook them when evaluating data extracted from the wait event interface. The common idle events are as follows:


Idle Wait Events


client message: PX Idle Wait
dispatcher timer rdbms ipc message
lock manager wait for remote message smon timer
Null event SQL*Net message from client
parallel query dequeue SQL*Net message to client
pipe get SQL*Net more data from client
PL/SQL lock timer virtual circuit status
pmon timer wakeup time manager

Scripts Used in Critical Production Biz Time Monitoring

=== Temp Usage ===

 

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;

 

SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid having sum(st.blocks)/1024*8 > 100 order by 2;

 

SELECT  /*+ RULE */ s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                         NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                         s.status ||' for '||
                         LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                         LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                         u.tablespace, u.contents, u.extents, round((u.blocks*8)/1024),
                         s.sql_address, s.sql_hash_value
                 FROM    v$session s, v$sort_usage u
                 WHERE   s.saddr    = u.session_addr
                 AND     u.contents = 'TEMPORARY'
                 AND     s.audsid != USERENV('sessionid')
                 AND    (u.blocks*8)/1024 >= 1000
                 ORDER   BY 1,2,3,4,5 Desc;

 

=== High Redo ===

 

SELECT s.inst_id,s.sid, s.serial#, s.username, s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;

=== Rollback Used ===
                                 
SELECT rn.name, ROUND(rs.rssize/1024/1024),
                        s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                        NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                        s.sql_address, s.sql_hash_value, p.spid,
                        s.status ||' for '||
                        LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                        LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                        round(ceil((t.used_ublk*8)/1024),1)
                 FROM   v$rollname rn, v$rollstat rs,
                        v$session s, v$transaction t, v$process p
                 WHERE  rn.usn = rs.usn
                 AND    round((t.used_ublk*8)/1024) >= 1000
                 AND    rs.usn = t.xidusn
                 AND    s.sid = p.pid (+)
                 AND    s.taddr = t.addr
                 ORDER  BY 2 desc, s.sid ,s.status

 

=== Roll back segement Information ====

 

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB" from dba_undo_extents where tablespace_name like '%UNDO%'
group by tablespace_name, status;

 

=== Shared Pool Usage ===

 

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' FROM gv$sgastat  WHERE name='free memory' AND pool='shared pool';

 

=== Archive Generation for last 5 hours ===

 

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  FROM v$log_history WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), TO_CHAR(first_time, 'HH24') ORDER BY 2;

 

=== High Memory ===

 

select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512;

 

=== Performance ===

 

select sql_hash_value, count(*) from v$session where event like 'enq%' group by sql_hash_value;

 

select sql_hash_value,username,osuser, count(*) from v$session where event like 'enq%' and SQL_HASH_VALUE='&event' group by sql_hash_value,username,osuser;

 

select sql_text from v$sqlarea where hash_value = '&hash_value';

 

select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;

 

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x where s.paddr in
( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;

 

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';

 

=== Top 10 Memory Process at OS level ===

 

UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep -i `echo $ORACLE_SID` | tail -10

 

=== Other Script to update the daily report ===

 

select status,count(1) from gv$session group by status; 

 

STATUS     COUNT(1)
-------- ----------
ACTIVE           22
INACTIVE        155

 

select count(1) from dba_tables where logging='NO';

 

  COUNT(1)
----------
       919

 

select distinct status,count(1) from dba_indexes group by status;

 

STATUS     COUNT(1)
-------- ----------
N/A            1639
UNUSABLE          1
VALID          6162

 

select count(1) from dba_objects where status='INVALID';
'
  COUNT(1)
----------
       681

 

CRSCTL CheatSheet

CRSCTL CheatSheet

You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

Start Oracle Clusterware

#crsctl start crs

Stop Oracle Clusterware

#crsctl stop crs

Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

Checking Voting disk Location

$crsctl query css votedisk

0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).

Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

Add Voting disk

#crsctl add css votedisk path

Remove Voting disk

#crsctl delete css votedisk path

Check CRS Status

$crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

You can also see particular daemon status

$crsctl check cssd

Cluster Synchronization Services appears healthy

$crsctl check crsd

Cluster Ready Services appears healthy

$crsctl check evmd

Event Manager appears healthy

You can also check Clusterware status on both the nodes using

$crsctl check cluster

prod01 ONLINE

prod02 ONLINE

Checking Oracle Clusterware Version

To determine software version (binary version of the software on a particular cluster node) use

$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.

There are other options for CRSCTL too which can be seen using

$crsctl

Or

$crsctl help

Recent Apps DBA Interview Questions..

1. What are .lct and .ldt files in Patch Directory?

Ans:

The patch metadata LDT files (also called datafiles) are FNDLOAD data files included in the top-level directory of all recent patches. The LDT files contain prerequisite patch information and a manifest of all files in the patch with their version numbers. The Patch Information Bundle metadata also include information about the relationships between patches, such as which minipacks are contained in the recommended.

LCT files (also called configfiles) are the configuration files which are used to download/upload data. Without configfiles, datafiles are useless.

2. If Ad worker fails during Ad patching, How many times by default adpatch automatically tries to resume the patching?


Ans:

Three Times

3. While trying to change one profile option at site level, the option is not editable mode? How to make it editable mode?


Ans:

Goto Application Developer -> Profiles -> Give Profile Options Names -> Check updatable

4. How do you check Compatibility of Oracle Applications with Any operating System?

Ans:

Metalink -> Certify Tab -> View Certification by platform

5. How do you check Latest CPU Patch for Oracle Server and Applications?

Ans:

Metalink -> Patches & Updates Tab

6. In RAC env, Each node contains How many IPs?

Ans:

Three

7. What are the tables updated when you apply application patch?

Ans:

ad_applied_patches and ad_bugs

8. Can you apply Opatch without inventory?

Ans:

No

9. If there is no inventory, How do you apply a opatch?

Ans:

Create inventory using runInstaller

10. What are the tables get created during Apps Patching?

Ans:

ad_deferred_jobs and fnd_installed_processes

11. Default environment variable to be set for Forms Config files?

Ans:

FORMS60_WEB_CONFIG_FILE

12. Profile option to determine which dbc file to use?

Ans:

Application Database ID

13. How do you hide apps password during adpatching?

Ans:

adpatch flags=hidepw

14. What is inter operability patch?

Ans:

OS compatibility patch, mostly applied during upgradation

15. How do you compile jsp files?

Ans:

Using adadmin or ojspCompile(perl -x $JTF_TOP/admin/scripts/ojspCompile.pl)

16. What is cache in Concurrent Managers Definition?

Ans:

No of concurrent requets that have to be cached from fnd_concurrent_requests while reading fnd_concurrent_requests

17. Types of profile options?

Ans:

1. Site level
2. User level
3. Responsibility Level
4. Server Level
5. Application Level

18. Opatch log file location ?

Ans:

$ORACLE_HOME/.patch_storage/patch_number/*.log

19. Different levels of SQL Tracing?

Ans:

Regular (Level 1 – standard/default level)
Level 4 (standard + binds)
Level 8 (standard + waits)
Level 12 (standard + binds and waits)

20. If you lost all redo logs files during DB is up and running? What will happen how do you recover it?

Ans:

DB will get crashed immediately

Solution;

1. You have to go for Incomplete Recovery
2. One way: Take previous backup, recover up to last archive and open the database
3. Second way: open the database in no mount state, create control file with rest logs and open the database with rest logs.

21. DB is up and running fine? you lost one data file? DB is in archive log mode? How do you recover it?

Ans:

1. If you have a backup of datafile, restore it and apply archives.
2. If you don't have backup of datafile, create datafile in database and apply archives.

22. How do you clone a context file or how do you change existing port pool?

Ans:

Using adclonectx.pl, you can clone next context file, during cloning you can give new port pool, and run autoconfig

23. How do you run autoconfig in test mode?

Ans:

adchkcfg.sh (AD_TOP/bin)

24. If you lost dbc file, How will you recover it?

Ans:

Using adgendbc($AD_TOP/bin) or run Autoconfig

MRC Implementaion on Existing Environment???

What is MRC?

MRC allows Oracle Applications to support organizations that are transitioning from their national currency (Functional Currency) to other Currencies Supported by Oracle.

Is the process of converting functional currency into other currency during reporting.

Modules Impacted With MRC

General Ledger
Account Payables
Account Receivables
Fixed Assets etc.

How do I Enable MRC
STEPS TO BE FOLLOWED:

Installing MRC with Invokers Rights On( This is for New Installation).
Set Up Environment and Database.
Compile and Validate APPS Schemas.
Convert to Multiple Reporting Currencies.
Verify the Installation.
Perform Post–Installation Steps.
Maintain MRC Schema Objects

Running adadmin

Login to FE Node (elephant)
Run adadmin
Filename [adadmin.log] : adadmin_dv1_mrc.log
Please enter the batchsize [1000] : 100000
Enter the password for your 'SYSTEM' ORACLE schema:
Enter the ORACLE password of Application Object Library [APPS]
4. Maintain Applications Database Entities menu
1. Validate APPS schema
This will generate APPS.lst @ $APPL_TOP/admin/SID/out/APPS.lst

Sample Summary of Issues and Proposed Fixes Reported by APPS.lst

Objects with the same name as schema
Proposed Fix: Dropping all objects
Invalid Synonyms in APPS
Proposed Fix: Drop & Recreate all ,drop which are still invalid as reported by validate APPS.
missing or incorrect synonyms in "APPS"
Proposed Fix : Repointed the missing and incorrect synonyms
Missing Grants
Proposed Fix: Grant will be given to the reported objects.
Missing Privileges/ synonyms to objects not in "APPS" base schemas - 2586
Proposed Fix: Grants will be given
Invalid Objects
Proposed Fix: If Close to production. The objects will be documented and the results verified post MRC Installation.

Code Objects in the Base Schema - (Recommended but not mandatory)
Proposed Fix: No Action
Tables exist both in APPS & Base Schema
Proposed Fix: Compare in production & dropped.
Checking for packages in "APPS" with lines > 255 characters
Proposed Fix: Breaking the lines having more than 255 chars.
Checking for missing/invalid APPS_DDL or APPS_ARRAY_DDL packages
Proposed Fix: DBA will address this.

some Issue while running adadmin

sqlplus -s SYSTEM/***** @/apps/SID/appl_top/ad/11.5.0/admin/sql/advrfapp.sql APPS APPLSYS
declare
*
ERROR at line 1:
ORA-20000: ORA-01403: no data found : This procedure cannot continue because
the "SELECT ANY DICTIONARY" privilege for the SYSTEM schema is missing.
Please rerun the procedure as the privilege has now been granted.
ORA-06512: at line 26

Re-run adamin or grant select any dictionary to system;

ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_LOCK.
Identify the session holding DBMS_LOCK
Workaround
Set job queue process to 0

sqlplus -s SYSTEM/***** @/apps/sid/appl_top/ad/11.5.0/admin/sql/advrfapp.sql APPS APPLSYS
ERROR at line 1:

ORA-20000: ORA-20000: ORU-10027: buffer overflow, limit of 50000 bytes :

Fix : Changed serveroutput from 50000 to 1000000

Different issues

Incorrect synonym:
APPS.MSD_APP_INSTANCE_ORGS- should point toMRP.MSD_APP_INSTANCE_ORGS.Incorrect

Synonym Pointing to Remote Objects with DB Link

Synomyms Pointing to Objects which is Invalid.

Synomyms pointing to Objects which does not exist.

Application Schema not having standard packages like APPS_ARRAY_DDL and APPS_DDL

Similar table object exists in two different schemas.

Necessary grants are not given from Custom Apps schema to APPS.

Package line length more than 255 chars

Invalid Objects

Enabling MRC through adadmin

Run adadmin
4. Maintain Applications Database Entities menu
5. Convert to Multiple Reporting Currencies
Enter SYSTEM password
Enter the number of workers [64] : 24

Converting to Multiple Reporting Currencies (MRC) will create
one extra schema per APPS schema in your database.

Each MRC schema requires about 600 Megabytes of free space in your
SYSTEM tablespace and takes from 6 to 18 hours to create.

You must not perform any DDL operations on your Oracle Applications
database while converting to MRC, but you may change data.

Issue and Fixes

declare
*
ERROR at line 1:
ORA-20005: ORA-02021: DDL operations are not allowed on a remote database
[create_mc_schema(APPLSYS, TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS,
APPS, APPS_MRC, none, TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,CCA_ACK_HEADER, FALSE,ALL,APPS):
create_grant(APPS,APPS_MRC,CCA_ACK_HEADER,ALL,FALSE,): do_apps_ddl(APPS,GRANT
ALL ON "CCA_ACK_HEADER" TO APPS_MRC): do_apps_ddl(APPS,GRANT ALL ON
"AMV_MATCHING_QUEUE_TBL" TO APPS_MRC): do_apps_ddl(APPS,GRANT ALL ON
"AK_LOADER_TEMP" TO APPS_MRC): Start Time: 2009-03-17;00:19:25 Failure Time:
2009-03-17;00:23:24 Elapsed: 00;00:03:59]
ORA-06512: at line 25

Fix : Drop the object pointing to remote object and re-create after MRC is over

ERROR at line 1:
ORA-20005: ORA-01403: no data found
ORA-01720: grant option does not exist for 'APPLSYS.FND_LOBS'
[create_mc_schema(APPLSYS, TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS,
APPS, APPS_MRC, none, TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,FCOA_ATTACHMENTS_V, FALSE,ALL,APPS):
create_grant(FCOA_ATTACHMENTS_V does not exist in ): do_apps_ddl(APPS,GRANT ALL
ON "FCOA_ATTACHMENTS_V" TO APPS_MRC): Start Time: 2009-03-05;08:56:58 Failure
Time: 2009-03-05;08:58:33 Elapsed: 00;00:01:35]
ORA-06512: at line 25

Fix :select owner,object_type from dba_objects where object_name='FCOA_ATTACHMENTS_V';

CONNECT APPLSYS/xxxx

grant ALL on APPLSYS.FND_LOBS TO XXFAXMGR with grant option;
grant ALL on APPLSYS.FND_ATTACHED_DOCUMENTS TO XXFAXMGR with grant option;

CONNECT APPS/xxxxxx
grant ALL on APPS.FND_ATTACHED_DOCS_FORM_VL TO XXFAXMGR with grant option;

CONNECT XXFAXMGR/xxxxx
grant ALL on XXFAXMGR.FCOA_ATTACHMENTS_V TO APPS with grant option;


ERROR at line 1:
ORA-20005: ORA-00980: synonym translation is no longer valid
[create_mc_schema(APPLSYS, TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS,
APPS, APPS_MRC, none, TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,AVL_LOOKUPS, FALSE,ALL,APPS):
create_grant(APPS,APPS_MRC,AVL_LOOKUPS,ALL,FALSE,): do_apps_ddl(APPS,GRANT ALL
ON "AVL_LOOKUPS" TO APPS_MRC): Start Time: 2009-03-10;03:27:40 Failure Time:
2009-03-10;03:27:43 Elapsed: 00;00:00:03]
ORA-06512: at line 25

conn /as sysdba
col object_name for a40
select owner,object_name, object_type from dba_objects where OBJECT_NAME ='AVL_LOOKUPS';
connect apps/xxxxx
grant all on AVL_LOOKUPS to APPS_MRC;

ERROR at line 1:
ORA-20005: ORA-20000: PACKAGE BODY APPS.CMF_CREATE_ITEMS_PKG: Line 28 longer
than 255 characters. [create_mc_schema(APPLSYS, TRUE, FALSE, none):
do_create_mc_schema(1, APPLSYS, APPS, APPS_MRC, none, TRUE):
invoker_mrc_grants(APPS, APPS_MRC): grant_a_package(APPS, CMF_CREATE_ITEMS_PKG,
PACKAGE, APPS_MRC, Y, D, FALSE):
ad_apps_private.copy_code(CMF_CREATE_ITEMS_PKG,PACKAGE BODY,APPS,APPS_MRC):
Start Time: 2009-03-09;03:50:50 Failure Time: 2009-03-09;03:55:23 Elapsed:
00;00:04:33]
ORA-06512: at line 25

Compile package
===============

ALTER PACKAGE APPS.CMF_CREATE_ITEMS_PKG COMPILE BODY;

ALTER PACKAGE APPS.CMF_CREATE_ITEMS_PKG COMPILE;

Action : Line length fix by Maynak and complied the package
Status : Restarted adadmin and Fixed

ERROR at line 1:
ORA-20005: ORA-04063: has errors
ORA-04063: view "CMS.CMS_R_SO_HEADERS_V" has errors [create_mc_schema(APPLSYS,
TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS, APPS, APPS_MRC, none,
TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,CMS_R_SO_HEADERS_V, FALSE,ALL,APPS):
create_grant(APPS,APPS_MRC,CMS_R_SO_HEADERS_V,ALL,FALSE,):
do_apps_ddl(APPS,GRANT ALL ON "CMS_R_SO_HEADERS_V" TO APPS_MRC): Start Time:
2009-03-17;03:56:51 Failure Time: 2009-03-17;03:56:53 Elapsed: 00;00:00:02]
ORA-06512: at line 25

Fix:

.Take bacup of source of that view using dbms_metadata
Drop that view after consulting appl team.

Maintaining MRC after Patching and addition of objects to APPS/Custom schema impacting MRC
Increase of Patching Window time
Synonym Objects exists in APPS which is pointing to remote DB through DB link
Non Standard APPS Custom schema creation without Standard packages.
New custom Package creation with Line > 255.
Full grant of SYS, APPS , APPLSYS and other Standard APPS schema objects to Custom Apps Schema Owners

Existence of Code objects in base schemas

Reference

Metalink IDS

135756.1
135773.1

Pre Requisites to Enable MRC

1. 1486355 or 1512489 patches to be applied
2. Requirement 2000m in SYSTEM/TEMP and UNDO each.
3. Modify Validation Script to increase buffer size as below:

cd $AD_TOP/admin/sql/
Modify advrfapp.sql

From :
begin
dbms_output.enable(50000);
end;

To :
begin
dbms_output.enable(1000000);
end;

What is AWR( Automatic workload repository ) and How to generate the AWR report?

About:

Automatic workload repository is a collection of persistent system performance statistics owned by SYS.
It resides in SYSAUX tablespace. By default snapshot are generated once every 60min and maintained for 7 days by default.

you can generate AWR reports by running SQL scripts:

1)The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids.

2)The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids on a specified database and instance.

3) The awrsqrpt.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids. Run this report
to inspect or debug the performance of a SQL statement.

4) The awrsqrpi.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

5) The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.

6) The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

Script Location: $ORACLE_HOME/rdbms/admin

To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql
script at the SQL prompt:


Few Views which helps while generating the AWR report

DBA_HIST_SNAPSHOT
DBA_HIST_WR_CONTROL
DBA_HIST_BASELINE

How to Modify the AWR SNAP SHOT SETTINGS:
=====================================

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/


Creating the Baseline:
======================

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/


Dropping the AWR baseline:
==========================

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/


Creating SNAPSHOT Manually:
===========================

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/


Workload Repository Views:
==========================

The following workload repository views are available:

* V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
* V$METRIC - Displays metric information.
* V$METRICNAME - Displays the metrics associated with each metric group.
* V$METRIC_HISTORY - Displays historical metrics.
* V$METRICGROUP - Displays all metrics groups.
* DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
* DBA_HIST_BASELINE - Displays baseline information.
* DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
* DBA_HIST_SNAPSHOT - Displays snapshot information.
* DBA_HIST_SQL_PLAN - Displays SQL execution plans.
* DBA_HIST_WR_CONTROL - Displays AWR settings.

Export / Import a table to zipped dump file using mknode

Export a table to zipped dump file using mknode

export scripts
================
1. Make a piped node
2. Gzip the file using gzip and in background
3. Export the file using exp

export NLS_LANG=AMERICAN_AMERICA.UTF8
mknod mknod_tmp p
gzip < mknod_tmp > exp_CMKT.dmp.gz &
exp file=mknod_tmp buffer=209715200 log=exp_CMKT.log direct=y statistics=none parfile=schema_list.par

export scripts
================
1. Make a piped node
2. Gunzip the file using gunzip and in background
3. Create user and grant necessary permission to the users
4. Check for enough space both data and index
5. Import the file using exp

Import a table to zipped dump file using mknode

import scripts
======================
export NLS_LANG=AMERICAN_AMERICA.UTF8
mknod cmkt_node p
gunzip < exp_CMKT.dmp.gz > cmkt_node &
imp file=cmkt_node buffer=209715200 log=imp_cmkt.log ignore=y destroy=n parfile=cmkt_imp_par.par commit=y

How to Export and Import Statistics

How to Export and Import Statistics of tables?

procedure with a scenerio

Case Definition

A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment. It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application. The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.
The production database has several clones; we decide to export back statistics from one of these clones, to the production database.

Steps in Brief

1) Create a table to hold statistics on the source database
2) Generate a script that export table statistics on the clone database
3) Generate a script that import statistics on the clone database
4) Export statistics on clone database
5) Export table containing the exported statistics from clone database
6) Ftp export file with clone statistics table, and the script to import statistics from clone server to production server
7) Import table containing clone statistics into production database
8) Import statistics on production server using the script to import statisctics generated on the clone server

1. Create tables to hold statistics on the clone database

-- On Clone Database

SQL> execute DBMS_STATS.create_stat_table('INV','OLD_STATS');

PL/SQL procedure successfully completed.

SQL> grant SELECT,INSERT,UPDATE,DELETE on INV.OLD_STATS to public;

Grant succeeded.

2. Generate a script that export table statistics on the clone database

The purpose of this script is to generate one export statistics command per table, the export is directed into the table created on step 1.

Variables:

&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

---- script to generate export table stats start here ----------

set linesize 130 pagesize 0
spool expstat.sql
select 'exec dbms_stats.export_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',null,'||chr(39)||'&tabname'
||chr(39)||',null,true,'||chr(39)||'INV'||chr(39)||')'
from dba_tables where owner ='&usrname'
/
spool off

---- script to generate export table stats end here ----------

Note: you may also use instead of the script this command:
exec DBMS_STATS.export_schema_stats('&usrname','&tabname')
This syntax will run in 10g. It may fail on 8i – 9i databases with some objects. That’s why I prefer the script on these versions.

3. Generate a script that import statistics on the clone database
The purpose of this script is to generate one import statistics command per table, the source is the table created on step 1.
&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

---- script to generate import table stats start here ----------

set linesize 130 pagesize 0
spool impstats.sql
select 'exec dbms_stats.import_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',null,'||chr(
39)||'&tabname'||chr(39)||',null,true,'||chr(39)||'&usrname'||chr(39)||')'
from dba_tables where owner ='&usrname'
/
spool off

---- script to generate import table stats end here ----------

Execute this script to generate impstats.sql that will import the statistics on the production database.

4. Export statistics on clone database

Using the script expstat.sql; generated on step 2, export statistics into the statistics table created on step 1.

5. Export table containing the exported statistics from clone database

vmractest:/oradisk/av/expstats>exp avargas file=exp_stats_from_clone tables=avr.old_stats feedback=1000
Export: Release 9.2.0.5.0 - Production on Tue Feb 20 11:57:02 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in IW8ISO8859P8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to AVR
. . exporting table OLD_STATS
....
4115 rows exported
Export terminated successfully without warnings.

6. Ftp export file with clone statistics table from clone server to production server and script to import statistics from clone server to production server
Execute FTP session from target server, get both the table that contains the exported statistics and the script to import them, generated on step :
proddb > ftp vmractest
Connected to vmractest
220 vmractest FTP server (SunOS 5.8) ready.
Name (vmractest:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> cd /oradisk/av/expstats
250 CWD command successful.
ftp> get exp_stats_from_clone.dmp
200 PORT command successful.
150 ASCII data connection for exp_stats_from_clone.dmp (10.5.180.72,64082) (473088 bytes).
226 ASCII Transfer complete.
local: exp_stats_from_clone.dmp remote: exp_stats_from_clone.dmp
478390 bytes received in 0.17 seconds (2680.69 Kbytes/s)
ftp> get impstats.sql
200 PORT command successful.
150 ASCII data connection for impstats.sql (10.5.180.72,64776) (31461 bytes).
226 ASCII Transfer complete.
local: impstats.sql remote: impstats.sql
31704 bytes received in 0.033 seconds (947.63 Kbytes/s)
ftp> bye
221 Goodbye.

7. Import table containing clone statistics into production database

On the production database import the table that contains the exported statistics.
proddb >imp avargas file= exp_stats_from_clone.dmp full =y
Import: Release 9.2.0.5.0 - Production on Tue Feb 20 12:19:11 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
export client uses IW8ISO8859P8 character set (possible charset conversion)
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing AVARGAS's objects into AVARGAS
. importing AVR's objects into AVR
. . importing table "OLD_STATS" 4115 rows imported
Import terminated successfully without warnings.

8. Import statistics on production server using the script to import statistics generated on the clone server

Using the script impstats.sql; generated on step 3, import statistics into the production database.

Oracle Apps DBA Interview Questions Links

http://appsdba4u.blogspot.com/2007/08/oracle-apps-dba-interview-questions.html

http://appsdbablog.wordpress.com/2006/09/11/apps-dba-interview-questions

http://teachmeoracle.com/interview.html

http://onlineappsdba.com/index.php/category/intervi%20ewqs

http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1111676,00.html

http://onlineappsdba.com/index.php/2007/09/30/oracle-apps-dba-interview-questions-install

Job Scheduling in Oracle (dba_jobs)

Job Scheduling in Oracle (dba_jobs)

Solution:

Tables Used:

dba_jobs
dba_jobs_running
job_queue_processes - init parameter
Submitting a job

--exec dbms_job.submit('', , , TRUE);--

Remove a job

--exec dbms_job.remove()--

Reset a broken job:

--exec dbms_job.broken(, FALSE);--

Killing a Oracle DBMS_JOB

Viewing scheduled dbms_jobs

When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.

scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;


What Jobs are Actually Running

A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.

running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

What Sessions are Running the Jobs

Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.

session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.

2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.

As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running

Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.

9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.

Source : it-tool-box/blogs

Troubleshooting with DBMS_JOBS

Metalink Note : 313102.1

Upgrade JDK to Latest Java 6.0 in R12

Upgrade JDK to Latest Java 6.0 in R12

Solution:

Oracle Note : 455492.1

Upgrade Oracle E-Business Suite Release 12 JDK to Java 6.0 latest update
You can upgrade Java 6.0 after finishing 10.1.3.3 PS upgrade

Steps:

a) Download Latest Update of JDK 6.0 (32 bit)
http://java.sun.com/javase/downloads/index.jsp
Java SE Development Kit (JDK) 6 Update 10

b) Stop the FE services, if not down already

c) install JDK on FE code tree $IAS_ORACLE_HOME/appsutil/jdk

. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
cd $IAS_ORACLE_HOME/appsutil/

Install the downloaded latest update of JDK 6.0 here, i.e., [IAS_ORACLE_HOME]/appsutil

chmod +x jdk-6u10-linux-i586.bin
./jdk-6u10-linux-i586.bin
ln -sf jdk1.6.0_10 jdk

d) Restart FE Services

e) Download Latest Update of JRE 6.0 for DB Nodes (32 bit)
http://www.hp.com/products1/unix/java/java2/jdkjre6_0/index.html

download the tar version
gunzip jre6_16002_ia.tar.Z

NOTE: Need to check the below OS Patches are there or not?
http://docs.hp.com/en/JDKJRE60RN/jdk_rnotes_6.0.02.html#installation

The Java 5.0 Quality Pack patches must be installed
Linker patch PHSS_37201 for 11.23 systems (This patch fixes a problem loading libjli.sl)


f) Install the jre on DB nodes $ORACLE_HOME/appsutil/jre
cd $ORACLE_HOME/appsutil/
tar -xvf jre6_16002_ia.tar

NOTE: to fix java -version error export SHLIB_PATH=$SHLIB_PATH:$ORACLE_HOME/appsutil/jre/lib/PA_RISC2.0/jli

g) verify the upgraded configurations on all fe nodes...as in readme

$ADJVAPRG -version
$AFJVAPRG -version
submit a conc req

http://test-inst-09.cisco.com:8118/OA_HTML/JDKtest.jsp

Upgradation of 10g Application Server Home (10.1.3 Home to 10.1.3.3) in R12

Upgradation of 10g Application Server Home (10.1.3 Home to 10.1.3.3) in R12

Solution:

Oracle Note : 454811.1

Upgrading Oracle E-Business Suite Release 12 10.1.3 Oracle Home to Oracle 10gAS Patch Set 3 (10.1.3.3)

Steps:

To check Oracle Home Version : $ORACLE_HOME/config/ias.properties

1) make sure the FE services are up and running
$INST_TOP/admin/scripts/adopmnctl.sh status


2) 6148874
========

set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that make,ar,ld,nm are in $PATH

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/COEPCH_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

Apply the above patch through runinstaller on all the non shared FS's

unzip the patch under dir 6148874
export DISPLAY=local_host:0.0
cd 6148874/Disk1
./runInstaller -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc

Enter the user password 'secret' if default one was not changed..

3. Run root.sh

4. shutdown any services started by above installation
$INST_TOP/admin/scripts/adstpall.sh

5. Run autoconfig on all application tier nodes after 10.1.3.3 patch set installtion.

6. 6655812
=======
Apply SSO-OID Patch

Unzip the downloaded zip file into a folder
set the environment to 10.1.3

set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that make,ar,ld,nm are in $PATH

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/TEST_INST_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

Apply the above patch through runinstaller on all the non shared FS's

unzip the patch under dir 6655812
export DISPLAY=local_host:0.0
cd 6655812/Disk1
./runInstaller -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc

During installation, OUI may ask you to choose option 'Yes/No' in "Register Oracle Application Server Metadata Repository" OUI screen.
Choose 'No' and proceed further.

shutdown any services started by above installation

7. Run autoconfig on all the application tier nodes after 10.1.3.3 SSO-OID patch installation.

8. Apply the below additional patches through opatch:
6311835
5919967

6311835
=======
set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/COEPCH/inst/apps/TEST_INST_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

cd 6311835
/apps/test_inst/PATCHES/2617419/OPatch/opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc

apply the patch with optach 2617419 version to avoid below error
------------------------error ------------------------------
Invoking fuser to check for active processes.

OPatch detects your platform as 46 while this patch 6311835 supports platforms:
0 (Generic Platform)

This patch is not suitable for this operating system.
Please contact support for the correct patch.

ERROR: OPatch failed during pre-reqs check.

# Workaround: Apply the fix for OPatch bug 2617419 from Oracle Metalink at
# http://metalink.oracle.com. Then other Oracle SES one-off patches can be
# applied using OPatch.

FIX:
apply patch with opatch 2617419 version
--------------------------------------------------------------------

5919967
=======

set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/TST_INST_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

cd 5919967
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc


9) Generate product JAR files with no force option from adadmin

10) verify patch application
Invoke runInstaller on Unix and Linux or setup.exe on the Windows platform.
Click on Installed products.
Select the 10.1.3 Oracle Home and expand the tree. You will see Oracle Application Server Patchset 10.1.3.3 and 10.1.3.3 SSO-OID patch installed in the list.

Upgradation of Forms and Reports Oracle Home(10g) in R12

Upgradation of Forms and Reports Oracle Home(10g) in R12

Solution:

Metalink Note :

437878.1 Upgrading Forms and Reports 10g in Oracle Applications Release 12:

Steps:

1) Stop all services and take backup of DB and all code tree's with inventory.
2) Let us Say Form and Report OH is /apps/test_inst/tech_stat/10.1.2
3) make sure that /apps/test_inst/tech_stat/10.1.2/oraInst.loc is pointing to the right inventory location (/var/opt/oracle/oraInst.loc)
4) Apply patch 4960210 through Runinstaller ..
5) export DISPLAY=local_host:0.0
6) cd Disk1 (If you unzip the patch, you will find it)
./runinstaller (or)
./runInstaller -invPtrLoc /apps/test_inst/tech_stat/10.1.2/oraInst.loc
7)check the installed products

click next

enter the full path of the products to be installed
/apps/test_inst/PATCHES/Disk1/stage/products.xml

select destination oracle home
apps_test_inst_apps_apps_tech_st_10_1_2

path
/apps/test_inst/apps/tech_st/10.1.2/

click next

select product to update Oracle Application Server Software Update
choose "Oracle Application Server and Developer Suite 10g R2 Software Update 10.1.2.2.0"

Supply the ias_admin default password 'secret' when it prompts.

8. run root.sh when it prompts.
9. post patch:
Update File Permissions
chmod ug+x $ORACLE_HOME/sso/bin/ssoreg.sh

10. Deploy new forms.ear file as per Note 397174.1
----------------------------------------------

cp $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml_org_10152008

vi $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

oc4jadmin
OC4J Administrator
OC4J Administrator
{903}7pt3cBV4AkL6tlFqYHmHdhHbrTpDcTS8


oc4jadmin
OC4J Administrator
OC4J Administrator
!welcome


$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp
Enter Application name for re-deployment ? forms
Enter Oc4j Instance password for re-deployment ? welcome
Run Autoconfig ? No

NOTE: mention the password as welcome only.

restore back the xml files for previous password

cp $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml_org_10152008 $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

Run autoconfig on all the nodes..

11.Apply patch 5861907

a) Shutdown the iAS instance.
b) Backup the $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml file.
c) Copy the patch oraclehomeproperties.xml to $ORACLE_HOME/inventory/ContentsXML.

12) Apply the below additional patches in same order

5841985
6002686
5985861
6146823
5637184
5466491


5841985
=======
export PATH=$PATH:/apps/test_inst/apps/tech_st/10.1.2/OPatch
export ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.2

make sure that /apps/tst_inst/apps/tech_st/10.1.2/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/TEST_INST_coe-dev-17/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

make sure that JRE_LIB_DIR in /apps/test_inst/apps/tech_st/10.1.2/sysman/lib/env_sysman.mk is pointing to the correct location

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc
Make sure the output is correct. This should not error out.

cd /apps/test_inst/PATCHES/5841985
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc


6002686
=======
cd /apps/test_inst/PATCHES/6002686
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc

5985861
=======
cd /apps/test_inst/PATCHES/5985861
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc

6146823
=======
pre req

echo $OPATCH_PLATFORM_ID
export OPATCH_PLATFORM_ID=46
chmod +X $ORACLE_HOME/bin/genshlib
unzip p6146823_101220_Linux.zip
cd 6146823

sh apply.sh | tee 6146823_apply.log

Note: This will apply all these patches (5456500,5527100,5650051,5753922,5893392 and 5985840)

unset OPATCH_PLATFORM_ID

5637184
=======

cd /apps/test_inst/PATCHES/5637184
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc

5466491
=======

cd /apps/test_inst/PATCHES/5466491
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc


13. Apply interoper patch 5411711

using adpatch


14.Regenerate Jar files if the time stamp is not current in $OA_JAVA/oracle/apps/fnd/jar directory

Using adadmin

select Generate Applications Files > Generate Product JAR Files
(Do not force the regeneration of all JAR files)

15. Start services and verify the upgrade.

a) Log on to Applications as any user, launch any form, select About Applications from the Help menu,
and confirm that the Forms version (under Forms Server) is 10.1.2.2.0.

b) Execute following command,to display various component versions.
This command requires DISPLAY environment variable to be set correctly.

$ORACLE_HOME/bin/frmcmp help=y

parallelism in Oracle?

Complete about Oracle Parallelism?

Solution:

Following initialization parameters are required for parallelism setup in database.

PARALLEL_SERVER,
PARALLEL_SERVER_INSTANCES,
PARALLEL_MIN_SERVERS,
PARALLEL_MAX_SERVERS?
PARALLEL_THREADS_PER_CPU

Parallel Queries and Parallel jobs execution

1. Select query with parallelism example:

Select /*+ parallel (a,32) */ * from dba_segments a;

32 is degreee here(Numbers of parallel processes)

Gather statistics of a table using parallelism Example

exec FND_STATS.GATHER_TABLE_STATS (ownname => '&owner', tabname => '&table_name', percent => 20 ,degree => 30 , granularity => 'ALL', cascade => TRUE);

Comple objects in the database with parallelism Example

exec sys.utl_recomp.recomp_parallel(32);

Table used to know number of parallel processes running are

v$px_session - Standard
gv$px_session - For RAC

Difference between DROP,Truncate and Delete in Oracle?

Difference between Truncate and Delete in Oracle?

Answer:

1.TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.

2. DELETE is a DML command and can be rolled back.

3. TRUNCATE : You can't use WHERE clause and DELETE : You can use WHERE clause

4. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

5.Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.Delete: You can keep object's statistics and all allocated space.

6. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

7. Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure, so no need to give specifications for another table creation.

8. Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

9. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

10. Truncate will not use the undo TBS, whereas a delete will.

What is High Water Mark in Oracle?

What is High Water Mark in Oracle?

Answer:

1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

2. Delete Table operation won't reset HWM.

3. TRUNCATE will reset HWM.

4. The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

I hope now you understand well. see the below example.

for example, if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,

At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.


Explanation in Detail:
http://asktom.oracle.com/pls/asktomf?p=100:11:0::::P11_QUESTION_ID:492636200346818072

Check Concurrent Manager Status from the Back end?

How to check Concurrent Managers Status from the Back End?

select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));

Find out Explain Plan of Session

How to get explain plan of Session?

set lines 120
select
lpad(' ', 2*(depth))||operation||' '||options
||' '||decode(object_owner,null,null,
object_owner||'.'||object_name)
||' '||decode(cost,null,null,'Cost = '||cost)
||' '||decode(partition_start,null,null,
'Partition='||partition_start||'..'||partition_stop)
||' '||decode(bytes,null,null,'Bytes= '||bytes)
-- ||' '||decode(access_predicates,null,null,'Access= '||access_predicates)
-- ||' '||decode(filter_predicates,null,null,'Filter= '||filter_predicates)
--, other,other_tag, temp_space
"Execution Plan"
from v$sql_plan
where (address, hash_value) =
(select sql_address, sql_hash_value
from v$session
where sid = &sid)
order by id
/
undefine 1

Note: Pass SID when it prompts

Find out Session Waits in a Database

How to find out Session wait events in a Database?

column event format a41
set pagesize 100

select a.event,count(*),avg(b.wait_time)
from v$session a,v$session_wait b
where a.sid = b.sid
and a.type != 'BACKGROUND'
group by a.event
order by count(*) desc
/

How to find out sid of a query running?

How to find out session id of a sql that is running?

SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%ALTER INDEX%';

Note: Where sql_text is some unique part of the sql running

Check Redolog count hourly basis

How to find , how many redo switches happening every hour?

select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/

Enabling Flashback Database

To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here.

1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SELECT STATUS FROM V$INSTANCE;

2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

3. Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE test1 FLASHBACK OFF;


You can re-enable flashback logging for a tablespace later with this command:

SQL> ALTER TABLESPACE test1 FLASHBACK ON;


Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command:

SQL> ALTER DATABASE FLASHBACK OFF;

You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment.

Requirements for Flashback Database

The requirements for enabling Flashback Database are:

* Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
* You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
* For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

Oracle Flashback Table: Returning Individual Tables to Past States

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability and faster restoration of data.

Prerequisites for Using Flashback Table

The prerequisites for performing a FLASHBACK TABLE operation are as follows:

* You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
* You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
* Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
* Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE table ENABLE ROW MOVEMENT;

Performing Flashback Table

The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table employee:

FLASHBACK TABLE employee TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');

The employee table is restored to its state when the database was at the time specified by the timestamp.

You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:

FLASHBACK TABLE employee TO SCN 123456;

The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:

FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;

The following scenario is typical of the kind of logical corruption where Flashback Table could be used:

At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;

Source: http://www.stanford.edu

Oracle Flashback Query: Recovering at the Row Level

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';

Restoring John's information to the table EMPLOYEE requires the following update:

INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

Oracle Flashback Technology: Overview

Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.

* Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
* Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
* Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
* Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.

* Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
* Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.

Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.

Flash Back Feature..

1. How to delete a table permanently?
Usage: drop table purge
eg: drop table emp purge;

2.How to delete a table from recycle bin?
Usage: purge < table name >
eg: purge emp;

3. How to recover a table from recycle bin?
Usage: flashback table "HBKLOEKIMO$#^^&777" to before drop;
Usage1:flashback table "HBKLOEKIMO$#^^&777" to before drop rename to emp1;

4.How to remove all objects in recyclebin?
Usage: purge recyclebin

5. How to show all objects in recyclebin?
Usage: show recyclebin

Find files greater than 3 GB in HP unix

How do you find files that are greater than 3GB in HP unix?

find / -size +3000000000c -exec ls -l {} \;

Using srvctl to Manage your 10g RAC Database

Oracle recommends that RAC databases be managed with srvctl, an Oracle-supplied tool that was first introduced with 9i RAC. The 10g version of srvctl is slightly different from the 9i implementation. In this article, we will look at how -- and why -- to manage your 10g databases with srvctl.

Interacting with CRS and the OCR: srvctl

srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.

Using srvctl

Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.

srvctl must be run from the $ORACLE_HOME of the RAC you are administering. The basic format of a srvctl command is

srvctl [options]

where command is one of

enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config

and the target, or object, can be a database, instance, service, ASM instance, or the nodeapps.

The srvctl commands are summarized in this table:



As you can see, srvctl is a powerful utility with a lot of syntax to remember. Fortunately, there are only really two commands to memorize: srvctl -help displays a basic usage message, and srvctl -h displays full usage information for every possible srvctl command.

Examples

Example 1. Bring up the MYSID1 instance of the MYSID database.

[oracle@myserver oracle]$ srvctl start instance -d MYSID -i MYSID1

Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.

[oracle@myserver oracle]$ srvctl stop database -d MYSID

Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.

[oracle@myserver oracle]$ srvctl stop nodeapps -n myserver

Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.

[oracle@myserver oracle]$ srvctl add instance -d MYSID -i MYSID3 -n myserver

Example 4. Add a new node, the mynewserver node, to a cluster.

[oracle@myserver oracle]$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A
149.181.201.1/255.255.255.0/eth1

(The -A flag precedes an address specification.)

Example 5. To change the VIP (virtual IP) on a RAC node, use the command

[oracle@myserver oracle]$ srvctl modify nodeapps -A new_address

Example 6. Find out whether the nodeapps on mynewserver are up.

[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver

Example 7. Disable the ASM instance on myserver for maintenance.

[oracle@myserver oracle]$ srvctl disable asm -n myserver

Debugging srvctl

Debugging srvctl in 10g couldn't be easier. Simply set the SRVM_TRACE environment variable.

[oracle@myserver bin]$ export SRVM_TRACE=true

Let's repeat Example 6 with SRVM_TRACE set to true:

[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
/u01/app/oracle/product/10.1.0/jdk/jre//bin/java -classpath
/u01/app/oracle/product/10.1.0/jlib/netcfg.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/rt.jar:
/u01/app/oracle/product/10.1.0/jdk/jre//lib/i18n.jar:/u01/app/oracle/product/10.1.0/jlib/srvm.jar:
/u01/app/oracle/product/10.1.0/jlib/srvmhas.jar:/u01/app/oracle/product/10.1.0/jlib/srvmasm.jar:
/u01/app/oracle/product/10.1.0/srvm/jlib/srvctl.jar
-DTRACING.ENABLED=true -DTRACING.LEVEL=2 oracle.ops.opsctl.OPSCTLDriver status nodeapps -n
mynewserver
[main] [19:53:31:778] [OPSCTLDriver.setInternalDebugLevel:165] tracing is true at level 2 to
file null
[main] [19:53:31:825] [OPSCTLDriver.:94] Security manager is set
[main] [19:53:31:843] [CommandLineParser.parse:157] parsing cmdline args
[main] [19:53:31:844] [CommandLineParser.parse2WordCommandOptions:900] parsing 2-word
cmdline
[main] [19:53:31:866] [GetActiveNodes.create:212] Going into GetActiveNodes constructor...
[main] [19:53:31:875] [HASContext.getInstance:191] Module init : 16
[main] [19:53:31:875] [HASContext.getInstance:216] Local Module init : 19
...
[main] [19:53:32:285] [ONS.isRunning:186] Status of ora.ganges.ons on mynewserver is true
ONS daemon is running on node: mynewserver
[oracle@myserver oracle]$

Pitfalls

A little impatience when dealing with srvctl can corrupt your OCR, ie, put it into a state where the information for a given object is inconsistent or partially missing. Specifically, the srvctl remove command provides the -f option, to allow you to force removal of an object from the OCR. Use this option judiciously, as it can easily put the OCR into an inconsistent state.

Restoring the OCR from an inconsistent state is best done with the assistance of Oracle Support, who will guide you in using the undocumented $CRS_HOME/bin/crs_* tools to repair it. The OCR can also be restored from backup.

Error messages

srvctl errors are PRK% errors, which are not documented in the 10gR1 error messages manual. However, for those with a Metalink account, they are documented on Metalink here.

Conclusion

srvctl is a powerful tool that will allow you to administer your RAC easily and effectively. In addition, it provides a valuable buffer between the DBA and the OCR, making it more difficult to corrupt the OCR.

Note: Info from Natalka Roshak's blog

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.

CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.

CRS manages the following resources:

* The ASM instances on each node
* Databases
* The instances on each node
* Oracle Services on each node
* The cluster nodes themselves, including the following processes, or "nodeapps":
o VIP
o GSD
o The listener
o The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.

RAC Architecture in Brief

RAC Architecture Overview

1.A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.

2. A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.

3.Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Concurrent Program Phases and status List

Concurrent Program Phases and status List

Phase_Code :

'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',

Status_Code :

'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',

Table: apps.fnd_concurrent_requests

Check all logs for ORA- errors

Check all logs for ORA- errors

grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -u

Get the 10046 trace for a Session?

How to take 10046 trace for a Session?

1. Get the SID from the user?
2. Get the OS Process ID (SPID) from v$session.
3. Use following commands as / as sysdba

oradebug setospid 420
oradebug unlimit
oradebug EVENT 10046 trace name context forever, level 12
oradebug tracefile_name
oradebug event 10046 trace name context off

JInitiator 1.1.8.7 Crash when launch it from IE

JInitiator 1.1.8.7 Crash when launch it from IE

Fixes

1. Try Uninstall existing JInitiator 1.1.8.7 and install the same

2. If the above dont work.. Try the following

Rename symcjit.dll into somethnig else ex: symcjit_old.dll

path is C:\Program Files\Oracle\JInitiator 1.1.8.16\bin

Get Concurrent Request ID from SID

Get Concurrent Request ID from SID:

select c.request_id, status_code, phase_code, USER_CONCURRENT_PROGRAM_NAME,d.use
r_name requestor, s.sid,p.spid,s.process,s.osuser
,s.username,s.program,s.status,logon_time,last_call_et where sid in ( select sid from gv$access where object like '%XXCCP_OE_ORDER_HEADERS_ALL_QTC%');
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurren
t_programs_tl ct, apps.fnd_user d
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id 11 00:14 show_rid.sql
and c.requested_by = d.user_id
and s.sid = '&enter_sid'

Check package is used by any session in RAC DB?

select inst_id,sid,serial#,status from gv$session where sid in ( select sid from gv$access where object like '%E_ORDER_HEADERS_ALL_%');

Important Apps Tables

Important Applications tables?

Users
fnd_user
ak_web_user_sec_attr_values (securing attributes)
fnd_user_resp_groups_direct ( direct responsibilities)


Define Responsibility
fnd_responsibility_vl
Menu Exclusions - fnd_resp_functions ( type, name, desc)

Request Groups
fnd_request_groups (header)
fnd_request_group_units (details)

Oracle Users
fnd_oracle_userid

Data Groups
fnd_data_groups (header)
fnd_data_group_units_v

Requests
fnd_conc_req_summary_v (main)

Request Set
fnd_request_sets_vl (main)
fnd_req_set_stages_form_v (stages)
fnd_request_set_programs (stage_requests)
fnd_descr_flex_col_usage_vl (request parameters)
fnd_req_set_stages_form_v (link stages)

Concurrent Managers
fnd_concurrent_queues_vl (main)
fnd_concurrent_queue_content (specialization rules)
fnd_concurrent_queue_size (work shifts)

work shifts
fnd_concurrent_time_periods

concurrent programs
fnd_concurrent_programs_vl (main)
fnd_descr_flex_col_usage_vl (parameters)
fnd_executables_form_v (executables)
fnd_conflict_domain ( concurrent conflicts domain)

profile
fnd_profile_options_vl

applications
fnd_application_vl

form functions
fnd_form_functions_vl (description)

menus
fnd_menus_vl (header)
fnd_menu_entries_vl (detail)

value sets
fnd_flex_value_sets, fnd_flex_values,


key flexfield segments
fnd_id_flexs
fnd_id_flex_segments_vl
fnd_segment_attribute_values (qualifiers)

Concurrent Managers

FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES
FND_CONCURRENT_QUEUE_SIZE

Important FND

fnd_nodes
fnd_responsibility
fnd_databases
fnd_unsuccessful_logins
fnd_languages
fnd_application
fnd_profile_option_values
fnd_logins
fnd_user
fnd_appl_tops
fnd_dm_nodes
fnd_tns_aliases

Important AD

ad_install_processes
ad_sessions
ad_appl_tops
ad_applied_patches
ad_patch_drivers
ad_bugs

Pinning objects in the Database

How to ping required objects in the Database?

Solution:
Use following syntax

BEGIN
SYS.DBMS_SHARED_POOL.KEEP(''HR.QP_PRIC_RQUEST','P');
end;

HR - is the Owner of the object
QP_PRIC_RQUEST- Object to be pinned
P- Default parameter

Table to know pinning objects status

v$db_object_cache

Undo/Rollback Segment used

How do you know How much Undo/Roll back Segment each session consuming ?

COLUMN USERNAME FORMAT a10
COLUMN status FORMAT a8
set linesize 100
set pagesize 15

Rem # rbs_used.sql
set feedback on
set linesize 132
set echo off
column RBS_NAME format a10
TTITLE 'Total Blocks used by a user in a rbs'
SELECT s.username,
s.sid,
t.used_ublk*8192/1024/1024 "RbsUsed(MB)",
t.used_urec,
t.log_io,
t.phy_io,
-- t.xidusn,
r.usn ,
r.name "RBS_NAME",
t.start_uext,
t.start_time,
t.status
FROM v$transaction t, v$session s, v$rollname r
WHERE t.addr = s.taddr
and r.usn = t.xidusn
order by s.sid
/

Find out Top 10 Memory consuming processes?

How to Find out Top 10 Memory consuming processes?

UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep oracle | tail -10

Pass Database Name in places of SID

Find out Mount points Which exceeds 80%

Find out mount points starts with oracle which exceeds 80%?

bdf|grep -e oracle grep '[7-9].%'

Find out Package Locks

Use Following Script:

CLEAR COL BREAK COMPUTE
SET PAGES 100 PAUSE OFF VERIFY OFF FEEDBACK ON ECHO OFF

COL object_name FORMAT A35
COL type FORMAT A20
COL sid FORMAT 9999
COL held FORMAT A5
COL request FORMAT A7

SELECT session_id sid,
owner||'.'||name object_name,
type,
mode_held held,
mode_requested request
FROM dba_ddl_locks
WHERE name LIKE UPPER('%&object_name%')
/

SET PAGES 32 PAUSE OFF VERIFY OFF FEEDBACK ON ECHO OFF
PROMPT

When prompts give Package name

Tablespace Usage Info (Free Space, Used Space, Percentage)

Solution:

select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
Note: Pass Tablespace whenever you are prompted

Tuning the Concurrent Manager

All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:

* Tuning the Concurrent Manager
- Tuning the Internal Concurrent Manager
- Purging Concurrent Requests
- Troubleshooting Oracle Apps performance problems
- Adjusting the Concurrent Manager Cache Size
- Analyzing the Oracle Apps Dictionary Tables
* Monitoring Pending Requests in the Concurrent Manager
* Changing the dispatching priority within the Concurrent Manager

Tuning the Internal Concurrent Manager (ICM)

The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.

* PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
* Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
* Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.

All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment.

An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.

Purging Concurrent Requests

One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications.

When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.

Adjusting the Concurrent Manager Cache Size

Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.

Analyzing Oracle Apps Dictionary Tables for High Performance

It is also very important to run the request Gather Table Statistics on these tables:

* FND_CONCURRENT_PROCESSES
* FND_CONCURRENT_PROGRAMS
* FND_CONCURRENT_REQUESTS
* FND_CONCURRENT_QUEUES.


Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.

To troubleshoot performance, a DBA can use three types of trace.

A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications.

Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace.

Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.

Monitoring Pending Requests in the Concurrent Managers

Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:

1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.

When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.

To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:

update fnd_concurrent_requestsset status_code='X', phase_code='C'where status_code='T';

Changing Dispatching Priority within the Concurrent Manager

If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority. Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request.

If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.

Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:

afcmstat.sql : Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql : Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.

afcmcreq.sql : Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql : Displays the requests that are pending, held, and scheduled.

afrqstat.sql : Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql : Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.

afimlock.sql : Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

Something about Concurrent Managers

Concurrent Managers

The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.

This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.

The Master Concurrent Managers

There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:

* Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
* Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
* Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.

Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.

Recreate Database Link

Solution:

col OWNER for a15
col DB_LINK for a25
col USERNAME for a15
col HOST for a25
set linesize 120
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK in ('DB_LINK_NAME')

1.Once, the password has been given by client, then use the following statement to recreate the DBLINK,

create database link "DB_LINK_NAME" connect to TARGET_USER IDENTIFIED BY TARGET_PASS using 'TARGET_SID';

2. Verify whether DB Link is working by using the following statement,

select * from dual@DB_LINK_NAME;

3.It should return 1 row. "

Enable FRD ( Forms Runtime Diagnostic ) Trace??

Solution:

1. Login into SYSADMIN --> System Administrator --> Profile --> System
Search the profile option for "ICX Forms Launcher" and Make sure Site and User leave has been checked.

2. Set the value following values at user level only according to the version

3. For R12, http://url:/OA_HTML/frmservlet?record=collect

4. For 11i, http://url:/dev60cgi/f60cgi?&record=collect&log=<>

5. Get the FRD trace from $FORMS_TRACE_DIR path at OS Level

Create Orignal Package Specification and body if it is corrupted?

Solution:

1. select text from dba_source where name='AC_PACKAGE_IC' and rownum < 10
2. You will get .pls file
3. Take a backup of current package body and specification by spooling
4. Goto That top/patch/115/sql/
5. Execute .pls files for both body spec

How to Analyze a table partition ?

Solution:

exec dbms_stats.gather_table_stats(ownname=>'ctsblr_o',tabname=>'ss_incide', partname=>'ss_incide1', 'estimate_percent' =>20, degree=>20,granularity=>'partition',cascade=>true)

Concurrent requests show Inactive No Manager? Then?

Solution:

1. Get the request id from the user,
2. Go to SYSADMIN --> System Administrator --> Others --> View Requests screen, search for the request. Click on Tools --> Manager log to see under which manager this program is being executed. Check the status of the manager in Concurrent --> Manager --> Administer and take necessary action.
3. If the Manager screen doesn't show any entry, this means the program is not attached to any manager. The application team needs to include this program under one of the managers.

How to register Jar File in Class Path?

Solution:

1. Make an entry in jserv.properties under Classpath section.
2. Add a new liine as "wrapper.classpath=".
3. Bounce the Apache.

How to rebuild Index Online with parallel?

Solution:

1. alter index APPS.CSS_SUMMARY_ST rebuild parallel 32; (After completion use following)
2. alter index APPS.CSS_SUMMARY_ST no parallel

How do you check whether Trace is enabled to particular concurrent program from the back end?

Solution:

1. select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Program Name%'; (You will get Id)

2. select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id ='concurrent program id';

Get the Temporary Table Space information?

Solution:

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;

or

SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;

(pass block_size(get it from show parameter block_size))

How do you compile invalid objects?

Solution:

@ORACLE_HOME/rdbms/admin/utlrp.sql or
exec sys.utl_recomp.recomp_parallel(32);

How do you collect Schema Level Statistics?

Solution:

set time on;
set timing on;

exec dbms_stats.gather_schema_stats(OWNNAME=>'AR','EST_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;

How do you collect Table level Statistics?

Solution:

exec dbms_stats.gather_table_stats(OWNNAME=>'MP',TABNAME=>'MP_FORAST_INTERFACE','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;

How do you know which concurrent manager is taking care of your request?

Solution:

sqlplus apps/appspwd @$FND_TOP/sql/@afcmcreq.sql
(Give Request number when prompts)

How do you get Oracle Process id from the concurrent request id?

Solution:

select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;

How do you know complete Concurrent program details ?

Solution:

1. Get the concurrent program Name.. Give it after running following script

col Program format a40;
col ARGUMENT_TEXT format a30;
col Interval format a10;
col requestor format a20;
set linesize 140;
set pagesize 999;

select distinct a.request_id,a.user_concurrent_program_name "Program",a.ARGUMENT_TEXT,to_char(a.REQUESTED_START_DATE,'DD/MM/YYYY HH24:MI:SS') "Start Date" ,a.RESUBMIT_INTERVAL||' '||a.RESUBMIT_INTERVAL_UNIT_CODE "Interval",requestor FROM apps.FND_CONC_REQUESTS_FORM_V a where
a.concurrent_program_id in (select CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '&1') and a.phase_code='P';

Tkprof Usage?

Solution:

tkprof coestg1_trc coestg1_.trc_tkprof sys=no explain=apps/appspwd sort='(fchela,exeela,prsela)'

How do you know, Your node details in your Oracle applications?

Solution:

SELECT node_name,support_cp,support_forms,support_web,support_admin,
status FROM apps.fnd_nodes ORDER BY 2,3,4;

How do you know your apps URL?

Solution:

select home_url from apps.icx_parameters;

or

grep -i login $APPL_TOP/admin/Context_name.xml

How do you kill Bulk number of sessions at a single Time?

Solution:

set head off
set pagesize 1000
spool kill.sql

select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='MT' AND status='INACTIVE';

spool off
@kill.sql

How do you assign priviliges to a user, same it has priviliges in another user?

Solution:

SELECT 'GRANT ' PRIVILEGE ' ON ' OWNER '.' TABLE_NAME ' TO newusername;' FROM DBA_TAB_PRIVS WHERE GRANTEE = 'firstusername'

How do you view source code of view?

Solution:

desc dba_views;
Set long 20000
Set pagsize 10000
Spool viewsource.txt

select text from dba_views where view_name=’VIE_SOURCE’;

Spool off

FNDCPASS Usage

How to user FNDCPASS to change the passwords of a Oracle Apps Front End Passwords/Database Product Passwords/Apps Passwords?


Solution:

1. To Change Front End application password for a user?

FNDCPASS apps/appspwd 0 Y system/systempwd USER username passwd

Example: FNDCPASS apps/apps0 Y system/manager USER test test123

2. To Change Database Product Password?

FNDCPASS apps/appspwd 0 Y system/systempwd ORACLE prodschema passwd

Example: FNDCPASS apps/apps 0 Y system/manager ORACLE GL glnew

Note: After Execution of FNDCPASS it will generate two files one is 282990.log and 32123.out files

Open the log file and see whether concurrent program successfully executed or not. If so, Everything ran successfully

Form/Reprot Compilation/Generation

1. How do you compile a form command line?

appltop/apps/ora/8.0.6/bin/f60gen module=/appltop/apps/au/11.5.0/forms/US/FNDRSRUN.fmb userid=APPS/apps output_file=/appltop/apps/fnd/11.5.0/forms/US/FNDRSRUN.fmx module_type=form batch=yes compile_all=special

2. how do you generate a report command line?

/appltop/apps/ora/8.0.6/bin/rwcon60 userid=APPS/apps source=/tappltop/apps/pa/11.5.0/reports/US/PAXPCEGS.rdf dest=/tappltop/apps/admin/INTGBL/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/tappltop/apps/admin/INTGBL/out/adrep001.txt overwrite=yes batch=yes compile_all=yes

3. How do you load data to db using FNDLOAD?

/appltop/apps/fnd/11.5.0/bin/FNDLOAD &ui_apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct @PJI:patch/115/import/US/pji115fn.ldt

what Sqls/scripts/modules are running in the databases by what users?

Solution: Use the following script

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username'('sid','serial#')' username, module, action, client_infofrom v$sessionwhere moduleactionclient_info is not null;

Know Currnet SQL Running

1. How do you know what is the current SQL Running in the Database?

Solution: use the folowing script

column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in ( select username'('sid','serial# ') ospid = ' process ' program = ' program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, LAST_CALL_ET
from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) max(decode(piece,1,sql_text,null)) max(decode(piece,2,sql_text,null)) max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where address = x.sql_address and piece < 4)
loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time ' ' x.current_time ' last et = ' x.LAST_CALL_ET); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;/

Table Locks and Remove Table locks

1. How do you know What are the objects have locks??

Solution:

select * from v$lock where block<>0;

if block=0 then no locks if block=1 then there is locks

2. How do you know locked tables? and how do you remove locks?

Solution:

1. select a.object_id, a.session_id, substr(b.object_name, 1, 40)from v$locked_object a,dba_objects bwhere a.object_id = b.object_idorder by b.object_name ;
2. select sid, serial#, command, taddr from v$session where sid=
3. alter system kill session 'sid,serial#';

Rollback Segments Issue/Fix/WorkAround

1. If you get an error like unable to extend rollback segment like below error?

ERROR at line 1: ORA-01562: failed to extend rollback segment number 10 ORA-01628: max # extents (150) reached for rollback segment R09 ORA-06512: at line 139

Solutioin:

1. SELECT segment_name, max_extents FROM dba_rollback_segs;
2. alter rollback segment r01 storage (maxextents unlimited)

Enabling Auditing for a user

How to enable audit for a particular user?

audit insert table,delete table,update table by newene;

ORA-1555 failures ("snapshot too old") Issue/Fixes/WorkAround

If you get ORA-1555 Failures or SnapShot Too Old errors

Solution:

1. Increase the size of undo tablespace or
2. Increase the undo_retentoin time

Steps to Fix the issue

1. First you find out what is the undo tablespace is needed for your database
2. Second you find out what is the undo retentino time is needed for your database

---Using following Scripts -----

Script #1

select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION (Secs)"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size'

/

Script #2

select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "
NEEDED UNDO SIZE (MEGS)"
from (select sum(a.bytes) undo_size
from v$datafile a, v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention' and f.name = 'db_block_size'
/

You will get Ideal Undo_tablespace size and Undo_retention time. According that outputs, Set your values as follows

If it is just problem with undo_retention, then do as follows

1. Update init.ora with the following undo_retention=36600
2. Bounce the database

If it is just problem with undo_tablespace size, then do as follows

Solution in this situation is to create a NEW UNDO tablespace with smaller size and switch over to your current UNDO tablespace to NEW UNDO tablespace.

Example:
/* Create new undo tablespace with smaller size
*/SQL> create undo tablespace undotbs2 datafile ‘/u01/oradata/decipher/undotbs2_01.dbf’ size 1024m autoextend on next 256m maxsize 10240m;
/* Set new tablespace as undo_tablespace */SQL> alter system set undo_tablespace= UNDOTBS2 scope=both;
If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This is normally to un-assign an undo tablespace in the event that you want to revert to manual undo management mode.
/* Drop the old tablespace */SQL> drop tablespace UNDOTBS1 including contents;

Temporary Tablespace Issue

If your Temporary Tableespace becomes 100%

Solution:

Drop and Re Create the Temporary Tablespace

Steps to Fix the issue

1. alter database backup controlfile to trace;
2. select file_name,tablespace_name from dba_temp_files;
3. create temporary tablespace tempbkp tempfile '/y01/orcl/dat/tempbkp.dbf' size 10m;
4. alter database default temporary tablespace tempbkp;Database altered.
5. select temporary_tablespace from dba_users;
6. drop tablespace temp including contents and datafiles;
If above statement doesn't work. Delete each datafile of the particular temporary tablespace as follows
alter database tempfile '/temp/orcle/dat/temp05.dbf' drop including datafiles;
7. alter tablespace temp add tempfile'/temp/orcl/dat/temp01.dbf' SIZE 118489088 reuse autoextend on 1048576 maxsize 8500m;
8. alter database default temporary tablespace temp;
9. drop tablespace tempbkp including contenets and datafiles

Oracle Apps DBA Interview Questions

Oracle Apps DBA - Interview Questions

1. what is the utility to change the password of a dba schema in oracle apps? Ans: FNDCPASS
2. what are mandatory users in oracle apps?
Ans: applsys,applsyspub,apps
3. What simplay a oracle Architechture?
Ans: Desktop Tier, Application Tier, Database Tier 5. What are the components in the Application Tier?
Ans: Apache(http)
Jserver(jre)
Forms Server(f60srv)
Metric Server(d2ls)
Metric Client(d2lc)
Report Server(rwm60)
Concurrent Server(FNDLIBR)
Discoverer 6.What are main file systems in Oracle Apps?
Ans: APPL_TOP, COMMON_TOP,
DB_TOP,ORA_TOP 7. What are there in Desktop Tier?
Ans: Internet Browser, JInitiator 8. What is the location of JInitiator in the Desktop Tier?
Ans: c:\program files\oracle\Jinitiator 9. What is the location of client cache?
Ans: c:\documents and settngs\user\oracle jar Cache 10. What is the location of Server cache?
Ans: $COMMON_TOP/_pages
11. Which package will be used for the user validation by plsql agent?
Ans: oraclemypage
12. What are adadmin utilities? and Its location?
$AD_TOP/bin
Ans: 1.adadmin
2.adpatch
3.adsplice
4.adident
5.adrelink
6.adlicmgr
13.What are the location of JaVA Files?
Ans: JAVA_TOP and all PRODUCT_TOP/Java/Jar
14. What is the name of the xml file of Apps and its location?
Ans: Context Name.xml and $APPL_TOP/admin
15. what is the location of Apps environment file? and its name?
Ans: contextname.env and $APPL_TOP
16. In how many way Jar files are generated?
Ans: Normal and Force
17. Once Jar files are generated what files get effected?
Ans: All Product_top/java/jar files and Two files in JAVA_TOP they are appsborg.zip
appsborg2.zip
18. How do you see the files in zip file?
Ans: unzip -v
19.How do you generate jar files?
Ans: Using adadmin and option 5
20. How do you start the apps services?
Ans: $COMMON_TOP\admin\scripts\Contextname\adstrtal.sh apps/appspwd
21. What is the executable to generate jar files?
Ans: adjava
22. How do you relink a executable of a product
Ans: by relinking option in adadmin or adrelink
23. How do you relink AD product executable? and usage?
Ans: adrelink.sh and adrelink.sh force=y "ad adsplice"
24.When do you relinking?
Ans: 1. when you miss a executable file
2. When there is a problem with any executable file
3. When any product executable get currupted
25. What is DAD?
Ans: It is a file which stores apps passwords in hard coded format. i.e wdbsvr
26.How do you relink OS files and libraries?
Ans: using make command
27.What is compile scheman option in adadmin?
Ans: This option is used to compile/resolve the invalid objects
28. Where do you get the info about invalid objects?
Ans: from dba_objects where status=invalid

29.How do you compile an obect ?
Ans: alter object_ type objet _name compile. Eg: alter table fnd_nodes compile
30.How do you see the errors of a table or view?
Ans: select text from dba_errors where name='emp_view'
31. How do you see the errors in the db?
Ans: show error
32. How do you compile a schema?
Ans: using utlrp.sql (location is ?/rdbms/admin/) or
going adadmin, compile schema option
33. How do you know how many invalid objects are in specific schema?
Ans: select count(*) from dba_objects where status='INVALID' group by owner;
34. How do you know the package version?
Ans: select text from dba_source where name='package name' and type='PACKAGE BODY' and rownum<10>/rdbms/admin)
41. How do you load java class to databae?
Ans: loadjava
42. What are restart files? and its location?
Ans: These files contains the previouse session info about adadmin.. location is $APPL_TOP\admin\sid\restart\*.rf9
43.How do you validate apps schema?
Ans: To validate synonyms, missing sysnonyms and all grant. You can do it in adadmin. after validating it iwll produce
a report in the location $APPL_TOP\admin\sid\out\*.out
44. How do you enable maintainance mode?
Ans: using adadmin or running a script called "adsetmmd.sql ENABLE/DISABLE" (AD_TOP/patch/115/sql)
45.What is APPS_MRC Schema?
Ans: It is used for multi language support. To synchronize APPs schema and APPS_MRC
46. How to see the version of a script or form or report or etc?
Ans: grep Header adsetmmd.sql or adident Header adsetmmd.sql
strings -a GLXSTEA.fmx grep Header or adident Header GLXSTEA.fmx
47.What is the location of adadmin log?
Ans: $APPL_TOP\admin\sid\log
48. What are the oracle homes in Apps?
Ans: 8.0.6ORACLE_HOME(Dev 6i products) and IAS_ORACLE_HOME (Apache)
49. How do you configure you ipaddress at client side? and server side?
Ans: c:\windows\system32\drivers\etc\hosts and \etc\host
50. What is the location of Datbase server related scripts?
Ans: $ORACLE_HOME\appsutil\scripts\contextname
51. what is the utility to clean the concurrent manager?
Ans: @cmclean.sql ( You have download from metalink)
52. How do you stage the 11.5.10 Apps software?
Ans: using adautostg.pl
53. What is the location of the source files of forms?
Ans: AU_TOP/forms/US/
54. What is the executable to generate forms?
Ans: f60gen

Metalink Important Note IDs

1. How to run OATM migration utility ---- Note:404954.1

2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1

3. Upgrading Oracle Applications ---- Note:289788.1

4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1

6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1

7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1

8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1

9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1

10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1

11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1

12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1

13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1

14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1

15.Shared APPL_TOP FAQ ---- Note:243880.1

16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1

17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1

18.Release 11i Adpatch Basics ---- Note:181665.1

19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1

20.How to Create a Custom Concurrent Manager ---- Note:170524.1

21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1

22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

Oracle 10g 2Day Training-1

Oracle 10g 2Day Training-1 - Upload a Document to Scribd

Read this document on Scribd: Oracle 10g 2Day Training-1

Oracle Database How To..

oracle database - howto-1 - Upload a Document to Scribd

Read this document on Scribd: oracle database - howto-1

Oracle DBA Interview Questions

oracle interview questions 1 - Upload a Document to Scribd

Read this document on Scribd: oracle interview questions 1

Oracle Apps DBA Important Tables

Oracle Apps DBA Important Tables


fnd_user

fnd_oracle_user_id

fnd_concurrent_requests

fnd_concurrent_programs_tr

fnd_concurrent_programs

fnd_responsibility

fnd_profile_option_values

fnd_menus

fnd_nodes

fnd_product_installations

fnd_application

fnd_product_groups

fnd_install_processes

ad_deferred_jobs

ad_applied_patches

ad_bugs

Oracle Database Important Tables

Oracle DBA Important Tables



v$database

v$controlfile

dba_data_files

v$datafile

dba_temp_files

v$tempfile

v$tempspace_header

dba_users

dba_db_links

v$link

dba_objects

dba_tables

dba_indexees

dba_ind_columns

dba_tab_columns

dba_sequences

dba_synonyms

dba_source

dba_views

dba_errors

dba_triggers

dba_roles

dba_role_privs

session_roles

dba_tablespaces

dba_tab_privs

role_tab_privs

session_privs

dba_ts_quotas

dba_registry

dba_rollback_segs

dba_properties

How to apply India Localization Patches

How to apply India Localization Patches




1. How to apply india Localizatin Patches?


1. We have to install India localization Patch Application Tool by downloading patch 6491231


2. Copy the downloaded patch to $JA_TOP and unzip the same there


3. A directory inpatch will be created afer unzipping. In which india localization patch tool avaialable


4. Go to india localization patch directory


5. use following command


perl $JA_TOP/inpatch/indpatch.pl drvr_file=6355941.drv fnd_patchset=H appspwd=apps japwd=ja logfile=6355941.log systempwd=manager


fnd_patchset= FND PATCHSET LEVEL

japwd = ja_top password

drv_file=patchnumber.drv file


2. How do you know what are india localization patches applied?

Solution : using JAI_APPLIED_PATCHES

Unix Important

1. How do you delete 3 days old log files?

Usage: find /location -name "*.log" -mtime +3 -exec rm -rf{} \;

Example : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;

2. Display latest 20 largest files/directories in current directory?

Solution: du -ka sort -n tail -20

3. How do you display/remove Specifice Month files in Unix?

Solution: rm `ls -l grep Jun awk '{print $9}'`

4. How do you find the files which contains a specific Word?
Solution : find /home/ganesh \( -type f \) -exec grep -l test {} \;

Patching Issues

Patching Issues/Sollutions/WorkArounds

During Patching, If you get different AD Worker Errors:

1. AD Worker error:
The following ORACLE error:
occurred while executing the SQL statement:
GRANT select on GV$LOGFILE to em_oam_monitor_role
Error occurred in file
/appltop/apps/ad/11.5.0/patch/115/sql/ademusr.sql

Work Around:


->connect DB / as sysdba
->grant select on GV_$LOGFILE to system with grant option.
->connect system/systempwd.
->grant select on GV$LOGFILE to em_oam_monitor_role.
-> Restart the failed worker using adctrl.

2.AD Worker error:
The following ORACLE error:ORA-12801: error signaled in parallel query server P000ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundoccurred while executing the SQL statement:CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXXAD Worker error:Unable to compare or correct tables or indexes or keysbecause of the error above

Work Around

->Execute the following SQL to prevent errors during Patch Application through adpatch:
->SELECT TRANSACTION_ID, count(*)FROM ICX.ICX_TRANSACTIONSGROUP BY TRANSACTION_IDHAVING count(*)>1
->If the Above query returns any Row then Please execute the following SQL :

->$ICX_TOP/sql (named ICXDLTMP.sql).

Apps DBA - Tracing

Oracle Apps DBA - Enable Trace Application level

1. How do you enable/disable a trace to a Oracle Application Forms Session?

solution:

1. Connection to Oracle Applications
2. Navigate to the particular form, which you want trace to be enabled
3. Goto Menu Help->Diagnostics->Trace->Regular Trace and select it
4. It will ask you for Apps Password. Provide it
5. Then it will show the file path where trace is going to be generated
6. Ask developer to perform their transactions, once they are done disable the trace
7. Goto to that location to get the trace file
8. Get the trace out put file using tkprof with different options

To disable Trace Session

Goto Menu Help-> Diagnostics->Trace->No Trace

2. How do you enable/disable a trace to a Oracle Application Forms Session? (Other Way)

Solution:

1. Get the serial #, sid of particular form session by navigating Help->about Oracle applicatins
2. Connect to database using sqlplus with relavant user
3. execute dbms_system.set_sql_trace_in_session(2122,332,TRUE);
4. Select spid from v$process where addr=(select paddr from v$session where sid=2122);
5. You will get spid like 4515 for above statement
6. Goto udump location and type ls -ltr *4515* you will get trace file

To disable Trace Session

1. execute dbms_system.set_sql_trace_in_session(2122,332,FALSE);

3. How do you enable/disable a trace to a Concurrent Program?

Solution:

1. Connect to Oracle Applications
2. Navigate to System Administrator->Concurrent->Program->Define
3. Query the concurrent program on which you want to enable trace.
4. Check the enable trace check box bottom of the screen, Save it.
5. Ask the developer to submit the request, Once the request got submitted and completed normal.
6. Get the spid as select oracle_process_id from apps.fnd_concurrent_requests where request_id=456624.
7. You will get a spid like 12340.
8. Goto Udump and ls -ltr *12344*.
9 . You will get trace file.

Oracle Apps DBA - Concurrent Managers

Oracle Apps DBA - Concurrent Managers




1. How do you start/stop/check Concurrent Managers?


Solution:


cd $COMMON_TOP/admin/scripts/context_name/


-> adcmctl.sh start apps/appspwd

-> adcmctl.sh stop apps/appspwd

-> adcmctl.sh status apps/appspwd

-> ps -ef grep FNDLIBR grep applmgr


2. How do you create custom concurrent manager?


Solution:


1. Login to System Administrator Responsibility

2. Navigate to Concurrent > Manager > Define

Manager Field: Custom Manager-

Short Name: CUSTOMCM-

Type: Concurrent Manager-

Program Library: FNDLIBR-

Enter desired Cache-

Work Shifts: Standard-

Enter number of Processes-

Provide Specialization Rules- Save

3. Navigate to Concurrent > Manager > Administer- Activate the Custom Manager


3. How to Start the Concurrent Manager from the Operating system?


solution:

startmgr [parameters]


Example: startmgr sysmgr="applsys/fnd" mgrname="std" printer="hqseq1"mailto="jsmith" restart="N" logfile="mgrlog" sleep="90" pmon="5" quesiz="10"


Parameters:

[sysmgr="fnd_usernamd/fnd_password"] [mgrname="mgrname"]

[printer=printer]

[mailto="userid1 userid2...]

[restart="Nminutes"]

[logfile="log_file_name"]

[sleep="new_check"]

[pmon="manager_check"]

[quesiz="number_check"]

[diag="YN"]


4. EachConcurrent Request Phase and Status Meaning?


Solution:


Phase Status Description


PENDING Normal Request is waiting for the next available manager.

PENDING Standby Program to run request is incompatible with other program(s) currently running.

PENDING Scheduled Request is scheduled to start at a future time or date.

PENDING Waiting A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete.

RUNNING Normal Request is running normally.

RUNNING Paused Parent request pauses for all its child requests to finish running. For example, a request set pauses for all requests in the set to complete.

RUNNING Resuming All requests submitted by the same parent request have completed running. The Parent request resumes running.

RUNNING Terminating Request is terminated by choosing the Cancel Request button in Requests window.

COMPLETED Normal Request completed successfully.

COMPLETED Error Request failed to complete successfully.

COMPLETED Warning Request completed with warnings. For example, a request is generated successfully but fails to print.

COMPLETED Cancelled Pending or Inactive request is cancelled by choosing the Cancel Request button in the Requests window.

COMPLETED Terminated Request is terminated by choosing the Cancel Request button in the Requests window.

INACTIVE Disabled Program to run request is not enabled. Contact your system administrator.

INACTIVE On Hold Pending request is placed on hold by choosing the Hold Request button in the Requests window.

INACTIVE No Manager No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.

Oracle DBA -Performance Tuning Questions

Oracle DBA - Performance Tuning Interview Questions




1. What is Performance Tuning?

Ans: Making optimal use of system using existing resources called performace tuning.

2. Types of Tunings?

Ans: 1. CPU Tuning 2. Memory Tuning 3. IO Tuning 4. Application Tuning 5. Databse Tuning

3. What Mailny Database Tuning contains?

Ans: 1. Hit Ratios 2. Wait Events

3. What is an optimizer?

Ans: Optimizer is a mechanizm which will make the execution plan of an sql statement

4. Types of Optimizers?

Ans: 1. RBO(Rule Based Optimizer) 2. CBO(Cost Based Optimzer)

5. Which init parameter is used to make use of Optimizer?

Ans: optimizer_mode= rule----RBO cost---CBO choose--------First CBO otherwiser RBO

6. Which optimizer is the best one?

Ans: CBO

7. What are the pre requsited to make use of Optimizer?

Ans: 1. Set the optimizer mode 2. Collect the statistics of an object

8. How do you collect statistics of a table?

Ans: analyze table emp compute statistics or analyze table emp estimate statistics

9. What is the diff between compute and estimate?

Ans: If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read

10. What wll happen if you set the optimizer_mode=choose?Ans: If the statistics of an object is available then CBO used. if not RBO will be used

11. Data Dictionay follows which optimzer mode?

Ans: RBO

12. How do you delete statistics of an object?

Ans: analyze table emp delete statistics

13. How do you collect statistics of a user/schema?

Ans: exec dbms_stats.gather_schema_stats(scott)

14. How do you see the statistics of a table?

Ans: select num_rows,blocks,empty_blocks from dba_tables where tab_name='emp'

15. What are chained rows?

Ans: These are rows, it spans in multiple blocks

16. How do you collect statistics of a user in Oracle Apps?

Ans: fnd_stats package

17. How do you create a execution plan and how do you see?Ans: 1. @?/rdbms/admin/utlxplan.sql --------- it creates a plan_table 2. explain set statement_id='1' for select * from emp; 3. @?/rdbms/admin/utlxpls.sql -------------it display the plan

18. How do you know what sql is currently being used by the session?

Ans: by goind v$sql and v$sql_area

19. What is a execution plan?

Ans: Its a road map how sql is being executed by oracle db?

20. How do you get the index of a table and on which column the index is?

Ans: dba_indexes and dba_ind_columns

21. Which init paramter you have to set to by pass parsing?

Ans: cursor_sharing=force

22. How do you know which session is running long jobs?

Ans: by going v$session_longops

23. How do you flush the shared pool?

Ans: alter system flush shared_pool

24. How do you get the info about FTS?

Ans: using v$sysstat

25. How do you increase the db cache?

Ans: alter table emp cache

26. Where do you get the info of library cache?

Ans: v$librarycache

27. How do you get the information of specific session?

Ans: v$mystat

28. How do you see the trace files?

Ans: using tkprof --- usage: tkprof allllle.trc llkld.txt

29. Types of hits?

Ans: Buffer hit and library hit

30. Types of wait events?

Ans: cpu time and direct path read

Oracle DBA - Interview Questions

Oracle DBA - Interview Questions




1. How do you kill a session from the database?

Ans: alter system kill 'sid,serial#'

Usage : alter system kill '9,8' (Get the info from v$session

2. How do you know whether the process is Server Side Process?

Ans. By seeing the process in ps-ef as oracle+sid

eg: suppose the sid is prod, then the server process is refered as server side process and local=no

3. Daily Activities of a Oracle DBA?

Ans: 1. Check the Databse availability

2. Check the Listerner availability

3. check the alert log filie for errors

4. monitoring space availablilty in tablespaces

5. monitoring mount point (see capacity planning document)

6. Validate Database backup or Archive backup

7. Find objects which is going to reach max extents

8. Database Health check

9. CPU, Processor, Memory usage

4. Where do you get all hidden parameters ?

Ans: In the table x$ksppi

5. How do you see the names from that table?

Ans:select ksppinm,ksppdesc from x$ksppi where substr(ksppinm,1,1)='_'

6. How do increase the count of datafiles?

Ans: Generate the control file syntax from the existing control file and recreate the control file by changing the parameter MAXDATAFILES = yourdesired size

Procedure:

1. open the database

2. Generate the control file change the maxdatafiles

3. open the db in nomount

4. execute the syntax with noresetlogs

5. alter databse open

7. What is the init parameter to make use of profile?

Ans: resource_limits=true

8. How do you know whether the parameter is dynamic or static?

Ans: By going v$parameter and check the fields isses_modifiable and issys_modifible

9. What is the package and procedure name to conver dmt to lmt and vice versa?

Ans: exec dbms_space_admin.tablespace_migrate_from_local("gtb")

exec dbms_space_admin.tablespace_migrate_to_local("gtb1")

10. What is the use of nohup?

Ans: The execution of a specific task is performed in the server side with out any interupting

Usage : nohup cp -r * /tmp/. &

11. Where alert log is stored? What is the parameter?

Ans: in bdump. parameter is background_dump_dest

11. Where trace file are stored? What is the parameter?

Ans: in udump. parameter is user_dump_dest

12. Common Oracle Errors ……

1) ORA-01555 : Snapshot Too Old

2) ORA-01109 : Database Not Mounted

3) ORA-01507 : Database Not Open

4) ORA-01801 : Database already In startup mode.

5) ORA-600 : Internal error code for oracle program

Usage : oerr ORA 600

13. How do you enable traceing while you are in the database?

Ans : alter session set sql_trace=true;

14. If you want to enable tracing in remote system? what will u do?

Ans: exec dbms_system.SET_SQL_TRACE_IN_SESSION(9,3,TRUE);

15. Which role you grant to rman user while configuring rman user

Ans: recover_catalog_owner

16. Where do you get to know the version of your oracle software and what is your version?

Ans: from v$version(field is banner) and the version is 9.2.0.1.0

17. What is the parameters to set in the init.ora if you create db using OMF(Oracle Managed Files)?

Ans: db_create_file_dest=

db_create_online_log_dest_1=

18. What is a runaway session?

Ans: you killed a session in the database but it still remains in the os level and vice versa. Its called runaway session. If runaway session is there cpu consumes more usage.

19. How do you know whether the specific tablespace is in begin backup mode?

Ans: select status from v$backup. if it is active it means it is in begin backup mode

20. If you want to maintain one more archive destination which parameter you have to set ?

Ans:Its a dynamic parameter you have to set log_archive_duplex_dest=

Usage : alter system set log_archive_duplex_dest=

21. How do you know the create syntax of your function/procedure/index/synonym ?

Ans: using function dbms_metadata.get_ddl(object type,object name,owner)

22. What is the password you have to set in the init.ora to enable remote login ?

Ans: remote_login_password_file=exclusive

23.How do u set crontab to delete 5 days old trace files at daily 10'0 clock?

Ans: crontab -e

0 10 * * * /usr/bin/find /u001/admin/udump -name "*.trc" -mtime +5 -exec rm -rf {} \;
save and exit (wq!)

24.How do u know when system was last booted?

Ans: 3 ways 1.uptime cmd

2.top

3.who -b

4.w

24.How do u know load on system?

Ans: 1.w

2.top

25. How do you know when the process is started

Ans : Using ps -ef grep process name

26. Tell me the location of Unix/Solaris log messages stored?

Ans:/var/log/messages(Unix)

/var/adm/messages (solaris)

27.How do you take backup of a controlfile?

Ans: alter database backup controlfille to destination (Database should be open)
file will be save in the your destination

28. What is the parameter to set the user trace enabiling?

Ans: sql_trace = true

29. How do you know whether archive log mode is enable or not?

Ans: issue command 'archive log list' at sqlplus prompt

30. Where do you get the information of quotas?

Ans: dba_ts_quotas view

31. How do you know how much archives are generated ?

Ans: using the view v$log_history

32. What is a stale?

Ans: The redolog file which has not been used yet

33. How do you read the binary file?

Ans: using strings -a

usage : strings -a filename

34.How do you read control file?

Ans: using command tkprof

Usage: tkprof contrl.ctl ctrol2.txt

35. How do you send the data to tape?

Ans: using 1. tar -cvf or 2. cpio

36. How do you connect to db and startup and shutdown the db without having dba group?

Ans: using remote_login_passwordfile

37. When will you take Cold back up especially?

Ans: during upgradation and migration

38. How do you enable/disable debugging mode in unix?

Ans: set -x and set +x

39. How do you get the create syntax of a table or index or function or procedure?

Ans: select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

40. How do you replace a string in vi editor?

Ans: %s/name/newname

41. When ckpt occurs?

Ans: 1. for every 3 seconds

2. when 1/3rd of DB buffer fills

3. when log swtich occurs

4. when databse shuts down

42. In which file oracle inventory information is available?

Ans : /etc/oraInst.loc

42. Where all oracle homes and oracle sid information available?

Ans: /etc/oratab

43. What is the environment variable to set the location of the listener.ora

Ans: TNS_ADMIN

44. How do you know whether listener is running or not

Ans: ps -ef grep tns

45. What are the two steps involved in instace recovery?

Ans: 1. Roll forward (redofiles data to datafiles)

2. Roll backward (undo files to datafiles).

46. If you delete the alert log fle what will happen?

Ans: New alert log will be created automatically

57. How do you create a table in another tablespace name?

Ans: create table xyz (a number) tablespace system

58. What are the modes/options in incomplete recovery?

Ans: cancel based, change based, time based

59. How do you create an alias?

Ans: alias bdump='cd $ORACLE_HOME/rdbms/admin'

60. Types of trace files?

Ans: 1. trace files generated by database (bdump)

2. trace files generated by user(udump)

61. How do you find the files whose are more than 500k?

Ans : fnd . -name "*" -size +500k

62. Where do you configure your hostname in linux?

Ans: vi /etc/hosts

63. What are the types of segments?

Ans: Data Segment,Undo Segment,Temporary segment,Index Segment

64. What is a synonym and different types of synonyms?

Ans: A synonym is an alias for a table,view, sequence or program unit.

1. Public synonym

2. private synonym

65. What are mandatory background processes in Oracle Database?

Ans: smon,pmon,ckpt,dbwr,lgwr

66. How do you make your redolog group inactive?

Ans: alter system switch logfile;

67. How do you drop a tablespace?

Ans: drop tablespace ts1 including contents and datafiles;

68. What is the parameter allows you to create max no. of groups?

Ans: maxlogfiles=

69. What is the paramter allows to create max no. of members in a group?

Ans: maxlognumbers=

70. What Controlfile contains?

Ans: Database name

Database creation time stamp

Address of datafiles and redolog files

Check point information

SCN information

71. What are the init parameters you have to set to make use of undo management?

Ans: undo_tablespace= undotbs1

undo_management= auto

undo_retention= time in minutes

comment rollback_segment

73. How do you enable monitoring of a table?

Ans: alter table tablename monitoring

74. How do you disable monitoring of a table?

Ans: alter table tablename no monitoring

75. How do you check the status of the table whether monitoring or not?

Ans: select table_name, monitoring from dba_table where owner='scott;

76. In which table records of monitoirg will be stored?

Ans: dba_tab_modificaitons

77. How do you get you demo files get created in your user?

Ans: ?/sqlplus/demo/demobld.sql execute this script on which user you want

78. What is netstat? and its Usage?

Ans: it is a utility to know the port numbers availability. Usage: netstat -na grep port number

79.How do you make use of stats pack?

Ans: 1. spcreate.sql (?/rdbms/admin/) -- to create statspack

2. execute statspack.snap -- to collect datbase snaps

3. spreport.sql-- to generate reports(in your current directory)

4. spauto.sql----to schedule a job

5. sppurge.sql---to delete statistics

6. spdrop.sql----to drop the statistics

Oracle Apps DBA - Complete Patching

1. How do you Apply a application patch?



-> Using adpatch


2. Complete Usage of adpatch?



1. download the patch in three ways.


a) Using OAM-Open Internet Exploreer->Select Oracle Application Manager-> Navigate to Patch Wizard -> Select Download Patches -> Give the patch number(more than one patch give patch numbers separated by comma-> Select option download only->Select langauge and Platform-> Give date and time-> submit ok
Note: Before doing this Your oracle apps should be configured with metalink credentials and proxy settings


b) If your unix system is configured with metalink then goto your applmgr account and issue following command


1.ftp updates.oracle.com

2.Give metalink username and password

3.After connecting, cd patch number

4. ls -ltr

5. get patchnumber.zip(select compatiable to OS)

c) Third way is connect to metalink.oracle.com.


1. After logging into metalink with your username and password

2. Goto Quickfind->Select patch numer-> Give patch number->Patch will be displayed->Select os type->Select download

3. ftp this patch to your unix environment


2. Apply the patch?



1. unzip downloaded patch using unzip

eg: unzip p6241811_11i_GENERIC.zip

2. Patch directory will be unzip with patch number.

3. Goto that directory read readme.txt completely.

4. Make sure that Middle tier should be down, Oracle apps is in maintainance mode and database and listener is UP

5. Note down invalid objects count before patching

6. Goto patch Directory and type adpatch

7. It will ask you some inputs from you like, is this your appl_top,common_top, logfile name,sytem pwd, apps pwd, patch directory location, u driver name etc. Provide everything


2. During Patch What needs to be done?



1. Goto $APPL_TOP/admin/SID/log

2. tail -f patchnumber.log(Monitor this file in another session)

3. tail -f patchnumber.lgi(Monitor this file in another session)

4. TOP comand in another session for CPU Usage


3. Adcontroller during patching?



1. During patching if worker fails, restart failed worker using adctrl(You wil find the option when u enter into adctrl)

2. If again worker fails, Goto $APPL_TOP/admin/SID/log/workernumber.log

3. Check for the error, fix it restart the worker using adctrl

4. If you the issue was not fixed, If oracle recommends if it can e ignorable, skip the worker using adctrl with hidden option 8 and give the worker number


4. Log files during patching?



1. patchnumber.log ($APPL_TOP/admin/SID/LOG/patchnumber.log)

2. patchnumber.lgi($APPL_TOP/admin/SID/LOG/patchnumber.lgi)

3. adworker.log($APPL_TOP/admin/SID/LOG/adworker001.log)

4. l.req($APPL_TOP/admin/SID/LOG/l1248097.req)

5. adrelink.log($APPL_TOP/admin/SID/LOG/adrelink.log)

6. adrelink.lsv($APPL_TOP/admin/SID/LOG/adrelink.lsv)

7.autoconfig.log($APPL_TOP/admin/SID/LOG/autoconfig_3307.log)


5. useful tables for patching?



1. ad_applied_patches->T know patches applied

2. ad_bugs->ugs info

3. fnd_installed_processes

4. ad_deferred_jobs

5. fnd_product_installations(patch level)


6. To know patch Info?



1. You can know whether particular patch is applied or not using ad_applied_patches or ad_bugs


2. Using OAM->Patch wizard-> Give patch numer


3. To know mini pack patchest level, family pack patchest level and patch numbers by executing script called patchsets.sh(It has to be downloaded from metalink


7. Reduce patch time?



1. using defaults file

2. Different adpatch options you can get these options by typing adpatch help=y(noautoconfig,nocompiledb,hotpatch,novalidate,nocompilejsp,nocopyportion,nodatabaseportion,nogenerateportion etc)

3. By merging patches into single file

4. Distributed AD if your appl_top is shared

5. Staged APPL_TOP while in production env


8. Usage of Admerging?



1. You can merge number of patches into single patch

2. create two directories like eg: merge_source and merged_dest

3. Copy all patches directories to merge_source

4. admrgpch -s merge_source -d merged_dest -logfile logfile.log

5. merged patch will be generated into merged_dest directory and driver name wil be u_merged.drv


8. Usage of Adsplice?



1. Download splice patch, and unzip it

2. Read the readme.txt perfect

3. As per read me, copy following three files to $APPL_TOP/admin

izuprod.txt

izuterr.txt

newprods.txt

4. open newprods.txt using vi and modify the file by giving correct tablespace names available in your environment

5. run adsplice in appl_top/admin directory

Oracle Apps DBA - Cloning

Oracle Apps DBA - Simple Clonig Steps



How do you Clone your Oracle Apps System with Rapid Clone?



Let us say:

source is Prod and destination is Test


1. Make sure Test Server is ready

a. Remove existing apps systems and file systems in Test

b. That is look for all mount points sames as Prod

c. Look for the same file structures of the prod.

d. Make sure that same users should be there in test such as oracle and applmgr.

e. make sure that all the mount points and required file systems in test has required permissions.


2. Make Sure everything is ready in Prod.

a. Apply Rapid clone patch.

b. login oracle user, Goto $ORACLE_HOME/appsutil/clone/bin.

c. run perl ./adpreclone.pl dbTier.

d. login applmgr user, Goto $COMMON_TOP/clone/bin.

e. run perl ./adpreclone.pl appsTier.


3. Copy everything(Restore everthing) from Prod to test using some tool (Data protector) or manually, including database


4. After successfull restore from Prod backup/Prod source to Test, Go to Test system, rename all root directories and change ownership to respective users to test directories

Example : mv /y23/data/PRD /y23/data/TEST

mv /z24/data/PRD /z24/data/TEST

chown soracle:dba /y23/data/TEST

chown sapplmgr:dba /y23/data/TEST


5. Check correct oraInst.loc is there in your Test envirnoment


6. Statrt DBTier cloning program , go to soracle user, goto $ORACLE_HOME/appsutil/clone/bin. run the command
perl ./adcfgclone.pl dbTier


7. After running the script, it will ask inputs like
Target System Name, Target System Domain name, Target system Oracle Home, Number of Data Tops and its locations, Target System Display. Give all appropriate values


8. Then actual database cloning part starts. Mean while open other session of putty/telnet/x-manager. tail the logfile of cloning ex: tail -f $ORACLE_HOME/appsutil/clone/bin/CloneContext_032832939.log


9 . Open another session and user TOP command for CPU usage


10 .After completioin of script check log files for any errors


11. Important thing.. Update fnd_concurrent_requests.. Means cancel all existing requests. (change thes status of concurrent requests to completed)


12. Goto sapplmgr user, goto $COMMON_TOP/clone/bin execute the script as perl ./adcfgclone.pl appsTier


13. After executing script it will ask different inputs to enter such as apps password(prod password),Target System Database SID, Target Databse Server node, Target system domain name, Target systems APPL_TOP,COMMON_TOP, ORACLE_HOME, IAS_ORACLE_HOME, Target system Display, Locatino of JDK, UTL_FILE_DIR location. Give all appropriate values. Then scripst starts actual cloning process


14. Open another session of putty/telnet/X-Managers. Tail the logfile as tail -f $COMMON_TOP/clone/bin/CloneContext_06666724.log


15. Open another session of putty/telnet/X-Manager. See the CPU usage using top command


16. Hope appscloning completed successfully. Stop all apps services going $COMMON_TOP/admin/scripts/ContextName/adstpall apps/appspwd


17. Remove temporary files located in $COMMON_TOP/temp/*.t and *.tmp


18. If you have any customizations update init.ora as per your needs. You better to get initprod.ora from the PROD and change it as per your needs


19. Goto database drop production database links and create the database links needed in the test env


20. change the passwords of some users which can be used in test environment


21. Goto $APPL_TOP. update APPSORA.env with test database sid, test server name bothe in small letters and capital letters


22. Update profile options ICX Session Time out for site value


23. Change apps password in wdbsvr.app file located in $IAS_ORACLE/Apache/modplsql/cfg


24. Update schema passwords


25. delete all production logfiles came while refresh

Oracle DBA - 9.2.0.8 Database Upgradation from 9.2.0.5

How do you know whether you Current Oracle Database is 64-Bit or 34-Bit?

solution: cd $ORACLE_HOME\binfile oracle or file ora*

How do you know your HP Unix Version?

Solution: uname -a

Procedure to upgrade database
:
1. Download upgrade patch for your OS and DB compatible. For Example if your current database is 64-bit and your OS is HP unix.Then download 4547809Hp1164bit.zip from Metalink download

2. unzip the patch file. Disk1 directory will get extracted.

3. set display of your unix environment to enable GUI.

4. Open X-Manager or Reflection X, and note down port number and frame number

5. export DISPALY=192.168.0.1:1.0

6. Test GUI by typing xclock.

7. Shut down database and listener.

8. Goto Disk1 directory you will find, runinstaller executable.

9. Invoke OUI(Oracle Universal Installer), by executing ./runInstaller

10. Choose Oracle Home/Name and click next

11. In the middle of the installation, it will ask you to execute root.sh by root user. Do the same

12. After installation completes, close the OUI

13. start the database in migration mode i.e startup migrate

14. Execute catpatch.sql located in ORACLE_HOME/rdmbs/admin. i.e in sql prompt: sql> ?/rdbms/admin/catpatch.sql

15. It will take some time, after that execute utlrp.sql to compile invalid objects i .e sql>?/rdbms/admin/utlrp.sql

16. Shutdown the database and startup in normal mode. i.e shut immediate and startup

17. Start the listener and release the instance