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

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?

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;

please run this script

ALTER DATABASE OPEN

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

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

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

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.

il faut faire

SQL> STARTUP FRORCE

alter pluggable database orclpdb open;`

worked for me.

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

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

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

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.