ORA 22 invalid session id; access denied

My colleage was badly struck with the 'ORA 22 invalid session id; access denied'  

problem while connecting to any database from an AIX machine (where Oracle client was installed).

All the links including ML note to this errors, never helped to resolve the issue.

In our case, the problem occured because the hostname of this machine was mentioned wrongly in the /etc/hosts file.

Once we corrected the hostname in the hosts file, problem has been resolved.

Regards,

Jaffar

A very happy New Year

Wishing you and your family a very happy and prospers New Year. 

May this New Year bring tons of happiness in your life.

Jaffar

How to boost RMAN performance on AIX5L systems

I have come accross of following two metalinks notes on 'how to improve RMAN performance on AIX5L based systems' and felt worthy blogging about it:

ML 421059.1 : How to boost the performance of RMAN on AIX5L Based Systems

ML 579158.1 : Advise On How To Improve Rman Performance

Excerpt from the ML notes.

IBM suggestions the following AIX related advices:

1. set AIXTHREAD to S on /etc/environment.

2. " ioo -o maxpgahead=256 " to set maxpgahead parameter
Initial settings were : Min/Maxpgahead 2 16

3. " vmo -o minfree=360 -o maxfree=1128 " to set minfree and maxfree...
Initial settings were : Min/Maxfree 240 256

The goal of setting those parameters would be Getting %15-20 performance improvements on RMAN backup performance on AIX 5L Based Systems.

Happy Reading,

Jaffar

A New Family Member in my life

By the grace of almighty we have been blessed with a new baby BOY (a third one) yesterday (7-11-2008) at 8:14pm local Saudi Time. We named him AAHIL.

Regards,

Mrs and Mr. Jaffar

Scientific methods vs Bullet Methods

Of late, there has been a lot of debate, fight, discussion (whatever you call it) in the Oracle community (I would rather say between Oracle experts) on 'which is the best method to tune a database?', a scientific method or Silver Bullets? Many DBAs across the global have expressed their opinions and views, so, I am here to express and share few incidents that I came across. If you decide that I am going to pin point someone or prove which is wrong and which is right, you would be disapointed to know that I am not, simply, becasue  you must be knowing which is the correct method.

I remembered sometime back  a DBA has told me that one of his DBA colleague has rebuild all the indexes in a production database and mailed  to their management and immediate boss stating that this is going (rebuilding indexes) to improve the database performance and also attached an URL which stated that rebuilding indexes will improve the database performance.  I had told my friend to get me the answer for the following questions from his DBA colleague:

Does he know what is the harm or side effects of doing it?

Were there any performance issues in the database, if so, are they related to indexes?

What amount of performance improvements he measured or gained after?

I am yet to get answers for the above questions. And moving on to another incident...

I know a company very personally who follws(still following) so called 'database re-org' method to re-organize their database weekly. When I questioned them why they are doing it so, their answer is that they have gained an immediate performance improvements when they did it first time and they are following this procedure in order to not to drop the performance level. It was really difficult to convience them how dangerious it would to follow this method.

Myself would definitely will not go for 'leaven an arrow in the dark' policy/method. Even if I solve something accidently, I love to spend some time to do R&D to come to a conclusion.

Last but not least, when I visit a doctor, I do expect him to do analysis of my problem and try to cure it permanently rather than giving a pain killer to relief me temporarily from the pain.

I, therefore, invite you all to discuss your wired experiences or incidents that you come across.

Happy reading,

Jaffar

HAPPY RAMADAN

Wishing you and your family a very happy RAMADAN!

RAMADAN Mubarak!

Jaffar

My OCM profile loaded on OTN

Here is the link to my OCM profile on OTN.

http://www.oracle.com/technology/ocm/shussain.html

Happy reading,

Jaffar

I am Oracle 10g OCM now

Hello everybody, here are the few joyful moments (passing OCM exam) of my life which I would like to share with everyone at this moment.

Exactly a month ago, I wrote my OCM exam in India (New Delhi) and it took almost a month to get the result. Thank god, finally I got a mail and I was truly ecstatic to know that I have successfully cleared the OCM exam and I have been awarded OCM status.

Its been reall a long cherished dream became true for me. It definately gives me a good feeling of being OCM and I know how much efforts I have put to clear this exam.

Happy reading,

Jaffar

My experience of converting a cross platform (AIX to HP) single instance database to RAC using RCONFIG tool

Its been really more than a month that I didn't write anything on my blog due to my busy schedule.  Well, I am here now to share my experience of converting a cross platform (AIX to HP) single instance database to 2 node RAC using the RCONFIG tool.

