Oracle9i Architecture on Windows

Text description of ntqrf003.gif follows

Oracle Database Architecture -1 Controlfile, Datafile, SGA and PGA

Hi,

In this article I will tell you about Oracle Database Architecture. Oracle Database is the most preferred database in the world, especially in large scale projects. To be able to learn the Oracle Database in detail, the first thing that should be done is knowing the Oracle Database architecture. So what happens in the background when the user starts a transaction.

Oracle_Database

When Oracle Database is running, a memory space that we call the shared global area (SGA) on the operating system is allocated to Oracle. At the same time, some processes that we call background processes are started by Oracle to meet the demands of the database. Oracle Instance consists of SGA + Background processes. The following image shows the Oracle Database architecture beautifully. I’ll explain all the concepts through this image.

Oracle Database Architecture

Control File: The Control file is a file with a .ctl extension that is physically stored on the operating system that is a must for an Oracle database. This file also acts as the brain for our Oracle database. When the Oracle database starts, it reads the parameter file called SPFILE or PFILE and learn the location of the Control file. Because the Control file is the brain of our database, the database needs to find this file to work. If it cannot find the Control File, the Oracle database will not start and will give an error. Thats why, the control file is stored in 2 copies in the production databases. Oracle’s recommended configuration is that we store 3 copies on separate disks. We said the control file is very important. So why is important, what information is in the control file.

  • The database name is stored in this file. When the database is opened, it learns what the name of the database is after reading this file.
  • It contains the location of Datafile files where the data is physically stored.
  • It contains the physical location of the Online Redo log files(transactions are stored in this file) and archive log files(archive of redo log files).
  • It contains RMAN backup information. Therefore, if you take a Full backup without backing up the Control file, this backup becomes invalid. It cannot be restored. Therefore, when taking a backup, the Control file must also backed up.
  • The current version of the SCN (System Change Number) number used during database operations is also stored in the Control file. SCN is a number that is generated after the transactions are committed. SCN is a unique value that is incrementing in order.
  • It contains Checkpoint information. I will explain more about Checkpoint.
  • The database creation date is also stored in this file.
  • It contains the sequence number of the log files where transactions are stored.

As can be seen from the items I mentioned, the Control file is a must for our Oracle database. That’s why, an Oracle DBA must to know that when he/she takes a backup, control file also must backed up. If we configure RMAN as follows, automatic backup of control file will be active.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

We can query where the control file is, as follows:

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 11:47:59 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter control_file;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/data/TESTDB/control01.ctl, /oracle/data/TESTDB/control02.ctl
SQL>

Data Files: These are physical files with the dbf extension that are stored by the Oracle database on the operating system. You can see these files as * .dbf on the operating system. When Oracle database is first created, System, sysaux, undo, user and temp datafiles are created by default. When the user initiates a transaction, Oracle first attempts to find the data that is searched in the Database Buffer Cache. If it cannot find it, it is directed to Data files to find this data. We can see all datafiles belonging to the database with the following query.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 13:26:26 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 800
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES,BLOCKS,MAXBYTES,ONLINE_STATUS from dba_data_files;

FILE_NAME     FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS   MAXBYTES ONLINE_
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ---------- -------
/oracle/data/TESTDB/system01.dbf   1 SYSTEM                          734003200      89600 3.4360E+10 SYSTEM
/oracle/data/TESTDB/sysaux01.dbf   2 SYSAUX                          744488960      90880 3.4360E+10 ONLINE
/oracle/data/TESTDB/undotbs01.dbf   3 UNDOTBS1                        519045120      63360 3.4360E+10 ONLINE
/oracle/data/TESTDB/users01.dbf    4 USERS                             5242880        640 3.4360E+10 ONLINE
/oracle/data/TESTDB/fda01.dbf   5 FDA                            1073741824     131072          0 ONLINE

SQL>

SGA: When Oracle instance starts, memory area of up to the SGA value specified during the installation phase is allocated by Oracle from the operating system. This memory area is called the System Global Area. This memory space is allocated from the physical server’s RAM until the Oracle instance is closed. When the instance is turned off, this memory space is returned to the operating system.

We determine the SGA value when creating the Oracle database, and the correct and optimum configuration of this value will directly affect oracle’s performance. We can look at the SGA value reserved for Oracle Database as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 15:31:32 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 512M
sga_target                           big integer 512M
SQL>

PGA (Program Global Area): This is the memory space allocated from the server’s physical memory when a process is started on an Oracle instance on the server. This memory space is used until the process related to Oracle is terminated. When the process is completed, this memory space is released. You can query the specified value for this memory space in the database as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 4 14:56:59 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 2635M
SQL>

Shared Pool: Shared Pool is an important memory area used in the vast majority of database operations. Because the query of all the transactions, their execution plans (work plans), the parsed and compiled PL/SQL codes are always stored in this memory area. The shared pool area consists of the following components.

