Oracle 9i and Redo log

[Darren Etheridge_2]

When I place one of my Oracle DBs in ARCHIVELOG mode, after a couple of minutes of usage, the DB hangs. I've checked all of my init parameters and the arc directory is valid, no problem with space and b4 the db hangs i can manually force the system to switch redo logs. Any help would be greatly appreciated... I'm STUCK :)

Darren

[John Wimmer_1 · Accepted solution]

Do you have log_archive_start = TRUE in your init.ora or your spfile? If not, you'll need to manually archive the redo logs that have switched.

[Darren Etheridge_2]

Yes it is set to TRUE

[John Wimmer_1]

Ok, so the easy answer was not helpful. I was suspecting that it wasn't set to automatically archive, and that was what was hanging. What's in the alert log? Anything interesting there?

How about if you do an:
archive log list;
What's that say?

[Darren Etheridge_2]

Nothing really helpful in the log files.. the output of the archive log list is:


Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /aptdms/admin/archive
Oldest online log sequence 2742
Current log sequence 2744

[Florian Heigl (new acc)]

Do You have largefiles enabled on the filesystem holding the archive logs? (unless You specified a smaller size for them, then ignore the question, please :)

is there anything in the alertlog?

does oracle by itself ever switch the archivelogs or does it just hang when the first file's size limit is reached?

[TwoProc]

if you
" select * from v$database"
Does it say "ARCHIVELOG" or "NOARCHIVELOG" for the "LOG_MODE" COLUMN?

It probably says "NOARCHIVELOG" - so you need to shutdown the database, and bring it backup with "startup mount"

Then run
"alter sytem archive log all" after you've mounted it (above).

Then run
"alter database open"
(Database is now "up" )

Then run
"select * from v$database"

You should see that the "LOG_MODE" is now set to "ARCHIVELOG" mode and it should switch on its own now. You don't have to do this each time afterwards, as you're now good to go.

[Darren Etheridge_2]

The DB seems to hang after the log file is full... Largefiles are on for this mount point but the files are too small for this to be a problem :)

[Darren Etheridge_2]

John,
I've done all you mentioned above with the exception of the ALL parameter ie.
alter database archivelog

does the all parameter have any signifigance?

Thanks

[renarios]

Hi Darren,

Your database is in NOARCHIVELOG mode.
To put it in archived mode do the following:
. Enable log_archive_start = TRUE in your init.ora or your spfile
. start SQL*PLUS
connect / as sysdba
startup mount;
alter database archivelog;
alter database open;
alter system switch logfile;
alter system switch logfile;

What is the output of "Archive log list;"?

It should look like this:
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
Oldest online log sequence 332
Next log sequence to archive 335
Current log sequence 335

Cheers,

Renarios

[T G Manikandan]

I would suggest you to check the status of your log files.

Make sure the logfiles are not in stale mode.

[Yogeeraj_1]

hi,

a glance at the alert.log and the status of logfiles (select * from v$log; ) would have helped us in the diagnosis...


regards
yogeeraj

[Volker Borowski]

Hi,

reasons for archiver stuck :

1) one or more archive destination(s) out of space
check in sqlplus "show parameter archive"
Watch it, if you have configured several destinations all of them need to be checked!
2) archive destination(s) not writable (check permission or (!) filesystem quota for db-user)
3) archiver process died "ps -ef | grep arc"
for whatever reason.
4) alert.log file in background_dump_dest can not grow. This one is not well known and I never faced it myself in real life. But if you have no errors in there, it would be a good explanation. Watch for space and permissions in this case!
5) one of the controlfiles can not grow dynamicly to update the archive log history upon a logswitch. -> This one should be logged in the alert file, with message indicating write failure to controlfile.

Pretty sure you will find what is defective, if you "tail -f" the alertfile while doing the logswitches that bring the DB to stopp. If you have no messages at all, check reason 4)

Good hunting
Volker

[Wodisch]

Hi Darren,

additionally you might want to check the ownership of all your oracle files, especially the alert-log, the online-redologs, the offline-redologs (and the directory containing those), the datafiles, and the codefiles "oracle" and "tnslsnr". All should be owned by your "oracle" user and the "dba" group (well, use the names your installation is done with).

Show us the "ls -ld" output of those, please.

Maybe you are using "quotas" on those filesystems?
Or an "ulimit" is set from within your "/etc/profile" or your "oracle" user's profile scripts (.profile, .shrc, and such)?

FWIW,
Wodisch

[Darren Etheridge_2]

Well the mystery has been solved :) The main problem was in my spfile the file path had the OLD 8i parameter of log_archive_dest_1:
LOCATION='/u01/maximus/admin/archive'

and after exporting it a pfile and trying to recompile it as a spfile it balked. The correct format for 9i is:

log_archive_dest_1:
'/u01/maximus/admin/archive'

after dropping the word 'LOCATION' from the file, it compiled fine and it works :) THank you for all of your help and points will be assigned :)

Darren

[Darren Etheridge_2]

Here are my notes for the resolve of this problem for anyone that needs them :)

ORACLE 8 pfile:
log_archive_start = true
log_archive_dest_1 = "location=/u10/tech/arch"
log_archive_format = arch_%t_%s.arc

ORACLE 9i pfile:
*.log_archive_dest='/u10/tech/arch'
*.log_archive_format = 'arch_%t_%s.arc'
*.log_archive_start=true


Shutdown Immediate;
startup mount ;
alter database archivelog;
archive log start;
alter database open;


archive log list ;
alter system switch logfile ;
alter system archive log start to '/u10/tech/arch' ;