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