«

May 20

RMAN table recovery error

I had a user delete the data in a table that they didn’t mean to. It took them a week to notify us, so no chance to use flashback to get the data back. I was not looking forward to restoring 100TB of datafiles and recovery logs just to restart a couple GBs of data. Thankfully the database is running 19c, and rman can now do a table recovery.

I tried to recover the table

run {
  set auxiliary instance parameter file to '/opt/tempam/pfile.ora';
  recover table amyers.mytable until time 'trunc(sysdate-12)'
  auxiliary destination '/opt/tempam'
  remap table 'amyers'.'mytable':'mytable_recovered';
}

I had 20TB of space provisioned to /opt/tempam. To do table recovery, the system, sysaux, undo and the source table tablespaces are restored. in my case this should come to about 3TB.

After waiting an hour for everything to restore and roll forward, I get the following error in the final step where it should be exporting the table with datapump

RMAN-06960: EXPDP> ORA-31626: job does not exist
ORA-31637: cannot create job TSPITR_EXP_enpf_BDAD for user SYS
ORA-39062: error creating master process DM00
ORA-31613: Master process DM00 failed during startup.
RMAN-05122: There is insufficient disk space 951901 MB to perform table recovery of 1921807 MB.
RMAN-05122: There is insufficient disk space 951901 MB to perform table recovery of 1058623 MB.

This is a lie, there was plenty of space.

Turns out this is a known bug, Bug 9109785 : ORA-1555 TRANSPORT TABLESPACE FAILS WITH ORA-01555 DURING THE EXPDP PHASE. 

The work around is to prevent the database from being dropped after the failure by adding the “keep auxiliary” statement to the restore, and manually performing the datapump export.

run{  
  set auxiliary instance parameter file to '/opt/tempam/pfile.ora';
  recover table amyers.mytable until time 'trunc(sysdate-12)'
  auxiliary destination '/opt/tempam' keep auxiliary
  remap table 'amyers'.'mytable':'mytable_recovered';
}

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>