More on Compress/Uncompress

March 27, 2008

Only very lately I figured out that there is a way to compress/uncompress files to a different directory / location/ folder on UNIX.  This is very important while taking a compressed OS backup or restoring compressed files from backup.

To compress to a different folder / locaiton :

compress -c /appl01/orabkup/system01.dbf > /u01/oradata/ORCL/ORCL_system01.dbf.Z
compress < /appl01/orabkup/system01.dbf > /u01/oradata/ORCL/ORCL_system01.dbf.Z

To uncompress to a different folder / locaiton :

uncompress -c /appl01/orabkup/ORCL_system01.dbf.Z > /u01/oradata/ORCL/system01.dbf
uncompress < /appl01/orabkup/ORCL_system01.dbf.Z > /u01/oradata/ORCL/system01.dbf

This may be a very primitive one, but I learned it very recently. Hope it helps you!


10g Upgrade – issues with DBMS_STATS package

January 28, 2008

Last week, one of our clients production database was upgraded from 9i to 10g.  Since then they were facing lot of performance problems.  They cannot pin point the problem at a particular place, so the problem seems to be “overall”.  I did not have access to the production box, all I had was an AWR report generated during the time of performance degradation.  When I dug into the report, I found that the instance performance is generally ok.  The problem was with most of the application queries – too many physical reads.

I remember I read an article from Jonathan Lewis on his blog regarding performance degradation after 10g upgrade.  I quickly went there and did a search and found out this URL, excellently explaining the change in the behaviour of dbms_stats package.  In short.

“If you didn’t specify a method_opt in your scripts under 9i you were not generating histograms; but under 10g you will be collecting histograms on any columns that Oracle thinks might be suitable candidates.” 

I then checked the AWR report to see if there was anything recorded regarding DBMS_STATS, indeed I found out that they were using defualt method_opt

OwnName => 'ADMIN',tabname=>'T2838',estimate_percent => dbms_stats.auto_sample_size,
Granularity => 'DEFAULT',Degree => 4)

Which means they were generating histograms, but on 9i the same script would not have generated them.  This could be having a major impact on generating execution plans on 10g.  I replied them this, providing the Jonathan Lewis’ URL.

Later, they did reset the METHOD_OPT to 9i levels.

exec dbms_stats.set_param('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1');

I am waiting to hear from them on whether there is any improvement in performance.

Note:  I later found Richard Foote has also written an excellent article on this :

Database refresh using cold backup

January 28, 2008

Sometime, you will be asked to refresh an UAT database using an coldbackup of production one.  Here are the steps :

1.  Prepare controlfile creation script.  This can be done by

alter database backup controlfile to trace as '/export/home/oracle/ctlfile.sql';

2.  View the edit ctlfile.sql, so that it should contain the following

  GROUP 1 '/u01/oradata/UATDB/redo01.log' SIZE 200M,
  GROUP 2 '/u02/oradata/UATDB/redo02.log' SIZE 200M,
  GROUP 3 '/u03/oradata/UATDB/redo03.log' SIZE 200M

3. Gernerate script to create TEMP tablespace

set long 2000

4. Shutdown UATDB cleanly

shutdown immediate

5. Copy the cold backup files from production to UAT box.

6. Start the database in nomount state
startup nomount

7. Run the create control file script


8. Open the database with resetlogs

alter database open resetlogs;

9. Create temp tablespace from the script generated from step 3:

'/u01/oradata/UATDB/TEMP01.dbf' SIZE 5242880000

Extracting one or more files from compressed tar archive

January 26, 2008

