Happy Earth Day 2013 !!

April 22 each year is observed as Earth Day. I wish all my friends Happy Earth Day!! Enjoy this Earth Day Google Doodle.

समुद्र वसने देवी, पर्वत स्तन मण्डले
विष्णुपत्नी नमस्तुभ्यं, पाद-स्पर्शं क्षमस्व मे

Samudravasane devi,
parvatastana mandite,
visnupatni namastubhyam,
padasparsham kshamasvame

The above Sanskrit Shloka means – dressed in the flowing robes of the seas and oceans (just like a draped saree), with mountains as your breasts (source of life giving nectar in form of rivers). O consort of Vishnu, I salute you and beg forgiveness for touching you with my feet.

Following is my list of some tips which you can try to contribute on this day

1. Plant Trees
2. Turn the lights off when you leave the office/room at night
3. Use your bicycle or other forms of human powered transportation to commute to work or school
4. Use Blackle.com as search engine. You can get the know the benefit of this here. All google fans the search is still powered by Google.

Here is the link for kids on this day.
I am sharing the following video from Earth Day Network - dedicated for this cause.

 

Happy Holi 2013!

I take this opportunity to wish all my readers a very Happy Holi. I wish that this holi brings all the happiness and joy in your life.
I wish your life is filled with all beautiful colors.

For those readers who are interested in knowing about Holi here is wiki link.

All my non-indian friends following youtube video is for you

 

Workshop on Oracle 11gR2 DBA: RAC and ASM

23, 24, 25, 26 February 2013
Hotel Lemon Tree, Electronic City, Bangalore, India.

Workshop on Oracle 11gR2 DBA – RAC and ASM

Recently I had attended the workshop on Oracle RAC and ASM at Electronic City, Bangalore, India. The training was given by RAC guru Murali Vallath. I am sharing my learning and experience from this workshop.

I sincerely thank Murali for inspiring and encouraging me to learn the latest feature in Oracle – RAC and ASM.

The training batch comprised participants from different geographical location and spanning different industries. I feel lucky to be part of this batch.  Lastly, I thank Sai Ram for organizing this workshop.

Standing (L to R): Syed, Abul, Chandra, Amit, Prabhakar, Vijay (me), Vasu,  Rukmini
Sitting (L to R): Bikash, Gautam, Murali, Kushal, Milind, Vivek, Sathish
Photo clicked by Sai Ram, Venue: Lemon Tree Hotel, Electronic City, Bangalore, India

What is Clustering?

Clustering is the grouping of individual computers in such a way that they can act as a single computer system. These combined resources can then be presented to applications as a single system.  One benefit of a cluster is that the application does not need to be cluster “aware”, and requires no special coding to take advantage of most cluster related services.

Oracle clustering is a shared everything architecture that involves sharing of storage by the systems of the cluster.

Oracle RAC:

Oracle RAC stands for Real Application Cluster. It was introduced by Oracle with 9i release. Latest version is 11gR2.

Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for all your business applications. Oracle RAC is a key component of Oracle’s private cloud architecture.

Single Instance Vs RAC Instance

As can be seen from the below diagram Oracle RAC database has more than one instance, whereas the database is single and shared across all the instances. Thus focus of RAC is on share everything concept. This ensures availability of Oracle database and also the scalability. Each RAC instance is installed on different machine (called nodes) thus scaling the computing power and maximizing availability.

Source: http://docs.oracle.com/cd/E11882_01/server.112/e10713/startup.htm

Below is the architecture diagram which shows the implementation of Oracle RAC instance in real life scenario. What it additionally shows is the interconnect (hb) i.e high speed communication between the RAC instances to ensure ACID (Atomicity, Consistency, Independent and Durability).

Source: http://docs.oracle.com/cd/B28359_01/rac.111/b28254/admcon.htm

ASM (Automatic Storage Management) Instance:

An Oracle ASM instance is a special Oracle instance that manages Oracle ASM disks.

It’s an instance for Storage Management. Both the ASM and the database instances require shared access to the disks in an ASM disk group.

ASM instances manage the metadata of the disk group and provide file layout information to the database instances. Database instances direct I/O to ASM disks without going through an ASM instance.

An ASM instance is built on the same technology as a database instance. For example, an ASM instance has a system global area (SGA) and background processes that are similar to those of a database instance. However, an ASM instance cannot mount a database and performs fewer tasks than a database instance.

Oracle Commands for RAC & ASM

I have tried to highlight the commands which are used for different instances. As the system has grown with different components, so have the commands to manage and monitor them. Below table shows the list of commands, their usage, where used (RAC/ASM) and home directory where the command is located.

