RAC Redo Log Thread Maintenance

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.

The redo log files specified in the create DATABASE command become thread 1. Additional threads are created using the ALTER DATABASE ADD LOGFILE command.

For example, to create a thread while creating the database (a manual process):

CREATE DATABASE 'RACDB'
  MAXLOGFILES 300 MAXLOGMEMBERS 6
  MAXDATAFILES 512 MAXINSTANCES 24
  MAXLOGHISTORY 1024
  DATAFILE '/u01/oracle/data/sys_racdb1' size 1024M
  LOGFILE
  GROUP 1
('u01/oracle/redo/file1a',
             'u02/oracle/redo/file1b') size 50M,
  GROUP 2
('u01/oracle/redo/file2a',
             'u02/oracle/redo/file2b') size 50M,
  GROUP 3
('u01/oracle/redo/file3a',
             'u02/oracle/redo/file3b') size 50M ;

To add an extra redo log thread:

ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 4 ('u01/oracle/redo/file4a',
         'u02/oracle/redo/file4a') size 50M,
 GROUP 5 ('u01/oracle/redo/file5a',
         'u02/oracle/redo/file5a') size 50M,
 GROUP 6 ('u01/oracle/redo/file6a',
         'u02/oracle/redo/file6a') size 50M  ;

A thread needs to be enabled before an instance can use it. While enabling, it can be designated as a public or a private thread. For example, to enable as a public thread:

ALTER DATABASE ENABLE PUBLIC THREAD 2 ;

To enable as a private thread:

ALTER DATABASE ENABLE PRIVATE THREAD 3 ;

When a thread is enabled as private, the thread parameter in the SPFILE (or init.ora) can be used to associate the instance with a thread. For example, instances racdb1 and racdb2 will have thread 1 and 2 respectively.

RACDB1.THREAD=1  # RACDB1 instance uses Thread 1
RACDB2.THREAD=2  # RACDB2 instance uses Thread 2

Next, here are some examples of how to drop a group or member.

For dropping an online redo log group, use the ALTER DATABASE statement with the DROP LOGFILE clause. The following statement drops redo log group number 3:

ALTER DATABASE DROP LOGFILE GROUP 3;

To drop specific inactive online redo log members, use the ALTER DATABASE statement with the DROP LOGFILE MEMBER clause. The following statement drops the redo log /u01/oracle/redo/log3c:

ALTER DATABASE DROP LOGFILE
MEMBER '/u01/oracle/redo/log3c';

Table 8.8 shows the views that deal with online redo information.

Table 8.8: Dynamic performance views related to Redo-Log

Segment Space Management

Each object in the database that occupies space is associated with a segment. A segment is a set of extents that contains all the data for a specific logical storage within a tablespace or multiple tablespaces. An extent is a storage unit composed of contiguous blocks. The free and used space in a segment can be managed in two ways:

* Manual

* Automatic

When a locally managed tablespace is created using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows the DBA to specify how free and used space within a segment is to be managed.

* The Manual Method involves managing the free space using the free lists of the data blocks. This method depends on the storage settings like pctused, freelists, and freelist groups for schema objects. Manual method is the default parameter when creating a tablespace.

* In the Automatic Method, Oracle uses bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more space becomes available or some space gets reduced in a data block, the new amount of free space is reflected in the bitmap. Automatic segment-space management is specified at the tablespace level. The Oracle database server automatically and efficiently manages free and used space within objects created in such tablespaces.