er, 'interesting' new 9i feature

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 05:16:33



Have you noticed that backup controlfile to trace now outputs a tracefile
containing the 'create controlfile' stuff *twice*? Once with the word
'noresetlogs' attached. And once with the word 'resetlogs'. No other
differences detectable.

This is happening in 9i R2, and it occurs to me that I don't think it
happened in 9i R1... but was wondering if anyone could check for me?

Quite what was wrong with editing out the two letters "no", like we used to
in earlier versions, I have no idea. I can already smell the confusion users
are going to get themselves into using the new, improved version.

Regards
HJR

 
 
 

er, 'interesting' new 9i feature

Post by Niall Litchfiel » Fri, 19 Jul 2002 05:31:46




Quote:> Have you noticed that backup controlfile to trace now outputs a tracefile
> containing the 'create controlfile' stuff *twice*? Once with the word
> 'noresetlogs' attached. And once with the word 'resetlogs'. No other
> differences detectable.

> This is happening in 9i R2, and it occurs to me that I don't think it
> happened in 9i R1... but was wondering if anyone could check for me?

Doesn't happen in 9.0.1 on this machine. just the noresetlogs version.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

 
 
 

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 05:36:45


Thanks Niall. I *thought* I hadn't seen it before.

I guess my next question for someone with 9iR2 on Unix is 'is this just a
Windows feature, or does it happen on Unix too'. I'd expect it to be the
same on all platforms, but maybe Oracle's just worked out that their Windows
Users need more help than the others!

Regards
HJR




> > Have you noticed that backup controlfile to trace now outputs a
tracefile
> > containing the 'create controlfile' stuff *twice*? Once with the word
> > 'noresetlogs' attached. And once with the word 'resetlogs'. No other
> > differences detectable.

> > This is happening in 9i R2, and it occurs to me that I don't think it
> > happened in 9i R1... but was wondering if anyone could check for me?

> Doesn't happen in 9.0.1 on this machine. just the noresetlogs version.

> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************

 
 
 

er, 'interesting' new 9i feature

Post by Sean » Fri, 19 Jul 2002 05:48:46



> Have you noticed that backup controlfile to trace now outputs a tracefile
> containing the 'create controlfile' stuff *twice*? Once with the word
> 'noresetlogs' attached. And once with the word 'resetlogs'. No other
> differences detectable.

There are 2 other differences in the two statements: 1) the alter
database open command differs by the word resetlogs (not just the create
controlfile statement) and 2) the recover database command does not
contain the "using backup controlfile" clause for the noresetlogs case.
So I guess I can understand why they allow for both scenarios since the
does differ a bit more than just the "no".  I don't feel really strongly
either way actually.

Quote:> This is happening in 9i R2, and it occurs to me that I don't think it
> happened in 9i R1... but was wondering if anyone could check for me?

Yes, you're correct, this is a new R2 feature - didn't happen like this
in R1.

Quote:> Quite what was wrong with editing out the two letters "no", like we used to
> in earlier versions, I have no idea. I can already smell the confusion users
> are going to get themselves into using the new, improved version.

Yeah, might prove a bit difficult to explain to newbies, but I think I
understand the intent, if not the execution, of the change.  Seems like
they could just force you to add the word 'resetlogs' or 'noresetlogs'
when you do your 'alter database backup controlfile to trace' command
instead of letting it default.  That way you'd know what kind of script
you're generating.  Instead they give you both.  Eh.  I suppose as long
as I don't have to teach people about it, I'm OK with it.  (Sorry
Howard.)  :)

Now, what I really don't understand is that they only managed to change
the comment character in certain places within the script, but not all.
There's still a bunch of #'s instead of -- 's.  #'s are great if you're
still using svrmgrl and 8.0.5, but this is 9i folks!  SQLPLUS likes --
's.  They only seemed to fix this in a few places, not the whole .trc
file.  OK, minor nitpick, but still... if you're going to fix it, then
why not fix it everywhere!

