Re: Database accidentally left in backup mode
- From: Janine Sisk <janine@xxxxxxxxxx>
- To: Jeremiah Wilton <jwilton@xxxxxxxxxxxxx>
- Date: Mon, 15 Mar 2010 14:37:09 -0700
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.
Other related posts:
