RE: session idle time

[John Kanagaraj]

Chris,

The best way of establishing whether a session is active is by using a
combination of V$SESSION.LAST_CALL_ET, V$SESSION.STATUS and
V$SESSION_WAIT.EVENT. I normally use the scripts below to determine  the
status of the identified SID, and it has served me well (this has evolved
from a series of SQLs on ML and this list, so if this looks familiar, it
should :). The Last_call_et will show the time the last call (to
parse/execute) was made - if the status is ACTIVE, then I would look at the
currently excuting SQL from that session...

REM
REM  Name:  os_proc.sql
REM  Purpose:   Display the process details given a SID
REM  Author:John Kanagaraj, DBSoft Inc/ Aug 2001
REM  Notes: Added Session waits 11/17/02
REM  
column sid_serial heading "Sid,Ser#" format a10
column spid format 99 heading "OS Pid"
column username format a17 heading "DB/OSUser"
column status heading "Status" format a8
column program heading "Program" format a31  trunc
column  last_call_et format 999.99 heading "LastCallMins"
column logon_time format a18 heading "Logon Time"
column waiting_event format a47 heading "Waiting on event + p1/p2/p3" trunc
select s.sid || ', ' || s.serial# sid_serial, p.spid,
s.username || '/' || s.osuser username, s.status,
to_char(s.logon_time, 'DD-MON-YY HH24:MI:SS') logon_time,
s.last_call_et/60 last_call_et,
w.event || ' / ' || w.p1 || ' / ' || w.p2 || ' / ' || w.p3 waiting_event,
p.program
from v$process p, v$session s, v$session_wait w
where s.paddr=p.addr and s.sid=&Oracle_SID
and w.sid = s.sid;


REM
REM  Name:  curr_sql.sql
REM  Purpose:   Display the current SQL for a given SID
REM  Author:John Kanagaraj, DBSoft Inc/ Aug 2001
REM  Notes: Still needs some more work for formatting, extra details,
etc
REM  
col STMT format a75  heading 'Statement'
select SQL.SQL_TEXT STMT
  from V$SESSION SES
 , V$SQLTEXT_WITH_NEWLINES  SQL
 where SES.USERNAME is not null
   and SES.SQL_ADDRESS= SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and ses.sid = &SQL_for_Session_ID
 order by sql.piece
/

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my
future! 

** The opinions and statements above are entirely my own and not those of my
employer or clients **


> -Original Message-
> From: Sarnowski, Chris [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 31, 2003 11:56 AM
> To: Multiple recipients of list ORACLE-L
> Subject: session idle time
> 
> 
> 
> 
> Oracle 8.1.7.2 on Solaris 8.
> I'm looking for a way to see how long a session has been idle 
> or whether it's done any work. I've just been looking at 
> v$sesstat, specifically 'session connect time' and 'process 
> last non-idle time'. But every time I've queried these 
> numbers, they were the same for each SID except SMON (that 
> is, for a given SID, except the SID assiociated with SMON, 
> the 2 numbers are the same). So they must not measure what I 
> guessed they measure.
> 
> So the immediate question is, are these statistics useful for 
> anything? 
> 
> The actual problem I'm trying to solve is, we are using a 
> connection pooling method for Java that seems to allocate far 
> more connections than it ever uses, and I am trying to find a 
> way to document what is actually going on with these connections;
> i.e. whether some are never used, and how often connections 
> are reused.
> 
> thanks for any help, and sorry for the legal goop at the end.
> 
> -Chris
> -- 
> 
> 
> LEGAL NOTICE:
> Unless expressly stated otherwise, this message is 
> confidential and may be privileged. It is intended for the 
> addressee(s) only. Access to this e-mail by anyone else is 
> unauthorized. If you are not an addressee, any disclosure or 
> copying of the contents or any action taken (or not taken) in 
> reliance on it is unauthorized and may be unlawful. If you 
> are not an addressee, please inform the sender immediately.
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Sarnowski, Chris
>   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: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOV

re SHUTDOWN ABORT -- was RE: Debate on rc commands Solaris and

[Hemant K Chitale]

Comparing the shutdown scenarios with what would happen if someone
were to "pull the plug" on the server is the best way to explain the behaviour
of Oracle.
What if
  a. Someone switched-off the server [there's a power failure and the UPS
doesn't kick in] ?
  b.  The server crashes with a Unix Panic
well, the Oracle instance dies and yet, [as long as the disks are still good,
even if you have to do an "fsck"] there's Instance Recovery which goes through
successfully.
So what's the problem with SHUTDOWN ABORT ?  Its about the same thing,
but not as bad.

I consistently use SHUTDOWN ABORT, STARTUP RESTRICT, SHUTDOWN NORMAL
on Production, Mission-Critical databases.  I've never had a problem with a
SHUTDOWN ABORT.
Even a clone of an ABORTed instance can work if the online redo logs and 
current
control-file are also cloned !

I have seen SHUTDOWN IMMEDIATE hang very many times [particularly when
DBMS_JOB jobs are running or the un-intelligent Intelligent Agent is 
connected].

Hemant

At 05:29 PM 31-01-03 -0800, you wrote:
Dan,

If you look at Rama Velpuri's most famous treatise on B & R - namely
'Oracle8 Backup & Recovery Handbook' (Oracle Press), you will see the path
that the various shutdown options take. [Page 43, Chapter 2: The Oracle
Architecture and Configuration]. I belive this is the closest that we could
come to a published look under the covers. Some one mentioned a switch
logfile prior to the shutdown abort. In any case, a shutdown abort would not
be as bad as someone pulling the plug on the juice (as in Cleaning lady: 'I
need to plug in my hoover... aaah - here's a socket I can use). The instance
goes down not-so-gracefully, but the disk is still safe as long as the
server stays up.

Just my $0.02.
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my
future!

** The opinions and statements above are entirely my own and not those of my
employer or clients **


> -Original Message-
> From: Fink, Dan [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 30, 2003 8:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Debate on rc commands Solaris and Oracle
>
>
> I know I am going to catch major grief for this, but I avoid
> shutdown abort
> when possible. Shutdown immediate can take some time, as it will do
> transaction rollback in serial (where recovery at startup can do it in
> parallel and as needed). The main reason I avoid it is that
> there are known
> bugs that will cause the database to be unrecoverable when a
> shutdown abort
> is done while a kernel transaction is being performed.
> Granted, I have not
> personally encountered this, and the chances of encountering
> it are slight,
> but why take the risk?
>
> 
> Dan Fink
>
> -Original Message-
> Sent: Thursday, January 30, 2003 6:15 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> I'll echo that sentiment.
>
> 'shutdown abort', 'startup restrict' was a regular part of my
> shutdown scripts beginning in 1994 with 7.0.16, as
> 'shutdown immediate' wasn't all that reliable, even in situations
> where it should have worked.
>
> Jared
>
> On Wednesday 29 January 2003 15:53, John Kanagaraj wrote:
> > Rao,
> >
> > And where did you read that 'shutdown abort' is not
> recommended? This is
> > another myth that has been busted a while ago. A shutdown
> abort followed
> by
> > a startup restrict and a normal shutdown is the way to go
> when dealing
> with
> > rogue sessions that open a connection and never shutdown.
> In such cases, a
> > shutdown immediate will _never_ return (certainly not
> within your 5 to 10
> > minutes). I have been using this method for more than 8 years now -
> > starting at 7.0.16 fyi. The trick in this case is to script
> it into the rc
> > commands.
> >
> > John Kanagaraj
> > Oracle Applications DBA
> > DBSoft Inc
> > (W): 408-970-7002
> >
> > I don't know what the future holds for me, but I do know
> who holds my
> > future!
> >
> > ** The opinions and statements above are entirely my own
> and not those of
> > my employer or clients **
> >
> >
> > -Original Message-
> > Sent: Wednesday, January 29, 2003 11:49 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Paula,
> >
> > Shutdown abort is not recommended as the file checkpointing
> is not done
> > during shutdown abort.  If you need to perform shutdown
> abort, then, it is
> > preferred to bring up the db with startup restrict (so that
> the users
> > wouldn't connect) and then, cleanly shutdown the db and
> bring it up again.
> >
> > Tell to your sys admins. that shutdown immediate would take
> some time
> > (about 5 to 10 minutes) depending on the activity on your
> db.  They would
> > have to wait for that much time before calling a DBA during
> system boots.
> >
> > Rao
> >
> > -Original Message-
> > Sent: Wednesday, January 29, 2003 11:30 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > System Administra

Re: Oracle, Siebel and rule-based optimization

[Jared Still]

On Friday 31 January 2003 07:09, Michael Fontana wrote:
>
> Still, some of the queries in Siebel SEEM to run better using CBO.  Is
> there any way to mix and match approaches?  To use RBO by default, but
> perhaps CBO might make sense for certain queries, and perhaps some 
> AD-HOC?

The problem with using CBO for 'some' queries is that analyzing a table
turns on CBO for every query the table is used in, not just the ones
you wish.

Tony's approach sounds interesting enough to make me start playing
with plan stabilility.  Like I don't have enough to do.  :)

Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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).

RE: Debate on rc commands Solaris and Oracle

[John Kanagaraj]

Dan,

If you look at Rama Velpuri's most famous treatise on B & R - namely
'Oracle8 Backup & Recovery Handbook' (Oracle Press), you will see the path
that the various shutdown options take. [Page 43, Chapter 2: The Oracle
Architecture and Configuration]. I belive this is the closest that we could
come to a published look under the covers. Some one mentioned a switch
logfile prior to the shutdown abort. In any case, a shutdown abort would not
be as bad as someone pulling the plug on the juice (as in Cleaning lady: 'I
need to plug in my hoover... aaah - here's a socket I can use). The instance
goes down not-so-gracefully, but the disk is still safe as long as the
server stays up.

Just my $0.02.
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my
future! 

** The opinions and statements above are entirely my own and not those of my
employer or clients **


> -Original Message-
> From: Fink, Dan [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 30, 2003 8:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Debate on rc commands Solaris and Oracle
> 
> 
> I know I am going to catch major grief for this, but I avoid 
> shutdown abort
> when possible. Shutdown immediate can take some time, as it will do
> transaction rollback in serial (where recovery at startup can do it in
> parallel and as needed). The main reason I avoid it is that 
> there are known
> bugs that will cause the database to be unrecoverable when a 
> shutdown abort
> is done while a kernel transaction is being performed. 
> Granted, I have not
> personally encountered this, and the chances of encountering 
> it are slight,
> but why take the risk?
> 
> 
> Dan Fink
> 
> -Original Message-
> Sent: Thursday, January 30, 2003 6:15 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> I'll echo that sentiment.
> 
> 'shutdown abort', 'startup restrict' was a regular part of my
> shutdown scripts beginning in 1994 with 7.0.16, as 
> 'shutdown immediate' wasn't all that reliable, even in situations
> where it should have worked.
> 
> Jared
> 
> On Wednesday 29 January 2003 15:53, John Kanagaraj wrote:
> > Rao,
> >
> > And where did you read that 'shutdown abort' is not 
> recommended? This is
> > another myth that has been busted a while ago. A shutdown 
> abort followed
> by
> > a startup restrict and a normal shutdown is the way to go 
> when dealing
> with
> > rogue sessions that open a connection and never shutdown. 
> In such cases, a
> > shutdown immediate will _never_ return (certainly not 
> within your 5 to 10
> > minutes). I have been using this method for more than 8 years now -
> > starting at 7.0.16 fyi. The trick in this case is to script 
> it into the rc
> > commands.
> >
> > John Kanagaraj
> > Oracle Applications DBA
> > DBSoft Inc
> > (W): 408-970-7002
> >
> > I don't know what the future holds for me, but I do know 
> who holds my
> > future!
> >
> > ** The opinions and statements above are entirely my own 
> and not those of
> > my employer or clients **
> >
> >
> > -Original Message-
> > Sent: Wednesday, January 29, 2003 11:49 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Paula,
> >
> > Shutdown abort is not recommended as the file checkpointing 
> is not done
> > during shutdown abort.  If you need to perform shutdown 
> abort, then, it is
> > preferred to bring up the db with startup restrict (so that 
> the users
> > wouldn't connect) and then, cleanly shutdown the db and 
> bring it up again.
> >
> > Tell to your sys admins. that shutdown immediate would take 
> some time
> > (about 5 to 10 minutes) depending on the activity on your 
> db.  They would
> > have to wait for that much time before calling a DBA during 
> system boots.
> >
> > Rao
> >
> > -Original Message-
> > Sent: Wednesday, January 29, 2003 11:30 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > System Administrator says he doesn't trust that the rc 
> commands will stop
> > if the database doesn't want to shutdown and even if it 
> does would want to
> > shutdown with scripts beforehand so that a DBA could 
> connect and resolve
> > the issue.  Other DBA says this is all wrong and rc commands should
> include
> > shutdown immediate of database.  In the past I had setup 2 
> processes in
> the
> > system scripts for the sys admin - shutdown immediate - 
> wait  shutdown
> > abort - on a read-only DSS system which of course allows 
> some room for
> this
> > type of activity.  I kind of would want to know if a 
> database was going to
> > be shutdown with an abort esp. in OLTP system and do it myself.
> >
> >
> >
> > - any ideas
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jared Still
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -

RE: investigating database lockup

[John Kanagaraj]

Steve,

I have seen such a hang occur when there is a lot of untuned buffer-get
intensive SQLs on a 8.1.7.3 database. There is a nasty 'cache chain buffer'
latch issue in 8.1.7.3. You haven't mentioned the subversion, so you might
consider an upgrade to 8.1.74 if this is true and the situation reoccurs. I
have also seen severe contention for the shared pool/library cache latches
bring the system to a virtual halt. You mentioned many of the v$ tables, but
I think you missed out one that can be very useful: V$SESSION_WAIT. A rollup
of count by event will reveal volumes about what's going on currently. Steve
Adams has a 'waiters' sql in his site that will also help. You can then
build monitors around such scripts that will alert you before they reach a
threshold. Besides, there is a ML note that can help in determining apparent
hangs - top among them are the oradebug hanganalyze command.. Let me know
and I can dig up the note number.

Hth,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my
future! 

** The opinions and statements above are entirely my own and not those of my
employer or clients **


-Original Message-
Sent: Thursday, January 30, 2003 11:43 AM
To: Multiple recipients of list ORACLE-L


This morning I have experienced a couple of strange lockups of our database.
The symptoms were the database was nonresponsive to any activity, freeze on
login to sqlplus, active sessions freeze as soon as they interact with the
db.  There is no alert log activity.  The systems cpu usage soars to 100%,
and an oracle process, once a shared server and once a dedicated server, is
dominating the usage(info gathered from top).  
 
The first time I killed the shared server process, and the database picked
up and operated normally.  Then a half hour later the dedicated connection
popped up.  The dedicated connection was owned by one of our oltp processes
that pretty well runs without issue 24/7, so I decided to bounce the db to
hopefully clear up the issue.
 
Our system is Oracle 8.1.7 on Solaris 8.   In short, I am hoping to get some
ideas in tracking down what happened.  I have some sql scripts that I have
used to identify problem sessions/sql in the past.  They focus on v$session,
v$sess_io, and v$sqltext.  Unfortunately, these did me little good because
while the trouble was occurring I couldn't interact with the database while
the sessions were active.
 
I am soliciting actively for any Hints, Tips, or Ideas you may have,
 
Thanks,
Steve McClure
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  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).

Re: Replication..

[Mogens Nørgaard]

Yep - advanced replication is updateable snapshots and master-to-master replication,
whereas basic replication is read-only snapshots (known these days as materialized
views). And here's the reply from Dominic Delmolino regarding versions:

  
Hey  Mogens!
 
 
 
I'll  be seeing you at the conference -- bring me an ice-cold bottle
of Denmark's  finest akvavit.


No, wait, that was the wrong one. Sorry. Here's the one I was thinking about...

  
Advanced replication was introduced in 7.1.6

Best regards,

Mogens

Richard Ji wrote:

  My understanding is that Basic replication is "readonly snapshot".
Anything above that, including updatable snapshot is advanced replication.

Richard

-Original Message-
Sent: Wednesday, January 29, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Paul - That is not my understanding. If you look in the Oracle guide that
shows the differences between Standard Edition and Enterprise Edition,
Standard has something named "Basic Replication", and Enterprise has
"Advanced Replication". Advanced means multimaster replication. Basic means
snapshots. In between there are several other replication alternatives, like
updatable snapshots, but I haven't seen a clear explanation of where basic
stops and advanced starts. If anyone knows, I would appreciate their posting
the facts.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, January 29, 2003 2:59 PM
To: Multiple recipients of list ORACLE-L


I believe that "Advanced Replication" was just a name change,
introduced in either Oracle8 or Oracle8i.

PB
--- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote:
  
  
The person I know who can answer this is the guy who was Mr
Replication 
in Cary's SPG-group from the beginning, namely Dominic Delmolino. He
was 
truly a pioneer with that stuff. I'll ask him and get back.

Mogens

DENNIS WILLIAMS wrote:



  Chaim - I agree with your note, but isn't that basic replication?
  

Robert


  asked about advanced (multimaster) replication.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, January 27, 2003 2:12 PM
To: Multiple recipients of list ORACLE-L



  

>from metalink note: 28018.1


  
5.14  Symmetric Replication

---

 The Oracle symmetric

replication facility is new with

release 7.1.6. The symmetric

replication facility allows

multiple copies of data to be

maintained at different sites in

a distributed environment. It

provides immediate, local access

to data and allows systems to

function autonomously even when

other systems in the distributed

environment are unavailable, or

networks fail. To use the

symmetric replication facility,

you must have purchased and

installed the replication

option. The symmetric

replication facility is

documented in the "Oracle7

Server Distributed Systems:

Replicated Data" manual.










Freeman Robert - IL <[EMAIL PROTECTED]>@fatcity.com on 01/27/2003
  

02:29:24


  PM

Please respond to [EMAIL PROTECTED]

Sent by:[EMAIL PROTECTED]


To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:



Any of you Oracle history buffs remember what version of Oracle that
advanced replication was first available in?

RF

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Freeman Robert - IL
 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  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).

RE: Excessive library cache latch contention

[John Kanagaraj]

Neil,

A followup on JL's indication of CPU usage. You can use the following script
on most *nix platforms to determine the top CPU consumers - either across
all processes, as well as for a specific string such as a DB SID ($1).

#!/bin/ksh
uptime
echo "PID   %CPURUSER CPUTIME ELAPSED COMMAND"
if [ $# == 1 ]; then
ps -eo pid,pcpu,ruser,time,etime,args | grep $1 | sort -nr +1  |
head -20 | awk '{print substr($0,1,80)}'
else
ps -eo pid,pcpu,ruser,time,etime,args | sort -nr +1  | head -20 |
awk '{print substr($0,1,80)}'
fi

The interesting aside is that you can quickly identify a CPU-hungry process
by comparing the CPUTIME and ELAPSED time - if they are close, you have
found a possible culprit. Remember that the 'get-miss-spin-sleep' cycle is
going to consume a lot of CPU in the spin phase. If you aren't able to fix
the Forms, then you could alleviate the pain little bit by reducing the
_spin_count. (I had tried this in desperation on a Sequent box many moons
ago.) Basically, you are facilitating more CPU bandwidth at the risk of
inducing more occurences of sleep for 'bad' sessions, and that may be good
overall as this might help in reducing the 'waits holding' occurrences. I
believe this parameter has been obsoleted in the later versions, but I am
suggesting this as you did not mention a specific version.

As ever, YMMV!
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end
of your journey in this earth?

** The opinions and statements above are entirely my own and not those of my
employer or clients **


> -Original Message-
> From: Harvey Neil [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 31, 2003 9:25 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Excessive library cache latch contention
> 
> 
> Jonathan,
> 
> I think you've got a point on all three counts. I am seeing 
> an imbalance of
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  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).

RE: Tuning Large Pool

[brain_damage]

Kirti,
I have checked it out on 8i , it works out fine with P_A_T. Now i 'm beginning to 
think that it might be showing bogus value or there's some other factor affecting it. 
I restarted the db, changed the large_pool_size param , but the free memory is not 
budging from the 4Gigs. The only way i am able to bring it down is by turning P_A_T 
off.
Thanks for the PARALLEL_EXECUTION_POOL stuff. 'll check it out.

Regards,

Tc
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

RE: Tuning Large Pool

[brain_damage]

Dennis,
Its very much like that. But unsetting large_pool_size and turning 
parallel_automatic_tuning can crash the Database if the calculated size is 2 big. I 
guess in
this case the view is either showing a bogus value :)  or theres something i am
missing. I've tried restarting , changing large_pool_size , no differnce , free
memory in Large pool still sticks to 4 Gigs. Checked it out on 8i with P_A_T on
& large pool size set, no such problem there. turning off P_A_T is what i am going 
with for now.

Thanks.


Regards,
Tc
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

RE: Export / Import

[Jeremy Pulcifer]

Title: RE: Export / Import





exp -help


> -Original Message-
> From: Scott Stefick [mailto:[EMAIL PROTECTED]] 
> Sent: Friday, January 31, 2003 3:10 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Export / Import
> 
> 
> Hello,
> 
> OS Version: HP-UX 11i
> DB Version: Oracle 8.1.7.4 and 9.2.0.1
> 
> I've been looking around and I cannot seem to find a difinitive guide 
> online about Oracle's Export and Import.  I'm looking for all of the 
> parameters and what they do.  Could anyone point me in the 
> right direction.
> 
> Thanks,
> -Scott
> 
> **
> Scott Stefick
> UNIX Systems Administrator
> Oracle Certified Professional DBA
> Wm. Rainey Harper College
> 847.925.6130
> **
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Scott Stefick
>   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).
> 

RE: Export / Import

[Johnson, Michael]

>From the O/S prompt

$EXP HELP=Y  or
$IMP HELP=Y

or see the Utilities manual, I believe chapters 2 and 3.

Also, Kirti wrote up some good principles when
using export and import that I keep on file.

Im sure he could help you if you request it.

HTH,  Mike


-Original Message-
Sent: Friday, January 31, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L


Hello,

OS Version: HP-UX 11i
DB Version: Oracle 8.1.7.4 and 9.2.0.1

I've been looking around and I cannot seem to find a difinitive guide 
online about Oracle's Export and Import.  I'm looking for all of the 
parameters and what they do.  Could anyone point me in the right direction.

Thanks,
-Scott

**
Scott Stefick
UNIX Systems Administrator
Oracle Certified Professional DBA
Wm. Rainey Harper College
847.925.6130
**
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Scott Stefick
  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: Johnson, Michael 
  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).

RE: index hint ignored?

[Johnson, Michael]

Title: Message



We 
went thru this a couple days ago and Jonathan Lewis
provided some possible scenarios as to how things are 

working beneath the radar. 
 
Also, 
page 166-167 on Guy Harrisons Book on SQL tuning
will 
give you additional information.
 
Good 
Luck.  Mike

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  10:31 AMTo: Multiple recipients of list ORACLE-LSubject: 
  index hint ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to use the 
  index - but it always does a FTS. Why? (I've tried it with and 
  without the alias)
   
   
  SQL> 
  set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
  */ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
  3  ORDER BY veh_year DESC;
   
  20 rows 
  selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
  Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 Card=20 
  Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
  'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

Re: tkprof on 901 database

[Connor McDonald]

nope - its an oversight of oracle's.  You can upgrade
to 9.2 (which I'd recommend because its so much
better) or it might even be in one of the 9.0 patches.
 Using the 817 or 92 tkprof binary also seems to work
effectively.

hth
connor

 --- Randy Pace <[EMAIL PROTECTED]> wrote: > I have a
9.01 database installed on my laptop and
> cannot find the
> tkprof.exe. Has it been renamed or is it part of an
> installation that I did
> not do?
> 
> Thanks for any and all replys.
> 
> Randy R. Pace
> Development Team Leader
> 
> Accela, Inc. 
> 9662 South 700 East
> Sandy
> UT, 84070
> 
> Tel: 801 495 9300 
> Fax: 801 495 9301
> 
> www.accela.com  
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Randy Pace
>   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).
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  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).

export/import

[Scott Stefick]

Never mind.  After I posted, I found a good website that included all of 
what I was looking for.

Just incase anyone else wants that kind of info, here is the link:

http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96652/part1.htm

Thanks,
-Scott

**
Scott Stefick
UNIX Systems Administrator
Oracle Certified Professional DBA
Wm. Rainey Harper College
847.925.6130
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Scott Stefick
 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).

