Friday, November 03, 2006

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning.

Scenario:
  1. Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also.
  2. Just setting the ORACLE_HOME, LD_LIBRARY_PATH, ORACLE_SID, PATH in the user's profile did not let him run the "sqlplus" command at all. The error shown was :
  3. $ sqlplus
    ld.so.1: sqlplus: fatal: /app/oracle/product/10.2/lib/libsqlplus.so: Permission
    denied
    Killed
  4. The files under "lib" directory under home didn't have permission for anybody other than "oracle" user.
  5. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/libsqlplus.so
  6. When we permit the other users (other than oracle) to read/execute on the lib directory (chmod -R o+rx $ORACLE_HOME/lib), the error changed.
  7. $ sqlplus
    Error 6 initializing SQL*Plus
    Message file sp1.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
  8. Now, sqlplus is looking for sp1us.lang file under $ORACLE_HOME/sqlplus/mesg directory as per the information available on Oracle forums. Hence, the error. I allowed the others to "rx" here too.
  9. The error changed again :
  10. $ sqlplus
    SP2-1503: Unable to initialize Oracle call interface
    SP2-0152: ORACLE may not be functioning properly
  11. The actual procedure followed was to allow "rx" on all directories after "sqlplus" & then removing "rx" privilege one-by-one from each unimportant directory & testing sqlplus command at each step, from another session.
  12. I used a little script to change all the directory permissions interactively, only if I want to (by looking at the name & thinking as an Oracle DBA ;-) ) and ran this from inside ORACLE_HOME .
  13. $ for i in `ls $ORACLE_HOME`;
    do
    echo "Dir or file : $i";
    echo "change ?";
    read ans;
    if [ $ans -eq '1' ];
    then chmod -R o-rx $i;
    else echo "Didn't change";
    fi;
    done
  14. The new information I discovered was on allowing "rx" to "oracore" directory in ORACLE_HOME, "sqlplus" started working. There's a mesg directory there too, which none of the errors pointed to :-( !

Conclusion :
  1. For any normal user (not a part of "oinstall" / "dba" groups ) to be able to run sqlplus and access an ORACLE database , read/execute permissions are required for these 4 directories :
    1. $ORACLE_HOME/bin
    2. $ORACLE_HOME/lib
    3. $ORACLE_HOME/oracore
    4. $ORACLE_HOME/sqlplus
  2. In addition, these 4 parameters should also be set in the user's environment (.profile) :
    1. ORACLE_HOME,
    2. LD_LIBRARY_PATH,
    3. ORACLE_SID,
    4. PATH

Tuesday, September 26, 2006

Oracle DBA Interview process

As a standard practice, the DBA selection process is split into 3 phases :

  1. Screening of resumes & filtering out the suitable candidates that can be interviewed.
  2. Scheduling & completing the interviews (rating & grading also go hand-in-hand during interview).
  3. Final rating of candidates & decision making.
The above procedures are described in detail below :

Screening of resumes & filtering out the suitable candidates that can be interviewed :

This is one of the relatively simpler stages.
  • When we receive resumes for DBA interviews, the first step is to skim through it & get a general idea about the layout, tidiness, clutter (if any) of the resume.
  • However, care needs to be taken to not reject a resume due to an unnecessary detail mentioned. The candidate might still be worth a trial.


Scheduling & completing the interviews (rating & grading also go hand-in-hand during interview) :
  • If possible, have multiple DBA's design and take the interview.
  • Use the rating chart (illustrated in next section) during the interview also. This will provide rough figures.
  • After the interview, consulting other DBA's who were present during interview, finalize the ratings.
  • We describe below a set of interview questions that we use progressively, to rate candidates on DBA track.
  • The standard first question that we ask to get the candidate comfortable & be able to exhibit his best, is to invite him to introduce himself as a DBA throughout his carrier since he / she started working.

