ora-16196 database has been previously opened and closed

ora-16196 database has been previously opened and closed

There are two terms that, when used in an Oracle context, seem to cause a great deal of confusion: ‘Instance’ and ‘Database’. In Oracle terminology, the definitions of these terms is as follows:

Database: A collection of physical operating system files or disk. When using Oracle 10g or above, Automatic Storage Management (ASM) or RAW partitions, the database may not appear as individual separate files in the operating system, but the definition remains the same.

Instance: A set of Oracle background processes/threads and a shared memory area, which is the memory that is shared across those threads/processes running on a single computer.  This is the place to maintain volatile, non persistent stuff (some of which gets flushed to disk).

A database instance can exist without any disk storage whatsoever. It might not be the most useful thing in the world, but thinking about it that way will definitely help draw the line between the instance and the database.

The two terms are sometimes used interchangeably, but they embrace very different concepts. The relationship between them is that a database may be mounted and opened by many instances. An instance may mount and open a single database at any point in time. In fact, it is true to say that an instance will mount and open at most a single database in its entire lifetime!

This restriction of an instance that is it will mount and open at most a single database in its entire lifetime cause above error to come into picture.

ORA-16196: database has been previously opened and closed
Cause: The instance has already opened and closed the database, which is allowed only once in its lifetime.
Action: Shut down the instance and startup again

Once the db is closed, the underlying memory structures and redo logfiles and data files are closed. Though the control file remains mounted but because the data file headers are not open any more, there can't be any sort of information exchange between both. In order to open the files, the control file has to make sure that the data files, redo logs are in place according to the info stored in it. Thus oracle can't just let the db be going towards the open stage, it has to make this check of the file existence and confirming that only, db is allowed to open. That's why the db cannot be put as open once closed. You have to shutdown the instance and startup again.

Let’s Take an Example here:

SQL> select name, open_mode from v$database

  2  /

NAME    OPEN_MODE

--------- --------------------

MYNEWDB   READ WRITE

SQL> select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE                   

  2  from v$instance

  3  /

INSTANCE_NAME     STATUS       DATABASE_STATUS ACTIVE_ST

---------------- ------------ ----------------- ---------

mynewdb      OPEN       ACTIVE            NORMAL

SQL> alter database close

  2  /

Database altered.

SQL> select name, open_mode from v$database

  2  /

NAME    OPEN_MODE

--------- --------------------

MYNEWDB   MOUNTED

SQL> select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE

  2  from v$instance

  3  /

INSTANCE_NAME     STATUS       DATABASE_STATUS ACTIVE_ST

---------------- ------------ ----------------- ---------

mynewdb      MOUNTED      ACTIVE          NORMAL

[You see that even if we have closed the database but DATABASE_STATUS still showing as active and hence this same instance cannot open same or another database. You have to shutdown the instance and startup again]

SQL> alter database open

  2  /

alter database open

*

ERROR at line 1:

ORA-16196: database has been previously opened and closed

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area  472887296 bytes

Fixed Size            1337296 bytes

Variable Size             331352112 bytes

Database Buffers    134217728 bytes

Redo Buffers                5980160 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL> select name, open_mode from v$database

  2  /

NAME    OPEN_MODE

--------- --------------------

MYNEWDB   READ WRITE

SQL> disconn