Expired storage battery causes database performance slow

Early Monday morning, I was told our data warehouse loading process took much longer than before.
The application has been running fine for months, and there was no recent changes made on application, system, or database. The data volume is about the same as before.
AWR reports showed that the bottleneck was on system IO, looks like storage IO performance is much worse than before. So I asked the storage engineer the check storage battery status of StorageTek.

Yes, our batteres expired!

Vendor is delivering new batteries for us, replacing batteries should solve our issue.

A few years ago, when I first started as a DBA, I encountered similar issue on an OLTP database.
All of sudden, one database became very slow. I spent much more time, but still couldn't figure out what went wrong. Only after a few days, our storage engineer noticed the battery expiration in HDS.
After replacing new batteries, database performance became normal again.

Why expired batteries affect the system I/O?
Joshua Townsend has a very good article on storage basics.
In storage, there is some amount of cache RAM acting as a buffer to physical disks, I/O operations on cache is much faster than the disks.
In case of battery expiration, storage will disable the cache, all the I/O operations directly goes to the disks, which are much slower than cache RAM.

For details, please visit Joshua's blog:
http://vmtoday.com/2010/03/storage-basics-part-v-controllers-cache-and-coalescing/

ORA-27300 and swap error

Recently I took over a HPUX running oracle 10g, when checking the alert log, I saw a few ORA-27300, ORA-27301, ORA-27301 errors, /var/log/messages also shows this:
Deferred swap reservation failure pid: 12345.
Apparently system doesn't have enough swap space for oracle.

$ swapinfo -am
           Mb    Mb      Mb   PCT  START/      Mb
TYPE    AVAIL  USED    FREE  USED   LIMIT RESERVE  PRI  NAME
dev      6656   195    6461    3%       0       -    1  /dev/vg00/lvol2
reserve     -  3134   -3134
memory   4536  1109    3427   24%

swapinfo shows that system has 4.5G physical memory, swap space is 6.6G, a bit smaller but quite close to oracle recommanded 4.5X1.5G

further check found that system hit ORA-27300 daily between 18:00 and 20:00, there must be some job running during this period causing the excessive swap usage.

AWR report shows this sql suspicous:

SELECT table_a.column_1, table_a.column_2, table_a.column_3
    BULK COLLECT INTO
    col_1, col_2, col_3
FROM table_a, table_b, table_c
    WHERE table_a.created_date >= to_date(:1,'dd/mm/yyyy:hh24:mi')
    AND    table_a.created_date <= to_date(:2,'dd/mm/yyyy:hh24:mi');

two issues with this sql:
1. cartition join table_b table_c, yet no columns from table_b and table_c are selected columns
    the cartition join of table_b and table_c produces about 2 billion rows,
    so if there is 1 row in table_a match the WHERE condition, sql will try to load the same row 2 billion times in memory.

2. use BULK COLLECT INTO directly
    according to oracle, BULK COLLECT should always be used with LIMIT

It turns out that the sql is part of a procedure, which runs daily at 18:00.
Since the job has been failing for months, I just disabled it, informed development team to rectify it.

So far two weeks passed, both server and database are working fine, no more ORA-27300 or "Deferred swap reservation failure" appears.

reference:
http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

Oracle silent installation using response file

Earlier in  Using Putty + Xming for X forwarding I mentioned how to setup X forwarding for oracle installation. Actually I have been using response file on most of the systems I configured.

The advantages of silent install using response file are:
1. All the installations are identical, so the setup is consistent across different environment.
2. No X required during the installation, just the normal ssh to server is sufficient.
3. Installation is simpler, just trigger the command, no need to click on different installing screen.

The most important part of silent install is the response file. Oracle installation binary contains a folder called response. From there we can modify the db installation rsp file. Many lines in the file are comments, so the file is self-explanatory. In 10g response file, the most important parameters to configure are:

UNIX_GROUP_NAME, ORACLE_HOME, ORACLE_HOME_NAME, INSTALL_TYPE

Once the response file is ready, we can install oracle binary by calling OUI
runInstaller -responseFile /full/path/to/myInstall.rsp -silent

The last few lines of the installation output, we will get some instructions run root.sh as root user. After run root.sh following the instruction, we are done with the installation.

Using Putty + Xming for X forwarding

A few years back, when I remotely installed Oracle in Solaris the first time, I thought exporting X display was the only option I have. I was told to use Xmanager for X forwarding, but Xmanager is not free. After searching around, I found Xming on sourceforge, so I wanted to give Xming a try.

Here is how I export X display to my local PC.
1. Software needed: Putty, Xming
Just search it, you can easily download them, you need to install Xming after downloading.

2. Start Xming
After starting Xming, you will see a small icon at the right bottom corner of your windows screen. Mouse over the icon, it will show "Xming Server:0.0"

3. Launch Putty to connect to remote server, after filling in the "Host Name" and "Port", click "Connection" -> "SSH" -> "X11", tick the checkbox before "Enable X11 forwarding", then click "Open" button.



4. You are connected to remote server with X11 forwarding, start any X application to test the settings are ok.



You can see that we run xterm from putty, will launch a xterm window on the local PC.

Reference: http://courses.cms.caltech.edu/cs11/misc/xwindows.html

Understanding Linux Hugepages

Hugepages can be extremely useful for systems having bigger RAM.

$ grep Huge /proc/meminfo

HugePages_Total:         0

HugePages_Free:          0

HugePages_Rsvd:          0

HugePages_Surp:          0

