User Managed Recovery - temp

Temp tablespace recovery in Oracle is a process used to recover a temporary tablespace when it becomes corrupted or unavailable. Temp tablespaces are used to store temporary data for sorting and joining operations in Oracle, and they are crucial for the performance of many database operations. If a temporary tablespace becomes corrupt or unavailable, it can disrupt the functioning of your Oracle database.


STEP 1 : Find the list of temp tablespaces available 
	SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

	FILE_NAME					TABLESPACE_NAME
	------------------------------------------------------------
	/u01/app/oracle/oradata/TES/temp01.dbf  	TEMP

STEP 2 : For demo purpose, removing the available temp tablespace 
	[oracle@tesdb admin]$ cd /u01/app/oracle/oradata/TES
	[oracle@tesdb TES]$ rm -rf temp01.dbf

STEP 3 : When we try to select data we get error 
	Conn scott/tiger
	1*  select * from emp1 order by 1
	SQL> /
	 select * from emp1 order by 1
				   *
	ERROR at line 1:
	ORA-01116: error in opening database file 201
	ORA-01110: data file 201: '/u01/app/oracle/oradata/TES/temp01.dbf'
	ORA-27041: unable to open file
	Linux-x86_64 Error: 2: No such file or directory
	Additional information: 3
STEP 4 : Conn as sys user and create a temp tablespace in desired location 
	Conn / as sysdba
	Sql> Create temporary tablespace temp1 tempfile
	?/u01/app/oracle/oradata/Tes /temp1.dbf? size 100m;

	Tablespace created.

	SQL> select * from database_properties where
	property_name='DEFAULT_TEMP_TABLESPACE';



	
PROPERTY_NAMEPROPERTY_VALUEDESCRIPTION

	
---------------------------------

	
DEFAULT_TEMP_TABLESPACETEMPName of default temporary tablespace

STEP 5 : Set the created temp tablespace as default temp tablespace 
        SQL> alter database default temporary tablespace temp1;

STEP 6 : Check the default tablespace 
        SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';



	
PROPERTY_NAMEPROPERTY_VALUEDESCRIPTION

	
---------------------------------

	
DEFAULT_TEMP_TABLESPACETEMP1Name of default temporary tablespace

STEP 7 : Conn as sys and check 
        Conn scott/tiger
        1* select * from emp1 order by 1
        SQL> /



	
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO

	
---------------------
	---------------------
	--------------
	

	
7369SMITHCLERK790217-DEC-8080020 

	
7369SMITHCLERK790217-DEC-8080020 

	
7369SMITHCLERK790217-DEC-8080020 





	
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO

	
---------------------
	---------------------
	--------------
	

	
7369SMITHCLERK790217-DEC-8080020 

	
7369SMITHCLERK790217-DEC-8080020 

	
7369SMITHCLERK790217-DEC-8080020 





	
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO

	
---------------------
	---------------------
	--------------
	

	
7369SMITHCLERK790217-DEC-8080020 

	
7369SMITHCLERK790217-DEC-8080020


(User Managed Recovery - point in time to recover(PITTR))