Hello,

I need to recover dropped partition from rman backup (Oracle 10). The last level 0 backup that contains this partition was performed on 09/27/15. I'm using EMC networker for bakcup.

There was no catalog database and now backups from 09/27/15 are no longer visible in RMAN, because control_file_record_keep_time was set too low.

However some of those backups are still on tape - they are visible in mminfo results:

/usr/sbin/nsr/mminfo -s bdlbkp -o t -r "ssid,name,totalsize,savetime,volume,state" -q "client=sdbkp"

...

5342625141 RMAN:/home/scripts/sddb_backup.rcv 327 GB 09/27/15 SD72LT E

5264665370 RMAN:/home/scripts/sddb_backup.rcv 122 GB 09/27/15 SD72LT E

5315742731 RMAN:/home/scripts/sddb_backup.rcv 124 GB 09/27/15 SD72LT E

5512210329 RMAN:/home/scripts/sddb_backup.rcv 115 GB 09/27/15 SD72LT E

...

The saveset 5342625141 corresponds to backupiece containing tablespace with dropped partition (results of "list backup" recorderd at the time those backups were still visible in RMAN - now RMAN does not see them):

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

124134  Incr 0  312.59G    SBT_TAPE    01:39:23     27-SEP-15     

        BP Key: 182503   Status: AVAILABLE  Compressed: NO  Tag: TAG20150927T0

        Handle: db_SDDB_214513_948961110   Media:

  List of Datafiles in backup set 121545

  File LV Type Ckp SCN    Ckp Time  Name

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

...

When I tried to recover backup directly by SSID from tape to disk (in order to register copy on disk in RMAN catalog) I got the following error:

# /usr/sbin/nsr/recover -s bdlbkp -S 5242625441 -d /tmp

Total estimated disk space needed for recover is 327 GB.

Recovering 1 files within RMAN:\home\scripts\ into /tmp

Permission denied by server bdlbkp : Cross platform recovery not supported

Both backup server and destination machine have Solaris.

Then I tried to register backup in RMAN:

RMAN> catalog device type 'SBT_TAPE' backuppiece 'db_SDDB_214513_948961110';

I got the following error:

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=163 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: NMO v4.2.0.0

allocated channel: ORA_SBT_TAPE_2

channel ORA_SBT_TAPE_2: sid=47 devtype=SBT_TAPE

channel ORA_SBT_TAPE_2: NMO v4.2.0.0

allocated channel: ORA_SBT_TAPE_3

channel ORA_SBT_TAPE_3: sid=45 devtype=SBT_TAPE

channel ORA_SBT_TAPE_3: NMO v4.2.0.0

allocated channel: ORA_SBT_TAPE_4

channel ORA_SBT_TAPE_4: sid=52 devtype=SBT_TAPE

channel ORA_SBT_TAPE_4: NMO v4.2.0.0

allocated channel: ORA_SBT_TAPE_5

channel ORA_SBT_TAPE_5: sid=182 devtype=SBT_TAPE

channel ORA_SBT_TAPE_5: NMO v4.2.0.0

allocated channel: ORA_SBT_TAPE_6

channel ORA_SBT_TAPE_6: sid=183 devtype=SBT_TAPE

channel ORA_SBT_TAPE_6: NMO v4.2.0.0

ORA-19870: error reading backup piece db_SDDB_214513_948961110

ORA-19507: failed to retrieve sequential file, handle="db_SDDB_214513_948961110", parms=""

ORA-27029: skgfrtrv: sbtrestore returned error

ORA-19511: Error received from media manager layer, error text:

   Could not locate the LNM save file 'db_SDDB_214513_948961110' on server 'bdlbkp

'. (2:9:0)

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

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

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

RMAN-03002: failure of catalog command at 01/20/2016 13:35:06

RMAN-06209: List of failed objects

RMAN-06211: ==========================

RMAN-06212:   Object Type   Filename/Handle

RMAN-06213: --------------- ---------------------------------------------------

RMAN-06214: Backup Piece    db_SDDB_214513_948961110

Another approach I tried was restoring older controlfile, that still contained records of those backups.

When I tried to restore controlfile that still contained records of backups I got:

run {

set until time "to_date('2015-11-27 00:00:00','yyyy-mm-dd hh24:mi:ss')";

allocate channel c0_db device type sbt

parms="ENV=(NSR_SERVER=bdlbkp, NSR_CLIENT=sdbkp)";

allocate channel c1_db device type sbt

parms="ENV=(NSR_SERVER=bdlbkp, NSR_CLIENT=sdbkp)";

restore controlfile to '/tmp/cfile';

}

executing command: SET until clause

allocated channel: c0_db

channel c0_db: sid=130 devtype=SBT_TAPE

channel c0_db: NMO v4.2.0.0

allocated channel: c1_db

channel c1_db: sid=55 devtype=SBT_TAPE

channel c1_db: NMO v4.2.0.0

Starting restore at 20-JAN-16

channel c0_db: starting datafile backupset restore

channel c0_db: restoring control file

output filename=/tmp/cfile

channel c0_db: reading from backup piece c-2315614121-20151102-02

ORA-19870: error reading backup piece c-2315614121-20151102-02

ORA-19507: failed to retrieve sequential file, handle="c-2315614121-20151102-02", parms=""

ORA-27029: skgfrtrv: sbtrestore returned error

ORA-19511: Error received from media manager layer, error text:

   Could not locate the LNM save file 'c-2315614121-20151102-02' on server 'bdlbkp

'. (2:9:0)

failover to previous backup

channel c0_db: starting datafile backupset restore

channel c0_db: restoring control file

output filename=/tmp/cfile

channel c0_db: reading from backup piece c-2315614121-20151102-01

ORA-19870: error reading backup piece c-2315614121-20151102-01

ORA-19507: failed to retrieve sequential file, handle="c-2315614121-20151102-01", parms=""

ORA-27029: skgfrtrv: sbtrestore returned error

ORA-19511: Error received from media manager layer, error text:

   A backup or restore session is already in progress. (100:17:0)

failover to previous backup

... // ("a backup or restore session is already in progress" error for all previous backups)

released channel: c0_db

released channel: c1_db

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

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

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

RMAN-03002: failure of restore command at 01/20/2016 14:45:10

RMAN-06026: some targets not found - aborting restore

RMAN-06101: no channel to restore a backup or copy of the control file

Even though controlfile backup corresponding to "c-2315614121-20151102-02" is visible in mminfo results:

3463255223 RMAN:/home/scripts/sddb_backup_lvl1.rcv 20 MB 11/02/15 SD61LT

Mminfo shows however that there are no savesets on tape for 03/11/15-11/12/15, even though they are visible in RMAN "list backup". Maybe that is the reason?

Restore for controlfile from 20/12/15 works.

I would be grateful for any hints or ideas what can I do at this point.