Oracle Database RMAN Backup not Restoring Tables or Users

Question

I am trying to backup an Oracle 19c Database using RMAN but I seem to be running into a few hiccups. What I did first was create test tables and users in the database before backup. Within RMAN I then ran

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

With the database backed up I then dropped the test tables and users I created and starting restoring the database

RMAN> restore controlfile from 'LOCATIN OF CONTROLFILE';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;   

When I then connected to the database, it is missing all the tables and users I deleted. I'm not sure whether if I needed to update my datafiles or there is a step that I am overlooking. Thank you

Answer 1

You have to use Set until SCn and provide a SCn before you are dropping the tables and and tablespaces. You can also create a restore point before dropping tables and use rman command to restore database to restore point 'rspname'

Answer 2

This is impossible! Firstly, rman run with "PLUS ARCHIVELOG" so your database is currently in archivelog mode. Secondly, you restore controlfile & database then perform "COMPLETE" recovery so at the time of complete - your test tables and users must be there.

Answer 3

If you would have followed the steps them you must see the tables. To avoid confusion use set until time or SCN or Time

SET UNTIL SCN <scn after object creation>;
SET UNTIL SEQUENCE <seq no after table creation>;
set until time <time after table creation>;

Also pls verify did you connect to right database

Here is the example

RMAN> restore controlfile from 'LOCATIN OF CONTROLFILE';
RMAN> ALTER DATABASE MOUNT;
RMAN>RUN
 {allocate channel c1 device type disk; 
allocate channel c2 device type disk; 
 set until time '2020-03-19:15:30:00';
  restore database;
  recover database;
ALTER DATABASE OPEN RESETLOGS;   
  }