Re: Incomplete recovery question again

On Oct 25, 11:28 am, eAddict <eAdd..._at_gmail.com> wrote:
> Thanks for the reply!
>
> Q> How did you create the new controlfile?
>
> It was from the original database AFTER the files were put into backup
> with an
>
> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
>
> Q >Which version are you on?
>
> 9.2.0.4
>
> Q> I suspect you didn't put temp files into backup mode when you
> copied
> them - does v$datafile_header show them as fuzzy?
>
> Yup. Fuzzy.
> SQL> select tablespace_name, status, fuzzy from v$datafile_header
> 2 where tablespace_name='TEMP';
>
> TABLESPACE_NAME STATUS FUZ
> ------------------------------ ------- ---
> TEMP ONLINE YES
> TEMP ONLINE YES
> TEMP ONLINE YES
> TEMP ONLINE YES
> TEMP ONLINE YES
> TEMP ONLINE YES
>
> 6 rows selected.
>
> Q> I'm wondering if you have created your temp tablespace
> "correctly." See Note:160426.1
>
> You mean the original ones or the ones on the backup? The cold backup
> recovery
> works fine but then again the database is down...
>
> Q> Is anything in v$tempfile?
>
> SQL> select count(*) from v$tempfile;
>
> COUNT(*)
> ----------
> 0
>
> Q> Is there an ADD TEMPFILE when you backup controlfile to trace?
>
> No. It has the 'old' datafiles listed.
>
> So.... can I drop the temporary tablespace and recreate it? Will the
> database then come up?
>
> Again, thanks for your help.
> Vince

Depends what's in it. If there are non-temporary segments you need to get them out of there, I think, I've never done that. Read the metalink note and all the ones it references so you understand what is happening. The simple fact you have something called temp that isn't leads me to think there may be a few mines buried about, especially if someone got default and temporary confused when defining users, or more obscure things with exp/imp have happened. Or it might have been a simple goof or mindless upgrade issue. At any rate, you need to be sure the right things are getting backed up, I have a feeling you'll find the procedures assumed temporary tablespaces were temporary and not to be backed up, which is how it should be. But isn't. Is there a manually written script that generates the alter tablespace commands? Some places generate such scripts from dba_tablespaces or whereever, that might have hidden this problem until Murphy came to town.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20071025/news_1b25drone.html