Commands	Usage	RAC	ASM	Home
cluvfy	Cluster Verify Utility or cvu performs system checks in integrity of all nodes, integrity of OCR, connectivity between all nodes, compare properties of one node with that of other and space availability	Y	Y	Grid Home and Oracle Home
olsnodes	The olsnodes command provides the list of nodes, local node name, vip addresses of nodes, status of nodes and type of nodes in cluster.	Y		Oracle CRS Home, Run as root user
crsctl	Starting and stopping Oracle Clusterware resources, Enabling and disabling Oracle Clusterware daemons, Checking the health of the cluster, Managing resources that represent third-party applications.	Y	Y* Voting disk	Grid Home
srvctl	Server Control Utility to manage the Oracle RAC database. SRVCTL commands to add, remove, start, stop, modify, enable, and disable a number of components/objects, such as databases, instances, listeners, SCAN listeners, services, grid naming service (GNS), and Oracle ASM.Some SRVCTL operations for instance, database, service and nodeapps modify the configuration data stored in the Oracle Cluster Registry (OCR).  Good link which shows various options for this command is here	Y	Y	Oracle Home,Run as oracle user
asmcmd	ASMCMD is a command-line utility that you can list the content, perform searches, create and remove directories and aliases, display space utilization within Automatic Storage Management (ASM) disk groups. Orafaq link for ASMCMD is here		Y	Preferably separate ASM home
ocrconfig	The ocrconfig command is used to manage the OCR. Ocrconfig provides the ability to import, export, add, delete, restore, overwrite, backup, repair, replace, move, upgrade, or downgrade the OCR. Same command can be used for OLR using –local option.	Y	Y	Grid Home,Run as root user
ocrdump	The ocrdump utility allows you to dump the contents of the OCR or the OLR to a file or to stdout. You can then read the resulting output for diagnostic and administration purposes.	Y	Y	Grid Home,Run as root user
ocrcheck	The ocrcheck utility is used to validate the integrity of the OCR and the OLR. Use the ocrcheck command whenever you have made any changes to your cluster (such as changing the VIP for example)	Y	Y	Grid Home,Run as root user
oifcfg	The Oracle Interface Configuration Tool is used to define and administer network interfaces such as the public and private interfaces.	Y		Grid Home,Run as oracle User

Processes

I have tried to consolidate all processes which are available in Single Instance, RAC Instance and ASM Instance of Oracle 11gR2 Database.

Processes	Name	Instance
		Single	RAC	ASM
ACFS	ASM Cluster File System CSS Process		Y	Y
ACMS	Atomic Controlfile to Memory Service (ACMS). In an Oracle RAC environment		Y
ARBn	ASM Rebalance Process			Y
ARBx	Background Process	Y	Y
ARCH	Archiver Process	Y	Y
ARCHMON	Archive Monitor	Y	Y
ASMB	Automatic Storage Management	Y	Y	Y
BMRn	Automatic Block Media Recovery Slave Pool Process	Y
Bnnn	ASM Blocking Slave Process for GMON			Y
CJQ0	Job Queue Coordinator Process	Y
CKPTxx	Checkpoint	Y		Y
CTWR	Change Tracking Writer	Y	Y
DBRM	Database Resource Manager Process	Y
DBWn	Database Writer	Y	Y
DIAG	Diagnostic Capture Process	Y		Y
DMnn	Data Pump Master Process	Y
Dnnn	Dispatcher	Y	Y
DRnn	ASM Disk Resynchronization Slave Process			Y
EMNC	EMON Coordinator Process	Y		Y
Ennn	EMON Slave Process	Y		Y
EXTPROC	Callout queues	Y	Y
FBDA	Flashback Data Archiver Process	Y		Y
FMON	File Mapping Monitor Process	Y		Y
GEN0	General Task Execution Process	Y		Y
GMON	ASM Disk Group Monitor Process			Y
GTX0-j	Global Transaction Process. The GTX0-j process provides transparent support for XA global transactions in a RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions.		Y
Innn	Disk and Tape I/O Slave Process	Y
Jnnn	Job Queue Slave Process	Y
LCK0	Instance Enqueue Process. The LCK0 process manages non-Cache Fusion resource requests such as library and row cache requests.		Y
LCKn	Lock Process	Y	Y
LGWR	Log Writer	Y	Y
LISTENER	TCPIP server	Y	Y
LMD	Global Enqueue Service Daemon. The LMD process manages incoming remote resource requests within each instance.		Y
LMHB	Global Cache/Enqueue Service Heartbeat Monitor	Y	Y	Y
LMON	Global Enqueue Service Monitor. The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.		Y
LMS	Global Cache Service Process. The LMS process maintains records of the datafile statuses and each cached block by recording information in a Global Resource Directory (GRD).		Y
MARK	Mark AU for Resynchronization Coordinator Process	Y		Y
MMAN	Background Process	Y	Y
MMNL	Memory Monitor Light	Y	Y
MMON	Memory Monitor	Y	Y
Mnnn	MMON Slave Process	Y		Y
ORBn	Background Process	Y	Y
OSMB	Background Process	Y	Y
PING	Interconnect Latency Measurement Process	Y	Y	Y
PMON	Process Monitor	Y	Y
Pnnn	Parallel Query Slave	Y	Y
PRnn	Parallel Recovery Process	Y
PSP0	Process Spawner Process	Y		Y
QMNxx	Advanced Queueing Processes	Y	Y
RBAL	Background Process	Y	Y
RCBG	Result Cache Background Process	Y	Y
RECO	Distributed Transaction Recoverer	Y	Y
RMSn	Oracle RAC Management Processes (RMSn).		Y
RSMN	Remote Slave Monitor manages background slave process creation and communication on remote instances.		Y
RVWR	Recovery Writer	Y	Y
Rnnn	ASM Block Remap Slave Process			Y
SMON	System Monitor	Y	Y
Snnn	Servers	Y	Y
SNPxx	Snapshot process queues	Y	Y
VKTM	Virtual Keeper of Time Process	Y		Y
VMB0	Volume Membership Process			Y
Vnnn	ASM Volume I/O Slave Process			Y
Xnnn	ASM Disk Expel Slave Process			Y