If you were following my blog, you might aware that we have a plan of implementing 16 node production RAC on HP UX Superdom Itanium servers using EMC DMX4 SAN storage. 

As part of production implementation, last week, I have configured initial 8 node RAC successfully and the immediate plans were to migrate the databases across operating systems (AIX to HP ux) and convert a single instance database to RAC. I have done this exercise on the development setup sucessfully, the only change this time is that I have used RCONFIG utility to convert single instance database to RAC. Earlier I have used DBCA method to do the same.

Oracle provides following methods to convert a single instance database to RAC:

Grid Control

DBCA

Manual

RCONFIG(from 10gR2)

I must say, each DBA may choose a different method according to his/her convineance to achieve the result. The following illustrates how I have migrated a database from AIX to HP UX and from a single instance to RAC (on 2 node).

Database and OS details

Source:

Database 10gR2 (10.2.0.4)

OS : AIX

non-ASM

Target:

Databae 10gR2 (10.2.0.4)

OS : HP UX Superdom Itaninum

ASM

RCONFIG brief

RCONFIG tool is introuced in Oracle 10gR2 and the main functionality of this tool is to convert single instance database to RAC. 

The tool(rconfig) can be found under $ORACLE_HOME/bin directry.

The xml input file (ConvertToRAC.xml) resides under $ORACLE_HOME/assistants/rconfig/sampleXMLs (it is recommended to copy the file before using it).

Log files(rconfig.log and etc) can be found under $ORACLE_HOME/cfgtoollogs/rconfig

The Convert verify option in the ConvertToRAC.xml file has three options:

• Convert verify="YES": rconfig performs checks to ensure that the prerequisites for single-instance to RAC conversion have been met before it starts conversion
• Convert verify="NO": rconfig does not perform prerequisite checks, and starts conversion
• Convert verify="ONLY" : rconfig only performs prerequisite checks; it does not start conversion after completing prerequisite checks 

Before you actually starts the convert process, you can run the rconfig with convert verify=ONLY option which basically check all the prerequisites. At the end, the result code either would be 0 or 1. Where 0 stands for operation success and 1 stand for operation failures and in case of failure, it will also gives you the reason of failure.

Following RMAN command converts the database from AIX to HP UX platform.

RMAN> convert database new database 'ORCL'

 transport script '/tmp_backup/tempdb/transport.sql'

 to platform 'HP-UX IA (64-bit)'

 db_file_name_convert

'+DG_ORCL/ORCL/datafile','/tmp_backup/tempdb';

Move all the converted datafiles to target machine and follow the ML NOTE: 414878.1  for further details on how to start the on target machine.

Following illustrate how to convert single instance database to RAC using the RCONFIG tool:

cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml convertdb.xml

modify the convertdb.xml file according to your environment. Following is the example:

==

convert_t24uat.xml 46 lines, 2964 characters 

 ?xml version=1.0 encoding=UTF-8?

 RConfig xmlns:n=http://www.oracle.com/rconfig

           xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

           xsi:schemaLocation=http://www.oracle.com/rconfig

    ConvertToRAC

  Verify does a precheck to ensure all pre-requisites are met, before the conversion is attempted. Allowable values are: YES|NO|O

NLY

        Convert verify=NO

 Specify current OracleHome of non-rac database for SourceDBHome

              SourceDBHome /u00/app/oracle/product/10.2.0/db_1 SourceDBHome

 Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome

              TargetDBHome/u00/app/oracle/product/10.2.0/db_1 TargetDBHome

 Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion

              SourceDBInfo SID=ORCL

                Credentials

                  User sys User

                  Password password Password

                  RolesysdbaRole

                Credentials

              SourceDBInfo

 ASMInfo element is required only if the current non-rac database uses ASM Storage

              ASMInfo SID=+ASM1

                Credentials

                  User sys User

                  Password passwordasm Password

                  RolesysdbaRole

                Credentials

              ASMInfo

 Specify the list of nodes that should have rac instances running. LocalNode should be the first node in this nodelist.

              NodeList

                Node name=rac01

                Node name=rac02

              NodeList

 Specify prefix for rac instances. It can be same as the instance name for non-rac database or different. The instance number will be attached to this prefix.

              InstancePrefix ORCL InstancePrefix

 !--Specify port for the listener to be configured for rac database.If port=, alistener existing on localhost will be used for rac

 database.The listener will be extended to all nodes in the nodelist --

              Listener port=""

 Specify the type of storage to be used by rac database. Allowable values are CFS|ASM. The non-rac database should have same storage type.

              SharedStorage type=ASM

 Specify Database Area Location to be configured for rac database.If this field is left empty, current storage will be used for rac database. For CFS, this field will have directory path.

                TargetDatabaseArea+DG_ORCL TargetDatabaseArea

 Specify Flash Recovery Area to be configured for rac database. If this field is left empty, current recovery area of non-rac dat

