Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Concepts - Online redo log management

Oracle Tips by Burleson Consulting

Administering Oracle Online Redo Logs

In an earlier chapter we introduced you to the Oracle redo log files. If you recall, they are files that are like Oracle?s little tape recorder, and Oracle records (almost) everything that happens inside the Oracle database. Oracle uses these redo log groups to recover the database, so they are pretty important.

Each individual redo log is assigned to a group. Oracle writes to only one online redo log group at a time. Once the online redo log(s) in that group are filled then Oracle will switch to writing the next online redo log group, and so on in a circular fashion.

Each online redo log is assigned a unique sequence number. No online redo log will ever have the same sequence number in a given database unless a new incarnation of that database is created. A new incarnation of a database will occur as the result of certain incomplete recovery operations, and you will not normally need to worry about conflicts in thread numbers unless you have performed an incomplete recovery of your database. We will discuss recovery in more detail later in this book.

We can multiplex each online redo log group. This means that each redo log group can consist of more than one online redo log file. Each file is known as a member. Each member should be located on a different disk, to protect the group from losing all of its members in the event a disk failure should occur. Oracle writes to those members in parallel, to ensure that the database is always recoverable while maintaining performance.

The online redo logs are first created when the database is created, and the database cannot live without them. If all members of the active redo log group are lost, the database crashes, and worse yet, there will be data loss. Hence, it is very important to preserve these files. There are two kinds of redo logs that we will consider in this section, the online redo logs and archived redo logs.

First we will address administration of online redo logs. This includes creation and removal of an online redo log group, as well as the addition and removal of redo log group members.

Create Online Redo Log Groups

You use the alter database command to add an online redo log group, and its associated members, to the database. Here is an example of the creation of a new online redo log group:

alter database add logfile group 4 ?c:\oracle\oradata\booktst\booktst\redo04.log' size 50m;

You can also add multiple members at the same time:

alter database add logfile group 5 (?c:\oracle\oradata\booktst\booktst\redo05a.log',

 ?d:\oracle\oradata\booktst\booktst\redo05b.log') size 50m;

Drop an Online Redo Log Group

Of course, there are times that you will want to drop a given redo log group. This is commonly done if you need to increase or reduce the size of the online redo logs. You drop the online redo log group using the alter database drop logfile group command as seen in this example:

alter database drop logfile group 5;

There are a few things to note concerning the dropping of redo log groups.  For starters, you cannot drop the CURRENT redo log group; meaning, if the log group you wish to drop is currently being written to, it will not be allowed.  In this case, you can switch to the next redo log group and then drop the old one.  You can do this with the following command:

SQL> alter system switch logfile;

Another thing to note is that when you drop the redo log group from the database, it is not dropped on the file system.  You must remove it manually from the file system AFTER it has been removed gracefully from the database.

Add a Member to an Online Redo Log

Sometimes we realize that we need to add an additional member to an online redo log. Usually this is when we discover to our horror that we have only one member in each of our online redo groups (if you find that this is the case and you are not horrified, then you don?t yet understand why we multiplex online redo logs).

You use the alter database command to add a member to a redo log group as seen in this example:

alter database
add logfile member 'c:\oracle\oradata\booktst\booktst\redo03a.log' 
to group 3;

Drop a Member from an Online Redo Log

Mistakes happen, and sometimes you want to remove a member from an online redo log group. You can use the alter database command to perform this operation too:

alter database
drop logfile member 'c:\oracle\oradata\booktst\booktst\redo03a.log' ;

Again, when you drop a member of a redo log group, the file will not be dropped from the OS itself.  You must do this manually at the operating system level.

Online Redo Log Data Dictionary Views

Oracle provides data dictionary views for the online redo logs as seen in this table:

v$log - Lists information about each member of each online redo log group.

v$logfile - Provides redo log file name information

If you query v$log, you will see information about each redo log group, including the size of each member and how many members in each log group.  Most importantly, you can find out which log group is the CURRENT redo log group.  Remember the alter system switch logfile command in the case that you want to change it.

Here is a dictionary query to display the redo logs:

set lines 120;
set pages 999;
 
select substr(time,1,5) day,
to_char(sum(decode(substr(time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(time,10,2),'23',1,0)),'99') "23"
from v$log_history
group by substr(time,1,5);

 

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.