Redo logs consists of two or more pre allocated files that store all changes made to the database. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
Main points to consider before creating redo log files?
- Members of the same group should be stores in separate disk so that no single disk failure can cause LGWR and database instance to fail.
- Set the archive destination to separate disk other than redo log members to avoid contention between LGWR and Arch.
- With mirrored groups of online redo logs , all members of the same group must be the same size.
What are the parameters related to Redo log files?
Parameters related to redo log files are
- MAXLOGFILES
- MAXLOGMEMEBERS
MAXLOGFILES and MAXLOGMEMEBERS parameters are defined while creation of database. You can increase these parameters by recreating the control file.
How do you create online Redo log group?
Alter database add logfile group <group Number> (‘<DISK>:\Directory\<LOG_FILE_NAME>.log’,’ (‘<DISK>:\Directory\<LOG_FILE_NAME>.log’) size 500K;
How to check the status of added redo log group?
Select * from v$log;
Interpretation:
Here you will observe that status is UNUSED means that this redo log file is not being used by oracle as yet. ARC is the archived column in v$log , it is by default YES when you create a redo log file. It will returns to NO if the system is not in archive log mode and this file is used by oracle. Sequence# 0 also indicate that it is not being used as yet.
How to create online redo log member ?
alter database add logfile member '<DISK>:\Directory\<LOG_FILE_NAME>.log’,'<DISK>:\Directory\<LOG_FILE_NAME>.log’' to group <GROUP NUMBER>;
How to rename and relocate online redo log members ?
Important: Take the backup before renaming and relocating.
Step:1 Shutdown the database .
Step:2 Startup the database as startup mount.
Step:3 Copy the desired redo log files to new location . You can change the name of redo log file in the new location.
Step:4 Alter database rename file ‘<DISK>:\Directory\<LOG_FILE_NAME>.log’ to ‘<new path><DISK>:\Directory\<LOG_FILE_NAME>.log’,
Step:5 Alter database open;
Step: 6 Shutdown the database normal and take the backup.
How to drop online redo log group?
Important:
- You must have at- least two online groups.
- You can not drop a active online redo log group. If it active switch it by alter system switch logfile before dropping.
- Also make sure that online redo log group is archived ( if archiving is enabled).
Syntax:
If you want to drop log group:
Alter database drop logfile group <GROUP_NUMBER>;
If you want to drop a logfile member:
Alter database drop logfile member ’ <DISK>:\Directory\<LOG_FILE_NAME>.log’;
How to Viewing Online Redo Log Information?
SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 10605 1048576 1 YES ACTIVE 11515628 16-APR-00
2 1 10606 1048576 1 NO CURRENT 11517595 16-APR-00
3 1 10603 1048576 1 YES INACTIVE 11511666 16-APR-00
4 1 10604 1048576 1 YES INACTIVE 11513647 16-APR-00
SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER
------ ------- ----------------------------------
1 D:\ORANT\ORADATA\IDDB2\REDO04.LOG
2 D:\ORANT\ORADATA\IDDB2\REDO03.LOG
3 D:\ORANT\ORADATA\IDDB2\REDO02.LOG
4 D:\ORANT\ORADATA\IDDB2\REDO01.LOG
If STATUS is blank for a member, then the file is in use.
