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

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

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 h

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

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