Oracle Database Technology

Oracle Virtualbox - UUID conflict resolved for VM

2017-09-04T15:02:00.003-04:00

Now-a-days, we use virtual machines (VMs) a lot, to test operating systems, software, databases etc. as sandboxes - test, play, destroy ... repeat ! I had an interesting learning with Oracle virtualbox (issue & then solution discovery), which I'm sharing here ... it might help someone with similar situation :

I had a VM in virtualbox, which I copied into a separate folder, while it was powered off. Now, after the original VM was gone, I wanted to add it as a separate VM with a different name. However, I was seeing this UUID conflict. (UUID already exists) What I did :

1) Created a new empty VM with the desired NAME.

2) Located the new folder, where copy of the VM was there.

3) Ran the commands to change the UUID for each disk-file (.vmdk or others) I had there : "C:\Program Files\Oracle\VirtualBox\vboxmanage.exe" internalcommands sethduuid disk1.vmdk
(you have to be in the same directory as the disk files, to run this command. Easy, wherever you need the command window, SHIFT+Right-click > open command window here)
4) I ran the above command 4 times for 4 disks I had in the folder. Each disk file got new UUID now.
(make sure to remember / note the sequence of disk file numbering if there are many)
5) Now, go into the settings of the empty VM, storage, add the disks in same sequence under SATA disks one-by-one (the pictures below show it for 1 disk).

6) That's it ! Now I could start my new VM without issues, as all the disks had new UUID without conflicts.

How do I know if I need Oracle ACO / ASO / RAC / RAT ?

2013-04-30T15:29:00.000-04:00

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 checklist sheet2

If you have any suggestions on improvement, please let me know. Ia appreciate your time spent here - I hope you get some useful information out of this. Have fun architect-ing :-) !

HWM (high watermark) of a tablespace ?

2009-07-08T18:22:00.004-04:00

How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculations, or better, based on the above SEGMENT theory, we should resize individual datafiles within a tablespace more accurately.
For the sake of simplicity & ease of understanding, we may still see the datafile usage as an overall tablespace usage :

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size';

--Tablespace-wise Used / unused blocks for HWM calculation

select tablespace_name, file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
order by 1;

You may save this above data output as a snapshot of starting point. Now, let's create a script to resize individual datafiles (you may exclude some important ones, like SYSTEM, UNDO, TEMP). I've used 2 types of scripts - one is exact resizing to the HWM & other one is with some future space allocation planning built-in & still reducing wasted space :

--Exact resizing

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

-- Future Space planned resizing - change the 0.1, which is 10% estimate, to any other value less than 1, to alter how much extra you wish to still keep in each datafile / tablespace

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 + 0.1*(nvl(hwm,1)*8192)/1024/1024) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0
/

Dear reader, I've been able to make use of this procedure successfully. Please let me know your results & learnings.

How to speed up data transfer between servers

2009-06-09T18:02:00.031-04:00

This article contains values as an example of a specific case. Your servers may have different configuration. Please use accordingly.

Over a network, although the network bandwidth could be 10 GigaBits, yet, if the servers have
1 GigE Ethernet network cards (1 GigaBit), the overall file-transfer
throughput is 1 GigaBits / sec, which means (1024*1024*1024 / 8) bytes
per sec = 125 MB/sec. This is the maximum throughput available on the network.

For convenience, the 2 esrvers we need to improve data transfer speeds between - we'd call one as source, & other target. Now, if we ping from a source to target server, we get about 42 ms of turnaround time.

This gives us maximum amount of data at any point of time possible on this network as (125 MB / s) * (0.042) = 5.250 MB . However, the default network level settings on the servers don’t let this much bandwith to be used. We may verify it with “ndd & route” commands (only SA can run these commands):

If you have root access to any locale Linux box, you may try "ndd get ..." & "route print" commands, otherwise ask the SA to provide you the outputs, to find out the current settings.

The **recv** & **xmit** (receive & transmit) values are the default ones, used by the server & maximum values are defined by **cwnd** & **max_buf. e.g. The 49152 value indicates that this server (or any RMAN job) would send / receive, by default, 48 KB of traffic only. Moreover, if someone is manually writing a program, may use the maximum of 1048576 bytes
= 1 MB, by using the **max** variables)

Now, our purpose is to be able to use the whole 5.25 MB that can be used on the current
connection. However, the allowed values for these 4 variables, should be a power of 2. Hence, we would pick the next higher power of 2, which is 8 MB (=2 ^ 3). We would need to set the **max** limit variables also equal to 8 MB.

DBA should ask SA to run these commands for setting the values, with “set” option, instead of “get”. E.g. “sudo ndd -set /dev/tcp tcp_recv_hiwat 8388608”

