Kamran Agayev's Oracle Blog

There’re different ways to get the values of the parameter file using PL/SQL. One of them is using DBMS_UTILITY.GET_PARAMETER_VALUE function. In the following example, I print the value of the compatible parameter of the parameter file:

[sourcecode language=”css”] 

SQL>set serveroutput on;
 declare
    id number;
    str varchar2(40);
 begin
    id:=dbms_utility.get_parameter_value(‘compatible’,id,str);
    dbms_output.put_line(str);
 end;
 /
SQL>   10.2.0.2.0
PL/SQL procedure successfully completed. [/sourcecode]

  Porus Homi Havewala is a Principal Consultant with extensive experience in Oracle technology since 1994, including being a Senior Production DBA, Senior Database Consultant, Database Architect, E-Business Technical DBA, Development DBA, and Database Designer Modeller (using Oracle Designer of course). He has worked in Oracle India and is an enthusiast for Oracle technology, especially Grid Control and RMAN, on which he has conducted seminars for various large corporates and their management, and helped them to implement these powerful enterprise tools. Porus has worked for a number of years with the first production Grid Control site in the world, Telstra in Australia. He has published a number of articles on Grid Control and RMAN on OTN, and created the first blog in the world fully dedicated to Grid Control, showcasing podcasts exclusively based on this technology.

Could you please provide answer to the following questions as follows:

• Brief information about yourself and your family

I am a Parsi Zoroastrian, proudly born in India.  My ancestors migrated from Iran to tolerant India about 1200 years ago after the Arabic conquest of Pre-Islamic Zoroastrian Iran, in order to preserve our ancient religion of Zoroastrianism. We are a small and highly respected minority in India, in which we have been given full freedom of worship for the last 1200 years.  I am a happily married man, married to a fine lady of my own Parsi community.

• Your education

Bachelor of Science, Post-Graduate Diplomas in Computer languages, Two Oracle Education Masters (Designer and Developer) from Oracle Education Australia, 10g/11g OCP, RAC certified expert, Performance certified expert.

 

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

I started my computing career as a Turbo C (Borland) developer, then moved to dBASE the PC database as a way to fast develop applications. In the early 1990s, the invoicing, profit/loss, export documentation dBASE applications I had wriiten for a Japanese beef export company in Australia were straining under an increasing volume of data and users. I started to investigate the client-server RDBMS world, where I was very impressed with Oracle’s Multi-version Read Consistency and multi-platform support at the database level, and Oracle CASE (Computer Aided Software Engineering) 5.1 at the development and design level – CASE 5.1 was the forefather of Oracle Designer. No other database vendor had anything like Oracle CASE at that time, which was years ahead of its time. I was hooked and convinced my boss to go for Oracle. This was in 1993-1994.

 

• What was the motive behind to prefer Oracle? Who you have been influenced by?

Oracle’s leading edge technology, even then, even now. Oracle’s enthusiasm for technology.

 

• What would your preference of profession if not Oracle?

 A biomedical researcher into old age geriatics. But I didnt want to sacrifice animal lives in research.  So I entered the computer field.

• What motivates you in your job?

Oracle technology, and the desire to further it’s use in the world. For example, not many people know how Oracle Audit Vault can help them in their auditing requirements. Not many people know how powerful Oracle Enterprise Manager Grid Control is, and how useful it can be.

  

• Do you give lectures on Oracle?

 Yes, I have conducted a number of successful Oracle technical seminars in Singapore.

 In late 2008 as part of business development activities, I conceptualized and conducted an extremely popular Oracle workshop series based on Oracle Enterprise Manager Grid Control:  http://www.si-asia.com/marketing/gridcontrol/

 From July 2009 onwards, I conducted another series of extremely successful Oracle workshops every month jointly with Oracle. The first was on Oracle RAC with Enterprise Manager Grid Control, the second on Active Dataguard and other options such as Streams and Oracle Data Integrator, the third on Patch management using Grid Control, and the fourth on the Diagnostic and Tuning Packs:

http://www.si-asia.com/marketing/oracleworkshops2009/

From February 2010 onwards, I started to conduct another monthly series of Oracle workshops jointly with Oracle. The first was on Oracle Active Data Guard 11g with Enterprise Manager Grid Control and Oracle GoldenGate. Details of the latest workshops are on :

http://www.si-asia.com/marketing/oracleworkshops2010/  

