Skip to content
Home » Oracle » How to Start Oracle Database

How to Start Oracle Database

Start Oracle Database

To start an Oracle database, you have to know which database you want to start. This includes at least two things, Oracle home and Oracle SID. For remote database startup, you additionally need to know where the server is.

For RAC databases, you may refer to: How to Start RAC Database.

The startup can be manual or automatic, it depends on your requirements. In this post, we will talk about several different ways to manually start Oracle database in Linux. For windows, don't worry about it, the SQL commands are all the same.

There're 4 states for an instance lifecycle, they are:

  1. IDLE
  2. The database service stops, no processes, no instance.

  3. NOMOUNT
  4. The instance is running and start to occupy required memory.

  5. MOUNT
  6. The instance knows all the locations of data files and redo log file.

  7. OPEN
  8. Users can access the database to do some operations.

Normally, we start a database from IDLE state, it starts the database from nothing to the state you want.

Oracle Database Startup - Start Oracle Database from IDLE
Oracle Database Startup - Start Oracle Database from IDLE

There're still some cases that you want the database to go up just one more state.

Oracle Database Startup – Switch Oracle Database State from One to Another
Oracle Database Startup – Switch Oracle Database State from One to Another

Different state needs different type of file to be started with. Here I listed their required files in a slide and a chart to clarify all types of files related to startup.

Oracle Database Startup – Required Files to States
Oracle Database Startup – Required Files to States

Database States vs Required Files
Database StateRequired File(s)
NOMOUNTParameter File (SPFILE or PFILE)
MOUNTControl Files
OPENData Files and Redo Files

Please note that, the data files mentioned above include permanent and undo data files, but not temp files. This is because the database will create new ones for missing temp files during starting up.

The first part of this post, section A, B and C, we will talk about commands that can switch database states. Later in section D, we will talk about how to startup multiple databases in a batch-fashioned way by utilizing Oracle provided script, dbstart. Furthermore, we can use it to deploy our automatic startup scripts in Linux.

  1. To NOMOUNT
  2. To MOUNT
  3. To OPEN
  4. Startup Script

By the way, to start Oracle database remotely, you have to know how to connect to an idle, NOMOUNT or restricted database from a remote client.

To NOMOUNT

In this state, the database will only need a parameter file which is usually a SPFILE, to allocate processes and get necessary memory from the server. When the database is running, we call it "instance".

Before we can startup an Oracle database from the server, we have to make sure two environment variables are set, which are ORACLE_HOME and ORACLE_SID in.

