Warm Backup/Restore of 9.2.0.5 Temporary tablespaces 2004-04-13 - By DENNIS WILLIAMS
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@(protected)
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Ray Stell
Sent: Tuesday, April 13, 2004 2:56 PM
To: oracle-l@(protected)
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 (See ORA-03217.ora-code.com))
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 (See ORA-12906.ora-code.com): 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 (See ORA-03217.ora-code.com): 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@(protected) (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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