Shared_pool_comp

Library Cache: This is one of the most important areas in the shared pool. Because when an SQL clause comes into the Oracle database, Oracle first checks whether it has been executed or not by looking at the library cache. If the queryhas been execute before, Oracle uses the previous execution plan without parsing this SQL.This process is called soft parse. If the query has not been executed before, oracle will parse the executed SQL and save it to the Shared SQL Area in the library cache. This process is called hard parse.One of the most important tasks of a DBA is to identify and fix unnecessary hard parse operations in a database.One of the most important reasons for unnecessary hard parse operations is that;There are upper-lower case differences in the same SQL statements:

1 nolu sorgu: select * from hr.personel;
2 nolu sorgu: select * from hr.Personel;

Another method to use as a solution is to use bind Variable for the same query types:

SQL> select name,surname,phone from hr.personel where id=1;

In the above query, personel information with an id value of 1 is requested. Imagine that this query is always requested with different id values. Oracle will repeatedly parse this query in the library cache even though the query is the same. So instead of making a soft parse and consuming less than Cpu source, it will make the hard parse and consumes unnecessary CPU resources. At this point, we assign the “bind variable” value for the id column to ensure that the query uses the same execution plan for each different incoming id value. The use of the above query with the bind variable is as follows.

SQL> variable person_id number
SQL> exec :person_id : =180251;
SQL> select name,surname,phone from hr.personel where id :=person_id;

If we use Bind variable, Oracle will use the same execution plan even if the id value in the queries changes.

Dictionary Cache: This memory space stores the metadata of our database.

That is, the following informations is stored in Dictionary cache:

  • Who is authorized to access a table
  • Tablespace informations
  • Column information of a table

This memory space is often used when parsing a query.

So I came to the end of my article. In the next article, I will continue to explain the Oracle Database Architecture.

Oracle Database Architecture -2 Buffer Cache, Redolog Buffer, Onlinelog and Archivelog:

Hi,

I will continue to explain Oracle database architecture in this article. I’m adding the following image to be memorable. If you didn’t read the first article, I suggest you read the first article.

Oracle Database Architecture

Read First Article before this.https://ittutorial.org/oracle-database-architecture-1-controlfile-datafile-sga-and-pga/embed/#?secret=NZwx4c3FeX

Database Buffer Cache: Data of a transaction that started by a user or application is stored in this memory space.

For example: When an insert, update, or delete operation is performed on a table, the corresponding change is not written directly to the datafiles. It is stored in the buffer cache for a while(This data called as dirty data).

The most frequently used and most up-to-date data in the database is stored in here, and this memory space is common to all users. Data stored in the Buffer cache are written to the data files after a while.

You can flush the data in the Buffer Cache with the following query:

alter system flush buffer_cache;

However, you should not execute the above command in production systems unless required. Because when the buffer cache is emptied, all queries or transactions will make I / O from the physical disk, which means that our queries will slow down.

Redo Log Buffer: The Oracle database stores each transaction’s record. When a user or an application initiates a transaction, the transaction is initially written to the Redo log buffer. Periodically, records in the Redo log buffer area are written to the Online Redo log files by the LGWR process. Storing records of transactions is required for recovery when the instance crashes.

Online Redo log Files: These files are physical files on the operating system where all transactions in the database are stored. These files store all changes in the database, and as I mentioned above, the records accumulated in the Redo log buffer area are recorded here at regular intervals. If the database is in Archive mode, these files are archived periodically as a result of a switch operation.

The database must be in archive mode in order to make a backup in online mode. If there is a damage to the database, archive files will also be required when returning from the backup.

You cannot open the database consistently without applying Archive Logs. Once the corresponding archive logs have been applied, online redo logs can be applied to recover the data until the last time.

Online Redo Log

There are logical Redo log groups on the database where the Redo log files are stored, and each group has 2 identical files. This is necessary for redundancy and consistency. When a file is corrupted, database continue to work using other member of the redo log group. You can also create more than 2 members for a redo log group. The information in the Redo log buffer is written to all the members of the redo log group at the same time. If any of the online Redo log groups are fully filled, the new logs will be written to another group. This process is called a log switch

You can see the online redo logs on the database with the following query:

bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 16:54:27 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 600
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1      18622   52428800        512          1 YES ACTIVE                12514027 03-JAN-13     12514404 03-OCT-13
         2          1      18623   52428800        512          1 NO  CURRENT               12514404 03- JAN -13   2.8147E+14
         3          1      18621   52428800        512          1 YES INACTIVE              12513648 03- JAN -13     12514027 03-OCT-13

Archivelog: If you have a Oracle database that is running in archive log mode, redo log files are copied to archive log files after log switch.

Archive files are files that are required to recover instance consistently. In addition, you can recover your database to a specific time using archive log files.

You can check archive log files with the following query:

bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 17:12:20 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 900
SQL> select NAME,DEST_ID,THREAD#,SEQUENCE#,CREATOR,APPLIED from v$archived_log where rownum<3;