Thanks for lettting me rant,
Sean M

 
 
 

er, 'interesting' new 9i feature

Post by Sean » Fri, 19 Jul 2002 05:50:02



> Thanks Niall. I *thought* I hadn't seen it before.

> I guess my next question for someone with 9iR2 on Unix is 'is this just a
> Windows feature, or does it happen on Unix too'. I'd expect it to be the
> same on all platforms, but maybe Oracle's just worked out that their Windows
> Users need more help than the others!

Happens in HP/UX as well.

Regards,
Sean M

 
 
 

er, 'interesting' new 9i feature

Post by Sean » Fri, 19 Jul 2002 05:57:29


Sorry, one more thing.  Just checked the 9i R2 SQL Reference manual, and
sure enough, this is expected new functionality.  Check out the last
sentence in particular:

-----------------------------------------------------------------------------
TO TRACE

Specify TO TRACE if you want Oracle to write SQL statements to a trace
file rather than making a physical backup of the control file. You can
use SQL statements written to the trace file to start up the database,
re-create the control file, and recover and open the database
appropriately, based on the created control file.

You can copy the statements from the trace file into a script file, edit
the statements as necessary, and use the script if all copies of the
control file are lost (or to change the size of the control file).

     Specify AS filename if you want Oracle to place the script into a
file called filename rather than into the standard trace file.
     Specify REUSE to allow Oracle to overwrite any existing file called
filename.
     RESETLOGS indicates that the SQL statement written to the trace
file for starting the database is ALTER DATABASE OPEN RESETLOGS. This
setting is valid only if the online logs are unavailable.
     NORESETLOGS indicates that the SQL statement written to the trace
file for starting the database is ALTER DATABASE OPEN NORESETLOGS. This
setting is valid only if all the online logs are available.

If you cannot predict the future state of the online logs, specify
neither RESETLOGS nor NORESETLOGS. In this case, Oracle puts both
versions of the script into the trace file, and you can choose which
version is appropriate when the script becomes necessary.

-----------------------------------------------------------------------------

So, that makes sense, I guess.

Regards,
Sean



> > Have you noticed that backup controlfile to trace now outputs a tracefile
> > containing the 'create controlfile' stuff *twice*? Once with the word
> > 'noresetlogs' attached. And once with the word 'resetlogs'. No other
> > differences detectable.

> There are 2 other differences in the two statements: 1) the alter
> database open command differs by the word resetlogs (not just the create
> controlfile statement) and 2) the recover database command does not
> contain the "using backup controlfile" clause for the noresetlogs case.
> So I guess I can understand why they allow for both scenarios since the
> does differ a bit more than just the "no".  I don't feel really strongly
> either way actually.

> > This is happening in 9i R2, and it occurs to me that I don't think it
> > happened in 9i R1... but was wondering if anyone could check for me?

> Yes, you're correct, this is a new R2 feature - didn't happen like this
> in R1.

> > Quite what was wrong with editing out the two letters "no", like we used to
> > in earlier versions, I have no idea. I can already smell the confusion users
> > are going to get themselves into using the new, improved version.

> Yeah, might prove a bit difficult to explain to newbies, but I think I
> understand the intent, if not the execution, of the change.  Seems like
> they could just force you to add the word 'resetlogs' or 'noresetlogs'
> when you do your 'alter database backup controlfile to trace' command
> instead of letting it default.  That way you'd know what kind of script
> you're generating.  Instead they give you both.  Eh.  I suppose as long
> as I don't have to teach people about it, I'm OK with it.  (Sorry
> Howard.)  :)

> Now, what I really don't understand is that they only managed to change
> the comment character in certain places within the script, but not all.
> There's still a bunch of #'s instead of -- 's.  #'s are great if you're
> still using svrmgrl and 8.0.5, but this is 9i folks!  SQLPLUS likes --
> 's.  They only seemed to fix this in a few places, not the whole .trc
> file.  OK, minor nitpick, but still... if you're going to fix it, then
> why not fix it everywhere!