• Have you authored any book in Oracle?

I am pleased to announce that my new book “Oracle Enterprise Manager Grid Control: Advanced OEM Techniques for the Real World” is getting ready to be published soon (Fall 2010) by Rampant Techpress.  Please do recommend to all your friends and colleagues, it is a great handy book if you really want to make best use of Oracle Enterprise Manager Grid Control.

I have based it on a lot of practical experience and I am sure it will be useful to a lot of you.

These are my Published Articles on The Oracle Technology Network (OTN): 

 Using Grid Control with Filer Snapshotting

Oracle Enterprise Manager Grid Control Architecture for Very Large Sites

Oracle RMAN Backups: Pushing the “Easy” Button

Patch a Thousand Databases, Using Oracle Enterprise Manager Grid Control

Easy Disaster Proof Production with Grid Control

Using Oracle GoldenGate for Real-Time Data Integration

 

In December 2009, the Oracle Technology Network (OTN) published the list of Most Popular OTN articles in 2009 and two of my articles on Enterprise Manager are in the list:

http://blogs.oracle.com/otn/2009/11/the_most_popular_articles_and.html

The OTN is the world’s largest online community of Developers, DBAs and Architects.

The two articles of mine in the top 10 are:

Oracle RMAN Backups: Pushing the “Easy” Button

Oracle Enterprise Manager Grid Control Architecture for Very Large Sites

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

 I read Oracle books ocassionally. I recently reviewed “Oracle SQL Developer 2.1″ by Oracle’s product manager, Sue Harper.

 

• What do you think on OTN forums?

 I try to help and advise, based on my knowledge and experience. Of course there are many other experts who know more than me.

• Do you refer to the documentation? And how often does it happen?

 Oracle 10g/11g Documentation is my bible. I refer to documentation whenever I want to verify a technical point.

• What is the biggest mistake you have ever made during your DBA career?

I stopped a production listener by mistake from another server. We password protected it after that after we realized the vulnerability.

 

• What was your greatest achivement as an Oracle DBA?

 In 2008 I was awarded the “Oracle ACE” title followed by the prestigious “Oracle ACE Director” title by Oracle Corporation USA. These titles are the FIRST such awarded in Singapore.

• What is you priority to manage the challenges you face?

Humanity first and foremost. A human being is the greatest thing on earth. There is a verse in in the Taittiriya Upanishad (Indian scripture) that says “Matru devo bhava, Pitru devo bhava, Acharya devo bhava, Atithi devo bhava” = One should treat their Mother, Father, Teacher and Guests as God.

• How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

Moderately successful – by God’s Grace.

 

• What are your best skills which make you differ from others?

Enthusiasm for technology and an interest in writing.

 

• What’s your major weakness?

At times, a lack of patience.

 

• Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

Everyone has that phase. Just think positive – tomorrow is a new day.

 

• What is the next success you would like to attain and your efforts to this end?

 Make the world a better place to live in.

 

• How do you balance your daily life with your career?

Life is life and career is career, draw a clear line.

 

• Please describe your one day summary of activities?

Work. Do something good. Help someone.

Learn. Learn something nice. Teach someone.

Eat simple. Think of God. Sleep.

 

• How many hours do you work and sleep in a day?

 I try to sleep at least 6 hours.

• Where and how do you spend your daily, weekly and annual holidays?

At home or shopping.

• Do you think about Oracle during vacations?  

Vacations are for thinking of my family.

• Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?  

Only walking, which was Mahatma Gandhi’s favourite exercise.

 

• What’s your favorite meal and non-alcoholic drink?

 Parsi dishes made from the fresh food in India is delicious.

Sugarcane and Mango juice – the sweetest is in India.

• What foreign languages do you know?

 English, Hindi, Gujarati (my mother tongue).

• What’s your average typing speed?

 I am a one-finger typist.

• Have you ever get involved in politics?

 No.

• What are your hobbies? 

 Blogging. Writing. Technical as well as spiritual topics.

The popular blog I maintain is http://enterprise-manager.blogspot.com with Oracle Press credentials, and has received more than 8,000 visits since 2008 from all over the world.