Hugepagesize:         2048 kB

Hugepagesize is the size of one Hugepage, on most x86 or x86_64 machines,  it's set to 2048 kB.
HugePages_Total is the number of Hugepages configured on the machine. In Redhat Linux, we can change this value by updating /etc/sysctl.conf

echo "vm.nr_hugepages = 200" >> /etc/sysctl.conf

 After updating, /sbin/sysctl -p may make the setting take effect, but it's better to restart the machine.

To confirm Hugepages is configured after rebooting,

$ grep Huge /proc/meminfo

HugePages_Total:       200

HugePages_Free:        200

HugePages_Rsvd:          0

HugePages_Surp:          0

Hugepagesize:         2048 kB

By setting Hugepages to 200, we are telling linux to reserve 200 Hugepages. So the memory associated with Hugepages is: HugePages_Total x Huagepagesize = 200 x 2048kB = 400M.
The memory associated with Hugepages is always allocated, and it cannot be swapped out.

Before setting Hugepages,

$ grep Mem /proc/meminfo

MemTotal:                1030940 kB

MemFree:                  861852 kB

After setting Hugepages, check memory usage again:

$ grep Mem /proc/meminfo

MemTotal:                1030940 kB

MemFree:                  452252 kB

We can see that the free memory dropped from 841M to 441M, after setting HugePages_Total to 200. The 400M memory is the associated to Hugepages, and it will always be allocated.

We can use Hugepages to lock some memory for oracle SGA, so the SGA will never be paged out, thus improving the database performance.

Oracle: How to roll forward standby database using archivelogs

At times oracle standby database may get out of sync with primary database. After fixing the issue causing standby out of sync, we need to roll forward standby database so that it can sync up with primary again.

To roll forward standby database, we can use archivelogs.

1. If the archivelogs are deleted from primary already.

We can restore the archivelogs from backups. Suppose our catdb stores RMAN catalog.

we can use this code to restore the archivelogs.

connect target /

connect catalog catuser/catpass@catdb

RUN {
allocate channel c1 type 'SBT_TAPE' 
parms 'ENV=(NB_ORA_CLIENT=prod1)' maxopenfiles 10;

allocate channel c2 type 'SBT_TAPE'
parms 'ENV=(NB_ORA_CLIENT=prod2)' maxopenfiles 10;
restore archivelog from time 'sysdate - 1' until time 'sysdate';

release channel c1;
release channel c2;

}

Depending on how long the standby has been out of sync, we can change the 'from time' accordingly. 

2. If the archivelogs are still on primary database.

We can simply copy them to the standby database,
after copying archivelogs to the standby, we need to catalog the files copied over.

RMAN> catalog start with '/tmp/logs_from_primary';

Otherwise oracle cannot recognize these files.

Once we have the archivelogs, either through method 1 or method 2,
we can roll forward the standby database.

SQL> recover managed standby database disconnect;

Oracle: How to setup physical standby database

In production environment, It's often required to have a physical standby database. In case the primary environment is gone, we can continue serve customer requests using standby database.

There are a few ways to set up oracle standby database, In any ways, please make sure you have the following ready:

• remote login is enabled, both primary and standby SYS password are the same, this can be set using orapwd.
• log shipping is enabled on primary, define the proper TNS entry, set log_archive_dest_x, and set log_archive_dest_state_x to enable.
• The pfile or spfile is ready on standby database.

1. Using storage level replication
On primary database, put the database in backup mode. This is to make sure the copy replicated to standby environment is consistent.

SQL> alter database begin backup;

In storage, split the replication. Stop backup mode on primary database:

SQL> alter database end backup;

Create standby controlfiile:

RMAN> backup current controlfile for standby format '/tmp/stdby_ctrol_%U';

Copy the controlfile backup to standby servers, restore the controlfile.

SQL> startup nomoun;

RMAN> catalog start with '/tmp/stdby_ctrol';

RMAN> restore controlfile from '/tmp/stdby_ctrol_blahblah';

Mount the standby database, and enable redo apply.

SQL> alter database mount standby database;

SQL> recover managed standby database disconnect;

Note: Please make sure do NOT sync back the standby environment on storage level again, otherwise it will destroy the standby database.

2. Using RMAN duplicate.

connect to primary database and standby databse.

$ rman target / AUXILIARY SYS/sys_pwd@sbdb

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

NetBackup: how to restore oracle backup to a different server

Production environment is running on RAC, prod1 and prod2 are the cluster members.
We need to restore the production data to a testing server test1 for application testing, if test1 uses the same NetBackup master and media servers, we can restore the data using this rman code

set dbid = 1234567890;
connect target /
connect catalog rmanid/rmanpass@rmancatalog

RUN {
allocate channel c1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=prod1)' maxopenfiles 10;
allocate channel c2 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=prod2)' maxopenfiles 10;
set until time "TO_DATE('2012-03-21 12:30:00', 'YYYY-MM-DD HH24:MI:SS')";
restore controlfile;
sql 'alter database mount';
restore database;
recover database;

release channel c1;

release channel c2;

 }

But if we try the restore directly, we will get this error:
ERROR: client is not validated to perform the requested operation.

Before restoration, we have to allow test1 to restore from the backup made on prod1 and prod2. To do this, you need log on to the backup master server. In directory /usr/openv/netbackup/db/altnames, create a file named test1, in file test1, put prod1 and prod2 as the content.

$ cat /usr/openv/netbackup/db/altnames/test1
prod1
prod2
$