Source: http://docs.oracle.com/cd/E14072_01/server.112/e10820/bgprocesses.htm

Workshop also touched Data Guard theory and Performance Tuning in RAC.

I have tried to share what I learnt in a very simple and concise manner. I hope you find this article useful and If you feel that something could have been done better kindly drop me mail at info@mahawar.net

Cheers!
Vijay Mahawar

 

SPOOL APPEND – Workaround to achieve nested spool in Oracle SQL Scripts

Have you ever tried to execute nested scripts with spool within each file. There may have been instances when you might have had numerous sql scripts, with each having their own spool and you might have wanted to invoke them using a main script.
Like as follows:

SPOOL main.log;
.<main.sql>
.SPOOL inner1.log;
..<inner1.sql>
.SPOOL OFF;
..SPOOL inner1_inner.log;
…<inner1_inner.sql>
..SPOOL OFF;
.<sql_statement_1>
.<sql_statement_2>
SPOOL OFF;

Problem:

In above example you might want to have separate spool file for each of the scripts viz. main.log, inner1.log and inner1_inner.log.

But you cannot achieve this using traditional spool command prior to 10g client, where nesting of spool command was not allowed and in above scenario the main.log won’t show the result of <sql_statement_1> and <sql_statement_2>.

Tip:

Starting Oracle 10g Client you can use SPOOL APPEND command which will append to the end of existing file and this can be used a workaround to achieve the nesting of spools

In the above example I can re-write the script as

SPOOL main.log;
.<main.sql>
SPOOL OFF;
.SPOOL inner1.log;
..<inner1.sql>
.SPOOL OFF;
..SPOOL inner1_inner.log;
…<inner1_inner.sql>
..SPOOL OFF;
SPOOL main.log APPEND;
.<sql_statement_1>
.<sql_statement_2>
SPOOL OFF;

By adding the text highlighted in red you can get the output of sql_statement_1 and sql_statement_2 in main.log itself, which was missed out due to traditional nesting of spool in version of oracle client prior to 10g.

For more enhancement of SPOOL command (CREATE, REPLACE) you can visit Oracle Documentation on Spool command here.

 

Understanding Performance Tuning in Oracle – Google Hangout Event

Dear Friends,
I will be presenting an online session on Oracle Performance Tuning. Its a Google Hangout Event to be conducted on 2nd Feb 2013, Saturday starting at 5:00 pm IST.

For details and registration please click register or click on below screenshot.

Looking forward to catch you all there.

For the events and session conducted by me in past you can visit the events archive section here.

 

‘Fightback’ application from Tech Mahindra to make streets safer for women

I share my heartfelt CONDOLENCES with  Damini/Nirbhaya/Amaanat. Let her soul rest in PEACE in the feet of ALMIGHTY. My prayers to all her family members during this distress time.

Just thought of sharing the following with all of you there. I got this from one of my colleague:

Tech Mahindra had long back developed an application called FightBack which was being used only by the staff members of Mahindra Group.

After the gang-rape of a woman in New Delhi; Anand Mahindra has now thrown open this smart phone application for public use, beyond Mahindra employees.

‘FightBack’ application is designed for making the streets safer for women. The FightBack application tracks a user’s location and sends SOS messages to selected contacts in case of an emergency.

This application is now available for download on the company website for free & IS ONLY FOR INDIAN MOBILE NUMBERS.

The FightBack app allows the user to press on a panic button whenever he/she feels unsafe.

It tracks the location using GPS and alerts chosen contacts about the location map and is available on Android and Blackberry.

www.fightbackmobile.com

Steps – How to download the application

1 – Register yourself or login through Faceback with Post click.
2 – After the successful Login in the portal, Click to Download.
3 – Enter the mobile number on which you want to deploy for the Fight Back application.
4 – Select your handset on which you would like to download the application from the device list.
5 – Click on Download button, and you will receive the link to download via SMS.
6 – By clicking on the downloadable link within the received SMS your application will start downloading.

Some sources available on this app can be found here and here.

 

Wish you all a happy and prosperous New Year 2013 !

Dear Friends,
As you look back on 2012, let’s look forward to 2013.
I take this opportunity to wish you and your family a very happy and prosperous New Year 2013!