Configuring Transparent Application Failover

Transparent Application Failover (TAF) is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side.

RAC on Windows: Oracle Clusterware Node Evictions a.k.a. Why do we get a Blue Screen (BSOD) Caused By Orafencedrv.sys? [ID 337784.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Microsoft Windows Itanium (64-bit)
Microsoft Windows x64 (64-bit)
Microsoft Windows 2000Microsoft Windows XPMicrosoft Windows Server 2003 (64-bit Itanium)Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 R2 (32-bit)
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7

Symptoms

While running RAC a blue screen is shown and a reboot takes place. Windows creates a coredump that shows that orafencedrv.sys is involved.

Configuring Ignite for Oracle Connections

For Ignite to connect to Oracle databases that are specified by a name (tnsnames or LDAP), the following Oracle Network configuration (.ora)  files are required
    tnsnames.ora (required for name TNSName resolution)
    ldap.ora (required for LDAP name resolution)
    sqlnet.ora (optional)

Oracle Data Guard Configuration (DGMGRL) (11gR2 Windows 2008R2)

Brief:

Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. DG broker does not have the ability to create standby and is used for managing the dataguard configuration.

Task:  Create  physical standby database for an existing primary database. Both primary and standby would be on same physical machine.

PRM = primary db 192.168.26.11

STBL = local Standby 192.168.26.11

How to Restrict User from Connecting to Database Through Specific IP Address

Some of the DBA asked me to restrict the connection to DB from specific IPs. Its simple and you can use the logon trigger for this purpose.

Enabling/Disable Archive Log mode in RAC (11gR2)

Whether a single instance or clustered database, Oracle tracks (logs) all changes to database blocks in online redolog files. In an Oracle RAC environment, each instance will have its own set of online redolog files known as a thread. Each Oracle instance will use its set (group) of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one.

cluvfy Healthcheck

Normally we run cluvfy in preparation of the installation of Grid Infrastructure or a set of RAC binaries to ensure everything is ready for the next step in the RAC install process. Beginning with 11.2.0.3, there is another option  "healthcheck" 

Part of the “comp” checks, it takes the following options:

Datapump import gets ORA-600 [kpudpxcs_ctxConvertStream_ref_1] while importing Spatial data

Working for one of our client , just got the below error in impdp log for some tables, one example below.

ORA-31693: Table data object "GDB"."STREETS" failed to load/unload and is being
skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00600: internal error code, arguments: [kpudpxcs_ctxConvertStream_ref_1],
 [SYS_TYPEID("SHAPE")], [], [], [], [], [], [], [], [], [], []

Send Message to Users by OS Command using PL/SQL

From one of my client ,it was required to send the message to application users (on Windows) for specific events by the database server. I used  the simple msg (Win command) for this purpose , example given below :

How To Connect database in sqlplus without tnsnames.ora

Many times we have requirement of connecting database to execute a sql script but we sometimes don't find the tnsnames.ora file and sometimes we don't have permission to edit (one of my client faced issue on Windows 2008 Server).

OPatch failure issue while patching RAC (11.2.0.3)

While patching the RAC/DB you may face different issues on Windows because of the .dll files being used. Here is the process how I got rid of such failure.
While performing the acfsroot install, it failed as below.

Applying Patch 6 on RAC 11gR2 (11.2.0.3)

Environment: 11gRAC/DB (11.2.0.3) , Windows Server 2008 R2 x64 Edition
Patch:13965211 - 11.2.0.3 Patch 6 for Windows 64 bit, download patch from metalink.
This patch is RAC rolling upgradable for GRID, and RDBMS homes.
You should administer one Oracle product, one Oracle home at a time, for its particular Bundle Patch Update.

CRS-4535: Cannot communicate with Cluster Ready Services

We have SCOM configured for Oracle Servers to know if some service crashes, today I got the following alert for one of our RAC node.
The OracleASMService+ASM1 service terminated unexpectedly.  
I started investigations and tried to get the status by crsctl as below 

SP2-1503: Unable to initialize Oracle call interface

Oracle Error :: SP2-1503

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Cause

Indicates a library used by SQL*Plus to communicate with the database failed to initialize correctly.

ORA-01002: fetch out of sequence

"ORA-01002: fetch out of sequence" usually means that a SQL fetch has been issued after a prior commit has closed a server side cursor.

The current default behaviour of the Oracle JDBC driver is to autocommit SQL Statements. So it is possible that a commit has been issued which you did not expect. Eg if you are performing update statements inside the fetch loop, it is possible that an automatic commit has been issued after the update which has closed the fetch loop's cursor.

Enable block change tracking in oracle 11g

The block change tracking (BCT) feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a block change tracking file. This file is a small binary file called block change tracking (BCT) file stored in the database area. RMAN tracks changed blocks as redo is generated.

RMAN backup fails with Ora-00245 And Rman-08132

Symptoms

RMAN backups report errors like :

ORA-00245: control file backup operation failed

RMAN-08132: WARNING: cannot update recovery area reclaimable file list

Cause

Incorrect specification for the Snapshot Controlfile

ORA-19815: WARNING: DB_RECOVERY_FILE_DEST_SIZE 100.00% USED

Symptoms

ALERT LOG
-----------
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
ARC0: Error 19809 Creating archive log file to '+DBFLASH'

How To Create a Connection With SQL Server In SQL*DEVELOPER

Ensure that the source database is accessible by the Microsoft SQL Server user and the system from where you are connecting that is used by SQL Developer for the source connection.You can test by creating the ODBC datasource to the SQLServer DB.

Access Control List for External Network Services (11g)

Starting with Oracle 11gR1 (11.1.0.6) so called "fine-grained access" was implemented to limit usage of packages like UTL_SMTP, UTL_HTTP connecting over the network to other services like mail server etc.

Log Files in RAC Environment

The Cluster Ready Services Daemon (crsd) Log Files

Log files for the CRSD process (crsd) can be found in the following directories:

ORA_CRS_HOME/log/hostname/crsd

the crsd.log file is archived every 10MB as crsd.101, crsd.102 ...
ORACLE_CRS_HOME/log//alert.log

Oracle Event Tracing

Oracle provides various diagnostic tools for debugging the RDBMS. Certain events can be turned on to dump diagnstic information to trace files. Next, some INIT.ORA parameters are available that can be used while diagnosing memory and disk corruptions. There parameters are not set during normal operation of the database because they affect the performance. But in some circumstances it becomes necessary to turn them on to diagnose the issues with RDBMS, you can think it as extended SQL trace.

Health Check - RAC/DB

Running the following OS commands you can get the Health Check for your cluster and Database, all the output is redirected to one file to analyze.You can run it in a .bat file or separately on the command prompt.

RMAN Duplicate Database from RAC ASM to RAC ASM (11gR2)

Duplicating RAC database is very simple, first duplicate RAC Database to a single instance using RMAN and convert the single instance into a RAC cluster.
Testing Environment:
- Two nodes RAC 11gR2 (11.2.0.3) on Windows 2008R2
- Source RAC Database: TESTRAC (testrac1,testrac2)
- Target RAC Database: DUPDB (dupdb1,dupdb2) on the same hosts where TESTRAC is running
- ASM instances with three diskgroups (DGDUP for dupdb)
- Backup took already (on Netbackup) for Source Database, We CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE; for our environment

The clock on host is not synchronous with the mean cluster time (RAC 11g)

Symptoms
Cluster alert log on one of the nodes shows the following message

[ctssd(804)]CRS-2409:The clock on host or-21 is not synchronous with the mean cluster time. No action has been taken as the Cluster Time Synchronization Service is running in observer mode.

OR

[ctssd(9534)]CRS-2409:The clock on host   is not synchronous with the mean cluster time. No action has been taken as the Cluster Time Synchronization Service is running in observer mode.

DG4MSQL - Where clause does not return data

Problem:
One of the customer was facing issue while querying to SQL Server Database using dblink through DG4MSQL.  They have a view on SQL Server side, when they were querying this view with where clause having string in arabic,it did not return data. Although data was available and it could be seen when using query without where clause.
Customer is using 11g (11.2.0.3) RAC - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production runs on Microsoft Windows 2008, Gateway is running on same  Database machine.

MSQL to MSQL Replication using GG

Prerequisite
Before attempting this post please review the other related GoldenGate posts on the page Other Cateogories for understanding GoldenGate concepts.
Goal
Testing the Oracle Golden Gate for SQLServer to SQLServer real time replication.

Gather Statistics

On SYS Objects
If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics.  The collection of statistics on SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects.

Simple Network Tests

Some times there is slowness in network and end users face it but complaining about the database or application. We can use some initial testing for such network slowness.

Using the SQL TUNING ADVISOR (STA)

SQL Tuning Advisor analyzes candidate SQL statements, and execute a complete 
analysis of the statement including: 
 - Determining stale or missing statistics
 - Determining better execution plan
 - Detecting better access paths and objects required to satisfy them (indexes, materialized views)
 - Restructuring SQL

How to use the Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor (ADDM) is an integral part of the Oracle RDBMS capable of gathering performance statistics and advising on changes to solve any existing performance issues measured. For this it uses the Automatic Workload Repository (AWR), a repository defined in the database to store database wide usage statistics at fixed size intervals (60 minutes).

ORA-20000 on executing DBMS_STATS.GATHER_TABLE_STATS on table

Problem:
DBMS_STATS.GATHER_TABLE_STATS('HOME','T_temp')

ORA-20000: Unable to analyze TABLE "HOME"."T_TEMP", insufficient
privileges or does not exist

Exadata Database Machine - Intro

Oracle Exadata is a database appliance with support for both OLTP and OLAP workloads. It can be said as “cloud in a box” composed of database servers, Oracle Exadata Storage Servers, an InfiniBand fabric for storage networking and all the other components required to host an Oracle Database.

ORACLE EXALYTICS - Intro

Background
We live in the age of information and information is power. In the business enterprise, it is imperative that everyone has the information they need in order to accurately and effectively fulfill their business obligations.

Oracle DBConsole service failed to start on Windows

If you have a database control of any database version on WINDOWS Operating System, some times OracleDBConsole Service failed to start with the following error, cause is not determined .

CRSCTL Utility/Other Cluster Commands Reference (11g)

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling Oracle Clusterware APIs for Oracle Clusterware objects.  You  can perform check, start, and stop operations on the cluster with CRSCTL. This utility is located in the Grid_home/bin directory.

Oracle Database Firewall

Oracle Database Firewall is the first line of defense for databases, providing real-time monitoring of database activity on the network. Highly accurate SQL grammar-based technology blocks unauthorized transactions, helping prevent internal and external attacks from reaching the database. Oracle Database Firewall is easy to deploy, requiring no changes to existing applications or databases.

Case Sensitive Passwords in 11g

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

Disable Oracle's password expiry (11g)

 Unlike older releases, Oracle 11g sets password expiry by default. That's really annoying

• for SYS and SYSTEM: nobody wants to regularly change (and forget) them.

Slow TNSPING time

TNSping's only function is to send a Connect Packet (NSPTCN) to the listener; the listener replies with a Refuse Packet (NSPTRF) and a round trip time is computed. TNSping should never be used to test network performance.

ORA-01017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log

Goal
How to reproduce the error ORA-3136 WARNING : inbound connection timed out ?
This is the most common reason when you notice the warning message in the alert log.

DG4MSQL Installation/Configuration - 11g RAC

Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application.

Installing 11g RAC on Windows 2008

Overview of new concepts in 11gR2 Grid Infrastructure
The single client access name (SCAN) is the address used by all clients connecting to the cluster. The SCAN
name is a domain name registered to three IP addresses, either in the domain name service (DNS) or the Grid
Naming Service (GNS). The SCAN name eliminates the need to change clients when nodes are added to or

Create the database - RAC 11g

Run ASMCA to create diskgroups
Prior to creating a database on the cluster, the ASM diskgroups that will house the database must be created.
In an earlier chapter, the ASM disks for the database diskgroups were stamped for ASM usage. We will now

RDBMS Software Install -RAC 11g

Prior to installing the Database Software (RDBMS) it is highly recommended to run the cluster verification
utility (CLUVFY) to verify that Grid Infrastructure has been properly installed and the cluster nodes have

Oracle Grid Infrastructure Install

Basic Grid Infrastructure Install (without GNS and IPMI)

• Shutdown all Oracle Processes running on all nodes (not necessary if performing the install on new servers)

Prepare the shared storage for Oracle RAC

This post describes how to prepare the shared storage for Oracle RAC
1. Shared Disk Layout
2. Enable Automounting of disks on Windows
3. Clean the Shared Disks
4. Create Logical partitions inside Extended partitions
5. Drive Letters

Prepare the cluster nodes for Oracle RAC

Before installation of RAC 11g R2 , you have to prepare the cluster nodes. Following must be performed.
User Accounts
The installation should be performed as the Local Administrator, the Local Administrator username and

SINGLE CLIENT ACCESS NAME (SCAN)

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access Oracle Databases running in a cluster. The benefit is that the client’s connect information does not need to change if you add or remove nodes in the cluster.

Direct printing to a default client printer in Oracle developer 10g

Purpose:  Steps how to print on a default client printer directly without previewing or displaying the report. (Oracle 10g)

Files Attached: The following files are required and can be downloaded from below.
orarrp.zip      orarrp folder that includes the printing tool.

Oracle Hints

Note: The demos on this page are intended to show valid syntax but it is far easier to use a hint to make a mess of things than it is to improve things. So many, if not most, of the demos will increase the cost.

Join methods:

How to grant on v$ views

If you face with an error when try to give permission on a v$view
SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Configuring new disks for ASM

RAC Speedup

If you want to make the query response speed up, then alter table with parallel option

alter table tablename parallel;

VIP Concept

1- The system shown here is 2 node RAC

Create and configure a listener

The listener forwards client requests to supported services. These services can be configured statically in the listener.ora file or they can be dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the PMON process.

Create and manage multiple network configuration files

LISTENER.ORA

If you have a server that is running multiple versions of Oracle software (multiple Oracle homes, each home has one or more database instances). You can use multiple listener.ora files.

Transparent Data Encryption (TDE) & LogMiner

1- Change in sqlnet.ora
######################SQLNET.ORA###############################

NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
sqlnet.authentication_services= (NTS)

Create and Manage a tablespace that uses NFS mounted file system file

Direct NFS
Direct NFS is a new feature introduced with Oracle 11g and  is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.

Create and manage bigfile tablespaces

Bigfile Tablespaces
- a tablespace with a single, but very large (up to 4G blocks) datafile.
-A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile.A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile.

Create and manage database configuration files

1- PFILE

Related Topic: Click here to view
Related Topic: Click here to view

Write alert to alert log

execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' Test alert ');

