KEY ORACLE BACKGROUND PROCESSES RELATED TO BACKUP AND RECOVERY

The main background processes which are related to Backup & Recovery process are :

A) THE CHECKPOINT PROCESS

B) THE LOG WRITER PROCESS

C) THE ARCHIVER PROCESS

Read further for brief description on these important oracle background processes.

A) THE CHECKPOINT PROCESS (ora_ckpt_<SID>)

The checkpoint process is a key database ‘concept’ and it does three important things:

• It signals the database write process (DBWn) at each ‘checkpoint’ to process all modified buffers in the SGA buffer cache (temporary location of DB blocks) to the database data files(permanent & original location of DB blocks). After it has been done, online redo log files can be recycled.
• It updates the datafile headers with the checkpoint information (even if the file had no changed blocks).
• It updates the control files with the checkpoint information.

Set parameter LOG_CHECKPOINTS_TO_ALERT=TRUE to observe checkpoint start and end times in the database alert log.

What causes Checkpoint?

i) The most common reason is redo log switch.

You can switch logfile manually to check this in the alert log.

SQL> alter system switch logfile;

System altered.

Alert.log entry>>
———————————————————
Mon Feb 03 14:24:49 2014
Beginning log switch checkpoint up to RBA [0x8.2.10], SCN: 1006600
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /u01/oracle/DB11G/oradata/brij/redo02.log
Mon Feb 03 14:24:49 2014
Archived Log entry 2 added for thread 1 sequence 7 ID 0x4c45a3de dest 1:
———————————————————

ii) Checkpoints can also be forced with the ALTER SYSTEM CHECKPOINT; command. We generally do checkpoint before taking backups. At some point in time, the data that is currently in the buffer cache would be placed on disk. We can force that to happen right now with a user invoked checkpoint.

iii) There are incremental checkpoints controlled by parameters such as FAST_START_MTTR_TARGET and other triggers that cause dirty blocks to be flushed to disk.

Frequent Checkpoints usually means redo log file size is small (and it also means a slow system). But if you increase your redo log files size very high, it will also increase the mean time to recover. So a DBA should determine log file size on the basis of various factors like database type (DWH/OLTP etc), Transactions volume, database behavior as shown in alert log error messages etc.

CKPT actually took one of the earlier responsibility of LGWR. LGWR was responsible for updating the data file headers before database release 8.0. But with increasing database size and number of data files this job was givne to CKPT process.

B) THE LOG WRITER PROCESS (ora_lgwr_<SID>)

LGWR play important role of writing the data changes from redo log buffer to online redo log files. Oracle’s online redo log files record all changes made to the database in sequential manner (SCN is the counter).

Why we are multiplexing only redo log files and not the datafiles?

Oracle uses a “writeahead” protocol, meaning the logs are written to before the datafiles are. Data changes aren’t necessarily written to datafiles when you commit a transaction, but they are always written to the redo log. Before DBWR can write any of the blocks that are changed to disk, LGWR must flush the redo information related to these blocks. Therefore, it is critical to always protect the online logs against loss by ensuring they are multiplexed.

Redo log files come into play when a database instance fails or crashes. Upon restart, the instance will read the redo log files looking for any committed changes that need to be applied to the datafiles.

The log writer (LGWR) writes to the online redo files under the following circumstances:

• At each commit
• Every three seconds
• When the redo log buffer is one-third full or contains 1MB of cached redo log data
• When LGWR is asked to switch log files

Remember that the data will not reside in the redo buffer for very long. For these reasons, having an enormous (hundreds/thousands of megabytes) redo log buffer is not practical; Oracle will never be able to use it all since it pretty much continuously flushes it.

On our 11gr2 database, the size is about 6MB. The minimum size of the default log buffer is OS-dependent

SQL> show parameter log_buffer;

NAME TYPE VALUE
———————————— ———– ——————————
log_buffer integer 6266880

Or you can also see the memory value when the databse is starting

ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 536874064 bytes
Database Buffers 289406976 bytes
Redo Buffers 6565888 bytes <<<<< ~6MB
Database mounted.
Database opened.

LGWR does lots of sequential writes(fast operation) to the redo log. This is an important distinction and one of the reasons that Oracle has a redo log and the LGWR process as well as the DBWn process. DBWn does lots of scattered writes (slow operation). The fact that DBWn does its slow job in the background while LGWR does its faster job while the user waits gives us better overall performance. Oracle could just write database blocks directly to disk when you commit, but that would entail a lot of scattered I/O of full blocks, and this would be significantly slower than letting LGWR write the changes out sequentially.

Also, during Commit, the lengthiest operation is, and always will be, the activity performed by LGWR, as this is physical disk I/O. For that reason LGWR is designed such that as you are processing and generating the redo log, LGWR will be constantly flushing your buffered redo information to disk in the background. So When it came to the COMMIT, there will not have much left to do and commit will be faster.

REMEMBER that the purpose of the log buffer is to temporarily buffer transaction changes and get them quickly written to a safe location on disk (online redo log files), whereas the database buffer tries to keep blocks in memory as long as possible to increase the performance of processes using frequently accessed blocks.

C) THE ARCHIVER PROCESS (ora_arc<n>_<SID>)

Although an optional process, but should be considered mandatory for all production databases!

The job of the ARCn process is to copy an online redo log file to another location when LGWR fills it up, before they can be overwritten by new data.
The archiver background process is used only if you’re running your database in archivelog mode. These archived redo log files can then be used to perform media recovery. Whereas online redo log is used to fix the data files in the event of a power failure (when the instance is terminated), archived redo logs are used to fix data files in the event of a hard disk failure.

For example, If we lose the disk drive containing the system.dbf  data file , we can go to our old backups, restore that old copy of the file, and ask the database to apply all of the archived and online redo logs generated since that backup took place. This will catch up that file with the rest of the data files in our database, and we can continue processing with no loss of data.

ARCH copies the online redo log a bit more intelligently than how the operating system command cp or copy would do: if a log switch is forced, only the used space of the online log is copied, not the entire log file.

CHECK DATABASE IS IN ARCHIVE MODE OR NOT:

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

CHECK NUMBER OF ARCH PROCESS IN THE DATABASE:

SQL> select * from v$archive_processes where STATUS=’ACTIVE’;

PROCESS STATUS LOG_SEQUENCE STAT ROLES
———- ———- ———— —- ————————————
0 ACTIVE 0 IDLE
1 ACTIVE 0 IDLE HEART_BEAT
2 ACTIVE 0 IDLE NO_FAL NO_SRL
3 ACTIVE 0 IDLE