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

Hi Again,

Firstly you can move the log file and Oracle will just create a new one. What I do is during the nightly backups move the file to a new file with the extension of the day. ie on Monday the alert.log gets moved to alert.mon. I also delete any trace files older than a week from udump and bdump directories. Others may do things different but I have found that this keeps a weeks worth of logs and traces online and cuts the alert file into manageable chunks.

> here is a segement from the alert log BTW this thing is growin rather
> fast, presently at nearly 7000 lines and the database is not so old, I
> should research if is there a process for swapping this
>

Looking at your alert log I don't see anything obviously wrong. The "Restarting dead background process EMN0" is normal and was introduced with Oracle 8i advanced queuing. I'm not 100% but I think that Oracle starts it during a shutdown as it will be the process that executes any shutdown triggers you have. Possibly one of your trace files may give information as to why it hung but I suspect not. If there is a next time also have a look at what UNIX processes are hanging around as you may be having a dead connection problem, where processes that have not disconnected cleanly are still in the database even though the client is not connected.

> --------------
> Thread 1 advanced to log sequence 1868
> Current log# 2 seq# 1868 mem# 0:
> /u01/app/oracle/oradata/DEV/redo02.log
> Mon Feb 4 15:06:16 2002
> ARC0: Beginning to archive log# 1 seq# 1867
> ARC0: Completed archiving log# 1 seq# 1867
> Mon Feb 4 18:16:12 2002
> Restarting dead background process EMN0
> EMN0 started with pid=17
> Mon Feb 4 18:16:13 2002
> Shutting down instance (immediate)
> License high water mark = 8
> Mon Feb 4 18:21:40 2002
> Restarting dead background process EMN0
> EMN0 started with pid=10
> Mon Feb 4 18:21:41 2002
> Shutting down instance (immediate)
> License high water mark = 8
> Mon Feb 4 18:48:06 2002
> LGWR: terminating instance due to error 472
> Instance terminated by LGWR, pid = 20519
> Mon Feb 4 18:51:36 2002
> Starting ORACLE instance (normal)
> -------------------------
>
>
>
>
>
>
>
> >
> > Your bigest danger is often acidental deletion.... so backup to tape is
> > essential.
>
> noted ... I shall do all I can in that area ... but the client does
> not have or want to buy tape. Best I can hope to do, is stash them in
> other locations, and hope for the best.
>

Oh well, just make sure you warn them of the risk they are taking! Does this mean that all your backups including those of the datafiles are just going to another disk somewhere? If so there is no point in keeping any more archive log files than those from the oldest backup you keep on those disks.

>
> > 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.
>
> yes, Howard mentions this, and I have got that in the backup plan, so
> far when I issue a alter database backup controlfile to trace; command
> I just get a script which has (as he suggests) placed in the udump
> directory containing
>
> STARTUP NO MOUNT
> CREATE CONROLFILE REUSE ...
> LOGFILE
> ...
> DATAFILE
> ...
> CHARACTER SET ... ;
>
> RECOVER DATABASE
>
> ALTER SYSTEM ARCHIVELOG ALL;
> ALTER DATABASE OPEN;
>

This is what I hoped you were doing.

>
> I guess that the database knows what and where the redo logs are, but
> there is nothing here to let it know where the archived redo logs are,
> so I guess it has to get that from the initXXX.ora file
>

Yes it gets the redo log locations, along with all your datafile locations, from inside the control files. It gets the archive log, control file locations from the initXXX.ora file. Also FYI as of Oracle 8 you could have both a LOG_ARCHIVE_DEST and a LOG_ARCHIVE_DUPLEX_DEST to put your archive logs into two different locations for safety. In 8i Enterprise edition that was updated to LOG_ARCHIVE_DEST_n where n was from 0-4 allowing you to go totally dingbat and specify up to five locations. Personally I usually just stick them on mirrored or RAID disk unless the client has a real emphasis on data recovery being a priority.

>
> > 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.
>
>
> when you say backup controlfile, are you talkine about a OS based copy
> of that file. When I query v$controlfile, I get 3 files listed, so I
> guess these are just redundant copies (for safety?)
>

Yes they are and hopefully to make them really safe they are on different physical disks?

>
> my cold backup grabs these and the redo log files so hopefully I am
> covered there.
>

Good, and yes you should be covered.

>
> thanks for all your time
>

No problem!

Kind Regards

Fraser Received on Tue Feb 05 2002 - 07:21:00 CST