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


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


Thursday, September 04, 2008

How load flat file to oracle

I just know how to load flat file into oracle database. Oracle has provided us to use both of External Table and SQL Loader. The main functionality for External table is to store data from flat file to oracle database and the SQL Loader is used for load flat file data into External table. Actually for load flat file into oracle we only need SQL Loader, but using External Table is very usefull for operation purpose

Here is a nice tutorial about external table http://www.orafaq.com/node/848
and also about SQL* Loader

Monday, August 25, 2008

Passing parameter from shell script into sql script

Today I learn something good and simple. I will create a sql script to do some of oracle statement but I will drive it from shell script. Then I will pas some parameter from shell script to sql script. Following steps are what I've done, this one only sample :

1. Create shell script as interface
#!/bin/sh
ORACLE_HOME=/opt/oracle/product/10.2.0
export ORACLE_HOME
ORACLE_SID=JAJANGDB
export ORACLE_SID
echo "Begining update activity ..."
date
$ORACLE_HOME/bin/sqlplus -S jajang/jajang @/export/home/jajang/update_prefix/update_prefix.sql 20080711170 20080712170;
echo "Ending update activity ..."
date


2. Create sql script which read input parameter from shell script
select &1, &2 from dual;
exit;


3. Run and look the result
bash-2.05# ./update_prefix.sh >> update_prefix.log
bash-2.05# tail -f update_prefix.log
Mon Aug 25 14:43:04 WIT 2008
old 1: select &1, &2 from dual
new 1: select 20080711170, 20080712170 from dual

20080711170 20080712170
----------- -----------
2.0081E+10 2.0081E+10

Ending update activity ...
Mon Aug 25 14:43:04 WIT 2008

Monday, August 11, 2008

Solaris reboot with no message

Yesterday I got problem, suddenly the solaris server was rebooting without any message left in /var/adm/messages. Usually all information related with system log exists inside. I also checked some log files in /var/log/sulog, /var/crash/, mail, using last command but none of those gave something.

But when I look at system messages when it's booting up then I found that some messages retrieve from LOM. Finally I got clue, I did show log message from ALOM directly with following steps :
1. Connect via ALOM
2. Type #. to change in ALOM mode
3. Type showlogs

Then the messages that I want appeared. It shows that something happen with system board therefore the server reboot abnormally.



Monday, August 04, 2008

Brainbench addict :)

This month I have addicted visit and try free test from Brainbench. Actually, If I have some spare times in my office then I take only free test and the result is not bad for the starter. But I have a preparation before taking the test by reading the book and getting help from Mr Google.

This transcript is the test that I've done, and fortunately I always passed those and the score is good enough. The next test I will take is about RHEL 5 (Redhat Enterprise Linux 5). I already got the manual book and still learning about that.

But the only thing is I cannot get certificate for free and only take free test :(. Let's see if there is some money then I will. And also I have dream to be get a Master cerfiticate, only if your score is more than 4.0 of 5.0. Currently my highest score only 3.99 :(


Tuesday, July 08, 2008

Firefox 3 bug ? :p

Today I get good and bad about Firefox 3. The good is I get certificate from Firefox for successfully downloading it to help set a World Record. I already put that in my desk wall :).

The bad on is a get not friendly message when access website which needs a valid certificate. Is it categorize with bug or not :P.

Please refer to http://jpsykes.com/178/firefoxs-invalid-security-certificate to look deep about the problem.

Thursday, April 03, 2008

Football Manager 2008 for free

How to get FM2008 for free ? Ridiculous question but I think you all very need it. Please let go through :
1. Getting FM2008 from http://www.bluefame.com/index.php?showtopic=50830&hl=fm2008&st=20, you need more bandwidth to download and some of links are not valid
2. Download cd simulator, you can use Freeware from M$, Mame, or Daemon Tool, and the last is working on me
3. Install and get play :)

Tuesday, April 01, 2008

Shell script and awk

This week I have play with shell script and awk. It's so interesting, remember me when I was at university. Refreshing my brain about programming language is very useful for me due I never really doing programming after I left my last company.

Still wishing get java project to level up my java language even I am javanesse :p. I am affraid my java skill which I got from 3 years experience will be gone.

Tuesday, March 25, 2008

Vi problem on Solaris

Couple days ago I was face with vi problem on solaris. When I tried to edit a file then the error message appeared "Terminal too wide". First action was going to ask Mr Google. Finally I got the solution just type on console and then tried to open vi program again. Cool it's working properly.

bash-2.05# stty columns 120

That syntax above will set terminal columns to be 120 width. The case was closed

Tuesday, January 29, 2008

3 Months is already passed

Today I get confirmation letter, It means I have passed my probation period. Thank GOD, It's my new life in new place is already beginning. Hopefully I can give my best in here.

Then the most important of life event is in front of me. I wish I can pass too like I passed this probation :)

Friday, January 04, 2008

Oracle SQL Developer with MySQL

Now, I always use Oracle SQL Developer as a tool to maintain Oracle RDBMS. It's free instead of TOAD that I used couple years ago. But today, I try to connect MySQL Server and by default this tool complains with error message "no jdbc driver for MySQL".

Then finally I found nice article to solve that problem. How is easy that and very simple. Now I can connect to Oracle and MySQL in one tool and one time. Great work from oracle developer.

http://rpbouman.blogspot.com/2007/01/oracle-sql-developer-11-supports-mysql.html

Pidgin is one for all IM

One IM tool for all, at least it supports my favourite messenger ( Yahoo and Google). With Pidgin you can communicate and chat with all your buddy either those are from yahoo or google. All buddies will appear together in one window.

There are some feature that I loved :
1. All buddies appear in one window
2. Chat history shows in one window
3. Email remainder from each account
4. and many more , I don't have much time to explore

Forget yahoo messenger, meebo , gtalk and others if you get this one.