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. [EMAIL PROTECTED] -----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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
