Home » RDBMS Server » Server Administration » Thread 1 cannot allocate new log, sequence... and Database hanged
icon5.gif  Thread 1 cannot allocate new log, sequence... and Database hanged [message #299460] Tue, 12 February 2008 02:22 Go to next message
Naif
Messages: 12
Registered: August 2007
Location: KSA
Junior Member
Dears
in our production database we got the following error

Thread 1 cannot allocate new log, sequence ....
and also Database hanged

we have done the following:

Step1
we change the destination of archive files to be in F:/OraArchive instead of G:\Archive by change LOG_ARCHIVE_DEST_1 parameter then we start database but after some time we face same issue again (Database hanged and Thread 1 cannot allocate new log, sequence )

Step2
the value of LOG_ARCHIVE_DEST was null so we suggest to use this parameter LOG_ARCHIVE_DEST instead of LOG_ARCHIVE_DEST_1 and use new destination but we face same problem (Database is hanged and Thread 1 cannot allocate new log, sequence)

Step3
then we decide to change the location of Redo logs to be in G drive instead of F because we are sure G dive not contain bad sectors after last scan disk but unfortunately still we face same problem.

Step4
we restart database and manually we switch logs and it was normal when switch from first file to then to the second then to the third but when switch back to the first file it get hang so it means oracle can not create archive log file before start writing to used redo log file ?!!

Step5
we decide to change the mode of database to be in NO archive mode and database running normally

notes the size of each redo log is 100MB and i have 3 groups and we are going to change some disks of this servers

any suggestion please why oracle can not create archive log file?.
Re: Thread 1 cannot allocate new log, sequence... and Database hanged [message #299546 is a reply to message #299460] Tue, 12 February 2008 07:01 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Z├╝rich, Switzerland
Senior Member
Hi,
Looks like the archive_dest is not writable for the DB ? It could not store the archive files after you switch the directory. So it stoppes if all 3 Logs are filled and waits until the logs are archived.

is it only a typo error or do you set the Directory wrong ?

we change the destination of archive files to be in F:/OraArchive instead of G:\Archive by change LOG_ARCHIVE_DEST_1
regards
Uwe

[Updated on: Tue, 12 February 2008 07:04]

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence... and Database hanged [message #299665 is a reply to message #299460] Tue, 12 February 2008 22:21 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Naif wrote on Tue, 12 February 2008 15:22
Dears
in our production database we got the following error

Thread 1 cannot allocate new log, sequence ....
and also Database hanged

we have done the following:

Step1
we change the destination of archive files to be in F:/OraArchive instead of G:\Archive by change LOG_ARCHIVE_DEST_1 parameter then we start database but after some time we face same issue again (Database hanged and Thread 1 cannot allocate new log, sequence )



You must to have respect for the OS's path internally, like '/' in Unix, '\' in Window to show the correct path. In your situation, you can do

SQL>alter system set log_archive_dest='g:\archive' scope=spfile; 


Quote:

Step2
the value of LOG_ARCHIVE_DEST was null so we suggest to use this parameter LOG_ARCHIVE_DEST instead of LOG_ARCHIVE_DEST_1 and use new destination but we face same problem (Database is hanged and Thread 1 cannot allocate new log, sequence)



After you change the spfile (or pfile), you must restart DB because of them are not dynamical parameter, they're static.

An example
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> alter system set log_archive_dest='G:\archive' scope=spfile;
SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> shutdown immediate
....
SQL> startup
...
SQL> archive log list
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34
SQL> show parameter log_archive
....


Quote:

Step3
then we decide to change the location of Redo logs to be in G drive instead of F because we are sure G dive not contain bad sectors after last scan disk but unfortunately still we face same problem.



There is nothing to sure F partition/disk is bad sector. What's your tool to check it? First time, you might to check validation of your REDOLOG including Group#, Memember and Members in your DB. See detail in chapter Managing Redo

Quote:

Step4
we restart database and manually we switch logs and it was normal when switch from first file to then to the second then to the third but when switch back to the first file it get hang so it means oracle can not create archive log file before start writing to used redo log file ?!!



You should not to restart DB to do switch log file only, do it only with urgent case. In this state, you should post something in nearest time in your alertSID.log here.

In many documents, many book, all of writer who wrote them, take one advice for "Avoid thread 1 can not allocate..." by increasing RedoLogFile which belonged to DB's data size...

Quote:

Step5
we decide to change the mode of database to be in NO archive mode and database running normally

notes the size of each redo log is 100MB and i have 3 groups and we are going to change some disks of this servers

any suggestion please why oracle can not create archive log file?.


Of course, if you switch your DB to NOARCHIVELOG, there are nothing to generate in G:\archive. The DB would work with only REDO physical file, DBWr/LGWr...background processes.

In your situation, I think you may do..

- Take the DB's archive in one clean Partition/HDD
- Increase your RedoLogfile to 300M per group
- Monitor your archive by RMAN utility.

The last one is the method I often do with. One backup plus archivelog are called as Obsolete whenever both of them obsolete. They are depend on your policy.
Re: Thread 1 cannot allocate new log, sequence... and Database hanged [message #300563 is a reply to message #299665] Sat, 16 February 2008 01:58 Go to previous message
Naif
Messages: 12
Registered: August 2007
Location: KSA
Junior Member
Dears

First I appreciate your support

The sizes of online redo log file around 100MB each one and I have 3 groups anyway today we try again to change the mode of database to archive log mode after we change the LOG_ARCHIVE_DEST to be in g:\Archive and log_archive_start to be TRUE then we restart database and switch the logs manually and every thing till now is OK really we are surprised !!!! what is going on in this server !!!! I will change the hared disks because I think it is related to hard disks or RAID controller (some time con not write).

thanks
Previous Topic: Dynamic service registration
Next Topic: Finding the size of database
Goto Forum:
  


Current Time: Mon Dec 05 09:16:08 CST 2016

Total time taken to generate the page: 0.14557 seconds