Startup and Shutdown

How to Start Oracle Database

Start Oracle Database

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

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.

A. 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.

1. From IDLE

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.

RAC

To start a cluster database to NOMOUNT on all nodes in a RAC, you have to use SRVCTL.

[oracle@primary01 ~]$ srvctl start database -d <db_unique_name> -o nomount

B. 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.

1. From IDLE

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';

RAC

To start a cluster database to MOUNT on all nodes in a RAC, you have to use SRVCTL.

[oracle@primary01 ~]$ srvctl start database -d <db_unique_name> -o mount

2. From NOMOUNT

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

C. 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.

1. From IDLE

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 a restricted mode.

SQL> startup restrict;

Later on, you can lift the restriction 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;

RAC

To start a cluster database to OPEN on all nodes in a RAC, you have to use SRVCTL.

[oracle@primary01 ~]$ srvctl start database -d <db_unique_name> -o open

If the default start option is OPEN, you can omit it by this.

[oracle@primary01 ~]$ srvctl start database -d <db_unique_name>

Of course, we can also change the default start option for your standby RAC.

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

RAC to READ ONLY

To start a cluster database to READ ONLY on all nodes in a RAC, you have to use SRVCTL.

[oracle@primary01 ~]$ srvctl start database -d <db_unique_name> -o "read only"

The double quotes are required to enclose multiple words.

2. From MOUNT

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

The SQL command is the same as the following:

SQL> alter database open read write;

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.

D. 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.

1. dbstart and dbshut

Oracle provides dbstart and dbshut two shell scripts to facilitate us to do startup and shutdown jobs more easily. 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

2. 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.

3. 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 *