OraclePortal.org Everything you need about Oracle

Using  SQL Interface: There are 2 main dynamic performance views to see your database's current redo log structure.

1. v$logfile => Shows redo log groups, group members and member status.

sql> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
1   /oradata/mydb/redo_log_files/redo01.log
2   /oradata/mydb/redo_log_files/redo02.log
3
  /oradata/mydb/redo_log_files/redo03.log

Here, this database has 3 redo log groups with 3 members. You can see the path of redo log files and see which group they belong to. If status is null then it means that the file is in use.

2. v$log => This view provides information about your redo log groups. This information is fetched from control file.

sql> select group#,archived,members,status from v$log ;

 

GROUP# ARCHIVED MEMBERS STATUS
1 YES 1 INACTIVE
2 YES 1 INACTIVE
3 NO
1
CURRENT

Here this database has 3 groups, which are identified by numbers 1,2,3. At the moment group 1 is the current redo log group. Redo records are written to this group. Group 2 and 3 are inactive and they have been archived. After redo log files in group 1 are filled then redo log records will be written to group 2 then group 3 respectively.

If status of a redo log file is "ACTIVE "it means that this file was recently used then log writer process switched to another redo log group. But still there are some redo records in active group, which has not been applied to datafiles yet. If you see a group with a status "ACTIVE" it should return to status "INACTIVE" soon without manual intervention.

3. There is one more view V$LOG_HISTORY where you can see information about your redo log history. But the information you can extract from this view is not as user-friendly as the two views above. You won't be dealing with V$LOG_HISTORY view in your routine tasks so we won't mention it in this article.

Using Operating System

On Linux:

If the database was setup according to Oracle Flexible Architecture ( more information on OFA on Linux) online redo log files will be in redolog<counter>.log or redo<counter>.log format where counter shows the order of online redo log files. So performing a search at operating system level will give you the list of online redo log files.

The command below will start searching from root location (/) and will look for any file, which starts with "redo" and has an ".log" extension.

$ find / -name redolog*.log -type f

/u01/oracle/product/10.2.0/oradata/my_db/redolog01.log
/u02/oracle/product/10.2.0/oradata/my_db/redolog02.log
/u03/oracle/product/10.2.0/oradata/my_db/redolog03.log

The files found are "probably" the online redo log files for the instance but it is also possible that they are not associated with an instance. They might me obsolete. Because of that it is more reliable to locate redo log files via sql interface than operating system interface.

On Windows:

If the database was setup according to Oracle Flexible Architecture ( more information on OFA on Windows ) online redo log files will be in redolog.log or redo.log format where shows the order of online redo log files. So performing a search at operating system level will give you the list of online redo log files.

The command below will  start searching from root location of drive E and will look for any file, which starts with "redo" and has an ".log" extension.

E:\>dir /s redo*.log
 E sürücüsü birimi: Data
 Birim Seri Numarası: 6CC3-6A8A

 E:\oracle\product\10.2.0\oradata\mydb dizini

14.12.2009  09:19        52.429.312 REDO01.LOG
11.12.2009  18:10        52.429.312 REDO02.LOG
11.12.2009  18:10        52.429.312 REDO03.LOG
               3 Dosya      157.287.936 bayt

     Listelenen Toplam Dosya:
               3 Dosya      157.287.936 bayt
               0 Dizin   157.829.578.752 bayt boş

The files found are "probably" the online redo log files for the database but they might not be associated with a database. They might me obsolete. Because of  that it is more reliable to locate online redo log files via sql interface than operating system interface.

Did this article help? Our free information service offers more.

Print
Posted by at 08:09
Tags :
redolog , v$log , v$log_history , v$logfile

0 Comments:

Do you want to say something?
* Your email address won't be published.

Knowledge Base