RMAN Recovery - undo / tempComplete 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 « Previous Next Topic » (RMAN Recovery - Recover dropped table) |