| 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
Ray
A temp space does not contain any data essential to full recovery. If you issue ALTER DATABASE BACKUP CONTROLFILE TO TRACE to create a "create controlfile" script, you will see at the end of the script instructions to recreate your tempfile. On recovery the controlfile still has the entry for the tablespace, but you must recreate the tempfile. I will admit this seems a little puzzling, but I have a test database right now that has a 5-gig. tempfile (don't ask) and it is good to avoid backing this up.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.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.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |