Starting database in linux

How to 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:

The database service stops, no processes, no instance.

The instance is running and start to occupy required memory.

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

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

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

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

Database States vs Required Files

Database State	Required File(s)
NOMOUNT	Parameter File (SPFILE or PFILE)
MOUNT	Control Files
OPEN	Data 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.

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.

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;

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;

RMAN> startup nomount force;

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.

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.

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;

SQL> select name, open_mode from v$database;

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;

SQL> alter database mount;

Database altered.

SQL> select instance_name, status from v$instance;

SQL> select name, open_mode from v$database;

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.

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;

SQL> select instance_name, status from v$instance;

SQL> select name, open_mode from v$database;

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

RESTRICTED SESSION

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;

SQL> select name, open_mode from v$database;

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;

SQL> alter database open;

Database altered.

SQL> select name, open_mode from v$database;

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.

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;

SQL> alter database open read only;

Database altered.

SQL> select name, open_mode from v$database;

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

Pluggable database altered.

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

SQL> alter pluggable database 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 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.

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.

[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: 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

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

Источник