Set the Redo Log Parameters to alter database

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:

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

SQL> connect internal;
Connected.
SQL> 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

You can use SQL*Plus 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.

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.
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.

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

1. MISSES and SLEEPS,
2. IMMEDIATE_GETS and
3. IMMEDIATE_MISSES

columns. These are latch statistics that we will be covering in detail in a later module. The following diagram shows 1) Redo_blocks_written, 2) Redo_log_space_wait_time, 3) Redo_buffer_allocation_retries

select * from v$sysstart 
where name 
in (
  'redo blocks written',
  'redo buffer allocation retries',
  'redo log space wait time')
;
// -----
STATISTIC# NAME
CLASS VALUE
87 redo buffer allocation retries
2 878
92 redo blocks written
2 1064226
95 redo log space wait time
2 78925

Redo_blocks_written	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.
Redo_log_space_wait_time	This indicates the time the user processes had to wait to get space in the redo file.
Redo_buffer_allocation_retries	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.

Viewing Space Usage for Temporary Tablespaces
The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.

SELECT * from DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
----------------------------------- --------------- --------------- ----------
TEMP 250609664 250609664 249561088

The V$SYSSTAT view contains a wealth of information about the redo log files. The most important statistics are

1. redo blocks written
2. redo entries linearized,
3. redo small copies,
4. redo log space wait time and
5. 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.

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