Re: how to create datafile in shared network drive ??

[Steve Perry]

Thanks for correcting me Richard.

I had tried that in the past and Oracle had  said it couldn't be done nor
would they want me doing it.
I agreed after thinking about it.

steve

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, January 30, 2003 9:49 PM


> Check out this metalink note:
>
> Note:211532.1
>
> -Original Message-
> Sent: Thursday, January 30, 2003 9:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Guys,
>
> the env. is 8.1.6/win2k.
>
> SQL > alter tablespace test add datafile
> '\\192.168.17.111\shared\test.dbf' size 100m;
>
> ORA-01119: error in creating database file
> '\\192.168.0.111\shared\test.dbf'
> ORA-27040: skgfrcre: create error, unable to create file
> OSD-04002: cannot open file.
> O/S-Error: (OS 5) access denied.
>
> But i have all access/permission to this drive.
> i mapped this shared network drive and then tried once again.
> even then i get the same error.
>
>
> Is it possible to create a tablespace with datafile residing in a
> shared network drive ??
>
> TIA.
>
> Prem.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: oraora  oraora
>   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: Richard Ji
>   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: Steve Perry
  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).

Export / Import

[Scott Stefick]

Hello,

OS Version: HP-UX 11i
DB Version: Oracle 8.1.7.4 and 9.2.0.1

I've been looking around and I cannot seem to find a difinitive guide 
online about Oracle's Export and Import.  I'm looking for all of the 
parameters and what they do.  Could anyone point me in the right direction.

Thanks,
-Scott

**
Scott Stefick
UNIX Systems Administrator
Oracle Certified Professional DBA
Wm. Rainey Harper College
847.925.6130
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Scott Stefick
 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).

RE: access/oracle

[DENNIS WILLIAMS]

Bill
   The way we have avoided the problem you describe is to create daily a
special set of tables that contain the data needed in Access. These tables
are then pulled over to Access and then reports are created in Access. If
the Oracle tables are part of an OLTP, the information probably needs some
processing before it is useful in Access. That is probably what they are
doing by joining the tables. Another idea might be to see if there are any
connection properties in Access that you could change.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 4:25 PM
To: Multiple recipients of list ORACLE-L



Hello,

We are stuck with a group of users who insist on using MS access 2K 9.0
on a Win2K workstation as an adhoc query front end to an 
Oracle 8.1.7.4 db on Sun.

The problem is that their queries run OK when executed from sqlplus
directly against Oracle, but are terrible when executed from access.
This is because access has tables linked to the oracle tables, and
access insists on downloading all the data, performing the joins and
where clause filters on the local workstation.

I don't know much about access, and don't want to; however, this has
been dumped on us, so I am hoping that someone has experienced the same
problem and found a solution. No, we can't dump access, and use something
different; for reasons beyond our control (management), we have to make
this work.

I think the solution is to somehow force the query to be processed on
the Oracle machine, and return only the result set. We have tried the
access "passthrough" option, but I believe that fails due to the newer 
ansi syntax used by access. (OUTER JOIN, INNER JOIN, etc.) The users insist
on using access's gui query generator.

Has anyone found a solution to this? Is there a good whitepaper somewhere
like,
"Using Access as a Front-End Query Tool to Oracle?" (I will search google)

Thanks to all who have read this far,
More thanks to any responders,
Most thanks for any helpful advice

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

tkprof on 901 database

[Randy Pace]

I have a 9.01 database installed on my laptop and cannot find the
tkprof.exe. Has it been renamed or is it part of an installation that I did
not do?

Thanks for any and all replys.

Randy R. Pace
Development Team Leader

Accela, Inc. 
9662 South 700 East
Sandy
UT, 84070

Tel: 801 495 9300 
Fax: 801 495 9301

www.accela.com  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Randy Pace
  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).

RE: access/oracle

[Bill Jonte]

We have a similar problem with our Data Warehouse users. If you can have
them set the pass through query parameter. The problem with this is that it
no longer point and click. The other thing we did was but views on top of
the oracle tables and had them go after the view.
Doing that let oracle try an optimize the queries some. We where also able
to place hints in the views so they would use selected indexes.

Hope these help.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 4:25 PM
To: Multiple recipients of list ORACLE-L



Hello,

We are stuck with a group of users who insist on using MS access 2K 9.0
on a Win2K workstation as an adhoc query front end to an 
Oracle 8.1.7.4 db on Sun.

The problem is that their queries run OK when executed from sqlplus
directly against Oracle, but are terrible when executed from access.
This is because access has tables linked to the oracle tables, and
access insists on downloading all the data, performing the joins and
where clause filters on the local workstation.

I don't know much about access, and don't want to; however, this has
been dumped on us, so I am hoping that someone has experienced the same
problem and found a solution. No, we can't dump access, and use something
different; for reasons beyond our control (management), we have to make
this work.

I think the solution is to somehow force the query to be processed on
the Oracle machine, and return only the result set. We have tried the
access "passthrough" option, but I believe that fails due to the newer 
ansi syntax used by access. (OUTER JOIN, INNER JOIN, etc.) The users insist
on using access's gui query generator.

Has anyone found a solution to this? Is there a good whitepaper somewhere
like,
"Using Access as a Front-End Query Tool to Oracle?" (I will search google)

Thanks to all who have read this far,
More thanks to any responders,
Most thanks for any helpful advice

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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: Bill Jonte
  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).

Re[2]: HELP!!! : Can't start database - Rollback Datafiles a

[dgoulet]

Steve,

Not totally true.  We did something similar, except that we forgot one of
the temp tablespace datafiles.  Oracle in it's infinite wisdom created a file
'MISSING00064' in $ORACLE_HOME/dbs.  The only way out of that is to offline drop
the datafile while mounted, meaning you've read the controlfile.  Then when you
open the database the tablespace stays offline & you can drop it and it's
contents thereby cleaning up the data dictionary.  Yes, you can't do that with
the rollback segments on line either, which is why you need to comment them out
of the init.ora.

Dick Goulet

Reply Separator
Author: Stephane Faroult <[EMAIL PROTECTED]>
Date:   1/31/2003 2:14 PM

Veronica Levin wrote:
> 
> Hi guys, I need help!
> At this moment, it doesn't matter how I messed everything up, but
> Development server is down.
> Had to recover operating system with a two month old backup.
> Oracle motor resides in the MkSysB backups, so some files where replaced,
> some datafiles where lost, and control file is mixed up.
> I can mount the database, but can't open it because rollback datafiles (and
> filesystem) where lost in the crash. Had to rebuild the filesystem, but I'm
> not able to rebuild the controlfile due to datafiles errors on the rollback
> tablespaces.
> I tried, with no success, to rebuild the controlfile leaving out the
> rollback datafiles, but that didn't work.
> What am I missing here???
> Is there a way I can recover the database without having to rebuild it?
> I do have a tape with a full backup (from last weekend), and I'm leaving
> that as a last resource. I also have full export from yesterday, wich I can
> download too.
> I made changes to the physical structure of the database today today, and
> would have to rebuild many filesystems to download that tapebut I will
> if I have to.
> I would appreciate any help you can give me
> please! (I've been on this matter for 6 hours already!)
> thanks,
> Vero.

Veronica,

 Normally you should be able to reopen your database by rebuilding the
controlfiles WITHOUT the rollback segment tablespace if :
  a) You comment out the rollback_segments = line in your init.ora
(otherwise Oracle will try to put them online)
  b) You create your controlfile with RESETLOGS (your current redo log
may contain references to a rollback segment). Please understand that
this will be bad for the integrity of your data. Typically, if a
transaction was going on at the moment of the crash, it will have been
'auto-committed' instead of being rolled back as you would expect since
Oracle will become unable to undo the changes. Have your users check
everything, and perhaps it would be wise to force constraints to be
checked.
 
 Then you should be able to recreate your rollback segment tablespace
and your rollback segments.

Good luck.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: 
  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).

Re:HELP!!! : Can't start database - Rollback Datafiles are m

[dgoulet]

Veronica,

First things first, calm down.

Second, assuming your control file hasn't been totally blasted to hell from
when you started, try the following:

0) shutdown the DB
1) make a backup
2) in the init.ora file, find the rollback_segement line & comment it out,
we don't want the database looking for them.

3) Startup mount the database.
4) alter database datafile  offline drop;
5) repeat 4 as mant times as necessary;
6) alter database open;
7) create rollback segment sys1 tablespace system;
8) alter rollback segment sys1 online;
9) drop tablespace 
;
11) create rollback segment ;
12) repeat 11 as often as needed per parameter in 2;
13) shutdown
14) make a backup
15) undo step 2
16) startup DB
17) Miller time.

DickG.

Reply Separator
Author: Veronica Levin <[EMAIL PROTECTED]>
Date:   1/31/2003 1:01 PM

Hi guys, I need help!
At this moment, it doesn't matter how I messed everything up, but
Development server is down.
Had to recover operating system with a two month old backup.
Oracle motor resides in the MkSysB backups, so some files where replaced,
some datafiles where lost, and control file is mixed up.
I can mount the database, but can't open it because rollback datafiles (and
filesystem) where lost in the crash. Had to rebuild the filesystem, but I'm
not able to rebuild the controlfile due to datafiles errors on the rollback
tablespaces.
I tried, with no success, to rebuild the controlfile leaving out the
rollback datafiles, but that didn't work.
What am I missing here???
Is there a way I can recover the database without having to rebuild it?
I do have a tape with a full backup (from last weekend), and I'm leaving
that as a last resource. I also have full export from yesterday, wich I can
download too. 
I made changes to the physical structure of the database today today, and
would have to rebuild many filesystems to download that tapebut I will
if I have to.
I would appreciate any help you can give me
please! (I've been on this matter for 6 hours already!)
thanks,
Vero.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Veronica Levin
  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: 
  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).

RE: Excessive library cache latch contention

[Cary Millsap]

Just to clarify...

If you upload something to our site, and you haven't purchased a license
to run the Hotsos Profiler, then make sure you send a note to
[EMAIL PROTECTED], letting him know that you're interested in
seeing a demo profile of your data.

We're glad to do demos of our software, especially if your trace file is
particularly interesting. Bear with us, though: if everyone responds at
once, then there'll be some queueing delay for assistance :).


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- 2003 Hotsos Symposium, Feb 9-12 Dallas
- RMOUG Training Days 2003, Mar 5-6 Denver
- Hotsos Clinic 101, Mar 25-27 London


-Original Message-
Michael 
Sent: Friday, January 31, 2003 3:13 PM
To: Multiple recipients of list ORACLE-L

Correction on this ... I should have said 
www.hotsos.com would have some additional
insights on dealing with 10046 data for you
if you are interested.

-Original Message-
Sent: Friday, January 31, 2003 10:57 AM
To: Multiple recipients of list ORACLE-L


Harvey,

Personally, I would go right to the user(s) who has complained
about the slowdown and have them run the application at the peak
hour/period where things seem very slow.

Set a 10046 event level 8 trace on that session(s) after they
log on and then take a look at the trace file in
the udump area after they are finished executing
the queries.   

There should be some strong clues in there about
why the session is the waiting. 

My bet is on some poorly written SQL and these session
competing for blocks.  

Check out Oracle Performance Tuning 101 Book and you
can send your 10046 event data to www.hotsos.com for review
if you are confused by it

fwiw.  good luck.  mike

-Original Message-
Sent: Friday, January 31, 2003 3:09 AM
To: Multiple recipients of list ORACLE-L


We've got about 30 sites all running the same application, and I'm
consistently seeing large numbers of 106 (library cache) latch free
waits.
They tend to happen at peak times during the day, and in the worst case
I
saw 12 sessions all on a 106 latch free wait event, spread across 3
P1RAW
addresses.

Running Steve Adams latch_sleeps scripts, yields the following:

LATCH TYPE IMPACT SLEEP RATE WAITS
HOLDING
LEVEL
- --- --
-
-
library cache 1281502  0.11%
2399666
5
cache buffers chains   273556  0.00%
23049
1
shared pool 73893  0.04%
91633
7
cache buffers lru chain 12236  0.01%
70756
3
session allocation  10639  0.06%
19969
5
row cache objects7835  0.00%
29816
4
cache buffer handles 3646  0.00%
2575
3
transaction allocation   2344  0.01%
4341
8
enqueue hash chains  1831  0.01%
13722
4
redo writing  778  0.01%
17328
5
session idle bit  714  0.00%
0
1

The results above are from an instance which has been up for 5 days

As you can see, library cache latch has a big impact (though I must
admit,
I'm not sure what Steve's IMPACT formula actually tells me). When I
check
across other sites, I see a similar pattern - large numbers of 106 latch
misses and sleeps.

I guess what I'd like to know is where these latches are happening,
which
objects / cursors etc are causing the contention. I've grappled with SQL
against x$kglob, trying to join back to the P1RAW but am not getting
very
far.

Any ideas?

TIA.

Neil.



--
This correspondence is confidential and is solely for the intended
recipient(s). If you are not the intended recipient, you must not use,
disclose, copy, distribute or retain this message or any part of it. If
you
are not the intended recipient please delete this correspondence from
your
system and notify the sender immediately. 

No warranty is given that this correspondence is free from any virus. In
keeping with good computer practice, you should ensure that it is
actually
virus free. E-mail messages may be subject to delays, non-delivery and
unauthorised alterations therefore, information expressed in this
message is
not given or endorsed by Sx3 unless otherwise notified by our duly
authorised representative independent of this message.

Sx3 is a trading name of Service and Systems Solutions Limited, a
limited
company registered in Northern Ireland under number NI 32979 whose
registered office is at 120, Malone Road, Belfast, BT9 5HT.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Harvey Neil
  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

access/oracle

[becker . bill]

Hello,

We are stuck with a group of users who insist on using MS access 2K 9.0
on a Win2K workstation as an adhoc query front end to an 
Oracle 8.1.7.4 db on Sun.

The problem is that their queries run OK when executed from sqlplus
directly against Oracle, but are terrible when executed from access.
This is because access has tables linked to the oracle tables, and
access insists on downloading all the data, performing the joins and
where clause filters on the local workstation.

I don't know much about access, and don't want to; however, this has
been dumped on us, so I am hoping that someone has experienced the same
problem and found a solution. No, we can't dump access, and use something
different; for reasons beyond our control (management), we have to make
this work.

I think the solution is to somehow force the query to be processed on
the Oracle machine, and return only the result set. We have tried the
access "passthrough" option, but I believe that fails due to the newer 
ansi syntax used by access. (OUTER JOIN, INNER JOIN, etc.) The users insist
on using access's gui query generator.

Has anyone found a solution to this? Is there a good whitepaper somewhere like,
"Using Access as a Front-End Query Tool to Oracle?" (I will search google)

Thanks to all who have read this far,
More thanks to any responders,
Most thanks for any helpful advice

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

RE: Making dispatchers re-read tnsnames.ora?

[John Kanagaraj]

Jeremiah,

I think Rich and I were maily addressing the SPoF question: When
architectured properly, ONames in itself can be quite reliable, and has
adequate fallback. Some of your other questions such as chopping up access
from segments and "cells" can be achieved using multiple TNS handles to the
same DB using different port numbers, etc (you point this out and we
concede). You handled this by creating _different_ contents in TNSNAMES.ORA
for different "cells", and have essentially tricked the system. So what is
to prevent a newbie from replacing all the TNSNAMES.ORA with a 'standard'
one? (apart from strict access controls?) And I do have a question - have
you tried pushing out TNSNAMES.ORA to 3000-4000 PC clients, all in the space
of an hour when a DB migration/server upgrade occurs? ONS is *the* man for
the job at such a time. As I said before, it was impossible for us to have
upgraded a major Apps 10.7 database without ONS. Another one is the addition
of new databases - it is a cinch with ONS... When you push out TNSNAMES.ORA,
you need to be sure that you have captured _all_ changes and nuances in the
client's file. This can be a major pain if you allow users to change the
TNSNAMES.ORA files. If ONS is being used, then you don't really care what
they put into the local files. If they change the TNS order, then they are
responsible for connection failures. This indeed has shown up on our screens
as 'consultants' who fiddle around without knowing what they were doing, and
serves as red flags...

As to fat-fingering, it can occur anywhere, and I bet both of us have
fat-fingered an important piece sometime in our lives :)  Actually, ONS
changes can also be scripted and tested. I have a dev/test ONS setup and
create/test my change scripts therein. And as I said before, maintenance and
cleanup as well as h/w switch of the ONS servers (via DNS aliases) is
simple, quick and easy to rectify. I cannot comment on the original
'dispatchers re-read tnsnames.ora' - I would then use local, limited entry
TNSNAMES.ORA as a special case, with the search order redefined to
(TNSNAMES, ONAMES) in this case.

As with everything in Life: YMMV!

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my
future! 

** The opinions and statements above are entirely my own and not those of my
employer or clients **


