Embedded Files
Check
Check
Thread#: Can be any number (usually 1) as long as all logs for the instance have the same number. In a RAC environment the thread number must be unique for each instance across the cluster.
Group#: Number identifying a log and any multiplexed copies. i.e. all logs in group 1 will be multiplexed copies of each other. Each group needs to be identified by a different number.
Status: UNUSEDACTIVEUNASSIGNED
Type:ONLINESTANDBY
Resize
Resize
If your logfiles are too small (or too large), use the following process to resize them...
Run /u01/dba/oraredologs.sql to identify existing log files and repeat the steps below until all logs are the new size...
ALTER DATABASE DROP LOGFILE 'member';
If you encounter ORA-01623 (log 999 is current log for thread 999 - cannot drop) then perform a log switch...
ALTER SYSTEM SWITCH LOGFILE;
Add a new logfile with the new size and the same name as the logfile you just dropped...
ALTER DATABASE ADD LOGFILE 'member' SIZE 5M;
Review the values for the initialization parameters LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT.
Initialization Parameters
Initialization Parameters
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_TIMEOUT
To switch logfile if log has not been switched for one hour...
ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT=3600 SCOPE=BOTH;
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_INTERVAL
TODO
Tuning Tips
Tuning Tips
If the average wait time is low but the number of waits is high... consider whether reducing the number of COMMITs is possible (e.g. by batching COMMITs).
Are you AUDITing SELECT statements? i.e. are the waits due to COMMITs to the AUDIT$ table.
Check SEQ# in V$SESSION_WAIT. If it's not incrementing every second then the local session has a problem, else the blocking process is LGWR.
Tips to reduce I/O waits...
Use dedicated disks for redo logs.
Alternate redo logs on different disks (and size appropriately) to minimise the impact of the ARCH process on the LGWR process.
Move the redo logs to faster disks. (e.g. RAID 5 to RAID 1 or RAID 10, or HDD to SSD or Flash drives).
Use ASM, or Raw devices.
Consider LOLOGGING / UNRECOVERABLE operations (but be aware of the risks this entails).
Consider COMMIT NOWAIT (but be aware of the risks this entails).
Check the size of the log buffer, perhaps it is too large.
The operations that contribute to the log file sync wait are:
Waking up LGWR if idle
LGWR gathering the redo to be written and issuing the I/O
LGWR waiting for the I/O to complete
LGWR I/O post-processing
LGWR telling the foreground/user session that the write is complete
Waking the Foreground/User session if idle
Steps 2 and 3 are accumulated in the "redo write time" statisticStep 3 is the "log file parallel write" wait eventStep 5 and 6 may become significant as system load increases
In a Data Guard environment with SYNC transport, step 3 also includes the network write time and the write time to the Standby Redo Logs.
Bibliography
Bibliography
https://docs.oracle.com/cd/B19306_01/server.102/b14231/onlineredo.htm
Log File Sync Waithttps://deepakoradba.wordpress.com/2015/03/22/log-file-sync-wait-eventhttps://support.oracle.com 34583.1:log file parallel writehttps://support.oracle.com 387174.1:MAA – Data Guard Redo Transport and Network Best Practices
Log File Sync Waithttps://deepakoradba.wordpress.com/2015/03/22/log-file-sync-wait-eventhttps://support.oracle.com 34583.1:log file parallel writehttps://support.oracle.com 387174.1:MAA – Data Guard Redo Transport and Network Best Practices
Page updated
Google Sites
Report abuse