Forum Stats

  • 3,825,012 Users
  • 2,260,455 Discussions
  • 7,896,382 Comments

Discussions

RMAN-05122: there is insufficient disk space to perform this table recovery

user6330308
user6330308 Member Posts: 5 Blue Ribbon
edited May 22, 2020 11:25AM in Recovery Manager (RMAN)

Hello,

I'm performing RMAN recovery of some tables that we lost in a test schema. Below is the script, which has about a list of 1300 tables (but have cut short the script here, for ease of reading).

run

{

set auxiliary instance parameter file to '/u26/app/oracle/product/12.2.0/db_1/dbs/initrecover.ora';

RECOVER TABLE

O428UAT."ACTIONPERMS",

O428UAT."ACTIONPLANS",

...............................................

...............................................

until time "to_date('21-MAY-2020 05:00:00','DD-MON-YYYY HH24:MI:SS')"

AUXILIARY DESTINATION '/u06/oracle/temp/oracle/recv_schema';

}

The aux destination is 15 TB and only 4.4TB is used. The entire test DB is about 9TB.

I would think it has enough space, but the job failed multiple times.

I have 2 issues, from the RMAN log below.

Removing automatic instance

shutting down automatic instance

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 05/21/2020 16:48:00

RMAN-06136: ORACLE error from auxiliary database: ORA-01000: maximum open cursors exceeded

RMAN-06136: ORACLE error from auxiliary database: ORA-01000: maximum open cursors exceeded

RMAN-05059: Table O428UAT.CS_ENTITY_REC_TREE_GTT resides in tablespace SYSTEM

RMAN-05003: Tablespace Point-in-Time Recovery is not allowed for tablespace SYSTEM

RMAN-05122: there is insufficient disk space to perform this table recovery

1. RMAN-06136: ORACLE error from auxiliary database: ORA-01000: maximum open cursors exceeded -- I found a Document which said we can define a pfile override, which I did.

set auxiliary instance parameter file to '/u26/app/oracle/product/12.2.0/db_1/dbs/initrecover.ora';

The init file contents are as below:

*.open_cursors=1000

*.processes=1200

Do I need one cursor/process per table? Do I need to change the above to 2000 each or probably 3000?

2. RMAN-05122: there is insufficient disk space to perform this table recovery -- Why am I getting this, when I have 11 TB of space and hardly a third of it is used, but it is still failing. Is there a limit on what rman can use or a default, which I can override? I cannot find absolutely anything on this error anywhere.

s608109ch3nsint.stars-net.com:/ifs/CH3_NFS/u06

                       15T  4.4T   11T  30% /u06

Thanks,

Venkatesh