> -Original Message-
> From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 30, 2003 4:30 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Making dispatchers re-read tnsnames.ora?
> 
> 
> Well, my first objection to ONames was just in the context of solving
> my problem.  Dispatchers cache addresses for outgoing connections,
> whether they cache them from tnsnames.ora or ofrom Oracle Names.  Or
> so I understand it.  If someone knows differently about Names then I
> would like to hear about it.
> 
> As for the limitations of names...  Yes, you can have the local host
> caching of directory entries.  My objection is more to adding a new
> service with added equipment and expense where flat files actually do
> a better job.
> 
> Sitations with very high connection rates pose a problem.  Using a
> pool of application servers, there may be a need for multiple
> listeners to handle the load if a connection pooling technology is not
> in the picture.  In such a situation it is helpful for purpises of
> scalability and availability to segregate a large number of app
> servers into "cells" of, say, 10 or 20 servers each. You want each of
> these cells to be inidividually maintainable, and to be able to be put
> in and taken out of service independently, so that overall aailability
> for the application base is always maintained.
> 
> In the cell model, a listener or set of listeners is created FOR EACH
> CELL.  The TNS entries vary from cell to cell for that one service,
> because they specify different listeners.  The advantages to doing
> this are many.  With a cell out of service, you can take down a
> listener for whatever reason with no impact to the running
> application.  And if a listener or set of listeners becomes saturated,
> the majority of inbound connections are unaffected by the problem,
> thus limiting the scope of a fault.  AFAIK, you can't really do this
> in ONames without making up a different service name for every cell's
> listeners.
> 
> Now, say your service is so important that you don't want all the
> back-office Duke Nukem and Doom sessions saturating the network and
> interfering with the application traffic.  In reaction, you create a
> totally separate dedicated network for the most important systems in
> the company to communicate with the database server.  You add a
> network interface card with its own IP addfress, but you leave the old
> one there so that the back office people can make occasional database
> queries.  Naturally you will want separate listeners servicing 

Re: HELP!!! : Can't start database - Rollback Datafiles are missing -

[Stephane Faroult]

Veronica Levin wrote:
> 
> Hi guys, I need help!
> At this moment, it doesn't matter how I messed everything up, but
> Development server is down.
> Had to recover operating system with a two month old backup.
> Oracle motor resides in the MkSysB backups, so some files where replaced,
> some datafiles where lost, and control file is mixed up.
> I can mount the database, but can't open it because rollback datafiles (and
> filesystem) where lost in the crash. Had to rebuild the filesystem, but I'm
> not able to rebuild the controlfile due to datafiles errors on the rollback
> tablespaces.
> I tried, with no success, to rebuild the controlfile leaving out the
> rollback datafiles, but that didn't work.
> What am I missing here???
> Is there a way I can recover the database without having to rebuild it?
> I do have a tape with a full backup (from last weekend), and I'm leaving
> that as a last resource. I also have full export from yesterday, wich I can
> download too.
> I made changes to the physical structure of the database today today, and
> would have to rebuild many filesystems to download that tapebut I will
> if I have to.
> I would appreciate any help you can give me
> please! (I've been on this matter for 6 hours already!)
> thanks,
> Vero.

Veronica,

 Normally you should be able to reopen your database by rebuilding the
controlfiles WITHOUT the rollback segment tablespace if :
  a) You comment out the rollback_segments = line in your init.ora
(otherwise Oracle will try to put them online)
  b) You create your controlfile with RESETLOGS (your current redo log
may contain references to a rollback segment). Please understand that
this will be bad for the integrity of your data. Typically, if a
transaction was going on at the moment of the crash, it will have been
'auto-committed' instead of being rolled back as you would expect since
Oracle will become unable to undo the changes. Have your users check
everything, and perhaps it would be wise to force constraints to be
checked.
 
 Then you should be able to recreate your rollback segment tablespace
and your rollback segments.

Good luck.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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).

Re: why so many log switches?

[Jonathan Lewis]

This depends on the methods you are using
to load the data.  It does seem excessive even for
a simple row at a time loop - which probably shouldn't
use more that about 100MB according to the
volumes and indexes you've quoted.  But it could be
the result of a direct path load that rebuilds indexes,
or a clunky generated routine that does one insert and
multiple updates to populate a row.

Would you care to explain how the data gets
into the table, and what proportion of the
new rows are likely to have non-null values
that would affect the indexes.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 30 January 2003 20:34


>I have a table with 40 million rows.  Daily I load about 70,000
records into
>it.  Each record is 128 characters wide. The flat file the data comes
in is
>9 megs.  My redo logs are 20 megs each and I have 3 groups of them.
>
>When I load the data, the alert log shows 29 log switches which
generates a
>lot of archives logs. Why am I getting so many log switches?  I would
think
>that if the OS file is 9 megs and the redo logs are 20, I would at
most get
>1 log switch.
>
>Thanks,
>Randy
>--


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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).

RE: RMAN - problems restoring to clone server

[Johnson Poovathummoottil]

check the client in Parms it has to be the sever name
from which you did the backup.




--- Freeman Robert - IL <[EMAIL PROTECTED]> wrote:
> Just offhand it looks to me like maybe you cant find
> the backup piece on the
> tape server. Talk to your tape folks and make sure
> that you can get to the
> delenn tape server from teh ariel server. They may
> need to put the tapes you
> backed up on brian into a different class or
> something.
> 
> RF
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 1/30/2003 1:56 PM
> 
> The support analyst handling my TAR gave up, perhaps
> someone on here can
> 
> help me out...
> 
> I have an 8.0.4.4.0 database.  I used RMAN to
> perform a full backup of
> my 
> database on server A (Brain). The RMAN recovery
> catalog database is on 
> server B (Delenn). I am attempting to restore it to
> a 3rd instance which
> is 
> on server C (Ariel). On server C I created a new
> instance and it is in 
> startup nomount currently. From server B I am
> attempting to restore to 
> server C.
> 
> run {
> allocate channel t1  type 'SBT_TAPE' parms 
> 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> set newname for datafile 1 to
> '/usr/oracle/DB1/datfiles/system01.dbf';
> set newname for datafile 2 to
> '/usr/oracle/DB1/datfiles/rbs01.dbf';
> set newname for datafile 3 to
> '/usr/oracle/DB1/datfiles/temp01.dbf';
> set newname for datafile 4 to
> '/usr/oracle/DB1/datfiles/user01.dbf';
> restore database;
> release channel t1;
> }
> 
> 
> 
> 
> RMAN-10032: unhandled exception during execution of
> job step 1:
> ORA-06512: 
> at line 137
> RMAN-10035: exception raised in RPC: ORA-19624:
> operation failed, retry 
> possible
> ORA-19507: failed to retrieve sequential file, 
> handle="Incr_level0_db_DB1_151_1.hot", parms=""
> ORA-27007: failed to open file
> Additional information: 7009
> Additional information: 2
> ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> RMAN-10031: ORA-19624 occurred during call to 
> DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
> 
> 
> 
>
_
> Help STOP SPAM with the new MSN 8 and get 2 months
> FREE*  
> http://join.msn.com/?page=features/junkmail
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Gary Jackson
>   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: Freeman Robert - IL
>   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).
> 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Johnson Poovathummoottil
  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).

RE: BCHR Tuning

[Johnson, Michael]

Book me on the next flight to Sydney please.

-Original Message-
Sent: Monday, January 13, 2003 6:24 PM
To: Multiple recipients of list ORACLE-L


Morgens is very correct in saying tha all sorts of measurements have their 
place. Actually, the length-of-skirt measurement works very well for me. 
Here is the algorithm:

Heat-by-day in inversely proportional to length of skirt which again is 
inversely proportional to my desire to have lunch outdoors at sidewalk 
cafe, but which directly affects my enjoyment of the day and inversely 
affects my productivity (much like this posting).

Right now (Jan / Feb), we are experiencing our heatwaves (30+ Celsius by 
day, 20 by night = absolute bliss), so I get a lot of opportunity to 
streamline the algorithm and processes. Melbourne is the place to be, 
unless you can get to work on Bondi beach in Sydney, where the skirt length 
= zero.

Of course, if you study the skirts or lack of it TOO intensely, this leads 
a high jealous-boyfriend-hit-ratio, which inversely affects my overall 
well-being and morale, so you need to find that optimal balance between 
appreciation and blatant gawking or technically put : maximum benefit 
within minimized response time.

Ferenc Mantfeld

-Original Message-
From:   Mogens Norgaard [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, January 14, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: BCHR Tuning

Something here doesn't compute. If you tried to use time-based
measurements and didn't find out where the time went - well, bad for
you. If you then stumbled on something, say the database
startup/database shutdown ratio (would normally be fairly close to 1,
but could vary) or the log file switch/archive log file ratio (again,
could be close to 1 or 100% or something - or could vary) or the ratio
of blocks from a certain index found in the permanent pool versus the
number of PIO's required for that statement - or whatever - it would
still be guesswork, checklist tuning, or what you'd prefer to call it.

All sorts of measures have their place. All sorts of measures could
prove interesting. When I went to school the famous example was the wolf
population of Canada which seemed to follow the birthrate of children in
Denmark. Or the length of skirts versus economic prosperity in the
Western world, which also proved rather closely matched.

If you want to measure response time (what else?) it just might be of
interest to find out where the time is spent.

The BCHR, the x/y, the DBStarup/DBShutdown ratio or other ratios or
measurements might be important to find out symptoms of things - but to
say that that kind of guesswork still has it's place is like saying that
we should still carefully watch the wolf population of Canada or the
skirt length in the Western World...because you never know.

And that just might be the case: You never (will) know until you adopt
an approach that is hierachical (spelling?) and which you can use to
prioritize and quantify your efforts (try that with the BCHR - the
x$kcbrbh, etc. of course are grossly wrong in those respects).

Yep, I've been there, I've used it all, I've tried to use all the notes
and articles regarding the wonderful statistics available in bstat/estat
- I've been through the stages of collecting more and more queries and
numbers and ratios until my file with scripts and queries was bigger
than Holland. Yet it never gave me solutions, just a lot of things to
check and change and fiddle with - without knowing which one to choose
first, and how much it would help.

The YAPP method works. There are cases where it is not 100% accurate. In
most cases it's spot on. Watch where the monitoring tools are going.
Spotlight in the latest edition have the YAPP method built in. Let's see
what Oracle does in 10i. Precise has it. Steve Adams' scripts has it.

This is not about the BCHR being low or high or in between. This is
about using a METHOD instead of 100s of different numbers that don't
mean anything.

Mogens

[EMAIL PROTECTED] wrote:

>I too think the BCHR has its place, as a problem indicator. It can tell me
>theres something wrong with my database. Say, I have this database
>performing well, the users are happy, the BHR is mostly at 90%, and now it
>suddenly shoots down to 70%, or it suddenly increases to 98. Somethings
>amiss. Its less tasking, to code for scripts that query v$sysstat to
>indicate me of some problems, rather than querying v$sqlarea. Or I need to
>code for some intelligent scripts to query v$session_wait or
>V$system_event. Or I need to look at the statspack reports every hour. The
>point is when do I look at wait events? When the user calls me up?
>
>All the papers out there, asking us rightly, to look at wait events, trash
>the BCHR. I think what the authors intended was to tell us that increasing
>DB_BLOCK_BUFFERS was not the solution to a low BCHR, and that a BCHR of 
99%
>does not mean a highly efficient database. Vice Versa, a BCHR of 50%

Re: HELP!!! : Can't start database - Rollback Datafiles are missing -

[tim]

What is your TAR number?

> Hi guys, I need help!
> At this moment, it doesn't matter how I messed everything
> up, but Development server is down.
> Had to recover operating system with a two month old
> backup. Oracle motor resides in the MkSysB backups, so
> some files where replaced, some datafiles where lost, and
> control file is mixed up. I can mount the database, but
> can't open it because rollback datafiles (and filesystem)
> where lost in the crash. Had to rebuild the filesystem,
> but I'm not able to rebuild the controlfile due to
> datafiles errors on the rollback tablespaces.
> I tried, with no success, to rebuild the controlfile
> leaving out the rollback datafiles, but that didn't work.
> What am I missing here???
> Is there a way I can recover the database without having
> to rebuild it? I do have a tape with a full backup (from
> last weekend), and I'm leaving that as a last resource. I
> also have full export from yesterday, wich I can download
> too.  I made changes to the physical structure of the
> database today today, and would have to rebuild many
> filesystems to download that tapebut I will if I have
> to. I would appreciate any help you can give me
> please! (I've been on this matter for 6 hours already!)
> thanks,
> Vero.
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net -- 
> Author: Veronica Levin
>   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: 
  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).

Re: hotel notice for folks considering attending IOUG

[Jared . Still]

No Matt, I wouldn't consider this OT.

Off topic indicators for a post:

* does this message have anything to do with Oracle?

* does it pertain to the functions of a DBA?

* does it relate to 3rd party software that many of us are stuck
  with, and also happens to run on Oracle?

* does it have to do with OS/HW issues that affect Oracle RDBMS?

* ...

So, this is definitely on topic.

Thanks,

Jared






"Adams, Matthew (GECP, MABG, 088130)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/31/2003 10:31 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:hotel notice for folks considering attending IOUG


I got this in the e-mail today. 
If you are considering attending the IOUG-A live 
in April, please read the below. 
(I hope Jared doesn't consider too far OT) 
--- 
Dear IOUG Member: 
  
Please be aware that an organization which has no affiliation with 
the IOUG is contacting our members and representing themselves 
as part of our organization - for the purpose of selling hotel 
rooms for this year's Live! event in Orlando. 
  
This company has no affiliation with the IOUG and we do not 
endorse their activity.  We strongly recommend that you do not 
do business with them, should they contact you directly. 
  
Our attorney is contacting this organization to notify them to 
cease this activity. If you have any questions, comments or 
 concerns, please contact our headquarters staff at +1.312.245.1579. 
Many thanks. 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

RE: Oracle 101 Performance Tuning comes to the rescue again!

[Paula_Stankus]

It is 
an excellent book!!!

  -Original Message-From: Mercadante, Thomas F 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  12:52 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Oracle 101 Performance Tuning comes to the rescue again!
  All,
   
  you 
  *MUST* buy this book.
   
  I 
  just got called over by the Warehouse people.  Their database was 
  hung.  We could log-on ok, but certain queries would 
  hang.
   
  Ran 
  the four "wait-state" queries and saw that two queries were hung on library 
  cache.  the two queries were an analyze table and a MV refresh - using 
  the same table.  hung them both out to dry.
   
  killed the analyze and the MV started up again.
   
  great book.  solves all problems.  great job Gaja, Kirti and 
  John.  you guys do the work, and I look like a hero.
   
  thanks again.
   
  Tom Mercadante Oracle Certified Professional 
   

RE: Reinitiate Sequence Number

[Hamid Alavi]

Title: RE: Reinitiate Sequence Number



Rajendra,
 
Thanks 
allot

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  12:15 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Reinitiate Sequence Number
  Search on Google ... you'll find many hits ... 
  http://www.google.com/search?q=recreating+oracle+sequences&hl=en&lr=&ie=UTF-8&oe=UTF-8 
  
  Raj __ Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  -Original Message- From: Hamid 
  Alavi [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, January 31, 2003 2:43 PM To: Multiple recipients of list ORACLE-L Subject: RE: ReInitiate Sequence Number 
  Kirti, 
  I test it it doesn't work. 







=== Confidentiality Statement === 

The information contained in this message and any attachments is 

intended only for the use of the individual or entity to which it is 

addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 

and exempt from disclosure under applicable law.  If you have received 

this message in error, you are prohibited from copying, distributing, or 

using the information.  Please contact the sender immediately by return 

e-mail and delete the original message from your system. 

= End Confidentiality Statement =  

Re: ReInitiate Sequence Number

[Jared . Still]

see http://www.cybcon.com/~jkstill/util/reset_sequence/reset_sequence.html
for an article on how to do this.

Jared






Hamid Alavi <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/31/2003 10:20 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:ReInitiate Sequence Number


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I 
want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement = 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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: 
  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).

RE: Excessive library cache latch contention

[Johnson, Michael]

Correction on this ... I should have said 
www.hotsos.com would have some additional
insights on dealing with 10046 data for you
if you are interested.

-Original Message-
Sent: Friday, January 31, 2003 10:57 AM
To: Multiple recipients of list ORACLE-L


Harvey,

Personally, I would go right to the user(s) who has complained
about the slowdown and have them run the application at the peak
hour/period where things seem very slow.

Set a 10046 event level 8 trace on that session(s) after they
log on and then take a look at the trace file in
the udump area after they are finished executing
the queries.   

There should be some strong clues in there about
why the session is the waiting. 

My bet is on some poorly written SQL and these session
competing for blocks.  

Check out Oracle Performance Tuning 101 Book and you
can send your 10046 event data to www.hotsos.com for review
if you are confused by it

fwiw.  good luck.  mike

-Original Message-
Sent: Friday, January 31, 2003 3:09 AM
To: Multiple recipients of list ORACLE-L


We've got about 30 sites all running the same application, and I'm
consistently seeing large numbers of 106 (library cache) latch free waits.
They tend to happen at peak times during the day, and in the worst case I
saw 12 sessions all on a 106 latch free wait event, spread across 3 P1RAW
addresses.

Running Steve Adams latch_sleeps scripts, yields the following:

LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING
LEVEL
- --- -- -
-
library cache 1281502  0.11%   2399666
5
cache buffers chains   273556  0.00% 23049
1
shared pool 73893  0.04% 91633
7
cache buffers lru chain 12236  0.01% 70756
3
session allocation  10639  0.06% 19969
5
row cache objects7835  0.00% 29816
4
cache buffer handles 3646  0.00%  2575
3
transaction allocation   2344  0.01%  4341
8
enqueue hash chains  1831  0.01% 13722
4
redo writing  778  0.01% 17328
5
session idle bit  714  0.00% 0
1

The results above are from an instance which has been up for 5 days

As you can see, library cache latch has a big impact (though I must admit,
I'm not sure what Steve's IMPACT formula actually tells me). When I check
across other sites, I see a similar pattern - large numbers of 106 latch
misses and sleeps.

I guess what I'd like to know is where these latches are happening, which
objects / cursors etc are causing the contention. I've grappled with SQL
against x$kglob, trying to join back to the P1RAW but am not getting very
far.

Any ideas?

TIA.

Neil.



--
This correspondence is confidential and is solely for the intended
recipient(s). If you are not the intended recipient, you must not use,
disclose, copy, distribute or retain this message or any part of it. If you
are not the intended recipient please delete this correspondence from your
system and notify the sender immediately. 

No warranty is given that this correspondence is free from any virus. In
keeping with good computer practice, you should ensure that it is actually
virus free. E-mail messages may be subject to delays, non-delivery and
unauthorised alterations therefore, information expressed in this message is
not given or endorsed by Sx3 unless otherwise notified by our duly
authorised representative independent of this message.

Sx3 is a trading name of Service and Systems Solutions Limited, a limited
company registered in Northern Ireland under number NI 32979 whose
registered office is at 120, Malone Road, Belfast, BT9 5HT.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Harvey Neil
  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: Johnson, Michael 
  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 mes

RE: ReInitiate Sequence Number

[Deshpande, Kirti]

Did you use 'recycle'? 
Sequence must not use 'cache' when attempting to do this. 

You may also want to check out other options from the Google link posted by Raj.

- Kirti 

-Original Message-
Sent: Friday, January 31, 2003 2:39 PM
To: Multiple recipients of list ORACLE-L


Kirti,

I have to set the maxvalue manually, when I do this still nextval not reset.
For example if the maxvalue is 100 and the current value is 500, some
how I have to set alter sequence to reach the maxvalue so the next value
start from 0 again.
This test doesn't do that.


-Original Message-
Sent: Friday, January 31, 2003 12:08 PM
To: Multiple recipients of list ORACLE-L


Please post your test... 

- Kirti

-Original Message-
Sent: Friday, January 31, 2003 1:43 PM
To: Multiple recipients of list ORACLE-L


Kirti,

I test it it doesn't work.


-Original Message-
Sent: Friday, January 31, 2003 11:09 AM
To: [EMAIL PROTECTED]
Cc: Hamid Alavi


Hamid,

Here is one way:

SQL> select myseq.currval from dual;

   CURRVAL
--
 4

SQL> alter sequence myseq nocache;

Sequence altered.

SQL> alter sequence myseq maxvalue 4 cycle;

Sequence altered.

SQL> select myseq.nextval from dual;

   NEXTVAL
--
 1

SQL> REM ---> Other alter sequence commands to change maxvalue, cache,
nocycle etc. 
   
Now, you get to do the tricky part of automating it ;) Because, I have not
attempted to do it, yet. 

HTH,

- Kirti


-Original Message-
Sent: Friday, January 31, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).







=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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: Deshpande, Kirti
  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 remove

Re: Check for all numeric in a column

[Jared . Still]

create or replace function is_number( chk_data_in varchar2 )
return boolean
is
dummy number(38,4);
begin
dummy := to_number(chk_data_in);
return true;
exception
when value_error then
return false;
when others then
raise;
end;
/

show errors function is_number


declare
v_test varchar2(10) := '1E';
begin
if is_number(v_test) then
dbms_output.put_line(v_test || ' is a number');
else
dbms_output.put_line(v_test || ' is NOT a number');
end if;
end;
/





"Smith, Ron L." <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/31/2003 08:24 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Check for all numeric in a column


What is the easiest / best way to check for a valid numeric value in a 
column?
 
R. Smith
 
