Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Backup controlfile does not include ALTER TABLESPACE ... ADD

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 :

  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 :
  5. I copy in controlfilebackup.dbf to the expected control01.ctl location
  6. I issue an STARTUP MOUNT to read the controlfile
  7. 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). Received on Tue Sep 16 2003 - 15:04:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US