ORACLE Instance ora1 - Can not allocate log, archival required

I am getting that error quite often. I am running 8.1.7 on w2k server. Any input would be greatly appreciated.

Thanks

Could be the case that there could be a log contention. For more information, check your alert_SID.log file and post your ORA-# to get a clear picture of what had happen.

Sam

Hi Sambavan,
I am not getting any thing about this in alert log.

Thanks for your response.

*************
Mon May 21 19:06:56 2001
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.0 Service Pack 1, CPU type 586
Starting up ORACLE RDBMS Version: 8.1.7.0.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 52428800
large_pool_size = 614400
java_pool_size = 20971520
control_files = I:\oracle\oradata\ora1\control01.ctl, I:\oracle\oradata\ora1\control02.ctl, I:\oracle\oradata\ora1\control03.ctl
db_block_buffers = 17461
db_block_size = 8192
compatible = 8.1.0
log_buffer = 32768
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
db_files = 1024
db_file_multiblock_read_count= 8
max_enabled_roles = 30
remote_login_passwordfile= EXCLUSIVE
global_names = TRUE
distributed_transactions = 10
instance_name = ora1
service_names = ora1
mts_dispatchers = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
open_links = 4
sort_area_size = 65536
sort_area_retained_size = 65536
db_name = ora1
open_cursors = 300
os_authent_prefix =
job_queue_processes = 4
job_queue_interval = 60
parallel_max_servers = 5
background_dump_dest = I:\oracle\admin\ora1\bdump
user_dump_dest = I:\oracle\admin\ora1\udump
max_dump_file_size = 10240
oracle_trace_collection_name=
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
SNP0 started with pid=8
SNP1 started with pid=9
SNP2 started with pid=10
SNP3 started with pid=11
Mon May 21 19:07:00 2001
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon May 21 19:07:01 2001
alter database mount exclusive
Mon May 21 19:07:07 2001
Successful mount of redo thread 1, with mount id 907205147.
Mon May 21 19:07:07 2001
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Mon May 21 19:07:07 2001
alter database open
Beginning crash recovery of 1 threads
Mon May 21 19:07:08 2001
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1505 Reading mem 0
Mem# 0 errs 0: I:\ORACLE\ORADATA\ORA1\REDO02.LOG
Mon May 21 19:07:08 2001
Thread recovery: finish rolling forward thread 1
Thread recovery: 0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
ARCH: Beginning to archive log# 3 seq# 1503
ARCH: Completed archiving log# 3 seq# 1503
Mon May 21 19:07:09 2001
Thread 1 advanced to log sequence 1506
Thread 1 opened at log sequence 1506
Current log# 3 seq# 1506 mem# 0: I:\ORACLE\ORADATA\ORA1\REDO03.LOG
Successful open of redo thread 1.
Mon May 21 19:07:09 2001
SMON: enabling cache recovery
SMON: enabling tx recovery
Mon May 21 19:07:16 2001
ALTER SYSTEM SET resource_manager_plan='';
Mon May 21 19:07:16 2001
ALTER SYSTEM SET resource_manager_plan='SYSTEM_PLAN';
Mon May 21 19:07:42 2001
Completed: alter database open
Mon May 21 21:37:31 2001
Thread 1 cannot allocate new log, sequence 1507
All online logs needed archiving
Current log# 3 seq# 1506 mem# 0: I:\ORACLE\ORADATA\ORA1\REDO03.LOG
**********
¨X¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T ¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨ T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨[
¨U ¨U
¨U Warning - The following error occured during ORACLE redo log archival: ¨U
¨U ¨U
¨^¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T ¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨ T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨a
ORACLE Instance ora1 - Can not allocate log, archival required
¨X¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T ¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨ T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨[
¨U Press to acknowledge message. ¨U
¨^¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T ¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨ T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨T¨a

O.K This could occour in two scenarios.

1. Scenario : You have set the archive log as true but haven't set the automatic archival. as a result when the redo log gets filled, the database awaits for the archival to take place. On this case you can check whether the automatic archival is enabled by

SQL> archive log list
SQL> select * from v$instance; check the archive column

Or do a manual archive;

ALTER SYSTEM ARCHIVE LOG ALL;

2. The case that the location where this redo is being written running out of disk space and as a result it couldn't write. OR your database is performing a high dml operations as a result your archive is getting filled pretty quickly.

So the simple solution for both the case is add more redo log groups or add more redo logs to each member of the group. Enable the automatic archival process.

Good luck,
Sam

Hi sambavan,
Thank you very much. It worked. I have altered to archive log file but did not comment out automatic archive in init file.

Hi Sam,

How did you read alert file to know it was archive problem?