> Thanks for lettting me rant,
> Sean M

 
 
 

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 06:47:30




> > Have you noticed that backup controlfile to trace now outputs a
tracefile
> > containing the 'create controlfile' stuff *twice*? Once with the word
> > 'noresetlogs' attached. And once with the word 'resetlogs'. No other
> > differences detectable.

> There are 2 other differences in the two statements: 1) the alter
> database open command differs by the word resetlogs (not just the create
> controlfile statement) and 2) the recover database command does not
> contain the "using backup controlfile" clause for the noresetlogs case.
> So I guess I can understand why they allow for both scenarios since the
> does differ a bit more than just the "no".  I don't feel really strongly
> either way actually.

True. I kinda meant, when one does the removal of 'no', one usually
remembers to add the word 'resetlogs' lower down in the script too... like
vegemite and toast, you wouldn't have the one without the other, and I'd
sort of expect anyone doing a resetlogs to know that. But fair enough, it
*is* more than just two letters.

Quote:

> > This is happening in 9i R2, and it occurs to me that I don't think it
> > happened in 9i R1... but was wondering if anyone could check for me?

> Yes, you're correct, this is a new R2 feature - didn't happen like this
> in R1.

> > Quite what was wrong with editing out the two letters "no", like we used
to
> > in earlier versions, I have no idea. I can already smell the confusion
users
> > are going to get themselves into using the new, improved version.

> Yeah, might prove a bit difficult to explain to newbies, but I think I
> understand the intent, if not the execution, of the change.  Seems like
> they could just force you to add the word 'resetlogs' or 'noresetlogs'
> when you do your 'alter database backup controlfile to trace' command
> instead of letting it default.  That way you'd know what kind of script
> you're generating.  Instead they give you both.  Eh.  I suppose as long
> as I don't have to teach people about it, I'm OK with it.  (Sorry
> Howard.)  :)

> Now, what I really don't understand is that they only managed to change
> the comment character in certain places within the script, but not all.
> There's still a bunch of #'s instead of -- 's.  #'s are great if you're
> still using svrmgrl and 8.0.5, but this is 9i folks!  SQLPLUS likes --
> 's.  They only seemed to fix this in a few places, not the whole .trc
> file.  OK, minor nitpick, but still... if you're going to fix it, then
> why not fix it everywhere!

And that was going to be my next comment! (Beat me to the punch!).
Exactly.... I dislike an immensely important script throwing up errors for
no good reason at a critical time (ie, just when you're trying to recover
all your control files).

Regards
HJR

- Show quoted text -

Quote:> Thanks for lettting me rant,
> Sean M

 
 
 

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 06:48:45


Excellent. Thanks Sean

Regards
HJR


> Sorry, one more thing.  Just checked the 9i R2 SQL Reference manual, and
> sure enough, this is expected new functionality.  Check out the last
> sentence in particular:

> --------------------------------------------------------------------------
---
> TO TRACE

> Specify TO TRACE if you want Oracle to write SQL statements to a trace
> file rather than making a physical backup of the control file. You can
> use SQL statements written to the trace file to start up the database,
> re-create the control file, and recover and open the database
> appropriately, based on the created control file.

> You can copy the statements from the trace file into a script file, edit
> the statements as necessary, and use the script if all copies of the
> control file are lost (or to change the size of the control file).

>      Specify AS filename if you want Oracle to place the script into a
> file called filename rather than into the standard trace file.
>      Specify REUSE to allow Oracle to overwrite any existing file called
> filename.
>      RESETLOGS indicates that the SQL statement written to the trace
> file for starting the database is ALTER DATABASE OPEN RESETLOGS. This
> setting is valid only if the online logs are unavailable.
>      NORESETLOGS indicates that the SQL statement written to the trace
> file for starting the database is ALTER DATABASE OPEN NORESETLOGS. This
> setting is valid only if all the online logs are available.

