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.

Read First Article before this.

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

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.

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.

https://ittutorial.org/oracle-database-architecture-3/

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/