Oracle-L: Backup controlfile does not include ALTER TABLESPACE ... ADD

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 16 Sep 2003 07:25:03 -0800
Message-ID: <F001.005D00DC.20030916072503@fatcity.com>

RDBMS 8.1.7.4
Sun Solaris [although I would think that this does not matter]

What is the common method of handling the missing TEMPFILE clause when cloning a database using a Hot Backup and recreating controlfiles based on the trace from the backup controlfile ?

My description to the Oracle Support Analyst :

TEMPFILE exists in the database.
If I do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE I can see the ALTER TABLESPACE .. ADD TEMPFILE in the Trace file
However, I do an ALTER DATABASE BACKUP CONTROLFILE TO controlfilebackup.dbf
I then copy controlfilebackup.dbf [with the Hot Backup of all Database Files] to the cloning server In the Cloning Server/Database :
I copy in controlfilebackup.dbf to the expected control01.ctl location
I issue an STARTUP MOUNT to read the controlfile
I then issue ALTER DATABASE BACKUP CONTROLFILE TO TRACE Here, the ALTER TABLESPACE .. ADD TEMPFILE is missing ! It just does not appear. Therefore, if I recreate the controlfile and/or OPEN RESETLOGS the database, the TEMPFILE is missing as it does not exist on the cloned-server. However, I do not have the ALTER TABLESPACE .. ADD TEMPFILE command to add it back. I have to go back to the source database, get a TRACE backup of the controlfile to regenerate the ALTER TABLESPACE .. ADD TEMPFILE statement.

The Analyst's response :
When you create a backup of the controlfile the backup will not have information about tempfiles.

To incude temporary tablespace in backup strategy follow the note:- 167135.1( How to Incorporate Locally Managed

Temporary Tablespaces into the Backup Strategy)

Is there a TechNote or Documentation reference which specifies

why/how tempfiles are excluded from backup controlfiles ?

My current backup strategy uses DBA_DATA_FILES to identify files to copy out / backup to tape / other storage. It ignores DBA_TEMP_FILES.
However, even if I were to include DBA_TEMP_FILES or V$TEMPFILE and copy the file out, my backup controlfile would not be aware of it. Recovery would be ok if I do not have to recreate the controlfile. I normally recreate the controlfile to easily relocate the datafiles before beginning recovery. Hmm..... I guess my backup script should also list the TEMPFILES in a .list file in my backup destination and I use the .list file to identify tempfiles. Not neat.

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com

--

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

Author: Hemant K Chitale
INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Sep 16 2003 - 10:25:03 CDT