Oracle 9i and Redo log

[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' ;