Preliminary screening questions : These questions should be used to filter out candidates at an initial stage by testing the conceptual knowledge & smaller details about database administration. If one passes 70% of a chosen subset of preliminary questions, move to intermediate test. Same law applies to next section too. If preliminary passing is below 70%, pick only 2 questions from each next phase & seek answers.

  • What is tnsping ? Can you use tnsping to confirm the database status ? Always ?
  • What are the database startup phases ? What's importance of each phase ?
  • How to monitor a log file that is continuously updated ? How do you monitor databases ?
  • How do you go about tuning a SQL statement ? What is Explain plan & what is it's use ?
  • Types of backups ? Difference between hot, cold, logical backups ?
  • Difference between data block, extent & segment ? What is meant by PCTFREE & PCTUSED ?
  • Difference between truncate & delete ? Difference between CHAR(10) / VARCHAR2(10) ?
  • Unix (if worked) : How to see hidden files ? How to search specific text in unknown files ?
  • What's the significance of listener configuration file on a client ?

Intermediate-knowledge test :

  • Difference between gv$, v$, v_$ & x$ objects ?
  • How does column cardinality affect your choice of indexes during DB design ?
  • Statspack (if worked) : What are the levels of snapshots ? How to install & deinstall Statspack ? How would you use it for clustered environment ?
  • DB cloning procedure ?
  • How'd you recreate a parent table without affecting the child objects ?
  • Import - 40 GB & Undo tablespace - 20 / 30 GB - how to import ? How to improve import performance ?
  • RMAN (if worked) : Recovery procedure ?
  • How to find Oracle background processes in Windows ?

Advanced database administration questions :
We call these as "super questions" - which are the most tricky, with maximum chances of error. With clarity of concepts and enough experience, one can answer these.

  • How to sync up the OEM with agent ?
  • Significant requirements / points of complete / incomplete recovery ?
  • Import - multiple times ? What should be the strategy ?
  • What should be the backup strategy of a large database - in TB's ?
  • We have a set of datafiles without controlfile & without DB-name on a disc. How to recover the database ?
  • RAC setup requirements & procedure ?
  • How to grant read-only access to v$session ?


Answer guidelines to above Oracle DBA interview questions :

