Question: My Oracle instance is
not accepting SYSDBA connections. How do I connect to
Oracle to do a shutdown abort?
Answer:
If you cannot connect, you have two choices, either wait
(impacting the end users) or immediately bounce the
instance.
1 - Killing the background processes and
removing the RAM segments. See
killing oracle
instance tips.
2 - Using the -prelim SQL*Plus option to bypass creation of
a new SQL*Plus session.
Also see my notes on
hung database analysis.
The -prelim option of the SQL*Plus connect
string was introduced in 10g to bypasses the creation of a
new session in cases where the database is hung and not
accepting traditional sqlplus connections:.
$sqlplus /nolog
SQL> set _prelim
on
SQL> conn / as sysdba
Prelim connection established
SQL>
shutdown abort
Here is how to quickly generate a hang analysis immediately prior to
re-starting a hung production instance:
/u01/app/oracle/bin>sqlplus system/mypass -prelim /nolog
SQL*Plus: Release 10.2.0.2.0 - Production
SQL> oradebug
setmypid
Statement processed.
SQL> oradebug hanganalyze
12
Hang Analysis in
/u01/app/oracle/admin/mysid/udump/mysid_ora_1234.trc
You can also use the -prelim argument to capture Automated Session
History (ASH) data for a hung database:
/u01/app/oracle/bin>sqlplus system/mypass
-prelim
/nolog
SQL*Plus: Release 10.2.0.2.0 - Production
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 10
Statement processed.
>alter session set
events 'immediate trace name ashdump level 10';
$sqlplus
-prelim /nolog
SQL> connect / as
sysdba
Prelim connection established
SQL> oradebug setmypid
Statement
processed.
SQL> oradebug hanganalyze 12
Hang Analysis in
C:\oracle\admin\orcl\udump\orcl_ora_5564.trc
SQL> oradebug
dump systemstate 10
Statement processed.
SQL> oradebug
tracefile_name C:\oracle\admin\orcl\udump\orcl_ora_5598.trc
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
