How to recover with RMAN at a given point in time?

Question

I perform backups with RMAN on an Oracle 12c database.
I'm trying to restore a table deleted on 11/17/2023 at 09:44 but I get an error message when I apply alter database open resetlogs; command

Below is the procedure I used :

$ rman target=/ 
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> run {
set until time "to_date('17/11/2023 09:40:00', 'DD/MM/YYYY HH24:MI:SS')";
restore database;
recover database;
}

Open the database and reset logs

RMAN> alter database open resetlogs; 

The Oracle error messages are as follows:

RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/17/2023 09:55:53
ORA-03113: end-of-file on communication channel
Process ID: 116719
Session ID: 1339 Serial number: 7632
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/17/2023 09:55:53
ORA-03113: end-of-file on communication channel
Process ID: 116719
Session ID: 1339 Serial number: 7632

Does anyone have an idea ?

Thanks !

Answer 1

Assuming backups (controlfile & datafile) exist before dropping table action. Then:

• First: Restoring controlfile
• Second: Restoring Database using Point in time recovery Then open database Resetlogs