If you are not the intended recipient of this e-mail message, any use, 
distribution or copying of the message is prohibited. Please let me know 
immediately by return e-mail if you have received this message by mistake, 
then delete the e-mail message. Thank you.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

HELP!!! : Can't start database - Rollback Datafiles are missing -

[Veronica Levin]

Hi guys, I need help!
At this moment, it doesn't matter how I messed everything up, but
Development server is down.
Had to recover operating system with a two month old backup.
Oracle motor resides in the MkSysB backups, so some files where replaced,
some datafiles where lost, and control file is mixed up.
I can mount the database, but can't open it because rollback datafiles (and
filesystem) where lost in the crash. Had to rebuild the filesystem, but I'm
not able to rebuild the controlfile due to datafiles errors on the rollback
tablespaces.
I tried, with no success, to rebuild the controlfile leaving out the
rollback datafiles, but that didn't work.
What am I missing here???
Is there a way I can recover the database without having to rebuild it?
I do have a tape with a full backup (from last weekend), and I'm leaving
that as a last resource. I also have full export from yesterday, wich I can
download too. 
I made changes to the physical structure of the database today today, and
would have to rebuild many filesystems to download that tapebut I will
if I have to.
I would appreciate any help you can give me
please! (I've been on this matter for 6 hours already!)
thanks,
Vero.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Veronica Levin
  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).

RE: ReInitiate Sequence Number

[Hamid Alavi]

Kirti,

I have to set the maxvalue manually, when I do this still nextval not reset.
For example if the maxvalue is 100 and the current value is 500, some
how I have to set alter sequence to reach the maxvalue so the next value
start from 0 again.
This test doesn't do that.


-Original Message-
Sent: Friday, January 31, 2003 12:08 PM
To: Multiple recipients of list ORACLE-L


Please post your test... 

- Kirti

-Original Message-
Sent: Friday, January 31, 2003 1:43 PM
To: Multiple recipients of list ORACLE-L


Kirti,

I test it it doesn't work.


-Original Message-
Sent: Friday, January 31, 2003 11:09 AM
To: [EMAIL PROTECTED]
Cc: Hamid Alavi


Hamid,

Here is one way:

SQL> select myseq.currval from dual;

   CURRVAL
--
 4

SQL> alter sequence myseq nocache;

Sequence altered.

SQL> alter sequence myseq maxvalue 4 cycle;

Sequence altered.

SQL> select myseq.nextval from dual;

   NEXTVAL
--
 1

SQL> REM ---> Other alter sequence commands to change maxvalue, cache,
nocycle etc. 
   
Now, you get to do the tricky part of automating it ;) Because, I have not
attempted to do it, yet. 

HTH,

- Kirti


-Original Message-
Sent: Friday, January 31, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).







=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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: Deshpande, Kirti
  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).





=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entit

RE: RMAN - problems restoring to clone server

[Jay Hostetter]

Gary,

 This is the procedure that I use to setup a duplicate/clone/auxiliary database, as 
outlined on pg. 7-18 of the 8.1.6 Rel.2 RMAN User's Guide and Reference.  This creates 
a duplicate database with a unique dbid.  Rman knows that it should recover to the 
clone because I issue a duplicate command in my script.  The command parameters are 
confusing ('target' would more aptly be named 'source' and 'auxiliary' is the real 
'target' when cloning).  
  Looking back over your email, I see that you are on 8.0.6, so there may be some 
differences.  I better let the advice to those that are more experienced with that 
version.

  Here is a sample script that I use:

run {
set until scn 1016905055;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
set newname for datafile 1 to '/t202/oradata/INTT/system01.dbf';
set newname for datafile 2 to '/t202/oradata/INTT/tools01.dbf';
...yadda yadda...
set newname for datafile 11 to '/t204/oradata/INTT/tape_data01.dbf';
duplicate target database to INTT
logfile
group 1 ('/t202/oradata/INTT/redo1a.log','/t203/oradata/INTT/redo1b.log') size 1m,
...yadda yadda...
group 4 ('/t202/oradata/INTT/redo4a.log','/t203/oradata/INTT/redo4b.log') size 1m;
}

 Jay

>>> "Gary Jackson" <[EMAIL PROTECTED]> 01/31/03 02:54PM >>>
Jay,
  I'm confused. Based on what you said I would want to connect my target to 
my production db and my catalog to my dbcat/tapeserver. If I understand your 
suggestion correctly, how would RMAN know to recover to the new clone 
database?
-gary





**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Hostetter
  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).

RE: Oracle 101 Performance Tuning comes to the rescue again!

[Paula_Stankus]

Love 
the book as well - have used it for tuning various times.  
Automate...automate..automate...

  -Original Message-From: Post, Ethan 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 2:43 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Oracle 101 Performance Tuning comes to the rescue again!
  Tom, would a monitor in place have notified the admins as to a locking 
  issue?  I usually run Steve Adam's enqueue.sql script to find locked 
  objects.  Also have monitors in place that generate an email when locked 
  exceed a specific time.  Pretty typical for me to call an app dev and ask 
  him if he forgot to issue a commit, which is usually the case.  By the 
  way I second the book recommend.  I went to once of Gaja's presentations 
  and got the gist of the methodology but when I bought the book I was able to 
  actually absorb it.
  
-Original Message-From: Mercadante, Thomas F 
[mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
11:52 AMTo: Multiple recipients of list 
ORACLE-LSubject: Oracle 101 Performance Tuning comes to the 
rescue again!
All,
 
you *MUST* buy this book.
 
I 
just got called over by the Warehouse people.  Their database was 
hung.  We could log-on ok, but certain queries would 
hang.
 
Ran the four "wait-state" queries and saw that two queries were hung 
on library cache.  the two queries were an analyze table and a MV 
refresh - using the same table.  hung them both out to 
dry.
 
killed the analyze and the MV started up again.
 
great book.  solves all problems.  great job Gaja, Kirti 
and John.  you guys do the work, and I look like a 
hero.
 
thanks again.
 
Tom Mercadante Oracle Certified Professional 
 

RE: index hint ignored?

[Fink, Dan]

Title: Message



I 
don't think it matters if there are nulls or not. As long as they are allowed is 
the issue (as I recall from an earlier thread).
 
Dan

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  12:56 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: index hint ignored?
  Hi 
  Dan,
   
  H. That's interesting. Yes, the column does allow nulls. Not sure 
  if there actually are any null values, I'll have to 
  check...
   
  Thanks!
   
  - 
  Jerry
  

-Original Message-From: Fink, Dan 
[mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 2:31 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
index hint ignored?
Gerald,
    Is veh_year a nullable column? If so, an index lookup 
may give you incorrect results (nulls are not indexed), so a full table scan 
will be used.
    Of course, is a full table scan a bad choice? Don't 
worry about the access path, find the plan that incurs the fewest I/Os and 
returns the quickest.
 
Dan Fink

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 
  2003 11:31 AMTo: Multiple recipients of list 
  ORACLE-LSubject: index hint ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to 
  use the index - but it always does a FTS. Why? (I've tried it with 
  and without the alias)
   
   
  SQL> set autotrace traceonlySQL> SELECT /*+ 
  INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year  2  FROM 
  TIREADVISOR.vehicle A  3  ORDER BY veh_year 
  DESC;
   
  20 
  rows selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE 
  (Cost=118 Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 
  Card=20 Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER 
  = 'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

RE: RMAN - problems restoring to clone server

[Gary Jackson]

Thank you all for your help on this. The solution was a misconfiguration of 
Legato. My tape SA and I discovered that Legato had not been configured to 
allow permissions for files to be restored from my source database to my 
clone database. We granted this through the 'nwrecover', and the database 
has begun its restore process.

Again, thanks to all for their tips/help!!!
-Gary






From: Janardhana Babu Donga <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: RMAN - problems restoring to clone server
Date: Fri, 31 Jan 2003 10:57:04 -0800

Contd

It looks to me that you are trying to restore to Ariel from Delenn (where
catalog database resides). You must log in to the Ariel Box, switch to the
DB using "oraenv", then connect to DB on Ariel( which is in nomount state)
and the catalog DB (Delenn) as:
 rman target / catalog rman/rman@.
then follow the instructions below. Make sure you have permission to 
restore
to ARIEL from the backup taken by BRAIN.

-- Babu


I do this many times with Veritas NetBackup. The steps may be similar for
legato with slight variation.

[1] By Default Netbackup or Legato restores files only to the client from
which the files were backed up(Brain). Your Netbackup administrator should
configure to let you restore to an alternate client(Ariel). There may be
similar steps for Legato.

[2] Startup nomount

[3] run {
  allocate channel t1 type 'SBT_TAPE'
parms="ENV=(NB_ORA_CLIENT=brain..com; export
NB_ORA_CLIENT)";
  restore controlfile;
  alter database mount;
}

Look for similar parameter for Legato, may be NSR_CLIENT in place of
NB_ORA_CLIENT.

[4] run {
  allocate channel t1 type 'SBT_TAPE'
parms="ENV=(NB_ORA_CLIENT=brain..com; export
NB_ORA_CLIENT)";
  restore database;
}

Hope this helps.

-- Babu

-Original Message-
Sent: Friday, January 31, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L


Legato Networker version 6.1.1.Build.238
OS:  SunOS 5.6
Database: Oracle8 Enterprise Edition Release 8.0.4.4.0

TIA!






>From: Jared Still <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED], "Gary Jackson" <[EMAIL PROTECTED]>
>Subject: Re: RMAN - problems restoring to clone server
>Date: Fri, 31 Jan 2003 07:02:06 -0800
>
>
>What is your MML?
>
>Jared
>
>On Thursday 30 January 2003 11:56, Gary Jackson wrote:
> > The support analyst handling my TAR gave up, perhaps someone on here 
can
> > help me out...
> >
> > I have an 8.0.4.4.0 database.  I used RMAN to perform a full backup of
>my
> > database on server A (Brain). The RMAN recovery catalog database is on
> > server B (Delenn). I am attempting to restore it to a 3rd instance 
which

>is
> > on server C (Ariel). On server C I created a new instance and it is in
> > startup nomount currently. From server B I am attempting to restore to
> > server C.
> >
> > run {
> > allocate channel t1  type 'SBT_TAPE' parms
> > 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> > set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf';
> > set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf';
> > set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf';
> > set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf';
> > restore database;
> > release channel t1;
> > }
> >
> >
> > 
> >
> > RMAN-10032: unhandled exception during execution of job step 1:
>ORA-06512:
> > at line 137
> > RMAN-10035: exception raised in RPC: ORA-19624: operation failed, 
retry
> > possible
> > ORA-19507: failed to retrieve sequential file,
> > handle="Incr_level0_db_DB1_151_1.hot", parms=""
> > ORA-27007: failed to open file
> > Additional information: 7009
> > Additional information: 2
> > ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> > RMAN-10031: ORA-19624 occurred during call to
> > DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
> >
> >
> >
> > _
> > Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> > http://join.msn.com/?page=features/junkmail


_
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
  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 

RE: Reinitiate Sequence Number

[Jamadagni, Rajendra]

Title: RE: Reinitiate Sequence Number





Search on Google ... you'll find many hits ...


http://www.google.com/search?q=recreating+oracle+sequences&hl=en&lr=&ie=UTF-8&oe=UTF-8


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Hamid Alavi [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 2:43 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ReInitiate Sequence Number



Kirti,


I test it it doesn't work.



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

RE: index hint ignored?

[Jamadagni, Rajendra]

Title: RE: index hint ignored?





Hmmm ... without where clause you are retrieving all rows ... how would you enforce index? 


Try adding 
where veh_year = veh_year ...


Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Cunningham, Gerald [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 2:56 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: index hint ignored?



Hi Dan,


H. That's interesting. Yes, the column does allow nulls. Not sure if there actually are any null values, I'll have to check...

Thanks!


- Jerry



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

RE: PS question

[Dawtrey, Lindsay L]

Is Braintree's SQLSecure product installed? It is software that is
PeopleSoft aware and enhances the somewhat weak password management in pre
PS8 releases.
I think the monitoring software used DBMS_JOB but I'm not certain, it's some
years since we had a demo version of SQLsecure installed.
Other than that I agree with David in that PS tries to avoid DBMS specific
features.

Lindsay  


-Original Message-
Sent: Friday, January 31, 2003 7:20 PM
To: Multiple recipients of list ORACLE-L


Lisa,

I have seen no requirement for DBMS_JOB from PeopleSoft in HRMS 7.5 (or 
earlier). Though we have used it in the past ourselves when we were doing 
replication.

I do see DBMS_IJOB running (due to job_queue_processes being set) but that 
wasn't a PeopleSoft thing. As a general rule PeopleSoft avoids DBMS specific

features (hence no triggers, sequences, RI features.

David Davis

>From: "Koivu, Lisa" <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], 
>[EMAIL PROTECTED]
>Subject: PS question
>Date: Fri, 31 Jan 2003 11:17:08 -0500
>
>Good morning, my esteemed friends,
>
>If you have a moment to answer a question, I would be most grateful.  If 
>you
>don't, that's OK, delete away :)
>
>What does Peoplesoft use DBMS_JOB for?  Do you know?  The previous DBA
>obviously has no clue.
>
>Thanks
>
>Lisa Koivu
>Oracle Database Administrator
>Fairfield Resorts, Inc.
>5259 Coconut Creek Parkway
>Ft. Lauderdale, FL, USA  33063
>Office: 954-935-4117
>Fax:954-935-3639
>Cell:954-309-4157
>


_
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: david davis
  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).

__

Disclaimer and confidentiality note


Everything in this e-mail and any attachments relating to the official business of 
Standard Bank Group Limited is proprietary to the company. It is confidential, legally 
privileged and protected by law. Standard Bank does not own and endorse any other 
content. 
Views and opinions are those of the sender unless clearly stated as being that of 
Standard Bank. 

The person addressed in the e-mail is the sole authorised recipient. Please notify the 
sender 
immediately if it has unintentionally reached you and do not read, disclose or use the 
content
in any way. 

Standard Bank can not assure that the integrity of this communication has been 
maintained nor 
that it is free of errors, virus, interception or interference.

__
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dawtrey, Lindsay L
  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).

RE: index hint ignored?

[Janardhana Babu Donga]

Title: Message



Try 
Analyzing the table first and issue the select stmt.
 
-- 
Babu

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  11:56 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: index hint ignored?
  I've 
  tried it both ways, with the comma and without - same result. 
  
   
   
  
  -Original Message-From: K 
  Gopalakrishnan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 
  31, 2003 2:13 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: index hint ignored?
  
Hi,
 
THe syntax is incorrect. Incorrect HINTs are treated as 
comments.
replace the comma with blank space and your hint will 
work
as 
expected.
 
Best Regards,K Gopalakrishnan

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, 
  GeraldSent: Friday, January 31, 2003 10:31 AMTo: 
  Multiple recipients of list ORACLE-LSubject: index hint 
  ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to 
  use the index - but it always does a FTS. Why? (I've tried it with 
  and without the alias)
   
   
  SQL> set autotrace traceonlySQL> SELECT /*+ 
  INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year  2  FROM 
  TIREADVISOR.vehicle A  3  ORDER BY veh_year 
  DESC;
   
  20 
  rows selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE 
  (Cost=118 Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 
  Card=20 Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER 
  = 'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

RE: ReInitiate Sequence Number

[Deshpande, Kirti]

Please post your test... 

- Kirti

-Original Message-
Sent: Friday, January 31, 2003 1:43 PM
To: Multiple recipients of list ORACLE-L


Kirti,

I test it it doesn't work.


-Original Message-
Sent: Friday, January 31, 2003 11:09 AM
To: [EMAIL PROTECTED]
Cc: Hamid Alavi


Hamid,

Here is one way:

SQL> select myseq.currval from dual;

   CURRVAL
--
 4

SQL> alter sequence myseq nocache;

Sequence altered.

SQL> alter sequence myseq maxvalue 4 cycle;

Sequence altered.

SQL> select myseq.nextval from dual;

   NEXTVAL
--
 1

SQL> REM ---> Other alter sequence commands to change maxvalue, cache,
nocycle etc. 
   
Now, you get to do the tricky part of automating it ;) Because, I have not
attempted to do it, yet. 

HTH,

- Kirti


-Original Message-
Sent: Friday, January 31, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).







=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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: Deshpande, Kirti
  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).

RE: RMAN - problems restoring to clone server

[Gary Jackson]

Jay,
 I'm confused. Based on what you said I would want to connect my target to 
my production db and my catalog to my dbcat/tapeserver. If I understand your 
suggestion correctly, how would RMAN know to recover to the new clone 
database?
-gary






From: "Jay Hostetter" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: RMAN - problems restoring to clone server
Date: Fri, 31 Jan 2003 11:21:04 -0800

Gary,

  Why are you trying to create the RMAN schema?  You could be connecting 
to the RMAN database that was used for making the backup.  To clone a 
database, your target should be your production db, your catalog should be 
the RMAN catalog that was used for the backup, your clone should be 
auxiliary.  e.g.:  rman  uname/password@prod target uname/password@rmandb 
auxiliary /  (assuming that your environment is set for the auxiliary 
database - SID, ORACLE_HOME, etc.).
  I only use RMAN for backing up to disk, so I can' t help with the MML 
portion (media management layer).

Jay

>>> [EMAIL PROTECTED] 01/31/03 12:20PM >>>
Can you please clarify what you mean by "linking the MML to Oracle"? I am
unable to create the RMAN schema,table,and views on the 'to-be-clone'
database yet since it is just a non-mounted instance at this point in time.

I assumed I would connect to rman as a rcvcat on the server with the dbcat
database and the legato tape device. Then connect as the target to the
to-be-clone non-mounted instance. Is this correct?

Then in the restore rman script I use:
NSR_SERVER as the tape device/dbcat server
NSR_CLIENT as the to-be-clone target server

Is this correct?

Thanks in advance!
-Gary





**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the 
use of the individual or entity to which they are addressed and may contain 
information that is privileged, proprietary and confidential. If you are 
not the intended recipient, you may not use, copy or disclose to anyone the 
message or any information contained in the message. If you have received 
this communication in error, please notify the sender and delete this 
e-mail message. The contents do not represent the opinion of D&E except to 
the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
  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).


_
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
 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).

session idle time

[Sarnowski, Chris]

Oracle 8.1.7.2 on Solaris 8.
I'm looking for a way to see how long a session has been idle or whether it's done any 
work. I've just been looking at v$sesstat, specifically 'session connect time' and 
'process last non-idle time'. But every time I've queried these numbers, they were the 
same for each SID except SMON (that is, for a given SID, except the SID assiociated 
with SMON, the 2 numbers are the same). So they must not measure what I guessed they 
measure.

So the immediate question is, are these statistics useful for anything? 

The actual problem I'm trying to solve is, we are using a connection pooling method 
for Java that seems to allocate far more connections than it ever uses, and I am 
trying to find a way to document what is actually going on with these connections;
i.e. whether some are never used, and how often connections are reused.

thanks for any help, and sorry for the legal goop at the end.

-Chris
-- 


LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. 
It is intended for the addressee(s) only. Access to this e-mail by anyone else is 
unauthorized. If you are not an addressee, any disclosure or copying of the contents 
or any action taken (or not taken) in reliance on it is unauthorized and may be 
unlawful. If you are not an addressee, please inform the sender immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  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).

RE: ReInitiate Sequence Number

[Hamid Alavi]

Kirti,

I test it but it doesn't work!

-Original Message-
Sent: Friday, January 31, 2003 11:13 AM
To: Multiple recipients of list ORACLE-L


Hamid,

Here is one way:

SQL> select myseq.currval from dual;

   CURRVAL
--
 4

SQL> alter sequence myseq nocache;

Sequence altered.

SQL> alter sequence myseq maxvalue 4 cycle;

Sequence altered.

SQL> select myseq.nextval from dual;

   NEXTVAL
--
 1

SQL> REM ---> Other alter sequence commands to change maxvalue, cache,
nocycle etc. 
   
Now, you get to do the tricky part of automating it ;) Because, I have not
attempted to do it, yet. 

HTH,

- Kirti


-Original Message-
Sent: Friday, January 31, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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: Deshpande, Kirti
  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).





=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).

RE: ReInitiate Sequence Number

[Hamid Alavi]

Kirti,

I test it it doesn't work.


-Original Message-
Sent: Friday, January 31, 2003 11:09 AM
To: [EMAIL PROTECTED]
Cc: Hamid Alavi


Hamid,

Here is one way:

SQL> select myseq.currval from dual;

   CURRVAL
--
 4

SQL> alter sequence myseq nocache;

Sequence altered.

SQL> alter sequence myseq maxvalue 4 cycle;

Sequence altered.

SQL> select myseq.nextval from dual;

   NEXTVAL
--
 1

SQL> REM ---> Other alter sequence commands to change maxvalue, cache,
nocycle etc. 
   
Now, you get to do the tricky part of automating it ;) Because, I have not
attempted to do it, yet. 

HTH,

- Kirti


-Original Message-
Sent: Friday, January 31, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).







=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).

RE: index hint ignored?

[Cunningham, Gerald]

Title: Message



Hi 
Dan,
 
H. 
That's interesting. Yes, the column does allow nulls. Not sure if there actually 
are any null values, I'll have to check...
 
Thanks!
 
- 
Jerry

  
  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 2:31 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  index hint ignored?
  Gerald,
      Is veh_year a nullable column? If so, an index lookup may 
  give you incorrect results (nulls are not indexed), so a full table scan will 
  be used.
      Of course, is a full table scan a bad choice? Don't worry 
  about the access path, find the plan that incurs the fewest I/Os and returns 
  the quickest.
   
  Dan 
  Fink
  
-Original Message-From: Cunningham, Gerald 
[mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
11:31 AMTo: Multiple recipients of list 
ORACLE-LSubject: index hint ignored?
Hi 
there.
 
I have a 
non-unique index on a table, and I'm trying to force Oracle to use the 
index - but it always does a FTS. Why? (I've tried it with and 
without the alias)
 
 
SQL> 
set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
*/ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
3  ORDER BY veh_year DESC;
 
20 rows 
selected.
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
Card=20 Bytes=80)
   1    
0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
2    1 SORT (UNIQUE) (Cost=67 Card=20 
Bytes=80)   3    
2   TABLE ACCESS (FULL) OF 'VEHICLE' 
(Cost=16 Card=19607 Bytes=78428)
 
 
===
 
 
select TABLE_NAME, INDEX_NAME, 
COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
1,2,4,3TABLE_NAME 
INDEX_NAME-- 
--COLUMN_NAME  
COLUMN_POSITION 
---VEHICLE    
VEHICLE_PKVEH_ID 
1
 
VEHICLE    
VEHICLE_VEH_YEAR_INDXVEH_YEAR   
1
 
 
 
Thanks for any 
help!
 
- 
Jerry

RE: index hint ignored?

[Cunningham, Gerald]

Title: Message



I've 
tried it both ways, with the comma and without - same result. 

 
 

-Original Message-From: K 
Gopalakrishnan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 
2003 2:13 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: index hint ignored?

  Hi,
   
  THe 
  syntax is incorrect. Incorrect HINTs are treated as 
  comments.
  replace the comma with blank space and your hint will 
  work
  as 
  expected.
   
  Best Regards,K Gopalakrishnan
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, 
GeraldSent: Friday, January 31, 2003 10:31 AMTo: 
Multiple recipients of list ORACLE-LSubject: index hint 
ignored?
Hi 
there.
 
I have a 
non-unique index on a table, and I'm trying to force Oracle to use the 
index - but it always does a FTS. Why? (I've tried it with and 
without the alias)
 
 
SQL> 
set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
*/ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
3  ORDER BY veh_year DESC;
 
20 rows 
selected.
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
Card=20 Bytes=80)
   1    
