Oracle - SPLessons

Oracle Database Instance

Chapter 12

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

Oracle Database Instance

Oracle Database Instance

shape Introduction

An arrangement of memory structures that oversees database documents is called Oracle Database Instance. The create database statement are a collection of physical files on the disc inside the central database. The instance accords with its associated information in the database are serves and the clients.

Database is combined with not less than one Oracle database instance. Since a database exists on disc and instance exists in memory, a database can exist without an instance and instance can exist without a database.

Structure of database instance

shape Description

At the starting of an instance, the system global area will be allocated in the memory by oracle database and then background processes will be started. The various purposes that the SGA can be included are as follows.

  • Buffering redo information before composing it to the online redo log documents.
  • Putting away SQL execution plans.
  • Maintaining internal data structures that are accessed by many processes and threads concurrently.
  • Caching data blocks read from disk.

The Oracle processes contribute the SGA, which comprises of server processes and background processes, executing in a specific system. The operating system by which the oracle  processes associated with the SGA differs according. The background processes are a part of Oracle Database Instance. The process memory and the server processes are allocated in these processes, that contained in the instance. When server processes terminate the instance continues to function.

Configuration of database instance

shape Description

Oracle Database Instance can be keep running in both of the accompanying fundamentally unrelated arrangements.

Single-instance configuration

Between the instance and the database one-to-one relationship exists.

Oracle Real Application Clusters (Oracle RAC) configuration

A one-to-numerous relationship exists among the  instances and  database.

Instance Startup and Shutdown

shape Description

Instance and Database Startup-Manually starting an instance and then mount and open the database, making it accessible for clients and can utilize the SQL*Plus STARTUP command, Oracle Enterprise Manager , or the SRVCTL utility to process these stages.

The following steps executed by oracle database, while processing for shut down stage to an open stage in the database.

Opening the database

shape Description

For normal database operations, opening of mounted database is accessible. Database can be opened and access its information by any valid client. Database can be opened by Database Administrator and can access the data for any normal use. Oracle database performs the following actions while opening the database.

Acquires an undo table space

When different undo table spaces exists, then the UNDO_TABLESPACE initialization parameter assigns the undo table space to use and first available undo table space is chosen, if the parameter is not arranged.

Opens the data files in table spaces other than undo table spaces

When the database shuts down the table space will be in offline mode,then the data files will also be in offline mode.Again when reopened the database, the data files will be in the same mode.

Mounting the database

shape Description

To associate the database with the instance, the database has to mount with the instance. To mount the database,the names of the database control files specified in the CONTROL_FILES initialization configuration parameter and opens the files, when the database instance is opened. Oracle Database peruses the control document to find the names of the information record and the redo log files that it will endeavor to execute when the database is opening.

When the database is closed and is available only to database administrators in the mounted database. For completing specific maintenance operations the administrator can close the database. For normal operations the database is not accessible.

  • To mount the same database concurrently, oracle database will allow multiple instance, that can make the database available to multiple instances for CLUSTER_DATABASE initialization configuration parameter. The behavior of database depends on these configurations.
  • The instance can mount the database if the first instance that mounts a database is said to be false

Instance begin without mounting database

shape Description

It performs the following basic steps, while performing the oracle database start an instance.

  • Read the parameter document to decide the estimations of in-statement parameters.
  • Allots the SGA taking into account the initialization parameter settings.
  • Begins the Oracle foundation forms.
  • Opens the ready log and follow documents and composes all unequivocal parameter settings to the ready log in legitimate parameter punctuation.
  • Looks for a server parameter record in a platform-specific default area and, if not found, for a content introduction parameter document.

Recovery of an instance

shape Description

Recuperation is the procedure of applying records in the online redo log to information document to remake the changes made after the most latest checkpoint. Instance recuperation occurs when an administrator endeavors

The database is in a consistent state after an instance failure occur to ensure instance recovery. Oracle Database manages database changes and left the database files in an inconsistent stage. To open a database that was beforehand closed down conflictingly.

All the changes generated by an instance is recorded in redo thread. One thread of redo containing a single instance database, whereas  multiple redo threads are containing in single oracle RAC, one for every  instance in the database.