Configure the database environment to support optimal data access performance

The Symptoms and the Problems
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:

Stripe data files across multiple physical devices and locations

Goal of striping data is simple:-
To avoid I/O bottlenecks during parallel processing, all tablespaces  accessed by parallel operations should be striped.  Many current OS's support disk striping hence DBA's need not stripe data  across the disk.

Create and manage temporary, permanent, and undo tablespaces

Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations.Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.

Determine and set sizing parameters for database structures

You can determine/set parameter sizes using Enterprise Manager's Server Tab-> Storage section. This is first section of configuration of database structure, and second is parameters form parameter file.

Create the database

Note: Practice performed on 11g Rel 1 (APPS DB)

Considerations Before Creating the Database
Database Planning Tasks
- Plan the database tables and indexes and estimate the amount of space they will require.
- Plan the layout of the underlying operating system files your database will comprise. To greatly simplify this planning task, consider using Oracle
Managed Files and Automatic Storage Management to create and manage the operating system files that comprise your database storage.

Error: Procedure entry point longjmp could not be located in dynamic link library orauts.dll

If you get "Procedure entry point longjmp could not be located in dynamic link library orauts.dll" while connecting the SQL Plus, your environment variables are not set properly.

Spell the numbers - Examples

Using JSP format

SELECT TO_CHAR(TO_DATE(123.50,'J'),'JSP') to_words FROM   dual;

