| Lesson 5 | Redo log init.ora parameters |
| Objective | Set the redo log parameters. |
Set the Redo Log Parameters
Redo Log Behavior
Several init.ora parameters influence the behavior of the redo logs. However, the most obvious parameter is one that is not in the init.ora file.
The ARCHIVELOG parameter is set with the ALTER DATABASE command. Oracle will use the ARCH server process to move the redo log files from the online redo log file system to the archived redo log directory. In some databases, especially those that are loaded with batch jobs, they forego recoverability and run Oracle in NOARCHIVELOG mode.
NOARCHIVELOG mode will run faster because Oracle does not have the overhead of the ARCH process.View the code below to review the redo log init.ora parameters:
The ARCHIVELOG parameter is set with the ALTER DATABASE command. Oracle will use the ARCH server process to move the redo log files from the online redo log file system to the archived redo log directory. In some databases, especially those that are loaded with batch jobs, they forego recoverability and run Oracle in NOARCHIVELOG mode.
NOARCHIVELOG mode will run faster because Oracle does not have the overhead of the ARCH process.View the code below to review the redo log init.ora parameters:
| log_archive_start | FALSE | Start archival process on SGA initialization |
| log_archive_buffers | 4 | Number of buffers to allocate for archiving |
| log_archive_buffer_size | 127 | Size of each archival buffer in log file blocks |
| log_archive_dest | %RDBMS80%\ | Archival destination text string |
| log_archive_duplex_dest* | Duplex | archival destination text string |
| log_archive_format | ARC%S.%T | Archival destination format |
| log_buffer | 8192 | Redo circular buffer size |
| log_checkpoint_interval | 10000 | Redo blocks checkpoint threshold |
| log_checkpoint_timeout | 0 | Maximum time interval between checkpoints in seconds |
| log_block_checksum | FALSE | Calculate checksum for redo blocks when writing |
| log_small_entry_max_size | 80 | Redo entries larger than this will acquire the redo copy latch |
| log_simultaneous_copies | 2 | Number of simultaneous copies into redo buffer of copy latches |
SVRMGR> connect internal; Connected. SVRMGR> show parameters log; NAME TYPE VALUE ---------------------------------- ------- ---------- dblink_encrypt_login boolean FALSE delayed_logging_block_cleanouts boolean TRUE log_archive_buffer_size integer 127 log_archive_buffers integer 4 log_archive_dest string ?/dbs/arch log_archive_format string %t_%s.dbf log_archive_start boolean FALSE log_block_checksum boolean FALSE log_buffer integer 2097152 log_checkpoint_interval integer 10000000 log_checkpoint_timeout integer 0 log_checkpoints_to_alert boolean FALSE log_file_standby_name_convert string log_files integer 255 log_simultaneous_copies integer 12 log_small_entry_max_size integer 80 remote_login_passwordfile string NONE
Use Server Manager to Display Current Values
You can use Server Manager to display the current values for all redo log-related parameters with the "show parameters log" command. The 1) log_simultaneous_copies, and 2) log_buffer parameters can have profound impact on Oracle performance on computers with multiple CPUs (i.e. SMP or MPP), since multiple redo copy latches allow multiple processes to copy entries to the redo log buffer concurrently. The number of redo copy latches is determined by the init.ora parameter log_simultaneous_copies.
Log_simultaneous_copies is obsolete in Oracle 8.1.5 (Oracle8i). To see all of the obsolete init.ora parameters in Oracle8i, you can issue a query against the x$ksppo structure. View the Code below to see obsolete parameters.
select kspponm Name, decode( KSPPOFLG,1,'Eliminated','Underscore') Setting from x$ksppo order by Setting, name; NAME SETTING --------------------------------------------- ---------- allow_partial_sn_results Eliminated cache_size_threshold Eliminated db_block_checkpoint_batch Eliminated db_block_lru_extended_statistics Eliminated db_block_lru_statistics Eliminated db_file_simultaneous_writes Eliminated delayed_logging_block_cleanouts Eliminated job_queue_keep_connections Eliminated log_block_checksum Eliminated log_files Eliminated log_small_entry_max_size Eliminated ops_admin_group Eliminated parallel_transaction_resource_timeout Eliminated sequence_cache_entries Eliminated sequence_cache_hash_buckets Eliminated snapshot_refresh_interval Eliminated snapshot_refresh_keep_connections Eliminated snapshot_refresh_processes Eliminated sort_direct_writes Eliminated sort_read_fac Eliminated sort_spacemap_size Eliminated sort_write_buffer_size Eliminated sort_write_buffers Eliminated temporary_table_locks Eliminated arch_io_slaves Underscore b_tree_bitmap_plans Underscore backup_disk_io_slaves Underscore cleanup_rollback_entries Underscore close_cached_open_cursors Underscore compatible_no_recovery Underscore complex_view_merging Underscore discrete_transactions_enabled Underscore distributed_lock_timeout Underscore distributed_recovery_connection_hold_time Underscore fast_full_scan_enabled Underscore freeze_DB_for_fast_instance_recovery Underscore gc_latches Underscore gc_lck_procs Underscore large_pool_min_alloc Underscore lgwr_io_slaves Underscore lock_sga_areas Underscore log_archive_buffer_size Underscore log_archive_buffers Underscore log_simultaneous_copies Underscore max_transaction_branches Underscore ogms_home Underscore parallel_default_max_instances Underscore parallel_min_message_pool Underscore parallel_server_idle_time Underscore push_join_predicate Underscore row_cache_cursors Underscore shared_pool_reserved_min_alloc Underscore spin_count Underscore use_ism Underscore 54 rows selected.
Solving redo copy latch contention
If there is contention for the redo copy latch then more latches should be added by increasing the value of log_simultaneous_copies.
This value should be set to double the number of CPUs on your server. For example, if your server has 12 processors, log_simultaneous_copies should be set to 24.
This value should be set to double the number of CPUs on your server. For example, if your server has 12 processors, log_simultaneous_copies should be set to 24.
Held redo latches
The V$LATCH and V$SESSION_wait views give information about held redo log latches.
To check for redo copy latch contention, run the following script in the shown in the code below.
Do not be concerned if you do not understand the functions of the
To check for redo copy latch contention, run the following script in the shown in the code below.
select
count(*) wait_count
from
v$session_wait a,
v$latch b
where
a.wait_time = 0
and
a.event = 'latch free'
and
a.p2 = b.latch#
and
b.name like 'redo copy';
select
substr(c.name,1,10) name,
-- a.addr,
a.gets,
a.misses,
a.sleeps,
a.immediate_gets imm_gets,
a.immediate_misses imm_miss,
b.pid
from
v$latch a,
v$latchholder b,
v$latchname c
where
a.addr = b.laddr(+)
and
a.latch# = c.latch#
and
c.name like 'redo copy'
order by a.latch#;
WAIT_COUNT
----------
0
NAME GETS MISSES SLEEPS IMM_GETS IMM_MISS PID
---- ----- --------- ---------- ---------- ---------- -------
redo copy 1141 1115 1603 14146584 2702
Do not be concerned if you do not understand the functions of the
- MISSES and SLEEPS,
- IMMEDIATE_GETS and
- IMMEDIATE_MISSES
- The foremost statistic is the 'redo blocks written' value.This is most useful for finding how much redo is generated for an operation or time interval
- This indicates the time the user processes had to wait to get space in the redo file.
- This indicates the number of repeated attempts to allocate space in the redo buffer. A value indicates that the redo writer is falling behind possibly due to a log switch. Although log switch is a normal event, frequent log switches indicate improper sizing of the redo log files
Oracle Redo Log Statistics
Redo statistics
The V$SYSSTAT view contains a wealth of information about the redo log files. The most important statistics are
File I/O for the redo logs should be distributed by separating the types of files onto different disks. Redo logs should be located on disks that do not contain database files. This is because the datafile I/O is scattered, whereas redo log files are always written sequentially. In the next lesson, we will examine the redo log wait statistics.
- redo blocks written
- redo entries linearized,
- redo small copies,
- redo log space wait time and
- redo writes.
File I/O for the redo logs should be distributed by separating the types of files onto different disks. Redo logs should be located on disks that do not contain database files. This is because the datafile I/O is scattered, whereas redo log files are always written sequentially. In the next lesson, we will examine the redo log wait statistics.
Redo Log Basics - Quiz
Before moving on to the next lesson, click the Quiz link below to check your understanding redo log init.ora parameters.
Redo Log Basics - Quiz
Redo Log Basics - Quiz