abase will be configured for rac database. If current database is not using recovery Area, the resulting rac database will not have

a recovery area.

         TargetFlashRecoveryArea +DG_ORCL Target FlashRecoveryArea

        SharedStorage

        Convert

     ConvertToRAC

 RConfig

===

Once you modify the convert.xml file according to your environment, use the following command to run the tool:

rconfig convertdb.xml

Oracle does the following during conversion:

oracle@usogp01: /u00/app/oracle/product/10.2.0/db_1/bin # ./rconfig convertdb.xml

Converting Database ORCL. to Cluster Database. Target Oracle Home : /u00/app/oracle/product/10.2.0/db_1.

Setting Data Files and Control Files

Adding Database Instances

Adding Redo Logs

Enabling threads for all Database Instances

Setting TEMP tablespace

Adding UNDO tablespaces

Adding Trace files

Setting Flash Recovery Area

Updating Oratab

Creating Password file(s)

Configuring Listeners

Configuring related CRS resources

Adding NetService entries

Starting Cluster Database

Starting Listeners

    

      

        

          Operation Succeeded

        

      

      

         /u00/app/oracle/product/10.2.0/db_1

      

        

          ORCL1<\SID>

          ORCL2<\SID>

        <\SIDList>    

    

  

oracle@usogp01: /u00/app/oracle/product/10.2.0/db_1/bin #

==

As you can see the exit code is 0, which indicates the success of the conversion process.

Uphills faced during the conversion process

The only error which I got during the conversion was missing listener.ora under ORACLE_HOME/network/admin directory as we had created listener under the ASM home (we have separate RDBMS and ASM homes). Problem has been resolved by creating the link under the RDBMS network admin directory of ASM listener.ora file.

We have contacted one of the developer of rconfig utiity to clarify the two changes (following) to make sure it will work:

non-ASM to ASM 

single instance to RAC.

He said that the main purpose of this utility is to convert single instance database to RAC and optinally few non-ASM of target database to ASM while converting to RAC. But, he is not sure about moving the entire non-ASM to ASM while conversion. As they don't have any test case. However, asked us to go ahed and try. Well, we were amazed that it worked for us and we delightly informed the developer that two changes are worked for us and you can have our reference.

Our next migration/conversion is our EBusiness suit and of course, I am going to share my expereince here.

References

http://download-west.oracle.com/docs/cd/B19306_01/install.102/b14201/cvrt2rac.htm#sthref1273

Important Meta Link Notes:

ML Note:387046.1 RCONFIG : Frequently Asked Questions

ML Note:371519.1 RCONFIG Error When Converting From Single Instance To RAC

ML Note:375472.1 RCONFIG FAILS with an null pointer EXCEPTION

ML Note:391297.1 RCONFIG fails when using ASM as Shared Storage

ML Note:388577.1 Using Oracle 10g Release 2 Real Application Clusters and

 Automatic Storage Management with Oracle E-Business Suite 

 Release 12

Happy Reading,

Jaffar

An interesting 5 day EMC SAN Storage Implementation course

When I reached the office this morning, I have been informed by my manager that my name has been suggested from the DBA team to attend the SAN Storage Implementation 5 day course and I must rush to the training center right away. It was a bit suprise news for me and I initally thought its not my cup of tea, but, still wanted to attend.

Today, we have just completed day 1 class in which all the basic stuff about SAN storage (specially EMC) has been discussed and I must say it is really worthy attending this training. It did really help me to clear some of the basic concepts which I was thinking about SAN Storage.

Now, I can talk about, what is a SAN Block Box (what does it consists of), what is a VLAN/VSAN, Fiber Channel, type of ports on a switch, how volume manager works, what is a LUN, port zoaning. prot channels, what is multipathing & it works, and etc. stuff. I am not sure how long I am going to keep this terminology in my memory, but, surely, this course going to give me bit of confidence to configure our RAC enviornment optimally.

I will be posting about the interesting stuff that learned in the class in coming days.

A final word about our storage. Our bank has brought EMC DMX4 SAN storage.

