Oracle Instance: Startup and shutdown

SHUTDOWN -> NOMOUNT

The following steps are performed when an idle (shutdown) instance is broght to nomount:

A spfile or pfile is searched.
The SGA is sized according to the values found in the parameter file
The background processes are started
The alert log is opened
Semaphores are allocated

In nomount stage, the instance is started but not associated with a database.

An instance needs to be in the nomount state to

create a new database
for some backup and recovery operations

SQ> startup nomount

Total System Global Area 1073738560 bytes
Fixed Size                  9036608 bytes
Variable Size             788529152 bytes
Database Buffers          268435456 bytes
Redo Buffers                7737344 bytes

v$sql is accessible:

set numformat 999999999999999
set lines 10000
set trimspool on
column sql_text format a9000
spool %temp%\sql.txt

select
   lag(to_number(address, 'xxxxxxxxxxxxxxxx')) over (order by first_load_time, address desc) - to_number(address, 'xxxxxxxxxxxxxxxx') addr_diff,
  (sysdate-to_date(first_load_time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60*60 loaded_s_ago,
-- sql_id,
   substr(sql_fulltext, 1, 9000) sql_text
from
   v$sql
order by
   first_load_time,
   address desc;

spool off

v$parameter is also accessible:

select substr(name,1,30), substr(value,1,50) from v$parameter order by name;

v$controlfile is empty:

select * from v$controlfile;

Only fixed tables and fixed views can be queried:

select count(*) from user_objects;

ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only


select * from x$kcccp;
              *
ERROR at line 1:
ORA-01507: database not mounted

Some processes are already available:

SQL> select ksbdphdl, ksbdpnam, ksbdppri from x$ksbdp where ksbdpser > 0 order by ksbdpnam;

        KSBDPHDL KSBDP KSBDPPRI
---------------- ----- --------
             239 CKPT  Normal
              21 CLMN  Normal
              42 D000  Normal
               8 DBRM  Normal
              75 DBW0  Normal
              23 DIA0  Normal
               6 DIAG  Normal
             328 FENC  Normal
               1 GEN0  Normal
               2 GEN1  Normal
              30 LG00  Normal
              30 LG01  Normal
             237 LGWR  Normal
             271 LREG  Normal
              81 M000  Normal
              74 MMAN  Normal
             346 MMNL  Normal
             345 MMON  Normal
              22 PMAN  Normal
              20 PMON  Normal
              15 PSP0  Normal
             273 PXMN  Normal
             250 RECO  Normal
              41 S000  Normal
             248 SMCO  Normal
             247 SMON  Normal
              14 SVCB  Normal
             176 TMON  Normal
               9 VKRM  Normal
               0 VKTM  Time Cr

The buffer cache is empty:

select count(*) from x$bh;
0

Shared memory is now available:

SQL> host ipcs -a
‥

There is a dual table, yet without synonym:

none> select * from dual;

ADDR                         INDX          INST_ID           CON_ID D
---------------- ---------------- ---------------- ---------------- -
00007FF77465A43C                0                1                0 X

The following statement causes ORA-01507: database not mounted:

select * from v$database;

The following statement returns a record:

select instance_name, startup_time, status, database_status from v$instance;

NOMOUNT -> MOUNT

In order to mount a database, the instance opens the control file as specified in the control_files init parameter:

select value from v$parameter where name = 'control_files';

In mount phase, the instance is associated with a database.

A mounted database is closed for ordinary users. Only administrators have access to it for a restricted set of operations.

alter database mount;

The control files are now opened:

select count(*) from v$controlfile;
…
…

The buffer cache is still empty:

select count(*) from x$bh;

The data dictionary is not available:

select count(*) from user_objects;

ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

Operations that are typically performed in mount stage include

Renaming datafiles
Enabling or disabling archiving options
Adding, dropping or renaming redo log files
Full database recovery

MOUNT -> OPEN

The control file that was opened in the previous step records the locations of the data files and online redo log files that belong to a database. This information is now needed to open these files.

If the instance fails to open one of these files or if the consistency check is not successful, the instance will issue an error. In this case, media recovery is likely.;

The steps to open a mounted database are:

The online data files that belong to a non-undo tablespace are opened and rollforward is applied (possible continuing after the database is open)
An undo tablespace is acquired by SMON
The online redo log files are opened
SMON(?) makes the database availabe for users

Part of the mount->open process is to execute the DDL statements recorded in bootstrap$.

A 10046 event can be used to show the statements executed in the mount -> open phase:

alter session set events '10046 trace name context forever, level 12';
alter database open;
alter session set events '10046 trace name context off';
oradebug setmypid
oradebug tracefile_name

Note: It's also possible to open a database in read only mode.

When a database is opened, the SYSTEM tablespace is always online.

alter database open;

select * from dual;
D
-
X

select open_mode from v$database;
select database_status from v$instance;

SHUTDOWN

Shutdown modes

A shutdown can be performed with the following modes

shutdown abort
shutdown immediate
shutdown transactional
shutdown normal

In any of these modes, no new connections are possible.

With shutdown normal, the instance waits for all connected users to disconnect and then shuts down.

With shutdown transactional, the instances waits for all active transactions to finish and then shuts down. A session is not allowed to start a new transaction.

With shutdown immediate, the instance terminates any executing SQL statements, rolls transactions back, disconnects users and then shuts down.

A shutdown abort just terminates the instance.

All shutdown modes except shutdown abort perform a checkpoint. Therefore, after a shutdown abort, instance recovery is required to startup and reopen a database.

Shutdowns taking long

A shutdown appears to be slow or hang because

processed do not terminate
SMON is cleaning up temp segments or performed delayed block cleanouts
Uncommitted transactions are being rolled back

Availability on v$ views

Not all v$ views are available at the different stages of the startup. For example, v$instance or v$bgprocess are available when the instance is started, but v$datafile is only available when the database is mounted.

STARTUP RESTRICT

If a database is started in restrict mode, only users that are granted the restricted session privilage are allowed to connect to the database.

This mode is typically used for maintenance operations where the database needs to be operational such as

backups
patching
etc.

Verify that the mode is restricted with

SQL> select logins from v$instance;
RESTRICTED

A database in restricted mode can be braught back to normal mode with

alter system disable restricted session;

Specifying a pfile for startup

SQL> startup pfile=/path/to/pfile

STARTUP UPGRADE

With startup upgrade, the database is started in upgrade mode which is specifically designed for the Database Upgrade Assistant (DBUA) or for manual upgrades.

logins of v$instance is RESTRICTED with this mode.

A database in upgrade mode can be braught to normal mode only with shutdown and restart.