ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0

One of the common error and below is one of the reason for this error,


In the Environment where you have more than one oracle Version ,this can happen.
If the wrong oracle home is set and if you try to login to the database of different version than the ORACLE_HOME set, you will be getting the below error.

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 20 07:18:44 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select count(1) from v$database;

select count(1) from v$database

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

Set the Right ORACLE_HOME and it will solve the issue

Range values {1..n} in FOR Loop doesn't work in AIX,HP,SUN ...

The FOR Loop with range values is specially for the BASH shell.
So when you get to other OS ,if the BASH is not set then you will not be getting the expected error.


Code :

echo Shell:: $SHELL
for range in {1..3}
do
echo "Range: $range"
done;

OUTPUT :

Linux	AIX	SUN
Shell::/bin/ksh    Range: 1   Range: 2   Range: 3	Shell::/bin/ksh  Range: {1..3}	Shell::/bin/ksh  Range: {1..3}

How to overcome this issue :

Add the "#!/bin/bash" . (find the path of the bash in other os and add it in the script.)

which bash

/usr/bin/bash

Code :

#!/usr/bin/bash

echo Shell::$SHELL

for range in {1..3}

do

echo "Range: $range"

done;

OUTPUT : AIX 

Shell::/bin/ksh

Range: 1

Range: 2

Range: 3

Running Sql in a single file

More often we get to the scenario where  100's of files in a directory or a list of files mentioned in a txt file and we need to run them. The way that comes to mind spontanesously is to run one by one, but how about run it in a single shot where you can know what you are running and also can spool it ,so that you know which one errored. Below are the two approaches where you can create a single file and run it in the sqlplus with spool. For files in a directory Get to the directory where files are (example : /home/srini/function ) In the command prompt run the following  >runall.sql for i in `ls -1` do echo "PROMPT Running File $i ...." >>runall.sql echo "@@$i" >>runall.sql ## echo "@$PWD$i" >>runall.sql -- For having the full path of the sql done; Run the runall.sql in the Sqlplus . For files in a text File Go to the location of the file having the list of all .sql (example : example : /home/srini/function.txt ) In the command prompt run the following  >runall.sql for i in `cat package.dat` do echo "PROMPT Running File $i . ..." >>runall.sql echo "@@$i" >>runall.sql ## echo "@$PWD$i" >>runall.sql -- For having the full path of the sql done; Copy this runall.sql to the location where you have all the .sql and run it in sqlplus

NULL = NULL ::: sys_op_map_nonnull

Today came across this feature " sys_op_map_nonnull " which allows you to compare NULL = NULL .

Lets make our hands dirty .......

Create the Table :

create table p2 (
c number,
d number primary key);



>desc p2
 Name         Null?    Type
 ---------- -------- ----------------
 C                     NUMBER
 D            NOT NULL NUMBER

Insert the Data :

insert into p1 values (NULL,2);

insert into p2 values (NULL,1);

insert into p2 values (NULL,3);

insert into p2 values (2,4);

>select * from p2;

         C          D

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

                    1

                    3

         2          4

Select the data by comparing column C with Column C :

>select * from p2 where c=c;

         C          D

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

         2          4

one of the Traditional Way to resolve NULL compare:

>select * from p2 where (c=c or (c is null and c is null));

         C          D

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

                    1

                    3

         2          4

Now try with sys_op_map_nonnull,

>select * from p2 where sys_op_map_nonnull(c)=sys_op_map_nonnull(c);

         C          D

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

                    1

                    3

         2          4

Variable Scope - PIPE and WHILE LOOP

The article discusses about the scope of a variable in a While loop (when using PIPE):

Code:

Test="ONE"
echo $Test|while read Test
do
if [ "$Test" = "ONE" ]
then
Test="Two"
echo "INSIDE LOOP :TEST= $Test"
break;
fi
done;
echo "OUTSIDE LOOP :TEST=$Test"

OUTPUT :
INSIDE LOOP :TEST= Two
OUTSIDE LOOP :TEST=ONE

The expected output is "Two" ,but it came as "ONE".
 This is due to the PIPE "|" used in "echo $Test|while read Test".
The PIPE runs the WHILE in a subshell which makes the scope of the WHILE Loop Local
resulting in the OUTPUT "Two".

How to resolve this ?

Code :

Test="ONE"
echo $Test>/tmp/sri.txt
while read Test
do
if [ "$Test" = "ONE" ]
then
Test="Two"
echo "INSIDE LOOP :TEST= $Test"
break;
fi
done </tmp/sri.txt
echo "OUTSIDE LOOP :TEST=$Test"

OUTPUT:
INSIDE LOOP :TEST= Two
OUTSIDE LOOP :TEST=Two

To resolve this, one of the workaround is to gat the date in to file and input the file to the While loop.

Generate & Analyze System State Dump

Steps to Generate the System State Dump 

• System State Dump on particular Session
• Get the ospid of the session using the below sql,

               SELECT S.SID||'/'||S.SERIAL# "SID/SERIAL", S.USERNAME,S.OSUSER,P.SPID "OS PID"

          FROM V$SESSION S,V$PROCESS P

          WHERE

              S.PADDR=P.ADDR

              AND S.SID=<SID>;

$ sqlplus "/as sysdba"
oradebug setospid <process ID>
oradebug unlimit
oradebug dump systemstate <LEVEL>

-- Do repeat the last step for atleast 3 times  in an interval of 5-10 minutes.
exit

LEVEL:

1              Very basic process information only

2              process + session state objects

10            Most common level - includes state object trees for all processes

Level+256 Adding 256 to the level will try to dump short stack info for each process.

• System State Dump when not able to login to Database,

$ sqlplus -prelim "/as sysdba"

oradebug setmypid
oradebug unlimit
oradebug dump systemstate <LEVEL>

-- Do repeat the last step for atleast 3 times  in an interval of 5-10 minutes.

exit

The Trace Files will get generated in USER_DUMP_DEST .

For analyzing the Trace you can use the aas.awk in the below link,

http://dba.5341.com/msg/34515.html  ( http://www.speakeasy.net/~jwilton/ass.awk )

ORA-28575: unable to open RPC connection to external procedure agent

One of the common Error with EXTPROC connection is ORA-28575 .
The Below article will suit for the server having 9i/10g or 9i/10g/11g installed .
In this case you will be having extproc for each Version running in different port and listener.


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (PROTOCOL_STACK = (PRESENTATION = TTC)(SESSION = NS))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <<HOST>>)(PORT = 1524))
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key_1))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc1)
      (ORACLE_HOME = <<ORACLE9i_HOME>>)
      (ENVS='EXTPROC_DLLS=ANY')
    ) )
LISTENER_10g =

  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (PROTOCOL_STACK = (PRESENTATION = TTC)(SESSION = NS))
      (ADDRESS = (PROTOCOL = TCP) (HOST = <<HOST>>) (PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC) (KEY = extproc_key_2))
    )
  )
SID_LIST_LISTENER_10g =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc2)
      (ORACLE_HOME = <<ORACLE_10g_HOME>>)
      (ENVS='EXTPROC_DLLS=ANY')
    )
  )

And while connecting to the extproc you will get the bleow error,

ORA-28575: unable to open RPC connection to external procedure agent

One of the solution to this error is ,

• Stop the listener
• Set the TNS_ADMIN pointing to the correct EXTPROC
• Start the listener

Testing EXTPROC : Metalink 47484.1