> If you cannot predict the future state of the online logs, specify
> neither RESETLOGS nor NORESETLOGS. In this case, Oracle puts both
> versions of the script into the trace file, and you can choose which
> version is appropriate when the script becomes necessary.

> --------------------------------------------------------------------------
---

> So, that makes sense, I guess.

> Regards,
> Sean



> > > Have you noticed that backup controlfile to trace now outputs a
tracefile
> > > containing the 'create controlfile' stuff *twice*? Once with the word
> > > 'noresetlogs' attached. And once with the word 'resetlogs'. No other
> > > differences detectable.

> > There are 2 other differences in the two statements: 1) the alter
> > database open command differs by the word resetlogs (not just the create
> > controlfile statement) and 2) the recover database command does not
> > contain the "using backup controlfile" clause for the noresetlogs case.
> > So I guess I can understand why they allow for both scenarios since the
> > does differ a bit more than just the "no".  I don't feel really strongly
> > either way actually.

> > > This is happening in 9i R2, and it occurs to me that I don't think it
> > > happened in 9i R1... but was wondering if anyone could check for me?

> > Yes, you're correct, this is a new R2 feature - didn't happen like this
> > in R1.

> > > Quite what was wrong with editing out the two letters "no", like we
used to
> > > in earlier versions, I have no idea. I can already smell the confusion
users
> > > are going to get themselves into using the new, improved version.

> > Yeah, might prove a bit difficult to explain to newbies, but I think I
> > understand the intent, if not the execution, of the change.  Seems like
> > they could just force you to add the word 'resetlogs' or 'noresetlogs'
> > when you do your 'alter database backup controlfile to trace' command
> > instead of letting it default.  That way you'd know what kind of script
> > you're generating.  Instead they give you both.  Eh.  I suppose as long
> > as I don't have to teach people about it, I'm OK with it.  (Sorry
> > Howard.)  :)

> > Now, what I really don't understand is that they only managed to change
> > the comment character in certain places within the script, but not all.
> > There's still a bunch of #'s instead of -- 's.  #'s are great if you're
> > still using svrmgrl and 8.0.5, but this is 9i folks!  SQLPLUS likes --
> > 's.  They only seemed to fix this in a few places, not the whole .trc
> > file.  OK, minor nitpick, but still... if you're going to fix it, then
> > why not fix it everywhere!

> > Thanks for lettting me rant,
> > Sean M

 
 
 

er, 'interesting' new 9i feature

Post by Sean » Fri, 19 Jul 2002 07:22:23



> True. I kinda meant, when one does the removal of 'no', one usually
> remembers to add the word 'resetlogs' lower down in the script too... like
> vegemite and toast, you wouldn't have the one without the other, and I'd
> sort of expect anyone doing a resetlogs to know that. But fair enough, it
> *is* more than just two letters.

Yeah - I think the big difference is the 'using backup controlfile'
which, if you accidentally used it after having carefully created your
controlfile NORESETLOGS, will force you to open resetlogs nonetheless.
Might be really bad if you've got stuff in the online redos that's
important.  So there seems to be a method to the madness.

Quote:> And that was going to be my next comment! (Beat me to the punch!).
> Exactly.... I dislike an immensely important script throwing up errors for
> no good reason at a critical time (ie, just when you're trying to recover
> all your control files).

That's the worst feeling.  Horrible.  Even if it only lasts a few
seconds...

On the other hand, I do really like the "AS" and "REUSE" additions to
the new syntax for backup controlfile to trace.  Nice to be able to pick
the filenames instead of hunting down some CJDZSAZZXX123.trc from the
alertlog.

Regards,
Sean

 
 
 

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 07:37:34


The "AS" is pure gold dust, that's for sure.

Interesting you used the alert log in the past.... I always tossed of a

select p.spid
from v$process p, v$session s
where p.addr=s.paddr
and s.username='SCOTT';

...to find out my number and got it from there. I never thought to check the
alert log (shuffles uncomfortably away looking very guilty).

Regards
HJR