Happy reading,

Jaffar

Database migration across platforms and conversion of single instance to RAC

Working in a huge RAC environment, each passing day is a learning day for me. After setting up 4 node RAC on HP UX Itanium 2 Severs, the subsequent task/challenge is to migrate single instance databases (around 16) from AIX OS to HP OS and plus converting single instance to RAC.

I have started this task on a low confidence note, I would be lying if I say I was very much confident. However, as thing progress, my confidence level went high and at the end I have successfully migrated a database (single instance) from AIX OS to HP OS and then converted the single instance database to RAC database.

To give you a rough idea what I have done:

Converted database from AIX to HP using RMAN.
On HP, then, converted this databases from single-instance to RAC database. There are few ways using which you can achieve this. However, I have opted the DBCA option to execute the task.

Next step is to migrate and convert the rest of the databases using the same method.

Happy reading,

Jaffar

Oracle doesn't allow replacing ocr disk or adding the mirror copy if the new disk size is less than the existing ocr disk size

At least my self now decided not to believe in the ML Notes (not always though), its better testing what they have said. I was trying to replace an existing ocr location and also trying to add mirror copy of ocr. During RAC setup, we have used 1gb disks for OCR and voting disk (with single copy of each).
In order to multiplex the OCR and Voting disk we have requsted our UNXI team to gives us the four raw disks with 500mb each (oracle recommends 300MB for OCR/Voting Disk, may be less) and whil trying to replace/mirror the ocr to the new raw disks, I have got the following errors:

for OCR Mirror copy: PROT-22:Storage is too small
for replace OCR : PROT-16: Internal Error

As usaul, we search in the ML and found couple of notes for the same errors (317628.1 & 444757.1). Well, the ML Note : 317628.1 said the following:

" fails with "PROT-22: Storage too small" error. The problem is due to an Oracle bug where this operation requires the OCR mirror partition/file tobe larger than the original by up to 128MB. The bug has been fixed in the 10.2.0.2 patchset."

The note says that this bug has been fixed in 10.2.0.2 patch, we are on 10.2.0.3 patch and the bug still persists.

Happy reading,

Jaffar

A stupid and silly issue took hours to resolve

This morning we were trying to create a RAC database using the dbca utility on HP UX Itanium II servers and a silly/stupid error keep coming for a long time and we got strucked for good amount of time.

The error message which was coming 'Unable to copy the '/etc/oratab' to '/tmp/oratab.' and when ignore option was choosen from the error screen, the subsequent message was 'Encountered file error when copying listeners from home=$ORACLE_HOME/asm".

We certainly knew that the issue is either with the permission or copying the said file and our immediate step was checking the permisson and ownership on the oratab file. Everything was just fine and then we try to create a file in the /tmp directory using touch command from the Oracle user and it worked without any hassles.

We were able to create the RAC database successfully on a single node and when tried to add instance on the other node, the same issue encountered again and again.

We finally thought of running the dbca in tracing mode and when the same problem arises and when we had a look at the trace file, we couldn't stop wondering about the silly/stupid thing which Oracle was complaining.

The trace file reveals that it is looking for 'scp' executable in '/usr/local/bin' location, whereas, the 'scp' excutable was in '/usr/bin' directory. We simply created a link for 'scp' in the '/usr/local/bin' and dbca run smoothly and we were able to complete the RAC database creation.

Past three months, its been really a great working expeirence on RAC and I must say, we have faced hell a lot of issues while trying to implement RAC on HP Virtual Machines. Definately, its a great learning phase.

Guess what's next? MOVING 8 DEVELOPMENT DATABASE TO RAC from AIX to HP. Umm.. a good and taugh task ahead.

I will keep posting my experience and the issues that have faced while setting up our 16 node RAC with 8 databases.

Happy reading,

Jaffar

Happy Birthday To ME! I am 34 years old now!

I was just wondering how days, months and a year past my life . It looks like I recently blogged about my previous birthday and now I am blogging about my another birthday.

I am not one of those good people who do set some goals every year to achieve. I am bit lazy doing so. However, this time I do set couple of goals, like, achieving the OCM status and a very careful planning about financial savings(which I never think seriously before).

It was really a very fruitful last year where I did achieve something good in my life, like, given my ever first international Oracle seminars in Sweden & Denmark, spending more time working on RAC environments and brought a good house.

Unlike last year, no French Corner Cacke this time. No plans of celebration. Its like a normal day for me.

Thanks for passing by my blog.

Happy reading,

Jaffar

