RE: Database accidentally left in backup mode

You are right that one day archived log will be deleted and backup actually failed.

BCV takes minutes to backup and any thing longer would spell trouble.

It's my understanding that end_hot_backup would not be called unless BCV was successful.

Otherwise, database would be left it hot backup mode and there is a cron checking this because it has happened before.

If I had a choice, then I would have integrated RMAN with split mirror backup.

But I don't and must live the cards I am dealt with.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Guillermo Alan Bort Sent: Mon 3/15/2010 5:46 PM
To: Michael Dinh
Cc: janine_at_furfly.net; Jeremiah Wilton; Oracle-L L Subject: Re: Database accidentally left in backup mode  

You know... I am amazed when I see a 9i database backed up with 'begin/end'. An 11g? I mean... come on! RMAN is very easy to set up for a disk backup. A bit more difficult if a MML is involved, but that's about it. You can get very complicated if you want to, but in general it's fairly simple. And you wouldn't have hit this little problem ;-)

in any case this line
delete noprompt archivelog until time 'SYSDATE-1/24'; is dangerous. Should the full backup take more than an hour you would lose archivelogs needed for recovery.

Other than that, you can always write a small script that runs every few minutes and checks the FRA usage, if it is above a given threshold, you can send an alert (e-mail?, alarm system?) and/or move archivelogs automatically to another drive or fire a tape archive.

hth

Alan.-

On Mon, Mar 15, 2010 at 8:28 PM, Michael Dinh <mdinh_at_xifin.com> wrote:

> This is what I ended up doing for DW using split mirror backup on EMC
> symmetrix with BCV.
>
> FRA and archive destination at separate directory.
>
> No RMAN here, but I do really like RMAN though.
>
> This is what end_hot_backup.sh script does.
>
> > cat end_hot_backup.sh
> #!/bin/sh -x
>
> sqlplus "/ as sysdba" << !
> alter database end backup;
> alter system archive log current;
> exit;
> !
> rman msglog rman.out > /dev/null << END
> connect target;
> delete noprompt archivelog until time 'SYSDATE-1/24';
> exit
> END
> echo "HBU Completed"
> exit
>
> And of course begin_hot_backup.sh
>
> > cat begin_hot_backup.sh
> #!/bin/sh
>
> sqlplus "/ as sysdba" << !
> alter system checkpoint;
> alter system archive log current;
> alter database begin backup;
> exit;
> !
> echo "HBU Completed"
> exit
> -Michael.
>
> NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
> individual or entity to which it is addressed, and may contain information
> that is privileged, confidential and exempt from disclosure under applicable
> laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH
> INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING,
> AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
> CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT
> NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS
> MESSAGE. If the reader of this email (and attachments) is not the intended
> recipient, you are hereby notified that any dissemination, distribution or
> copying of this communication is strictly prohibited. Please notify the
> sender of the error and delete the e-mail you received. Thank you.
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Janine Sisk
> *Sent:* Monday, March 15, 2010 4:11 PM
> *To:* Jeremiah Wilton
> *Cc:* Oracle-L L
> *Subject:* Re: Database accidentally left in backup mode
>
> Ok, I found the error - my flash recovery area had reached the max size
> specified in db_recovery_file_dest_size, so it's failing even though there
> is plenty of disk space. The short-term fix is to increase that value, but
> I also want to think longer term here.
>
> At this point in time I don't really need those archive log files to stick
> around very long, so I don't want to waste the disk space on them. I know
> they're needed for recovery if the system crashes, but since I'm taking a
> hot backup every night, once that is completed I don't need the old archive
> log files anymore. Since they clearly pile up fast, I need some kind of
> strategy to prune them. Unfortunately, all I can find online is "use RMAN".
> Ok, I'll get there, but it's not going to be this week. Plus that strategy
> seems to be to use RMAN to back up and then delete the files, but I don't
> need to back them up. Any better suggestions? Preferably something I can
> script?
>
> thanks,
>
> janine
>
> On Mar 15, 2010, at 2:59 PM, Jeremiah Wilton wrote:
>
> Your log_archive_dest_*n* is full, or the archiver is not working for
> some other reason. Everything will work again when you clear space for the
> archivelogs.
>
> Regards,
> Jeremiah Wilton
> Blue Gecko, Inc.
>
> On Mar 15, 2010, at 2:37 PM, Janine Sisk wrote:
>
> I've now tried ending the backup on each tablespace manually and they
> all hang, so clearly something is holding up the show. I looked at waits
> but I don't see anything directly about control files - a little Googling
> tells me that the log file switch errors probably mean my redo log is too
> small, but no indication that that would cause this problem. Am I missing
> something?
>
> SQL> select sid, event, seconds_in_wait from v$session_wait;
>
> SID EVENT
> ---------- ----------------------------------------------------------------
> SECONDS_IN_WAIT
> ---------------
> 29 log file switch (archiving needed)
> 1198
>
> 30 log file switch (archiving needed)
> 1198
>
> 33 log file switch (archiving needed)
> 153
>
> 38 log file switch (archiving needed)
> 153
>
> 42 log file switch (archiving needed)
> 1198
>
> 48 log file switch (archiving needed)
> 153
>
> 51 log file switch (archiving needed)
> 253
>
>
> ---
> Janine Sisk
> President/CEO of furfly, LLC
> 503-693-6407
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l