Sandeep Panchal Blog

Table of Wait Events and Potential Causes:The V$SESSION, V$SESSION_WAIT, V$SESSION_EVENT, and V$SYSTEM_EVENT views provide information on what resources were waited for, and, if the configuration parameter TIMED_STATISTICS is set to true, how long each resource was waited for.

Oracle Perfornamce Tuning approach. Its a very vast topic, i am trying to cover very high level of it. 1. In order to tune your Oracle database, First you need to know what to tune. The first step is to define the scope of tuning. 2. After obtaining a full set of operating system, database, and application statistics, examine the data for any evidence of performance problems. This will help you in understanding of problem area and you will be able to set the scope of tuning. 3. What is affected by the slowdown? For example, is the whole instance slow? Is it a particular application, program, specific operation, or a single user? 4. My approach is to first Examine the Host System. (UNIX : here) Use: iostat (I/o examine), sar ,prstat, vmstat ,netstat ,top , and examine OS error log (e.g /var/adm/messages in (Solaris) ) make sure that you have enough CPU available for oracle processes. Example of cpu monitor Basic global program and thread-level summary tprof -x s...

My Certification From Oracle University are : Oracle Certified Master 11g , OCP 11g, OCP10g,OCM9i,OCP8i ,ITIL V3 Certified Oracle Database Administrator with over 12+years of IT experience in Oracle Apps Projects. Technical strengths include over 12 +years experience in the area of Oracle database and Oracle Applications administration Committed team player with excellent customer orientation. Excellent written and verbal communication skills. Engineering and Architecting all activities like, Oracle Database and Oracle Applications installation, upgrade, patching and security on Oracle Database and Oracle Applications Specialties - Oracle Database and Oracle Applications Administrator --Primary - Program / Project Management - Solution Architect drafting ERP roadmaps - Operations Management - Oracle Applications and Databases Management - High Availability Solutions for Enterprise Databases - Oracle Application Server ( Discoverer, Identity Management, Oracle Portal) ...

Logout R12 Oracle applications: • Ensure the users are trained to use the Logout or Home global buttons when completing their transactions. • Do not train them to use the browser close (“x”) link. • Logging out gracefully releases the memory and corresponding resources (i.e. connections, etc..) • Releases process resources sooner

Transparent Data Encryption (TDE) is NOT about access control. This is to protect the data access from OS files. If someone has access to OS datafiles, with some utilities DBFs data can be read in text format. Transparent Data Encryption (TDE) : in 10g  :We need to configure encryption on a column-by-column basis. in 11g: We can do Tablespace encryption  instead of column-by-column. Encryption algorithm can have one of the following values: 3DES168, AES128,AES192,AES256 The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm is used. The default encryption algorithm is AES128. 1)We cannot use Tablespace encryption  for SYSTEM, SYSAUX, UNDO and TEMP tablespaces 2)We need to create new tablespace for encrption as Existing tablespace cannot be encrypted 3)An issue with any encryption method is the negative impact on performance. 4)SELECT  encrypted FROM dba_tablesp...

in 11g database, You can shrink the temporary tablespace by command alter tablespace temp shrink space; Earlier, large sort operations by Oracle database were causing the temporary table space to grow to a higher value and space once allocated was not being released. in 11g we can shrink the temporary tablespace. Also, we can limit the size of temporary tablespace by KEEP command. ALTER TABLESPACE temp SHRINK SPACE KEEP 200M; OR shrink the tempfile. ALTER TABLESPACE temp SHRINK TEMPFILE '/sandeep/dbfs/temp.dbf';

11g new feature : index rebuild online. In Oracle Database 11g, the online rebuild is truly online: it does not hold an exclusive lock. The DMLs are not affected.  It does so by tracking which blocks are being accessed and at the end merging these blocks with the newly built index.

We can now make a table read only in database (11g new feature). ALTER TABLE ABC READ ONLY; If someone try any DML on ABC table he will get this error ORA-12081: update operation not allowed on table SCHEMA.ABC. To check if the table is read only , select read_only from user_tables where table_name='ABC'; ALTER TABLE ABC READ WRITE;  --> this will change the readonly mode of table and DML can be performed on this now.   No need to worry about who has select,insert,update,delete on table, If you want to make sure that data is read only, You can make the table readonly.   Enjoy...I am sure this is a great feature...and will help DBA/developers a lot to protect the data (Like refrence data)