16 node RAC with 12 databases

Umm.. its been really a quite long time that I did update my blog. becauseI have left my previous company (bank) after over 4 fruitful years. I have joined a new upcoming bank where I have got an opportunity to setup a 16 node RAC on HPUX (development) with 12 databases plus grid computing setup.

Though the soft lunch of the bank would be in coming few months , we are too busy setting up the RAC and Grid environment. In process, we have just started with a setup of 8 node RAC for the development environment this evening and will be ending up adding 8 more nodes on HP-UX Superdom (Itanimum 2) servers using EMC storage.

It is going to be the RAC setup all the way, i.e., starting with development, testing, pre-production and finally to production environments. I am not sure whether any coompany has such sort setup here in Saudi Arabia and this is going to be very challenging to our DBA's team as well to the OS administrators.

The next challenging which lays after the RAC setup is moving/migrating stand alone databases from AIX OS to the RAC setup on HPUX os. Which required database conversion and then adding to RAC.

I am sure that it gonna be a great fun handling such setup. There is plan to fully utilize the Computing technology, most probably looking at the workload management, TAF, FAN and etc. Its is not yet clear that all the databases (probably 12) will have scalability across all the nodes or something else.

I will keep writing in my blog about the developments, issues we faced and other technical issues during this juroney.

Till then, have good time.

Jaffar

First ever Saudi Oracle User Group

Its been really quite a long wait for all Oracle techies (who are working in Saudi Arabia) to have a local Oracle User Group: that is, a Saudi Oracle User Group. Well, cherished dream became finally true on March 11 when Oracle had an formal meeting about starting an official Saudi Oracle User Group and explained their plans to run the group.

As per the press news, Dr. Majid Altuwaijri, CIO, National Guard Health Affairs will be the president of Saudi Oracle User Group.

Following is the excerpt from the press release:
=================
The autonomous group comprises 14 most distinguished CIO's from the various industry verticals in the Kingdom.

Apart from addressing member problems, the group will provide networking and collaboration opportunities to Oracle users in the Kingdom, enabling them to have a better understanding on specialist areas of Oracle software. The Saudi Oracle User Group will enable Oracle customers from around the Kingdom to share their firsthand experiences in using Oracle technology and solutions, and how to best leverage their IT investments.

'Though independent, the Saudi Oracle User Group will work closely with Oracle to offer constructive feedback and assist different customers with their crisis,' said Dr. Majid Altuwaijri, CIO, National Guard Health Affairs, and President of the Saudi Oracle User Group. 'The feedback will help Oracle determine long term customer requirements, improvise on product and solution offerings to help customers in the Kingdom have a global presence with groups around the world.'

Moving forward, the group will meet members to discuss the Charter to establish a set of milestones to be achieved in the short and long term. Once the Charter is defined, members will meet to define online objectives. The website will be crucial in sharing information with member and will boost memberships. It will also act as a medium of managing and publishing the group's presence. The group will also attend meetings at international and local Oracle User Groups, participate in IT exhibitions and third party forums.

Over the next 12 months, the group will meet on a quarterly basis to form special interest groups across industries. This initiative will allow groups to be more focused and interactive as it speaks the same language for a set of customers.
=========

I am very curious about joining this group and would really like to involve actively.

Jaffar

Oracle 10g Admin I - 5 day course.

Now-a-day I am busy delivering Oracle University 5 days courses at various places (oracle onsite and customer site). Last week I have delivered a 5 day course on 'Oracle 10g: programming with PL/SQL' and starting with today, I am teaching 'Oracle 10g: Admin I' here at Oracle faclities in Riyadh, Saudi Arabia. I always felt that teaching is a tough job, however, I am loving it.

Happy Reading,

Jaffar

Fun in teaching 'Oracle Programming with PL/SQL'

I am currently teaching a 5 day Oracle training course 'Oracle 10g: Programming with PL/SQL' and its been pleasure refreshing the basic concepts once again.

There were 12 atendees on the day 1 for this class, all of them have ample of programming experience in VB and .NET technologies. Though, not many serious questions have been asked on day 1, but, I loved teaching the Programming concepts.

Today day was the first day and still 4 days to go.

Happy Reading,

Jaffar

100,000 HITS!!!

This morning my blog got 100,000 hits and its glad to see the magical number (100,000) despite the fact that my blog is not so much familiar like other Oracle Gurus blog.


Regards,

Jaffar

Now my articles appears on SSWUG.org web site - WITHOUT MY PERMISSION!!

