ORA-16005

I did a shutdown abort in an Oracle 19c database:

C:\Users\Admin>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 9 20:52:49 2020
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown abort
ORACLE instance shut down.
SQL>

A shutdown abort blocks new connections and kills current sessions whether they are doing anything or not. It does not roll back terminated transactions either. As a result, Oracle has some recovery to do when the instance is restarted to make the database consistent again.

I tried to open the database in read only mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 7717518448 bytes
Fixed Size                  9284720 bytes
Variable Size            1258291200 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7491584 bytes
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

SQL>

This would have prevented the recovery from taking place so Oracle returned an ORA-16005.

I recovered the database manually:

SQL> recover database
Media recovery complete.
SQL>

I thought this might allow me to open the database in read only mode but it didn't:

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

SQL>

To open the database in read only mode, I had to open it normally, close it then reopen it as shown below:

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 7717518448 bytes
Fixed Size                  9284720 bytes
Variable Size            1258291200 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7491584 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL>

ORA-01090

The first screen print below shows 3 sessions on the same UNIX machine.

 

In the first session, I connected to ORCL as an externally identified user at 14:10:46.

 

In the second session, I connected to ORCL as SYS at 14:12:07 and issued a shutdown command. There are several ways to do this e.g. shutdown normal, shutdown transactional, shutdown immediate, shutdown abort and shutdown timeout. The default, if none of these options are specified, is shutdown normal. This causes the shutdown command to wait until all other sessions have logged out and this is what happened here.

 

Once you have run a shutdown command, Oracle does not allow other users to connect to the database. In the third session below, I tried to connect to database ORCL as an externally identified user at 14:14:13. This connection failed with an ORA-01090. As usual, click on the images to enlarge them and bring them into focus:

   

The second screen print below shows sessions 1 and 2 above a little later. The externally identified user logged out of the database in session 1 at 14:18:51. This allowed the shutdown command in session 2 to complete: