Square DBA - Oracle DBA Talk

Oracle Podcasts

I am looking for a god Oracle podcast.  Do you know of any?  I did subscribe to one podcast fro OTN, but I am looking for others.  I spend about two hours in the car daily and normally devote that time to news, talk radio, and music.  I catch a couple of podcasts on photography and I thought an Oracle Podcast would be a good addition.

Managing Oracle with an iPhone

My next (personal) project will be managing my Oracle databases with an iPhone. Yes I do need to still buy an iPhone first, but that is just a small detail. I already built and Oracle DBA application that I can use from my current phone so I will just take some tweaking to work on an iPhone. Yet I am thinking about a complete recoding of the application. My current application is custom built and not really designed to handle all Oracle databases. It is specific to many of the jobs running in my data warehouse. From any web browser I can see what is running on my database and perform simple DBA tasks. While there are some nice applications out there to manage a database remotely I want to have something a bit more custom.

My current application is built with an Oracle backend (of course) and running ASP as the front end. I think my new application will be running PL/SQl pages or PHP. I may even move to Oracle Express as my backend database with Oracle Application Express (Oracle APEX). This would allow me to freely distribute the full application. No matter how I go with this new application it will be slow going. This will be a personal project and with kids, a wife, and a full-time job my new iPhone Oracle application will not be on the top of my list.

Reclaim lost space with tune2fs

File system Reserved Block Count is supposed to reduce Linux file system defragmentation, to allow root user login for maintenance or to allow Linux system logging facility to function properly in case file system running low of free disk space.

File system utility called tune2fs can be used to tune Linux ext2 / ext3 file system parameters, such as adjusting file system reserved block count, frequency of file system force-check based, maximal time between two file system checks, behavior of the kernel code when errors are detected, overriding the default ext3 journal parameters, etc.

tune2fs -l /dev/hda

or

dumpe2fs -h /dev/vg0/lvol1

could be used to inspect a file system superblock information. Take note on these fields of both tune2fs and dumpe2fs command output:

Reserved block count: 3399024
Free blocks: 4997248
Free inodes: 42473835
First block: 0
Block size: 4096

The block size is measured in byte unit. In this case, it’s 4,096 byte or 4KB. So, the Reserved Block Count: 3399024 is equal to 13,922,402,304 byte or roughly equivalent to 14GB.

Now, if execute the command

tune2fs -m 0 /dev/vg0/lvol1

will set the percentage of file system reserved block count to 0% for /dev/vg0/lvol1 file system (the first column of df command output). It’s OK to totally disable file system reserved block count, if the file system is not used by root user account or storing system log and system/program temp files (e.g. /var and /tmp). For example, file system dedicated to Oracle datafiles are safe to disable file system reserved block count.

So, if you’re running a Linux machine with 320GB storage array and haven’t tune the file system reserved block count, you may tune it now to claim back as much as 16GB of free disk space!

Moving an Oracle tablespace

Today I needed to move a tablespace between mount points. My primary mount points were starting to fill up and I needed to make some breathing room. I recently added secondary storage to the server. I say secondary because the storage just added is on less spindles and slightly slower disks. The idea is to move less accessed data to the secondary storage. One on of my first moves involves the USERS tablespace. In this particular database the USERS tablespace is simple used to store temp type tables and is not used for anything “production”. Here are the steps I followed.

In SQLPlus:

alter tablespace USERS offline;

OS Level:

cd /u01/app/oracle/oradata/dw2

cp users01.dbf /storage/oradata/dw2/users01.dbf

Back in SQLPlus:

alter tablespace USERS
rename datafile /u01/app/oracle/oradata/dw2/users01.dbf
to /storage/oradata/dw2/users01.dbf;

alter tablespace USERS online;

ReiserFS vs. ext3

Today while waiting for a new server to be setup I received a call from the system admin. He was at the point of setting up some storage shelves on the new server and had a question. ReiserFS or EXT3? In my experience, EXT3 has been the most stable and easy to recover file system. ReiserFS is supposed to be the fastest with small files. ReiserFS is only fast in Namesys benchmarks. The problem with reiser is that readdir operations don’t return the filenames in the same order they were written to disk (inode order). So if you pack thousands of small files into a directory, then try to back them up with tar, or copy them, or search through them for a string, Your IO throughput drops to nearly 0 while the hard drive furiously seeks randomly across the whole platter for each and every file. JFS and XFS don’t have this problem, and were much faster for hosting large directories of small files (the Maildir test.)

