How to Start an Oracle 12c Database

By Chris Ruel, Michael Wessler

You don’t actually start an Oracle 12c database per se; you start the instance. A database is defined as the actual data, index, redo, temp, and control files that exist on the files system. The instance consists of the processes (PMON, SMON, DBWR, LGWR, and others) and the SGA (memory pool) that access and process data from the database files.

The instance is what accesses the database, and it is the instance that users connect to. Thus, it is the instance (not the database) that you actually start.

In ascending order, during startup the database instance goes through these states:

NOMOUNT

  • Read Parameter File

  • Allocate SGA

  • Start Background Processes

  • Only SGA and Background Processes Running

  • Used for CREATE DATABASE (only SYS can access)

  • Specified by STARTUP NOMOUNT

MOUNT

  • Read Parameter File

  • Allocate SGA

  • Start Background Processes

  • Open and Read Control File

  • SGA and Background Processes Running and Control Files Open

  • Used for database maintenance and recovery operations (only SYS can access)

  • Specified by STARTUP MOUNT

OPEN

  • Read Parameter File

  • Allocate SGA

  • Start Background Processes

  • Open and Read Control File

  • Open All Database Files

  • SGA and Background Processes Running, Control Files Open, All Database Files Open

  • Default OPEN state for database and is accessible by users and applications

  • Specified by STARTUP or STARTUP OPEN

Unless you’re performing specialized maintenance, the default is as follows:

  • STARTUP with the parameter file read

  • Background processes and SGA started

  • Control files open and read

  • All database files open

In this open state, users access the database normally.

Here’s what it looks like when starting the database into the default OPEN mode. Here’s the default parameter file.

$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.1.0 - Production on Sun May 19 09:59:12 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 789172224 bytes
Fixed Size         2148552 bytes
Variable Size       557844280 bytes
Database Buffers     218103808 bytes
Redo Buffers        11075584 bytes
Database opened.
SQL>

Although most people normally go straight to the fully open mode, you can increment the modes. For example, you could do database maintenance with the database in MOUNT mode and, once done, issue ALTER DATABASE OPEN to take the database to open mode so users can start work.

That’s what’s done here:

SQL> startup mount
ORACLE instance started.
Total System Global Area 789172224 bytes
Fixed Size         2148552 bytes
Variable Size       570427192 bytes
Database Buffers     205520896 bytes
Redo Buffers        11075584 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL>

Note that you can only go forward to a more open state; you can’t move to a more restrictive state without issuing a shutdown.

In most cases, when you open a database you want it open for every user. Sometimes, however, you want to block all or some users even though the database is in the OPEN state.

To do this, put the database in RESTRICTED SESSION mode via one of these ways:

  • STARTUP RESTRICT

  • ALTER SYSTEM ENABLE RESTRICTED SESSION

    SQL> startup restrict;
    ORACLE instance started.
    Total System Global Area 789172224 bytes
    Fixed Size         2148552 bytes
    Variable Size       570427192 bytes
    Database Buffers     205520896 bytes
    Redo Buffers        11075584 bytes
    Database mounted.
    Database opened.
    SQL>
    SQL> alter system enable restricted session;
    System altered.
  • When the database is OPEN, you must grant users CREATE SESSION to connect.

  • When the database is RESTRICTED, users must have CREATE SESSION and they also must have RESTRICTED SESSION to connect.

The only backdoor is if the user was already logged in when an ALTER SYSTEM ENABLE RESTRICTED SESSION was issued; then the user can remain logged in. Therefore, you should kill all user sessions after putting the database in RESTRICTED mode to kick them out. If they don’t have RESTRICTED SESSION, they get this Oracle error when they try to log in:

$ sqlplus barb/test123
SQL*Plus: Release 12.1.0.1.0 - Production on Sun May 19 11:26:41 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Why would you want to do this? Although frustrating users is the secret pleasure of every administrator (especially security administrators), some valid technical reasons exist. Major data, table, or application updates often need a stable system with no updates or locks to contend with so they can process successfully. Some database maintenance operations also require a restricted session.

If you need to allow in a subset of users or perhaps the application user processing a database job, you may grant them RESTRICTED SESSION:

SQL> grant restricted session to barb;
Grant succeeded.
SQL> connect sdeas/test123
Connected.

Revoke the RESTRICTED SESSION from any non-DBA user once the user’s work is done. Also, don’t forget to take the instance out of restricted session.

SQL> alter system disable restricted session;
System altered.

Starting up database instances isn’t terribly difficult, and most times you use the default STARTUP command to take the database instance to the OPEN state. Occasionally the situation require a RESTRICTED SESSION.

If the database startup seems to take a few minutes, it may be because of a large SGA during which time memory is being allocated. Or there may be many database files to open.

If the database crashed or a SHUTDOWN ABORT occurred prior to the startup, database instance recovery is occurring, which can take time. If this occurs, leave your screen with the STARTUP command open; let it run. View the alert log with another window.

If more severe errors occur (such as media recovery), they appear both on the startup screen and in the alert log file. Of course, you can prevent many of these issues if you stop the database in a clean manner. Carry on to the next topic.