RMAN: a fast question about single table recover-CodePudding

I want to get a single table accidentally dropped from db. Db is a pluggable db I have tried this but fail

rman target=/
recover table myuser.persons2 OF PLUGGABLE DATABASE pdb2
  until scn 37128783
  auxiliary destination '/tmp/aux'
  datapump destination '/var/oracle/data/export'
  dump file 'saved_copy.dmp'
  notableimport;

The command fail because the command search for undo logs in SYSTEM tablespace, but my table was in another tablespace called "users_pers", is possible to specify the tablespace or there is another way? The error message is similar to:

table myuser.persons2 don'exist or not found, actually I don't have the console error (it was a test db for training).

CodePudding user response：

Solution found, this is the correct procedure. Is essential to have a full backup and recover correctly, in case of multiple backups you must specify TAG when recovery.

a)we must know the correct date, personally I give the time before delete the table

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
select sysdate from dual;

b)on rman the backup was executed with this command

BACKUP DATABASE TAG "backup-oracledb" PLUS ARCHIVELOG;

c) AFTER the drop table, I execute those commands

mkdir /var/oracle/backup/aux'

rman target=/

d)on rman I recover the lost table in datapump file(of course the datapump dir must exist, configured in ORACLE, etc..)

recover table "MYUSER"."MYTABLE" OF PLUGGABLE DATABASE nameofpluggabledb
until time "to_date('07/11/2022 16:34:10','dd/mm/yyyy hh24:mi:ss')"
auxiliary destination '/var/oracle/backup/aux'
datapump destination '/var/oracle/backup/datapump'
dump file 'saved_copy.dmp'
notableimport;

If you want to use SCN

recover table "MYUSER"."MYTABLE" OF PLUGGABLE DATABASE nameofpluggabledb
UNTIL SCN 37128783
auxiliary destination '/var/oracle/backup/aux'
datapump destination '/var/oracle/backup/datapump'
dump file 'saved_copy.dmp'
notableimport;

e)finally exit from rman (if no error reported) and import the table

impdp youruser/yourpassword@yourhost/nameofpluggabledb full=Y directory=DUMP_DIR dumpfile=saved_copy.dmp