How to become an Oracle DBA?

How to become an Oracle DBA?

Lesson 01

This is where I started 7 years back, and sharing my thoughts from few years. 7 years back, database administrator is very less known and when I started working out to be a Database Administrator, I opted to take a course from Oracle University affiliated SQL * International, of course its costly I felt its better and it worked for me. At that time, I used to browse for online documents, used to find very less. These days we have more documents on line, Oracle Forums for new aspirants, so many websites offer lot.

As a starter you have plenty of options, if you are a self starter you can browse Oracle Documentation on line and you can start learning.
http://www.oracle.com/pls/db102/homepage

In this post and upcoming posts I will discuss the topics to concentrate.
1. Get familiar with the Oracle Database Users and their responsibilities, it will help you to understand the roles and responsibilities and of course that you want to concentrate to become a Database administrator.

You can find more information on http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#sthref57

It will give you a basic understanding of database administrator. It’s the stepping stone. I don’t want to talk much about SQL, PL/SQL. Assuming that you already aware of this. Basics is well to go with, you don’t need to be a mater in PL/SQL.

2. What type of activities does Database administrator do?
Database Administrators as so many responsibilities which he/she needs to deliver. So get to know what the tasks of a DBA.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#i1006205
Going with the above, you might get to know what a DBA can do and you might be prepared to get ready to learn Oracle Database.

3. How to go-ahead after this?
Download Oracle Database 10gR2 for your operating system; initially you can use your Windows XP Professional where you can install Oracle Database Software easily. If you have good exposure on Linux, well I suggest you to try working out.

On Windows

http://www.oracle.com/technology/obe/10gr2_db_single/install/wininst/wininst_otn.htm

On Linux
http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html
This gives you a good start, and of course hands on experience which is must for a DBA. So keep working I will come up with my next post on what to do once you have Oracle Database on your Laptop/Desktop/Workstation.

Screening and Interview Questions for DBAs

I stopped by a BLOG “Screening and Interview Questions for DBAs” which shows good point in screening a DBA.

orainstRoot and root.sh

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?

orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.

Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

orainstRoot.sh

[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory to 770.

Changing groupname of /u01/app/oraInventory to dba.

The execution of the script is complete

root.sh

[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

OCR - Backups: Oracle Real Application Clusters

1. Recent Copies of OCR Backups?
acmrac2-> ocrconfig -showbackup
rac1 2007/06/26 06:33:12 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/26 02:33:12 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/25 22:33:11 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/25 15:09:34 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/17 17:56:06 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
2. You can use OCRDUMP to review the contents of the backup.
$ ocrdump -backupfile file_name # file_name is the name of the backup file.
3. What is the frequency of OCR backups automatically done by Oracle Clusteware?
Oracle Clusterware automatically creates OCR backups every 4 hours, each full day and at the end of each week.
More Info on OCR administration:
Refer Administering Oracle Clusterware components.

Oracle Applications 11i FAQ's

1. Why does 11i have an 8.0.6 and an 8.1.6 ORACLE_HOME?
Release 11i requires an Oracle8i (v8.1.6) database which is installed in the 8.1.6 ORACLE_HOME. The 8.0.6 ORACLE_HOME is required to host several other components of the Release 11i technology stack (such as Developer 6i and WebDB 2.2). The applications run in server partitioned mode, where the code is compiled and linked with the 8.0.6 ORACLE_HOME.

Oracle RAC Interview Questions & some useful Information

1)What is Oracle Clusterware?
i) Oracle clusterware is portable cluster software that allows clustering of single servers so that they cooperate as a single system.
ii) Oracle Clusterware also provides the required infrastructure for Oracle Real Application clusters (RAC).
Oracle clusterware enables the protection of any Oracle application or any other kind of application within a cluster.
iii) Oracle clusterware provides the level of high availability that is commonly known to be achievable for nearly all kind of applications and databases managed in cluster environments, like Oracle single Instance databases, oracle application servers, oracle enterprise manager components, third party databases or any other kind of application.
2. How to check if the database is RAC enabled?
3. Will the CRS services hang if CRS Home is full?
4. How will you convert single instance to RAC?
5. How will you tune interinstance communication?
http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/monitor.htm#CFAGAAGD
6. Explain how will you add nodes to the existing cluster?
http://download.oracle.com/docs/cd/B19306_01/rac.102/b28759/addnodes2.htm#CHDFBGFJ

i) Configure the operating system and hardware for the new node.
ii) Add the node to the cluster
You can do this by running $ORACLE_CRSHOME/oui/bin/ addNode.sh
iii) Add the rac software to the new node $ORACLE_HOME/oui/bin/addNode.sh
iv) Reconfigure listeners for the new node
Run -> $netca
v) Add new Instance,
run -> $dbca

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

7. How will you shutdown everything in a Cluster in a single command?

root@prmord1p # /etc/init.d/init.crs stop
Shutting down Oracle Cluster Ready Services (CRS):
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
root@prmord1p # ps -ef grep crs
root 29640 27940 0 15:14:16 pts/4 0:00 grep crs
root 12595 1 0 Jun 20 ? 0:00 /usr/sbin/vxnotify -g oracrsdg
root 29227 1 0 15:13:41 ? 0:00 /bin/sh /etc/init.d/init.crsd run
*************** And to start ***********************
root@prmord1p # clear
root@prmord1p # /etc/init.d/init.crs start
Startup will be queued to init within 30 seconds.

------------------------------------------------------------------------------------------------
8. Explain what is VIP?
9. Describe the difference in Architecture of 9i and 10g RAC?

What would be the first thing you would do if an end user complains that performance is poor?

I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.
As for the answer to the question, one should touch upon all these points:
- Check alert log, for any errors and UN even entries
- Check for archive log destination/file system full
- Check for database locks # Click here for a notes on locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates as been done from the Application team.

And few more from Oracle Online Documentation as well
1) Identifying and Gathering Data on Resource-Intensive SQL # Oracle Doc
2) Oracle9i Database Performance Tuning Guide and Reference # Oracle Doc
I recommend reading the documents from Oracle, which give clear understanding of all the above. Please add lines if you I miss something which is useful.

Oracle Interview Questions: Level 1

Most often asked:

About Interview Questions on Oracle.

http://get-best.com/IT_Interview_Questions/dbaQA1.htm

http://get-best.com/IT_Interview_Questions/dbaQA2.htm

http://get-best.com/IT_Interview_Questions/dbaQA3.htm

http://get-best.com/IT_Interview_Questions/dbaQA4.htm


For more Technical Questions

http://blogs.ittoolbox.com/database/solutions/archives/006260.asp


Click the above link