How to Resolve ORA-01109: database not open

ORA-01109

ORA-01109 is pretty normal when the database you want to connect or operate with is at MOUNT or NOMOUNT state. In this post, we have some cases for you:

1. Operation at MOUNT or NOMOUNT
2. Connecting to a PDB
3. Connecting to a PDB on RAC
4. ALTER SYSTEM SET in Multitenant DB

Operation at MOUNT or NOMOUNT

In this case, SYSDBA users are trying to do something which should be done in OPEN but the database is at MOUNT or NOMOUNT state. The database could be a typical database (non-CDB) or a container database (CDB)

SQL> conn / as sysdba
Connected.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

When we tried to do something at MOUNT state, we got ORA-01109.

Opening a PDB

SQL> alter pluggable database orclpdb open;
alter pluggable database orclpdb open
*
ERROR at line 1:
ORA-01109: database not open

Switching Logfile

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

In fact, both operations should be done when the database or container is OPEN.

Solutions

The solution is to open the database, either a typical database or a CDB.

NOMOUNT

For not mounted databases, you can take two steps to open the database from not mounted by this.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

To distinguish what state the database is currently at, you may take a look.

MOUNT

For mounted databases, you can take one step to open the database from mounted by this.

SQL> alter database open;

Database altered.

If there's some issue to prevent you from opening the database normally, you should check the alert log of database for more details.

Some cases, for examples, at the moment after performing a point in time recovery and changing DB_NAME, the database should be opened with RESETLOGS.

SQL> alter database open resetlogs;

Database altered.

For normal users without SYSDBA privilege connect to a MOUNT typical or container database, they shall see ORA-01033: ORACLE initialization or shutdown in progress. That's the difference.

Connecting to a PDB

In this case, normal users are trying to connect to a pluggable database (PDB) at MOUNT state while its container database (CDB) is OPEN.

SQL> conn / as sysdba
Connected.
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        MOUNTED

When we tried to connect to the PDB, we got ORA-01109.

SQL> conn hr/hr@orclpdb
ERROR:
ORA-01109: database not open
...

Solution

To solve ORA-01109, we should open the PDB.

SQL> alter pluggable database orclpdb open;

Pluggable database altered.

The key point is that, CDB must be open before opening any PDB.

Connecting to a PDB on RAC

In this case, we were trying to connect to an opened PDB on RAC DB, but it sometimes, about 50% chances fails. Let's see the case.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 ORCLPDB2                       MOUNTED    NO

We decide to open the mounted PDB.

SQL> alter pluggable database ORCLPDB2 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 ORCLPDB2                       READ WRITE NO

Usually, it solves ORA-01109, but not in a cluster environment.

For example, when a client tried to connect to a PDB in a RAC container database (CDB), we saw ORA-01109 again.

SQL> conn hr/hr@ORCLPDB2
ERROR:
ORA-01109: database not open

Solution

Since the above connect identifier ORCLPDB2 points to the PDB through a SCAN IP, the connection might go to the second instance. More precisely, what PDB we opened is only on the first instance of the RAC database, the second instance still remain unopened.

To solve ORA-01109, we should open the PDB on all instances.

SQL> alter pluggable database ORCLPDB2 open instances=all;

Pluggable database altered.

Please inform the client to connect it again.

SQL> conn hr/hr@ORCLPDB2
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
ORCLCDB2

Not only opening a PDB, but also closing a PDB should be done on all instances.

ALTER SYSTEM SET in Multitenant DB

In this case, we tried to change a parameter on all containers, but it failed with ORA-01109 and ORA-00604.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set open_cursors=3000 container=all sid='*' scope=both;
alter system set open_cursors=3000 container=all sid='*' scope=both
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01109: database not open

I know what you're thinking. Yes, parameters can be changed at NOMOUNT state, there's no need to open the database. But this is a special case when we specify CONTAINER=ALL modifier in the statement. In a multitenant database, to-be modified pluggable databases should be OPEN.

Solution

You should either open the PDB just like we did in the previous 2 section.

Or, we should remove CONTAINER=ALL modifier from the statement.

SQL> alter system set open_cursors=3000 sid='*' scope=both;

System altered.

Without CONTAINER=ALL, we make all sub-containers inherit the value from the root container.