RE: Database accidentally left in backup mode

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Mon, 15 Mar 2010 16:28:15 -0700
Message-ID: <>

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 script does.  

> cat

#!/bin/sh -x  

sqlplus "/ as sysdba" << !
alter database end backup;
alter system archive log current;
rman msglog rman.out > /dev/null << END
  connect target;
  delete noprompt archivelog until time 'SYSDATE-1/24';   exit
echo "HBU Completed"

And of course  

> cat


sqlplus "/ as sysdba" << !
alter system checkpoint;
alter system archive log current;
alter database begin backup;
echo "HBU Completed"


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.  

[] 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?



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.

	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
		29 log file switch (archiving needed)

		30 log file switch (archiving needed)

		33 log file switch (archiving needed)

		38 log file switch (archiving needed)

		42 log file switch (archiving needed)

		48 log file switch (archiving needed)

		51 log file switch (archiving needed)

Janine Sisk
President/CEO of furfly, LLC

Received on Mon Mar 15 2010 - 18:28:15 CDT

Original text of this message