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

T

14

35

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?

Talkative answered 3/12, 2014 at 19:50 Comment(0)

S

41

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;

Sisson answered 3/12, 2014 at 19:54 Comment(5)

Thanks !! For some reason, when I entered that command it tells me "Oracle not available" .. not sure – Talkative 3/12, 2014 at 19:55

I think I should avoid using sysdba user? – Talkative 3/12, 2014 at 19:59

please note that without the semicolon (;) at the end of the statement this will not work (at least from inside the SqlPlus) – Arboriculture 6/9, 2016 at 15:17

Oracle. Where "STARTUP" doesn't "start up" a database and you're expected to know to do this as well... – Heptad 15/5, 2019 at 14:14

Its already opened but still getting the same error while making connection in Oracle SQL Developer software. #70707462 – Footnote 14/1, 2022 at 7:36

P

31

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.

Peril answered 17/10, 2015 at 4:5 Comment(0)

D

7

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.

Delayedaction answered 15/1, 2021 at 8:27 Comment(0)

R

6

alter pluggable database orclpdb open;`

worked for me.

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

Ricoriki answered 20/9, 2019 at 0:20 Comment(0)

R

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

Rew answered 21/10, 2020 at 4:12 Comment(1)

Thanks a lot Your solution solved my problem – Sciomancy 6/4, 2021 at 10:5

R

2

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

Robb answered 3/12, 2014 at 19:54 Comment(7)

Hmm, I do this code, it tells me "Oracle not available" ?? – Talkative 3/12, 2014 at 19:56

if not available how you can login? check the database and listener services and try again – Robb 3/12, 2014 at 19:58

I think because I log in using : sqlplus sys/sys_password as sysdba - i think i should use normal user? – Talkative 3/12, 2014 at 19:59

SYS has all privileges, you're wellcome – Robb 3/12, 2014 at 20:12

Hmm, that's what I thought. So something is really strange here, I will look into – Talkative 3/12, 2014 at 20:13

If you're the DBA, just run SHUTDOWN, and then STARTUP, and report us what he replies. – Isotherm 4/12, 2014 at 14:16

#70707462 – Footnote 14/1, 2022 at 7:36

C

2

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

This Worked for me .

Cataclinal answered 7/7, 2021 at 18:17 Comment(0)

B

1

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.

Bidentate answered 2/1, 2015 at 4:39 Comment(0)

M

1

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

Milomilon answered 3/5, 2017 at 7:29 Comment(0)

H

1

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;

Heavyladen answered 21/3, 2020 at 3:47 Comment(1)

Its already opened but still getting the same error while making connection in Oracle SQL Developer software. #70707462 – Footnote 14/1, 2022 at 7:37

P

0

please run this script

ALTER DATABASE OPEN

Paul answered 17/1, 2020 at 13:1 Comment(0)

S

0

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!

Standoff answered 15/1, 2022 at 14:13 Comment(0)

H

0

If you get this error for Spring boot, then check VM arguments whether you have provided correct database configurations or not.

How answered 20/9, 2023 at 10:41 Comment(0)

L

-1

il faut faire

SQL> STARTUP FRORCE

Lynda answered 31/12, 2020 at 14:56 Comment(2)

Please reply in English. Also review your answer, its probably STARTUP FORCE – Corina 31/12, 2020 at 15:25

please answr in english, it is against the site policy to answer in other languages – Ansilma 31/12, 2020 at 15:29

Recommended topics

Hot tags