Skip to main content


Oracle Virtualbox - UUID conflict resolved for VM

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 win…
Recent posts

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

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 ha…

HWM (high watermark) of a tablespace ?

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 calculati…

How to speed up data transfer between servers

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 an…

Issue with invalid objects while upgrading the database to

Some times we face a problem with invalid objects while upgrading a 9i database to …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 o…

How to optimize UNDO tablespace ?

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

(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 = &…

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

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