topic Re: Oracle 9i and Redo log in Operating System - HP-UX

Oracle 9i and Redo log

Darren Etheridge_2 — Thu, 28 Apr 2005 14:09:51 GMT

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

Re: Oracle 9i and Redo log

John Wimmer_1 — Thu, 28 Apr 2005 14:34:30 GMT

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.

Re: Oracle 9i and Redo log

Darren Etheridge_2 — Thu, 28 Apr 2005 14:38:17 GMT

Yes it is set to TRUE

Re: Oracle 9i and Redo log

John Wimmer_1 — Thu, 28 Apr 2005 15:06:13 GMT

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?

Re: Oracle 9i and Redo log

Darren Etheridge_2 — Thu, 28 Apr 2005 15:40:08 GMT

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

Re: Oracle 9i and Redo log

Florian Heigl (new acc) — Thu, 28 Apr 2005 17:12:48 GMT

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?

Re: Oracle 9i and Redo log

TwoProc — Thu, 28 Apr 2005 17:28:40 GMT

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.

Re: Oracle 9i and Redo log

Darren Etheridge_2 — Thu, 28 Apr 2005 18:45:10 GMT

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 :)

Re: Oracle 9i and Redo log

Darren Etheridge_2 — Thu, 28 Apr 2005 18:50:54 GMT

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

Re: Oracle 9i and Redo log

renarios — Fri, 29 Apr 2005 01:41:52 GMT

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

Re: Oracle 9i and Redo log

T G Manikandan — Fri, 29 Apr 2005 01:45:22 GMT

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

Make sure the logfiles are not in stale mode.

Re: Oracle 9i and Redo log

Yogeeraj_1 — Fri, 29 Apr 2005 04:56:58 GMT

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

Re: Oracle 9i and Redo log

Volker Borowski — Sun, 01 May 2005 12:03:40 GMT

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

Re: Oracle 9i and Redo log

Wodisch — Sun, 01 May 2005 14:29:25 GMT

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

Re: Oracle 9i and Redo log

Darren Etheridge_2 — Mon, 02 May 2005 11:41:52 GMT

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

Re: Oracle 9i and Redo log

Darren Etheridge_2 — Mon, 02 May 2005 11:44:02 GMT

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