If I remember correctly, sometime back couple of people complained that their articles appeared / published on SSWUG.ORG web site without their knowledge and permissions.

Today, I was shocked and surprised to see couple of my articles (which were written on blog) appeared on this site. I don't remember that they have ever taken my permissions to publish at their site.

Following is the link of few articles published on their web site:

Simulating 11g Snapshot Standby Database feature on Oracle 10g?
http://www.sswug.org/see/37089

Change behavior of GROUP BY clause in 10g
http://www.sswug.org/see/34897


I don't mind publishing my articles on their web site if they would have taken my permission.

Happy Reading,

Jaffar

Ad - Looking for some consulting work (specially in Saudi Arabia)

Currently I am looking for any Oracle database consultancy work or any training offer here in Saudi Arabia (even out side Saudi Arabia).

Following are my specialized ares for consultancy work:

Database installation/configuration
RAC Setup
Database Migration
Data Guard/Physical Standby Setup (Disaster Recovery)
Performance Tuning
RMAN Policies and Setup

Following are my expertise for Oracle Technology Training:

Advanced RMAN concepts - 1 day
Automatic Storage Management (ASM) - 1 day
Data Guard - 2 days
Database Concepts

Feel free to contact me on sjaffarhussain@gmail.com for further enquirers.

Happy Reading,


Jaffar

ocrconfig 11g improvement

Starting in 11g, the ocrconfig command now supports manual backup option which enables you to perform ocr file backup at any time, rather than waiting for the default automatic backup (i.e. every 4 or more hrs) interval.

Using the ocrconfig -manualbackup option, you can perform the manual (binary) backup on demand.

The -showbackup option now includes auto and manual flags that you can optionally specify to view the auto/manual existing backup information.

http://download.oracle.com/docs/cd/B28359_01/rac.111/b28255/votocr.htm#i1012456


Happy Reading,

Jaffar

Wow! I am able to change my email ID on OTN forums.

It was a known fact that once you setup an email id on the OTN forums, it was not possible to change the email id again and many people were just surprised about this behaviour.

I don't know, how many of you aware that the changing of email id on OTN forums is possible now. I knew that many of you are least bothered about this, however, if anybody wants to change the email id, now you can do that easily and get rid off your legay email id.

How to change the profile information on OTN forums:

Once you log in on OTN forums, click 'Your Control Panel' option on right top corner which gives the facility to change the profile information, including your email id.

Happy Reading,

Jaffar

Complete Checklist for Manual Upgrades to 11gR1 - ML Note:429825.1

I am really not sure how many of you aware of the newly published Metalink Note about step-by-step manual upgrade to 11gR1.

Metalink Note : Note:429825.1. (latest revision on 05-MAR-2008)

Jaffar

Does ASM has controlfile?

SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
+asm

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE
                                                                     \CTL1+ASM.ORA

Probably we all know that ASM consist of an Instance, doesn't have database, i.e. physical structure. Therefore, the above definitely misleads.

According to the ASM book, the control_files parameters point to a dummy (nonexistent) file. This is the default internal value for this parameter and it is a meaningless value in the ASM instance.


Happy Reading,

Jaffar

Oracle Automatic Storage Management - Book Review

I have borrowed this book from my colleague to read during the week-end and at the same time, thought of writing a summary/review. When I told my idea, I knew that my family disliked this, specially during week-end.

The book started with the ‘Foreword’ page consists of ‘History of ASM’ headline which is written by Bill Bridge (an architect in Oracle over 18 yrs and the creator of ASM).

He revealed few interesting points about ASM history, like when did he get an Idea about ASM, how it can be useful with Oracle technology and the total time which took to build ASM technology.

Chapter 1 – Storage Stack Overview

This chapter starts explaining an overview about Disk drive technology, disk layouts, Disk Drive performance, different class of storage system architectures (such as SAN, NAS, DAS and SCSI) .

RAID levels also been discussed, and storage arrays concepts too.

Conclusion:

It might look interesting, but, I have skipped this chapter in order to read more about ASM concepts.


Chapter 2 – ASM Instances

This chapter starts on a note about ASM introduction, managing ASM (startup/shutdown) instances, few important ASM Instance parameters (such as instance_type, asm_diskstring & asm_diskgroups), ASM instance new background processes, V$ ASM views, ASM installation, upgrade and patching concepts.

It also talk about the significance of two ASM files, the ‘ab_.dat ‘ and the ‘hc_.dat’ that are present under $ORACLE_HOME/dbs directory.

These files are used by the RDBMS instances for env. Information and health check monitoring.

