Thursday, February 9, 2012

Hard parsing vs Soft parsing in Oracle

Oracle SQL is parsed before execution, and a hard parse includes these steps:

  • Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)
  • Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
  • Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
  • Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.
  • Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).
  • Create executable - Oracle builds an executable file with native file calls to service the SQL query.
Reference: Oracle Tips by Burleson Consulting

Wednesday, February 1, 2012

Abbreviations commonly used for an Oracle DB

Oracle System Identifier (SID)

Transparent Network Substrate (TNS)

Transparent Application Failover (TAF): A run-time failover for high-availability environments, such as Oracle Real Application Clusters and Oracle Fail Safe, that refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and, optionally, resume a SELECT statement that was in progress. This reconnect happens automatically from within the Oracle Call Interface (OCI) library.

VPD (Virtual Private Database) is also known as Fine Grained Access Control (FGAC) or Row-level Security (RLS). It provides added security capabilities to the Oracle database by masking data so that users only see their private information.

TKPROF (Transient Kernel Profiler): used to convert trace files into more read-able format



Reference: Oracle documentation

Service Vs SID in tnsnames.ora file

A service name is more flexible than a SID would be.

A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name (think about a clustered environment where you have multiple instances that all are the same database under the covers).

A database on the other hand has a single SID. And a single SID goes to a single database. It is a pure 1:1 relationship.

A service is a many to many relationship.

Service names are used with dynamic registration - the data registers with the listener after it starts up. Once it does that, you can connect.


With the SID - that is more like telling the listener "I want you to connect to this specific database, I know the 'address', here you go"

With the SERVICE - you are asking the listener to put you in touch with a database that can service your request, a database that registers using that service.

That you cannot connect using the service name MYDB means your database is not registering with that listener (see the Net Admin guide for dynamic registration) or if it is registered with that listener (lsnrctl services is a command you can use to see the registered services) then it is registering with a different name.

Reference: Tom Kytes

RAC Software Architecture

Real Application Clusters Software Architecture




Some of these components are supplied with the Oracle database software while others are vendor-specific.

The Operating System-Dependent Clusterware
Real Application Clusters Shared Disk Components
Real Application Clusters-Specific Daemon and Instance Processes
The Global Cache and Global Enqueue Service

The Operating System-Dependent Clusterware

Real Application Clusters processing uses operating system-dependent (OSD) clusterware to access the operating system and for cluster-related service processing such as communicating information about instance startup and shutdown. Vendors provide the OSD clusterware for UNIX operating systems, and Oracle provides the OSD clusterware for Windows NT and Windows 2000 operating systems. The OSD has the following subcomponents:

The Cluster Manager
The Node Monitor
The Interconnect

The Cluster Manager

The cluster manager (CM) oversees internode messaging that travels over the interconnect to coordinate internode operations. The cluster manager also provides a global view of the cluster and the nodes and instances that are members of it. The cluster manager also controls cluster membership.
The Node Monitor

The cluster manager includes a subset of functionality known as a node monitor. The node monitor polls the status of each resource in the cluster including the nodes, the interconnect hardware and software, and the shared disks. In the Oracle-supplied Cluster Manager for Windows, the node monitor also polls the Oracle instances.

The cluster manager informs clients and the Oracle server when the status of cluster resources change. This is because Real Application Clusters manages cluster membership by reconfiguring the cluster database when a joining instance registers with the cluster manager or when an existing instance disconnects from it.

The node monitor also serves the cluster manager by:

Providing node management interface modules
Discovering and tracking the membership states of the nodes by providing a common view of node membership across the cluster
Detecting and diagnosing changes in the states of active nodes and communicating information about those change events

The IPC or interconnect controls messaging among the nodes. Real Application Clusters also uses the IPC to transfer data blocks between instances.

The Global Services Daemon

The Global Services Daemon (GSD) runs on each node with one GSD process per node. The GSD coordinates with the cluster manager to receive requests from clients such as the DBCA, EM, and the SRVCTL utility to execute administrative job tasks such as instance startup or shutdown. The GSD is not an Oracle instance background process and is therefore not started with the Oracle instance.
Instance Processes Specific to Real Application Clusters

A Real Application Clusters database has the same processes as single-instance Oracle databases such as process monitor (PMON), database writer (DBWRn), log writer (LGWR), and so on. There are also additional Real Application Clusters-specific processes. The exact names of these processes and the trace files that they create are platform-dependent.

Global Cache Service Processes (LMSn), where n ranges from 0 to 9 depending on the amount of messaging traffic, control the flow of messages to remote instances and manage global data block access. LMSn processes also transmit block images between the buffer caches of different instances. This processing is part of the Cache Fusion feature.
The Global Enqueue Service Monitor (LMON) monitors global enqueues and resources across the cluster and performs global enqueue recovery operations. Enqueues are shared memory structures that serialize row updates.
The Global Enqueue Service Daemon (LMD) manages global enqueue and global resource access. Within each instance, the LMD process manages incoming remote resource requests.
The Lock Process (LCK) manages non-Cache Fusion resource requests such as library and row cache requests.
The Diagnosability Daemon (DIAG) captures diagnostic data about process failures within instances. The operation of this daemon is automated and it updates an alert log file to record the activity that it performs.

The Global Cache and Global Enqueue Service

