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.
|
1 |
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.
|
1 |
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.
|
1 2 3 4 5 |
$ 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
E:>dir /s redo*.log E sürücüsü birimi: Data Birim Seri Numarası: 6CC3-6A8A E:oracleproduct10.2.0oradatamydb 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.
