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

A backup controlfile allows me to do Point-in-Time or Incomplete Recovery. This I have to do when

1. I am cloning a database from a Hot Backup and cannot afford to shutdown the source database to copy the active controlfile and online redologs
2. If I have lost ALL my files in the source database.

The backup controlfile does give me the full structure in terms of datafiles. Therefore, it allows me to apply as many archivelogs as I have available. Unfortunately, it does not include the ALTER commands to add tempfiles back. *That* is my issue.

Hemant

At 07:54 AM 17-09-03 -0800, you wrote:
>How is backup controlfile different? It doesn't contain the last SCN.
>
>--
>Mladen Gogala
>Oracle DBA
>
>
>
> > -----Original Message-----
> > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> > Behalf Of Hemant K Chitale
> > Sent: Wednesday, September 17, 2003 11:45 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: 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).
> >
>
>
>
>
>Note:
>This message is for the named person's use only. It may contain
>confidential, proprietary or legally privileged information. No
>confidentiality or privilege is waived or lost by any mistransmission. If
>you receive this message in error, please immediately delete it and all
>copies of it from your system, destroy any hard copies of it and notify
>the sender. You must not, directly or indirectly, use, disclose,
>distribute, print, or copy any part of this message if you are not the
>intended recipient. Wang Trading LLC and any of its subsidiaries each
>reserve the right to monitor all e-mail communications through its networks.
>Any views expressed in this message are those of the individual sender,
>except where the message states otherwise and the sender is authorized to
>state them to be the views of any such entity.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).