Alertlog里的异常:
Beginning log switch checkpoint up toRBA [0x14ce5.2.10], SCN: 10167846714202
Thread 1 advanced to log sequence 85221 (LGWR switch)
Current log# 2 seq# 85221 mem# 0:/oracle_ebs/prod/db/apps_st/data/log02a.dbf
Current log# 2 seq# 85221 mem# 1:/oracle_ebs/prod/db/apps_st/data/log02b.dbf
Fri Apr 19 08:45:41 2013
******************************************************************
LGWR: Setting 'active' archival for destinationLOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 85221 fordestination LOG_ARCHIVE_DEST_2
Fri Apr 19 08:45:46 2013
Completed checkpoint up to RBA [0x14ce5.2.10], SCN:10167846714202
Fri Apr 19 08:47:53 2013
ORACLE Instance PROD - Can not allocate log, archivalrequired
Thread 1 cannot allocate new log, sequence 85222
All online logs needed archiving
Current log# 2 seq# 85221 mem# 0:/oracle_ebs/prod/db/apps_st/data/log02a.dbf
Current log# 2 seq# 85221 mem# 1:/oracle_ebs/prod/db/apps_st/data/log02b.dbf
Fri Apr 19 08:56:15 2013
归档日志目录：
-rw-r----- 1oraprod dba 524M Apr 19 08:23 1_85217_724772258.dbf
-rw-r----- 1oraprod dba 967M Apr 19 08:33 1_85218_724772258.dbf
-rw-r----- 1oraprod dba 967M Apr 19 08:56 1_85219_724772258.dbf
-rw-r----- 1oraprod dba 972M Apr 19 08:56 1_85220_724772258.dbf
-rw-r----- 1oraprod dba 970M Apr 19 08:561_85221_724772258.dbf
Can Not AllocateLog [ID 1265962.1] 转到底部
--------------------------------------------------------------------------------
修改时间:2013-3-2类型:HOWTO状态:PUBLISHED优先级:3 注释(0)
In this Document
Goal
Fix
1) Enlarge the existing REDO log files
2) Add REDO log groups
3) Tune checkpoints
4) Increase I/O speed for writing online REDOlog/Archived REDO
References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3[Release 10.2 to 11.2]
Information in this document applies to any platform.
Goal
PROBLEM DESCRIPTION
Error messages in alert log:
Thread cannot allocate new log,sequence
Checkpoint not complete
OR / AND
ORACLE Instance - Can not allocatelog, archival required
Thread cannot allocate new log, sequence
From the AWR we can see what issue ismore frequent:
a) DBWR has not finishedcheckpointing (log file switch (checkpoint incomplete) )
b) ARCH has not finished copying the redo log file to the archivedestination (Archival required)
These error messages are addressed ina similar way.
Fix
1) Enlarge the existing REDO logfiles
This applies to both issues as itwill prevent log switch and it may gain time for the ARCH toarchive the online redo logs until they need to be reused.
A good way to determine the optimal size of redo log files is tolook into the alert.log file and see how frequently the logsequence# are changing. Each log switch is recorded in thealert.log.
The alert log should be examined whenthere are heavier loads in the database.
Redo log switching should occur at most once every 20 minutes tohave a better database performance. If they switch too frequently,you will need to check the current size of redo log files fromV$LOG and you will need to create redo log groups with bigger size.
You should start with a minimum of100 MB redo log files for any production database.
See these note for detailed howto:
Note 602066.1 HowTo Resize and/or Add Redo Logs
Note 1038851.6 How to Estimate Size of Redo Logs
You may use the V$INSTANCE_RECOVERYview, column OPTIMAL_LOGFILE_SIZE, to determine the size of youronline redo logs.
Reference:
Oracle® Database Performance Tuning Guide
Configuring a Database for Performance
Sizing Redo Log Files
10.2 - http://docs.oracle.com/cd/B19306_01/server.102/b14211/build_db.htm#i19558
11.1 - http://docs.oracle.com/cd/B28359_01/server.111/b28274/build_db.htm#i19558
11.2 - http://docs.oracle.com/cd/E11882_01/server.112/e16638/build_db.htm#PFGRF94147
2) Add REDO log groups
ORACLE Instance - Can not allocate log, archival required
Thread cannot allocate new log, sequence
Having more groups allows ARCH toarchive them until they need to be reused.
3) Tune checkpoints
This applies to Thread cannot allocate new log, sequence
Checkpoint not complete
For details see: Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
4) Increase I/O speed for writingonline REDO log/Archived REDO
This applies to Thread cannot allocate new log, sequence
Checkpoint not complete
- use ASYNC I/O if not already so
- use DBWR I/O slaves or multiple DBWR processes
Reference:
Oracle Database Performance TuningGuide
Instance Tuning Using Performance Views
Consider Multiple Database Writer (DBWR) Processes or I/OSlaves
10.2 - http://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm#i42802
11.1 - http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm#i42802
11.2 - http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94511
- consider the genericrecommendations for REDO log files:
If the high I/O files are redo logfiles, then consider splitting the redo log files from the otherfiles. Possible configurations can include the following:
1. Placing all redo logs on one diskwithout any other files. Also consider availability; members of thesame group should be on different physical disks and controllersfor recoverability purposes.
2. Placing each redo log group on a separate disk that does notstore any other files.
3. Striping the redo log files across several disks, using anoperating system striping tool. (Manual striping is not possible inthis situation.)
4. Avoiding the use of RAID 5 for redo logs.
Reference:
Oracle Database Performance Tuning Guide
Redo Log Files
10.2 - http://docs.oracle.com/cd/B19306_01/server.102/b14211/iodesign.htm#sthref534
11.1 - http://docs.oracle.com/cd/B28359_01/server.111/b28274/iodesign.htm#CHDBCDHG
11.2 - http://docs.oracle.com/cd/E11882_01/server.112/e16638/iodesign.htm#PFGRF94396
For
ORACLE Instance - Can not allocatelog, archival required
Thread cannot allocate new log, sequence
In the above document you may check section "Archived RedoLogs".
Note:
If the REDO rate seems abnormal tryto identify which queries are generating too much redo:
Note 300395.1 Using LogMiner, How to determine the cause of lots ofredo generation
Check if any activity can safely bedone with NOLOGGING / UNRECOVERABLE options and consider the factsfrom:
Note 290161.1The Gains and Pains ofNologging Operations
References
NOTE:1038851.6 - How to Estimate Size of Redo Logs
NOTE:147468.1 - Checkpoint Tuning and Troubleshooting Guide
NOTE:270954.1 - Significance of LOG_ARCHIVE_MAX_PROCESS in Oracle8i
NOTE:290161.1 - The Gains and Pains of Nologging Operations
NOTE:300395.1 - How To Determine The Cause Of Lots Of RedoGeneration Using LogMiner
NOTE:602066.1 - How To Maintain and/or Add Redo Logs
@NOTE:749056.1 - Alert Log Parser Tool
NOTE:832504.1 - Excessive Archives / Redo Logs Generation due toAWR / ASH - Troubleshooting
