 Thread 1 cannot allocate new log, sequence... and Database hanged [message #299460] |
Tue, 12 February 2008 02:22  |
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  |
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 |
trantuananh24hg
Messages: 744
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
