How do I resolve this "ORA-01109: database not open" error?

Question

I'm trying to create my own database using SQLPlus. So first I log into it as admin:

sqlplus sys/sys_password as sysdba

And then I try to create a new user, called sqlzoo :

CREATE USER sqlzoo IDENTIFIED BY sqlzoo 
DEFAULT TABLESPACE tbs_perm_01sqlzoo 
TEMPORARY TABLESPACE tbs_perm_01sqlzoo 
QUOTA 20M ON tbs_perm_01sqlzoo;

This gives me the following error :

ERROR at line 1:
ORA-01109: database not open

Why is it giving me such an error?

score 39 · Accepted Answer · edited Mar 21 '20 at 07:39

39

As the error states - the database is not open - it was previously shut down, and someone left it in the middle of the startup process. They may either be intentional, or unintentional (i.e., it was supposed to be open, but failed to do so).

Assuming that's nothing wrong with the database itself, you could open it with a simple statement:(Since the question is asked specifically in the context of SQLPlus, kindly remember to put a statement terminator(Semicolon) at the end mandatorily, otherwise, it will result in an error.)

ALTER DATABASE OPEN;

edited Mar 21 '20 at 07:39

Sreedhar S

699
9
21

answered Dec 03 '14 at 19:54

Mureinik

297,002
52
306
350

Thanks !! For some reason, when I entered that command it tells me "Oracle not available" .. not sure – Caffeinated Dec 03 '14 at 19:55
I think I should avoid using `sysdba` user? – Caffeinated Dec 03 '14 at 19:59
2

please note that without the semicolon (;) at the end of the statement this will not work (at least from inside the SqlPlus) – Hakan Fıstık Sep 06 '16 at 15:17
Oracle. Where "STARTUP" doesn't "start up" a database and you're expected to know to do this as well... – Jobbo May 15 '19 at 14:14
Its already opened but still getting the same error while making connection in Oracle SQL Developer software. https://stackoverflow.com/questions/70706962/oracle-db-service-is-already-opened-but-still-getting-database-not-open-error – Kamlesh Jan 14 '22 at 07:36

Renhuai · Answer 2 · 2020-08-06T22:02:08.467

I got a same problem. Below is how I solved the problem. I am working on an oracle database 12c pluggable database(pdb) on a windows 10.

-- using sqlplus to login as sysdba from a terminal; Below is an example:

sqlplus sys/@orclpdb as sysdba

-- First check your database status;

SQL> select name, open_mode from v$pdbs;

-- It shows the database is mounted in my case. If yours is not mounted, you should mount the database first.

-- Next open the database for read/write

SQL> ALTER PLUGGABLE DATABASE OPEN; (or ALTER PLUGGABLE DATABASE YOURDATABASENAME OPEN;)

-- Check the status again.

SQL> select name, open_mode from v$pdbs;

-- Now your dababase should be open for read/write and you should be able to create schemas, etc.

score 5 · Answer 3 · answered Jan 15 '21 at 08:27

If you are using 19c then just follow the following steps

Login with sys user.
alter the session to the pluggable database with the following command.
SQL>alter session set container=orclpdb;
Next startup the database.
SQL>startup After that database will not show the above error.

score 4 · Answer 4 · answered Sep 20 '19 at 00:20

4

