AristaDBA's Oracle Blog….

An Oracle Blog Of A Learner….

AristaDBA's Oracle Blog....

Using PRELIM Option Of SQL*PLUS….

A recent thread in the Database General Questions place was posted today. The original poster set some wrong values for the database memory related parameters and using those exceptionally large values, database got hung. Well, I don’t know whether that was the real issue or not as the original poster didn’t share the contents of the alert.log file of his database neither shared any other details. What he wanted to know that how he can edit the values of those memory parameters for SGA and PGA. There were few suggestions given by the other Oracle community members. What I had suggested is an option of sql*plus which probably can be very handy for such situation i.e. when the database has completely gone hung-PRELIM. Now, a disclaimer, this is not a documented option and not really meant for to be used in general. The major purpose of having this option is to have get into the SQL*PLUS to connect to the SGA, without opening a sqlplus session and further execute commands like ORADEBUG etc. to diagnose what’s the issue probably is that the database has gone hung. So as I normally say-not documented probably means not supported as well-ergo , be careful.

Now, what could be done in the context of the above thread is that using this PRELIM option, we can get into the database, shutdown the database and once it is down, we can create a PFILE from the SPFILE. Once we have the PFILE,we can edit it and restart the database with the edited options.

Here is a small demo of the same,

[oracle@test12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 29 09:32:05 2015

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  578813952 bytes

Fixed Size            2926952 bytes

Variable Size          461375128 bytes

Database Buffers      109051904 bytes

Redo Buffers            5459968 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@test12 ~]$ sqlplus -prelim “/as sysdba”

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 29 09:32:22 2015

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

SQL> shut abort

ORACLE instance shut down.

SQL> create pfile=’/tmp/pfile.ora’ from spfile;

File created.

SQL>

How good to see the message-File Created , isn’t it :=)  ?

Happy holidays everyone!

Reference: http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so