The “ndd” commands get / set values for ALL connections. We may not want to do that everytime,
because if a non Netbackup / media server (say, a windows client) connects to this server, which has a lower capacity of being able to handle traffic (say, 1 MB), then the 5 MB packets would overwhelm the connection. Hence, to avoid this scenario, we should use “route” commands to set connections settings between specific IP’s , which don’t work the same as “ndd”.

It’s always safer to use “route” settings instead of
“ndd” except “tcp_max_buf”. Here are some screenshots (please esarch internet to see more detailed syntax of route / ndd commands) :

The “sendpipe” option is same as “tcp_xmit_hiwat” of ndd & recvpipe is “tcp_recv_hiwat”.
The “ssthresh” is same as “tcp_cwnd_max”. However, the “tcp_max_buf” still needs to be set through “ndd”.

With "route" settings, all these settings will be wiped out & will go back to default whenever a server is rebooted, so changes are temporary till next boot. With "ndd', it may be made permanent, which may be unnecessary, unless it's between some DB & RMAN backup server (viz. Netbackup) where there's a regular transfer of data.

"ndd" uses get / set options to show or edit values, whereas "route" uses print / add / delete options.

Now, having understood all the above concepts, when a DBA / SA is ready to start working on their specific environment of servers, you may follow this :

1) 1st we need to get the current values from the SA, of the 4 parameters (to be executed from source / target server),
a. ndd –get /dev/tcp tcp_recv_hiwat
b. ndd –get /dev/tcp tcp_xmit_hiwat
c. ndd –get /dev/tcp tcp_max_buf
d. ndd –get /dev/tcp tcp_cwnd_max
e. route get

2) For “route get” output, these parameters are named sendpipe, recvpipe, ssthresh (there’s no equivalent of tcp_max_buf

here – it can be set only through ndd command). “ndd” sets the parameters universally, for all IP’s, whereas “route” can be used to set between specific IP’s & temporarily.

3) Along with this we need to know the server Ethernet card types (on both source & target servers) , viz. 1 GigE, 10GigE ; AND the network bandwidth , viz. a 10 Gbps line, 24 Mbps line etc.

4) Then we need to make calculations based on the above network bandwidth values, taking into the consideration, an example calculation on my Blog - http://databaseoracle.blogspot.com/2009/06/how-to-speed-up-data-transfer-between.html .

5) This way, we’d arrive at a number, which is the total bandwidth that CAN BE available between the 2 servers under consideration. We’ll call this number (if it is a power of 2) or the next power of 2, MAX-BANDWIDTH

6) Then we request the SA to set the above parameters to new values (use route command, instead of ndd , to set these values temporarily) :
a. route add –host

… -sendpipe MAX-BANDWIDTH -recvpipe MAX-BANDWIDTH -ssthresh MAX-BANDWIDTH

b. ndd –set /dev/tcp tcp_cwnd_max MAX-BANDWIDTH (this parameter is available only through ndd & SA may decide whether to set it or not, based on implications) .

7) If the SA is responding in minutes, this whole calculation would take about 10-20 minutes.

8) Please let me know in case of any doubts & your experience when you try these modifications.

9) Happy transferring lightning-fast data !

Issue with invalid objects while upgrading the database to 10.2.0.3

2008-01-27T17:10:00.000-05:00

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: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04068: existing state of packages has been discarded
ORA-04063: stored procedure "CTXSYS.TEXTINDEXMETHODS" has errors

IN this case please do the following…….

