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
Recent Comments