How to manually delete oracle background processes?

Hello,

I have more than 1 oracle 10g instance on the same server (solaris). I created an instance and a database via a shell script and later deleted it, also via a script (just to test my scripts) - I deleted all the datafiles, redologs, controlfiles, parameter files and some other directories for this database...I thought that deleting the parameter files would delete the instance completely, but when i issue the command "ps -ef | grep testdb", i still see the background processes running. I think this is causing problems when i try to create an instance and database with the same SID again via my script.

So how can i manually (via a shell script, not via dbca) delete all the background processes of an instance?
Thanks.

why didnt you shut it down before you deleted the files?

anyway, kill the processes then use ipcs to see if any shared memory segments are left over

you could also try

export ORACLE_SID=testdb
sqlplus "/ as sysdba"
shutdown abort


That might work

I did do a shutdown abort via my script before the deletion of files -this was the output of my script for the shutdown ...

Code:

---

SQL> SQL> SQL> SQL> SQL> Connected to an idle instance.
SQL> ORACLE instance shut down.
SQL> Disconnected

---

...so i assume that the instance was shutdown before deletion (yes, i did export the correct ORACLE_SID first).

Anyway, I tried this...
export ORACLE_SID=testdb
sqlplus "/ as sysdba"
shutdown abort
...but I cant log in to sqlplus anymore. I get these errors....

Code:

---

oracle@server ~> export ORACLE_SID=testdb
oracle@server ~> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 14 12:51:45 2006

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

ERROR:
ORA-09925: Unable to create audit trail file
SVR4 Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on


Enter user-name: sys
Enter password:
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

---

How do i kill the processes and then use ipcs (dont even know what that is)?

Using ipcs can get tricky if there is more than one instance on the machine I find. Have a read up on sysresv instead:

https://metalink.oracle.com/metalink...,1,1,helvetica

To kill the processes use kill -9 where pid is the PID column in the ps output.

I could not access the link because i do not have metalink (and didnt know the support identifier to register for it).
But I found a solution using sysresv on another forum and tried that, as shown below...

Code:

---

oracle@server ~> export ORACLE_SID=testdb
oracle@server ~> cd /u01/app/oracle/product/10.2.0/bin/
oracle@server /u01/app/oracle/product/10.2.0/bin> sysresv

IPC Resources for ORACLE_SID "testdb" :
Shared Memory:
ID              KEY
805306484      0xe9389910
Semaphores:
ID              KEY
167772287      0xf09f4914
Oracle Instance alive for sid "testdb"
oracle@server /u01/app/oracle/product/10.2.0/bin> ipcrm -m 805306484
oracle@server /u01/app/oracle/product/10.2.0/bin> ipcrm -s 167772287
oracle@server /u01/app/oracle/product/10.2.0/bin> sysresv

IPC Resources for ORACLE_SID "testdb" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "testdb"
oracle@server /u01/app/oracle/product/10.2.0/bin> cd
oracle@server ~> ps -ef | grep testdb
  oracle  3326 23776  0 14:12:05 pts/1      0:00 grep testdb

---

It looks like my instance has been completely deleted now, but how can i avoid doing this again after deleting my instance & database via a script (coz at the moment, even after a shutdown abort and then deleting all the necessary files, the background processes still remain)?

Quote:

---

Originally Posted by oraclechick

It looks like my instance has been completely deleted now, but how can i avoid doing this again after deleting my instance & database via a script (coz at the moment, even after a shutdown abort and then deleting all the necessary files, the background processes still remain)?

---

The fact that the background processes were still running indicates the database was not brought down. You may have thought you brought it down, but the evidence suggests otherwise.

This is the part of my script which does a shutdown....

Code:

---

orcl_sid=$1

run_sql ()                               
{                       
sqlplus /NOLOG <   set pagesize 0               
  set linesize 1024               
  set heading off               
  set feedback off               
  connect / as sysdba       
  $1
  exit;
EOF
}

# Shutdown database
echo "shutdown database..."
echo "`export ORACLE_SID=${orcl_sid}`"
run_sql "shutdown abort;"
echo "database shutdown"

---

Why doesnt this work, but yet shows in the output that the instance is shutdown?

Code:

---

SQL> SQL> SQL> SQL> SQL> Connected to an idle instance.
SQL> ORACLE instance shut down.
SQL> Disconnected

---

oops...looks like something went wrong there...here is my script again...

Code:

---

run_sql ()                               
{                       
sqlplus /NOLOG <   set pagesize 0               
  set linesize 1024               
  set heading off               
  set feedback off               
  connect / as sysdba       
  $1
  exit;
EOF
}

# Shutdown database
echo "shutdown database..."
echo "`export ORACLE_SID=${orcl_sid}`"
run_sql "shutdown abort;"
echo "database shutdown"

---

dont know why that keeps happening...

run_sql ()
{
sqlplus /NOLOG < set pagesize 0
set linesize 1024
set heading off
set feedback off
connect / as sysdba
$1
exit;
EOF
}

# Shutdown database
echo "shutdown database..."
echo "`export ORACLE_SID=${orcl_sid}`"
run_sql "shutdown abort;"
echo "database shutdown"

something went wrong again....it should be ...

sqlplus /nolog << EOF
set pagesize 0
.....

NOT...
sqlplus /nolog < set pagesize 0

I suspect your environment is not setup correctly. You can set an ORACLE_SID that doesn't exist and then shutdown abort and it looks just like a successful shutdown.

I have set ORACLE_BASE and ORACLE_HOME in my script and everytime i run the sqlplus function, i set ORACLE_SID, as shown below....

# VARIABLES
ORACLE_BASE="/u01/app/oracle"
ORACLE_HOME="$ORACLE_BASE/product/10.2.0"

echo "`export ORACLE_SID=${orcl_sid}`"
run_sql "startup nomount pfile='$pfile';"

Since this doesnt work, any suggestions on how to set up my environment correctly in my script?

Perhaps I'm missing something here, but are you sure you're setting your ORACLE_SID correctly:

Code:

---

austin$ echo $SHELL
/bin/bash
austin$ unset ORACLE_SID
austin$ echo $ORACLE_SID

austin$ echo "`export ORACLE_SID=TESTDB`"

austin$ echo $ORACLE_SID

austin$ export ORACLE_SID=TESTDB
austin$ echo $ORACLE_SID
TESTDB
austin$

---

I guess I was doing it the wrong way all this time. I tried it the way you showed me, and it seems to work fine now.
Thank you for your help and also thanks to the others who helped me solve my errors :)