Andrew's Oracle Blog: sqlplus -prelim

Monday, May 11, 2015

ORA-01075

I tried to log on to an Oracle 11.2 test database, which the developers could no longer use, and saw the following error:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:41:24 2014

ERROR:

ORA-01075: you are currently logged on

Enter user-name:

I looked in the alert log and saw several of the following messages:

Fri Mar 28 09:36:46 2014

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /oracle/app/oracle/product/diag/rdbms/mdmdev1/MDMDEV1/trace/MDMDEV1_smon_522.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select count(*) from edition$","sga heap(1,0)","kglsim object batch")

I closed the database like this:

MDMDEV1 /export/home/oracle > sqlplus -prelim

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:45:45 2014

Enter user-name: / as sysdba

SQL> shutdown abort

ORACLE instance shut down.

SQL>

Then I opened the database again:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:49:39 2014

Connected to an idle instance.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2225832 bytes

Variable Size 390072664 bytes

Database Buffers 134217728 bytes

Redo Buffers 7946240 bytes

Database mounted.

Database opened.

SQL>

... and the developers could login again.

Friday, May 23, 2014

If all the available processes in a database are used up, you will get an ORA-00020 when you try to login. The best way round this is to get some people to log out. However, this may not be possible e.g. if people have logged in from some application then killed it, leaving their sessions alive in the database but with no way to get back to them and end them tidily.

Oracle 11: sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:40 2013

ERROR:

ORA-00020: maximum number of processes (100) exceeded

Enter user-name: ^C

Oracle 11:

An alternative is to login with the –prelim option. This does not allow you to run any SQL but it does let you close the database:

Oracle 11: sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:59 2013

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

SQL> shutdown abort

ORACLE instance shut down.

SQL>

Before opening the database again, you might consider increasing the processes parameter to reduce the chance of this happening again.