Moving tables between database

Today I needed to move a table from a 9i database to a 10g database. What do you suppose is the quickest way to get the move completed? The table is 59+ million rows with 2 indexes. The indexes were easy. I knew I would not move the indexes and simply rebuild them on the new database.

To move the actual table my first thought was to use the export and import utilities. Unfortunately I did not have enough local storage to export the data locally. So I mounted via NFS a storage array I already had online. I started the export with the following command:

exp tables=localizer DIRECT=Y INDEXES=N STATISTICS=NONE BUFFER=2000000

Over an hour later and I am still waiting, so I started to look at my other methods. Exporting to an NFS mount point can be painfully slow. Here are a few options I considered.

1. Spool to Flat File, Load with SQL*Loader or External Tables: SQL*Plus does a reasonable job of unloading. Tom Kyte has a C utility for unloading data also.
2. SQL*Plus COPY command: Sadly deprecated, and without support for all data types, but a handy tool for basic data transfers. Allows arbitrary SQL on the data selection.
3. Database Links: Direct movement from one database to another. Aside from the network latency this is basically the same as reading data from your local database. If you combine database links and materialized views, then you can replicate remote data to your local database through the databae link.
4. Export/Import: Sometimes mistaken for part of a backup and recovery strategy!
5. Transportable Tablespaces: Closely related to Export/Import, allowing movement of entire tablespaces without the need for unloading and reloading data.
6. Data Pump: Export/Import for the new millenium - and with many new features.
7. Oracle Streams: Powerful functionality here. Non-trivial, but probably the modern tool of choice for the regular propagation of data around your corporate network.

In the end since this was simply a single table I went with just insert into as a select statement across database links. I still had to deal with some network latency but it was much faster than dealing with my NFS mounted storage.

Site Features

This site is new, actually just a day old, so there is not a lot of content.  I will keep posting more everyday.  I will also add new features.  The goal is to share DBA knowledge while having fun.  So I have installed a forum as will as a Wiki.  The wiki will be used as a document repository and the forums will be used for question and answers as well as comments on my front page rants.  If you have an Oracle question, feel free to post in the Oracle Forums.

Make your data warehouse not scalable.

I’ve got 10 of them. Just in case you are wondering, you should avoid these. Unfortunately some DBAs find their data warehouse suffering from performance problems, not because there is a platform issue, but often because the features are not used or are not used correctly. The following is meant to be sarcastic and read with a bit of humor. Consider it the “Comedy of Errors” data warehouse edition. Continue reading Make your data warehouse not scalable.

OTN forums infiltrated with virus spam

Sad but true, even OTN (Oracle Technology Network) is subject to viruses.  Oracle’s OTN forums are under attack today, with spammers, posting disguised links that redirect to web pages that deploy viruses on your computer.

OTN has become NSFW, with members posting insults, personal threats of physical violence, spam, xenophobic and racist remarks, and now, destructive hyperlinks disguised as information.

Tip: Getting rid of spaces after TO_CHAR

Have you ever notice the exatra space in you output when using the TO_CHAR function?  Want to get rid of it?  Here is how.

If I run this script:

select to_char(1234.89, '$999,990.00') num_format
from dual
/
select to_char(SYSDATE, 'Day, Month DD, YYYY') date_format
from dual
/

I get:

NUM_FORMAT
------------
   $1,234.89

1 row selected.

DATE_FORMAT
-----------------------------
Friday   , August    14, 2006

1 row selected.

Do you notice the lead spacing in the first statement and the odd spacing in the second?  That can be annoying. To get rid of it, just use the FM format mask modifier.

Now I can run this:

select to_char(1234.89, 'FM$999,990.00') num_format
from dual
/
select to_char(SYSDATE, 'FMDay, Month DD, YYYY') date_format
from dual
/

And I get:

NUM_FORMAT
------------
$1,234.89   

1 row selected.

DATE_FORMAT
-----------------------------
Friday, August 14, 2006      

1 row selected.