Adding Online Redo Log File Group

Adding Online Redo Log File Groups

To create a new group of online redo log files, following SQL statement is used:
SQL> ALTER DATABASE ADD LOGFILE GROUP 3
( ‘ C:\ORACLE\ORADATA\ORCL\log3a.rdo ’ ,
‘ C:\ORACLE\ORADATA\ORCL\log3b.rdo ’ ) SIZE 1M ;
Adding Online Redo Log File Members
We can add new members to existing online redo log file group using the following commands.
SQL> ALTER DATABASE ADD LOGFILE MEMBER
‘ C:\ORACLE\ORADATA\ORCL\log1c.rdo ’ TO GROUP 1 ,
‘ C:\ORACLE\ORADATA\ORCL\log2c.rdo ’ TO GROUP 2 ,
‘ C:\ORACLE\ORADATA\ORCL\log3c.rdo ’ TO GROUP 3 ;
Adding Online Redo Log File Groups and Members
(Using Oracle Enterprise Managers)
To open Oracle Enterprise Manager
Start → Programs → Oracle-OraHome92 → Enterprise Manager Console
To add group or member
1- Navigate to Stroage
2- Click the Redo Log Groups folder
3- Select Create from right mouse menu
4- Select the General tabbed page
5- Complete the information to create the online redo log file group and members
6- Click Create
Dropping Online Redo Log File Groups
To drop group of online redo log files, following SQL statement is used:
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Online Redo Log File Members
To drop member of online redo log group, following SQL statement is used:
SQL> ALTER DATABASE DROP LOGFILE MEMBER
‘ C:\ORACLE\ORADATA\ORCL\log1c.rdo ’;
Dropping Online Redo Log File Groups and Members
(Using Oracle Enterprise Managers)
To remove group.
1- Navigate to Stroage
2- Click the Redo Log Groups folder
3- Select the online redo log file group we want to remove
4- Select Remove from right – mouse menu
5- Confirm remove.
To remove member.
1- Navigate to storage
2- Expand the redo log group folder and navigate to the group containing the member we want
to remove
3- Select the General tab page
4- Highlight the member
5- Select Remove from the right mouse menu
6- Confirm Remove.
Relocating Or Renaming Redo log files
Step-1. Shutdown the database.
SQL> SHUTDOWN
Step-2. Copy the online redolog file to new location.
Step-3. Startup database in mount mode
SQL> STARTUP MOUNT
Step-4. Rename the online redo log member using
SQL> ALTER DATABSE RENAME FILE
‘C:\ORACLE\ORADATA\ORCL\LOG2A.rdo’ TO

‘C:\ORACLE\LOG2A.rdo’;
Step-5. Open the database.
SQL> ALTER DATABSE OPEN;
Managing Redo log file with OMF
To create online redo log files to be managed by OMF, following parameters must be defined in
“init.ora” file……
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
To create a new group of online redo log file, the DBA use the the following SQL command….
SQL> ALTER DATABASE ADD LOGFILE;
A group can be added with no file specification.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
The above SQL statement drops the logfile group 3 and its operating system files associated with
OMF log file member in group 3.