0

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).

elbarna
  • 597
  • 1
  • 10
  • 26
  • Please include the exact error message in your question, and the version of Oracle you are using. – pmdba Nov 07 '22 at 01:42

1 Answers1

-1

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
elbarna
  • 597
  • 1
  • 10
  • 26