0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
2    1 SORT (UNIQUE) (Cost=67 Card=20 
Bytes=80)   3    
2   TABLE ACCESS (FULL) OF 'VEHICLE' 
(Cost=16 Card=19607 Bytes=78428)
 
 
===
 
 
select TABLE_NAME, INDEX_NAME, 
COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
1,2,4,3TABLE_NAME 
INDEX_NAME-- 
--COLUMN_NAME  
COLUMN_POSITION 
---VEHICLE    
VEHICLE_PKVEH_ID 
1
 
VEHICLE    
VEHICLE_VEH_YEAR_INDXVEH_YEAR   
1
 
 
 
Thanks for any 
help!
 
- 
Jerry

RE: Oracle 101 Performance Tuning comes to the rescue again!

[Post, Ethan]

Tom, would a monitor in place have notified the admins as to a locking 
issue?  I usually run Steve Adam's enqueue.sql script to find locked 
objects.  Also have monitors in place that generate an email when locked 
exceed a specific time.  Pretty typical for me to call an app dev and ask 
him if he forgot to issue a commit, which is usually the case.  By the way 
I second the book recommend.  I went to once of Gaja's presentations and 
got the gist of the methodology but when I bought the book I was able to 
actually absorb it.

  -Original Message-From: Mercadante, Thomas F 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  11:52 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Oracle 101 Performance Tuning comes to the rescue again!
  All,
   
  you 
  *MUST* buy this book.
   
  I 
  just got called over by the Warehouse people.  Their database was 
  hung.  We could log-on ok, but certain queries would 
  hang.
   
  Ran 
  the four "wait-state" queries and saw that two queries were hung on library 
  cache.  the two queries were an analyze table and a MV refresh - using 
  the same table.  hung them both out to dry.
   
  killed the analyze and the MV started up again.
   
  great book.  solves all problems.  great job Gaja, Kirti and 
  John.  you guys do the work, and I look like a hero.
   
  thanks again.
   
  Tom Mercadante Oracle Certified Professional 
   

RE: index hint ignored?

[Koivu, Lisa]

Title: Message



Bah.  You are right.  I see the cardinality 
down there - 19K + records.  What on earth am I thinking.  
<>
 
Have 
you tried adding an index on both of those columns?  I'll betcha it will 
use a concatenated index to avoid the extra I/O.  
 
Just a 
thought for a braindead Friday. 
 
LK

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  2:32 PMTo: Koivu, Lisa; [EMAIL PROTECTED]Subject: RE: 
  index hint ignored?
  Thanks, Lisa.
   
  This 
  particular table is rather small (~20,000 rows, 1.3 Mb in size, 20 distinct 
  values for VEH_YEAR) - so it may be faster doing a FTS scan, not sure. I was 
  mainly wondering why my hint was ignored. It's a WebSphere app (The person who 
  wrote it is long gone!) where this statement gets executed thousands of times 
  a day, so if I could shave some time off the query it may 
  help... 
   
   
  Thanks again!
   
  - 
  Jerry
  

-Original Message-From: Koivu, Lisa 
[mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 
1:56 PMTo: [EMAIL PROTECTED]; Cunningham, 
GeraldSubject: RE: index hint ignored?
Hi 
Jerry, 
 
Methinks it's because this is a small table.  
20 records?  Peanuts.  Why bother with the 
index.
 
On 
the same token you should probably not spend a lot of time worrying about 
this...  unless this is just a learning exercise :)
 
hth

Lisa Koivu 
Oracle Diaper 
Administrator Fairfield Resorts, 
Inc. 5259 Coconut Creek Parkway 
Ft. Lauderdale, FL, USA  33063 

 
 

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 
  2003 1:31 PMTo: Multiple recipients of list 
  ORACLE-LSubject: index hint ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to 
  use the index - but it always does a FTS. Why? (I've tried it with 
  and without the alias)
   
   
  SQL> set autotrace traceonlySQL> SELECT /*+ 
  INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year  2  FROM 
  TIREADVISOR.vehicle A  3  ORDER BY veh_year 
  DESC;
   
  20 
  rows selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE 
  (Cost=118 Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 
  Card=20 Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER 
  = 'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

Re: Oracle, Siebel and rule-based optimization

[Ferenc Mantfeld]

Replies below.
Ferenc MantfeldDreaming costs you 
nothing. Not dreaming costs you everything.

  - Original Message - 
  Still, some of the queries in Siebel SEEM to run better using CBO.  
  
  Ferenc: No doubt this is true, but overall the entire suite comes to a 
  grinding halt for reasons in your next statement.
   
  Is there any way to mix and match approaches?  
  Ferenc: A rule of thumb I give myself is that if Tony Aponte says 
  something, I sit up and listen, he is one of the best Oracle DBA's I have yet 
  met. I see a post here about plan stability. I think Tony has the only 
  'approved' partitioned table amongst Siebel customers, and the way he did it 
  was just so slick, it HAD to be approved, and as you know, partitioned tables 
  are a cost-based feature. Thoug I must say that wih query plan stability, all 
  we are really doing in CBO is forcing RBO hints, well kind of, you know what I 
  mean.
   
  To use RBO by default, but perhaps CBO might make sense for certain 
  queries, and perhaps some 
   AD-HOC?
  If you are talking about ad-hoc queries as in sql*PLus, I don't see any 
  harm in experimentation. If you mean a user using the application to generate 
  the ad-hoc query from hell, I don't need to state the repercussions on this 
  list, we've all been there, and of course it is always us stupid DBA's who 
  can't configure their databases properly to meet the impossible demands of a 
  user who sees all databases equal. Nuff sed !We've got consultants 
  who've told me that Siebel 'abuses' Oracle software init's excessive use 
  of outer joins, that Siebel defiles the notions of proper relationaldesign 
  with 200+ column tables, and otherwise does not "play nice" with thenormal 
  rules of relational database management as implemented in Oracle 9i.  
  
  Ferenc: Yes, there are outer joins everywhere, and this is handled so 
  well by RBO. 200+ columns ? Try 1000+ columns. There are things called 
  extension tables, which were meant for holding the physical dimension of the 
  1:M relationships, but the flexibility of the application has allowed these to 
  be used everywhere. Try keying a query on a large entity off an extension 
  table and all hell breaks loose, see my write-up for details.
  Normal rules of rdbms ? There is such a thing ? Just like SAP and PSOFT, 
  all RI is in the applicatin repository, and contained in the data tables. 
  There are no PK or FK constraints, whcih woul make a migration towards CBO 
  somewhat easier, but as it happens, the DB independence crusade states that 
  DB2 and SQL Server do not implement PK and FK in the same way that Oracle does 
  (wow, Sherlock Holmes must have been here), so therefore it cannot, will not 
  be used.I've also heard that Siebel runs better with DB2 and 
  SQLServer, which may be options for us.Ferenc: Not 
  necessarily. Siebel's largest customer is IBM, nothing confidential about 
  that. Also no secret that IBM uses DB2. Yet, there are several customers 
  around the world that run several thousand (I have encountered 6,000 myself) 
  concurrent users on Siebel, and it works fine. My point is that the larger the 
  implementation, the larger your tables, the less of a mercy factor there is, 
  especially for those ad-hoc queries.
Hope that was helpful.
  

RE: index hint ignored?

[Cunningham, Gerald]

Title: Message



Thanks, Lisa.
 
This 
particular table is rather small (~20,000 rows, 1.3 Mb in size, 20 distinct 
values for VEH_YEAR) - so it may be faster doing a FTS scan, not sure. I was 
mainly wondering why my hint was ignored. It's a WebSphere app (The person who 
wrote it is long gone!) where this statement gets executed thousands of times a 
day, so if I could shave some time off the query it may 
help... 
 
 
Thanks 
again!
 
- 
Jerry

  
  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 
  1:56 PMTo: [EMAIL PROTECTED]; Cunningham, 
  GeraldSubject: RE: index hint ignored?
  Hi 
  Jerry, 
   
  Methinks it's because this is a small table.  20 
  records?  Peanuts.  Why bother with the index.
   
  On 
  the same token you should probably not spend a lot of time worrying about 
  this...  unless this is just a learning exercise :)
   
  hth
  
  Lisa Koivu 
  Oracle Diaper 
  Administrator Fairfield Resorts, 
  Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA  33063 
  
   
   
  
-Original Message-From: Cunningham, Gerald 
[mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
1:31 PMTo: Multiple recipients of list 
ORACLE-LSubject: index hint ignored?
Hi 
there.
 
I have a 
non-unique index on a table, and I'm trying to force Oracle to use the 
index - but it always does a FTS. Why? (I've tried it with and 
without the alias)
 
 
SQL> 
set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
*/ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
3  ORDER BY veh_year DESC;
 
20 rows 
selected.
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
Card=20 Bytes=80)
   1    
0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
2    1 SORT (UNIQUE) (Cost=67 Card=20 
Bytes=80)   3    
2   TABLE ACCESS (FULL) OF 'VEHICLE' 
(Cost=16 Card=19607 Bytes=78428)
 
 
===
 
 
select TABLE_NAME, INDEX_NAME, 
COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
1,2,4,3TABLE_NAME 
INDEX_NAME-- 
--COLUMN_NAME  
COLUMN_POSITION 
---VEHICLE    
VEHICLE_PKVEH_ID 
1
 
VEHICLE    
VEHICLE_VEH_YEAR_INDXVEH_YEAR   
1
 
 
 
Thanks for any 
help!
 
- 
Jerry

RE: ReInitiate Sequence Number

[Deshpande, Kirti]

Hamid,

Here is one way:

SQL> select myseq.currval from dual;

   CURRVAL
--
 4

SQL> alter sequence myseq nocache;

Sequence altered.

SQL> alter sequence myseq maxvalue 4 cycle;

Sequence altered.

SQL> select myseq.nextval from dual;

   NEXTVAL
--
 1

SQL> REM ---> Other alter sequence commands to change maxvalue, cache, nocycle etc. 
   
Now, you get to do the tricky part of automating it ;) Because, I have not attempted 
to do it, yet. 

HTH,

- Kirti


-Original Message-
Sent: Friday, January 31, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L


Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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: Deshpande, Kirti
  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).

RE: index hint ignored?

[Fink, Dan]

Title: Message



Gerald,
    Is veh_year a nullable column? If so, an index lookup may 
give you incorrect results (nulls are not indexed), so a full table scan will be 
used.
    Of course, is a full table scan a bad choice? Don't worry 
about the access path, find the plan that incurs the fewest I/Os and returns the 
quickest.
 
Dan 
Fink

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  11:31 AMTo: Multiple recipients of list ORACLE-LSubject: 
  index hint ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to use the 
  index - but it always does a FTS. Why? (I've tried it with and 
  without the alias)
   
   
  SQL> 
  set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
  */ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
  3  ORDER BY veh_year DESC;
   
  20 rows 
  selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
  Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 Card=20 
  Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
  'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

RE: RMAN - problems restoring to clone server

[Jay Hostetter]

Gary,

  Why are you trying to create the RMAN schema?  You could be connecting to the RMAN 
database that was used for making the backup.  To clone a database, your target should 
be your production db, your catalog should be the RMAN catalog that was used for the 
backup, your clone should be auxiliary.  e.g.:  rman  uname/password@prod target 
uname/password@rmandb auxiliary /  (assuming that your environment is set for the 
auxiliary database - SID, ORACLE_HOME, etc.).
  I only use RMAN for backing up to disk, so I can' t help with the MML portion (media 
management layer).

Jay

>>> [EMAIL PROTECTED] 01/31/03 12:20PM >>>
Can you please clarify what you mean by "linking the MML to Oracle"? I am 
unable to create the RMAN schema,table,and views on the 'to-be-clone' 
database yet since it is just a non-mounted instance at this point in time.

I assumed I would connect to rman as a rcvcat on the server with the dbcat 
database and the legato tape device. Then connect as the target to the 
to-be-clone non-mounted instance. Is this correct?

Then in the restore rman script I use:
NSR_SERVER as the tape device/dbcat server
NSR_CLIENT as the to-be-clone target server

Is this correct?

Thanks in advance!
-Gary





**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Hostetter
  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).

RE: index hint ignored?

[K Gopalakrishnan]

Title: Message



Hi,
 
THe 
syntax is incorrect. Incorrect HINTs are treated as 
comments.
replace the comma with blank space and your hint will 
work
as 
expected.
 
Best Regards,K Gopalakrishnan

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, 
  GeraldSent: Friday, January 31, 2003 10:31 AMTo: 
  Multiple recipients of list ORACLE-LSubject: index hint 
  ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to use the 
  index - but it always does a FTS. Why? (I've tried it with and 
  without the alias)
   
   
  SQL> 
  set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
  */ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
  3  ORDER BY veh_year DESC;
   
  20 rows 
  selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
  Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 Card=20 
  Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
  'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

RE: Excessive library cache latch contention

[Johnson, Michael]

Harvey,

Personally, I would go right to the user(s) who has complained
about the slowdown and have them run the application at the peak
hour/period where things seem very slow.

Set a 10046 event level 8 trace on that session(s) after they
log on and then take a look at the trace file in
the udump area after they are finished executing
the queries.   

There should be some strong clues in there about
why the session is the waiting. 

My bet is on some poorly written SQL and these session
competing for blocks.  

Check out Oracle Performance Tuning 101 Book and you
can send your 10046 event data to www.hotsos.com for review
if you are confused by it

fwiw.  good luck.  mike

-Original Message-
Sent: Friday, January 31, 2003 3:09 AM
To: Multiple recipients of list ORACLE-L


We've got about 30 sites all running the same application, and I'm
consistently seeing large numbers of 106 (library cache) latch free waits.
They tend to happen at peak times during the day, and in the worst case I
saw 12 sessions all on a 106 latch free wait event, spread across 3 P1RAW
addresses.

Running Steve Adams latch_sleeps scripts, yields the following:

LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING
LEVEL
- --- -- -
-
library cache 1281502  0.11%   2399666
5
cache buffers chains   273556  0.00% 23049
1
shared pool 73893  0.04% 91633
7
cache buffers lru chain 12236  0.01% 70756
3
session allocation  10639  0.06% 19969
5
row cache objects7835  0.00% 29816
4
cache buffer handles 3646  0.00%  2575
3
transaction allocation   2344  0.01%  4341
8
enqueue hash chains  1831  0.01% 13722
4
redo writing  778  0.01% 17328
5
session idle bit  714  0.00% 0
1

The results above are from an instance which has been up for 5 days

As you can see, library cache latch has a big impact (though I must admit,
I'm not sure what Steve's IMPACT formula actually tells me). When I check
across other sites, I see a similar pattern - large numbers of 106 latch
misses and sleeps.

I guess what I'd like to know is where these latches are happening, which
objects / cursors etc are causing the contention. I've grappled with SQL
against x$kglob, trying to join back to the P1RAW but am not getting very
far.

Any ideas?

TIA.

Neil.



--
This correspondence is confidential and is solely for the intended
recipient(s). If you are not the intended recipient, you must not use,
disclose, copy, distribute or retain this message or any part of it. If you
are not the intended recipient please delete this correspondence from your
system and notify the sender immediately. 

No warranty is given that this correspondence is free from any virus. In
keeping with good computer practice, you should ensure that it is actually
virus free. E-mail messages may be subject to delays, non-delivery and
unauthorised alterations therefore, information expressed in this message is
not given or endorsed by Sx3 unless otherwise notified by our duly
authorised representative independent of this message.

Sx3 is a trading name of Service and Systems Solutions Limited, a limited
company registered in Northern Ireland under number NI 32979 whose
registered office is at 120, Malone Road, Belfast, BT9 5HT.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Harvey Neil
  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: Johnson, Michael 
  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 subscribin

RE: Oracle 101 Performance Tuning comes to the rescue again!

[Mercadante, Thomas F]

Kirti,
 
well, 
maybe it doesn't solve the problem of my forehead getting higher and higher 
every day, but the technique described in your book is always helpful in 
determining where problems exist in the system.
 
thanks 
again
 
Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Deshpande, Kirti 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  1:53 PMTo: [EMAIL PROTECTED]Cc: 
  [EMAIL PROTECTED]Subject: RE: Oracle 101 Performance Tuning 
  comes to the rescue again!
  Tom,
   
   Thanks a lot.
   
   I am glad to read that the book is helping 
  you.
   
   I am not so sure about ".. solves all problems", though ;) 
  
   
   Regards,
   
  - 
  Kirti
   
   
  -Original Message-From: Mercadante, Thomas F 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  11:52 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Oracle 101 Performance Tuning comes to the rescue again!
  All,
   
  you 
  *MUST* buy this book.
   
  I 
  just got called over by the Warehouse people.  Their database was 
  hung.  We could log-on ok, but certain queries would 
  hang.
   
  Ran 
  the four "wait-state" queries and saw that two queries were hung on library 
  cache.  the two queries were an analyze table and a MV refresh - using 
  the same table.  hung them both out to dry.
   
  killed the analyze and the MV started up again.
   
  great book.  solves all problems.  great job Gaja, Kirti and 
  John.  you guys do the work, and I look like a hero.
   
  thanks again.
   
  Tom Mercadante Oracle Certified Professional 
   

RE: index hint ignored?

[Koivu, Lisa]

Title: Message



Hi 
Jerry, 
 
Methinks it's because this is a small table.  20 
records?  Peanuts.  Why bother with the index.
 
On the 
same token you should probably not spend a lot of time worrying about 
this...  unless this is just a learning exercise :)
 
hth

Lisa Koivu 
Oracle Diaper 
Administrator Fairfield Resorts, Inc. 
5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA  33063 

 
 

  -Original Message-From: Cunningham, Gerald 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 
  1:31 PMTo: Multiple recipients of list ORACLE-LSubject: 
  index hint ignored?
  Hi 
  there.
   
  I have a 
  non-unique index on a table, and I'm trying to force Oracle to use the 
  index - but it always does a FTS. Why? (I've tried it with and 
  without the alias)
   
   
  SQL> 
  set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) 
  */ DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  
  3  ORDER BY veh_year DESC;
   
  20 rows 
  selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
  Card=20 Bytes=80)
     1    
  0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
  2    1 SORT (UNIQUE) (Cost=67 Card=20 
  Bytes=80)   3    
  2   TABLE ACCESS (FULL) OF 'VEHICLE' 
  (Cost=16 Card=19607 Bytes=78428)
   
   
  ===
   
   
  select TABLE_NAME, INDEX_NAME, 
  COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
  'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
  1,2,4,3TABLE_NAME 
  INDEX_NAME-- 
  --COLUMN_NAME  
  COLUMN_POSITION 
  ---VEHICLE    
  VEHICLE_PKVEH_ID 
  1
   
  VEHICLE    
  VEHICLE_VEH_YEAR_INDXVEH_YEAR   
  1
   
   
   
  Thanks for any 
  help!
   
  - 
  Jerry

