Tuesday, November 25, 2008

Last week my application was abnormal due a datafile was offline suddenly. I checked that datafile need to be recovered. And there was the good point of oracle which could recover datafile in runtime. It meant we did not need to stop running application. Below are steps to recover datafile in oracle :

1. Login as sysdba
% sqlplus / as sysdba

2. Checking datafile that needs to recover
SQL> select * from v$recover_file;
SQL> select * from v$datafile where status in ('OFFLINE', 'RECOVER', 'SYSOFF');
This must give row/rows back as result.

3. Recover and bring online datafile
SQL> recover datafile '';
SQL> alter database datafile online;

4. Re-check by execute step no 2
And it must not give row/rows back as result

It's so simple but very usefull in daily maintenance. But you must investigate further the root cause which is caused that datafile offline and need to be recovered.

Patching Oracle

That was amazing experience for me, I was first time patching oracle :). The history was starting when I found error message in bdump and cdump directory and it kept growing every time. After got solution from metalink and downloaded the patch then I began patching actvity. Please follow steps below that I've done successfully :

1. Checking the environment
- Perl
bash-2.05$ env | grep -i perl

- Current patch version
bash-2.05$ /opt/oracle/product/10.2.0/OPatch/opatch lsinventory
Invoking OPatch

Oracle interim Patch Installer version
Copyright (c) 2005, Oracle Corporation. All rights reserved..

Oracle Home : /opt/oracle/product/10.2.0
Central Inventory : /opt/oracle/orainventory
from : /var/opt/oracle/oraInst.loc
OPatch version :
OUI version :
OUI location : /opt/oracle/product/10.2.0/oui
Log file location : /opt/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2008-11-14_11-25-32AM.log

Lsinventory Output file location : /opt/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory2008-11-14_11-25-32AM.txt

Installed Top-level Products (2):

Oracle Database 10g
Oracle Database 10g Release 2 Patch Set 2
There are 2 products installed in this Oracle Home.

Interim patches (2) :

Patch 4572043 : applied on Thu Jul 10 00:31:10 WIT 2008
Created on 14 Jun 2007, 04:09:43 hrs PST8PDT
Bugs fixed:

Patch 5752399 : applied on Fri Mar 16 23:15:29 WIT 2007
Created on 12 Jan 2007, 20:07:35 hrs US/Pacific
Bugs fixed:


2. Extract patch package
bash-2.05# unzip p6168288_10203_SOLARIS64.zip
Archive: p6168288_10203_SOLARIS64.zip
creating: 6168288/
creating: 6168288/files/
creating: 6168288/files/lib/
creating: 6168288/files/lib/libserver10.a/
inflating: 6168288/files/lib/libserver10.a/evaa2g.o
creating: 6168288/etc/
creating: 6168288/etc/config/
inflating: 6168288/etc/config/inventory
inflating: 6168288/etc/config/actions
creating: 6168288/etc/xml/
inflating: 6168288/etc/xml/GenericActions.xml
inflating: 6168288/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 6168288/README.txt

3. Shutting down oracle instance
# su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate;

4. Apply patch
$ cd 6168288/
$ /opt/oracle/product/10.2.0/OPatch/opatch apply
$ exit

5. Ensure the patch has been installed properly using step 1

And finally starting up oracle instance and enjoy the change :)

Tuesday, October 28, 2008

TOAD Error

I just got error message when trying to load schema browser in TOAD. Below is the detail error message :
Module : TOAD.exe
Type : EListError
Message: 43 is an invalid PageIndex value. PageIndex must be between 0 and 42

Then I remembered that previously I installed TOAD version 9.5, but after that I re-installed with the older version, that was TOAD version 9.0. This problem was caused by TOAD read the newer configuration which was not supported by the older version.

The solution for this problem is :
1. Open toad.ini under C:\Program Files\Quest Software\Toad for Oracle\User Files, please ensure you did backup first and close TOAD application.
2. Edit and delete the section [PAGEORDERS], maybe you need delete [PAGENAMES] and [PAGESHOW] also.
3. Close it and open TOAD

Now it works properly :)