Note : Beware, these files should not be deleted.

Conclusion:

Nothing new has been offered in this chapter. This information is already available in the Oracle documents.

Chapter 3 – ASM disk

This chapter starts explaining about logical term LUN and explained about creating LUNs, LUN optimal sizes and what is the minimum disk size (4mb) and maximum (2 power 32 MB). Also, explained disks discovery(asm_disktring init parameter) and explained the meaning of disk header status (Former, Candidate, Member & Provisioned).

It also talks about the disk preparation/configuration on different OS, such as, Solaris, AIX and Windows and how to use the asmtool & asmtoolg utilities.

Conclusion:

Nothing new offered in this chapter as well.

Chapter 4 – Diskgroups and Failure Groups.

This chapter started explaining about ASM diskgroup concepts and why ASM diskgroups are differ from typical LVM (logical volume manager). Also discussed ASM diskgroups redundancy types (normal, high and external) and their practice.

In the next segment of this chapter, it talk about Creating Diskgroups, default ASM Disk Names & how to change default disk naming convention and Diskgroup Numbers. An interesting point was made, i.e., “you are not restricted to having two failure groups for normal redundancy and three for high redundancy”.

Couple of new feature of v11g ASM, ASM Preferred Read, Diskgroups Attributes and Fast Disk Resync have been discussed.

In the last segment of this chapter, it explained I/O concepts and I/O failures. The chapter finished explaining about the Space Management Views and important columns that need to consult for space management.

Conclusion:

This chapter gave some good inputs about I/O errors, 11g new features, ASM extent management , understanding of redundancy level and etc.

Chapter 5 – ASM Space Allocation and Rebalance

I particularly liked this chapter, it presented deeper insights about ASM Space allocation, operations, such as ASM AU (Allocation Units), ASM Extents distribution evenly among existing disks, how ASM Strips works, and difference between coarse and fine-grained striping types and which type of striping is good for what type of database files.

It also discussed when to change the default values of _asm_ausize (deault 1m) & _asm_stripesize (128k/1M) and how to set/change values for VLDs databases.

I also like the way author explained about ASM Rebalance operation concepts and the impact of setting various values to the ASM_POWER_LIMIT init parameters.

I really like the point “It is commonly believed that adding and dropping a disk against the same diskgroup required two separate rebalance activities. In fact, disks can be dropped and then added and a single rebalance command can be evenly distribute the I/O”. It shows how to avoid two rebalancing operations on this occasion.

A 11g new feature Fast Rebalancing which overhead of ASM-to-ASM and ASM-toRDBMS messaging also been discussed.

Conclusion:

I really enjoyed reading this chapter which gave me great insight of ASM extent mechanism, rebalancing and striping internals. It also provides few very useful scripts.

Chapter 6 – ASMLIB Concepts and Overview:

Since I have not worked much on Linux, I have quickly reviewed this chapter about ASMLIB API which is currently available on Linux OS.

This chapter explains the benefits of ASMLIB API, and the following functionality of ASMLIB:

• Device Discovery
• I/O processing
• Performance and reliability

Explained ASMLIB configuring, Managing disk in ASMLIB, Upgrading ASMLIB, Migrating to ASMLIB and troubleshooting ASMLIB.


Chapter 7 – ASM Files, Alias and Templates

This chapter explains about the default ASM Filenames, creating tablespaces, ASM Alias and Templates.

Conclusion:

Nothing new offer in this chapter.

Chapter 8 – ASM Command-Line

This chapter explained about the ASMCMD (unix based) utility and its usage. It starts explaining existing means to manage and access ASM and its components.

I really liked the following 11g new features:

Bad Block Remapping in ASMCMD. Starting with 11g, when ASM redundancy is used, it is possible to recover bad sot by restoring the data from their mirror copy using the ‘remap’ command.

Note : ‘remap’ don’t fix logical errors or corruptions detected by the RDBMS.

ASMCMD Copy:

Starting with 11g, moving datafiles in and out of ASM made easy with “copy” command introduced in ASM.

Conclusion:

11g couple of new features about ASMCMD utility worth to know.


Chapter 9 – Managing Databases in ASM

