Re: hot backup archivelog the online-redo files and Oracle locks up

Chris,

Answers inline with your post.

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"obakesan" <cjundieseastwd_at_powerup.com.au> wrote in message news:5Yw78.7175$T4.68682_at_nnrp.gol.com...
> HiYa
>
>
> assuming that my database is in archivelog mode (verifyable from
v$database),
> and that I have set the archiver process on by both the command alter
system
> archive log start followed up by log_archive_start=true entry in the
> init$ORACLE_SID.ora file
>
> I thought that this was all that was needed to get the database moving
along the
> right path for everything to operate well. The arhiver process then should
bump
> out stuff from the redo-logs (as they fill) into the archive
automatically.
>

Correct, and as you are getting files in the archive log directory when you do a switch then it is definitely working.

> BTW when I do a ps -ef | grep $ORACLE_SID is one of these processes the
> archiver? hints on which one please??
>

Try "ps -ef | grep ora" this will list all the background processes (smon, pmon, lck, dbwr and lgwr).

> LOCKUPS
>
> One of the reasons why am I asking these things, is that today, when doing
a
> cold backup, the database hung on the shutdown immediate. This is strange,
and
> the only process open on it could have been either or a SQLPlus command
from my
> PC (Oracle is on a Linux machine) and perhaps a cron job that I run every
minute
> to insert 5 records into the database to have some sort of growth load on
the
> tables.
>

Are you using OEM or any other monitoring software as these can have processes that connect to the database and prevent a shutdown. What sort of shutdown did you issue, to stop for a backup I would issue either a shutdown immediate or a shutdown transactional (immediate rollsback current transactions, transactional waits for them to finish, both stop any new connections).

> The process that ran the cold backup (shell script) hung on the
commandline, and
> so did other SQL plus prompts that I ran up (from an Xterm on the host)
rather
> strange.
>
> I thought that perhaps the redo logs had filled (cant imagine why) and the
> server had come to a halt due to that. In desperation after some searches
on
> this topic, I ended killing all the processes that were that $ORACLE_SID
and
> that seemed to sort it out.
>

Did you try a shutdown abort first? If the archiver was not switching and your logs were full there should be error messages in you log file, found in your /bdump directory.

Assuming you did a shutdown immediate and could get/had a session in the database you could do a "select * from x$ktuxe where ktuxecfl = 'DEAD';" to find what processes smon was waiting to finsih rollback before shutting down.

If you continue to have shutdown problems add "alter session set events '10046 trace name context forever,level 12';" prior to your shutdown as this turns on extended SQL_TRACE for the shutdown process and should generate a trace file in your udump directory that points to your problem.

>
> I fired it up again from the commandline (sqlplus /nolog and connect / as
> sysdba) and it seems to be running fine now. I am running Oracle 8.1.6 on
> Miracle Linux 1.0 (yes, its Japanese and I'm not able to read an awful
lot)
>
>
> Back on the subject of the redo logs, these get moved to the archive area
by
> either the archiver proces when full, or when I issue an
> alter system switch logfile statement.
>
> My present understanding of these is that I need to keep all of these from
the
> time that the database is started in order to do recovery.
>

Correct.

> BUT IF I am doing a weekly shutdown, and full cold back up, it seems to me
that
> I only need to be strictly worried about the newly generated archive logs
from
> the point of startup again (assuming that after the weekly shutdown there
was no
> problem) At the weekend (sunday) shutdown, can I then flush the redo logs
to
> say off line storage and be able to start the week fresh?? Have I missed
> something here??
>

Totally correct, as long as you backup your logfiles (online not redo) as part of your cold backup!

> Finally, my filesystem, is raid 5 4 disks (striping and mirrored) so I
feel more
> comfortable that I wont loose just a disk, thus taking out my archive logs
or
> anything else (but will still copy them just in case.
>

Your bigest danger is often acidental deletion.... so backup to tape is essential.

> Ok ... this is getting long ... thanks for your time folks.
>

One more thing regarding backups... make sure anytime you add or resize a datafile that you create a backup control file and put it somewhere safe. All the archive log files in the world won't help you recover if you delete your control files and have changed your database structure! Actually there are ways but having a backup controlfile is by far the easiest.

>
>
> See Ya
>
> --
> (when bandwidth gets better ;-)
>
> Chris Eastwood
> Photographer, Programmer, Motorcyclist and dingbat
>
> please remove u n d i e s for reply
>
Received on Mon Feb 04 2002 - 13:18:22 CST