How to Resolve ORA-01109: database not open

  • by

ORA-01109

ORA-01109 is pretty normal when your database is idle, stop or close, mostly it's previously shutdown manually by someone, or the database shutdown itself by accident, you can just ask DBA for starting up the database.

If you really want to connect to a closed database, there's a way to make you connect to an idle database.

In this post, we'll about talk a more advanced topic, which is a case in a multitenant environment.

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

When a client tried to connect to the PDB, we saw ORA-01109.

C:\Users\edchen>sqlplus /nolog
...
SQL> conn hr/hr@ORCLPDB2
ERROR:
ORA-01109: database not open

Since the connect identifier points to a PDB on a RAC container, the connection might go to the second instance. That is to say, what PDB we opened is on the first instance of the RAC database only, the second instance still remain unchanged.

Solution

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.

Leave a Reply

Your email address will not be published. Required fields are marked *