restore without catalog nor auto backup control file VTL

The story goes as follows the AWR retention at a customer was set to a mere 7 days.
way too low, to troubleshoot a problem which happened 2 weeks before ….

I proposed to duplicate the database to another db and restore it to an earlier date to extract the AWR and ASH data.

Well the CONTROL_FILE_RECORD_KEEP_TIME was set to 7 days so that didn’t really help, especially since there were no control file auto backup nor an RMAN catalog (due to a bug with transportable tablespaces that are older then the create time of your new database).

luckily we could find the backup piece name containing the control file so we could restore that from Legato VTL.

this was how we restored it and in the end we choose for a restore instead of a duplicate

run
{
allocate channel ch1 type ‘SBT_TAPE’ PARMS=”BLKSIZE=131072″;
send channel ch1 ‘NSR_ENV=(NSR_SERVER=server,NSR_CLIENT=solaris)’;
restore controlfile from  ‘solaris-z1a_LZ1_L1_ct_8cp4olkm_1_1’;
}
 

ok that was the first step the second was do the restore in ASM and skip unrelated tablespaces

run
{
  allocate  channel t1 device type ‘SBT_TAPE’
          PARMS “ENV=(NSR_SERVER=server,NSR_CLIENT=solaris)”;
set newname for datafile ‘/oracle/database/DB1/system/system_001.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/system/system_002.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/data/data_05_002.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/system/sysaux_001.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/undo/undo_003.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/undo/undo_004.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/undo/undo_002.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/undo/undo_001.dbf’ to ‘+DATA_ADEV’;
set newname for datafile ‘/oracle/database/DB1/data/users_001.dbf’ to ‘+DATA_ADEV’;
restore database
skip tablespace TOOLS,PHILIPPE_TBS;
switch datafile all;
}

run
{
allocate  channel t1 device type ‘SBT_TAPE’
          PARMS “ENV=(NSR_SERVER=server,NSR_CLIENT=solaris)”;
RECOVER DATABASE  skip tablespace TOOLS,PHILIPPE_TBS;
} 

of course the restore was not consistent and we needed to restore extra archives which were not on the server anymore but on VTL …..

we knew  the backup piece of the archive log but had to catalog it ( this is undocumented but documented in Metalink note 550082.1 )

First step  set default SBT channel :

  configure channel device type ‘SBT_TAPE’ PARMS “ENV=(NSR_SERVER=server,NSR_CLIENT=solaris)”; 

then

CATALOG  DEVICE TYPE ‘SBT_TAPE’  BACKUPPIECE solarisDB1_L0_arc_7657.1′; allocated channel: ORA_SBT_TAPE_1channel ORA_SBT_TAPE_1: SID=1 device type=SBT_TAPEchannel ORA_SBT_TAPE_1: NMDA Oracle v1.6.0cataloged backup piecebackup piece handle=server_LZ1_L0_arc_7657.1 RECID=7638 STAMP=846843644 

once the archive was know we could recover until cancel.