[oracle@test ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@test ~]$ echo $ORACLE_SID
ORCL
[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

There's only 1 state that can go to NOMOUNT.

From IDLE to NOMOUNT

There're several kinds of scenarios to startup a database to NOMOUNT.

Normal

We don't have to explicitly specifying the location of SPFILE when issuing STARTUP SQL command. The database will locate the parameter file in the default location.

The located parameter file is not always SPFILE, because Oracle will choose the most proper parameter file by examining their file names to startup the database.

SQL> startup nomount;

Please note that, even though the last semicolon is optional, I'd rather keep the habit to append it to the end of every statement.

In NOMOUNT state, the only view that you can query is V$INSTANCE.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             STARTED

PFILE

If you'd like to start the database with PFILE in a different location, you have to specify the path of PFILE.

SQL> startup nomount pfile='/tmp/initORCL.ora';

Force

When you almost lost all files including SPFILE, you can use the following command to get a dummy instance for later restoration of SPFILE. Please note that, you can't use the command anywhere but RMAN.

RMAN> startup force nomount;

Or this:

RMAN> startup nomount force;

They are the same thing.

To MOUNT

In this state, the database will additionally need the control file to acknowledge everything about the data files and redo log files, which includes filename, location and checkpoint.

Please keep in mind, the instance now knows every file's conditions, but it will not touch them. That is to say, if you move data files forth and back in this state, the instance complains nothing. That's why we can rename the data file in this state.

There're 2 different states that need to mount the database.

  1. From IDLE to MOUNT
  2. From NOMOUNT to MOUNT

From IDLE to MOUNT

Normal

Again, you don't have to specify the location of parameter file, the database will find the most proper one to use.

SQL> startup mount;

Then we check the state of database. Both V$INSTANCE and V$DATABASE can be used to query.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             MOUNTED

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED

PFILE

Same reason here, if you'd like to start the database with PFILE in a different location, you have to specify the path of PFILE.

SQL> startup mount pfile='/tmp/initORCL.ora';

From NOMOUNT to MOUNT

Sometimes, you have done some jobs in NOMOUNT state and want to go further, you can alter the database with MOUNT option. Such situations could be that control files have been restored to the original locations described in parameter file.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             STARTED

SQL> alter database mount;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             MOUNTED

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED

Please note that, ALTER DATABASE MOUNT affects only in the current instance for a RAC database. The states of other instances in the same cluster remain unchanged.

To OPEN

We have two sub-states in OPEN, one is READ WRITE, the other is READ ONLY. Conventionally, if we didn't specially any one, it's usually READ WRITE.

There're 3 different states that need to open the database.

  1. From IDLE to OPEN
  2. From MOUNT to OPEN
  3. From NOMOUNT to OPEN

From IDLE to OPEN

OPEN really means that we open the database for public connections.

Normal

This operation is very common and often used. The following 3 commands are the same.

SQL> startup; SQL> startup open; SQL> startup open read write;

Let's check the status.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             OPEN

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE

Please note that, if the database is playing standby role, STARTUP brings it to READ ONLY automatically, this is the only exception.

RESTRICTED SESSION

In some cases, you needs to do something in OPEN state solely, e.g. changing database character set or analyzing the entire database before going public. Therefore, you should startup the database to restricted mode.

SQL> startup restrict;

Later on, you can leave the restricted mode by disabling it.

SQL> alter system disable restricted session;

To toggle the special mode online, you can use the following statement to enable the restriction again.

SQL> alter system enable restricted session;

To READ ONLY

READ ONLY means that you allow users to query but no changes on data. In such situation, you can perform a consistent user backup because no data file can go any further step, no one.

SQL> startup open read only;

Then we check the status of the instance.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             OPEN

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ ONLY

From MOUNT to OPEN

Sometimes, you have done some jobs in MOUNT state and want to go further, you can alter the database with OPEN modifier. Such situations could be that all data file have been recovered to the newest system change number, or after enabling archivelog mode.

To READ WRITE

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED

SQL> alter database open;

Database altered.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE

Now the database is open, it may be far behind the instance startup. In fact, there's difference between instance startup time and database open time.

Please note that, there's no such ALTER DATABASE OPEN RESTRICT statement to make the database restricted.

The SQL command is the same as the following:

SQL> alter database open read write;

Please note that, ALTER DATABASE OPEN affects only in the current instance for a RAC database. The states of other instances in the same cluster remain unchanged.

With RESETLOGS

If you were performing a point-in-time recovery, the best state that you can reach is to open the database with reset redo logs.

SQL> alter database open resetlogs;

The sequence number of redo logs will be reset to 1, a new incarnation is born.

Please note that, you cannot use the RESETLOGS clause with a READ ONLY clause.

To READ ONLY

You could open the database to READ ONLY for developers to check data integrity before going public.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED

SQL> alter database open read only;

Database altered.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ ONLY

Please note that, you can't directly switch the database between READ WRITE and READ ONLY. You have to bounce the database to bring itself from one type of OPEN to another.

Pluggable Databases

Since 12c, we can have multitenant architecture of a database, the host database is called Container Database (CDB), the guest database is called Pluggable Database (PDB). PDBs are MOUNT by default when CDB startup.

To open specific PDB to READ WRITE, we can do this:

SQL> alter pluggable database <PDB_NAME> open;

Pluggable database altered.

To open specific PDB to READ ONLY, we can do this:

SQL> alter pluggable database <PDB_NAME> open read only;

Pluggable database altered.

To open specific PDB to READ WRITE on all nodes in a RAC system, we can do this:

SQL> alter pluggable database <PDB_NAME> open instances=all;

Pluggable database altered.

To open all PDBs to READ WRITE, we can do this:

SQL> alter pluggable database all open;

Pluggable database altered.

We can also automatically open PDB when startup CDB by saving their states.

From NOMOUNT to OPEN

To open a NOMOUNT database, there's no direct way to do it, you have to take two steps:

If you issue ALTER DATABASE OPEN when NOMOUNT state, the database will throw ORA-01507 to alert you that the database is not mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted

Sometimes, you may not know the condition of the database, there're some ways to know what state of the instance currently is.

Startup Script

After knowing some basic knowledge about how to start an Oracle database in various situations. Let's see how we make the startup and shutdown jobs more smoothly and automatically by leveraging Oracle provided scripts.

dbstart and dbshut

Oracle provides dbstart and dbshut two shell scripts to facilitate us to do startup and shutdown jobs more easily.

Furthermore, we can also learn how to restart a database in one step from this command.

Let's see where they are:

[oracle@test ~]$ ll $ORACLE_HOME/bin/dbstart
-rwxr-x---. 1 oracle oinstall 15737 Jan  1  2000 /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart
[oracle@test ~]$ ll $ORACLE_HOME/bin/dbshut
-rwxr-x---. 1 oracle oinstall 8142 Jan  1  2000 /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbshut

Check /etc/oratab

Before we can use the scripts to start Oracle database, we have to make sure /etc/oratab is well-configured.

The format of every entry consists of the following components delimited by semicolons.

$ORACLE_SID:$ORACLE_HOME:<N|Y>
  • $ORACLE_SID: What instance should be started.
  • $ORACLE_HOME: What release of database software should be used.
  • Y or N: Do startup if "Y", otherwise don't start.

In this case, we have two ORACLE_SID to be started.

[oracle@test ~]$ vi /etc/oratab
...
CATDB:/u01/app/oracle/product/19.0.0/dbhome_1:Y
ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:Y

Please note that, if you moved Oracle home, you have to modify the file by yourself.

How to Use

To start databases only, you can just issue:

[oracle@test ~]$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener

The above error "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener" notified us that it does not start the listener because we didn't set the first argument in the command. To start databases as well as the listener, you can issue:

[oracle@test ~]$ dbstart $ORACLE_HOME
Processing Database instance "CATDB": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log
Processing Database instance "ORCL": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log

Let's check instances.

[oracle@test ~]$ ps -ef | grep smon
oracle     6776      1  0 19:56 ?        00:00:00 ora_smon_CATDB
oracle     7166      1  0 19:56 ?        00:00:00 ora_smon_ORCL

The best thing is that, we can startup all database instances in one command.

Next, we should learn how to stop an Oracle database, or something more about automatically start Oracle database script on system boot in Linux.

Leave a Reply

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