Re: Database accidentally left in backup mode

From: Janine Sisk <janine_at_furfly.net>
Date: Mon, 15 Mar 2010 14:37:09 -0700
Message-Id: <305EA6A8-1459-439E-B42B-6FCB09A137B6_at_furfly.net>

On Mar 15, 2010, at 1:42 PM, Jeremiah Wilton wrote:

> 
> On Mar 15, 2010, at 1:36 PM, Janine Sisk wrote:
>> 
>> Yes, that would at least give me some kind of progress indication.  Though I kind of hate to kill it now, it has been going for so long already!   But all files are still marked active so whatever it's doing doesn't seem to be very effective.
> 
> The database doesn't do anything of any duration during this process, except update the file headers and the controlfile.  It should be almost instantaneous for even large numbers of datafiles.  Duration of backup mode is irrelevant. You can (and should) cancel and retry at any time.  If it hangs again, check the wait event of the session trying to take it out of backup mode.  You are probably stuck on a controlfile enqueue.

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

---

	 2 pmon timer
	      3

	 3 VKTM Logical Idle Wait
	1181751

	 4 rdbms ipc message
	      1


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	 5 DIAG idle wait
	      1

	 6 rdbms ipc message
	      1

	 7 rdbms ipc message
	      1


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	 8 DIAG idle wait
	      1

	 9 rdbms ipc message
	      1

	10 rdbms ipc message
	      2


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	11 rdbms ipc message
	      0

	12 rdbms ipc message
	      0

	13 buffer busy waits
	 324593


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	14 rdbms ipc message
	    884

	15 library cache pin
	    147

	16 rdbms ipc message
	      1


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	18 rdbms ipc message
	      6

	20 rdbms ipc message
	      6

	21 rdbms ipc message
	      7


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	22 rdbms ipc message
	     15

	25 Streams AQ: qmn coordinator idle wait
	     13

	28 log file switch (archiving needed)
	   1198


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	29 log file switch (archiving needed)
	   1198

	30 log file switch (archiving needed)
	   1198

	31 SQL*Net message from client
	1181721


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	33 log file switch (archiving needed)
	    153

	34 rdbms ipc message
	      3

	38 log file switch (archiving needed)
	    153


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	40 Streams AQ: qmn slave idle wait
	     13

	42 log file switch (archiving needed)
	   1198

	44 Space Manager: slave idle wait
	      1


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	47 buffer busy waits
	   8017

	48 log file switch (archiving needed)
	    153

	51 log file switch (archiving needed)
	    253


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	52 buffer busy waits
	 140574

	56 log file switch (archiving needed)
	   1198

	57 log file switch (archiving needed)
	   1292


       SID EVENT


---------- ----------------------------------------------------------------

SECONDS_IN_WAIT

---

	58 log file switch (archiving needed)
	   1198

	61 SQL*Net message to client
	      0

38 rows selected.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 15 2010 - 16:37:09 CDT