Sql>connect ctxsys/****
Sql>@$ORACLE_HOME/ctx/admin/dr0def.sql
Sql>connect sys as sysdba
Sql>declare
ver varchar2(80);
begin
select ver_dict into ver from ctxsys.ctx_version;
dbms_registry.loaded('CONTEXT', ver,
'Oracle Text Release 'ver' - Production');

-- to do: a validation procedure
dbms_registry.valid('CONTEXT');
end;
/
Sql>exit

How to optimize UNDO tablespace ?

2007-11-29T18:52:00.000-05:00

--contributed by Manjunath Ulthi,

It becomes a challenge to figure out the right amount of UNDO to be allocated to a database. Try this method :

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

where,
(UR) UNDO_RETENTION in seconds - an init parameter
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

Two (UPS) can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula (DBS) requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.

The following formula calculates the total number of blocks generated and divides
it by the amount of time monitored, in seconds:

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400)
FROM v$undostat;

The following query calculates the number of bytes needed:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

Simple, ain't it ? Please provide your inputs & actual case studies through the comments link ! We love feedback.

Intereseting --- 249 days bug for Oracle on Linux servers

2007-10-18T12:12:00.000-04:00

--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. Download the patch from oracle metalink.

The downloaded patch (p4612267_10201_LINUX.zip) is available at server120 at location /app/oracle

2. Copy the Zip file at $ORACLE_HOME
3. Unzip the file .It will create a directory with patch number 4612267
4. Test the opatch utility and check the patches which have been applied to the database server.

bash-2.05b$ $ORACLE_HOME/OPatch/opatch lsinventory

bash-2.05b$ $ORACLE_HOME/OPatch/opatch lsinventory

Invoking OPatch 10.2.0.1.0

Oracle interim Patch Installer version 10.2.0.1.0

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

Oracle Home : /app/oracle/product/10.2.0

Central Inventory : /app/oracle/oraInventory

from : /app/oracle/product/10.2.0/oraInst.loc

OPatch version : 10.2.0.1.0

OUI version : 10.2.0.1.0

OUI location : /app/oracle/product/10.2.0/oui

Log file location : /app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch-2007_Oct_13_10-11-13-EDT_Sat.log

Lsinventory Output file location : /app/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory-2007_Oct_13_10-11-13-EDT_Sat.txt

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

Installed Top-level Products (1):

Oracle Database 10g 10.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (2) :

Patch 5865568 : applied on Thu Mar 01 21:50:15 EST 2007

Created on 15 Feb 2007, 20:01:44 hrs PST8PDT

Bugs fixed:

5865568

Patch 4516865 : applied on Wed Sep 20 04:47:39 EDT 2006

Created on 14 Feb 2006, 00:22:00 hrs US/Pacific

Bugs fixed:

5039292, 4747264, 4908101, 4516865

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

OPatch succeeded.

5. Take the backup of lib directory in $ORACLE_HOME

bash-2.05b$ pwd

/app/oracle/product/10.2.0/lib

bash-2.05b$ cp -R * ../bkp_lib/

6. Go to $ORACLE_HOME/4612267
7. Bring down the databases, listeners and DB consoles. Ensure that you have good backup pieces before proceeding
8. Apply the patch

$ORACLE_HOME/4612267>>>$ $ORACLE_HOME/OPatch/opatch apply

Invoking OPatch 10.2.0.1.0

Oracle interim Patch Installer version 10.2.0.1.0

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

Oracle Home : /app/oracle/product/10.2.0

Central Inventory : /app/oracle/oraInventory

from : /app/oracle/product/10.2.0/oraInst.loc

OPatch version : 10.2.0.1.0

OUI version : 10.2.0.1.0

OUI location : /app/oracle/product/10.2.0/oui

Log file location : /app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch-2007_Oct_13_10-22-20-EDT_Sat.log

ApplySession applying interim patch '4612267' to OH '/app/oracle/product/10.2.0'

Invoking fuser to check for active processes.

Invoking fuser on "/app/oracle/product/10.2.0/bin/oracle"

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/app/oracle/product/10.2.0')

Is the local system ready for patching?

Do you want to proceed? [y|n]

y

User Responded with: Y

Backing up files and inventory (not for auto-rollback) for the Oracle Home

Backing up files affected by the patch '4612267' for restore. This might take a while...

Backing up files affected by the patch '4612267' for rollback. This might take a while...

Patching component oracle.oracore.rsf, 10.2.0.1.0...

Updating archive file "/app/oracle/product/10.2.0/lib/libcore10.a" with "lib/libcore10.a/sltrg.o"

Patching component oracle.rdbms, 10.2.0.1.0...

Updating archive file "/app/oracle/product/10.2.0/lib/libcore10.a" with "lib/libcore10.a/sltrg.o"

Running make for target client_sharedlib

Running make for target client_sharedlib

Running make for target ioracle

ApplySession adding interim patch '4612267' to inventory

The local system has been patched and can be restarted.

OPatch succeeded.

9. Verify the patch in inventory……. Check the patch number 4612267 in the results

bash-2.05b$ $ORACLE_HOME/OPatch/opatch lsinventory

Invoking OPatch 10.2.0.1.0

Oracle interim Patch Installer version 10.2.0.1.0

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

Oracle Home : /app/oracle/product/10.2.0

Central Inventory : /app/oracle/oraInventory

from : /app/oracle/product/10.2.0/oraInst.loc

OPatch version : 10.2.0.1.0

OUI version : 10.2.0.1.0

OUI location : /app/oracle/product/10.2.0/oui

Log file location : /app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch-2007_Oct_13_10-24-32-EDT_Sat.log

Interim patches (3) :

Patch 4612267 : applied on Sat Oct 13 10:23:06 EDT 2007

Created on 5 Oct 2005, 13:48:00 hrs US/Pacific

Bugs fixed:

4612267

Patch 5865568 : applied on Thu Mar 01 21:50:15 EST 2007

Created on 15 Feb 2007, 20:01:44 hrs PST8PDT

Bugs fixed:

5865568

Patch 4516865 : applied on Wed Sep 20 04:47:39 EDT 2006

Created on 14 Feb 2006, 00:22:00 hrs US/Pacific

Bugs fixed:

5039292, 4747264, 4908101, 4516865

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

OPatch succeeded.

10. Start the databases, listeners & DB Consoles and verify the databases & applications ::::

The art and science of Oracle DB performance tuning

2007-10-01T15:19:00.000-04:00

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. Is there any substantial increase in data ?
7. Is there any increase in number of concurrent users ?
8. Is there any increase in total number of users ?
8. Define the problem in your own words.
9. Suppose if we find the certain module of the application is causing the problem, Is it possible to change that module ?
10. Any other comments about the problem ?

PS : I recently got a request to bounce (/ restart) a PRODuction database from a user, stating that the DB was down. When I spoke to him, I discovered that just because he was not able to connect to his application & see anything, he thought the Oracle database behind his application, was down & needed a bounce. I already knew that the database was up & fine, I asked him to speak to the Application team. It was discovered as an application issue, never to be handled by a DBA :-)

Tackle the problem now

Once the problem is clear & the DBA knows that there is a real issue, one may follow these sure-shot steps. You are welcome to follow your own strategy & resolve the issue. If anyone has some strategy to go about performance issues, please share your way of doing things, through comments on this post. I will incorporate them here, as your contribution.

Talk to the user who is facing the problem, to clarify any of the above inputs
Install & run STATSPACK on the concerned Oracle database. By default, spauto.sql schedules the snapshots hourly.
STATSPACK would take some time to provide some useful report. Meanwhile, look at the other aspects below.
If the user knows some areas / schema / SQL / PL/SQL code areas of the database, which might be causing trouble, look at them once. Understand from the user, the database logic used in their "problem operation".
Analyze the database objects involved in the above key areas, for appropriate indexing, fresh statistics.
Look at the explain plan if there's already a SQL code pointed out , otherwise perform this step after you get STATSPACK report.
Start tracing for the schema(s) which the users use to connect to the database.
Have the user run their operation once, to make sure it is captured in STATSPACK & the trace.
Generate the STATSPACK report & use my "STATSPACK post" , the "Survival guide" or a statspack analysis tool to see it in a more understandable format with recommendations too (this tool is not necessary, however, it is helpful).
Format the recent trace (with tkprof utility), generated after the trial of the concerned operation by the user.
Analyze the above 2 reports & find out the issues / areas to be focussed on. STATSPACK report can list out top SQL's / PL/SQL areas of the database. We can start your real, concentrated analysis here. You may repeat steps 4, 5, 6, 7 again if required (most of the times, they will be).
If the problem area is identified as PL/SQL code, it's best to use DBMS_PROFILER to find out the problem line(s) of code. You may use "Oracle official area" to know how to use DBMS_PROFILER or try the database journal (you may just enter DBMS_PROFILER in a search engine & get 100's of results on how to use it). DBMS_PROFILER needs to be installed first as SYS user (profload.sql) & then some objects are created in the concerned schema through schema login (proftab.sql).

Let me know your case-studies - I'll post them here too, along with mine. More on tuning later ...

Tracing Errors

2007-09-26T11:45:00.000-04:00

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-00900: invalid SQL statement

ORA-06512: at line 2

Now, as evident from output above, the information provided in error stack is sufficent for application error logs and can be worked upon accordingly. And the best part is there's no limitation of number of characters we can capture in error stack. Makes life easy indeed :)

Statspack report analysis

2007-09-19T00:25:00.000-04:00

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.

Permitting a user on UNIX / Linux to use sqlplus

2006-11-03T18:24:00.000-05:00

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/execute on the lib directory (chmod -R o+rx $ORACLE_HOME/lib), the error changed.
$ 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
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.
The error changed again :
$ sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
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.
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 .
$ 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
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 :

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 :

$ORACLE_HOME/bin
$ORACLE_HOME/lib
$ORACLE_HOME/oracore
$ORACLE_HOME/sqlplus

In addition, these 4 parameters should also be set in the user's environment (.profile) :

ORACLE_HOME,
LD_LIBRARY_PATH,
ORACLE_SID,
PATH

Oracle DBA Interview process

2006-09-26T14:58:00.000-04:00

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

Oracle certification information

2006-08-25T20:08:00.000-04:00

Information to be posted soon .

Useful links on Oracle database and related topics

2006-04-03T19:02:00.000-04:00

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.

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

2006-01-18T14:38:00.000-05:00

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

The art & science of working with Oracle databases & applications

2006-01-01T17:16:00.000-05:00

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