Oracle Linux 6 Has Been Released

Oracle Linux 6 has been released and the installation DVD images are available on edelivery web site. Oracle Linux 6 is free to download, install and use. As is the case with Oracle Linux 5.6, the default kernel on x86_64 platform in Oracle Linux 6 is the Unbreakable Enterprise Kernel. On the other hand, Oracle Linux 6 also includes a Red Hat compatible kernel built directly from RHEL source. It’s already installed, and you can use it if modify /etc/grub.conf file.

Unbreakable Linux Network: http://linux.oracle.com
Oracle Public Yum Server: http://public-yum.oracle.com
Oracle E-Delivery: https://edelivery.oracle.com/linux

ASP, ADODB and ORA-01013

One of our software developers told me that there’s a problem with database server, and they get ORA-01013 while trying to fetch data from a web page (using ASP). Although ORA-01013 error is related with client side, I checked the database and saw that everything is OK.

So I started to examine the web page with the developer. The page was executing a query which takes 1 minute to return the result. I noticed that they got the error after 30 seconds, so I wanted to check the timeout settings of IIS (Internet Information Services – Application server of Microsoft). We see that the timeout was 10 minutes. I examined the connection strings and see that ConnectionTimeout value of ADODB was set to 900. These settings look OK but the error was still there.

In almost every case, DBAs are expected to know to handle all kind of database connection problems, even if the problem is related with programming. After reading the documents of ADODB, I noticed that there’s a paramtere called “CommandTimeout”. So the developer added the following line and the problem is solved:

Performing Block Media Recovery with RMAN

We’ll corrupt a block on a datafile and try to restore it via RMAN. First let’s create a sample tablespace:

Then let’s create a table in this tablespace and insert a sample row:

Now we need to find the block number of this record (so we can corrupt it):

Before we corrupt the datablock, we’ll take a full backup.

After the backup completed, we’ll fill this block with zeros at the OS level.

How to Set Current Value of a Sequence Without Dropping/Recreating

Oracle does not let you change the value of a sequence. If you need to change its value, you should re-create the sequence. On the other hand, we can use a trick to change the value of a sequence without recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

Terminating Oracle Sessions at the Operating System Level

Sometimes, “ALTER SYSTEM KILL SESSION” will not terminate a session, it will just mark it as “killed”. In this case you can kill the process or thread of that session at the operation system level.

To kill the session at the operation system level, we need to identify the process ID of the session. Following query will give us the process/thread ID (SPID) of a session we know SID:

To kill the session on the Windows operating system, just use “orakill” utility (comes with Oracle):

To kill the session on the Unix operatin system, you can use “kill” command:

If after a few minutes the process hasn’t stopped, you can force the session to terminate by using “-9” parameter:

While killing a process, make sure that you are not killing background processes of Oracle such as DBWR, LGWR, SMON, PMON. If you kill one of them, Oracle will crash or become unstable.