Starting in Oracle 11g release 2, Oracle has renamed the flash recovery area to  the fast recovery area (also simply called the recovery area), which automates management of backup-related files. The Fast Recovery Area is a location in which Oracle Database can store and manage files related to backup and recovery. in 11g R1 , It is flash recovery area, But this is changed to fast recovery area in 11g r2. Default fast recovery area: $ORACLE_BASE/recovery_area db_recovery_file_dest -  flash_recovery_area - This is the location of the flash recovery area. This can be a directory, file system, or Automatic Storage Management (Oracle ASM) disk group. It cannot be a raw file system. db_recovery_file_dest_size - Specifies the maximum total bytes to be used by the Fast Recovery Area. In an Oracle RAC environment, the settings for these two parameters must be the same on all instances

While i was trying to check the alert log in 11g. I knew that the .log was replaced by xml files. I tried to open the xml file in xml editor and notepad i recieved the error ===================================== The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/tmp/log.xml'. Line 8, Posi... ======================================= on investigation i found that adcri is the tool which should be used to view logs in 11g.. "C:\Users\Sandeep>adrci" ADRCI: Release 11.1.0.7.0 - Production on Sun Feb 21 10:48:29 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. ADR base = "d:\11gsoftware" adrci> adrci> show alert Choose the alert log from the following homes to view: 1: diag\clients\user_system\host_3693351798_11 2: diag\rdbms\sandeep\sandeep 3: diag\tnslsnr\sandeep-pc\listener...

NOTE:171647.1 - Tracing Oracle Applications using Event 10046 NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)" NOTE:214106.1 - Using TKProf to compare actual and predicted row counts NOTE:225598.1 - How to Obtain Tracing of Optimizer Computations (EVENT 10053) NOTE:242374.1 - Tracing PX session with a 10046 event or sql_trace NOTE:258418.1 - Getting 10046 Trace for Export and Import NOTE:32951.1 - TKProf Interpretation (9i and below) NOTE:398838.1 - FAQ: Query Tuning Frequently Asked Questions NOTE:41634.1 - TKProf Basic Overview NOTE:75713.1 - Important Customer information about using Numeric Events NOTE:760786.1 - TKProf Interpretation (9i and above) Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1] Best is FAQ: Query Tuning Frequently Asked Questions [ID 398838.1] Enjoy..

A nice collection of links to learn 11g new features..... Eddie Awad’s Blog http://awads.net/wp/2007/08/20/40-links-for-knowing-the-new-oracle-database-11g/

Oracle Application Express (Oracle APEX) is a web application developemnt tool for Oracle database. All you need to do is to have a web browser . 1. You can download and install it (If you want to host your application). 2. If you don't have access to a local instance, request a Workspace on Oracle's hosted instance, apex.oracle.com and ....Its Free. 3. http://tryapexnow.com/ : You can review Oracle application Express by participating in the Early Adopter release. 4. I was surprised to know that This is the application development environment used to build the AskTom site itself. 5. No client software is required to develop, deploy, or run Application Express applications. Application Express provides three primary tools: Application Builder - to create dynamic database driven web applications SQL Workshop - to browse your database objects, run ad-hoc SQL queries, as well as a graphical query builder Utilities - allows for data to be loaded and unloaded from both...

Previous releases of Oracle Applications only used a text file, config.txt, to store the supplied configuration values. In Release 12, the name of this configuration file has changed, and it now includes the database SID, to give a file name of conf_ .txt (for example, conf_PROD.txt). This file stores the information collected by Rapid Install for all database and Applications nodes. Rapid Install stores copies of the conf_ .txt file in three separate locations: • Database 10g R2 /appsutil: This copy is used on database nodes, on Applications nodes in multi-node installs, and in upgrades. It is permanently stored and not deleted. • $INST_TOP: This copy is used on Applications nodes in multi-node installs, and in upgrades. It is permanently stored and not deleted. • /tmp/ : This copy is used by Rapid Install during the installation run. It is deleted when the installation is completed

