Recover a Dropped Tablespace Using TSPITR

Testato su Oracle 12c.
Cancellare un tablespace di un pluggable database e recuperarlo.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S12AVA1 READ WRITE NO
4 S12AVA1_CLONE READ WRITE NO

SQL> alter session set container=s12ava1;

Session altered.

SQL> create tablespace tbs_dc
datafile '/u01/app/oracle/oradata/CDB12S01/s12ava1/tbs_dc0101'
size 500M autoextend on next 50 M maxsize 1024M
extent management local autoallocate
blocksize 8k
segment space management auto;
/
Tablespace created.

SQL> create user bruce identified by bruce default tablespace tbs_dc account unlock temporary tablespace temp quota unlimited on tbs_dc profile default;

User created.

SQL> grant connect to bruce;

Grant succeeded.

SQL> alter session set current_schema=bruce;

Session altered.

SQL> create table heroes tablespace tbs_dc as select * from dba_objects;

Table created.

SQL> select count(*) from heroes;

COUNT(*)

----------

72783

SQL> select sequence# from v$log where status='CURRENT';

SEQUENCE#

----------

Fare un backup in locale del database più archive log.

rman target /

RMAN> backup database plus archivelog;

oppure fare un backup su dispositivo esterno.

rman target / catalog rman/rman@catdb

RMAN>

run {

allocate channel c0 type sbt PARMS="SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U' trace 2;

send channel 'c0' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c0" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

ar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/backup_L0_c0.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

allocate channel c1 type sbt PARMS="SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U' trace 2;

send channel 'c1' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c1" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

ar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/backup_L0_c1.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

allocate channel c2 type sbt PARMS="SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U' trace 2;

send channel 'c2' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c2" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/av

mar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/backup_L0_c2.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

allocate channel c3 type sbt PARMS="SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U' trace 2;

send channel 'c3' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c3" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

ar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/backup_L0_c3.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

backup filesperset = 1 incremental level = 0 database plus archivelog delete input;

}

Collegarsi al db ed effettuare log switch e drop del tablespace.

SQL> alter session set container=cdb$root;

Session altered

SQL> alter system switch logfile;

SQL> alter session set container=s12ava1;

Session altered.

SQL> drop tablespace tbs_dc including contents and datafiles;

Tablespace dropped.

SQL> select SEQUENCE# from v$log where status='CURRENT';

SEQUENCE#

----------

Collegarsi ad RMAN ed effettuare il recover del tablespace.

rman target /

RMAN> recover tablespace s12ava1:tbs_dc01 until logseq 24 auxiliary destination '/tmp/testrepo';

Oppure collegarsi al catalogo ed effettuare prima il Restore e poi il Recover.

RMAN> rman target / catalog rman/rman@catdb

run {

allocate channel c0 type sbt PARMS= "SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U';

send channel 'c0' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c0" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

mar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/restore_tbs_c0.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

allocate channel c1 type sbt PARMS= "SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U';

send channel 'c1' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c1" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

mar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/restore_tbs_c1.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

allocate channel c2 type sbt PARMS= "SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U';

send channel 'c2' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c2" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

mar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/restore_tbs_c2.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

allocate channel c3 type sbt PARMS= "SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U';

send channel 'c3' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c3" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava

mar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/restore_tbs_c3.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';

RESTORE TABLESPACE s12ava1:TBS_DC until logseq 24;

}

run {

RECOVER TABLESPACE s12ava1:TBS_DC until logseq 24 auxiliary destination '/tmp/testrepo';

}

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

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

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

RMAN-03002: failure of recover command at 12/20/2018 14:57:26

RMAN-03015: error occurred in stored script Memory Script

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 18 and starting SCN of 3957404

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 17 and starting SCN of 3952747

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 16 and starting SCN of 3952245

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 15 and starting SCN of 3952242

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 14 and starting SCN of 3952239

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 13 and starting SCN of 3952236

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 12 and starting SCN of 3952233

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 11 and starting SCN of 3952229

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 10 and starting SCN of 3952224

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 9 and starting SCN of 3952221

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 8 and starting SCN of 3952217

RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 7 and starting SCN of 3952171

Occorre restorare gli archive log e poi di nuovo il recover.