I am also the author of a Zoroastrian book, “Saga of the Aryans” (http://saga.zoroastrianism.com)

• How do you spend your free time?

Watch movies, Indian and English, also documentaries. Read Wikipedia.

• What’s your biggest ambition?  

To help humanity.

• What would be your advice to the beginners in Oracle?

 Have patience. You become a DBA only after years of experience.

• Would you like your children to follow in your footsteps or take a different path in life?

 First and foremost, they should never forget humanity.

• Do you have any followers of you?

 I have friends not followers.

• What is your vision on the future of Oracle?

 Oracle has an awesome future.

• Could you please take a photo in your office near to your desktop?

This is me conducting the most recent “Oracle Database Security” Seminar for Oracle and S&I Systems (Platinum  Partner) at the Sun Solution centre auditorium in Singapore.

Ittichai  Chammavanijakul has started working with Oracle database since 2000, he has been involved in about all aspects of Oracle database technologies including RAC, ASM, Data Guard and Streams. He has designed and implemented many different varieties of high available database environments using RAC on ASM and Veritas Cluster File System. He enjoys database and SQL tuning. Recently he has also been interested in web development using Oracle APEX.

In his current role as data warehouse architect with Motorola, he has architected and implemented many business-driven solutions using Oracle and other vendor products to meet critical business needs especially in warehousing area.

He is Oracle Certified Master, OCP (8i,9i,10g), CCNA, SCSA and MCP

Could u please provide answer to the following questions as follows:

• Brief information about yourself and your family

 I’m married to the most beautiful, charming, funny and talented woman on earth (who is also watching my screen as I’m typing this). We live in Chicago, Illinois with our daughter and a dog who thinks he’s a cat. 

• Your education

 I did my Bachelor Degree in Electrical Engineering with the emphasis on Telecommunications. My Master Degree is also in the same field with the same emphasis. 

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence? 

I started my career as a wireless engineer, but later switched into IT as a system administrator. Then one thing led to another and I got into Oracle. I’ve been working with Oracle since version 8.0. 

• What was the motive behind to prefer Oracle? Who you have been influenced by?

Whatever I say here will bore you to tears. I was interested in Oracle because it has the largest market share in RDBMS, and I found its technology to be very challenging. So I motivated myself to learn and become better at it. I’ve had fun along the way.  

• What would your preference of profession if not Oracle? 

  In the alternate universe, I would be a professional golf player. 

• What motivates you in your job?

I’m a troubleshooter by nature. I like to solve problems the way I do puzzles or riddles. And in this field, problem is something you never run out of. 

• Do you give lectures on Oracle?  

  I do internal workshops and trainings all the time.  

• Have you authored any book in Oracle?

  No, but I’d love to someday. 

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?  

Yes, definitely. Currently I’m reading the “Oracle PL/SQL Programming” by Steven Feuerstein. There are many tips and tricks I’ve never known before.  

• What do you think on OTN forums?  

It is interesting place where you can interact directly with Oracle experts. I believe what makes Oracle great is its community and contributions, especially in the OTN forums, from all Oracle technologists worldwide. 

• Do you refer to the documentation? And how often does it happen?  

All the time. I always go to tahiti.oracle.com for Oracle documentation if I would like to research for any topics. 

• What was your greatest achievement as an Oracle DBA?  

Besides the OCM certification, the fact that I’m still able to balance my technical and management roles at work is my personal achievement. 

• What is you priority to manage the challenges you face?  

Plan ahead as much as you can, but expect the unexpected. 

• How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

In addition to dedication and hard working, I love what I’m doing. That will drive you to accomplish anything. 

• What are your best skills which make you differ from others?

Seeing the big picture to connect the dots.  

• What’s your major weakness?

  I can’t say no to ice cream.  

• Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

Have I lost my spirit? I would say no. Occasional discouragement, perhaps. But doesn’t that happen to everybody? 

• What is the next success you would like to attain and your efforts to this end?

I’d like to find more time to blog more consistently. Ideas abound, but time is scarce. 

• How do you balance your daily life with your career?

It hasn’t been a challenge, really. I love my job and I enjoy goofing off with my family. I’ve been blessed to have a good balance between the two while making time for myself as well. 

• Please describe your one day summary of activities?

I work 8-10 hours a day with occasional breaks in between and lots of phone calls and (back-to-back) meetings. In the evening, I walk the dog, play with my daughter, and cook up something with my wife in the kitchen. We love to cook. 

• How many hours do you work and sleep in a day?

It is varied. Usually I work about 8-10 hours a day. I also sleep 8-10 hours a day.  

• Where and how do you spend your daily, weekly and annual holidays?  

At home, we relax in the kitchen a lot, cooking up new dishes. On weekends, my family and I like to visit a new restaurant to try their food. Each year, we make a point to travel to a new overseas destination. 

• Do you think about Oracle during vacations?

  Uh, heck, no.  

• Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?  

  I play golf. It relaxes me and helps increase my ability to focus at the same time. Golf is about figuring out creative solutions to problems. You practice a lot disciplines in this game. You compete with yourself because, unlike other sports, it has no referee or umpire. 

• What’s your favorite meal and non-alcoholic drink?  

I love Thai street foods: Khao Man Gai, Pad Thai, etc. I’m a fan of foie gras torchon, artisan cheeses and all those things, but the truth remains — the best foods in the world are made street-side and sold on carts. 

• What foreign languages do you know? 

English (foreign to me as my mother tongue is Thai). I also speak some Mandarin and read Attic Greek and Classical Hebrew. 

• What’s your average typing speed?  

 63 WPM last time I checked when playing SpongeBob SquarePants Typing game at Apple Store. 

• Have you ever get involved in politics? 

No. Never. 

• What are your hobbies?   

Golf, photography and reading.

• How do you spend your free time?

We’re a family of foodies, so we’re always going out to eat different types of food at different places. 

• What’s your biggest ambition?  

I’d like to start a nonprofit organization that deals with education for the underprivileged. 

• What would be your advice to the beginners in Oracle?

Read a lot especially Oracle documentations and/or blogs. Ask questions on forums or blogs. Test everything you read or you are told. Understanding basic concepts is the key. It is tempting to jump into more advanced concepts like RAC, ASM, etc. But without a solid foundation, it may be difficult to grasp the whole. 

• Would you like your children to follow in your footsteps or take a different path in life? 

I just want my daughter to be a happy person. That’s all I ever want for her in life. If happiness means she follows my footsteps, then so be it. 

• Do you have any followers of you?  

You mean stalkers?  

• What is your vision on the future of Oracle? 

Oracle will continue to expand its product portfolios by acquiring more companies. Oracle solutions will be more complete covering more aspects of technologies. 

• Could you please take a photo in your office near to your desktop?  

Unfortunately, this is against the company’s policy to take picture in the office.

In this video tutorial I perform a disaster recovery of the database using two virtual machine with RMAN. Enjoy it

To download the .mp4 version of this file, use the following link

http://www.kamranagayev.com/Video_Tutorials/Disaster_Recovery.mp4

Today I want to show you the demonstration of the article that was posted before on “Corruption data block and performing block media recovery”. I’ll try to prepare series of video tutorials on different Backup and Recovery scenarios using RMAN which is covered in my book – “Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump”

This video tutorial explains the manual block corruption techniques both on Linux and Windows (which shouldn’t be tested on production database!) and performing Block Media Recovery with RMAN. Enjoy it!

To download the .mp4 format of this video, use the following link:

http://www.kamranagayev.com/Video_Tutorials/Block_Media_Recovery.mp4

It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups. Look at the following demonstration. Here I:

– Create a new user and a table in that schema
– Take OS backup (hot backup) of the users01.dbf where the table resides
– Corrupt the data in that table and get block corruption error (Don’t wish you to get such errors in your production database! )
– Connect with RMAN and try to use BLOCKRECOVER command. As we haven’t any backup, we get an error
– Catalog the “hot backup” to the RMAN repository
– Use BLOCKRECOVER command and recover the corrupted data block using cataloged “hot backup” of the datafile
– Query the table and get the data back!

Here is the scenario

[sourcecode language=”css”]

SQL> CREATE USER usr IDENTIFIED BY usr;
User created.

SQL> GRANT DBA TO usr;
Grant succeeded.

SQL> CONN usr/usr
Connected.
SQL> CREATE TABLE tbl_corrupt_test (id NUMBER);
Table created.

SQL> INSERT INTO tbl_corrupt_test VALUES(1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> COLUMN segment_name FORMAT a45

SQL> SELECT segment_name, tablespace_name from dba_segments WHERE segment_name=’TBL_CORRUPT_TEST’;

SEGMENT_NAME TABLESPACE_NAME
——————————————— ——————————
TBL_CORRUPT_TEST USERS

SQL> COLUMN segment_name FORMAT a15
SQL> COLUMN tablespace_name FORMAT a15
SQL> COLUMN name FORMAT a45

SQL> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b
WHERE a.header_file=b.file# AND a.segment_name=’TBL_CORRUPT_TEST’;
SEGMENT_NAME TABLESPACE_NAME NAME
————— ————— ———————————–
TBL_CORRUPT_TEST USERS /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf
SQL> ALTER TABLESPACE users BEGIN BACKUP;

Tablespace altered.

SQL> host cp /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf

SQL> ALTER TABLESPACE users END BACKUP;

Tablespace altered.

SQL> SELECT header_block FROM dba_segments WHERE segment_name=’TBL_CORRUPT_TEST’;

HEADER_BLOCK
————
59

[oracle@localhost admin]$ dd of=/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf bs=8192 conv=notrunc seek=60 <<EOF
> corruption
> EOF
0+1 records in
0+1 records out

[oracle@localhost admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Mar 9 03:35:32 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> CONN usr/usr
Connected.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT * FROM tbl_corrupt_test;
SELECT * FROM tbl_corrupt_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 60)
ORA-01110: data file 4:
‘/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf’
SQL> EXIT

[oracle@localhost admin]$ rman target sys

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Mar 9 03:35:58 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:
connected to target database: NEWDB (DBID=2953562798)

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 60;

Starting blockrecover at 09-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 03/09/2010 03:36:13
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN> CATALOG DATAFILECOPY ‘/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf’;

cataloged datafile copy
datafile copy filename=/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf recid=1 stamp=713158624

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 60;

Starting blockrecover at 09-MAR-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 09-MAR-10

RMAN> EXIT

Recovery Manager complete.
[oracle@localhost admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Mar 9 03:37:28 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONN usr/usr
Connected.
SQL> SELECT * FROM tbl_corrupt_test;

ID
———-
1

SQL>

[/sourcecode]

Sometimes, in order to test the RMAN’s  BLOCKRECOVER command, we need to corrupt the specific data block and recover it for testing purpose. To do it in Linux, use dd command. In the following example, let’s create a table and corrupt it manually (Don’t try it on the production database or you’ll be retired from the job )

SQL> CREATE TABLE corruption_test (id NUMBER);
Table created.

SQL> INSERT INTO corruption_test VALUES(1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM corruption_test;
        ID
———-
         1

SQL> SELECT header_block FROM dba_segments WHERE segment_name=’CORRUPTION_TEST’;
HEADER_BLOCK
————
          67

[oracle@localhost ~]$ dd of=/u02/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=68 << EOF
> testing corruption
> EOF
0+1 records in
0+1 records out

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SQL> SELECT * FROM corruption_test;
select * from corruption_test
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 68)
ORA-01110: data file 4: ‘/u02/oradata/orcl/users01.dbf’
SQL>

Now connect to the RMAN and recover the data block (not the whole datafile or database) using BLOCKRECOVER command as follows:

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;

Starting blockrecover at 01-MAR-10

<… output trimmed … >
<… output trimmed … >

Finished blockrecover at 01-MAR-10

RMAN> exit

Connect to SQL*Plus and query the table:
SQL> SELECT * FROM corruption_test;

        ID
———-
         1

SQL>

Hemant K Chitale has 17 years of experience as an Oracle DBA. He has been a DBA on a wide variety of Unix platforms (some of them extinct now), Linux and Windows, from V6 to 10g. His career has spanned organisations from the Financial Services Industry to Manufacturing to Consulting. He has been a guide to junior DBAs and is in the habit of writing notes and case studies on technical issues in Oracle Database Administration. Portions of his spare time are spent on Oracle forums and on testing features of the Database. Database Performance and Backup and Recovery are his areas of interest. However, he does also have other interests in Non Fiction books and Serious Movies. “To learn and share knowledge” is a constant driver..

Could u please provide answer to the following questions as follows:

• Brief information about yourself and your family

I have a wife and a 14 year old son in Secondary School.

• Your education

My formal degree is in Finance.  However, I have done a Post  Graduate Diploma in Software Technology after I began working in I.T.

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

I began with Oracle5 on DOS and Xenix.   When I joined Stock Holding Corporation of India Ltd, I began in Securities Trading Market Operations.  However, the systems we were using intrigued me.  The architecture was ahead of it’s time : Distributed Databases with Oracle5 on DOS sharing data with a “central” Oracle5 database on a Xenix server.

I moved to the I.T. department after my first year and soon started working on the Database sometime in 1991.

• What was the motive behind to prefer Oracle? Who you have been influenced by?

Other than Oracle, the only “Database” I had seen was dBASE-III+  !  I liked Oracle for it’s multi-user support in V5.  V6 and V7 introduced Rollback Segments, Redo Logs, Row Locking (the “Transaction Processing Option”), PLSQL and I was hooked.

Years ago, before Oracle books came out, I read Steve Adam’s postings on newsgroups.   Then, later, Cary Millsap’s book “Optimizing Oracle Performance” Jonathan Lewis’s book “The Cost Based Optimizer”.

• What would your preference of profession if not Oracle?

Teaching.  I like sharing knowledge.

• What motivates you in your job?

The opportunities to “discover” different facets of databases and application design.  Some implementations really leave you with “awe” while others are “shocking”.  It’s always something new, exciting, challenging or disappointing or frustrating.  It is much more intellectual than Finance.

• Do you give lectures on Oracle?

When I have had the occasion to.  I have conducted a 5 day DBA-I course once and a course in Advanced Replication (both in the 8i days).   Besides these, I have, on occasion, conducted 2-4 hour sessions.

• Have you authored any book in Oracle?

Unfortunately, no.  I wish to and intend to.

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

I read non-Oracle books as well !  See my recommendations page http://web.singnet.com.sg/~hkchital/Recommendations.htm

The most recent Oracle books that I have been reading have been “Refactoring SQL Applications”  (ok, not strictly Oracle-only) by Stephane Faroult and “Advanced SQL Functions in Oracle 10g” by Richard Walsh Earp and Sikha Saha Bagui.

However, I am currently reading “The Professional” by Subroto Bagchi and “Fool’s Gold” by Gillian Tett.

• What do you think on OTN forums?

A very useful platform, still underused.  There are many people I know that should be on OTN forums but, for whatever reasons that I cannot fathom, are averse.  I guess you have to have a certain attitude before you open up or spend time on forums.  This is an infinitely better way to spend your time than something silly as Facebook.

• Do you refer to the documentation? And how often does it happen?

Very frequently.   Most often the SQL Reference (because I don’t  — and do not intend to – memorise syntax.  Syntax has to be understood rather than memorized). Also, the RMAN Reference (known as the “Backup and Recovery Reference”) and the Database Reference.

• What is the biggest mistake you have ever made during your DBA career?

There was this time when I deleted an Online Redo Log when re-organising Logs. Fortunately, it wasn’t a CURRENT file but an INACTIVE one, so there was no harm done.  I could clear that quickly enough but did also bounce the database instance “to be safe”.

• What was your greatest achivement as an Oracle DBA?

 Single-greatest ?  Actually multiple great ones.  Cross-Platform Migration from V6 to V7 in a short time-frame was one – although most DBAs would laugh at this now, back in those days it was my first Upgrade + Cross Platform + Cross Location migration.  Another one was implementing Oracle OPS in V7.   Building a Standby Database in V7 (DataGuard was a decade in the future !).  There have been a few Oracle Ebusiness Suite (10.7 and R11) upgrades that have been significant achievements.  As also storage-based D.R. implementations.

• What is you priority to manage the challenges you face?

 Staying cool.  I can handle technology failures.  People’s unwillingness  to learn and/or share knowledge, particularly when it is needed, is something which makes me despair.  What makes me angry pushing a “never-live” project and continuing it as if it were a success.  I have seen instances of refusal to acknowledge that corrective action is required and that responsibility must be owned.

• How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

I wouldn’t say that I am successful yet !  I am trying to do a good job but am not near achieving my potential.

• What are your best skills which make you differ from others?

Understanding the technology

• What’s your major weakness?

Not understanding people – what motivates one person but not another, why does a person or a group of people behave in a certain manner ?  Humans just are weird !

• Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

Yes.  Leading to depression.  The best person to talk to is your wife.  The next best person is someone outside the organization you work for.  However, fortunately, I have had good supervisors throughout my career, only that I have approached them too late.

• What is the next success you would like to attain and your efforts to this end?

Ah!  That would be telling. 

• How do you balance your daily life with your career?

“Work-Life balance” is an  empty phrase.  What you really need to do is to be happy – whether at work or at home or on OTN forums  (or, I wouldn’t say this about myself, playing computer games !)

• Please describe your one day summary of activities?

Get up.   Read the newspaper.  Get ready for work.  Commute to work.  Do some work.  Return home.  Watch Television.  Spend time on the Internet (forums, lists, news etc).

Work should not be the most important part of the day – everything is equally important.

• How many hours do you work and sleep in a day?

 4 to 6.  Sometimes I wake up in the night and can’t go back to sleep. 

• Where and how do you spend your daily, weekly and annual holidays?

 Annual holidays in India with my parents, brothers and in-laws.  An additional visit to some other neighbouring country in the region, about once in two years.

• Do you think about Oracle during vacations?

Only on rare occasions.

• Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?

No.

• What’s your favorite meal and non-alcoholic drink?

Indian Vegeterian  (which covers a whole gamut of different foods, believe me !).  Tea.

• What foreign languages do you know?

Foreign to which country ?  Hindi, Marathi and Gujarati are Indian languages that are Foreign outside of India !

• What’s your average typing speed?

I have a certified speed of 40 wpm on manual typewriters from 1981.   Computer keyboards nowadays are different from those typewriters but I have heard people say that I am “fast on the keyboard”.

• Have you ever get involved in politics?

No.

• What are your hobbies? 

Reading.  Non-fiction – economics, history, politics, science/technology and Oracle – besides a bit of  crime / detective fiction.

• How do you spend your free time?

Television.  Crime Channels,  Knowledge Programs, News Programs and a few family programmes.

Reading. 

On Internet Forums and Email lists.

Internet News.

• What’s your biggest ambition?

I am not known to be ambitious. 

• What would be your advice to the beginners in Oracle?

Read the documentation, practice Oracle, learn, keep an open mind.

• Would you like your children to follow in your footsteps or take a different path in life?

My son will NOT be an Oracle DBA.  That is 100% certain !  He knows what the job requires.

• Do you have any followers of you?

Define “followers”.  I do not like any possible definition of the word.

• What is your vision on the future of Oracle?

Cheaper licencing for the Database products.

• Could you please take a photo in your office near to your desktop?

That’s not possible.

–          Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo

–          Undo records are used to L:

• Rollback transactions when a ROLLBACK statement is issued
• Recover the databsae
• Provide read consistency

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

–          In automatic undo management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions

–          When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.

Undo Retention

–          After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

–          When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Dataabase attempts to retain old undo information before overwriting it. Old (commiteed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.

–          Oracle database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the databsae begins to overwrite expired undo. If the undo tbalespace has no space for new transactions after all expired undo is overwritten, the databsae may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

–          To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed, the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

–          You enable retention guarantee by specifying the RETENTIN GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement.

–          Oracle database assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view. Relative file number uniquely identifieds a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number.

–          When starting an instance, the DB_FILES parameter specifies the maximum number of database files that can be opened for this database. Default value is 200

–          If several disk drives are available to store the databsae, consider placing potentially contending datafiles on separate disks. This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time

–          Datafiles should not be stored on the same disk drive that stores the databsae redo log files. if the datafiles and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures.

–          To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column

–          To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses

–          Clauses of the ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace.

ALTER TABLESPACE tbs_test DATAFILE [ONLINE | OFFLINE]

To rename and relocate datafiles use the following steps:

·         Take the tablespace that contains the datafiles offline:

ALTER TABLESAPCE users OFFLINE NORMAL;

·         Rename the datafiles using the operating system

·         Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database

ALTER TABLESPACE users RENAME DATAFILE ‘file1’ TO ‘file2’;

·         Backup the database

 

–          To drop the datafile, the database must be open and the file must be empty. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile. You can’t drop the first or only datafile in a tablespace, or datafiles in a read only tablespace or SYSTEM tablespace.

–          If you want to configure the database to use checksums to verify data blocks, set the initialization parameter DB_BLOCK_CHECKSUM to TRUE. This causes the DBWn process and the direct loader to calculate a checksum for each block and to store the checksum in the block header when writing the block to disk.

The checksum is verified when the block is read, but only if DB_BLOCK_CHECKSUM is TRUE and the last write of the block stored a checksum. If corruption is detected, the database returns message ORA-01578 and writes information about the corruption to the alert log