Connect to hanging database using sqlplus –prelim option in Oracle 10g, 11g

In real DBA life there would be several times we faced situations like not able to login to the database due to the ORA-00020 maximum number of processes exceeded etc. Database will not allow any connections during this kind hanging situations. Oracle provides an option in 10g onwards called preliminary connection.
It allows the sqlplus connection when all other connections are hanging.

Using this sqlplus –prelim you will be able to connect to the database with limited access to the SGA. Preliminary connection allows you to take diagnostic information like hanganalyze and systemstate dumps for the problem resolution. You cannot perform any sql query executions. You can perform shutdown abort and then restart your database using this option. You don’t need to reboot the server where the other instance may be running. This is the last and final option before reboot. You can try to kill some oracle user sessions to get a regular sqlplus /as sysdba session.

There are two ways to connect to sqlplus using prelim option
  1. sqlplus –prelim / as sysdba

$ sqlplus -prelim / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 18 05:35:17 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

  1. sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 18 05:37:29 2011

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

SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google