As a oracle DBA you will come across extracting files from compressed tar archive files like *.gz/*.Z normally compressed through gzip or UNIX compress utility.  You can uncompress them and extract the tar file.  But it is a two step process and you need more diskspace to do this (twice the amount nearly) .  Instead you can do them in one step. 

How to do this with gzip is clearly demonstrated in here in this URL

gzip -dc t.tar.gz | tar -tvf -

That saves you lot of disk space. 

 Now lets try the same with UNIX uncompress utility.

I have 3 text files and I add them to t.tar
$ ls -ltr
total 146
-rw-r--r-- 1 oracle dba 6 Jan 26 05:28 t1.txt
-rw-r--r-- 1 oracle dba 6 Jan 26 05:28 t2.txt
-rw-r--r-- 1 oracle dba 6 Jan 26 05:28 t3.txt
$ tar -cvf t.tar *.txt
a t1.txt 1K
a t2.txt 1K
a t3.txt 1K

Now compress them using UNIX compress command

$ compress t.tar
$ ls -ltr
total 148
-rw-r--r-- 1 oracle dba 296 Jan 26 05:31 t.tar.Z

Try the uncompress command

$ uncompress -c t.tar.Z | tar -tvf -
tar: blocksize = 8
-rw-r--r-- 101/201 6 Jan 26 05:28 2008 t1.txt
-rw-r--r-- 101/201 6 Jan 26 05:28 2008 t2.txt
-rw-r--r-- 101/201 6 Jan 26 05:28 2008 t3.txt

Yes!! it really works!  If you want to extract just one file, you can do the below

$ uncompress -c t.tar.Z | tar -xvf - t1.txt
tar: blocksize = 8
x t1.txt, 6 bytes, 1 tape blocks
$ ls -ltr
total 144
-rw-r--r-- 1 oracle dba 6 Jan 26 05:28 t1.txt
-rw-r--r-- 1 oracle dba 296 Jan 26 05:31 t.tar.Z

Configuring wireless network

January 11, 2008

I have my ADSL modem and PC on the upstairs of my home. It is good, because I am isolated and work in peace when needed.  But carrying laptop to upstairs can sometime be tough, especially you need to work for a short duration.  So I thought of configuring wireless network for this purpose. Yesterday I did configure my HUAWEI modem WA1003A – BSNL broad band to work wireless.  Just thought I would give it in steps so that it can help someone.  

Router/Modem configuration:

1.  Router type WA1003A HUAWEI.  Brodband vendor – BSNL Chennai
2.  Log on to the admin website from a PC that is connected to network.  Usually it will be
3.  On the top menu, click “Setup”.  On the left-hand side menu click “Wireless”. 
4.  Check “Enable AP”. Give a name for SSID.  The SSID uniquely identifies your wireless network. Click “Apply”
5.  On the top menu, click “Advanced”.  On the left-hand side menu click “Wireless Security”.
6.  Select the kind of security you wish to have.  Though this is optional, it is absolutely necessary to have this.
    You don’t want an intruder connected to your router.
7.  Check “WEP” option.
8.  Check “Enable WEP Wireless Security”
9.  Select Authentication Type “Shared”
10. Give an encryption key in hexa decimal.  Select cipher as “64 bits”
11. Apply the changes.
12. Go to Tool (top menu) -> System commands (side menu) -> Save all.  After saving, click restart.
13. Now your router will be restarted.  The wireless LED on the modem should be on now.

Laptop Configuration:

1.  Enable wireless.  On my Dell D610 laptop it is the combination of “Fn” + “F2” keys.
2.  Go to Start -> My Network Places.
3.  Right click “Wireless Network Connection” -> View available wireless networks.
4.  You should find your wireless network there. Select it and click connect.
5.  You will be asked to enter the key.  Enter the hexa decimal key you have set while configuring the modem.
6.  That’s it, you are done!!! I always use DHCP, so no need to bother about IP addresses.

Hope this helps you!!

It served the purpose..

January 8, 2008

Couple of months back, I was asked to run a script on UAT as part of a change deployment.  When I looked into it, I got shocked.  You can see yourself why..

REM    abc.sql - Delete Contact
REM       ********************************************************
PROMPT    *** abc.sql ...                                      ***
set define off
set serveroutput on
delete from XXXXXXXX where EMAIL='';
dbms_output.put_line('*** Script Confirmation : Property Deleted from XXXXXXXX ***');
  when others then
    dbms_output.put_line('*** Error: sqlcode: ' || sqlcode || '; sqlerrm: ' || substr(sqlerrm, 1, 100) || ' ***');
PROMPT    *** abc.sql ... completed                            ***
REM       ********************************************************

If you notice, there is no DECLARE block, no BEGIN block, only exception block!!!!
I asked the developer how it didn’t give errors on development environment? He told “yes, but the record got deleted and commit was done.  That’s all I want.  It served the purpose.”
He was one among those who attended training on PL/SQL earlier that month.  Needless to say, he has learnt the “Error handling” very well, at least.

OEL on VMWare

January 8, 2008

Finally sometime back, I have completed installing Oracle Enterprise Linux on VMWare server.  The installation is quite simple and straight forward.  I just followed the steps given by Frederik (Frikkie) Visser in .  It is an excellent article that tells you step by step not only how to install linux on VMWare but also configuring RAC.  A must read for a DBA. Thanks Frikkie.

My first reaction to OEL is, it is bulky and a bit slow.  But I have allocated only 800 MB or memory to it.  May be I should throw more memory to it and see how it behaves.

My next step would be to download Oracle 11g for Linux ans start installing it.