> > True. I kinda meant, when one does the removal of 'no', one usually
> > remembers to add the word 'resetlogs' lower down in the script too...
like
> > vegemite and toast, you wouldn't have the one without the other, and I'd
> > sort of expect anyone doing a resetlogs to know that. But fair enough,
it
> > *is* more than just two letters.

> Yeah - I think the big difference is the 'using backup controlfile'
> which, if you accidentally used it after having carefully created your
> controlfile NORESETLOGS, will force you to open resetlogs nonetheless.
> Might be really bad if you've got stuff in the online redos that's
> important.  So there seems to be a method to the madness.

> > And that was going to be my next comment! (Beat me to the punch!).
> > Exactly.... I dislike an immensely important script throwing up errors
for
> > no good reason at a critical time (ie, just when you're trying to
recover
> > all your control files).

> That's the worst feeling.  Horrible.  Even if it only lasts a few
> seconds...

> On the other hand, I do really like the "AS" and "REUSE" additions to
> the new syntax for backup controlfile to trace.  Nice to be able to pick
> the filenames instead of hunting down some CJDZSAZZXX123.trc from the
> alertlog.

> Regards,
> Sean

 
 
 

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 07:40:59


I'm going to take that guilt back, by the way. I've just checked my alert
log, and all I get is...

Wed Jul 17 14:56:38 2002
alter database backup controlfile to trace
Completed: alter database backup controlfile to trace

...no indication of filename, process id or anything.

How did you hunt down the name of your trace file from the alert log again?

Regards
HJR



> The "AS" is pure gold dust, that's for sure.

> Interesting you used the alert log in the past.... I always tossed of a

> select p.spid
> from v$process p, v$session s
> where p.addr=s.paddr
> and s.username='SCOTT';

> ...to find out my number and got it from there. I never thought to check
the
> alert log (shuffles uncomfortably away looking very guilty).

> Regards
> HJR




> > > True. I kinda meant, when one does the removal of 'no', one usually
> > > remembers to add the word 'resetlogs' lower down in the script too...
> like
> > > vegemite and toast, you wouldn't have the one without the other, and
I'd
> > > sort of expect anyone doing a resetlogs to know that. But fair enough,
> it
> > > *is* more than just two letters.

> > Yeah - I think the big difference is the 'using backup controlfile'
> > which, if you accidentally used it after having carefully created your
> > controlfile NORESETLOGS, will force you to open resetlogs nonetheless.
> > Might be really bad if you've got stuff in the online redos that's
> > important.  So there seems to be a method to the madness.

> > > And that was going to be my next comment! (Beat me to the punch!).
> > > Exactly.... I dislike an immensely important script throwing up errors
> for
> > > no good reason at a critical time (ie, just when you're trying to
> recover
> > > all your control files).

> > That's the worst feeling.  Horrible.  Even if it only lasts a few
> > seconds...

> > On the other hand, I do really like the "AS" and "REUSE" additions to
> > the new syntax for backup controlfile to trace.  Nice to be able to pick
> > the filenames instead of hunting down some CJDZSAZZXX123.trc from the
> > alertlog.

> > Regards,
> > Sean

 
 
 

er, 'interesting' new 9i feature

Post by Sean » Fri, 19 Jul 2002 08:04:07


Ah, sorry, I see what I wrote was confusing.  I normally just check the
timestamp of the backup controlfile command from the alertlog and match
it up to the right *.trc in UDUMP.  That or just grep for 'create
controlfile' in UDUMP and sort by time.  But that's only because I'm
usually doing this well after the fact.  In other words, my typical
scenario is:

- take yesterday's backup (our hotbackup scripts include a backup
controlfile to trace at the end) and copy it to another host
- go to primary host, dig through alert.log looking for timestamp when
backup controlfile statement was executed at the end of the hotbackup
along with the sequence number of the logfile containing the changes
from the hotbackup
- go to udump and find the appropriate *.trc file
- get the needed archive to the target host (restore from tape if need
be)
- copy the trc file to the target host and change the names as needed to
reflect new database name and filesystem names
- rebuild, recover, resetlogs