Part 1: Basics: This post will guide you about the patching  for oracle Applications. 1. Patch File Structure Patches generally consist of a top-level directory that may contain several files, and one or more subdirectories. The top-level directory is named , where is the number of the patch. The most important files in the top-level directory are: README.txt, README.html and the unified driver file (named u.drv). 2.  Patch Formats 3. Adpatch will do the following Use AutoPatch to apply patches to the Oracle Applications file system or database. It gathers necessary information about your system through a series of prompts. When you have completed the prompts, AutoPatch performs all the tasks required to apply the patch, including the following: ■ Reads patch metadata to determine patch dependencies and requirements. ■ Uploads patch information from a prior patch session to the database (if needed). ■ Reads and validates the patch driver file and reads the p...

I was trying to find out the last reboot time for windows...I knew it for UNIX (by uptime).. for windows..systeminfo C:\Users\Sandeep>systeminfo Host Name: MY-PC OS Name: Microsoft® Windows VistaT Home Basic OS Version: 6.0.6002 Service Pack 2 Build 6002 OS Manufacturer: Microsoft Corporation OS Configuration: Standalone Workstation OS Build Type: Multiprocessor Free Registered Owner: Sandeep Registered Organization: Product ID: xxxxxxxxxxxxxxxxxxxxxx Original Install Date: 28/09/2008, 01:43:54 System Boot Time: 13/02/2010, 03:16:23 System Manufacturer: Acer System Model: Aspire 5535 System Type: X86-based PC Processor(s): 1 Processor(s) Installed. [01]: x64 Family 17 Model 3 Stepping 1 AuthenticAMD ~1000 Mhz I remember that in some service pack of windows uptime is also there.

a. cursor movements (items below are sometimes called objects): h - left one character l - right one character j - down one line k - up one line w - right one word b - back one word $ - to the end of line 0 - to the beginning of the line ) - right one sentence ( - left one sentence } - right one paragraph { - left one paragraph Ctrl-F - forward one page Ctrl-B - back one page G - go to (without arguments, go to end of file) b. deleting: d - delete then add one of the cursor movement symbols to show what should be deleted, i.e.: d$ - delete to end of line d0 - delete to the beginning of the line d} - delete to the end of paragraph dd - delete delete (delete the whole line) x - delete character cursor is on c. other basic commands: r - replace one character ZZ - save and exit (hold down shift and press "z" twice) y - yank (copy into temporary buffer) then add cursor movement symbol to show what should be copied, for example: y) - copy to the end ...

How to find a jar file which contains a particular JAVA CLASS object 1) Find invalid java classes: Lets assume that we have two JAVA CLASS invalids as returned by the following query: Col object_name for a50 Col owner for a10 SQL>SELECT object_name,object_type,owner,status FROM dba_objects WHERE object_type='JAVA CLASS' AND status='INVALID'; OBJECT_NAME OBJECT_TYPE OWNER STATUS /b0993e19_MyAppsContext JAVA CLASS APPS INVALID oracle/apps/fnd/common/FileLog JAVA CLASS APPS INVALID 2) Try to resolve invalids: First of all try to resolve these invalids using following statements. SQL>ALTER JAVA CLASS "/b0993e19_MyAppsContext" RESOLVE; SQL> ALTER JAVA CLASS "oracle/apps/fnd/common/FileLog" RESOLVE; Note: If you don't include java class name in double quotes the statements will fail. If these objects are still invalid proceed to next step 3) Find respective modules of java classes: We need to check as to which module th...