alter pluggable database orclpdb open;`

worked for me.

orclpdb is the name of pluggable database which may be different based on the individual.

answered Sep 20 '19 at 00:20

Charitha

41
1

score 3 · Answer 5 · answered Oct 21 '20 at 04:12

3

I was facing some problem from SQL PLUS Command Promt.

So I resolve this issue from windows CMD ,I follow such steps--->

1: open CMD (Windows)

2: type show pdbs;

   now u have to unmount the data base which is mounted

3: type alter pluggable database database_Name open;

4: type show pdbs;(for cross check)

It works for me

answered Oct 21 '20 at 04:12

Suraj Verma

463
6
8

2

Thanks a lot Your solution solved my problem – Hamid Mousavi Apr 06 '21 at 10:05

score 2 · Answer 6 · answered Dec 03 '14 at 19:54

2

have you tried SQL> alter database open; ? after first login?

answered Dec 03 '14 at 19:54

void

7,760
3
25
43

Hmm, I do this code, it tells me "Oracle not available" ?? – Caffeinated Dec 03 '14 at 19:56
1

if not available how you can login? check the database and listener services and try again – void Dec 03 '14 at 19:58
I think because I log in using : `sqlplus sys/sys_password as sysdba` - i think i should use normal user? – Caffeinated Dec 03 '14 at 19:59
1

SYS has all privileges, you're wellcome – void Dec 03 '14 at 20:12
Hmm, that's what I thought. So something is really strange here, I will look into – Caffeinated Dec 03 '14 at 20:13
If you're the DBA, just run SHUTDOWN, and then STARTUP, and report us what he replies. – tvCa Dec 04 '14 at 14:16
https://stackoverflow.com/questions/70706962/oracle-db-service-is-already-opened-but-still-getting-database-not-open-error – Kamlesh Jan 14 '22 at 07:36

score 1 · Answer 7 · answered Jan 02 '15 at 04:39

If your database is down then during login as SYSDBA you can assume this. While login command will be executed like sqlplus sys/sys_password as sysdba that time you will get "connected to idle instance" reply from database. This message indicates your database is down. You should need to check first alert.log file about why database is down. If you found it was downed normally then you can issue "startup" command for starting database and after that execute your create user command. If you found database is having issue like missing datafile or something else then you need to recover database first and open database for executing your create user command.

"alter database open" command only accepted by database while it is on Mount stage. If database is down then it won't accept "alter database open" command.

score 1 · Answer 8 · answered May 03 '17 at 07:29

The same problem takes me here. After all, I found that link, it's good for me.

Source link

CHECK THE STATUS OF PLUGGABLE DATABASE.

SQL> STARTUP; ORACLE instance started.

Total System Global Area 788529152 bytes Fixed Size 2929352 bytes Variable Size 541068600 bytes Database Buffers 239075328 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE ------------------------------ ---------- PDB$SEED MOUNTED PDBORCL MOUNTED PDBORCL2 MOUNTED PDBORCL1
MOUNTED

WE NEED TO START PDB$SEED PLUGGABLE DATABASE in UPGRADE STATE FOR THAT

SQL> SHUTDOWN IMMEDIATE;

Database closed. Database dismounted. ORACLE instance shut down.

SQL> STARTUP UPGRADE;

ORACLE instance started.

Total System Global Area 788529152 bytes Fixed Size 2929352 bytes Variable Size 541068600 bytes Database Buffers 239075328 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE; Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE ------------------------------ ---------- PDB$SEED MIGRATE PDBORCL MIGRATE PDBORCL2 MIGRATE PDBORCL1
MIGRATE

score 1 · Answer 9 · answered Mar 21 '20 at 03:47

As the error states - the database is not open - it was previously shut down, and someone left it in the middle of the startup process. They may either be intentional, or unintentional (i.e., it was supposed to be open, but failed to do so).

Assuming that's nothing wrong with the database itself, you could open it with a simple statement. (Since the question is asked specifically in the context of SQLPlus, kindly remember to put a statement terminator(Semicolon) at the end mandatorily, otherwise, it will result in an error. The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard.)

ALTER DATABASE OPEN;

Its already opened but still getting the same error while making connection in Oracle SQL Developer software. https://stackoverflow.com/questions/70706962/oracle-db-service-is-already-opened-but-still-getting-database-not-open-error — Kamlesh, Jan 14 '22 at 07:37

score 0 · Answer 10 · answered Jan 17 '20 at 13:01

0

please run this script

ALTER DATABASE OPEN

answered Jan 17 '20 at 13:01

harun ugur

1,718
18
18

score 0 · Answer 11 · answered Jul 07 '21 at 18:17

0

Open SQLPLUS and login with sys as sysdba.
After that run alter pluggable database <<database_name>> open;

This Worked for me .

answered Jul 07 '21 at 18:17

Saurabh parwani

31
3

score 0 · Answer 12 · answered Jan 15 '22 at 14:13

While working on SQL developer tool I received the error while connecting to my pdb

ORA-01109 database not open

I followed these steps to solve this issue:

Logged in as a sys user
ALTER DATABASE OPEN throws an error:- ORA-01531: a database already open by the instance
changed container to pdb1:- ALTER SESSION SET CONTAINER=pdb1

Problem Solved!

score -1 · Answer 13 · answered Dec 31 '20 at 14:56

-1

il faut faire

SQL> STARTUP FRORCE

answered Dec 31 '20 at 14:56

Amine MO

1

Please reply in English. Also review your answer, its probably ```STARTUP FORCE``` – Pedro Ferreira Dec 31 '20 at 15:25
1

please answr in english, it is against the site policy to answer in other languages – phoenixstudio Dec 31 '20 at 15:29

How do I resolve this "ORA-01109: database not open" error?

13 Answers13