Oracle Concepts - 13 Oracle Database Instance

The Oracle RDBMS - Instance And Database

Database Architecture - Instance & Database

• Instance
  • Background Processes (CPU)
  • Memory structures (RAM)
• Database
  • Datafiles (Disk)

The Instance - Background Memory Processes

Database Architecture - Background Processes

• Part of the Oracle instance
• Align with the CPU resource
• Run as a part of the Oracle Kernel
• Multi-threaded (Windows) vs Multi-process (UNIX & Linux)
  • PMON - Process Monitor
    • Regulates all other processes
    • Cleans up dead processes
    • Must be alive
    • View in Linux with ‘ps -ef | grep pmon’
  • SMON - System Monitor
    • Responsible for instance recovery
    • cleanup of temporary files
  • DBWn - Database Writer
    • No operations done on disk - in memory instead
    • Reads from disk and writes back to disk
    • Multi Processes - DBW0, DBW1, DBW2, ..etc
    • DBWR writes dirty buffers to files when one of the following occurs
      • - dirty list reaches a threshold value
      • - process scans a specified number of blocks in LRU list but doesn't find a free block
      • - timeout occurs
      • - checkpoint occurs
    • Checkpoint forces DBWR to write dirty blocks to datafile BUT writing of dirty blocks to datafile in other scenarios doesn't force any checkpoint
  • CKPT - Checkpoint Process
    • Dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk
    • Blocks and buffers - dirty and clean
    • CKPT signals writing of dirty buffers to disk
    • Full and incremental checkpoints
  • LGWR - Log Writer Process
    • Recoverability architecture
    • Every change written to files for recovery
    • First memory, then disk as redo logs
  • ARCn - Archiver Process
    • NOARCHIVELOG vs ARCHIVELOG mode
    • copies redo logs archivelogs
    • Multi destinations
  • Other Background Processes
    • MMON - writes out performance metrics
    • MMAN - automatically manages memory
    • LREG - New process that registers databases

The Instance - System Global Area

Database Architecture - System Global Area (SGA)

• Part of the Oracle instance
• ‘Everything is a cache’
• Volatile Storage (Memory) Faster than non-volatile storage (disk)
• SGA is system or Shared Global Area
• Shared memory
  • Database Buffer Cache
    • Blocks read into buffers
    • Users require the same data at times
    • Keeps data in cache based on the LRU (Least-Recently-Used)
  • Shared Pool
    • The concept of parsing - expensive operation
    • Library Cache - shared stored SQL
    • Data dictionary cache - stored metadata
  • Log Buffer
    • Log writer process writes changes to memory first
    • three conditions to write data (Logs flashed Out or writes to disk):
      • Commit occurs
      • Log buffers become full
      • Mandatory checkpoint occurs
  • Large Pool
    • Benefits of caching - free up shared pool
    • Used in shared server architecture
    • caching for backup and recovery
  • Streams/Java Pools
    • Streams Pool - support oracle streams
    • Java Pool - can be used to instantiate Java objects

The Instance - Program Global Area

Database Architecture - Program Global Area (PGA)

• Second type of memory
• Private memory - sorting, session variables
• the old way - constrained by number of users (inefficient)
• the new way - a pool of memory (start with oracle 9i) 

Oracle Database and Instance Architecture

- Database instance is a set of memory structures that manage database files

Database Instance

- Database Instance Configurations

• Single-instance > database > one-to-one > instance
• RAC > database > one-to-many > instances

Instance Startup

- instance startup

• startup occurs when DB transitions between states
• states can be controlled by the DBA
• NOMOUNT state
  • Parameter file is read
  • Memory allocated
  • Background processes stated

• MOUNT state
  • Control file is read
  • Datafile become visible, but not availabile
  • The recovery stage
• OPEN state
  • Datafile integrity is verified – if missing, stays in MOUNT
  • Database made available to users

SQL> shutdown abort

SQL> startup nomount

SQL> alter database mount

SQL> alter database open

Instance and Database Startup

- Instance Is Started

• search for spfile if not then pfile
• Reads the parameter file to determine the values of initialization parameters
• Allocates the SGA based on the initialization parameter settings
• Starts the Oracle background processes
• Opens the alert log and trace files and writes all explicit parameter settings

- Database Is Mounted