This chapter started on a note on how to use ASM while creating databases and covered some good points about the internal functionality of CSS (Cluster Synchronize Services) daemon/process, how RDBMS interacts with ASM, important background process (role of RBAL, ASMB & 00nn b/g), few vital parameters that influence ASM performance (like, processes + add 16, large_pool add additional 60k and shared_pool size additional memory to store extent maps and concluded with good examples of converting ASM no non-ASM and vice-versa.

Conclusion:

Explained well about CSS internal behavior, how RDBMS communicates with ASM instance, converting instances from ASM-to-Non ASM and vice versa.


Chapter 10 – ASM Persistent Data Structures

Stars explaining about MEATADATA and where does ASM stores the metadata, and different types of metadata. ASM has two main classes of metadata, physical and virtual metadata.

Physical metadata which located at fixed locations on disk contains the following:

• Disk Header
• Allocation Table(AT)
• Free Space Table (FST)
• Partnership Status Table (PST)

Virtual Metadata stored in ASM files and contains the following:

• File Directory
• Disk Directory
• Active Change Directory (ACD)
• Continuing Operations Directory (COD)
• Template Directory
• Alias Directory
• Attribute Directory
• Staleness Directory
• Staleness Registry

Chapter 11 – ASM Operations

This chapter is one of those chapters I liked in this book. It really explains how ASM works internally on the following condition:

• First time when RDBMS instance tries to access an ASM file.
• Operations on ASM Files
• File creation
• File open
• File input/output (I/O)
• File close
• File delete

It also talk about the variable sized extents and Striping types (such as Coarse and fine-grind), Resync, Rebalancing and ASM Instance Recovery & Crash Recovery concepts.

Conclusion:

This is one of interesting chapters present in this book which offers an internal mechanism of ASM functionality on the above mentioned operations.


On APPENDIX

Appendix A: explained about ASM and Storage Array Configurations.

Appendix B : Troubleshooting – In page, it discussed about ASM startup issues, Diskgroup mounting issues, Space related Issues and a walkthrough of an ASM alert log.

Appendix C: Scripts and Tips - Automatically Starting up and Shutting down ASM instance in Non-RAC env.

Migrating Individual Non-ASM datafiles to ASM.


Overall thoughts:

This book is recommended for someone who serious wants to study about ASM technology and its in & outs.

Having a copy of this book is better than scanning 100th of Oracle documents.

This book is summarized and explained all ASM contents to a great extent.

For me, I liked 4 chapters in this books and might need another read.

Happy Reading


Jaffar

Why should we apply 10.2.0.4 patch set?

At least for us, it is not really necessary to apply every latest Oracle patch set on our databases unless we really need them. After knowing about the release of 10.2.0.4 patch set from Laurent and Mr.Doug's blogs, I thought of reading a metalink note that describes the list of bugs/issues fixed by this patch set. (ML Note : 401436.1).

While reading the note, following bug fixes really caught my attention:

- SYSTEM RESET of an spfile parameter can corrupt the SPFILE
- /*append parallel*/ can corrupt an index

-deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS)

I have come across of many DBAs complaing about this behavior. Few links:

http://momendba.blogspot.com/2007/06/cursor-pin-s-wait-on-x-wait-event-in.html
http://www.dbaportal.eu/?q=node/49/print

I am really interested in the following bugs fix:

+ Higher CPU / Higher "cache buffer chains" latch gets / Higher "consistent gets" after truncate/Rebuild.

Jonathan Lewis and others have talked about this bug at their respective blogs

http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/


Few other bugs fixed in this patch set:
+ Wrong results with bind variables/CURSOR_SHARING
5079978 High US enqueue contention in RAC
5387030 Automatic tuning of undo_retention causes unusual extra space allocation
5439554 "buffer busy wait" timeouts with automatic undo management and in memory undo
5442919 Expired extents not being reused (ORA-30036)
5512921 Instance crash caused by SMON OERI[kcblus_1] / dump

It been really a long list of bugs fixed in this patch set. From my personal experience prospective, I would say, first study the bugs fixed in the patch set before applying on a production database. If all resolving majority of your issues.

Happy reading,

Jaffar

1Z0-048 Oracle Database 10g: Real Applications Clusters Administrator Certified Expert - CLEARED

Well, this morning I have passed the 1ZO-048 RAC expert exam.

Let me reveal about my preparations before my mail box gets filled by hundreds of mails asking me to provide documents and etc stuff. For me, Oracle RAC documentation, Oracle university (RAC course) material and of course the extensive production practice knowledge of RAC certainly helped me a lot to prepare and pass this exam. I am sorry, I can't offer anything more than this.

After the exam, a small thought comes into my mind. Since this exam comes under EXPERT category, I thought, it would have been more better doing a day practical lab exam than simply doing the multiple choice questioning exam.

Have nice time,

Jaffar