The Global Cache Service (GCS) and Global Enqueue Service (GES) are integrated components of Real Application Clusters that coordinate simultaneous access to the shared database and to shared resources within the database. These services maintain consistency and data integrity. The GCS and GES on each instance, as well as the Cluster Manager, use the IPC to communicate between instances and within the cluster. This section describes the following features of the GCS and GES:

Application Transparency
Global Resource Directory with Distributed Architecture
Resource Mastering and Affinity
GCS and GES Interaction with the Cluster Manager

Application Transparency

The coordination of access to resources that is performed by the GCS and GES is transparent to applications. Applications in Real Application Clusters use the same concurrency mechanisms as in single-instance Oracle databases.
Global Resource Directory with Distributed Architecture

The GCS and GES maintain a Global Resource Directory to record information about resources. The Global Resource Directory resides in memory, is distributed throughout the cluster, and is available to all active instances. In this distributed architecture, each node participates in the management of information in the directory. This distributed scheme provides fault tolerance and enhanced runtime performance.

The GCS and GES ensure the integrity of the Global Resource Directory even if multiple nodes fail. The shared database is always accessible if at least one instance is active after recovery is completed. The fault tolerance of the resource directory also enables Real Application Clusters instances to start and stop at any time, in any order.
Resource Mastering and Affinity

The Global Cache and Global Enqueue Services maintain information about each resource within a cluster. The GCS and GES nominate one instance to manage all information about a particular resource. This instance is called the resource master. The GCS evaluates resource mastering periodically and changes the resource master based on data access patterns. This reduces network traffic as well as resource acquisition time.

GCS and GES Interaction with the Cluster Manager

The GCS and GES operate independently of the cluster manager. However, these services rely on the cluster manager for timely and correct information about the statuses of the instances in the cluster. If these services cannot obtain the information they need from a particular instance, then Oracle shuts down the unresponsive instance. This ensures the integrity of Real Application Clusters databases because each instance must be aware of all other active instances to coordinate shared disk access.

Reference: Oracle Documentation

Thursday, January 5, 2012

All services on node 2 down (10gRAC 2-node)

all service on node 2 were down:

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

/oracle_crs/product/10.2.0/crs_1/bin

-> ./crs_stat -t

Name Type Target State Host

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

ora....U1.inst application ONLINE ONLINE nc2s...db01

ora....U2.inst application ONLINE OFFLINE

ora....4U1.srv application ONLINE ONLINE nc2s...db01

ora....4U2.srv application ONLINE OFFLINE

ora....RV01.cs application ONLINE ONLINE nc2s...db01

ora.RMS4U.db application ONLINE ONLINE nc2s...db01

ora....SM1.asm application ONLINE ONLINE nc2s...db01

ora....01.lsnr application ONLINE ONLINE nc2s...db01

ora....b01.gsd application ONLINE ONLINE nc2s...db01

ora....b01.ons application ONLINE ONLINE nc2s...db01

ora....b01.vip application ONLINE ONLINE nc2s...db01

ora....SM2.asm application ONLINE OFFLINE

ora....02.lsnr application ONLINE OFFLINE

ora....b02.gsd application ONLINE OFFLINE

ora....b02.ons application ONLINE OFFLINE

ora....b02.vip application ONLINE ONLINE nc2s...db01

login as root and restart crs service:

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

# cd /oracle_crs/product/10.2.0/crs_1/bin

# ./crsctl check crs

Failure 1 contacting CSS daemon

Cannot communicate with CRS

Cannot communicate with EVM

# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

/oracle_crs/product/10.2.0/crs_1/bin

-> .//crs_stat -t

Name Type Target State Host

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

ora....U1.inst application ONLINE ONLINE nc2s...db01

ora....U2.inst application ONLINE ONLINE nc2s...db02

ora....4U1.srv application ONLINE ONLINE nc2s...db01

ora....4U2.srv application ONLINE ONLINE nc2s...db02

ora....RV01.cs application ONLINE ONLINE nc2s...db01

ora.RMS4U.db application ONLINE ONLINE nc2s...db01

ora....SM1.asm application ONLINE ONLINE nc2s...db01

ora....01.lsnr application ONLINE ONLINE nc2s...db01

ora....b01.gsd application ONLINE ONLINE nc2s...db01

ora....b01.ons application ONLINE ONLINE nc2s...db01

ora....b01.vip application ONLINE ONLINE nc2s...db01

ora....SM2.asm application ONLINE ONLINE nc2s...db02

ora....02.lsnr application ONLINE ONLINE nc2s...db02

ora....b02.gsd application ONLINE ONLINE nc2s...db02

ora....b02.ons application ONLINE ONLINE nc2s...db02

ora....b02.vip application ONLINE ONLINE nc2s...db02

MOS IDs

Installs, Upgrades, Patches:
-----------------------------

Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]

Friday, December 30, 2011

Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment [Index block splits]

Refer below on MOS

Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment. [ID 873243.1]

http://www.confio.com/English/Tips/Index_Block_Split.php

# Show all sessions waiting for any lock:

select event,p1,p2,p3 from v$session_wait where wait_time=0 and event='enqueue';

# From 10g a different more descriptive event name exists for the more frequent enqueues and you can query the TX wait event as follows:

select sid,p1raw, p2, p3 from v$session_wait

where wait_time=0 and event='enq: TX - row lock contention';

# Show sessions waiting for a TX lock:

select * from v$lock where type='TX' and request>0;

# Show sessions holding a TX lock:

select * from v$lock where type='TX' and lmode>0;