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;
or
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
PERL5LIB=/opt/oracle/product/10.2.0/perl/lib/site_perl/5.8.3:/opt/oracle/product/10.2.0/perl/lib/site_perl/5.8.3/sun4-solaris-thread-multi/:/opt/oracle/product/10.2.0/perl/lib/5.8.3/:/opt/oracle/product/10.2.0/perl/lib/5.8.3/sun4-solaris-thread-multi/
PATH=/opt/oracle/product/10.2.0/Apache/perl/bin:/usr/bin:.:.:/bin:/usr/bin:/usr/openwin/bin:/etc:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/local/bin:/opt/VRTSvxfs/sbin:/opt/VRTSvxvm/bin:/etc/vx/bin:/opt/sfw/bin:/usr/local/bin:/opt/oracle/product/10.2.0/bin

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

Oracle interim Patch Installer version 10.2.0.3.0
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 : 10.2.0.3.0
OUI version : 10.2.0.3.0
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 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
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:
4572043

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:
5752399


--------------------------------------------------------------------------------

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 :)