RE: Backup controlfile does not include ALTER TABLESPACE ...

Dennis,
Yes. Sometimes, Oracle doesn't want to do some additional work. Although I can't understand how the backup controlfile is different from the active controlfile
in that manner.

The last Support Analyst's response was
"... the backup binary control file does not store information about the temporary files and this is a expected

   behavior."

My retort before I closed the TAR [maybe I should be filing an Enhancement Request]
was
"I would expect the backup controlfile to have the same information

   as the original control file. If a trace from the active controlfile    does provide the ALTER TABLESPACE .. ADD TEMPFILE .. I don't see    why the backup controlfile cannot provide the same."

Hemant

At 12:04 PM 16-09-03 -0800, you wrote:
>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 :
> 1. TEMPFILE exists in the database.
> 2. If I do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE
> I can see the ALTER TABLESPACE .. ADD TEMPFILE in the Trace file
> 3. However, I do an ALTER DATABASE BACKUP CONTROLFILE TO
>controlfilebackup.dbf
> 4. I then copy controlfilebackup.dbf [with the Hot Backup of all
>Database Files] to the cloning server
> In the Cloning Server/Database :
> 1. I copy in controlfilebackup.dbf to the expected control01.ctl location
> 2. I issue an STARTUP MOUNT to read the controlfile
> 3. 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
>---------------------------------------------------------------------
>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).

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