Preliminary screening questions :

  • What is tnsping ? Can you use tnsping to confirm the database status ? Always ?
  • A. Oracle utility to verify the status of a DB listener. No, we can never use - only listener can be verified.
  • What are the database startup phases ? What's importance of each phase ?
  • A. Instance start (SGA allocation based on init parameters), Mount (read control file & locate database files), open (open all the files in read-write mode - or read-only if a tablespace is read-only)
  • How to monitor a log file that is continuously updated ? How do you monitor databases ?
  • A. Write a script, schedule in cron for frequent intervals. Script can have a temporary copy of log & compare (diff) it with current log at every run, if there's difference, REPORT to DBA & replace old temporary copy with current copy.
  • A. OEM / Unix scripts / manually
  • How do you go about tuning a SQL statement ? What is Explain plan & what is it's use ?
  • A. Look at syntax, explain plan, index availability & usage, full table scans, etc. . Explain plan is an Oracle feature to see & analyze the detailed execution path of any SQL statement.
  • Types of backups ? Difference between hot, cold, logical backups ? How do you shutdown DB for logical backup ?
  • A. Hot / online - DB isn't shutdown (tablespace begin / end backup is used); Logical - export (no shutdown) , Cold - shutdown & backup files.
  • Difference between data block, extent & segment ? What is meant by PCTFREE & PCTUSED ?
  • A. Data block - smallest unit of allocation in database; extent - group of data blocks that can be allocated to an object; segment - group of extents belonging to a specific database object.
  • Difference between truncate & delete ? Difference between CHAR(10) / VARCHAR2(10) ?
  • A. 'trunc' resets high-water mark, deallocates space, can't be rolled back; 'delete' doesn't reset HWM as it doesn't release space, & can be rolled back.
  • A. CHAR(n) type allocates n fixed bytes, where VARCHAR2(n) allocates space upto n bytes only when an actual value is used.
  • Unix (if worked) : How to see hidden files ? How to search specific text in unknown files ?
  • A. '-a' ; grep "txt" ./*
  • What's the significance of listener configuration file on a client ?
  • A. No significance - it's not used.
Intermediate-knowledge test :

  • Difference between gv$, v$, v_$ & x$ objects ?
  • A. V_$ views are created over X$ base tables . v_$ views have v$ synonyms & gv$ views are global views that have multiple instance information (of the same DB - as in Oracle parallel server, RAC)
  • How does column cardinality affect your choice of indexes during DB design ?
  • A. If table is large & any column's cardinality to total rows ratio is small (less than 1:200), that column should have bitmap index creteated on in, if it is used in queries. Simply, if the unique values in a column is quite less, as compared to total rows in a table,bitmap index is required for better performance.
  • Statspack (if worked) : What are the levels of snapshots ? How to install & deinstall Statspack ? How would you use it for clustered (say unix level) environment ?
  • A. L1-7 ; Installation / removal - spcreate / spdrop ; Cluster - no change, as it is installed on the database level, not instance level.
  • DB cloning procedure ?
  • A. map & place online / cold backup data files --> generate & edit basic text controlfile --> nomount --> run 'create controlfile' from basic controlfile --> OPEN with restlogs
  • How'd you recreate a parent table without affecting the child objects ?
  • A. Disable constraints, recreate, enable.
  • Import - 40 GB & Undo tablespace - 20 / 30 GB - how to import ? How to improve import performance ?
  • A. Use commit ; Performance : groups of schemas in parallel
  • RMAN (if worked) : Recovery procedure ?
  • A. Tape identification --> restore --> recover
  • How to find Oracle background processes in Windows ?
  • A. 'procexp' - a windows tool helps find the Windows threads, which are Oracle background processes
Advanced database administration questions :

  • How to sync up the OEM with agent ?
  • A. Shut agent, remove server, clean agent directories (of .q & snmp.ro / rw files), restart, discover server
  • Significant requirements / points of complete / incomplete recovery ?
  • A. Complete - point-in-time (time based / ARCH no. based), incomplete - past-time recovery with resetlogs (cancel based)
  • Import - multiple times ? What should be the strategy ?
  • What should be the backup strategy of a large database - in TB's ?
  • A. RMAN - incremental L1 daily, Full L0 weekly
  • We have a set of datafiles without controlfile & without DB-name on a disc. How to recover the database ?
  • A. Generate basic text control file from any DB --> map & arrange available datafiles --> edit text controlfile for new paths & DB name --> NOMOUNT DB --> run the file as .sql --> OPEN DB with resetlogs
  • RAC setup requirements & procedure ?
  • A. Requirements - A. mimimum 2 servers, NAS storage, 2 NIC cards on all servers
  • A. Procedure - Coming soon.
  • How to grant read-only access to v$session ?
  • A. Grant select to v_$session


Final rating of candidates & decision making :

  • For effective rating, have a chart ready to list your comments & grades. We use a standard chart like this (open the chart in another window, for clarity, by clicking on it).
  • Feel free to add / subtract columns to the above Oracle DBA rating chart based on your individual interview requirements.
  • It's good to add interview dates, contact numbers, in "remarks" field along with other comments.
  • Have another round of interview if you feel necessary & make your decision based on the ratings.
When you prepare well & go well equipped with your weapons, you will have successful interviews & candidates would also be happy about the professionalism of the interviewer. Happy interviewing !
Feel free ask me any questions, through comments on this page.

Friday, August 25, 2006

Monday, April 03, 2006

Useful links on Oracle database and related topics

  1. A place to find some useful scripts (database related Unix shell / SQL / PL/SQL / monitoring / Tuning etc.) : Orafaq
  2. Oracle Apps (applications) / E-business suite related tips : Searchoracle
  3. A useful book available on Oracle Database Normalization . I'll review this soon & post here.

Wednesday, January 18, 2006

How to upgrade Oracle 9.2.0.1 to 9.2.0.5 or 9.2.0.6 (9i versions)

Applying Oracle upgrade to version 9.2.0.5 (patchset 3501955) or 9.2.0.6 (patchset 3948480)

Applying Oracle 9i Patch (This can be used for the Oracle client and server both)

1. Log in to the database server machine as the oracle user (administrator role in case of windows) on Unix.
2. Make sure that your environment contains the correct values for the ORACLE_HOME and ORACLE_SID variables.
3. Stop the listener process (in case of Oracle server upgrade). As the oracle user:

lsnrctl stop

4. Download the patchset Extract the 9.2.0.5 patchset (3501955) or 9.2.0.6 patch set (3948480) archive into a temporary directory.
5. Change to the directory that contains the 9.2.0.5 or 9.2.0.6 patch archive and execute the following:

Unzip the patch archive, viz. p3948480_9206_***.zip (this example is for Linux. Windows & other Unix patch names would be different.)


6. Install the Oracle Universal Installer (setup.exe in case of Windows patch) :

cd /usr/local/tmp/oracle/patches/3948480
Disk1/runInstaller &

Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME
Destination Path: /usr/local/oracle/products/9.2
Next ->
Select a Product to Install
Select: Oracle Universal Installer 10.1.0.3.0
Next ->
Summary
Install ->
End of Installation
Exit


7. Install the Oracle 9.2.0.6 patch set:

Disk1/runInstaller &

Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME
Destination Path:
Next ->
Select a Product to Install
Select: Oracle 9iR2 Patch Set 9.2.0.6.0
Next ->
Summary
Install ->

A window will open and you will be prompted to execute (This will not happen in Windows)
root.sh. Open a console window and run $ORACLE_HOME/root.sh as
"root" user of Unix.
OK ->
End of Installation
Exit


Database Upgrade Presteps

Before upgrading the database, do the following:

*

If JServer is part of the installation, ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
*

For RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.
*

Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
1. Start the database:

SQL> startup


2. Enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):

SQL> show parameter pfile;


This displays the name and location of the server parameter file or the initialization parameter file.

If the system is using a server parameter file, do the following:
1. Execute the following command to display the current value of the SHARED_POOL_SIZE initialization parameter:

SQL> show parameter shared_pool_size;


2. If SHARED_POOL_SIZE initialization parameter is less than 150 MB, execute the following command:

SQL> alter system set shared_pool_size='150M' scope=spfile;


3. Execute the following command to display the current value of the JAVA_POOL_SIZE initialization parameter:

SQL> show parameter java_pool_size;


4. If the JAVA_POOL_SIZE initialization parameter is less than 150 MB, execute the following command:


SQL> alter system set java_pool_size='150M' scope=spfile;


3. Shut down the database:

SQL> shutdown immediate;


[Note] Note

The UF installation uses an spfile (Oracle9i Database will automatically use the $ORACLE_HOME/dbs/spfilewebct.ora file if it exists), but we want to make sure that the pfile and the spfile match up to reduce confusion.

If you adjust the above parameters, run the following:

SQL> create pfile from spfile;


Upgrading the Database

Perform the following steps on every database associated with the upgraded Oracle home:

1. Log in as the oracle user:

sqlplus /nolog
SQL> connect / as sysdba


2. Enter the following SQL*Plus commands:

SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off


3. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script. This list provides the version and status of each SERVER component in the database. If necessary, rerun the catpatch.sql script after correcting any problems.
4. Restart the database:

SQL> shutdown immediate
SQL> startup


5. Execute the following script to recompile all invalid PL/SQL packages now. (This step is optional but recommended.)

SQL> @?/rdbms/admin/utlrp.sql


6. Restart the listener process (in case of Oracle server). As the oracle user:

lsnrctl start

Sunday, January 01, 2006

The art & science of working with Oracle databases & applications

I'm an Oracle database & applications specialist - that's what I call myself, for my experience, certifications & interest in the subject & the technology. Oracle database administration (DBA) & Apps (also called as Oracle Applications) are my passion, as well as my job, my food & water :-) . I'm a certified OCP (Oracle Certified professional) for Oracle 9i and 8i both. I'm planning further certifications soon - 10g and Apps DBA.

I was recently looking for some information on Oracle Apps, when I landed upon a very useful discussion on Asktom website . I immediately thought, that I must save this piece of research somewhere, or it would be lost soon. After spending so much of resources research, I figure out that Asktom is a really wonderful resource of truthful & to the point information on the subject. Tom Kyte works in Oracle Corp. & knows things in and out. A good source of Oracle Demos, news & developments is PSOUG.org .

Another rather small and useful piece of information was found in Sridhar's posts, who seems to have researched the subject well. I appreciate his focussed information, organized into numbered statements. Two alltime good resources of Apps related information are Oracle Applications Community and included here, is Apps documentation . I've found a really useful Oracle Apps blog (my wife searched for me :-) ) -- Oracleappsblog . If you want to search for some more resources, try the Google search box below (there are few good ones on the left sidebar). You'll find Database industry news at the bottom.

I myself plan to contribute some interesting pieces of knowledge which are not available in books & could be gained only through experience & research. This would be my target in later posts on this page. So, here goes my first Oracle technology post ! I better keep it small & simple :-)

--Anshu