SELECT    TO_CHAR (TO_DATE (TRUNC (&num), 'J'), 'JSP')
       || ' Point '
       || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (&num, INSTR (&num, '.') + 1)),'J'),'JSP')
  FROM DUAL;

Collect Full DML Statements for specific user without AUDIT

Use a logon trigger for the specific user to enable SQL tracing : it  generates a  trace file in the USER_DUMP_DEST location.The trace file contains all the SQL statements executed by the user after logon.
Create the following trigger in SYS schema:

create or replace trigger SCOTTLOG
after logon
on scott.schema
begin
   -- optionally add a tag to the trace file name to make the files discernable:
   execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER =''SCOTT''';
   -- enable sql_trace for this session:
   execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/

Note that all the SQLs of SCOTT user after logon will be traced and if the required collection is sufficient, disable the trigger to stop the collection and generation of trace files.

SQL> alter trigger scottlog disable;

Note
 i) Overhead involved is trace file generation in user_dump_dest.
 ii) Grant the CREATE SESSION system privilege to the user account rather than granting the CONNECT role. This is necessary  because in the versions older than 10gR2 the CONNECT role includes the ALTER SESSION system privilege and the user can disable sql tracing after logon. To avoid this, do not grant the CONNECT and RESOURCE roles , but grant the necessary privileges explicitly.

Ref: 309798.1

How to check if the IO of the Database is Slow

We will outline some of the thresholds whereby RDBMS Support may consider IO to be slow and thus a potential reason for a performance problem. If the underlying cause for slow performance is found to be a result of slow IO at the OS level, then the appropriate vendor responsible for the IO subsystem (hardware and software) should be engaged to diagnose and correct the situation

How To Configure Anti-Virus On Windows Server Running Oracle Database

When an Anti-virus performs a scan on a file it holds a lock on it. This lock interrupts the normal functioning of the database. To prevent any disaster situation such as database crash/hang, we recommend the following files to be excluded from online anti-virus scanning.

DCD & TCP Keep Alive (Windows)

KNOWN PROBLEMS OR LIMITATIONS  for DCD
1- Of the few reported problems, perhaps the most significant is DCD's poor performance on Windows NT. Dead connections are cleaned up only when the server is rebooted and the database is restarted. Exactly how well DCD works on NT depends on the client's proto implementation.

All Users with Client side Info

Create Or Replace Force View Sys.Av_users_all (Logon_time,
                                               Sid,
                                               Serial#,

Calling External Procedure (DLL) from PL/SQL

If Oracle has been configured for the external procedures , you can call any DLL from your PL/SQL. How to configure for EXTPROC please click on the link below

Configuring SQL*Net for External Procedures

External procedures are functions written in a third-generation language  
(3GL) such as C, and callable from within PL/SQL or SQL as if they were a 
PL/SQL procedure or function.  External procedures enable you to take  
advantage of the strengths and capabilities of a 3GL programming language  
in a PL/SQL environment.

Restricting user to change the password

If for some reason you want to restrict the user to change the password ,

Use the event trigger AFTER ALTER with the attribute function ora_des_encrypted_password 

specific for ALTER USER events:

CREATE or REPLACE TRIGGER pass_change 
    AFTER ALTER on database
          BEGIN
               IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and ora_des_encrypted_password is not null
               THEN
                  RAISE_APPLICATION_ERROR(-20003,  'You are not allowed to alter password user.');
               END IF;
          END;
/

Note:
In the trigger, instead of raising the error, you may want to insert a row into a custom audit table.

How To Audit Application Username Using Trigger

With default auditing Oracle stores Oracle username but not the application username. In order to store application username you need to set CLIENT IDENTIFIER for the application session which is connecting to the database.

Copying Files Over Remote Desktop

Start up a remote desktop dialog and set the local resources as desired using options button. After this you will be able to use the clipboard of local system on remote machine.

How To Use PROFILES To Limit User Resources

You have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users.

How to Audit Connect AS SYSDBA Using Oracle Server

Windows Systems
---------------
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ' as sysdba ') in the
event viewer. 

Application Tuning using Explain

The EXPLAIN facility can be used quickly and easily to
determine how the data is accessed (known as the access path) for
any given SQL statement, namely queries.  The ability to modify
this access path can yield an incredible performance benefit.

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Description

This script checks the current users Foreign Keys to make sure of the
following:

1) All the FK columns have indexes to prevent a possible locking

How to Collect Diagnostics for Database Hanging Issues

If you are encountering a database hanging situation, you need to take system
state dumps/hanganalyze so that Oracle Support can begin to diagnose the cause of the problem.
Whenever you take such dumps for a hang it is important to take at least 3 of
them a few minutes apart, on all instances of your database.

How To Connect Using A Sqlplus Preliminary Connection

There is an another  way to connect to a database through Sqlplus when all other connection methods are hanging.  Using a Sqlplus preliminary connection you will be able to connect to the database since no session is actually created,

Using DBMS_MONITOR

Using DBMS_MONITOR, Tracing has been enabled for diagnose and workload management based on, a specified client identifier or a hierarchical combination of service name, module name and action name. Also we have the facility to trace on session level.

Using TKPROF

The TKPROF facility accepts as input an SQL trace file and produces a formatted output file.  Note that TKPROF can be run on individual or
appended trace files to produce the formatted output file.

How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug

The ORADEBUG utility can enable/disable setting the SQL tracing for another 
user's session or an MTS session.  To enable tracing for another session, the 
Oracle process identifier (PID) or the Operating System processes identifier 
(SPID) must be identified from v$process.  This is an effective way of capturing 
a SQL trace from a process which is already running.  The output can be used to 
analyze SQL related performance issues.

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.

This is a special EVENT code. It can be used to signal Oracle to perform SQL_TRACE type 

actions. The 10046 trace is the equivalent of setting SQL_TRACE=TRUE.The advantage of using 

the event is that extra details may be output to the trace file depending on the level

specified with the event. 

Investigating a Database Performance Issue

To investigate a slow performance problem, begin by deciding what diagnostics will be gathered. To do this, consider the following questions and take the appropriate action:-
Is the performance problem constant or does it occur at certain times of the day ?

Optimizing Joins

The first thing to remember about optimising the performance of joins is that the performance can decrease considerably as a 3rd or 4th or 5th table is added to the query. The more tables that are added, the more important it is to ensure that the query is properly tuned and tested.

Oracle database Performance Tuning

The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. ne should do performance tuning for the following reasons:

• The speed of computing might be wasting valuable human time (users waiting for response);

Monitoring Index Usage

Monitoring Index Usage

Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

Desktop Heap Overview

Desktop heap is probably not something that you spend a lot of time thinking about, which is a good thing.  However, from time to time you may run into an issue that is caused by desktop heap exhaustion, and then it helps to know about this resource.  Let me state up front that things have changed significantly in Vista around kernel address space, and much of what I’m talking about today does not apply to Vista.

Schedule Killing sessions dynamically

You can kill session dynamically as below in a single instance but for the RAC you may face issues , so approach below can be used.

WUC-14 Object Cache Error: Specified object handle {0} not found in the cache.

WUC-14 was occurring when developer was trying to write a file with more than 10000 lines as below.

500 Internal Server Error

Client was getting following error while accessing the form application.

500 Internal Server Error
java.io.IOException: Too many open files at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.(FileInputStream.java:106) at java.io.FileInputStream.(FileInputStream.java:66) at java.io.FileReader.(FileReader.java:41) at oracle.forms.servlet.ConfigFileParser.(Unknown Source) at oracle.forms.servlet.FormsServlet.doRequest(Unknown Source) at

Finding FK for a Parent table

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name

     from all_constraints

    where constraint_type='R'

    and r_constraint_name in (select constraint_name from all_constraints

    where constraint_type in ('P','U') and table_name='T_LOOKUP');

ORA-19815: WARNING: db_recovery_file_dest_size

We were getting warning in Alertlog for a database although we were not using FRA.

ORA-19815: WARNING: db_recovery_file_dest_size of 6442450944 bytes is 99.78% used, and has 14326272 remaining bytes available.

How to Delete a Windows Service in Windows

If you are a fan of tweaking your system and disabling services, you might find that over time your Windows Services list becomes huge and unwieldy with a large number of services in the list that will never be enabled.