Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools
   
How to add and drop online redo log members and groups?




By Vigyan Kaushik
Dec 08, 2002

Digg! digg!     Print    email to friend Email to Friend

Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.




How to add and drop online redo log members and groups?

Redo log Files: The Oracle server maintains online redo log files to minimize the loss of data in the database. The redo log files record all changes made to data in the database buffer cache with some exceptions; for example, in the case of direct writes.

Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are only used for recovery.

What are online Redo Log Groups?

A set of identical copies of online redo log files is called an online redo log group.

The background process LGWR concurrently writes the same information to all online redo log files in a group.

The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database. Oracle suggests keeping three groups.

What are online Redo Log Members?

Each online redo log file in a group is called a member.

Each member in a group has identical log sequence number and the same size. The log sequence number is assigned each time the server starts writing to a log group to identify each redo log file uniquely. The current log sequence number is stored in the control file and in the header of all data files.

How to obtain Information about Groups and Members? The following query returns information about the online redo log file from the control file:

SQL> select group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS

1

215

104857600

1

INACTIVE

2

216

104857600

1

 CURRENT

3

214

104857600

1

INACTIVE

 3 rows selected.

The following query returns information about all members of a group:

SVRMGR> select * from v$logfile;

 

GROUP# STATUS TYPE MEMBER ---------- ------- ------- ---------------------------------------- 3 ONLINE /u02/ORADATA/VTEST/REDO03.LOG 2 ONLINE /u03/ORADATA/VTEST/REDO02.LOG 1 ONLINE /u03/ORADATA/VTEST/REDO01.LOG 3 rows selected.

Adding Online Redo Log Groups : In some cases you might need to create additional log file groups. For example, adding groups can solve availability problems. To create a new group of online redo log files use the following command:

ALTER DATABASE ADD LOGFILE ('/DISK1/log3a.rdo','/DISK2/log3b.rdo') size 1M;

Adding Online Redo Log Members:You can add new member to an existing redo log file group using the following command:

ALTER DATABASE ADD LOGFILE MEMBER
 /DISK2/log1b.rdo' TO GROUP 1,
'/DISK2/log2b.rdo' TO GROUP 2;

Dropping Online Redo Log Groups :To drop a group of online redo log files use the following command:

ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Online Redo Log Members: To drop a member of an online redo log group use the following command:

ALTER DATABASE DROP LOGFILE MEMBER
'/DISK2/log2b.dbf';

Please note when dropping redo log groups and redo log files, there must be at least two redo log groups and each redo log group must have at least one log member.

 

 
About author:

Vigyan Kaushik is an Oracle certified professional serving IT industry for more than 12 years as an Oracle DBA and System Administrator. He has expertise in Database Designing, Administration, Networking, Tuning, Implementation, Maintenance with web deployment activities on different Unix flavors as well as on Windows Operating Systems.

 




  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor