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.

HugePages and Oracle Database 11g Automatic Memory Management

When you create a 11g database using Database Configuration Assistant (DBCA), Automatic Memory Management (AMM) is enabled by default. On a Linux system, if AMM is enabled, SGA memory will be allocated by creating files under /dev/shm so HugePages will not be used. Unfortunately AMM is absolutely incompatible with HugePages.

If you want to use HugePages, both MEMORY_TARGET / MEMORY_MAX_TARGET initialization parameters should be unset for the database instance.

Setting Nonstandard/Multiple Block Sizes

A block size of 8 KB is optimal for most systems. On the other hand, OLTP systems can use smaller block sizes (such as 4K) and DSS systems can use larger block sizes (such as 16K, 32K).

Guidelines for database block size:

1) You need to set Oracle Block size equal to or a multiple of the operating system block size.

2) If your rows are small and you use a large block size, when you fetch a block you may get lots of rows and waste your buffer cache. It may also cause block contention.

3) If your rows are large then you may need a large block size to prevent “chaining”.

4) Larger Oracle block sizes may give better index balancing with more rows in each branch.