Oracle Database Technology

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