• database is closed and accessible only to database administrators(maintenance operations)
• database is not available for normal operations

- Database Is Opened (Read-only, Read-Write)

• available for normal database operations
• Opens the online data files in tablespaces 
• Acquires an undo tablespace
• Opens the online redo log files

Instance Shutdown

- instance shutdown

• Differences in shutting down DB based on the way it occurs
  • SHUTDOWN (NORMAL)
  • SHUTDOWN TRANSACTIONAL
  • SHUTDOWN IMMEDIATE
  • SHUTDOWN ABORT
• In Windows, must also shutdown services to release resources
  • services.msc

SQL> shutdown abort

SQL> startup

SQL> shutdown immediate

Instance and Database Shutdown Sequence

- steps whenever an open database is shut down consistently:

• Database closed - The database is mounted, but online data files and redo log files are closed.
• Database unmounted - The instance is started, but is no longer associated with the control file of the database.
• Database instance shut down - The database instance is no longer started.

- Shutdown Modes

• ABORT - use only with problems, recovery begin automatic when starting database
• IMMEDIATE - most you will use
• TRANSACTIONAL - if you have enough time
• NORMAL - if you have another life

- checkpoint may be

• data structure that indicates the checkpoint position, which is the SCN in the redo stream where instance recovery must begin
• writing of modified database buffers in the database buffer cache to disk

- Basic Instance Recovery Steps: Rolling Forward and Rolling Back

- Initialization parameters are configuration parameters that affect the basic operation of an instance. The instance reads initialization parameters from a file at startup.

- Parameter Files

• SPfile > server parameter file > recommended > Binary file
• Pfile > initialization parameter file > legacy implementation > text file

parameter File

Parameter file

•  Database configuration is controlled by parameters
• Two ways to store those parameters
• Located in
  •  $ORACLE_HOME\database → Windows
  • $ORACLE_HOME\dbs → Linux
• Initialization Parameter File (PFILE)
  • Text based
• Server Parameter File (SPFILE) – start with oracle 9i
  • Binary, read into memory

$ sqlplus / as sysdba

SQL> create pfile from spfile

$ cd $ORACLE_HOME/dbs/

$ gedit initorcl.ora

Modifying parameters

Modifying Parameter

•  Two Types of parameters
  • Dynamic – can be modified 'hot' if spfile is used
  • Static – require database to be restarted
• if pfile is used, all parameters are static
• determine if your database was started with a PFILE or SPFILE

$ sqlplus / as sysdba

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM

sys.v_$parameter WHERE name = 'spfile';

SQL> show parameter

SQL> show parameter undo

SQL> alter system processes=170 → can't be modified (static parameter)

SQL> show parameter undo_retention

SQL> alter system set undo_retention=1200 scope=memory;

SQL> alter system set undo_retention=1200 scope=both; → (memory & spfile)

SQL> show parameter processes;

SQL> alter system set processes=170 scope=spfile;

SQL> shutdown immediate

SQL> startup

SQL> show parameter processes;

SQL> show parameter undo_retention;

$ sqlplus / as sysdba

### list database parameter

SQL> show parameter

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

active_instance_count                integer

aq_tm_processes                      integer     0

archive_lag_target                   integer     0

asm_diskgroups                       string

......................

### list one parameter

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

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

db_name                              string      orcl

### main query paramter

SQL> desc v$parameter
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 ISBASIC                                            VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER

SQL> select num,name,type from v$parameter;

       NUM NAME                                                                                   TYPE
---------- -------------------------------------------------------------------------------- ----------
        38 lock_name_space                                                                           2
        39 processes                                                                                 3
        40 sessions                                                                                  3
        41 timed_statistics                                                                          1
        42 timed_os_statistics                                                                       3
        43 resource_limit                                                                            1
        44 license_max_sessions                                                                      3

..................................

### list spfile loaction in the database
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/oracle/app/oracle/produc
                                                 t/11.2.0/dbhome_2/dbs/spfileor
                                                 cl.ora

### create pfile from spfile with defaults loaction
SQL> create pfile='$ORACLE_HOME/dbs/initorcl.ora' from spfile;

File created.

### create pfile from memory
SQL> create pfile='$ORACLE_HOME/dbs/initorcl.ora' from memory;

File created.

### create spfile from pfile with defaults loaction
SQL> create spfile from pfile