RE: RMAN - problems restoring to clone server

[Janardhana Babu Donga]

Contd

It looks to me that you are trying to restore to Ariel from Delenn (where
catalog database resides). You must log in to the Ariel Box, switch to the
DB using "oraenv", then connect to DB on Ariel( which is in nomount state)
and the catalog DB (Delenn) as: 
 rman target / catalog rman/rman@.
then follow the instructions below. Make sure you have permission to restore
to ARIEL from the backup taken by BRAIN. 

-- Babu


I do this many times with Veritas NetBackup. The steps may be similar for
legato with slight variation. 

[1] By Default Netbackup or Legato restores files only to the client from
which the files were backed up(Brain). Your Netbackup administrator should
configure to let you restore to an alternate client(Ariel). There may be
similar steps for Legato.

[2] Startup nomount

[3] run {
  allocate channel t1 type 'SBT_TAPE' 
parms="ENV=(NB_ORA_CLIENT=brain..com; export
NB_ORA_CLIENT)";
  restore controlfile;
  alter database mount;
}

Look for similar parameter for Legato, may be NSR_CLIENT in place of
NB_ORA_CLIENT.

[4] run {
  allocate channel t1 type 'SBT_TAPE' 
parms="ENV=(NB_ORA_CLIENT=brain..com; export
NB_ORA_CLIENT)";
  restore database;
}

Hope this helps.

-- Babu

-Original Message-
Sent: Friday, January 31, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L


Legato Networker version 6.1.1.Build.238
OS:  SunOS 5.6
Database: Oracle8 Enterprise Edition Release 8.0.4.4.0

TIA!






>From: Jared Still <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED], "Gary Jackson" <[EMAIL PROTECTED]>
>Subject: Re: RMAN - problems restoring to clone server
>Date: Fri, 31 Jan 2003 07:02:06 -0800
>
>
>What is your MML?
>
>Jared
>
>On Thursday 30 January 2003 11:56, Gary Jackson wrote:
> > The support analyst handling my TAR gave up, perhaps someone on here can
> > help me out...
> >
> > I have an 8.0.4.4.0 database.  I used RMAN to perform a full backup of 
>my
> > database on server A (Brain). The RMAN recovery catalog database is on
> > server B (Delenn). I am attempting to restore it to a 3rd instance which

>is
> > on server C (Ariel). On server C I created a new instance and it is in
> > startup nomount currently. From server B I am attempting to restore to
> > server C.
> >
> > run {
> > allocate channel t1  type 'SBT_TAPE' parms
> > 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> > set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf';
> > set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf';
> > set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf';
> > set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf';
> > restore database;
> > release channel t1;
> > }
> >
> >
> > 
> >
> > RMAN-10032: unhandled exception during execution of job step 1: 
>ORA-06512:
> > at line 137
> > RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry
> > possible
> > ORA-19507: failed to retrieve sequential file,
> > handle="Incr_level0_db_DB1_151_1.hot", parms=""
> > ORA-27007: failed to open file
> > Additional information: 7009
> > Additional information: 2
> > ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> > RMAN-10031: ORA-19624 occurred during call to
> > DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
> >
> >
> >
> > _
> > Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> > http://join.msn.com/?page=features/junkmail


_
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gary Jackson
  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: Janardhana Babu Donga
  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 sub

RE: Oracle 101 Performance Tuning comes to the rescue again!

[Deshpande, Kirti]

Tom,
 
 Thanks a lot.
 
 I am glad to read that the book is helping you.
 
 I am not so sure about ".. solves all problems", though ;) 

 
 Regards,
 
- 
Kirti
 
 
-Original Message-From: Mercadante, Thomas F 
[mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 11:52 
AMTo: Multiple recipients of list ORACLE-LSubject: Oracle 
101 Performance Tuning comes to the rescue again!
All,
 
you 
*MUST* buy this book.
 
I just 
got called over by the Warehouse people.  Their database was hung.  We 
could log-on ok, but certain queries would hang.
 
Ran 
the four "wait-state" queries and saw that two queries were hung on library 
cache.  the two queries were an analyze table and a MV refresh - using the 
same table.  hung them both out to dry.
 
killed 
the analyze and the MV started up again.
 
great 
book.  solves all problems.  great job Gaja, Kirti and John.  you 
guys do the work, and I look like a hero.
 
thanks 
again.
 
Tom Mercadante Oracle Certified Professional 
 

RE: RMAN - problems restoring to clone server

[Paula_Stankus]

Title: RE: RMAN - problems restoring to clone server





There really are a number of ways to do this.  Another gotcha - make sure the OS versions are the same otherwise you will have to relink the oracle software.

Go to metalink and type in NSR_SERVER and you will find a really useful note:  120188.1  "How To Restore A Database To A Second Node Using RMAN and Legato Networker" 


You will not have to create to the RMAN catalog locally.  As long as the RMAN binaries are there you will be able to run rman and connect to a remote RMAN catalog.  Of course, this implies that you have your Net8 listener and clients (tnsnames.ora) setup appropriately.  If you run:

rman and you get a prompt and can successfully do the following
connect rcvcat user/pass@databaselinkforrmancatalog 
connected successfully


Then you are using the rman catalog in a remote database (i.e. another host) - no problem.  


There are specific commands to link networker libraries to oracle libraries and you can do this relinking without the rman catalog or a database - just the oracle software and networker libraries.  

The following is create granted NSR_SERVER is where the backup was done.  Since you don't have a database or rman locally you will have to run rman with a connect command as above.

NSR_SERVER as the tape device/dbcat server
NSR_CLIENT as the to-be-clone target server


Also, you likely will have to set your dbid to restore the correct control file.


See below:


disaster recover mgm with rman


Hi, 
I simulate a disaster recover by losing all datafile, online redo and controlfile. 
The db was backed up on tape and after that only redos were. 
I remove all db files and try to restore/recover with following 


connect target /; 
connect catalog system/manager@obk; 
run { 
debug on; 
set command id to "REC_DB"; 
allocate channel ch1 type "SBT_TAPE" 
parms "ENV=(NSR_DATA_VOLUME_POOL=RC)"; 
shutdown abort; 
startup pfile=/opt/oracle/admin/instances/omc/pfile/initomc.ora nomount; 
restore controlfile; 
alter database mount; 
restore database; 
recover database; 
# Complete this last step only if no more archived logs need to be applied. 
alter database open resetlogs; 
} 


I got the error: 


EXEC SQL AT RCVCAT begin dbms_rcvman . setDatabase ( upper ( :dbname:dbname_i ) , :rlscn , :rltime , :fhdbi:fhdbi_i ) ; end ; 

sqlcode=-20005 
setDatabase: db_id is null 
krmicomp: error 6004 signalled during compilation 
krmicomp: error 3002 signalled during compilation 
RMAN-00571: === 
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === 
RMAN-00571: === 
RMAN-03002: failure during compilation of command 
RMAN-03013: command type: restore 
RMAN-03002: failure during compilation of command 
RMAN-03013: command type: IRESTORE 
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous 
RMAN-06097: text of failing SQL statement: begin dbms_rcvman . setDatabase ( upper ( :dbname:dbname_i ) , :rlscn , :rltime , :fhdbi:fhdbi_i ) ; end ; 

RMAN-06099: error occurred in source file: krmk.pc, line: 9140 


Recovery Manager complete. 
krmxcd: xc=6807624 


Thanks in advance for help. 








From: HARVINDER SINGH 13-Sep-00 20:24 
Subject: Re : disaster recover mgm with rman 



Hi, 


Try to start the database in nomount from svrmgr 
and then connect with rman. 


Harvinder 









From: Pramod Gudula 14-Sep-00 05:27 
Subject: Re : disaster recover mgm with rman 



Hi, 


you have coonected to the target before starting your restore operation and same time you are trying shutdown database inside the program. i think RMAN is confused about null db name . 

you can shutdown and statrtup with nomount your target database before starting your job . 


Hope this helps. 



Pramod Gudula 
[EMAIL PROTECTED] 
[EMAIL PROTECTED] 
ColesMyer Pty Ltd. 
Australia 








From: [EMAIL PROTECTED] 14-Sep-00 10:10 
Subject: Re : disaster recover mgm with rman 



Thanks for suggestions, 
but I tried to do what you suggest before posting my mail and It didn't work. 
I try again this morning with same result. 
Bye 







From: Oracle, Reem Munakash 15-Sep-00 20:20 
Subject: Re : Re : disaster recover mgm with rman 



You need to recover the controlfile from the recovery catalog using the set dbid. 


Try the following: 


svrmgrl> connect internal 
connected 
svrmgrl> starutp nomount 
exit 
rman 
RMAN> set dbid =  
RMAN> connect target / catalog 
allocate channel(s) 
restore controlfile to 'apsolute path' 
allocate channel d1 type disk 
replicate controlfile from 'abso

RE: RMAN - problems restoring to clone server

[Janardhana Babu Donga]

I do this many times with Veritas NetBackup. The steps may be similar for
legato with slight variation. 

[1] By Default Netbackup or Legato restores files only to the client from
which the files were backed up(Brain). Your Netbackup administrator should
configure to let you restore to an alternate client(Ariel). There may be
similar steps for Legato.

[2] Startup nomount

[3] run {
  allocate channel t1 type 'SBT_TAPE' 
parms="ENV=(NB_ORA_CLIENT=brain..com; export
NB_ORA_CLIENT)";
  restore controlfile;
  alter database mount;
}

Look for similar parameter for Legato, may be NSR_CLIENT in place of
NB_ORA_CLIENT.

[4] run {
  allocate channel t1 type 'SBT_TAPE' 
parms="ENV=(NB_ORA_CLIENT=brain..com; export
NB_ORA_CLIENT)";
  restore database;
}

Hope this helps.

-- Babu

-Original Message-
Sent: Friday, January 31, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L


Legato Networker version 6.1.1.Build.238
OS:  SunOS 5.6
Database: Oracle8 Enterprise Edition Release 8.0.4.4.0

TIA!






>From: Jared Still <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED], "Gary Jackson" <[EMAIL PROTECTED]>
>Subject: Re: RMAN - problems restoring to clone server
>Date: Fri, 31 Jan 2003 07:02:06 -0800
>
>
>What is your MML?
>
>Jared
>
>On Thursday 30 January 2003 11:56, Gary Jackson wrote:
> > The support analyst handling my TAR gave up, perhaps someone on here can
> > help me out...
> >
> > I have an 8.0.4.4.0 database.  I used RMAN to perform a full backup of 
>my
> > database on server A (Brain). The RMAN recovery catalog database is on
> > server B (Delenn). I am attempting to restore it to a 3rd instance which

>is
> > on server C (Ariel). On server C I created a new instance and it is in
> > startup nomount currently. From server B I am attempting to restore to
> > server C.
> >
> > run {
> > allocate channel t1  type 'SBT_TAPE' parms
> > 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> > set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf';
> > set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf';
> > set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf';
> > set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf';
> > restore database;
> > release channel t1;
> > }
> >
> >
> > 
> >
> > RMAN-10032: unhandled exception during execution of job step 1: 
>ORA-06512:
> > at line 137
> > RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry
> > possible
> > ORA-19507: failed to retrieve sequential file,
> > handle="Incr_level0_db_DB1_151_1.hot", parms=""
> > ORA-27007: failed to open file
> > Additional information: 7009
> > Additional information: 2
> > ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> > RMAN-10031: ORA-19624 occurred during call to
> > DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
> >
> >
> >
> > _
> > Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> > http://join.msn.com/?page=features/junkmail


_
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gary Jackson
  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: Janardhana Babu Donga
  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).

hotel notice for folks considering attending IOUG

[Adams, Matthew (GECP, MABG, 088130)]

Title: hotel notice for folks considering attending IOUG





I got this in the e-mail today.
If you are considering attending the IOUG-A live 
in April, please read the below.


(I hope Jared doesn't consider too far OT)


---


Dear IOUG Member:
 
Please be aware that an organization which has no affiliation with 
the IOUG is contacting our members and representing themselves 
as part of our organization - for the purpose of selling hotel 
rooms for this year's Live! event in Orlando.
 
This company has no affiliation with the IOUG and we do not 
endorse their activity.  We strongly recommend that you do not 
do business with them, should they contact you directly.
 
Our attorney is contacting this organization to notify them to 
cease this activity. If you have any questions, comments or
 concerns, please contact our headquarters staff at +1.312.245.1579.


Many thanks.

index hint ignored?

[Cunningham, Gerald]

Title: Message



Hi 
there.
 
I have a non-unique 
index on a table, and I'm trying to force Oracle to use the index - but it 
always does a FTS. Why? (I've tried it with and without the 
alias)
 
 
SQL> set 
autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) */ 
DISTINCT veh_year  2  FROM TIREADVISOR.vehicle A  3  
ORDER BY veh_year DESC;
 
20 rows 
selected.
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=118 
Card=20 Bytes=80)
   1    
0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)   
2    1 SORT (UNIQUE) (Cost=67 Card=20 
Bytes=80)   3    
2   TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=16 
Card=19607 Bytes=78428)
 
 
===
 
 
select TABLE_NAME, INDEX_NAME, COLUMN_NAME, 
COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 
'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 
1,2,4,3TABLE_NAME 
INDEX_NAME-- 
--COLUMN_NAME  
COLUMN_POSITION 
---VEHICLE    
VEHICLE_PKVEH_ID 
1
 
VEHICLE    
VEHICLE_VEH_YEAR_INDXVEH_YEAR   
1
 
 
 
Thanks for any 
help!
 
- 
Jerry

ReInitiate Sequence Number

[Hamid Alavi]

Dear List,

How can I reinitiate a sequence Instead or dropping & recreating it, I want
every night my sequence number reinitiate and start from 1 again.
Thanks for your help.


Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hamid Alavi
  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).

RE: The lightbulb goes on - WAS-Debate on rc commands Solaris and

[Mercadante, Thomas F]

Dave,

change the last shutdown to shutdown immediate.

you are waiting because the Oracle Agent re-connected, and a shutdown normal
will wait for it to disconnect.  By oracle Agent, I mean the DBSNMP session
(started from lsnrctl).

hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, January 31, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L
and Oracle


Ok, so I changed my cold backup script lastnight so it does this;

connect internal/amianidiot
shutdown abort
startup restrict
shutdown normal
exit

It then does the abort and the startup and then the shutdown normal but then
I get this and my database hangs;

Shutting down instance (normal)
License high water mark = 10
Fri Jan 31 03:05:35 2003
SHUTDOWN: waiting for logins to complete.

So now I was totally ready to go postal, but then rational thinking
prevailed so I RTFMed.  I then come across this in the FM;

Typically, all users with the CREATE SESSION system privilege can connect to
an open database. Opening a database in restricted mode allows database
access only to users with both the CREATE SESSION and RESTRICTED SESSION
system privilege; only database administrators should have the RESTRICTED
SESSION system privilege

So my question is, should all users be created with the system privilege of
CREATE SESSION and only I get the RESTRICTED SESSION in addition?  Am I
reading this correctly that for the STARTUP RESTRICT to work all users need
to have the CREATE SESSION privilege?  We have some third party replication
software that is trying to make a connection to Oracle every 5 seconds and I
think this was the culprit.  It DID NOT have the CREATE SESSION but it does
now so I am curious as to if the database will shutdown tonight.  Did I
actually learn something??

Thanks,

Dave(iamanidiot)  :o)

-Original Message-
Sent: Thursday, January 30, 2003 7:15 AM
To: Multiple recipients of list ORACLE-L



I'll echo that sentiment.

'shutdown abort', 'startup restrict' was a regular part of my
shutdown scripts beginning in 1994 with 7.0.16, as 
'shutdown immediate' wasn't all that reliable, even in situations
where it should have worked.

Jared

On Wednesday 29 January 2003 15:53, John Kanagaraj wrote:
> Rao,
>
> And where did you read that 'shutdown abort' is not recommended? This is
> another myth that has been busted a while ago. A shutdown abort followed
by
> a startup restrict and a normal shutdown is the way to go when dealing
with
> rogue sessions that open a connection and never shutdown. In such cases, a
> shutdown immediate will _never_ return (certainly not within your 5 to 10
> minutes). I have been using this method for more than 8 years now -
> starting at 7.0.16 fyi. The trick in this case is to script it into the rc
> commands.
>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002
>
> I don't know what the future holds for me, but I do know who holds my
> future!
>
> ** The opinions and statements above are entirely my own and not those of
> my employer or clients **
>
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Paula,
>
> Shutdown abort is not recommended as the file checkpointing is not done
> during shutdown abort.  If you need to perform shutdown abort, then, it is
> preferred to bring up the db with startup restrict (so that the users
> wouldn't connect) and then, cleanly shutdown the db and bring it up again.
>
> Tell to your sys admins. that shutdown immediate would take some time
> (about 5 to 10 minutes) depending on the activity on your db.  They would
> have to wait for that much time before calling a DBA during system boots.
>
> Rao
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> System Administrator says he doesn't trust that the rc commands will stop
> if the database doesn't want to shutdown and even if it does would want to
> shutdown with scripts beforehand so that a DBA could connect and resolve
> the issue.  Other DBA says this is all wrong and rc commands should
include
> shutdown immediate of database.  In the past I had setup 2 processes in
the
> system scripts for the sys admin - shutdown immediate - wait  shutdown
> abort - on a read-only DSS system which of course allows some room for
this
> type of activity.  I kind of would want to know if a database was going to
> be shutdown with an abort esp. in OLTP system and do it myself.
>
>
>
> - any ideas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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 

OT Posts vs HTML posts

[Dave Morgan]

Hi All, Jared,

I find it funny that complaints about offtopic posts 
are effective (move to the OT list, which I refuse to join 
because of the hoster's privacy policy), however, complaints 
about HTML in emails are ignored. A recent Oracle-l
digest was 1.2M when it arrived at my server and only 380K 
once the HTML was stripped out. OT posts are hard on people 
who use Lotus notes, HTML is devastating to contractors who do 
60% of their work with PDA's and wireless (both WIFI and cell)

If there are to be these limits can we not move to a model 
similar to the dbi-users maillist where OT's get nicely 
flamed and HTML does not appear.

Just my $0.02 CAN ($0.013 US)

Dave

-- 
Dave Morgan
Operations Manager, Rigskills Canada
Canada's Geographical Oilfield Services Locator
http://www.rigskills.ca
[EMAIL PROTECTED]
403 399 2442
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dave Morgan
  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).

RE: RMAN - problems restoring to clone server

[Gary Jackson]

Can you please clarify what you mean by "linking the MML to Oracle"? I am 
unable to create the RMAN schema,table,and views on the 'to-be-clone' 
database yet since it is just a non-mounted instance at this point in time.

I assumed I would connect to rman as a rcvcat on the server with the dbcat 
database and the legato tape device. Then connect as the target to the 
to-be-clone non-mounted instance. Is this correct?

Then in the restore rman script I use:
NSR_SERVER as the tape device/dbcat server
NSR_CLIENT as the to-be-clone target server

Is this correct?

Thanks in advance!
-Gary








From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: RMAN - problems restoring to clone server
Date: Fri, 31 Jan 2003 08:24:44 -0800

Are you sure that you have linked your MML to Oracle RDBMS on all hosts?  I
thought NSR_SERVER needed to be set to the host on which the target was -
due to the fact that the client indexes on each database target host stores
information on the backupsets.

-Original Message-
Sent: Friday, January 31, 2003 10:05 AM
To: Multiple recipients of list ORACLE-L



What is your MML?

Jared

On Thursday 30 January 2003 11:56, Gary Jackson wrote:
> The support analyst handling my TAR gave up, perhaps someone on here can
> help me out...
>
> I have an 8.0.4.4.0 database.  I used RMAN to perform a full backup of 
my
> database on server A (Brain). The RMAN recovery catalog database is on
> server B (Delenn). I am attempting to restore it to a 3rd instance which
is
> on server C (Ariel). On server C I created a new instance and it is in
> startup nomount currently. From server B I am attempting to restore to
> server C.
>
> run {
> allocate channel t1  type 'SBT_TAPE' parms
> 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf';
> set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf';
> set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf';
> set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf';
> restore database;
> release channel t1;
> }
>
>
> 
>
> RMAN-10032: unhandled exception during execution of job step 1: 
ORA-06512:
> at line 137
> RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry
> possible
> ORA-19507: failed to retrieve sequential file,
> handle="Incr_level0_db_DB1_151_1.hot", parms=""
> ORA-27007: failed to open file
> Additional information: 7009
> Additional information: 2
> ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> RMAN-10031: ORA-19624 occurred during call to
> DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
>
>
>
> _
> Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
  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).