Now that you mention it, I suppose I could grab the spid in the
hotbackup script and record that for posterity, but, well, maybe one of
these days in all my free time... ;)

Regards,
Sean


> I'm going to take that guilt back, by the way. I've just checked my alert
> log, and all I get is...

> Wed Jul 17 14:56:38 2002
> alter database backup controlfile to trace
> Completed: alter database backup controlfile to trace

> ...no indication of filename, process id or anything.

> How did you hunt down the name of your trace file from the alert log again?

> Regards
> HJR



> > The "AS" is pure gold dust, that's for sure.

> > Interesting you used the alert log in the past.... I always tossed of a

> > select p.spid
> > from v$process p, v$session s
> > where p.addr=s.paddr
> > and s.username='SCOTT';

> > ...to find out my number and got it from there. I never thought to check
> the
> > alert log (shuffles uncomfortably away looking very guilty).

> > Regards
> > HJR

 
 
 

er, 'interesting' new 9i feature

Post by Howard J. Roger » Fri, 19 Jul 2002 08:12:34


Oh, good. So long as I wasn't missing something.

Cheers,
HJR


> Ah, sorry, I see what I wrote was confusing.  I normally just check the
> timestamp of the backup controlfile command from the alertlog and match
> it up to the right *.trc in UDUMP.  That or just grep for 'create
> controlfile' in UDUMP and sort by time.  But that's only because I'm
> usually doing this well after the fact.  In other words, my typical
> scenario is:

> - take yesterday's backup (our hotbackup scripts include a backup
> controlfile to trace at the end) and copy it to another host
> - go to primary host, dig through alert.log looking for timestamp when
> backup controlfile statement was executed at the end of the hotbackup
> along with the sequence number of the logfile containing the changes
> from the hotbackup
> - go to udump and find the appropriate *.trc file
> - get the needed archive to the target host (restore from tape if need
> be)
> - copy the trc file to the target host and change the names as needed to
> reflect new database name and filesystem names
> - rebuild, recover, resetlogs

> Now that you mention it, I suppose I could grab the spid in the
> hotbackup script and record that for posterity, but, well, maybe one of
> these days in all my free time... ;)

> Regards,
> Sean


> > I'm going to take that guilt back, by the way. I've just checked my
alert
> > log, and all I get is...

> > Wed Jul 17 14:56:38 2002
> > alter database backup controlfile to trace
> > Completed: alter database backup controlfile to trace

> > ...no indication of filename, process id or anything.

> > How did you hunt down the name of your trace file from the alert log
again?

> > Regards
> > HJR



> > > The "AS" is pure gold dust, that's for sure.

> > > Interesting you used the alert log in the past.... I always tossed of
a

> > > select p.spid
> > > from v$process p, v$session s
> > > where p.addr=s.paddr
> > > and s.username='SCOTT';

> > > ...to find out my number and got it from there. I never thought to
check
> > the
> > > alert log (shuffles uncomfortably away looking very guilty).

> > > Regards
> > > HJR

 
 
 

1. Interesting 'undocumented feature'

Hi

When I run the following in iSQL or iSQL_w:



I get:
Msg 913, Level 22, State 2
Could not find row in Sysdatabases with database id 30. Run DBCC
CHECKTABLE on Sysdatabases.

Interestingly it works on some servers, but only if there is a DB with
id = 30.

Has anybody got an idea why and how this happens?
Ta
LXG
---------------------------------------

---------------------------------------

2. SQL Desktop

3. OI new 'performance' features

4. Sybase Central - SQL code

5. One of Paradox 7's best features (that's not on the feature list)

6. Looking for Query Help (long post)

7. Oracle8's new features - what are they

8. JDBC Starting - need Help

9. Oracle8's new features

10. help with interesting 'query'

11. Interesting behaviour of : LIKE @v + '%'

12. Integrating c++ .dll's : Non-C'er needs help

13. looking for 'ER diagram' examples