RMAN Recovery - undo / temp

LOSS OF UNDO TABLESPACE DATAFILE
Complete Recovery and Only will loose the un commited transactions. Loss of undo datafile need the database outage.

Step 1 :
I have created one temporary table for testing.
    SQL> create table dba_segments_new as select * from dba_segments;
    Table created.

Step 2 :
I have inserted the data into table and not commited. 
    SQL> insert into dba_segments_new select * from  dba_segments_new;
    5029 rows created.

    SQL> insert into dba_segments_new select * from  dba_segments_new;
    10058 rows created.

    SQL> insert into dba_segments_new select * from  dba_segments_new;
    20116 rows created.

    SQL> select count(1) from dba_segments_new;
    COUNT(1)
    ----------
        40232

    SQL> insert into dba_segments_new select * from  dba_segments_new;
    40232 rows created.

    SQL> select count(1) from dba_segments_new;
    COUNT(1)
    ----------
        80464

Step 3 :
Here we can see the undo segments active and expired info below. 
    SQL> select tablespace_name,status,sum(bytes) from dba_undo_extents group by tablespace_name,status;
    TABLESPACE_NAME 	       STATUS	 SUM(BYTES)
    ------------------------------ --------- ----------
    UNDOTBS1		       UNEXPIRED   46268416
    UNDOTBS1		       ACTIVE	    1048576

    SQL> select file_name from dba_data_files where tablespace_name='UNDOTBS1';
    FILE_NAME
    ----------------------------------------------------------------------------
    /u01/app/oracle/oradata/prod/undotbs01.dbf

Step 4:
I have deleted the undo tablespace datafile at OS level. Below are the details. 
    rm -rf undotbs01.dbf

    SQL> insert into dba_segments_new select * from  dba_segments_new;
    80464 rows created.
    SQL> select count(1) from dba_segments_new;
    COUNT(1)
        ----------
        160928

    SQL> insert into dba_segments_new select * from dba_segments_new;                                                            
    160928 rows created.
    /
    321856 rows created.
    SQL> /
    643712 rows created.
    SQL> /

    insert into dba_segments_new select * from dba_segments_new
    *
ERROR at line 1: ERROR at line 1:ORA-01116: error in opening database file 2
ORA-01110: data file 2: ‘/u01/app/oracle/oradata/prod/undotbs01.dbff’
ORA-27041: unable to open fileIBM AIX RISC System/6000
Error: 2: No such file or directoryAdditional information: 3

Step 5 :
Shut down the database using shut abort. 
    SQL> shut abort
    ORACLE instance shut down.

Step 6:
Connect to RMAN and mount the database (If we have rman backup). 
    [oracle@sdbt ~]$ export ORACLE_SID=prod
    [oracle@sdbt ~]$ sqlplus / as sysdba
    SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 16 16:14:07 2023
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    Connected to an idle instance.
    SQL> startup mount

ORACLE instance started.
Total System Global Area 532676608 bytes
Fixed Size 8622720 bytes
Variable Size 297799040 bytes
Database Buffers 218103808 bytes
Redo Buffers 8151040 bytes
Database mounted.

  RMAN> restore database;

  Starting restore at 16-DEC-23  
  using channel ORA_DISK_1 
  channel ORA_DISK_1: starting datafile backup set restore
  channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf
  channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/prod/sysaux01.dbf
  channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/prod/undotbs01.dbf
  channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/prod/users01.dbf
  channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/prod/PROD/backupset/2023_12_16/
                                                                        o1_mf_nnndf_TAG20231216T154741_lqtybfbg_.bkp
  channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/prod/PROD/backupset/2023_12_16/
                                                    o1_mf_nnndf_TAG20231216T154741_lqtybfbg_.bkp tag=TAG20231216T154741
  channel ORA_DISK_1: restored backup piece 1
  channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
  Finished restore at 16-DEC-23

  RMAN> recover database;

  Starting recover at 16-DEC-23
  using channel ORA_DISK_1
  starting media recovery
  media recovery complete, elapsed time: 00:00:32
  Finished recover at 16-DEC-23

  RMAN> restore database;

  Starting restore at 16-DEC-23
  using channel ORA_DISK_1
  channel ORA_DISK_1: starting datafile backup set restore
  channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf
  channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/prod/sysaux01.dbf
  channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/prod/undotbs01.dbf
  channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/prod/users01.dbf
  channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/prod/PROD/backupset/
                                                    2023_12_16/o1_mf_nnndf_TAG20231216T154741_lqtybfbg_.bkp
  channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/prod/PROD/backupset/2023_12_16/
                                          o1_mf_nnndf_TAG20231216T154741_lqtybfbg_.bkp tag=TAG20231216T154741
  channel ORA_DISK_1: restored backup piece 1

  channel ORA_DISK_1: restore complete, elapsed time: 00:00:46

  Finished restore at 16-DEC-23


(RMAN Recovery - Recover dropped table)