Sunday , March 3 2024

Oracle Database Architecture Part 2

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 Part 1

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:

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:

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:

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

Oracle Database Architecture Part 3


About Mehmet Salih DEVECİ

Leave a Reply

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