RMAN restore may get ORA-04030 if there are large number of records in control file (Doc ID 1388014.1)

Last updated on AUGUST 30, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.5 [Release 10.2]
Information in this document applies to any platform.

Symptoms

RMAN Restore database got ORA-04030 error


RMAN> set DBID=1368701688;
2> connect target *
3> run
4> {
5> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE'
6> parms="ENV=(NB_ORA_SERV=umcbp01, NB_ORA_CLIENT=umcu071b)";
7> restore controlfile until time='2011-11-27 23:00:00';
8> sql 'alter database mount';
9> restore database until time='2011-11-27 23:00:00';
10> recover database until time='2011-11-27 23:00:00';
11> release channel ch00;
12> }
13>

...

Starting restore at 2011-11-29 16:28:28
Starting implicit crosscheck backup at 2011-11-29 16:28:28
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/29/2011 16:35:33
ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)



Here is the failed SQL statement captured in error stack:


ksedmp: internal or fatal error
ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)
Current SQL statement for this session:
SELECT :B13 TYPE_CON, BRL.RECID KEY_CON, BRL.RECID RECID_CON, BRL.STAMP STAMP_CON, BS.SET_STAMP SETSTAMP_CON, BS.SET_COUNT SETCOUNT_CON,

----- PL/SQL Call Stack -----
object line object
handle number name
0x78bf0e04 14891 package body SYS.X$DBMS_RCVMAN
0x78bf0e04 11775 package body SYS.X$DBMS_RCVMAN
0x78859d14 1 anonymous block

...


============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT ORDER BY | | | | | |
| 2 | UNION-ALL | | | | | |
| 3 | FILTER | | | | | |
| 4 | MERGE JOIN | | | | | |
| 5 | SORT JOIN | | | | | |
| 6 | FIXED TABLE FULL | X$KCCBS | | | | |
| 7 | SORT JOIN | | | | | |
| 8 | FIXED TABLE FULL | X$KCCBL | | | | |
| 9 | FILTER | | | | | |
| 10 | FIXED TABLE FULL | X$KCCPA | | | | |
-----------------------------------------+-----------------------------------+



As per heapdump, this SQL got failed after acquiring 1.2 GB memory for sorting.


---> HEAP DUMP heap name="kxs-heap-w" desc=0xb72c0834
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
freeable 20806 1336544712 64238.43 <======
free 3 76580 25526.67
recreate 3 12432 4144.00
perm 3 7148 2382.67

BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
sort subheap 20803 1336554024 64248.14 99.99 <===================


There are many records in control file:


SQL> select count(*) from X$KCCBS;

COUNT(*)
----------
19632

SQL> select count(*) from X$KCCBL;

COUNT(*)
----------
3786240




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms