Yesterday I have done a Preventive Maintenance (PM) for Oracle database at Ministry of Education Malaysia. At the end of PM when I want to retrieve the final report for Oracle database (after compiling, analyzing etc), suddenly…the database get mad and I can’t connect to any user in that Oracle database. I can’t even conn to perfstat to retrieve the statspack. Not only perfstat user, I even try to conn to sys and system but the result is same, failed to connect. Owh god…what is this.. I have no idea about this matter..

My colleague adviced to check the error message in bdump. Shown that:

ORA-04030: out of process memory when trying to allocate #### bytes (pga heap,redo read buffer)

Damn it, what is this? I just checked that the memory is much enough for that Oracle database. Then, again I’ve asked advice from my colleague about that.

“In that case, you need to restart the Oracle database because there are too many session..bla..bla..”

“Okay, as you wish. I will restart the Oracle database”

I just follow all the thing and issuing the following command,

SQL> shutdown immediate

Then, I wait.. waiting for the following result to come out:

Database closed.
Database dismounted.
ORACLE instance shut down.

1 minute gone..
2 minutes gone..
5 minutes gone..
10 minutes gone..

Nothing happen. The only thing I can see is the last command that I type i.e. shutdown immediate

Again, I asked advice from my colleague about what really happen to that Oracle database.

Owh, perhaps there are another user still connected to the Oracle database and I need to kill their session manually. Accidentally, I have killed all session included my session that already running the shutdown immediate.

Then I got unexpected advice i.e. “Nothing you can do unless shutdown abort“

After several minutes discussed the best solution for that problem, at last I need to shutdown abort because there were nothing to do because the shutdown immediate session already stuck and hang.


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  ##### bytes
Fixed Size                  ##### bytes
Variable Size             ##### bytes
Database Buffers          ##### bytes
Redo Buffers                ##### bytes
Database mounted.
SQL> recover database
Media recovery complete.
SQL>
SQL>
SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area ##### bytes
Fixed Size ##### bytes
Variable Size ##### bytes
Database Buffers ##### bytes
Redo Buffers ##### bytes
Database mounted.
Database opened.
SQL> conn perfstat/****
Connected.
SQL> conn system/****
Connected.
SQL>

Huh!!!

I am lucky because database recovered successfully and no error came out after recovery. All DBA know the consequences of shutdown abort and the most horrible thing is if there are error come out after recover database. If and only if there are error come out, then I need to come to MOE everyday in a week or month to fix that Oracle database, and in certain cases we need to reinstall the Oracle database again.

But, for yesterday all the thing goes smoothly.. Thanks God..:)