| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Warm Backup/Restore of 9.2.0.5 Temporary tablespaces
Alter tablespace temp add tempfile "/db05/oradata/dbx/temp01.dbf"
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ray Stell
Sent: Tuesday, April 13, 2004 2:56 PM
To: oracle-l_at_freelists.org
Subject: Warm Backup/Restore of 9.2.0.5 Temporary tablespaces
These notes indicate that you do not backup temp ts:
Note:167056.1 ("Alter Tablespace Begin Backup" on a Temporary Tablespace
Fails with ORA-03217)
Note:167135.1 (How to Incorporate Locally Managed Temporary Tablespaces
into the Backup Strategy)
So, I drop and recreate the temp ts on a test restore machine.
How is the default temp ts supposed to be recovered? I can drop and recreate other temp ts, but not the default defined at db creation time:
SQL> drop tablespace TEMP including contents;
drop tablespace TEMP including contents
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
and I can't back it up with the warm backup commands, per the notes above:
SQL> alter tablespace temp begin backup;
alter tablespace temp begin backup
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
How should this ts be handled to complete a recovery from a warm backup? The db restores, but does not seem all together correct:
on production I have this ts and datafile, the default temp ts:
SQL>select TABLESPACE_NAME, FILE_NAME, bytes from DBA_TEMP_FILES;
TABLESPACE_NAME FILE_NAME BYTES
-------------------- ---------------------------------------- ----------
TEMP /db05/oradata/dbx/temp01.dbf 41943040
but on the recovery db I have no datafile included in the recovery:
SQL> select TABLESPACE_NAME, FILE_NAME, bytes from DBA_TEMP_FILES where TABLESPACE_NAME = 'TEMP'; no rows selected
even though this tablespace does exist:
SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces where TABLESPACE_NAME like '%TEM%';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEMP ONLINE
I moved the file over, but it seems I need some magic to make the ts usable. Is it just me, or has Oracle Corp really made a mess here. They don't seem to document a good recovery process.
 |
 |