Re: Database accidentally left in backup mode
Date: Mon, 15 Mar 2010 21:46:59 -0300
Message-ID: <172762181003151746j6e12f074p8609964b678be129_at_mail.gmail.com>
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-lReceived on Mon Mar 15 2010 - 19:46:59 CDT