DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORACLE Instance ora1 - Can not allocate log, archival required

  1. #1
    Join Date
    Aug 2000
    Posts
    5
    I am getting that error quite often. I am running 8.1.7 on w2k server. Any input would be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Aug 2000
    Posts
    5

    this is the alert log file

    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


  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Aug 2000
    Posts
    5

    that works

    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.

  6. #6
    Join Date
    Apr 2001
    Posts
    151
    Hi Sam,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width