Tuesday, July 04, 2006
Redo Log Files
| Online Redo log file GROUPS: | |
| ADDING | ALTER DATABASE ADD LOGFILE GROUP 3 ('ORA_DIR\log3a.rdo', 'ORA_DIR\log3B.rdo') SIZE 10M; |
| DROPPING | ALTER DATABASE DROP LOGFILE GROUP 3; |
| Online Redo log file group Members: | |
| ADDING | ALTER DATABASE ADD LOGFILE MEMBER 'ORA_DIR\log1d.rdo' TO GROUP 1, 'ORA_DIR\log2d.rdo' TO GROUP 2, 'ORA_DIR\log3d.rdo' TO GROUP 3; |
| DROPPING | ALTER DATABASE DROP LOGFILE MEMBER 'ORA_DIR\log3d.rdo'; |
| RENAMING / RELOCATING Redo log files | ALTER DATABASE CLEAR LOGFILE 'new_ilename'; ALTER DATABASE RENAME FILE 'filename' TO 'new_filename; |
| Views: | V$LOG , V$LOGFILE |
| Parameter | MAXLOGFILES, MAXLOGMEMBERS, DB_CREATE_ONLINE_LOG_DEST_n, |
Structure of Redo log files:
Redo log groups : consists of a set of identical copies of online redo log files. LGWR concurrently writes the same information to all the online redo log files in a group.
Multiple copies should be maintained to avoid losing database due to a single point of failure. Oracle needs minimum two online redo log groups for normal operation of a database.
Log sequence number: Each member in a group has identical log sequence number and are of the same size. This log sequence is assigned each time Oracle server writes to a log group, to uniquely identify each redo log file. This sequence number is stored in the control file & in the header of all datafiles.
Redo log specification during DATABASE CREATE :
-
MAXLOGFILES
Absolute maximum of online redo log file groups.
MAXLOGMEMBERS
Maximum number of members per group.
Functioning of Redo log: Redo log files record all the changes made to the data & provide a recovery mechanism. Redo log files are only used for recovery. These are used in a situation such as instance failure to recover committed data that has not been written to the datafiles. Oracle server sequentially writes all changes made to the database to Redo log buffers.
These include:
transactions that have not yet been committed,
undo segment information, and
schema & object management statements.
LGWR writes these buffers to Redo log files sequentially under the following circumstances:
when a transaction commits
when Redo log buffer become 1/3rd full
when more tha 1Mb of changed record in the Redo log buffer
Before DBWn writes modified blocks in the Database Buffer cache to the datafiles.
Log Switch : Redo log work in a cyclic fashion. When a redo log file is full, the LGWR moves on to the next log goup. This is the log switch. A checkpoint occurs and information is written to control files.
A log swich can also be manually forced by: ALTER SYSTEM SWITCH LOGFILE;
Checkpoint: During a checkpoint DBWn writes dirty databse buffers, that are covered by the logs that is being checkpointed, to the datafiles.
The checkpoint background process CKPT updates the control files to reflect that successful completion of a checkpoint. Checkpoints caused by a log-switch causes CKPT to update datafile headers also.
Checkpoints can occur for all datafiles in the database or only for specific datafiles. For example a checkpoint occurs in following situations:
SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE ]
FAST_START_MTTR_TARGET = 600
Default value is zero.This initialization parameter implies instance recovery shouldnot take more than 600 seconds, thus forcing checkpoints to meet this goal. (it replaces FAST_START_IO_TARGET & LOG_CHECKPOINT_TIMEOUT). It determines the number of dirty buffers that DBWn needs to write during a checkpoint.
ALTER SYSTEM CHECKPOINT; <----- Forcing checkpoint manually.
ALTER TABLESPACE [ OFFLINE NORMAL | READ ONLY | BEGIN BACKUP ]
Causes checkpointing on specific datafiles.
Adding Online Redo log file groups:
ALTER DATABASE [database] ADD LOGFILE
[GROUP group_integer] filespec
[, GROUP group_integer] filespec]...
Note : If GROUP clause is ommitted Oracle Server will automatically generate the value.
Adding Online Redo Log file members:
ALTER DATABASE ADD LOGFILE MEMBER
[ 'filename' [REUSE] TO { GROUP group_integer
[, 'filename [REUSE]]....
| ('filename' [, 'filename']...)
}
]....
Note : The file name must be fully specified otherwise it will be created in the database server default directory.
If the files already exists then it must be of the same size and must specify REUSE option.
Target group may be specified by either of these:
one or more members of the group.
Group no.
OEM : Database->Storage -> Redo Log Groups [right click] -> create -> input info under General Tab -> create
Dropping Online Redo log file groups:
ALTER DATABASE [database]
DROP LOGFILE {GROUP integer | ('filename' [,'filename']...)}
[,{GROUP integer|('filename'[,filename']...)}]...
Restrictions:
An instance requires at 2 online redo log file groups.
Active or current group can't be dropped.
Operating system files are not deleted.
Dropping Online Redo Log file members:
ALTER DATABASE [database]
DROP LOGFILE MEMBER
'filename' [,'filename']...
Restrictions:
Can't drop the last valid member of a group.
If the group is current, the member can't be dropped. A log-switch must be forced.
If database is running on ARCHIVELOG mode, and the concerned log group has not been archieved, then the member can't be dropped.
Operating system file is not dropped unless u r using OMF.
OEM : Database->Storage ->Redo Log Groups-> select redo log group->[General Tab] -> select member->[right click] remove->confirm
Relocating/Renaming Online Redo Log files:
Steps to take to rename/relocate online redo log files:
Copy the log file to new destination using Operating system commands
ALTER DATABASE CLEAR LOGFILE 'new_filename'; <-- clears the new file for use.
ALTER DATABASE [database] RENAME FILE 'old_filename' [,'filename']... TO 'new_filename' [,'filename']...
Note: Before renaming ensure that the new redo log files exists. Oracle server only changes the pointers in the control file.
OEM : Database->Storage ->Redo Log Groups-> [select the redo log group]->modify filename/file Directory or relocate members.
CONFIGURING ONLINE REDO LOG FILE:
Number of redo log groups needed depends on the database requirement. Just two groups can suffice in some systems. But if LGWR frequently has to wait ( reflected in LGWR trace file or alert files) because a checkpoint has not completed or a group has not been archieved then more groups may be needed.
When multiplexing relo log files, keep members of a group in separate disks. This way even if one member becomes unavailable, the instance won't shut down.
Although multiplexed groups can contain different number of members, they should ideally be symmetric. Asymmetric configuration should only be due to unusual situation such as a disk failure.
Keep archive log files & online redo log files on separate disks to reduce disk contention between LGWR & ARCn background processes.
Similiarly keep database file & redo log files on separate disks to reduce disk contention between LGWR & DBWn processes.
Minimum size of redo log file is 50K. Members of different groups can have different sizes, however there is no benefit of different sized groups. This may be needed when you wish to change the size of the online redo log groups, creating new ones with new size and removing the old ones.
Configuration of redo log files are effected by:
no. of log switches & checkpoints
number & amount of redo entries
amount of space on the storage medium
OMF managed ONLINE REDO LOG FILE:
Parameters: DB_CREATE_ONLINE_LOG_DEST_n
where n the multiplexed copy number.
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
implies 2 members per group is created. Default size of members is 100MB. Unique filenames for redo log files are Oracle generated (eg. ora_1_wo94n2xi.log) and is displayed in alertSID.log.
in the above case two groups with two members each will be created.
Groups can be added without any file specifications:
ALTER DATABASE ADD LOGFILE;
adds a new log file group with 2 members located in the direcectories specified by parameters. DB_CREATE_ONLINE_LOG_DEST_n.
Dropping a Group:
ALTER DATABASE DROP LOGFILE GROUP 3;
Archieved Redo Log Files: Can't be OMF.
ARCHIVED REDO LOG FILES:
Advantages of running database in ARCHIVELOG mode and archiving redo log files:
Recovery: a database backup with online & archived redo log files can guarantee recovery of all commited transactions.
Backup: this can be done while the database is open.
By default the database is created in NOARCHIVELOG mode. The redo log files are overwritten each time an online redo log file is filled & log switch occurs. The overwriting occurs only after the checkpoint for that group is completed.
In ARCHIVEMODE inactive groups of filled online redo log files must be archived. Since all the changes to database are recorded in online redo log files, so the DBA can use physical backup and the archived redo log files to recover database without losing any commited data.
Online redo log files can be archived either manually or automaticallydepending on the initialization parameter LOG_ARCHIVE_START :
| TRUE | Indicates archiving is automatic. ARCn initiates archiving of the filled log groups at eveyr log switch. |
| FALSE | Default value. Disables. DBA must manually execute a command to back up redo log files each time. All or specific redo log files can be archived manually. |
When successfully archived ( manually or automatically by ARCn)
an entry is made in the control file
Records : archive log name, log sequence number, and high & low SCN
filled redo log can't be reused until a checkpoint has taken place and the file is archived by ARCn.
Can be multiplexed.
Maintained by DBA
OEM : Database->Instance -> Configuration-> Recovery Tab->mode,filename format, destination directory
Views
V$LOG, V$LOGFILE
SQL>desc v$log;
Name Null? Type
----------------------- -------- --------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
SQL> SELECT group#, sequence#, bytes, members, status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 0 104857600 1 UNUSED
2 17 104857600 1 CURRENT
3 15 104857600 1 INACTIVE
Status | Description |
| UNUSED | indicates that the online redo log group has never been written to. This is the state when an online redo log file is just added. |
| CURRENT | the current & active online redo log file group. |
| ACTIVE | implies active but not current. It is required for crash recovery. It may be in use for block recovery. It may or maynot be archived. |
| CLEARING | Indicates that the log is being recreated as an empty log after an ALTER DATABASE CLEAR LOGFILE command. Once log is cleared, the status changes to UNUSED. |
| CLEARING_CURRENT | Indicated that the current log is being cleared of a closed thread. The log can stay in this status for long if there is some failure in the switch such as I/O error writing the new log header. |
| INACTIVE | Indicates it is no longer needed for instance recovery. It may or maynot be archived. |
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- --------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
SQL> select group#, status, type, substr(member,1,50) from v$logfile;
GROUP# STATUS TYPE SUBSTR(MEMBER,1,50)
---------- ------- ------- ----------------------------------
3 STALE ONLINE D:\ORACLE\ORADATA\CHIK\REDO03.LOG
2 ONLINE D:\ORACLE\ORADATA\CHIK\REDO02.LOG
1 ONLINE D:\ORACLE\ORADATA\CHIK\REDO01.LOG
| Status | Description |
| INVALID | indicates the file is inaccessable. |
| STALE | Indicates the contents of the file are incomplete. |
| DELETED | The file is no longer used. |
| Blank | implies file is in use. |
