Skip to content

How to Resolve ORA-01109: database not open

ORA-01109

ORA-01109 is pretty normal when you connected to a NOMOUNT or MOUNT database as SYSDBA and doing something which should be done under OPEN. Most likely, the database is in maintenance.

NOMOUNT Database

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

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

MOUNT Database

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

SQL> select status from v$instance;

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

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 who have no SYSDBA and connect to a MOUNT database, they see ORA-01033: ORACLE initialization or shutdown in progress.

Next, we'll talk about more advanced topics, which are cases in a multitenant environment.

  1. Open Mounted PDB
  2. PDB on RAC

A. Open Mounted PDB

Let's see what pluggable databases (PDB) we have currently.

SQL> show pdbs

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

Since the PDB is mounted, we'd like to open it.

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

It failed with ORA-01109.

Let's see the status of the container database (CDB).

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

It turns out that the root container is at MOUNT and not fully open.

Solution

We should open CDB first.

SQL> alter database open;

Database altered.

Then 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.

B. PDB on RAC

There's a situation, that you may see ORA-01109 again when you connect to a open PDB. Let's see the case.

[oracle@primary01 oracle]$ sqlplus / as sysdba
...
SQL> show pdbs;

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

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 ORCLPDB1                       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.

C:\Users\edchen>sqlplus /nolog
...
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.

2 thoughts on “How to Resolve ORA-01109: database not open”

  1. Thanks a lot for this article. I was struggling and investigating why I can’t perform an import into PDB database in RAC environment. It took me 30 minutes of thinking what’s wrong and it transpired that PDB was MOUNTED on a second node.

Leave a Reply

Your email address will not be published.