Oracle Instance: Startup and shutdown

Shutdown -> nomount

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

A server parameter file 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

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

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

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

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
An undo tablespace is acquired
The online redo log files are opened

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.

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.