Oracle Database Startup

Oracle Database Startup

We start the oracle database with the command ‘startup’.  The user should have SYSDBA or SYSOPER privilege to start the database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 17 17:17:25 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                    2257840 bytes

Variable Size                541068368 bytes

Database Buffers            289406976 bytes

Redo Buffers                   2371584 bytes

Database mounted.

Database opened.

SQL>

Oracle database is started in three stages.

1.       Nomount

This is the first stage of database startup. It is called instance startup. Oracle reads initialization file either pfile or spfile.  If you don’t specify the location of init file, it will read from default location.  Based on the parameters specified in file, memory is allocated to SGA and background processes are started.

Operations performed in this stage:

Database creation

Control file recreation

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size          2257840 bytes

Variable Size          541068368 bytes

Database Buffers       289406976 bytes

Redo Buffers             2371584 bytes

If you watch alert log during “startup nomount” you can see that all background processes have started.

Sun Sep 17 19:56:38 2017

Starting ORACLE instance (normal)

...

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1

...

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfileORADB1.ora

System parameters with non-default values:

...

  db_name                  = "ORADB1"

...

PMON started with pid=2, OS id=5717

PSP0 started with pid=3, OS id=5719

VKTM started with pid=4, OS id=5721 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

GEN0 started with pid=5, OS id=5725

DIAG started with pid=6, OS id=5727

DBRM started with pid=7, OS id=5729

DIA0 started with pid=8, OS id=5731

MMAN started with pid=9, OS id=5733

DBW0 started with pid=10, OS id=5735

LGWR started with pid=11, OS id=5737

CKPT started with pid=12, OS id=5739

SMON started with pid=13, OS id=5741

RECO started with pid=14, OS id=5743

MMON started with pid=15, OS id=5745

MMNL started with pid=16, OS id=5747

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

2.       Mount

In this stage, database is mounted. Oracle reads Control file and gets the location of datafiles. Started instance is associated with a database.

Operations performed in this stage:

Enable/Disable archivelog

Add/Drop/Rename redo log

Rename datafile

Full database recovery

All these operations requires access to datafiles but no other user operations can be carried out.

Only a dba can access the database in this stage.

If database is already opened in nomount stage, it can be mounted like below.

SQL> alter database mount;

Database altered.

You can see progress of this command in alert log as below.

Sun Sep 17 20:01:25 2017

alter database mount

Sun Sep 17 20:01:29 2017

Successful mount of redo thread 1, with mount id 2300134229

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount

3.       Open

In this stage, database is opened. Oracle tries to access all of the datafiles and redolog files associated with database. Once all the datafiles are accessed, oracle checks whether all datafiles are consistent. The scn in control file will be matched with scn in datafile headers. It it does not match, instance recovery will be carried out. Once the database is opened, all authenticated users can connect to database and perform operations.

If database is already in mount stage, it can be opened like below.

SQL> alter database open;

Database altered.

You can see in alert the progress of this stage. The background process CJQ0 (Job co-ordinator process) starts during this stage as its used for user jobs which can run in open mode of database only. Its same for QMNC (Queue monitor ) background process

Sun Sep 17 20:06:39 2017

alter database open

Sun Sep 17 20:06:39 2017

Thread 1 opened at log sequence 9

  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/ORADB1/redo03.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sun Sep 17 20:06:39 2017

SMON: enabling cache recovery

[5752] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:10101724 end:10101834 diff:110 (1 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Sun Sep 17 20:06:40 2017

QMNC started with pid=20, OS id=5896

Completed: alter database open

Sun Sep 17 20:06:41 2017

db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Sun Sep 17 20:06:41 2017

Starting background process CJQ0

Sun Sep 17 20:06:41 2017

CJQ0 started with pid=22, OS id=5910

To check and verify the open mode, we can check:

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

To see the oracle instance you can see the background process started as a part of startup.

     [oracle@localhost ~]$ ps -aef |grep ORADB1

oracle    3271     1  0 17:38 ?        00:00:00 ora_pmon_ORADB1

oracle    3273     1  0 17:38 ?        00:00:01 ora_psp0_ORADB1

oracle    3275     1  2 17:38 ?        00:02:32 ora_vktm_ORADB1

oracle    3279     1  0 17:38 ?        00:00:00 ora_gen0_ORADB1

oracle    3281     1  0 17:38 ?        00:00:00 ora_diag_ORADB1

oracle    3283     1  0 17:38 ?        00:00:00 ora_dbrm_ORADB1

oracle    3285     1  0 17:38 ?        00:00:02 ora_dia0_ORADB1

oracle    3287     1  0 17:38 ?        00:00:00 ora_mman_ORADB1

oracle    3289     1  0 17:38 ?        00:00:00 ora_dbw0_ORADB1

oracle    3291     1  0 17:38 ?        00:00:00 ora_lgwr_ORADB1

oracle    3293     1  0 17:38 ?        00:00:01 ora_ckpt_ORADB1

oracle    3295     1  0 17:38 ?        00:00:00 ora_smon_ORADB1

oracle    3297     1  0 17:38 ?        00:00:00 ora_reco_ORADB1

oracle    3299     1  0 17:38 ?        00:00:01 ora_mmon_ORADB1

oracle    3301     1  0 17:38 ?        00:00:01 ora_mmnl_ORADB1

oracle    3303     1  0 17:38 ?        00:00:00 ora_d000_ORADB1

oracle    3305     1  0 17:38 ?        00:00:00 ora_s000_ORADB1

oracle    3669     1  0 18:02 ?        00:00:00 ora_qmnc_ORADB1

oracle    3685     1  0 18:02 ?        00:00:00 ora_cjq0_ORADB1

oracle    3693     1  0 18:03 ?        00:00:00 ora_q000_ORADB1

oracle    3695     1  0 18:03 ?        00:00:00 ora_q001_ORADB1

oracle    3763     1  0 18:07 ?        00:00:00 ora_smco_ORADB1

oracle    4949     1  0 19:18 ?        00:00:00 ora_w000_ORADB1

oracle    4978     1  0 19:19 ?        00:00:00 ora_j000_ORADB1

oracle    4980     1  0 19:19 ?        00:00:00 ora_j001_ORADB1

In order to make a database startup automatically when the system boots, you should add a script to /etc/rc.d/init.d/ directory. This file will include the Oracle provided dbstart and dbshut scripts in it.

Ipcs will report the use of shared memory and semaphores.

[oracle@localhost trace]$ ipcs -a

------ Message Queues --------

key        msqid      owner      perms      used-bytes   messages   

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status     

0x00000000 131072     oracle     600        524288     2          dest        

0x00000000 163841     oracle     600        4194304    2          dest         

0x00000000 327682     oracle     600        4194304    2          dest        

0x00000000 851971     oracle     640        4096       0                      

0x00000000 884740     oracle     640        4096       0                      

0x3243a900 917509     oracle     640        4096       0                      

0x00000000 655367     oracle     600        2097152    2          dest        

------ Semaphore Arrays --------

key        semid      owner      perms      nsems    

0x90c3f0a8 557056     oracle     640        154      

- An instance is a set of background processes and shared memory.

- A database is a collection of data stored on disk.

- An instance can mount and open only a single database.

- A database may be mounted and opened by one or more instances (RAC)