How to Startup the Oracle Database

In this article, we are going to How to Startup the Oracle Database. Try to restart your Linux machine and login again as an oracle user. Open the terminal and connect to the database. Connect to the database using sqlplus / as sysdba as shown in the below image. This command indicates connecting to the SYS user.

We are getting the above error as Connected to an idle instance. This error indicates that the database is down. When the above error appears, we cannot access the database. The reason behind this is we have Restarted the Linux Machine. Usually, the server should always be up and running.

How to Startup the Oracle Database?

Command: startup;

This command will give you the below output which indicates that the database is started. This command will open the instance again.

Note: Whenever we connect to a database we are connecting to an instance.

In the above image, you can see that the shared global area is allocated with 2533358136 bytes(2.3GB) reserved for SGA. The size allocation in the SGA will be shown in bytes. This information is read from the file called the parameter file. Below you can see Database Mounted. Database Opened.

Oracle Database Startup Methods:

We manually issue the command startup to start the instance using the command startup. When we issue command startup the database goes into multiple phases- NoMount, Mount, and Open.

Startup No Mount:

The instance is started but the instance is not associated with the database. The memory is allocated for SGA but it is not associated with the database. The startup nomount will undergo the below process and make the instance started. During the no mount stage, the database is accessed to only DBAs.

Search for pfile, spfile(Instance searches for pfile or spfile)
Read the parameter file
From the parameter file, the SGA is allocated.
Starts the background processes
Open the alert and trace files.

Startup Mount:

The instance is started by reading its control file. The instance is started and associated with the database by reading the control file but the database is closed to the users. The database is accessible only to the DBAs. No insert, update or delete statements are allowed to perform on the database when the database is in a mounted state.

Startup Open:

The instance is started and associated with an open database and the data files are accessible to all the users. The data contained in the datafiles is accessible to all the users present inside the database. Below is the process of opening the database.

Opens the online database files present in the database.
Acquires the Undo tablespaces
Opens the online redo log files.

Connection with Administrator Privileges:

Database startup and shutdown are powerful administrative options that are restricted to the users who connect to the Oracle database with administrator privileges. Anyone in the database cannot startup or shut down the database. The users who have administrative privileges can be able to start and shut down the database. Special privileges enable access to a database instance even when the database is not open:

SYSDBA
SYSOPER
SYSBACKUP
SYSDG
SYSKM

The authentication access happens using the password file. Any users having the above privileges can be able to log in to the database if not open. Any users having the privileges like SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM are super users they can perform any task inside the database.

The SYS admin account is automatically created when a database is created. The SYS account can perform all the database admin functions. The SYS schema stores the base tables and views for the data dictionary. The SYS is the super user for Oracle.

What are the differences between SYSDBA and SYSOPER?

SYSDBA and SYSOPER are the administrative privileges required to perform high-level admin works such as creating, starting up, shutting down, backup, recover the database.

The SYSDBA system privilege is for a fully empowered database admin. The SYSOPER system privilege allows a user to perform basic operational tasks without the ability to look at user data. The SYSOPER doesn’t access user data. So, if we connect to SYSDBA and SYSOPER we connect to the highest privilege in the database.

Practical Exercise:

Let’s do some practical exercises regarding the “startup;”.

Step 1: Connect to the database.

Go to the virtual machine. Open the terminal and connect to the database using “sqlplus / as sysdba”. This shows the below image because we have already started the database.

Step 2: Shutting down the Database.

As we have already started the database. we will try to shut down the database. We can shut down the database using the below command.

Command: shutdown immediate;

We will discuss the shutdown in our next article. It will take a little time to get the database closed.

Step 3: Startup nomount

Now the database is down and we need to start the database. We will go through the startup methods that we have learned. We will start with the startup nomount.

Command: startup nomount;

Press Enter and then you will the process of the startup mount. Below is the image of the startup nomount.

The instance is started but not associated with the database. Let’s prove this statement with a simple query.

Query: Select count(*) from dba_users;

This query displays how many users are present inside the database. After executing the query you will get the below error. This error indicates that the database is not yet open for the users to execute the queries.

We have limited queries in this startup nomount. We can find the status of the database i.e started, mounted, or else in open mode using the below query.

Query: select status from v$instance;

In the above query, you can see the status of the database is mentioned as started. The term started indicates the database is in nomount stage.

Step 4: Startup Mount.

Let’s move to the next step of the startup methods. Until now we have started the database using nomount method. Now we will mount the database and see what else is changed in the mount state using the below command.

Command: alter database mount;

Now the database is mounted. Let’s check the status of the database using the below query and check whether we can be able to access other tables as well in this mounted stage.

Query:

select status from v$instance;
Select count(*) from dba_users;

At this stage also we are unable to get the database tables. We are getting the same error as in the nomount stage. So, the tables are not accessible on the mounted stage also.

Step 5: Open Database

Let’s run the last step as opening the database. This step will allow the database to be available to all the users in the database.

Command: alter database open;

So, now the database is opened. Let us try to run the same select statement that we have used for previous steps.

Query: select count(*) from dba users;

We got an output of 36 users. So, we are able to access the tables inside the database. All users in the database can be able to run their queries. Let’s try to see the status of the database.

Query: select status from v$instance;

Note: You may have a question about why we are doing all this process while we can execute using a single statement “startup;”. These operations are required during maintenance tasks. These steps will be used during the patching of the database. There will be multiple actions of fixes will be done during these stages of the database.

There are still some startup options. Below is the image from the oracle website. Below are a few other options that we can try during the startup of the database.

Syntax Element: Description

STARTUP: If you specify only STARTUP with no other options, then the instance starts the instance with the default server parameter file, mounts the control file, and opens the database.
DBA: Restricts access to users with the RESTRICTED SESSION privilege.
FORCE: If the database is open, then FORCE shuts down the database with a SHUTDOWN ABORT statement before re-opening it. If the database is closed, then FORCE opens the database.
MOUNT: Starts the instance, then mounts the database without opening it.
NOMOUNT: Starts the instance without mounting the database. If no parameter file exists, then RMAN starts the instance with a temporary parameter file. You can then run RESTORE SPFILE to restore a backup server parameter file.
PFILE filename: Specifies the file name of the text-based initialization parameter file for the target database. If PFILE is not specified, then the default initialization parameter file name is used.

You can refer to the below Startup webpage of oracle for more details.

Source: STARTUP (oracle.com)

Startup dba: This method restricts the database to users who have RESTRICT SESSION privilege. Any user having the RESTRICT SESSION privilege can be able to access the database.

Startup force: When the database is running if we enter startup the command doesn’t work because we have to shut down and startup again. But if we use startup force the database will be rebooted and the database will be started. During the shutdown of the database, it uses shutdown abort which we can discuss later. Let’s try this manually.

Try running a startup while connected to the database.

So, we got this error which indicates that the database is already running shutdown this database, and then start again. We will use this Startup force here now and see what happens.

So, the database is restarted in this mode.

Startup pfile filename: We can use this command to startup the database using the pfile rather than the spfile that is normally used to startup. So, the database will read all the parameters from this pfile. We will discuss this later.

In the next article, I am going to discuss How to Shutdown Oracle Database. Here, in this article, I try to explain How to Startup the Oracle Database and I hope you enjoy this How to Startup the Oracle Database article.

How to Startup Oracle Database