_
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
 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).

RE: Excessive library cache latch contention

[Stephen Lee]

After I posted this, I thought I should elaborate:
This seems to work sometimes; but we also had some problems with it and
opened a TAR.  We were told there is an unspecified bug associated with it.
I guess the conclusion is: Try it.  Maybe it will work; maybe not.

> -Original Message-
> 
> I don't know about 9, but I am pretty sure that in 8, this 
> "feature" is
> broken.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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).

Oracle 101 Performance Tuning comes to the rescue again!

[Mercadante, Thomas F]

All,
 
you 
*MUST* buy this book.
 
I just 
got called over by the Warehouse people.  Their database was hung.  We 
could log-on ok, but certain queries would hang.
 
Ran 
the four "wait-state" queries and saw that two queries were hung on library 
cache.  the two queries were an analyze table and a MV refresh - using the 
same table.  hung them both out to dry.
 
killed 
the analyze and the MV started up again.
 
great 
book.  solves all problems.  great job Gaja, Kirti and John.  you 
guys do the work, and I look like a hero.
 
thanks 
again.
 
Tom Mercadante Oracle Certified Professional 
 

RE: Middle Tier spawning sessions, possible performance issues

[Orr, Steve]

Title: RE: Middle Tier spawning sessions, possible performance issues



Anjo,
 
Actually I saw this in your (et al) famous YAPP 
paper.
 
Still 
curious,
Steve
  


  -Original Message-From: Orr, Steve Sent: 
  Thursday, January 30, 2003 10:32 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Middle Tier spawning sessions, possible 
  performance issues
  I read somewhere that for apps that constantly logon/logoff 
  (like web apps), one possible thing to do is increase the cache size for the 
  AUDSES$ sequence. Anyone done this and seen improvements? 
  Curiously, Steve Orr 
  -Original Message- From: Anjo 
  Kolk [mailto:[EMAIL PROTECTED]] 
  Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance 
  issues 
  You are kind of on the right track. The number of sessions 
  doesn't really matter. What matters is that they 
  logon/logoff all the time. That is the worst thing 
  that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will 
  logon/logoff. The symptoms that you will see. Latch 
  contention (shared pool, library cache), shared pool 
  fragmentation etc. 
  Anjo. 
  On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] 
  wrote: > I'm far more of a developer than a DBA, 
  but when someone told me this it > set off a big 
  red light in my head. > > We are using an Oracle Backend with a .net front front. One of our 
  .net > guys told me that the middle tier they are 
  using 'spawns' sessions. > > We have 2 pretty distinct skillsets here so fixing the middle tier 
  is > probably way beyond my pervue. However, they 
  told me that in a recent demo, > the performance 
  degraded overtime. Its my understanding that generally this > is caused by one of two things. > > 1. Failure to use Bind Variables... 
  we are using them everywhere. > > 2. Too many sessions. > 
  > Am I on the right track here? How much would shared 
  server mode help? This > may be an enormous issue 
  since they are expecting 250 contiguous users. > 
  Another option I tossed around was moving as much logic from the client 
  > side to the database to avoid the session spawning. I 
  know that generally > this is a good idea, but its 
  difficult when the database people are lousy > in 
  C# and the .net people our lousy in PL/SQL and SQL. > > Any opinions? 
  --  
  Anjo Kolk http://www.oraperf.com 
  
  -- Please see the official ORACLE-L 
  FAQ: http://www.orafaq.net -- 
  Author: Anjo Kolk   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). 

RE: Excessive library cache latch contention

[Stephen Lee]

I don't know about 9, but I am pretty sure that in 8, this "feature" is
broken.

> -Original Message-
> cursor_sharing=FORCE may be an option,
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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).

RE: Excessive library cache latch contention

[Harvey Neil]

Jonathan,

I think you've got a point on all three counts. I am seeing an imbalance of
gets / misses and sleeps on one of the latch children, and it does get worse
when the CPU's are being hammered.

Public synonyms could be an issue in certain areas of the application but
not all, though that should be resolved in the next release.

Thanks for your input - useful as ever!

N.

-Original Message-
Sent: 31 January 2003 16:15
To: Multiple recipients of list ORACLE-L



The 'objects on hot latches' is likely to help
only if there is a significant imbalance in the
latch activity on the latch children (as you probably
know).

The fact that 'waits holding' is rather high
may be a hint.  The most likely latches that
you would be holding whilst waiting for the
library cache are (I think) the shared pool
and the library cache.  And one of the more
likely reasons for holding (one of) the library
cache latches whilst waiting for another is
the excessive use of public synonyms in
a system with a large number of distinct
users.

The other thing to note is that the latch
pressure occurs when the system is at
peak usage.  It is possible that excessive
CPU usage is causing the latch problem -
the more CPU being used by 'other users'
the longer it is before I get a chance to get
back on the run queue and release the latch
that someone else is waiting for.  Possibly
you could 'fix' the latch problem by finding
opportunities to reduce the level of CPU
used.  The 'small number of specific forms'
may be exactly the place where there is
some relatively inefficient SQL that uses
CPU too aggressively.



Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 31 January 2003 15:55


>I've got access to Metalink and the doco you mentioned, but its too
generic.
>
>Its a forms application, but the developers are generating dynamic
where
>clauses on blocks, as well as the standard block.item notation in
>predicates, so there is a degree of literal SQL in the mix, and
multiple
>versions of SQL. I don't believe a larger shared pool will help as
there is
>a good chunk of free space there in v$sgastat.
>
>cursor_sharing=FORCE may be an option, but I'll need to test and
observe the
>results.
>
>The 106 latches we get seem to be in a small number of specific
forms, so
>I'll start with those.
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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).


--
This correspondence is confidential and is solely for the intended recipient(s). If 
you are not the intended recipient, you must not use, disclose, copy, distribute or 
retain this message or any part of it. If you are not the intended recipient please 
delete this correspondence from your system and notify the sender immediately. 

No warranty is given that this correspondence is free from any virus. In keeping with 
good computer practice, you should ensure that it is actually virus free. E-mail 
messages may be subject to delays, non-delivery and unauthorised alterations 
therefore, information expressed in this message is not given or endorsed by Sx3 
unless otherwise notified by our duly authorised representative independent of this 
message.

Sx3 is a trading name of Service and Systems Solutions Limited, a limited company 
registered in Northern Ireland under number NI 32979 whose registered office is at 
120, Malone Road, Belfast, BT9 5HT.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Harvey Neil
  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 (l

Re: PS question

[david davis]

Lisa,

I have seen no requirement for DBMS_JOB from PeopleSoft in HRMS 7.5 (or 
earlier). Though we have used it in the past ourselves when we were doing 
replication.

I do see DBMS_IJOB running (due to job_queue_processes being set) but that 
wasn't a PeopleSoft thing. As a general rule PeopleSoft avoids DBMS specific 
features (hence no triggers, sequences, RI features.

David Davis

From: "Koivu, Lisa" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], 
[EMAIL PROTECTED]
Subject: PS question
Date: Fri, 31 Jan 2003 11:17:08 -0500

Good morning, my esteemed friends,

If you have a moment to answer a question, I would be most grateful.  If 
you
don't, that's OK, delete away :)

What does Peoplesoft use DBMS_JOB for?  Do you know?  The previous DBA
obviously has no clue.

Thanks

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-309-4157



_
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: david davis
 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).

Oracle Documentation

[Bahar, Rivaldi (BBASSI-CHQ)]

Hi listers,

How to view the Oracle Documentation with the Information Navigator using 
index.htm (instead of products.htm ) ?
I have Java console enabled and Java logging enabled.  

Internet Explorer 6.0, Oracle 8.1   

-Valdi-

This e-mail, including any attachments, may include confidential and/or proprietary 
information,
and is intended for use only by the person or entity to which it is addressed.  If the 
reader of this
e-mail is not the intended recipient, or his or her authorized agent, the reader is 
hereby notified
that any dissemination, distribution, or copying of this e-mail is strictly 
prohibited.  If you have
received this e-mail in error, please notify the sender by replying to this message 
and delete
this e-mail immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bahar, Rivaldi (BBASSI-CHQ)
  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).

RE: Check for all numeric in a column

[Jamadagni, Rajendra]

Title: RE: Check for all numeric in a column





select *
  from your_table
 where replace(translate(your_column_name,'.1234567890','***'),'*') is not null
/


Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 11:46 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Check for all numeric in a column



Hi Ron - 


in pl/sql, try and convert it with to_number and catch the error if it won't convert. 


Lisa Koivu 
Oracle Database Administrator 
Fairfield Resorts, Inc. 
5259 Coconut Creek Parkway 
Ft. Lauderdale, FL, USA  33063 




-Original Message-
From: Smith, Ron L. [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 11:25 AM
To: Multiple recipients of list ORACLE-L
Subject: Check for all numeric in a column



What is the easiest / best way to check for a valid numeric value in a column?


R. Smith


If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you.


*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: Oracle, Siebel and rule-based optimization

[Aponte, Tony]

Take a look at the query plan stability features.  I believe the original purpose was 
to facilitate migration from RBO to CBO.  Its been looking good for the small proof of 
concepts I have done with it.  My overall plan is to capture all plans while in RBO, 
turn on usage of Stored Outlines and switch to CBO.  The hard part is bringing people 
and process into this strategy.  The developers need to provide the outlines for new 
SQL that will be emitted from the app.  The primary tools are Siebel trace files and 
TOAD's explain plan output.

HTH
Tony Aponte  

-Original Message-
Sent: Friday, January 31, 2003 3:19 AM
To: Multiple recipients of list ORACLE-L


One relatively effective way to convert an app from
rule to cost is to enable cost in a very
"rule-oriented" way (namely, you want indexes to be
chosen almost religiously).

You enable cbo but set things like
optimizer_index_cost_adj and optimizer_index_caching
to very index favourable values.  I remember when SAP
went to rule to "cost" and whilst I'm sure some code
changes took place I also remember setting the adjust
parameter to '10' (instead of 100) so nested loop
access was still the dominant access path.  

Of course, this all needs testing and your good
friends at Siebel would probably not support you - but
I've seen it used very successfully a number of times
in the past because you get "rule-like" access paths
but all the goodies that go with cbo as well.

hth
connor

 --- Michael Fontana <[EMAIL PROTECTED]> wrote: > 
> 
> I would like to ask a question of this esteemed and
> knowledgeable audience 
> about running
> SIEBEL applications on Oracle.  Siebel insists that
> their CRM application 
> will only run with
> acceptable performance if rule-based optimization is
> chosen, yet, as we all 
> know, there are
> documented examples of poor performance, and strong
> recommendations to the 
> contrary from Oracle.
> 
> At one point, I recall reading an article from a
> knowledgeable consultant 
> describing what must be done
> to get Siebel to run with optimizer_mode=choose. 
> Any assistance in 
> reaching this goal, or comments,
> would be more than appreciated.  We're still in the
> implementation phase, 
> and are already receiving
> poor performance on certain processes, even using
> the recommended rule 
> based optimization.  I
> predict it will only get worse!
> 
> 
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  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: Aponte, Tony
  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).

RE: Oracle 9.2 installation on Pentium 4, Windows 2000 machine

[Jesse, Rich]

Have you applied any Winders Service Packs?  I just installed 9.2.0.1.0 on a
2.4Ghz Intel running 2K with SP3 without a hitch.  No renaming of .dlls and
I used the default "disk1/setup.exe" to install.  The only issues I had were
some sqlnet.ora parameter problems and the Oracle Names service setup, but
completely unrelated to the installation.

Perhaps the fix for this is in the Service Packs?

Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


-Original Message-
Sent: Friday, January 31, 2003 6:44 AM
To: Multiple recipients of list ORACLE-L


Don't start it from install/setup.exe in the disk1 directory... go to
install/win32 and use install/setup.exe there

-Original Message-
Sent: Friday, January 31, 2003 12:39 PM
To: Multiple recipients of list ORACLE-L


Hi,

I had run the Oracle 9.2 installation on Pentium 4, Windows 2000
machine, after renaming the symcjit.dll file. Still the installation
is not starting.
Can someone help me in this?

Regards,
Pradeep
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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).

Re: RMAN - problems restoring to clone server

[Gary Jackson]

Legato Networker version 6.1.1.Build.238
OS:  SunOS 5.6
Database: Oracle8 Enterprise Edition Release 8.0.4.4.0

TIA!







From: Jared Still <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], "Gary Jackson" <[EMAIL PROTECTED]>
Subject: Re: RMAN - problems restoring to clone server
Date: Fri, 31 Jan 2003 07:02:06 -0800


What is your MML?

Jared

On Thursday 30 January 2003 11:56, Gary Jackson wrote:
> The support analyst handling my TAR gave up, perhaps someone on here can
> help me out...
>
> I have an 8.0.4.4.0 database.  I used RMAN to perform a full backup of 
my
> database on server A (Brain). The RMAN recovery catalog database is on
> server B (Delenn). I am attempting to restore it to a 3rd instance which 
is
> on server C (Ariel). On server C I created a new instance and it is in
> startup nomount currently. From server B I am attempting to restore to
> server C.
>
> run {
> allocate channel t1  type 'SBT_TAPE' parms
> 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf';
> set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf';
> set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf';
> set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf';
> restore database;
> release channel t1;
> }
>
>
> 
>
> RMAN-10032: unhandled exception during execution of job step 1: 
ORA-06512:
> at line 137
> RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry
> possible
> ORA-19507: failed to retrieve sequential file,
> handle="Incr_level0_db_DB1_151_1.hot", parms=""
> ORA-27007: failed to open file
> Additional information: 7009
> Additional information: 2
> ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> RMAN-10031: ORA-19624 occurred during call to
> DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
>
>
>
> _
> Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail


_
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
 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).

RE: Check for all numeric in a column

[Koivu, Lisa]

Title: Message



Hi Ron 
- 
 
in 
pl/sql, try and convert it with to_number and catch the error if it won't 
convert. 
 

Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut 
Creek Parkway Ft. Lauderdale, FL, USA  
33063 
 
 

  -Original Message-From: Smith, Ron L. 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 11:25 
  AMTo: Multiple recipients of list ORACLE-LSubject: Check 
  for all numeric in a column
  What is the 
  easiest / best way to check for a valid numeric value in a 
  column?
   
  R. 
  Smith
  

 
  If you are not the intended recipient of this 
  e-mail message, any use, distribution or copying of the message is prohibited. 
  Please let me know immediately by return e-mail if you have received this 
  message by mistake, then delete the e-mail message. Thank 
you.

Check for all numeric in a column

[Smith, Ron L.]

Title: Message



What is the easiest 
/ best way to check for a valid numeric value in a column?
 
R. 
Smith

  
   

If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited.  Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you.

RE: The lightbulb goes on - WAS-Debate on rc commands Solaris and

[Jesse, Rich]

Hey Dave,

In order for any session to be created by a user, that user MUST have CREATE
SESSION, AFAIK.  The only users that should be able to connect to a
restricted DB are those with the RESTRICTED SESSION priv.  By default, this
is granted to the DBA role.

You might want to throw some info/debug output in your script after the
startup restrict.  Perhaps SELECTs from V$INSTANCE and/or V$SESSION.
V$INSTANCE should tell you if the DB is really in restricted mode or not
("logins" column???) as well as provide an audit in your logfile of exactly
what DB is being bounced/shutdown.  V$SESSION will tell you if another
user's sneaking in there.

Also, I mentioned in another post that the semi-Intelligent Agent may cause
problems.  But now that I look at the account privs, it doesn't look like it
should be able to login restricted.  Hm...

Good Beer to you,
Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


-Original Message-
Sent: Friday, January 31, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L
and Oracle


Ok, so I changed my cold backup script lastnight so it does this;

connect internal/amianidiot
shutdown abort
startup restrict
shutdown normal
exit

It then does the abort and the startup and then the shutdown normal but then
I get this and my database hangs;

Shutting down instance (normal)
License high water mark = 10
Fri Jan 31 03:05:35 2003
SHUTDOWN: waiting for logins to complete.

So now I was totally ready to go postal, but then rational thinking
prevailed so I RTFMed.  I then come across this in the FM;

Typically, all users with the CREATE SESSION system privilege can connect to
an open database. Opening a database in restricted mode allows database
access only to users with both the CREATE SESSION and RESTRICTED SESSION
system privilege; only database administrators should have the RESTRICTED
SESSION system privilege

So my question is, should all users be created with the system privilege of
CREATE SESSION and only I get the RESTRICTED SESSION in addition?  Am I
reading this correctly that for the STARTUP RESTRICT to work all users need
to have the CREATE SESSION privilege?  We have some third party replication
software that is trying to make a connection to Oracle every 5 seconds and I
think this was the culprit.  It DID NOT have the CREATE SESSION but it does
now so I am curious as to if the database will shutdown tonight.  Did I
actually learn something??

Thanks,

Dave(iamanidiot)  :o)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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).

Slightly OT: Multiple database on one UNIX system

[Godlewski, Melissa]

Sun 
Solaris 8
Oracle 
9i
 
Where 
can I find information about, a SUN Solaris 8 UNIX on how much memory 
can the kernel address?  I'm wondering what the semaphore and shared memory 
settings for the SUN OS config file need to be when running multiple 
(20-30)databases on one physical machine.  My current settings are from the 
Oracle install guide for 1 database only.  Any help 
appreciated.
 
 
* 
Oracle 
set 
shmsys:shminfo_shmmax=4294967295set shmsys:shminfo_shmmin=1set 
shmsys:shminfo_shmmni=256set shmsys:shminfo_shmseg=10set 
semsys:seminfo_semmns=1024set semsys:seminfo_semmni=512set 
semsys:seminfo_semmsl=512set semsys:seminfo_semvmx=32767* Oracle 
End   
 
/etc 
330$ipcsIPC status from  as of Fri Jan 31 11:14:35 EST 
2003T 
ID  KEY    
MODE    OWNER    
GROUPMessage Queues:Shared Memory:m  
23808   0x5ec12124 --rw-r-   ora 
oracleiSemaphores:s   24576000   0x4c12445c 
--ra-r-   ora oracleis    6094849   
0x4c12445d --ra-r-   ora oracleis    
6094850   0x4c12445e --ra-r-   ora oraclei 

 
Memory: 16G real, 12G free, 2576M swap in use, 12G swap 
free  

Re: Excessive library cache latch contention

[Jonathan Lewis]

The 'objects on hot latches' is likely to help
only if there is a significant imbalance in the
latch activity on the latch children (as you probably
know).

The fact that 'waits holding' is rather high
may be a hint.  The most likely latches that
you would be holding whilst waiting for the
library cache are (I think) the shared pool
and the library cache.  And one of the more
likely reasons for holding (one of) the library
cache latches whilst waiting for another is
the excessive use of public synonyms in
a system with a large number of distinct
users.

The other thing to note is that the latch
pressure occurs when the system is at
peak usage.  It is possible that excessive
CPU usage is causing the latch problem -
the more CPU being used by 'other users'
the longer it is before I get a chance to get
back on the run queue and release the latch
that someone else is waiting for.  Possibly
you could 'fix' the latch problem by finding
opportunities to reduce the level of CPU
used.  The 'small number of specific forms'
may be exactly the place where there is
some relatively inefficient SQL that uses
CPU too aggressively.



Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 31 January 2003 15:55


>I've got access to Metalink and the doco you mentioned, but its too
generic.
>
>Its a forms application, but the developers are generating dynamic
where
>clauses on blocks, as well as the standard block.item notation in
>predicates, so there is a degree of literal SQL in the mix, and
multiple
>versions of SQL. I don't believe a larger shared pool will help as
there is
>a good chunk of free space there in v$sgastat.
>
>cursor_sharing=FORCE may be an option, but I'll need to test and
observe the
>results.
>
>The 106 latches we get seem to be in a small number of specific
forms, so
>I'll start with those.
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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).

Any E.piphany sites?

[DENNIS WILLIAMS]

We are currently considering implementing the E.piphany CRM software suite.
Does anyone on this list have experience with how this interfaces with
Oracle? Any hard-earned lessons to share with a fellow Oracle DBA? Any
resources to help me get ahead of the issues? Feel free to email me
privately if you prefer.



Dennis Williams 
DBA, 40%OCP 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

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

Re:Oracle on SQL Slammer

[dgoulet]

Well sooner or later Oracle, and no doubt IBM as well, had to get their turn
banging MicroSoft for that Slammer fiasco.  I'll give MicroSoft a little here
(out of character I know) as when your the biggest cheese on the block just
about everyone is out there trying to knock you over.  In MicroSoft's case
though they leave way too many opportunities to wear egg on the face out there
for the picking.  Oracle's Collaborative suite does have advantages over MS, the
best being that MS targeted worms/viruses don't affect Unix/Linux platforms.