Answers

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited May 22, 2020 4:12AM

    withdrawn

    Regarding disk space, it seems your storage is via NFS, and there could be all sorts of trouble. For example your network partner may have aborted, etc. causing the error you see.

  • user6330308
    user6330308 Member Posts: 5 Blue Ribbon
    edited May 21, 2020 9:53PM

    Hi,

    Thanks for the response.

    I took the parameter from current, running Database from which we dropped the tables.

    It is actually *.open_cursors=1000. Below is from my active test DB.

    SQL> show parameter open

    NAME                                 TYPE                             VALUE

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

    open_cursors                         integer                          1000

    As far space errors, yes, it is nfs. But that is the only storage I have for this restore. What are my options? Any good way to debug or see what could be wrong and fix them? Or is my only option to use another equivalent disk group for the aux DB?

    Thanks,

    Venkatesh

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited May 22, 2020 4:13AM

    Yes, OPEN_CURSORS is correct. As far as NFS concerns, what OS are you running?

  • f10r1n-Oracle
    f10r1n-Oracle Posts: 60 Employee
    edited May 22, 2020 5:30AM

    Hi,

    When doing table recovery please note you need more space then just the table. Oracle will create a auxiliary instance with below tablespaces:

    SYSTEM, SYSAUX, UNDO

    and

    the tablespace that contains the table.


    If for example your table has 10G but the tablespace on which resides has 1TB..then Oracle will need space of 1TB plus the space for SYSTEM, SYSAUX and UNDO..

    Please check above and make sure you have appropriate space


    Regards

  • user6330308
    user6330308 Member Posts: 5 Blue Ribbon
    edited May 22, 2020 9:41AM

    Hi,

    OS is Red Hat Enterprise Linux Server release 6.10 (Santiago).

    Tablespace size.

    Total disk group size of the database is 9TB.

    Disk Group        Sector   Block   Allocation
    Name                SizeSizeUnit Size State   Type   Total Size (MB) Used Size (MB) Pct. Used

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

    EETST11DISK1         512   4,0961,048,576 CONNECTED   EXTERN   8,700,538  8,618,212 99.05
    EETST11FRA           512   4,0961,048,576 CONNECTED   EXTERN     310,733     55,239 17.78

    nfs has 11TB free.

    s608109ch3sl706:EETST111:/u06/oracle/bin/rman_temp> df -h /u06

    Filesystem            Size  Used Avail Use% Mounted on

    s608109ch3nsint.stars-net.com:/ifs/CH3_NFS/u06

                           15T  4.3T   11T  29% /u06

    I changed from my huge list of 1300+ tables to just 2. This time I get a different error, but it is still complaining about space.

    This particular datafile is for a tablespace defined "big file" and it is pretty large. We have also used section size during our regular RMAN backups and I saw a bug for recovery if that is used. Would one of these be an issue?

    RMAN-00571: ===========================================================

    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

    RMAN-00571: ===========================================================

    RMAN-03002: failure of recover command at 05/21/2020 19:52:31

    RMAN-03015: error occurred in stored script Memory Script

    ORA-01116: error in opening database file /u06/oracle/temp/oracle/recv_schema/THLG_PITR_EETST11/datafile/o1_mf_stard__512y462p_.dbf

    ORA-17501: logical block size 0 is invalid

    RMAN-05122: there is insufficient disk space to perform this table recovery

    RMAN-05122: there is insufficient disk space to perform this table recovery

    channel ORA_AUX_SBT_TAPE_8: restoring section 8 of 13

    channel ORA_AUX_SBT_TAPE_8: reading from backup piece EETST111_bk_123253_8_1040580779

    channel ORA_AUX_SBT_TAPE_1: ORA-19870: error while restoring backup piece EETST111_bk_123253_1_1040580779

    ORA-19504: failed to create file "/u06/oracle/temp/oracle/recv_schema/THLG_PITR_EETST11/datafile/o1_mf_stard__512y462p_.dbf"

    ORA-17505: ksfdrsz:1 Failed to resize file to size 802684928 blocks

    ORA-17500: ODM err:No space left on device

    Thanks,

    Venkatesh

  • f10r1n-Oracle
    f10r1n-Oracle Posts: 60 Employee
    edited May 22, 2020 10:05AM

    Hi,

    Add SYSTEM, SYSAUX and UNDO size..to those 2 tablespaces..

    What size do you get?


    Regards

  • user6330308
    user6330308 Member Posts: 5 Blue Ribbon
    edited May 22, 2020 10:27AM

    Hello,

    Sorry, I'm a bit confused by that question. I'm using this single disk group for my ENTIRE database - EETST11.

    Disk Group        Sector   Block   Allocation
    Name                SizeSizeUnit Size State   Type   Total Size (MB) Used Size (MB) Pct. Used

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

    99.05

    EETST11DISK1         512   4,0961,048,576 CONNECTED   EXTERN   8,700,538 

    8,618,212

    The tablespace in question, is in this disk group. ALL tablespaces for the entire DB is in this Disk group. It is around 8.6TB.

    My nfs file system is 15 TB, 11TB FREE. It is about 2TB more than my entire data disk group.

    I have not deleted the aux DB files under nfs after my latest failure. They are all still there. They hardly amount to 250G in the entire sub-directory of the aux database destination - /u06/oracle/temp/oracle/recv_schema.

    s608109ch3sl706:EETST111:/u06/oracle/temp/oracle/recv_schema> du -h /u06/oracle/temp/oracle/recv_schema

    4.6M    /u06/oracle/temp/oracle/recv_schema/EETST11/datafile

    2.0K    /u06/oracle/temp/oracle/recv_schema/EETST11/onlinelog

    2.0K    /u06/oracle/temp/oracle/recv_schema/EETST11/controlfile

    4.6M    /u06/oracle/temp/oracle/recv_schema/EETST11

    100K    /u06/oracle/temp/oracle/recv_schema/THLG_PITR_EETST11/datafile

    102K    /u06/oracle/temp/oracle/recv_schema/THLG_PITR_EETST11

    244G    /u06/oracle/temp/oracle/recv_schema

    But the job is failing with lack of space, even though there is 11 TB of space left.

    Thanks,

    Venkatesh

  • f10r1n-Oracle
    f10r1n-Oracle Posts: 60 Employee
    edited May 22, 2020 11:11AM

    Hi,

    What is the output of:

    col diskgroup for a10

    col diskname for a12

    col path for a20

    select a.name DiskGroup,b.name DiskName, b.total_mb, b.free_mb,b.path, b.header_status                              from v$asm_disk b, v$asm_diskgroup a where a.group_number (+)=b.group_number order by b.group_number,b.name;

    select name,total_mb,free_mb from v$asm_diskgroup;

    Regards

  • user6330308
    user6330308 Member Posts: 5 Blue Ribbon
    edited May 22, 2020 11:25AM

    Hi,

    DISKGROUP  DISKNAME       TOTAL_MB    FREE_MB PATH                 HEADER_STATUS

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

            0    0    /dev/mapper/SL707U08TMP_emc10k08f3p1    CANDIDATE

    CRSDG    CRSDG_0000    43172    14940    /dev/mapper/12g_UAT_CRS_emc10k095cp1    MEMBER

    CRSDG    CRSDG_0001    43172    14988    /dev/mapper/12g_UAT_CRS_emc10k095dp1    MEMBER

    CRSDG    CRSDG_0002    43172    14988    /dev/mapper/12g_UAT_CRS_emc10k095ep1    MEMBER

    EESTG11CSMIG    EESTG11CSMIG_0000    621464    365408    /dev/mapper/EESTG11CSMIG_emc0b02p1    MEMBER

    EESTG11CSMIG    EESTG11CSMIG_0001    621464    365416    /dev/mapper/EESTG11CSMIG_emc0b06p1    MEMBER

    EESTG11CSMIG    EESTG11CSMIG_0002    621464    365428    /dev/mapper/EESTG11CSMIG_emc08d6p1    MEMBER

    EESTG11CSMIG    EESTG11CSMIG_0003    621464    365428    /dev/mapper/EESTG11CSMIG_emc08dap1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0008    621464    11700    /dev/mapper/EESTG11DISK1_emc10k0892p1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0009    621464    11696    /dev/mapper/EESTG11DISK1_emc10k0896p1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0010    621464    11692    /dev/mapper/EESTG11DISK1_emc10k089Ap1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0011    621464    11696    /dev/mapper/EESTG11DISK1_emc10k089Ep1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0012    621464    11684    /dev/mapper/EESTG11DISK1_emc10k08A2p1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0013    621464    11692    /dev/mapper/EESTG11DISK1_emc10k08A6p1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0014    621464    11696    /dev/mapper/EESTG11DISK1_emc10k08AAp1    MEMBER

    EESTG11DISK1    EESTG11DISK1_0015    621464    11692    /dev/mapper/EESTG11DISK1_emc10k08AEp1    MEMBER

    EESTG11FRA    EESTG11FRA_0001    310732    310536    /dev/mapper/EESTG11FRA_emc10k088Ep1    MEMBER

    EETST11DISK1    EETST11DISK1_0000    621467    5877    /dev/mapper/EETST11DISK1_emc10k0a1fp1    MEMBER

    EETST11DISK1    EETST11DISK1_0002    621467    5876    /dev/mapper/EETST11DISK1_emc10k08E6p1    MEMBER

    EETST11DISK1    EETST11DISK1_0003    621467    5891    /dev/mapper/EETST11DISK1_emc10k0a13p1    MEMBER

    EETST11DISK1    EETST11DISK1_0004    621467    5879    /dev/mapper/EETST11DISK1_emc10k0a17p1    MEMBER

    EETST11DISK1    EETST11DISK1_0005    621467    5877    /dev/mapper/EETST11DISK1_emc10k0a1bp1    MEMBER

    EETST11DISK1    EETST11DISK1_0013    621467    5875    /dev/mapper/EETST11DISK1_emc10k08B2p1    MEMBER

    EETST11DISK1    EETST11DISK1_0014    621467    5879    /dev/mapper/EETST11DISK1_emc10k08B6p1    MEMBER

    EETST11DISK1    EETST11DISK1_0015    621467    5870    /dev/mapper/EETST11DISK1_emc10k08BAp1    MEMBER

    EETST11DISK1    EETST11DISK1_0016    621467    5890    /dev/mapper/EETST11DISK1_emc10k08BEp1    MEMBER

    EETST11DISK1    EETST11DISK1_0017    621467    5872    /dev/mapper/EETST11DISK1_emc10k08C2p1    MEMBER

    EETST11DISK1    EETST11DISK1_0018    621467    5877    /dev/mapper/EETST11DISK1_emc10k08C6p1    MEMBER

    EETST11DISK1    EETST11DISK1_0019    621467    5908    /dev/mapper/EETST11DISK1_emc10k08CAp1    MEMBER

    EETST11DISK1    EETST11DISK1_0020    621467    5877    /dev/mapper/EETST11DISK1_emc10k08CEp1    MEMBER

    EETST11DISK1    EETST11DISK1_0021    621467    5878    /dev/mapper/EETST11DISK1_emc10k08D2p1    MEMBER

    EETST11FRA    EETST11FRA_0001    310733    206735    /dev/mapper/EETST11FRA_emc10k0890p1    MEMBER

    NAME                             TOTAL_MB    FREE_MB

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

    CRSDG    129516    44916

    EESTG11DISK1    4971712    93548

    EESTG11FRA    310732    310536

    EETST11DISK1    8700538    82326

    EETST11FRA    310733    206735

    EESTG11CSMIG    2485856    1461680

    Thanks,

    Venkatesh