SQL> ! # connect to host shell

$ cd $ORACLE_HOME/dbs

$ ls -la

-rw-r--r--  1 oracle oracle 2851 May 15  2009 init.ora  # sample file use for troubleshooting

-rw-r-----  1 oracle oracle 1118 Sep 21 05:52 initorcl.ora  # pfile 

-rw-r-----  1 oracle oracle 3584 Sep 21 04:29 spfileorcl.ora  # spfile

[oracle@localhost dbs]$ exit # exit from host and comeback to sql plus command line
exit

### locate control files
SQL> set lines 2000
SQL> select name, value from v$parameter where name = 'control_files';

NAME                 VALUE
-------------------- -----------------------------------------------------------------------------------------------
control_files        /home/oracle/app/oracle/oradata/orcl/control01.ctl, /home/oracle/app/oracle/flash_recovery_area
                     /orcl/control02.ctl

SQL>  show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradat
                                                 a/orcl/control01.ctl, /home/or
                                                 acle/app/oracle/flash_recovery
                                                 _area/orcl/control02.ctl

### startup database from pfile if any problems with spfile
[oracle@localhost ~]$ $ sqlplus / as sysdba

SQL> startup pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora

Static and Dynamic Initialization Parameters

Characteristic	Static	Dynamic
Requires modification of the parameter file (text or server)	Yes	No
Requires database instance restart before setting takes affect	Yes	No
Described as "Modifiable" in Oracle Database Reference initialization parameter entry	No	Yes
Modifiable only for the database or instance	Yes	No

- Dynamic parameters are grouped into 

• session-level parameters, which affect only the current user session
• system-level parameters, which affect the database and all sessions

- scope of a parameter change

• SCOPE=MEMORY > Changes apply to the database instance only, not to be survive database restart
• SCOPE=SPFILE > need instance restart, Changes are written to the server parameter file but do not affect the current instance
• SCOPE=BOTH > Changes are written both to memory and to the server parameter file. This is the default scope when the database is using a server parameter file.

Automatic Diagnostic Repository (ADR)

ADRCI

• old way of managing log and traces files – Non Standard files
• starting in 11g, consolidated into the ADR
• Automatic Diagnostic Repository
• Interface using ADRCI
• can be used to package problem incidents for oracle support

###  V$DIAG_INFO describes the state of Automatic Diagnostic Repository (ADR) functionality using NAME=VALUE pairs

SQL> desc v$diag_info;

 Name                                      Null?    Type

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

 INST_ID                                            NUMBER

 NAME                                               VARCHAR2(64)

 VALUE                                              VARCHAR2(512)

SQL> set pages 0

SQL> set lines 2000

SQL> select name, value from v$diag_info;

Diag Enabled         TRUE

ADR Base             /home/oracle/app/oracle

ADR Home             /home/oracle/app/oracle/diag/rdbms/orcl/orcl

Diag Trace           /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace

Diag Alert           /home/oracle/app/oracle/diag/rdbms/orcl/orcl/alert

Diag Incident        /home/oracle/app/oracle/diag/rdbms/orcl/orcl/incident

Diag Cdump           /home/oracle/app/oracle/diag/rdbms/orcl/orcl/cdump

Health Monitor       /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm

Default Trace File   /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15439.trc

Active Problem Count 1

Active Incident Coun 11

t

11 rows selected.

ADR Directory Structure for an Oracle Database Instance

$ adrci

adrci> help

adrci> show base

adrci> show homes

adrci> show incident

adrci> show problem

adrci> show tracefile

adrci> show alert

adrci> exit

Alert Log And Trace Files

Alert Log and Trace Files

• Two types of log files
  • Alert log – first stop when some problem with database
  • Trace files – used for oracle support
• Alert log
  • Primary source of problem information
  • can be viewed as text or through ADRCI
• Trace files – comes in flavor (background processes & user processes)
  • Purely diagnostic information
  • mostly unreadable, except by Oracle support
  • we have two files extension (.trc & trm) – trm binary file stored data with xml format readable by ADR
  • dump is a special type of trace file. Whereas a trace tends to be continuous output of diagnostic data, a dump is typically a one-time output of diagnostic data in response to an event (such as an incident)

$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

  orcl_ckpt_2234.trc – trace file about a checkpoint process