Sunday, January 27, 2008

Small change make difference

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a

SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise

MYSUN:oracle> (10.1.0.4) /usr/proc/bin

$ ps -aef grep pmon

oracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_test
oracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmon

mysun:oracle> (10.1.0.4) /usr/proc/bin

$ pwdx 2424
2424: /u01/app/oracle/product/10.1.0.4/dbs

<

But on Linux(RHEL 4 ) there is no command like “pwdx” . In linux you can check current working directory of processes ( ORACLE_HOME/dbs i.e lock file location ) in /proc/

[root@apps001 proc]# ps -aef grep pmon

oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
root 23180 13728 0 23:19 pts/2 00:00:00 grep pmon

[root@apps001 proc]# ls -l /proc/8268

total 0
dr-xr-xr-x 2 oracle oinstall 0 Jan 27 23:20 attr
-r-------- 1 oracle oinstall 0 Jan 27 23:20 auxv
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:19 cmdline
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 cwd -> /u01/app/oracle/11.1.0/dbs
-r-------- 1 oracle oinstall 0 Jan 27 23:20 environ
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracle
dr-x------ 2 oracle oinstall 0 Jan 27 23:20 fd
-rw-r--r-- 1 oracle oinstall 0 Jan 27 23:20 loginuid
-r-------- 1 oracle oinstall 0 Jan 27 23:20 maps
-rw------- 1 oracle oinstall 0 Jan 27 23:20 mem
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 mounts
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 root -> /
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:18 stat
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 statm
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:16 status
dr-xr-xr-x 3 oracle oinstall 0 Jan 27 23:20 task
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 wchan

>

<

[oracle@apps001 oracle]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ ls
hc_orcl11g.dat initdw.ora init.ora lkORCL11G orapworcl11g spfileorcl11g.ora

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

I asked her to create one small shell script to source oracle 11g environment variable ,
she wrote following script

ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~

"11g.env" 7L, 136C written

[oracle@apps001 ~]$chmod 755 11g.env
[oracle@apps001 ~]$ . ./11g.env

She called me after some time and said , after sourcing 11g environment variable , when she try to connect oracle 11g as sysdba , it says “connect to ideal instance”

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 27 23:45:42 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.

Ctl-d

[oracle@apps001 ~]$ ps -aef grep pmon
oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
oracle 24791 24446 0 23:46 pts/4 00:00:00 grep pmon

[oracle@apps001 8268]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

# It means database is up (may be nomount , mount or open mode )
#

When ran “fuser” on lock file oracle BG processes are connected to lock file, It means database is up (may be nomount , mount or open mode )

Checked alert.log , to make sure things are fine. It shows database is OPEN

[oracle@apps001 oracle]$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Sun Jan 27 23:52:16 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set editor vi
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/apps001/listener
2: diag/rdbms/orcl2/orcl2
3: diag/rdbms/stdorcl2/stdorcl2
4: diag/rdbms/orcl11g/orcl11g
Q: to quit

Please select option:4

space available in the underlying filesystem or ASM diskgroup.
2008-01-27 21:22:07.366000 +05:30
Completed: ALTER DATABASE OPEN

2008-01-27 21:22:26.499000 +05:30
Starting background process CJQ0
CJQ0 started with pid=26, OS id=8394
2008-01-27 21:23:24.231000 +05:3
Thread 1 advanced to log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl11g/redo03.log
2008-01-27 21:29:03.616000 +05:30

Finally decided to check shell script , which source 11g environment variable. There suppose to be no issue at script because we able to run “adrci” and able to see alert.log

[oracle@apps001 ~]$vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written


#
# I made little change in script i.e. removed last “/” from ORACLE_HOME
#


vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written

After I removed last “/” from ORACLE_HOME , she able to connect to database J

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sys

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 28 00:00:54 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

So small change make difference.

1 comment:

  1. Small change make difference is actually correct. In fact this implies in our daily life as well. It is really easy to make changes in your 11g database to work fine.

    oracle ebs

    ReplyDelete

Google