User Managed Recovery - tempTemp 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: 3STEP 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';
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';
STEP 7 : Conn as sys and check Conn scott/tiger 1* select * from emp1 order by 1 SQL> /
« Previous Next Topic » (User Managed Recovery - point in time to recover(PITTR)) |