Warm Backup/Restore of 9.2.0.5 Temporary tablespaces

From: Ray Stell <stellr_at_cns.vt.edu>
Date: Tue, 13 Apr 2004 15:55:57 -0400
Message-ID: <20040413195557.GA23261@locust.cns.vt.edu>

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.

Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Apr 13 2004 - 16:11:14 CDT