This content has been marked as final. Show 12 replies
Can anybody please help in solving or guiding what can be done in order ot bring the db up quickly.Do you have a good Backup of this Database ?
If yes, you may try to execute this from MS-DOS:
Then, check the alert.log and post an extract of the last 50 lines so that we can see it.
set ORACLE_SID=<SID> sqlplus /nolog connect / as sysdba startup force exit
Hope this help.
Will this command execution ensure my data is not lost.The startup force will cause a shutdown abort and a startup. So, you can execute it, the Database will use the online redo logs to start. However it's not the normal way to start a Database.
On the other hand, I don't know how is your Database now and, in this kind of situation, it's always better to have a BACKUP. It's even more necessary if you cannot loose data.
Hope this help.
If you are using 399 out of 400 allowed sessions I would start by trying to kill some of the sessions using alter system kill session 'sid,serial#'
If that does not work then shutdown abort followed by a start up should after crash recovery completes clean up the problem on a temporarily basis. The issue being why do you have so many processes?
There have been numerous reported cases of connection pooling software (web server) spawing more processes when the database was slow resulting in slowing the database even more which in turn causes more connections to be spawned until the maximum number of processes exist.
I have also seen .net code spawn a new connection every time through a loop where the developer had rigged up connection reuse but it was not working as expected.
Another major question is why do you not have a backup of the database? The primary job of a DBA is to be able to restore the database in the event of a diaster. A DBA who fails in this task should not have a job unless management has failed to provide requested resource necessary to perform the backup such failure to provide adequate disk space.
If this is test then an export is likely good enough to qualify as a backup.
-- Mark D Powell --
Thanks Mark for the suggestion. I would go for back-up . I have the back up of the db but it some days old. I would need to take the fresh backup of the DB.
We have already tried to kill session by using the command alter system kill session 'sid,serial#' but still no of processes in v$session_limit is not gettign reduced.
As i Had already mentioned in my thread, we are not able to issue command shutd immediate and are able to see thread which is marked as killed . Even when we are issuign command select * from v$session we are abel to see no of connections as such and no connections are gettign reduced.
Can You Please elaborate on this.
Sorry for the delay in response but I notice a couple of others did respond. Here are a couple of suggestions if the problem comes back.
Stop the listener to prevent remote clients from getting sessions while you make whatever changes you need to protect/fix the database.
Stop and re-start the Oracle service(s) to eliminate and clean up the existing sessions.
HTH -- Mark D Powell --
figure out the machine and program that mounts DoS attack.
select machine, program, count(*) from v$session group by machine, program order by 3;
get one with largest #.
go to the machine and kill the program to stop the attack.
then ether restart DB or run
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where machine=<machine> and program=<program> ;
One assumes you have the database in archivelog mode? (... since you don't backup often)
Please paste the recent entries from the alert log showing the errors relating to this as already requested.
Once you get your db up and running again and identify which user(s) were the culprit, you might want to look at altering their profile to limit their sessions.
The last time I had this happen to me, it was the DBSNMP (grid agent) user. My browser had hung, but something had obviously had gone beserk in the background.
we kill the session which were able but that all are showing in KILLED state and not disappearing
You want to DISCONNECT the sessions to get rid of the now; not kill them.
See the DISCONNECT SESSION Clause of ALTER SYSTEM in the SQL language Reference
DISCONNECT SESSION Clause
Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever).
See the KILL SESSION Clause for the difference.
A DISCONNECT terminates the process now and frees the associated resources while KILL says wait for things to complete. Similar to the difference between SHUTDOWN IMMEDIATE and SHUTDOWN ABORT.
By the way are there any logon triggers that are active?