Long live the Pengiun!!


Dick Goulet

Reply Separator
Author: "Boivin; Patrice J" <[EMAIL PROTECTED]>
Date:   1/30/2003 12:55 PM

They saw an opportunity to get their point across...

http://www.oracle.com/ip/deploy/database/theme_pages/index.html?se_01282003.
html

Contrast with:
http://www.smh.com.au/articles/2003/01/29/1043534095219.html

Meanwhile the coffee wars are brewing.
http://seattletimes.nwsource.com/html/businesstechnology/134623712_oracle290
.html

Pat.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boivin, Patrice J
  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: 
  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).

RE: RMAN - problems restoring to clone server

[Paula_Stankus]

Title: RE: RMAN - problems restoring to clone server





Are you sure that you have linked your MML to Oracle RDBMS on all hosts?  I thought NSR_SERVER needed to be set to the host on which the target was - due to the fact that the client indexes on each database target host stores information on the backupsets.  

-Original Message-
From: Jared Still [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 10:05 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: RMAN - problems restoring to clone server




What is your MML?


Jared


On Thursday 30 January 2003 11:56, Gary Jackson wrote:
> The support analyst handling my TAR gave up, perhaps someone on here can
> help me out...
>
> I have an 8.0.4.4.0 database.  I used RMAN to perform a full backup of my
> database on server A (Brain). The RMAN recovery catalog database is on
> server B (Delenn). I am attempting to restore it to a 3rd instance which is
> on server C (Ariel). On server C I created a new instance and it is in
> startup nomount currently. From server B I am attempting to restore to
> server C.
>
> run {
> allocate channel t1  type 'SBT_TAPE' parms
> 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)';
> set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf';
> set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf';
> set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf';
> set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf';
> restore database;
> release channel t1;
> }
>
>
> 
>
> RMAN-10032: unhandled exception during execution of job step 1: ORA-06512:
> at line 137
> RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry
> possible
> ORA-19507: failed to retrieve sequential file,
> handle="Incr_level0_db_DB1_151_1.hot", parms=""
> ORA-27007: failed to open file
> Additional information: 7009
> Additional information: 2
> ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925
> RMAN-10031: ORA-19624 occurred during call to
> DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
>
>
>
> _
> Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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).

RE: Oracle 9.2 installation on Pentium 4, Windows 2000 machine

[Thomas Day]

I thought that this problem was fixed with Oracle 9.

Did you just rename the symcjit.dll file or did you also then copy over a
good version?



   

  "Naveen Nahata"  

  
  @mindtree.com>   cc: 

  Sent by: rootSubject: RE: Oracle 9.2 installation on 
Pentium 4, Windows 2000 machine 
   

   

  01/31/2003 07:43 

  AM   

  Please respond   

  to ORACLE-L  

   

   





Don't start it from install/setup.exe in the disk1 directory... go to
install/win32 and use install/setup.exe there

-Original Message-
Sent: Friday, January 31, 2003 12:39 PM
To: Multiple recipients of list ORACLE-L


Hi,

I had run the Oracle 9.2 installation on Pentium 4, Windows 2000
machine, after renaming the symcjit.dll file. Still the installation
is not starting.
Can someone help me in this?

Regards,
Pradeep

Disclaimer:
"The  information  in  this  e-mail is intended only for the person or
entity  to  which  it is addressed and may contain confidential and/or
privileged  material.  It  should not be used by anyone who is not the
original  intended  recipient.  If  you have erroneously received this
message,  please  delete  it  immediately  and  notify the sender. The
recipient  acknowledges  that  IBS  or  IBS Group like any other large
global  corporation, are unable to exercise total control or ensure or
guarantee  the  integrity  of contents of the information contained in
individual  e-mail  transmissions  and the recipient acknowledges that
any  views  expressed  in  this message may be those of the individual
sender  and  no  binding  nature  of  the  message shall be implied or
assumed  unless the sender does so expressly with due authority of IBS
Group.  Before  opening  any attachments please check them for viruses
and defects."

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  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).



DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received this
message by mistake please notify the sender by return  e-mail and delete
this message from your system. Any unauthorized use or dissemination of
this message in whole or in part is strictly prohibited.  Please note that
e-mails are susceptible to change and MindTree shall not be liable for any
improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
  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 

Re:RE: The lightbulb goes on - WAS-Debate on rc commands Sol

[dgoulet]

Dave,

You, SYSTEM, and SYS should be the only people with the restricted session
system privilege.  If those third party applications have it, revoke it.  If
they have DBA which includes restricted session revoke that as well.  Then a
startup restrict and shutdown normal will run successfully.

BTW: If that third party software has a fit because you revoked DBA, find
another vendor.

Dick Goulet

Reply Separator
Author: "Farnsworth; Dave" <[EMAIL PROTECTED]>
Date:   1/31/2003 7:34 AM

Ok, so I changed my cold backup script lastnight so it does this;

connect internal/amianidiot
shutdown abort
startup restrict
shutdown normal
exit

It then does the abort and the startup and then the shutdown normal but then I
get this and my database hangs;

Shutting down instance (normal)
License high water mark = 10
Fri Jan 31 03:05:35 2003
SHUTDOWN: waiting for logins to complete.

So now I was totally ready to go postal, but then rational thinking prevailed so
I RTFMed.  I then come across this in the FM;

Typically, all users with the CREATE SESSION system privilege can connect to an
open database. Opening a database in restricted mode allows database access only
to users with both the CREATE SESSION and RESTRICTED SESSION system privilege;
only database administrators should have the RESTRICTED SESSION system privilege

So my question is, should all users be created with the system privilege of
CREATE SESSION and only I get the RESTRICTED SESSION in addition?  Am I reading
this correctly that for the STARTUP RESTRICT to work all users need to have the
CREATE SESSION privilege?  We have some third party replication software that is
trying to make a connection to Oracle every 5 seconds and I think this was the
culprit.  It DID NOT have the CREATE SESSION but it does now so I am curious as
to if the database will shutdown tonight.  Did I actually learn something??

Thanks,

Dave(iamanidiot)  :o)

-Original Message-
Sent: Thursday, January 30, 2003 7:15 AM
To: Multiple recipients of list ORACLE-L



I'll echo that sentiment.

'shutdown abort', 'startup restrict' was a regular part of my
shutdown scripts beginning in 1994 with 7.0.16, as 
'shutdown immediate' wasn't all that reliable, even in situations
where it should have worked.

Jared

On Wednesday 29 January 2003 15:53, John Kanagaraj wrote:
> Rao,
>
> And where did you read that 'shutdown abort' is not recommended? This is
> another myth that has been busted a while ago. A shutdown abort followed by
> a startup restrict and a normal shutdown is the way to go when dealing with
> rogue sessions that open a connection and never shutdown. In such cases, a
> shutdown immediate will _never_ return (certainly not within your 5 to 10
> minutes). I have been using this method for more than 8 years now -
> starting at 7.0.16 fyi. The trick in this case is to script it into the rc
> commands.
>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002
>
> I don't know what the future holds for me, but I do know who holds my
> future!
>
> ** The opinions and statements above are entirely my own and not those of
> my employer or clients **
>
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Paula,
>
> Shutdown abort is not recommended as the file checkpointing is not done
> during shutdown abort.  If you need to perform shutdown abort, then, it is
> preferred to bring up the db with startup restrict (so that the users
> wouldn't connect) and then, cleanly shutdown the db and bring it up again.
>
> Tell to your sys admins. that shutdown immediate would take some time
> (about 5 to 10 minutes) depending on the activity on your db.  They would
> have to wait for that much time before calling a DBA during system boots.
>
> Rao
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> System Administrator says he doesn't trust that the rc commands will stop
> if the database doesn't want to shutdown and even if it does would want to
> shutdown with scripts beforehand so that a DBA could connect and resolve
> the issue.  Other DBA says this is all wrong and rc commands should include
> shutdown immediate of database.  In the past I had setup 2 processes in the
> system scripts for the sys admin - shutdown immediate - wait  shutdown
> abort - on a read-only DSS system which of course allows some room for this
> type of activity.  I kind of would want to know if a database was going to
> be shutdown with an abort esp. in OLTP system and do it myself.
>
>
>
> - any ideas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
---

RE: The lightbulb goes on - WAS-Debate on rc commands Solaris and

[DENNIS WILLIAMS]

Dave - Why not do a shutdown immediate? 

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, January 31, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L
and Oracle


Ok, so I changed my cold backup script lastnight so it does this;

connect internal/amianidiot
shutdown abort
startup restrict
shutdown normal
exit

It then does the abort and the startup and then the shutdown normal but then
I get this and my database hangs;

Shutting down instance (normal)
License high water mark = 10
Fri Jan 31 03:05:35 2003
SHUTDOWN: waiting for logins to complete.

So now I was totally ready to go postal, but then rational thinking
prevailed so I RTFMed.  I then come across this in the FM;

Typically, all users with the CREATE SESSION system privilege can connect to
an open database. Opening a database in restricted mode allows database
access only to users with both the CREATE SESSION and RESTRICTED SESSION
system privilege; only database administrators should have the RESTRICTED
SESSION system privilege

So my question is, should all users be created with the system privilege of
CREATE SESSION and only I get the RESTRICTED SESSION in addition?  Am I
reading this correctly that for the STARTUP RESTRICT to work all users need
to have the CREATE SESSION privilege?  We have some third party replication
software that is trying to make a connection to Oracle every 5 seconds and I
think this was the culprit.  It DID NOT have the CREATE SESSION but it does
now so I am curious as to if the database will shutdown tonight.  Did I
actually learn something??

Thanks,

Dave(iamanidiot)  :o)

-Original Message-
Sent: Thursday, January 30, 2003 7:15 AM
To: Multiple recipients of list ORACLE-L



I'll echo that sentiment.

'shutdown abort', 'startup restrict' was a regular part of my
shutdown scripts beginning in 1994 with 7.0.16, as 
'shutdown immediate' wasn't all that reliable, even in situations
where it should have worked.

Jared

On Wednesday 29 January 2003 15:53, John Kanagaraj wrote:
> Rao,
>
> And where did you read that 'shutdown abort' is not recommended? This is
> another myth that has been busted a while ago. A shutdown abort followed
by
> a startup restrict and a normal shutdown is the way to go when dealing
with
> rogue sessions that open a connection and never shutdown. In such cases, a
> shutdown immediate will _never_ return (certainly not within your 5 to 10
> minutes). I have been using this method for more than 8 years now -
> starting at 7.0.16 fyi. The trick in this case is to script it into the rc
> commands.
>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002
>
> I don't know what the future holds for me, but I do know who holds my
> future!
>
> ** The opinions and statements above are entirely my own and not those of
> my employer or clients **
>
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Paula,
>
> Shutdown abort is not recommended as the file checkpointing is not done
> during shutdown abort.  If you need to perform shutdown abort, then, it is
> preferred to bring up the db with startup restrict (so that the users
> wouldn't connect) and then, cleanly shutdown the db and bring it up again.
>
> Tell to your sys admins. that shutdown immediate would take some time
> (about 5 to 10 minutes) depending on the activity on your db.  They would
> have to wait for that much time before calling a DBA during system boots.
>
> Rao
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> System Administrator says he doesn't trust that the rc commands will stop
> if the database doesn't want to shutdown and even if it does would want to
> shutdown with scripts beforehand so that a DBA could connect and resolve
> the issue.  Other DBA says this is all wrong and rc commands should
include
> shutdown immediate of database.  In the past I had setup 2 processes in
the
> system scripts for the sys admin - shutdown immediate - wait  shutdown
> abort - on a read-only DSS system which of course allows some room for
this
> type of activity.  I kind of would want to know if a database was going to
> be shutdown with an abort esp. in OLTP system and do it myself.
>
>
>
> - any ideas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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 oth

RE: Making dispatchers re-read tnsnames.ora?

[Stephen Evans]

jeremiah,

some good points.

we use onames becuase we have many workstations spread thru out our org (we are a med center/university) to which we dont have access.

not only do we not have access to them, we dont even know where they are or how many there are.

we also have the problem that some local adminstrators of these workstations load oracle clients that create their own tnsnames files (i guess they are using canned vendored apps)  and in so doing stomp all over our tnsnames file.

all we do now is to ensure these admins have a copy of our sqlnet.ora file (which specifies to look at tnsnames & then onames in its search order) which they make sure is in the current TNS_ADMIN directory

so, in our case, its not feasible to distribute the tnsnames file.

thanks,

steve








Jeremiah Wilton <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/30/2003 07:30 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Making dispatchers re-read tnsnames.ora?


Well, my first objection to ONames was just in the context of solving
my problem.  Dispatchers cache addresses for outgoing connections,
whether they cache them from tnsnames.ora or ofrom Oracle Names.  Or
so I understand it.  If someone knows differently about Names then I
would like to hear about it.

As for the limitations of names...  Yes, you can have the local host
caching of directory entries.  My objection is more to adding a new
service with added equipment and expense where flat files actually do
a better job.

Sitations with very high connection rates pose a problem.  Using a
pool of application servers, there may be a need for multiple
listeners to handle the load if a connection pooling technology is not
in the picture.  In such a situation it is helpful for purpises of
scalability and availability to segregate a large number of app
servers into "cells" of, say, 10 or 20 servers each. You want each of
these cells to be inidividually maintainable, and to be able to be put
in and taken out of service independently, so that overall aailability
for the application base is always maintained.

In the cell model, a listener or set of listeners is created FOR EACH
CELL.  The TNS entries vary from cell to cell for that one service,
because they specify different listeners.  The advantages to doing
this are many.  With a cell out of service, you can take down a
listener for whatever reason with no impact to the running
application.  And if a listener or set of listeners becomes saturated,
the majority of inbound connections are unaffected by the problem,
thus limiting the scope of a fault.  AFAIK, you can't really do this
in ONames without making up a different service name for every cell's
listeners.

Now, say your service is so important that you don't want all the
back-office Duke Nukem and Doom sessions saturating the network and
interfering with the application traffic.  In reaction, you create a
totally separate dedicated network for the most important systems in
the company to communicate with the database server.  You add a
network interface card with its own IP addfress, but you leave the old
one there so that the back office people can make occasional database
queries.  Naturally you will want separate listeners servicing the
"importantnet" than service the old office LAN.  In ONames, you would
again have to name the same database's services on different networks
differently.

So then I ask, what advantage is there to Names?  You can change
things in a central location.  There are no files to push around.
Well, pushing files isn't really bad once you have a good set of
scripts to do it.  And flat files are a really reliable sevice.  If
you screw up a tnsnames.ora file change, you can catch the mistake by
just testing with TNS_ADMIN, rather than taking down the whole company
by fatfingering an ONames change.

So in short, why again do I want yet another service that needs to be
available (or the local caching up to date) to keep people connecting
to my databases (especialy if I have to set up a separate service name
for every little special situation)?

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Thu, 30 Jan 2003, Jesse, Rich wrote:

> Not that ONames doesn't have it's shortcomings, but I'm not sure how ONames
> is a single point of failure.  Even in our little 35-alias ONames repository
> with only the root region, we have a secondary Names Server.  With local
> checkpoint files, the repository is not required for continuous access, so
> there's no SPoF there.
> 
> Could you expound a bit on that, Jeremiah?
> 
> Thanks,
> Rich
> 
> 
> Rich Jesse                           System/Database Administrator
> [EMAIL PROTECTED]                  Quad/Tech International, Sussex, WI USA
> 
> 
> -Original Message-
> Sent: Thursday, January 30, 2003 10:10 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> On Thu, 30 Jan 20

SQL*Plus Worksheet Question

[Harrington, Eric]

Title: SQL*Plus Worksheet Question





Version 9.2

I am monitoring several databases and have several SQL*Plus Worksheet sessions open. I would like to display the connected user name and instance as the prompt associated w/ the appropriate session. Per Metalink Note:120664.1 I added the following lines to the login.sql:

set termout off

spool set_prompt.sql

select 'set sqlprompt '||instance||'>'

from sys.v_$thread;

spool off

start set_prompt.sql

set termout on

This does successfully change the prompt to the instance name for SQL*Plus but not SQL*Plus Worksheet. What am I missing? Is there another way to accomplish this?

Eric Harrington

State of Maine

Bureau of Motor Vehicles 

RE: The lightbulb goes on - WAS-Debate on rc commands Solaris and Oracle

[Farnsworth, Dave]

Ok, so I changed my cold backup script lastnight so it does this;

connect internal/amianidiot
shutdown abort
startup restrict
shutdown normal
exit

It then does the abort and the startup and then the shutdown normal but then I get 
this and my database hangs;

Shutting down instance (normal)
License high water mark = 10
Fri Jan 31 03:05:35 2003
SHUTDOWN: waiting for logins to complete.

So now I was totally ready to go postal, but then rational thinking prevailed so I 
RTFMed.  I then come across this in the FM;

Typically, all users with the CREATE SESSION system privilege can connect to an open 
database. Opening a database in restricted mode allows database access only to users 
with both the CREATE SESSION and RESTRICTED SESSION system privilege; only database 
administrators should have the RESTRICTED SESSION system privilege

So my question is, should all users be created with the system privilege of CREATE 
SESSION and only I get the RESTRICTED SESSION in addition?  Am I reading this 
correctly that for the STARTUP RESTRICT to work all users need to have the CREATE 
SESSION privilege?  We have some third party replication software that is trying to 
make a connection to Oracle every 5 seconds and I think this was the culprit.  It DID 
NOT have the CREATE SESSION but it does now so I am curious as to if the database will 
shutdown tonight.  Did I actually learn something??

Thanks,

Dave(iamanidiot)  :o)

-Original Message-
Sent: Thursday, January 30, 2003 7:15 AM
To: Multiple recipients of list ORACLE-L



I'll echo that sentiment.

'shutdown abort', 'startup restrict' was a regular part of my
shutdown scripts beginning in 1994 with 7.0.16, as 
'shutdown immediate' wasn't all that reliable, even in situations
where it should have worked.

Jared

On Wednesday 29 January 2003 15:53, John Kanagaraj wrote:
> Rao,
>
> And where did you read that 'shutdown abort' is not recommended? This is
> another myth that has been busted a while ago. A shutdown abort followed by
> a startup restrict and a normal shutdown is the way to go when dealing with
> rogue sessions that open a connection and never shutdown. In such cases, a
> shutdown immediate will _never_ return (certainly not within your 5 to 10
> minutes). I have been using this method for more than 8 years now -
> starting at 7.0.16 fyi. The trick in this case is to script it into the rc
> commands.
>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002
>
> I don't know what the future holds for me, but I do know who holds my
> future!
>
> ** The opinions and statements above are entirely my own and not those of
> my employer or clients **
>
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Paula,
>
> Shutdown abort is not recommended as the file checkpointing is not done
> during shutdown abort.  If you need to perform shutdown abort, then, it is
> preferred to bring up the db with startup restrict (so that the users
> wouldn't connect) and then, cleanly shutdown the db and bring it up again.
>
> Tell to your sys admins. that shutdown immediate would take some time
> (about 5 to 10 minutes) depending on the activity on your db.  They would
> have to wait for that much time before calling a DBA during system boots.
>
> Rao
>
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> System Administrator says he doesn't trust that the rc commands will stop
> if the database doesn't want to shutdown and even if it does would want to
> shutdown with scripts beforehand so that a DBA could connect and resolve
> the issue.  Other DBA says this is all wrong and rc commands should include
> shutdown immediate of database.  In the past I had setup 2 processes in the
> system scripts for the sys admin - shutdown immediate - wait  shutdown
> abort - on a read-only DSS system which of course allows some room for this
> type of activity.  I kind of would want to know if a database was going to
> be shutdown with an abort esp. in OLTP system and do it myself.
>
>
>
> - any ideas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: Farnsworth, Dave
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatc

Re: Oracle, Siebel and rule-based optimization

[Michael Fontana]

At 01:53 AM 1/31/2003 -0800, Ferenc Mantfeld wrote:
Connor
You're ass-u-ming that what worked for SAP will work for Siebel. Not a
good
assumption. Been there, done it, it does not work ! What you're
advocating,
I did similar measures when supporting Ora-Apps when we went from 9.6
to
10.5 (Oracle 7.1.6 RBO to 7.3.2 CBO), what a nightmare that was !
But
understand that Siebel was written specifically to take advantage 
of
Oracle's RBO. 
Still, some of the queries in Siebel SEEM to run better using CBO. 
Is there
any way to mix and match approaches?  To use RBO by default, but
perhaps
CBO might make sense for certain queries, and perhaps some
 AD-HOC?
We've got consultants who've told me that Siebel 'abuses' Oracle software
in
it's excessive use of outer joins, that Siebel defiles the notions of
proper relational
design with 200+ column tables, and otherwise does not "play
nice" with the
normal rules of relational database management as implemented in Oracle
9i.  
I've also heard that Siebel runs better with DB2 and SQLServer, which may
be options for us.
Any comments on these points?