Friday, June 18, 2010

I/O Errors using NFS on Netapps storage on Oracle 10.2.0.4

I recently faced the following issue, on one of my client environment.

After successfully creating a 10g database using NFS file system on a Linux OS, I turned it over to the client for production implementation. Developers started populating the data using Schema level import. When a large table is imported, the import failed with the following errors

ORA-01115: IO error reading block from file 6 (block # 5631)
ORA-01110: data file 6: '/u0201/oradata/reportdata.dbf'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 5630

After searching several hours on Oracle Support, found a document that talked about setting up filesystemio_options to 'setall' on NFS file systems for redo log corruption. We did not have any corruption, but I wanted to try it anyway because since we were using NFS.

Alter system set filesystemio_options=setall scope=spfile;

After setting this, developers did the imports successfully without any problem.


Parameter: FILESYSTEMIO_OPTIONS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"directIO" - This allows directIO to be used where supported by the OS. Direct IO bypasses any Unix buffer cache. As of 10.2 most platforms will try to use "directio" option for NFS mounted disks (and will also check NFS attributes are sensible).
"setall" - Enables both ASYNC and DIRECT IO. The 'setall' option chooses async or direct I/O based on the platform and file system used.
Enabling directio against nfs datafiles/redo logs should always be used so that no writes are ever cached in the os buffer cache. The consistency of writes cannot be guaranteed when relying on stateless nfs to perform asynchronous writes over the network.

Saturday, June 05, 2010

Size of RMAN incremental backups

We had a situation recently where backup destination on a Linux server was 97%. Our system admin, wanted to know why? because RMAN retention has not changed, we wanted to know the db did actually grew that big to make that difference. We were taking Lavel 0 backups every Sunday followed by Level 1 and 2 backups during the week and Level 0 again next Sunday.

We were keeping 45 days worth of backups on disk. Now, we needed to find out the size of backups sets every week and then calculate which week had a enormous growth in size. Messy way of doing this is, at the UNIX level, we can issue, ls -ltr and then total the backups belonging to each week. The database was 500Gb and each backupsets had different sizes. It was a cumbersome effort.

How did we do this elegantly in RMAN? There is a view called rc_backup_datafile that you can use fir this purpose.

First Week

select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('04-APR-2010') and ('10-APR-2010')
order by 1

SQL> /

SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)

---------------------------------------
255.587982

Second week

select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('11-APR-2010') and ('17-APR-2010')
order by 1
SQL> /



SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)
---------------------------------------
255.705849


select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('26-APR-2010') and ('03-MAY-2010')
order by 1

SQL> /


SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)
---------------------------------------
283.666035


select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('04-MAY-2010') and ('11-MAY-2010')
order by 1

SQL> /


SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)
---------------------------------------
279.676537

Ok, between 26th Apr and 3rd May, the backups had grown almost 30Gb. It was admitted to me , there indeed was a major upload that happened that week, which would explain the sudden increase in backup directory usage.

Ok, demand for more disk was justified!

Dbca hangs while creating the database

I came across the following weird issue with dbca, that I wanted to share with others.
I had to create a database on a Linux x86_64 server. When I ran ./dbca , the screens ran through and I answered all the questions and then clicked the last step that creates the database.

dbca just was hung , did not proceed. There was no logs, no errors just nothing! I was puzzled and didn't know where to start digging. Finally there was a problem with my Xming utility, I was missing several font files that oracle needs. AFter reinstalling the X utilities by the sys admin, dbca finished to completion.

So if you are stuck with unexplainable issue with dbca, check your X windows utilities and make sure you have all the required set.