While i was working on some problems related to data transfer from Excel to Oracle, I found a very interesting tool on internet. http://www.excel-db.net/index.htm Excel-DB is the oldest and most respected software tool for database enabling your spreadsheets. Following features are available.....I found this very interesting and nice tool. Excel-DB for Oracle Excel-DB and Oracle Transferring Excel data to Oracle Excel-DB and Oracle BI Excel-DB Oracle BI Software Excel-DB vs. traditional Oracle BI tools Oracle Downloading into Excel made easy Using Excel-DB with ODBC Creating spreadsheet data form Oracle Upload spreadsheet data into Oracle Use Excel-DB to save spreadsheet data into Oracle Converting Oracle Data into Excel spreadsheets Oracle data extraction to Excel Oracle data migration to Excel Oracle data transfer Excel Oracle OLAP software Oracle OLAP tool Oracle Excel interface Oracle Excel spreadsheet interface Spreadsheet for Oracle Excel database data O...

Oracle9i Application Server (short name: 9iAS) refers to all application server (middle-tier) products up to 9iAS 9.0.3, including: Oracle9i Application Server Release 1 Version 1.0.2.2.2 Oracle9i Application Server Release 2 Version 9.0.2 Oracle9i Application Server Release 2 Version 9.0.3 Oracle Application Server 10g (short name: OracleAS 10g) refers to all subsequent application server (middle-tier) releases, including: Oracle Application Server 10g Release 1 Version 9.0.4 Oracle Application Server 10g Release 1 Version 9.0.4.1 Oracle Application Server 10g Release 2 Version 10.1.2.0.0 Oracle Application Server 10g Release 2 Version 10.1.2.0.1 Oracle Application Server 10g Release 2 Version 10.1.2.0.2 Oracle Application Server 10g Release 2 Version 10.1.3

Voting disk –Manages cluster membership and arbitrates cluster ownership between the nodes in case of network failures. The voting disk is a file that resides on shared storage. For high availability, Oracle recommends that you have more than one voting disk, and that you have an odd number of voting disks. If you define a single voting disk, then use mirroring at the file system level for redundancy. Oracle Cluster Registry (OCR)– Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases. The OCR also stores information about processes that Oracle Clusterware controls. The OCR resides on shared storage that is accessible by all the nodes in your cluster. Oracle Clusterware can multiplex, or maintain multiple copies of, the OCR and Oracle recommends that you use this feature to ensure high ava...

The following is a description of the tools used for installing, configuring, and managing the Oracle Clusterware, Oracle Automatic Storage Management (Oracle ASM), and Oracle Real Application Clusters (Oracle RAC): Oracle Universal Installer (OUI)—OUI installs the Oracle grid infrastructure software (which consists of Oracle Clusterware and Oracle ASM) and the Oracle Database software with Oracle RAC. After you configure the nodes that you want to use in your cluster, OUI installs the Oracle software on the specified nodes using a network connection. Cluster Verification Utility (CVU)— CVU is a command-line tool that you can use to verify a range of cluster and Oracle RAC components such as shared storage devices, networking configurations, system requirements, and Oracle Clusterware, as well as operating system groups and users. You can use CVU for preinstallation as well as postinstallation checks of your cluster environment. CVU is especially useful during preinstallation a...

1)Running Autoconfig in parallel on multiple application nodes in Release 12 is supported right now.  2)You can run restore.sh (UNIX) or restore.cmd (Windows) to roll back an AutoConfig session.

Very nicely explained. All about  Oracle applications Document Deliverables http://ebusinessuite.wordpress.com/2007/07/05/aim-document-deliverables/ You can download oracle AIM from http://download-east.oracle.com/partners/265498.EXE

Logical Database Limits Rows Maximum number per table Unlimited Indexes Maximum per table Unlimited total size of indexed column 75% of the database block size minus some overhead Columns Per table 1000 columns maximum Per index (or clustered index) 32 columns maximum Per bitmapped index 30 columns maximum Constraints Maximum per column Unlimited Subqueries Maximum levels of subqueries in a SQL statement Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause Partitions Maximum length of linear partitioning key 4 KB - overhead Maximum number of columns in partition key 16 columns Maximum number of partitions allowed per table or index 1024K - 1 Stored Packages Maximum size PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. Trigger Cascade Limit Maximum value Operating system-dependent, typically 32 Users and Roles Maximum 2,147,483,638 Tables...

Metalink note [ID 419475.1] descibes the process of Removing Credentials from Cloned EBS Production Database ..A very decent process