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

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 16 Sep 2003 12:04:40 -0800
Message-ID: <F001.005D012B.20030916120440@fatcity.com>

Hemant

I think that the Oracle philosophy is that there is no reason waste time/tape to back up a temp tablespace. To help matters, Oracle added the ALTER TABLESPACE command to the BACKUP CONTROLFILE TO TRACE. I personally disagree, because after a database recovery this is one more annoying thing to remember, and if you forget it, users often get more upset over a strange tempfile error message than they do with a database crash.

But in Oracle's business you can't please everyone, and I don't expect them to rearrange their internals to fix this obscure issue soon.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Tuesday, September 16, 2003 10:25 AM To: Multiple recipients of list ORACLE-L

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).
--

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

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

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