NAME     DEST_ID    THREAD#  SEQUENCE# CREATOR APPLIED
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ------- ---------
/oracle/ARCH/1_15266_821030721.arc   1          1      15266 ARCH    NO
/oracle/ARCH/1_15267_821030721.arc   1          1      15267 ARCH    NO

SQL>

You can find the last article of this article series below.

Oracle Database Architecture -3 SMON, PMON, DBWR,CKPT and LGWR Processes

Mehmet Salih Deveci February 28, 2018 8 Comments

Hi,

I will continue to explain Oracle database architecture in this article. I’m adding the following image to be memorable. If you didn’t read the previous articles, I suggest you read these articles.

Oracle Database Architecture

Read previous article before this.https://ittutorial.org/oracle-database-architecture-2-buffer-cache-redolog-buffer-onlinelog-and-archivelog/embed/#?secret=MkbLwUFPqS

SMON (System Monitor): It is an important process responsible for recovering Oracle Instance. If this process does not work, the database is down.

This process ensures that the instance to open consistently, during the database opening using the online redo log files when the Oracle database is closed inconsistently. This process also allows the recovery of suddenly killed transactions.

We can see this process via the operating system as follows.

bash-4.1$ ps -ef | grep smon
oracle    9055     1  0 13:38 ?        00:00:00 ora_smon_TESTDB
oracle    9250  8803  0 14:24 pts/0    00:00:00 grep smon
bash-4.1$

When we kill this process through the operating system, the database will shut down suddenly as follows.

bash-4.1$ ps -ef | grep smon
oracle    9055     1  0 13:38 ?        00:00:00 ora_smon_TESTDB
oracle    9258  8803  0 14:28 pts/0    00:00:00 grep smon
bash-4.1$ kill -9 9055
bash-4.1$ ps -ef | grep smon
oracle    9260  8803  0 14:29 pts/0    00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 4 14:29:08 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>

PMON (Process Monitor): This process frees up system resources that are used by processes that have failed or are suddenly terminated, and will give back this resources to the server.

It also allows Oracle instance to communicate with the listener.

We can see this process on the operating system as follows.

bash-4.1$ ps -ef | grep pmon
oracle    9423     1  0 14:37 ?        00:00:00 ora_pmon_TESTDB
oracle    9559  8803  0 14:37 pts/0    00:00:00 grep pmon
bash-4.1$

RECO (Recoverer Process): This process enables the completion of unfinished operations.

DBWn (Database Writer): The DBW process is a process that shuttles between Datafiles and Database Buffer Cache. That is, when a transaction starts, if the corresponding blocks are not in the buffer cache, DBW move these blocks from the datafiles to the Buffer cache. In the same way, it writes the dirty blocks that should be written to the Datafiles to the Datafiles from the Buffer cache.

bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 4 13:39:44 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_writer_process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     1
SQL>

These processes work when the following events occur, and the changing blocks in Buffer Cache are written to the datafiles.

  • When the memory space in the database buffer cache is full, these blocks are written to the datafiles starting from the oldest blocks in this memory area.
  • If the Checkpoint process is triggered.
  • When a tablespace is set to read-only mode.
  • When a tablespace is taken offline.
  • When any table is dropped or truncated.

The following code can be used to manually write the data in the buffer cache to the datafiles.

SQL> Alter system checkpoint;

LGWR (Log Writer Process): This process is a process that writes the data in the buffer memory to physical files like DBW process. This process runs between the Redo log buffer and the Online Redo log files. Writes transaction information in Redo log buffer to Online redo log files respectively. The LGWR process writes the data in the Redo log buffer to the Online Redo log files when the following conditions occur.

  • When a commit operation occurs
  • When a log switch occurs
  • Every 3 seconds
  • Average, when the redo log size is 1 mb.
  • When 1/3 of the Redo log buffer area is full

CKPT (Checkpoint Process): When this process is triggered, the Database Writer (DBW) process writes dirty blocks in the database buffer cache to datafiles. It also updates the header information of Datafiles.

If this process is triggered at very frequent intervals, the database will slow down because disk I / O will increase.

If it is rarely triggered, it will take some time to recover instance when the Oracle database instance crashes suddenly. Because changing blocks are not written to datafiles and the number of changing blocks is accumulated. Therefore, during the recovery process, the number of blocks to be written from the online redo log to the data files will be more.

Determining the frequency of triggering of this process is important for the above reasons.

ARCn ( archiver process ): This is a process that is activated when the database is in archive mode. This process copies a copy of the redo log file to the archive file during the log switch operation when the online redo log groups are full. Another task of this process is send Redo log files to the “disaster/standby” server in the disaster recovery scenario.

So, I am completing the article series of the Oracle database architecture with this article.

Shajib Mahmud

Leave a Reply

Your email address will not be published. Required fields are marked *