The log writer writes both the the transaction SCN to the online redo log and remaining redo entries in memory, when the transaction is committed. Whenever it is most efficient the database writer process writes modified data blocks to the data files. Hence, temporarily uncommitted changes may exist in the data files while in the data files committed changes do not exist.

If database fails to open an instance during an open instance, shut down or abort statement can occurred.

  • When the instance is failed,the information files containing the changes will not be committed. To ensure transnational consistency, roll back this changes.
  • The changes must be reapplied to the database when data block committed by the transaction are not written in the data files.


shape Description

For instance recovery,database shut downs and Oracle database operation, check points plays an important role in it. The following are the terms that related to check points.

  • The checkpoint position will be indicated by the data structures, where instance recovery will begins, which is the SCN in the redo stream.
  • The composition of changed database supports in the database cushion store to circle.
  • Inside the database buffer cache the oldest dirty buffer, the position of check points will be determined. The checkpoint position goes about as a pointer to the re-try stream and is put away in the control record and in every information document header.

To achieve the following goals, oracle database use check points

  • Ensure that all committed data is written to disk during a consistent shutdown.
  • Ensure that dirty buffers in the buffer cache are written to disk regularly.
  • Reduce the time required for recovery in case of an instance or media failure.

Oracle Database 12c Alert Log

shape Description

The oracle alert log is a trace file. A trace is a programming greek term that essentially means some kind of predefined event that can be captured and analysed. Such as

  • Database messages
  • ORA-errors
  • Administrative options(startup, shutdown, DDL)
  • Initialization parameters with non-default settings

And some part of the Automatic Diagnostic Repository(ADR), and is crucial for monitoring database/instance health.

View the alert log will read the plain text representation and parse the xml formatted files that are used in the enterprise manager cloud control the oracle 12c,and also used in the ADRCI command line utility

Dynamic performance views

shape Description

Dynamic performance views are called the v$ views because they all start with a ‘v’ and a dollar sign. Views are database objects that can be thought of as just a stored SQL query. The dynamic performance views are virtual tables and there are not persistent real tables because they store in dynamic data and contain the following key features

  • Virtual tables that records current database activity
  • System and session parameters
  • Memory usage and allocation
  • File state(backup,diagnostic,etc)
  • job/task execution states
  • sql execution states

The v$ views are used for traditional and pluggable databases. In RAC environments the DP views are gv$ instead of v$. The ‘g’ stands for grid and the gv$ view shows data for the entire cluster. Now you can go to an individual RAC cluster node and run v$ views to look at states for only that node, gv covers the entire instance. In RAC environments, the GV$DPVs show aggregate data over the entire cluster DPVs are available for both standards and pluggable databases.

shape Syntax

create view <table_name>as select <column_name> FROM <table_name> where <condition>;

table_name => Any accurate table.

column_name => The columns that inserted in the table.

Condition => The condition to get the specific result set . 

shape Examples

By viewing the below example, the concepts of simple view can be understood easily.

sql&gt; select * from cars;
| car_id | car_name | cost   |
|      1 | audi     |  52642 |
|      2 | skoda    | 526400 |
|      3 | volva    |  52640 |
|      4 | volva    |  52000 |
|      5 | hummer   |  41400 |
5 rows in set (0.00 sec)

sql&gt; create view cheapcars as select name from cars where cost&amp;lt;52640; 
ERROR 1054 (42S22): Unknown column 'name' in 'field list' 
sql&gt; CREATE VIEW Cheapcars AS SELECT car_name from cars where cost&amp;lt;52640; 
Query OK, 0 rows affected (0.13 sec) 
sql&gt; select * from cheapcars;
| car_name |
| volva    |
| hummer   |
2 rows in set (0.16 sec)

In the above example, a simple table cars has been created and by applying view operation to that table, like inserting some condition on column name.(i.e.,cost of car<52640 is the condition, and it show the cost of cars less than that value).  


shape Key Points

  • Oracle database instance – An instance is an memory that oracle database used to manipulate data in the database.
  • Oracle RAC configuration – A one-to-numerous relationship exists among the  instances and  database.
  • Single instance configuration – Between the instance and the database one-to-one relationship exists.
  • Instance start-up and shut down – means opening and closing of database.
  • Recovering of an instance – Rolling forward to recover the data that has not been recorded.
  • Check point – Event which synchronizes the changes that data blocks in memory with the data files on disk.