Oracle Database Technology

   The Database Technology market is full of various products, even when we look at just Oracle's line of products - Advanced Security, Advanced Compression, Real Application Clusters (RAC), Real Application Testing (RAT), Exadata & so on ! However, we don't just go out and buy anything - we have to have a need to have certain helper software / features to make our lives easier. How do we assess whether I need one or another ? Here's my humble attempt today to make a checklist for these 4 products (To print-out or view the sheets fully, use the direct links) ... Exadata coming soon :-) : (1) Assessing the need for Oracle Real Application Clusters (RAC) : RAC checklist (2) Assessing the need for Oracle Advanced Security Option (ASO) : ASO checklist sheet1 ASO checklist sheet2 (3) Assessing the need for Oracle Advanced Compression Option (ACO) ACO checklist (4) Assessing the need for Oracle Real Application Testing (RAT) RAT checklist sheet1 RAT...

Some times we face a problem with invalid objects while upgrading a 9i database to 10.2.0.3 …When the objects in CTXSYS are invalid the upgrade will not proceed. In this case we need manually uninstall & install Oracle text. This is what we have done with one of our databases on a Solaris 9 unix server. Please refer the following notes for uninstalling & installing the Oracle text Note 275689.1 "Manual installation, deinstallation of Oracle Text 9.2.0.x" Note.280713.1 Manual installation, deinstallation of Oracle Text 10gR1 and 10gR2 An additional point to remember ……….. We faced this problem while doing it in one database : While installing oracle text, especially when we run the script “start ?/ctx/admin/defaults/drdefus.sql” (refer the aforementioned notes), it may throw the following error.. SQL> create index dropme_idx on dropme(name) indextype is ctxsys.context; create index dropme_idx on dropme(name) indextype is ctxsys.context * ERROR at line 1: ORA-29855...

--contributed by Manjunath Ulthi, Senior DBA at Infosys Technologies limited 1 Introduction Problem: OCI client spins when machine uptime >= 249 days in Linux machines. Once the uptime crosses 249 days the database client hangs and leads to high CPU utilization and server hung state. The user will not be able to connect to the databases This is oracle known bug 4612267. Solution: This can be handled by applying the patch 4612267. The patch has been applied in the servers server126, server041 & server120 and tested. After the patch has been applied the client connections were normal after 249 days and all the operations of databases/applications were normal and didn’t encounter any problems. For further information refer the Metalink Notes Note: 338461.1: SQL*Plus With Instant Client 10.2.0.1 Hangs, When System Uptime Is More Than 248 Days Note:4612267.8 : Bug 4612267 - OCI client spins when machine uptime >= 249 days 2 General Instructions Procedure to apply the patch 1. Do...

Back to square one, where I started this blog ? ... Nooooo ... Here I am not providing a step by step procedure for Oracle performance tuning, however a framework to help a DBA with tackling Oracle performance issues. This could be seen as a reference to understand where to start, what information to collect, where to proceed, how to conclude, etc. . Without much ado, let's go ahead. What's the problem ? Some of us (DBA's) get bad performance notice from users & we might tend to directly jump into trying to find a solution. Instead of trying that, a better way to do that is to first find out what that problem is & how is it seen by the user. First, start with these questions asking the requestor : 1 Is the whole application slow or only some modules are slow ? 2. When did you first notice this problem ? 3. Is the problem noticed all of sudden? Is it gradual ? 4. Is it recurring as you mentioned ? 5. Is there any recent upgrade to application ? 6. ...

For most of us, writing procedures, functions or say just a PL/SQL Code Block based on a given logic is wasy. The challenge lies in designing and placing the "Exception Handling" Block with the intent of capturing the correct line of Code that raised the exception so that one can them Zoom into the erroneous line and do make the necessary fixes. Now many of you would agree that this comes with experience 'n' ofcourse proactive thinking. Now conder the following Code Snippet, WHEN NO_DATA_FOUND THEN dbms_output.put_line (sqlerrm); END; Looks familiar...? But here's a catch - this code block will keep rescuing us as far as Error String is less than 255 Characters. (The limitation with put_line() is that it can capture a max of 255 Characters). Oracle has revolved this by its offering - DBMS_UTILITY .FORMAT_ERROR_STACK BEGIN error(); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( SQLERRM ); DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); END; / ORA-009...

Have you ever faced this ? ... You have your statspack report ready & are trying to work with your Statspack analyzer tool, which performs statspack report analysis & generates an html file, which is more understandable to non-DBA's too (with some charts, top-queries, recommendations, etc.) . At this moment, either your analysis tool gives up, or the website which you used for analysis, is unavailable (recently happened with oraperf, which we used) ! A solution to this, for a DBA, is to be able to read through the statspack report & identify the key areas, issues of importance . This place offers a simple statspack survival guide , to help understand the report. Hope this helps a DBA in speedy statspack analysis, instead of depending on a tool that is not working, or giving erratic results.

We encountered an interesting scenario worth mentioning. Scenario: Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also. 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 : $ sqlplus ld.so.1: sqlplus: fatal: /app/oracle/product/10.2/lib/libsqlplus.so: Permission denied Killed The files under "lib" directory under home didn't have permission for anybody other than "oracle" user. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/libsqlplus.so When we permit the other users (other than oracle) to read/exe...

As a standard practice, the DBA selection process is split into 3 phases : Screening of resumes & filtering out the suitable candidates that can be interviewed. Scheduling & completing the interviews (rating & grading also go hand-in-hand during interview). 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...

Information to be posted soon .

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

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)...

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 rat...