Topic: Database Administration >> Rego log file
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| |
Posted: Feb 22, 2008 12:25:02 AM |
Total Post: 43
Joined: Sep, 2007
|
Hi All
how can i monitor the filling of the redo log file? |
|
|
 Ratnaker |
| Posted: Feb 22, 2008 05:29:23 AM |  |
|
Total Post: 136
Joined: Apr, 2007
|
Connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
REDO log entries
The redo entries in the redo log files are used for database recovery. The buffer is usually flushed by reaching: one third of the redo log buffer size, frequent COMMITs, and every 3 seconds.
Notice that if you have a fast processor and a slow disk, the Server Process may fill the redo log buffer faster than the Log Writer can write the redo entries to the online Redo Log file, and you may have to increase the size of the Redo Log file to avoid such a contention.
View REDO log buffer allocation entries
Keep your eyes on the redo buffer allocation entries.
SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'redo buffer allocation entries'
/
Note that if you have a positive number, that means that you may have a problem. Be sure that you have compared the above positive number with the Redo entries and it should not be more than 1%.
REDO log buffer entries HIT Ratio
Query the redo allocation buffer entries ratio. Be sure that your ratio is not more than 1%.
SQL> SELECT a.value/b.value "redo buffer entries ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'redo buffer allocation entries'
AND b.name = 'redo entries'
/
If the number is greater than 1%, you should increase the size of the Redo Log buffer. You should also check the checkpoint and size of the online redo log file.
Waiting for REDO log buffer
Check to see if any other sessions are waiting for log buffer space.
SQL> SELECT sid, event, seconds_in_wait, state
FROM v$session_wait
WHERE event = 'log buffer space'
/
If the Log Buffer space waits exist, consider increasing the size of the redo log. Also you should check the speed of the disk that the Online Redo Log files are in.
REDO log space requests
Now, check to see if that Online Redo Log file is full and the server is waiting for the next Redo Log file.
SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests'
/
To resize the Online Log Buffer do the following:
SQL> ALTER SYSTEM SET log_buffer=###M scope=SPFILE; -- ### is a integer number that you want to assign to your log buffer.
|
|
|
|
|
| |
| Posted: Feb 22, 2008 05:39:48 AM | |
|
Total Post: 43
Joined: Sep, 2007
|
Thanks a lots .
its really very much helpful.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
