Tablespace e Table Point in Time Recovery

 Creaimo una directory ausiliare per effettuare il "poit in time recovery".

[oracle@primary ~]$ mkdir /home/oracle/tspitr

Collegarsi al pdb che contiene una tabella che vogliamo cancellare e poi recuperare.

[oracle@primary ~]$ sqlplus bat/man@pdb1

SQL> select count(*) from archam;

  COUNT(*)

----------

      1025

SQL> select current_scn from v$database;

CURRENT_SCN

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

    2863093                          ------->  valore a cui volgiamo effettuare il restore.

SQL> conn bat/man@pdb1

SQL> delete from archam;

1025 rows deleted.

SQL> commit; 

Collegarsi al catalogo con RMAN ed effettuare il restore della tabella until time o scn.

[oracle@primary ~]$ rman target / catalog rcatown/rcatown@rcatpdb

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 22 15:32:36 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1590558615)

connected to recovery catalog database

RMAN> RECOVER TABLE BAT.ARCHAM OF PLUGGABLE DATABASE pdb1 

UNTIL SCN 2863093 

AUXILIARY DESTINATION '/home/oracle/tspitr' 

REMAP TABLE 'BAT'.'ARCHAM':'ARCHAM_OLD';

Verifica che la tabella nuova contiene le stesse righe cancellate.

[oracle@primary tspitr]$ sqlplus bat/man@pdb1

SQL> select count(*) from archam;

  COUNT(*)

----------

0

SQL> select count(*) from archam_old;

  COUNT(*)

----------

      1025

Ora proviamo a cancellare un datafile.

SQL> select df.file#, df.name from v$datafile df, v$tablespace ts where df.ts#=ts.ts# and ts.name='TBS_BAT' ;

     FILE#                                                  NAME

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

23                                /u01/app/oracle/oradata/orcl/pdb1/tbs_bat01,dbf

24                                /u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df

Facciamo una copia immagine del datafile 24

[oracle@primary ~]$ rman target / catalog rcatown/rcatown@rcatpdb

RMAN> backup as copy datafile 24 format '/home/oracle/backup/tbs_bat02.df';

Supponiamo che il datafile 24 sia stato cancellato.

[oracle@primary backup]$ rm  /u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df

Per recuperarlo mettere il datafile offline.

SQL> conn sys/system@pdb1 as sysdba

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> alter database datafile 24 offline;

Database altered.

RMAN> recover datafile 24;

Starting recover at 22-MAR-21
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/22/2021 16:07:04
RMAN-06094: datafile 24 must be restored

Occorre prima fare lo switch alla copia del datafile.

RMAN> switch datafile 24 to copy;

datafile 24 switched to datafile copy "/home/oracle/backup/tbs_bat02.df"
starting full resync of recovery catalog
full resync complete

RMAN> recover datafile 24;

Starting recover at 22-MAR-21

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 22-MAR-21

Verifica del datafile ripristinato.

SQL> select df.file#, df.name from v$datafile df, v$tablespace ts where df.ts#=ts.ts# and ts.name='TBS_BAT' ;

     FILE#                                                        NAME

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

23                /u01/app/oracle/oradata/orcl/pdb1/tbs_bat01,dbf

24                /home/oracle/backup/tbs_bat02.df

Ora proviamo a droppare un tablespace.

Validiamo il restore del tablspace TBS_BAT

RMAN> RESTORE TABLESPACE pdb1:TBS_BAT PREVIEW;

SQL> drop tablespace TBS_BAT including contents and datafiles;

RMAN> RECOVER TABLESPACE pdb1: TBS_BAT 

UNTIL SCN    2863093

AUXILIARY DESTINATION '/home/oracle/tspitr';

SQL> alter tablespace TBS_BAT online;

Verifica:

SQL> conn bat/man@pdb1

SQL> select count(*) from archam;

  COUNT(*)

----------

      1025

SQL> select count(*) from archam_old;

select count(*) from archam_old

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

Attenzione che il datafile 24 è sempre ripristinato sotto la directory di backup.