RE: redo log file setup with mirrored drives

[Stephen Lee]

I suppose I should come clean on this deal and admit that we do indeed have
Oracle duplex the redo files.  The only time we would not do this is if some
user with sufficient bureaucratic power has some suckwad app and was
demanding that everything be done to bump up performance.  If it comes to
that, we'll do it and not lose any sleep over it.  Even though we have
Oracle duplexing, we still have had it happen that some storage array
maintenance person went in and managed to hose up both sides of the duplex.
Of course, this doesn't result in the loss of the database, but rather the
loss of some data.  But wasn't it fun debate.

What I found interesting was that nobody brought up what to do about the
archived logs -- how much mirroring is "enough" and how long to wait before
shoving them off onto tape.  Now, the loss of these babies can get you into
deep doodoo.  But, here again, we must sometimes make compromises for the
sake of a rotten application and overtaxed hardware.  At our shop here, we
are forced to rely on hardware mirroring of archives.  We have no choice.

You just try to get as many people to sign off on the setup as you can.

None of this changes the truth of anything I wrote.  I have found hardware
and software mirroring to be extremely reliable.  I have never lost a file
to it, and it has saved my butt many times.  At one place I worked, we
regularly tested yanking out power cords, I/O cables, storage array drawers,
anything we could think of, while the database and application were running
full blast.  It never failed once (except for early Veritas on Motorola 88K
which was a mess).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).

OSF11 in TRU64 Unix

[Rajesh Dayal]

Hi everybody,

I am about to install Oracle 9i release 2 on TRU64 Unix. In one
of the pre-requisites, Oracle suggests to have OSF11 installed on the
Operating System. Some how our System Admin is not able to locate where
from this component/package would come. 
Any-body having any idea, how to install and where from to
download this component/package. Also please clarify what minimum
version of JDK is required? At some place they say minimum required is
1.3.1 while in some other document (Doc ID: 169706.1) they say minimum
version of JDK required  is 1.1.8. 
Some body help please,

Best Regards,
Rajesh

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rajesh Dayal
  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: Problem doing RMAN backup of Clone?

[Ron Yount]

Doug,

I think I may have seen a post that answered this question already, but
just to be on the safe side:

You cannot backup two databases with the same dbid using the same rman
catalog.  This always the case with cloned databases unless...

9i - You use the dbid utility to change the dbid of the clone
8i - You use the dbms_backup_restore.zerodbid(0) (MAKE SURE YOU
understand this before implementing it)

-Ron-

-Original Message-
Gramolini
Sent: Tuesday, November 26, 2002 8:34 AM
To: Multiple recipients of list ORACLE-L


Ditto!  Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, November 25, 2002 9:59 AM


Good to hear from you Lisa!!  Welcome Back!!

-Original Message-
Sent: Monday, November 25, 2002 7:49 AM
To: Multiple recipients of list ORACLE-L




Hi Doug,

I can't answer the question about rman... but if you recreate the
controlfile you will have a new incarnation number of the database.
Have you tried that?

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


-Original Message-
Sent:   Sunday, November 24, 2002 10:14 PM
To: Multiple recipients of list ORACLE-L

Someone has just told me you can't do an RMAN backup of a clone because
it has
the same database id as the original.   Is this true or not?  If so, how
to
get
around it?

Thanks,
Doug

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
  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.com
-- 
Author: Ruth Gramolini
  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.com
-- 
Author: Ron Yount
  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: SQL tuning help

[Sathyanaryanan_K/VGIL]

check out the status in v$sess
Regards,

Sathyanarayanan




|+--->
||  "Sergei" |
|||
||   |
||  27/11/2002   |
||  00:24|
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >--|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   <[EMAIL PROTECTED]> |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: SQL tuning help   |
  >--|





Hello everybody,

I have the following query that runs every week.

UPDATE tmp_brian_metareward1 tmp
  SET offers_seen  = (SELECT count(f.fastcash_id) FROM
metareward.fastcash f
 WHERE f.subsite_id = tmp.subsite_id
   and attempt >= trunc(sysdate-1)
   and attempt < trunc(sysdate)
 group by tmp.subsite_id);

This week it began to hang and I can't figure out why.  No changes were
made to a database.  Please advise me on how I can tune it, which hints
to add, or anything else I can do.

Thank you
Sergei


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sergei
  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.com
-- 
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 Log Miner Question

[Arup Nanda]

Sure, it's the Log Miner tool on Oracle Enterprise Manager.







From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Oracle Log Miner Question
Date: Tue, 26 Nov 2002 19:43:49 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc8-f33.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 Nov 
2002 20:19:40 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id UAA60365;Tue, 26 Nov 2002 20:14:25 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050D23C; 
Tue, 26 Nov 2002 19:43:49 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: [EMAIL PROTECTED]
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 27 Nov 2002 04:19:40.0682 (UTC) 
FILETIME=[348566A0:01C295CC]

Dear All,

Does anybody know if there is a front end tool available for the Oracle
Log Miner...?


Prem


_
Add photos to your messages with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arup Nanda
 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 OS level security

[Arup Nanda]

Thanks for the info, Gopal.

I did find a bbed.exe but it asks for a password. For unix ports it doesn't, 
per your post.

Since you obviously have some experience on this tool; would you mind 
sharing that - usage and all.

From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: Oracle OS level security
Date: Tue, 26 Nov 2002 19:28:42 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc6-f19.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 Nov 
2002 19:57:49 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id TAA58357;Tue, 26 Nov 2002 19:52:14 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050D21F; 
Tue, 26 Nov 2002 19:28:42 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: "K Gopalakrishnan" <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 27 Nov 2002 03:57:49.0937 (UTC) 
FILETIME=[27419610:01C295C9]

Arup:

BBED is

B lock
B rowser &
ED itor.


Best Regards,
K Gopalakrishnan




-Original Message-
Sent: Tuesday, November 26, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


What is BBED? I never heard of it.






>From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Oracle OS level security
>Date: Tue, 26 Nov 2002 16:09:27 -0800
>MIME-Version: 1.0
>Received: from newsfeed.cts.com ([209.68.248.164]) by
>mc8-f17.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 
Nov
>2002 17:06:39 -0800
>Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com
>(8.9.3/8.9.3) with UUCP id RAA43612;Tue, 26 Nov 2002 17:01:24 -0800 (PST)
>Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 
0050CFBE;
>Tue, 26 Nov 2002 16:09:27 -0800
>Message-ID: <[EMAIL PROTECTED]>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "K Gopalakrishnan" <[EMAIL PROTECTED]>
>Sender: [EMAIL PROTECTED]
>Errors-To: [EMAIL PROTECTED]
>Organization: Fat City Network Services, San Diego, California
>X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
>Precedence: bulk
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 27 Nov 2002 01:06:39.0079 (UTC)
>FILETIME=[3D590770:01C295B1]
>
>Jared:
>
>Any one with a reasonable knowledge of Oracle Data Storage
>Internals can use the Data block Editor (BBED) to update
>anything in your database without the knowledge of the
>RDBMS kernel auditing mechanisms.
>
>Agreed,BBED is protected by a password in Windoze ports
>and one need to explicitly make the executable in Unix
>ports. But the point here is the hacker can do anything
>using the BBEd and this can be done even while your
>database is up and running !!
>
>What is their take on this kind of attack(!)s?>
>
>
>Best Regards,
>K Gopalakrishnan
>
>
>
>
>-Original Message-
>[EMAIL PROTECTED]
>Sent: Tuesday, November 26, 2002 3:05 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Dear list,
>
>Let me toss a hypothetical situation at you.
>
>Say some auditors looked at some of your primary systems,
>and concluded that they had no assurance that someone with
>admin access to the server had not changed financial information
>to benefit themselves, or to falsify financial records for the
>gain of the company.
>
>Not that they might have any proof that something like that
>had been done, but rather, just not proof that it had *not*
>been done.
>
>I've been pondering this for a bit, and it seems to me that if
>someone had good knowledge of both the OS and the
>database (Oracle), as well as having admin rights on the
>server, there are few things you can do to prevent such a person
>from changing data in the database, and completely
>covering his or her tracks.
>
>The platforms in question are Unix, Windows NT and
>Windows 2000.   I've limited it to those as most database
>systems use one of those, and besides, that's all I know.  :)
>
>Consider what steps you might take to audit unauthorized
>transactions performed by an admin.
>
>Oracle Auditing could be used, but someone with admin
>access to the server and database could easily alter the
>records created by system auditing.
>
>You could create an audit table, using a trigger to audit
>sensitive tables.  A materialized view on a remote database
>could be created on sensitive tables to remotely log all
>actions.
>
>In the case of the audit table, that could easily be disabled,
>and then re-enabled after the nefarious DML had completed.
>
>The materialized views might be more difficult to circumvent.
>
>If the remote end is using a dblink to the server employing a
>password 

RE: Advanced Rep between 9.2.0 and 8.1.7?

[Arup Nanda]

Ferenc,

I have a multimaster setup between 8174 and 920 and works like a charm.

Tables 200
Schemas 5
Total Size 600 Gb
Average Table Size 14 million rows
Transaction per second (from STATSPACK) about 13
Machine HP 6000 servers
OS Windows 2000 Advanced Server

About the setup, there is nothing special about replication between 8ik and 
9i, it's pretty much the same as in case of 8i-8i.

HTH

Arup Nanda
www.proligence.com






From: mantfield <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: Advanced Rep between 9.2.0 and 8.1.7?
Date: Tue, 26 Nov 2002 19:04:04 -0800
MIME-Version: 1.0
Received: from mc4-f18.law16.hotmail.com ([65.54.237.153]) by 
mc4-s19.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 
Nov 2002 19:32:57 -0800
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc4-f18.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 
Nov 2002 19:32:55 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id TAA56272;Tue, 26 Nov 2002 19:27:42 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050D197; 
Tue, 26 Nov 2002 19:04:04 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: mantfield <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 27 Nov 2002 03:32:55.0586 (UTC) 
FILETIME=[AC8DB820:01C295C5]

What is performance like ? how many objects in replication group ?

What I am essentially looking for is performance degradation metrics and
full specs (# users, machine, OS, size of DB, # rows in largest tables,
rate of inserts / updates) from source for a one-way replication of about
500 tables and about 3,000 indexes on these tables. You wouldn't happen to
have any of those metrics, would you ? Would you ? Pretty please ?

Quests touts that Shareplex is so much better than AR, but how much is that
? Does it justify the price premium which they are not shy on ? Or should I
be looking at Shadow Base 3, or Omni-Replicator, or just stick with AR and
slap in an extra CPU ?

Thanks.

Ferenc Mantfeld

-Original Message-
From:	OraCop [SMTP:[EMAIL PROTECTED]]
Sent:	Wednesday, November 27, 2002 12:49 PM
To:	Multiple recipients of list ORACLE-L
Subject:	Re: Advanced Rep between 9.2.0 and 8.1.7?

Yes, I am replicating between 8.1.7.3 and 9iR2
without any issues.

 Need help?

OraCop


--- Michael Barger <[EMAIL PROTECTED]> wrote:
> Has anyone heard of or experienced a successful
> implementation of Advanced Replication between an
> 8.1.7 database and a 9.2.0 db?
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Web Hosting - Let the expert host your site
> http://webhosting.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Michael Barger
>   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.com
--
Author: OraCop
  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.com
--
Author: mantfield
  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
(o

Re: Best way to store images in DB ?

[Arup Nanda]

UTL_FILE_DIR is for text files only using utl_file package;  not useful for 
pictures.

For pictures you have two choices

(1) Use BLOB fields where the picture can be stored in the database. This 
adds to security and reliability. However, it generates too much redo (and 
archvive).

(2) Use BFILE, where the actual file is stored on the file system and 
pointer to it is stored in the database. However security is definitely an 
issue. And how do you plan to back it up?

There are other ways like using WebDAV and iFS, but I have no experience in 
using them.

HTH

Arup Nanda
www.proligence.com





From: "oraora  oraora" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Best way to store images in DB ?
Date: Tue, 26 Nov 2002 19:14:20 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc5-f20.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 Nov 
2002 20:03:52 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id TAA59053;Tue, 26 Nov 2002 19:58:40 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050D1CD; 
Tue, 26 Nov 2002 19:14:20 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: "oraora  oraora" <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 27 Nov 2002 04:03:52.0570 (UTC) 
FILETIME=[FF66FDA0:01C295C9]

Lee,

i have to store 20,000,000 images of 5k each in DB.
which is the best possible way to do it ?
can i store it as BLOB or use UTL_FILE_DIR ?
is there any other means of achieving the same ?

it's 8.1.6 on Win2k.

Kindly let me know.

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


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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arup Nanda
 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 Log Miner Question

[prem]

Dear All,

Does anybody know if there is a front end tool available for the Oracle Log Miner...?


Prem

Best way to store images in DB ?

[oraora oraora]

Guys,

i have to store 20,000,000 images of 5k each in DB.
which is the best possible way to do it ?
can i store it as BLOB or use UTL_FILE_DIR ?
is there any other means of achieving the same ?

it's 8.1.6 on Win2k.

Kindly let me know.

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

RE: Oracle OS level security

[K Gopalakrishnan]

Arup:

BBED is

B lock
B rowser &
ED itor.


Best Regards,
K Gopalakrishnan




-Original Message-
Sent: Tuesday, November 26, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


What is BBED? I never heard of it.






>From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Oracle OS level security
>Date: Tue, 26 Nov 2002 16:09:27 -0800
>MIME-Version: 1.0
>Received: from newsfeed.cts.com ([209.68.248.164]) by
>mc8-f17.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 Nov
>2002 17:06:39 -0800
>Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com
>(8.9.3/8.9.3) with UUCP id RAA43612;Tue, 26 Nov 2002 17:01:24 -0800 (PST)
>Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050CFBE;
>Tue, 26 Nov 2002 16:09:27 -0800
>Message-ID: <[EMAIL PROTECTED]>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "K Gopalakrishnan" <[EMAIL PROTECTED]>
>Sender: [EMAIL PROTECTED]
>Errors-To: [EMAIL PROTECTED]
>Organization: Fat City Network Services, San Diego, California
>X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
>Precedence: bulk
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 27 Nov 2002 01:06:39.0079 (UTC)
>FILETIME=[3D590770:01C295B1]
>
>Jared:
>
>Any one with a reasonable knowledge of Oracle Data Storage
>Internals can use the Data block Editor (BBED) to update
>anything in your database without the knowledge of the
>RDBMS kernel auditing mechanisms.
>
>Agreed,BBED is protected by a password in Windoze ports
>and one need to explicitly make the executable in Unix
>ports. But the point here is the hacker can do anything
>using the BBEd and this can be done even while your
>database is up and running !!
>
>What is their take on this kind of attack(!)s?>
>
>
>Best Regards,
>K Gopalakrishnan
>
>
>
>
>-Original Message-
>[EMAIL PROTECTED]
>Sent: Tuesday, November 26, 2002 3:05 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Dear list,
>
>Let me toss a hypothetical situation at you.
>
>Say some auditors looked at some of your primary systems,
>and concluded that they had no assurance that someone with
>admin access to the server had not changed financial information
>to benefit themselves, or to falsify financial records for the
>gain of the company.
>
>Not that they might have any proof that something like that
>had been done, but rather, just not proof that it had *not*
>been done.
>
>I've been pondering this for a bit, and it seems to me that if
>someone had good knowledge of both the OS and the
>database (Oracle), as well as having admin rights on the
>server, there are few things you can do to prevent such a person
>from changing data in the database, and completely
>covering his or her tracks.
>
>The platforms in question are Unix, Windows NT and
>Windows 2000.   I've limited it to those as most database
>systems use one of those, and besides, that's all I know.  :)
>
>Consider what steps you might take to audit unauthorized
>transactions performed by an admin.
>
>Oracle Auditing could be used, but someone with admin
>access to the server and database could easily alter the
>records created by system auditing.
>
>You could create an audit table, using a trigger to audit
>sensitive tables.  A materialized view on a remote database
>could be created on sensitive tables to remotely log all
>actions.
>
>In the case of the audit table, that could easily be disabled,
>and then re-enabled after the nefarious DML had completed.
>
>The materialized views might be more difficult to circumvent.
>
>If the remote end is using a dblink to the server employing a
>password that is *different* than that of it's own account at the
>remote server, it should be impossible for someone to completely
>cover the traces of transactions created to falsify data.
>
>The MV  Logs could be dropped, but without access to the MV's
>at the remote server, the MV's would have to be left in place.
>
>These could be used as a reference to look for unauthorized transactions
>in the primary server.  If this same admin has access to the remote
>server where the MV's are, then this can also be circumvented.
>
>There is also the logs created as when logging in as internal
>or sysdba. ( $ORACLE_HOME/rdms/audit/*.aud )
>
>These can simply be deleted.  Some system could be used to save
>these to a remote server, but it would have to run *very* frequently to
>be effective.
>
>Oracle password files could also be used. While this can prevent
>someone from logging in as SYS or SYSTEM while in place, all it
>takes is a change to init.ora, and a database bounce to fix that.
>
>Make your bogus data changes, change the init.ora back and
>bounce the database again.
>
>A somewhat clever person could set this up to automatically
>take place the next time the DB is bounced.
>
>The conclusion I have come to is that the only effective method
>that could be used to create an audit trail fo

Best way to store images in DB ?

[oraora oraora]

Lee,

i have to store 20,000,000 images of 5k each in DB.
which is the best possible way to do it ?
can i store it as BLOB or use UTL_FILE_DIR ?
is there any other means of achieving the same ?

it's 8.1.6 on Win2k.

Kindly let me know.

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

RE: Autoextend WAIT statistic?

[MacGregor, Ian A.]

As much as the v$"wait" views are touted they do have problems.  First, I believe they 
are only updated every 3 seconds and can miss events.  Second,  examine enough 
samplings from the tables and you'll discover bizarre data.  Events which managed to 
wait say 50 seconds in a single sampling period when the sampling rate was 5 Hz.  On 
the other hand, one may see events which are continuous over many samplings, but their 
wait times are not incremented.

If you really want to know what's going on there's no substitute for a 10046 trace.

N.B., I am not stating the v$"wait" statistics tables are useless just that they have 
their shortcomings.


Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

 
  

-Original Message-
Sent: Tuesday, November 26, 2002 3:26 PM
To: Multiple recipients of list ORACLE-L


Dennis,
I did some quick & dirty testing by creating a very small(10M) datafile with a 
large(2000m) autoextend clause. On the insert, the session was waiting on 'file open' 
for most of the time. When I did a rollback and reinserted the data, there were no 
waits (that I saw) on file open.

Interestingly, this wait event does not appear to be accurately tracked in 
v$session_event. In v$session_wait the seconds in wait (last
trapped) was 132. In v$session_event, it shows 0. Okay, gurus, why? Am I missing 
something in this?

select * from v$session_wait where sid = 14
  SID   SEQ# EVENT
-- --

P1TEXT   P1
P1RAW
 --

P2TEXT   P2
P2RAW
 --

P3TEXT   P3
P3RAW WAIT_TIME SECONDS_IN_WAIT
 --
 -- ---
STATE
---
14322 file open
fib  4327126592
000101EAB640
iov  4327069760
000101E9D840
0 0
00   -1 132
WAITED SHORT TIME

select * from v$session_event where sid = 14
   SID EVENT  TOTAL_WAITS TOTAL_TIMEOUTS
TIME_WAITED AVERAGE_WAIT   MAX_WAIT
-- -- --- --
---  --
14 rdbms ipc reply  4  1
210 52.5205
14 control file sequential read18  0
16   .9 15
14 local write wait 1  0
00  0
14 log buffer space72  0
124217.25 82
14 log file switch completion   6  0
250   41.667 72
14 log file sync4  0
6115.25 28
14 db file sequential read  7  0
1   .142857143  1
14 db file scattered read 164  0
152   .926829268  5
14 db file single write 2  0
1   .5  1
14 file identify4  0
00  0
14 file open6  0
00  0
14 SQL*Net message to client   41  0
00  0
14 SQL*Net message from client 40  0
67829 1695.725  19952


Dan Fink
-Original Message-
Sent: Tuesday, November 26, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L


Oracle says that when a file autoextends, there is a slight delay. Does anyone know 
which Oracle WAIT statistic that would appear under?
  We have been using autoextend on OLTP production tables for awhile now, and the 
results have been satisfactory. This is an ERP system, so the critical performance 
time is at month-end. Some of the developers are concerned that table autoextending 
may slow batch programs, and suggesting that I should determine which tables are 
likely to autoextend during month-end and add storage beforehand. I would like to 
ensure that I am fixing a real problem (short on time, like most of you), so I am 
wondering if autoextend was causing a delay, what wait statistic would it show up 
under. Any ideas?

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafa

RE: BBED

[mantfield]

Block Browser Editor

They cannot call it DBED (datablock editor ) because Veritas already has 
first dibs on the acronym (Veritas database edition, which includes qio ad 
cached qio) and allows true direct IO to UFS (Vxfs) without requiring raw 
device, another story though.

Check Metalink, they have some info on it (BBED) . I first heard of this 
indirectly from Jeff Holt (blimin genius if I ever met one).

Ferenc Mantfeld

-Original Message-
From:   Arup Nanda [SMTP:[EMAIL PROTECTED]]
Sent:   Wednesday, November 27, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Oracle OS level security

What is BBED? I never heard of it.






>From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Oracle OS level security
>Date: Tue, 26 Nov 2002 16:09:27 -0800
>MIME-Version: 1.0
>Received: from newsfeed.cts.com ([209.68.248.164]) by
>mc8-f17.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 
Nov
>2002 17:06:39 -0800
>Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com
>(8.9.3/8.9.3) with UUCP id RAA43612;Tue, 26 Nov 2002 17:01:24 -0800 (PST)
>Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050CFBE; 
>Tue, 26 Nov 2002 16:09:27 -0800
>Message-ID: <[EMAIL PROTECTED]>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "K Gopalakrishnan" <[EMAIL PROTECTED]>
>Sender: [EMAIL PROTECTED]
>Errors-To: [EMAIL PROTECTED]
>Organization: Fat City Network Services, San Diego, California
>X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
>Precedence: bulk
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 27 Nov 2002 01:06:39.0079 (UTC)
>FILETIME=[3D590770:01C295B1]
>
>Jared:
>
>Any one with a reasonable knowledge of Oracle Data Storage
>Internals can use the Data block Editor (BBED) to update
>anything in your database without the knowledge of the
>RDBMS kernel auditing mechanisms.
>
>Agreed,BBED is protected by a password in Windoze ports
>and one need to explicitly make the executable in Unix
>ports. But the point here is the hacker can do anything
>using the BBEd and this can be done even while your
>database is up and running !!
>
>What is their take on this kind of attack(!)s?>
>
>
>Best Regards,
>K Gopalakrishnan
>
>
>
>
>-Original Message-
>[EMAIL PROTECTED]
>Sent: Tuesday, November 26, 2002 3:05 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Dear list,
>
>Let me toss a hypothetical situation at you.
>
>Say some auditors looked at some of your primary systems,
>and concluded that they had no assurance that someone with
>admin access to the server had not changed financial information
>to benefit themselves, or to falsify financial records for the
>gain of the company.
>
>Not that they might have any proof that something like that
>had been done, but rather, just not proof that it had *not*
>been done.
>
>I've been pondering this for a bit, and it seems to me that if
>someone had good knowledge of both the OS and the
>database (Oracle), as well as having admin rights on the
>server, there are few things you can do to prevent such a person
>from changing data in the database, and completely
>covering his or her tracks.
>
>The platforms in question are Unix, Windows NT and
>Windows 2000.   I've limited it to those as most database
>systems use one of those, and besides, that's all I know.  :)
>
>Consider what steps you might take to audit unauthorized
>transactions performed by an admin.
>
>Oracle Auditing could be used, but someone with admin
>access to the server and database could easily alter the
>records created by system auditing.
>
>You could create an audit table, using a trigger to audit
>sensitive tables.  A materialized view on a remote database
>could be created on sensitive tables to remotely log all
>actions.
>
>In the case of the audit table, that could easily be disabled,
>and then re-enabled after the nefarious DML had completed.
>
>The materialized views might be more difficult to circumvent.
>
>If the remote end is using a dblink to the server employing a
>password that is *different* than that of it's own account at the
>remote server, it should be impossible for someone to completely
>cover the traces of transactions created to falsify data.
>
>The MV  Logs could be dropped, but without access to the MV's
>at the remote server, the MV's would have to be left in place.
>
>These could be used as a reference to look for unauthorized transactions
>in the primary server.  If this same admin has access to the remote
>server where the MV's are, then this can also be circumvented.
>
>There is also the logs created as when logging in as internal
>or sysdba. ( $ORACLE_HOME/rdms/audit/*.aud )
>
>These can simply be deleted.  Some system could be used to save
>these to a remote server, but it would have to run *very* frequently to
>be effective.
>
>Oracle password files could also be used. While this can pre

RE: Advanced Rep between 9.2.0 and 8.1.7?

[mantfield]

What is performance like ? how many objects in replication group ?

What I am essentially looking for is performance degradation metrics and 
full specs (# users, machine, OS, size of DB, # rows in largest tables, 
rate of inserts / updates) from source for a one-way replication of about 
500 tables and about 3,000 indexes on these tables. You wouldn't happen to 
have any of those metrics, would you ? Would you ? Pretty please ?

Quests touts that Shareplex is so much better than AR, but how much is that 
? Does it justify the price premium which they are not shy on ? Or should I 
be looking at Shadow Base 3, or Omni-Replicator, or just stick with AR and 
slap in an extra CPU ?

Thanks.

Ferenc Mantfeld

-Original Message-
From:   OraCop [SMTP:[EMAIL PROTECTED]]
Sent:   Wednesday, November 27, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: Advanced Rep between 9.2.0 and 8.1.7?

Yes, I am replicating between 8.1.7.3 and 9iR2
without any issues.

 Need help?

OraCop


--- Michael Barger <[EMAIL PROTECTED]> wrote:
> Has anyone heard of or experienced a successful
> implementation of Advanced Replication between an
> 8.1.7 database and a 9.2.0 db?
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Web Hosting - Let the expert host your site
> http://webhosting.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Michael Barger
>   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.com
--
Author: OraCop
  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.com
-- 
Author: mantfield
  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: redo log file setup with mirrored drives

[Stephen Lee]

-Original Message
I believe the forgone conclusion you are talking about is that "mirroring
outside of Oracle MAY result in data loss"  MAY is a very important word.
The multiplexing of redo logs across multiple disks and controllers is a
simple way protect your database from potential failure.

Your position appears to be that hardware mirroring, software mirroring,
RAID hardware, and the controllers feeding them all are infallible.


For those of you who are averse to the acquisition of knowledge through
muscular debate, I trust you know where the DELETE button is.  For the rest
of you 

As far as "MAY" goes, we can take that to any ridiculous extreme you wish to
take it.  The issue is NOT: "The multiplexing of redo logs across multiple
disks and controllers".  The issue is HOW one does this.  Let's get this
back to my original post.  I was responding to the implication that there is
some danger in using hardware mirroring such than one should not use it.

As one who HAS ACTUALLY DONE BOTH and ACTUALLY USES BOTH and HAVE DONE SO
FOR A LONG TIME (have you?) with both DATABASE and NON-DATABASE files, I
felt it necessary to state that notwithstanding whatever armchair academia
is floating around on the topic, I have NEVER experienced a loss with
hardware mirroring;  And have never seen a  reason to imply that the
practice has any inherent dangers.  Does that mean that a problem can never
occur?  Certainly not.  Have we ever had a controller or hard drive fail?
Yes, indeed.  But, have we ever lost a database as a result?  Nope.

Let me turn things around on you and look at Oracle multiplexing.  Has
anyone ever lost a database who was doing Oracle multiplexing?  Sure.  Well
gosh!  I thought this was supposed to keep this from happening.  Why didn't
it?

The previous posts seemed to be totally preoccupied with this apparently
ubiquitous phenomenon of corrupt blocks.  Let me ask you this: How often
does it occur that you run your rman backup, and it detects bad blocks that
your OS missed or Oracle missed and failed to report?  I'm just curious to
know how prevalent these things are.

Another thing that was stated by the original response was that there was
some performance benefit to Oracle doing the multiplexing -- that Oracle
somehow "optimizes" the process.  In the case of software mirroring by the
OS, this is a dubious statement.  In the case of hardware mirroring, the
statement is patently false and is the main reason why one would use
hardware mirroring -- because performance demands on the system require it.

Let's take this performance thing a little further.  As we have read in many
posts to the list, we even do such reckless and unthinkable things (at least
it was a few years ago) as allow storage arrays to cache our writes ... even
our redo writes (lions, tigers, and bears, oh my!) because performance
demands require it.  Now, you can peruse the database literature and find an
abundance of text on what a hideously EVIIL practice this is.  But we do
it anyway.  And, saints preserve us!  We don't have a landscape littered
with lost databases.

As one who has never lost a file of any kind to hardware or software
mirroring (well ... except for the early releases of Veritas on the Motorola
88K system where Veritas was a complete abortion and worse than nothing at
all) I am going to go with my own considerable experience on the subject.
If you wish to quote chapter and verse from this doc or that doc, that's
great.  But I'm going to go with what I have actually seen tempered by any
tangible, objective, hard evidence I come across.

Now for those who are into this "worst scenario" thing let me ask you: What
if I put your storage array between a 30HP air conditioning blower moter and
a spot welder, and run a couple of paint shakers on top of the array to
boot.  What will your vaunted Oracle multiplexing do for you then?  Huh?
Well, smarty pants, I'm waiting!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Oracle OS level security

[Arup Nanda]

What is BBED? I never heard of it.







From: "K Gopalakrishnan" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: Oracle OS level security
Date: Tue, 26 Nov 2002 16:09:27 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc8-f17.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Tue, 26 Nov 
2002 17:06:39 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id RAA43612;Tue, 26 Nov 2002 17:01:24 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050CFBE; 
Tue, 26 Nov 2002 16:09:27 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: "K Gopalakrishnan" <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 27 Nov 2002 01:06:39.0079 (UTC) 
FILETIME=[3D590770:01C295B1]

Jared:

Any one with a reasonable knowledge of Oracle Data Storage
Internals can use the Data block Editor (BBED) to update
anything in your database without the knowledge of the
RDBMS kernel auditing mechanisms.

Agreed,BBED is protected by a password in Windoze ports
and one need to explicitly make the executable in Unix
ports. But the point here is the hacker can do anything
using the BBEd and this can be done even while your
database is up and running !!

What is their take on this kind of attack(!)s?>


Best Regards,
K Gopalakrishnan




-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, November 26, 2002 3:05 PM
To: Multiple recipients of list ORACLE-L


Dear list,

Let me toss a hypothetical situation at you.

Say some auditors looked at some of your primary systems,
and concluded that they had no assurance that someone with
admin access to the server had not changed financial information
to benefit themselves, or to falsify financial records for the
gain of the company.

Not that they might have any proof that something like that
had been done, but rather, just not proof that it had *not*
been done.

I've been pondering this for a bit, and it seems to me that if
someone had good knowledge of both the OS and the
database (Oracle), as well as having admin rights on the
server, there are few things you can do to prevent such a person
from changing data in the database, and completely
covering his or her tracks.

The platforms in question are Unix, Windows NT and
Windows 2000.   I've limited it to those as most database
systems use one of those, and besides, that's all I know.  :)

Consider what steps you might take to audit unauthorized
transactions performed by an admin.

Oracle Auditing could be used, but someone with admin
access to the server and database could easily alter the
records created by system auditing.

You could create an audit table, using a trigger to audit
sensitive tables.  A materialized view on a remote database
could be created on sensitive tables to remotely log all
actions.

In the case of the audit table, that could easily be disabled,
and then re-enabled after the nefarious DML had completed.

The materialized views might be more difficult to circumvent.

If the remote end is using a dblink to the server employing a
password that is *different* than that of it's own account at the
remote server, it should be impossible for someone to completely
cover the traces of transactions created to falsify data.

The MV  Logs could be dropped, but without access to the MV's
at the remote server, the MV's would have to be left in place.

These could be used as a reference to look for unauthorized transactions
in the primary server.  If this same admin has access to the remote
server where the MV's are, then this can also be circumvented.

There is also the logs created as when logging in as internal
or sysdba. ( $ORACLE_HOME/rdms/audit/*.aud )

These can simply be deleted.  Some system could be used to save
these to a remote server, but it would have to run *very* frequently to
be effective.

Oracle password files could also be used. While this can prevent
someone from logging in as SYS or SYSTEM while in place, all it
takes is a change to init.ora, and a database bounce to fix that.

Make your bogus data changes, change the init.ora back and
bounce the database again.

A somewhat clever person could set this up to automatically
take place the next time the DB is bounced.

The conclusion I have come to is that the only effective method
that could be used to create an audit trail for such a scenario is
to create Materialized Views on sensitive tables, and create them
on a server that admins are guaranteed to not have access to.

Of course, I may be missing something.  I'm not always one to
catch all the details right off.  Input, comments, suggestions, far
out ideas are all welcome.

If 

RE: Imp of all users tables

[Stephen Lee]

-Original Message-

imp system/pwd fromuser=david touser=david tables=a,b,c,... ignore=y
file=expdat.dmp

How can I do all (and not a full=y) of the 544 tables for one user at one
time?


Leave out the "tables=".  Then it will default to all the tables of that
user.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Oracle OS level security

[Rachel Carmichael]

jared,

no answers... but I did read to the end. Thank YOU, you've given me a
lot to think about and to talk to our hosting company about.

What really scares me is that I have no way of auditing the hosting
company who have total access to production.

Rachel

--- [EMAIL PROTECTED] wrote:
> Dear list,
> 
> Let me toss a hypothetical situation at you.
> 
> Say some auditors looked at some of your primary systems,
> and concluded that they had no assurance that someone with
> admin access to the server had not changed financial information
> to benefit themselves, or to falsify financial records for the
> gain of the company.
> 
> Not that they might have any proof that something like that 
> had been done, but rather, just not proof that it had *not*
> been done.
> 
> I've been pondering this for a bit, and it seems to me that if
> someone had good knowledge of both the OS and the 
> database (Oracle), as well as having admin rights on the
> server, there are few things you can do to prevent such a person
> from changing data in the database, and completely 
> covering his or her tracks.
> 
> The platforms in question are Unix, Windows NT and
> Windows 2000.   I've limited it to those as most database
> systems use one of those, and besides, that's all I know.  :)
> 
> Consider what steps you might take to audit unauthorized
> transactions performed by an admin.
> 
> Oracle Auditing could be used, but someone with admin 
> access to the server and database could easily alter the 
> records created by system auditing.
> 
> You could create an audit table, using a trigger to audit
> sensitive tables.  A materialized view on a remote database
> could be created on sensitive tables to remotely log all
> actions.
> 
> In the case of the audit table, that could easily be disabled,
> and then re-enabled after the nefarious DML had completed.
> 
> The materialized views might be more difficult to circumvent.
> 
> If the remote end is using a dblink to the server employing a 
> password that is *different* than that of it's own account at the 
> remote server, it should be impossible for someone to completely
> cover the traces of transactions created to falsify data.
> 
> The MV  Logs could be dropped, but without access to the MV's
> at the remote server, the MV's would have to be left in place. 
> 
> These could be used as a reference to look for unauthorized
> transactions
> in the primary server.  If this same admin has access to the remote
> server where the MV's are, then this can also be circumvented.
> 
> There is also the logs created as when logging in as internal 
> or sysdba. ( $ORACLE_HOME/rdms/audit/*.aud )
> 
> These can simply be deleted.  Some system could be used to save
> these to a remote server, but it would have to run *very* frequently
> to
> be effective.
> 
> Oracle password files could also be used. While this can prevent
> someone from logging in as SYS or SYSTEM while in place, all it
> takes is a change to init.ora, and a database bounce to fix that.
> 
> Make your bogus data changes, change the init.ora back and 
> bounce the database again.
> 
> A somewhat clever person could set this up to automatically
> take place the next time the DB is bounced.
> 
> The conclusion I have come to is that the only effective method 
> that could be used to create an audit trail for such a scenario is
> to create Materialized Views on sensitive tables, and create them
> on a server that admins are guaranteed to not have access to.
> 
> Of course, I may be missing something.  I'm not always one to 
> catch all the details right off.  Input, comments, suggestions, far
> out ideas are all welcome.
> 
> If you've read this far, thanks!
> 
> Jared
> 
> 
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> 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).
> 


__
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.com
-- 
Author: Rachel Carmichael
  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 'L

Re: RE: LGWR using lots of CPU time, low CPU usage

[chao_ping]

VIVEK_SHARMA,
Can i know how did you get your result of oversizing the 
session_cached_cursors do harm to performance? My applications do a lot of softparse 
with pro*C and i used session_cached_cursors=200 in my db. I want to know How did you 
find it out and can you share your experience?
And another add on: lgwr using a lot of cpu time,low cpu usage, does it mean 
that it look like my profile? I think It is because lgwr is consistantly using cpu , 
and the database have been up for a long time.So, from ps/top, the total cpu is 
high,but cpu usage is low?
main-db1# /usr/ucb/ps -aux|grep ora_ |grep -v grep |sort +8nr

oracle1078  0.1 46.857277125697408 ?S   Oct 30 220:00 ora_lgwr_biddb
oracle1076  0.1 46.857317205701200 ?S   Oct 30 92:37 ora_dbw0_biddb
oracle1086  0.0 46.957328325709560 ?S   Oct 30 47:02 ora_snp0_biddb
oracle1088  0.0 46.957335925710896 ?S   Oct 30 25:04 ora_snp1_biddb
oracle1094  0.0 46.857275685696760 ?S   Oct 30 20:54 ora_arc0_biddb
oracle2662  0.0 46.857275685697472 ?S   Oct 30 20:20 ora_arc2_biddb
oracle1597  0.0 46.857275685697456 ?S   Oct 30 19:42 ora_arc1_biddb
oracle1092  0.0 46.957323765709312 ?S   Oct 30 17:19 ora_snp3_biddb
oracle1090  0.0 46.957344965709648 ?S   Oct 30 10:23 ora_snp2_biddb
oracle1096  0.0 47.257831605745720 ?S   Oct 30  8:22 ora_p000_biddb
oracle1101  0.0 47.257781605743520 ?S   Oct 30  7:36 ora_p002_biddb
oracle1098  0.0 47.257781765743904 ?S   Oct 30  6:34 ora_p001_biddb
oracle1103  0.0 47.257781525743576 ?S   Oct 30  6:42 ora_p003_biddb
oracle1080  0.0 46.857277125697440 ?S   Oct 30  4:21 ora_ckpt_biddb
oracle1107  0.0 47.157770005741416 ?S   Oct 30  4:18 ora_p005_biddb
oracle1105  0.0 47.057646325730624 ?S   Oct 30  3:20 ora_p004_biddb
oracle1109  0.0 47.157729045736024 ?S   Oct 30  2:25 ora_p006_biddb
oracle  0.0 47.157728485735952 ?S   Oct 30  2:28 ora_p007_biddb
oracle1074  0.0 46.857268085698184 ?S   Oct 30  0:00 ora_pmon_biddb
oracle1082  0.0 46.857259365700096 ?S   Oct 30  0:59 ora_smon_biddb
oracle1084  0.0 46.857258165699392 ?S   Oct 30  0:31 ora_reco_biddb





Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(Chinese Oracle User Group)

=== 2002-11-26 11:25:00 ,you wrote£º===

>In some of our benchmarks with our hybrid application on Oracle 8.1.7 , Oversizing 
>session_cached_cursors would HARM performance greatly . Our Optimal Value is 50
>
>
>-Original Message-
>Sent: Wednesday, November 27, 2002 12:20 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Deborah,
>
>First, don't remove Oracle's RedoLog duplexing, you may regret about it
>later (see recent thread on this issue).
>
>Second, if what you are telling ("logs are about 100 MB in 2 groups of 20
>members each") is accurate, then this is your main problem.  If you have
>your log switches on avg 2.5 per day, change your RedoLog configuration to
>be: 3 (or 4) groups, 3 members each (if you can put them on separate
>"physical" devices, if not - 2 members should suffice), and you can make
>them smaller, like 50Mb (or even smaller).  You will have more log switches
>per day, but it's perfectly fine as long, as don't have them every 5 min.
>
>And "old school" is still right about not putting RedoLogs onto RAID5.
>
>Igor Neyman, OCP DBA
>[EMAIL PROTECTED]
>
>
>
>- Original Message -
>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>Sent: Tuesday, November 26, 2002 1:00 PM
>
>
>> We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
>> on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>>
>> Periodically throughout the day the LGWR background process clocks 20+
>> minutes of CPU time while actual CPU usage is quite low. I ran a statspack
>> report and for a 45-minute period that included the slow LGWR process.
>>
>> The top 5 timed events in my 45-minute report are:
>>
>> CPU time 1,295 60.41
>> db file sequential read 392,516 341 15.91
>> db file scattered read 70,245 168 7.85
>> log file sync 26,916 133 6.22
>> library cache pin 22 59 2.76
>>
>> (Now that the top 5 is "timed" events, 3 spots almost always include CPU
>> and the db file reads, so I only get two other events, usually log file
>> sync, sometimes enqueue or latch free.)
>>
>> Statspack also shows the log file parallel write had 28,589 timeouts in
>> that 45 minute period--rather typical for us.
>>
>> I have session_cached_cursors set to 150.
>>
>> I am considering the following:
>>
>> 1. Removing my own redo log duplexing (mirroring) since redo logs are on
>> the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
>> My sysadmin talked to the sun engineer yesterday and he said this is
>> "old school" thinking that redo logs should n

Re:The future DBAs?

[Rachel Carmichael]

not my personal favorite thing to be doing.. I like making the database
work from the model :)

And Dave Hay is not only a modelling expert, he's an excellent magician
and makes REALLY cool origami animals. I have several from him :)


--- [EMAIL PROTECTED] wrote:
> Personally, I like Data Architecture.
> 
> And data modeling.  I never could get enough
> of that.  The hard part is explaining to people that
> don't quite understand the concept.
> 
> Dave Hay rules!
> http://www.amazon.com/exec/obidos/tg/detail/-/0932633293
> 
> Being the sole DBA for the company, I don't get
> nearly enough opportunities for this anymore, and
> don't have the time for much of it anyway.
> 
> Jared
> 
> 
> 
> 
> 
> [EMAIL PROTECTED]
> Sent by: [EMAIL PROTECTED]
>  11/26/2002 10:04 AM
>  Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:Re:The future DBAs?
> 
> 
> Well, I give MicroSlop pretty poor grades for predicting the future
> and
> Monster.com is absolutely useless (naw make that less than) at job
> stuff 
> in
> general.  I will agree with the person who wrote the article on one
> point. 
>  The
> job of being a DBA is changing and we all need to remain flexible to 
> remain
> useful in the marketplace.  That in some cases means spreading our
> wings 
> from
> the historical role of DBA.  We may need to become part time (or full
> 
> time) data
> architects, reporting tool experts, etc...  But in the end, I don't
> see us
> degrading to the level of an order entry clerk nor order entry clerks
> 
> upgrading
> to DBA's.  As usual the MicroSlop propaganda machine is at work
> again. 
> 
> Dick Goulet
> 
> Reply Separator
> Author: "Arup Nanda" <[EMAIL PROTECTED]>
> Date:   11/25/2002 5:48 PM
> 
> Fellow DBAs and other DBA wannabes,
> 
> Ever wondered the best path into a DBA career? Microsoft offers a 
> brilliant 
> way. MSN Careers at
> http://editorial.careers.msn.com/articles/nofuture/ 
> suggests some jobs are effectively dead, like farmers and sewing
> machine 
> operators and how the experts in that field can progress to the next 
> logical 
> career move. Guess which profession's logical career move is database
> 
> administrator? See the excerpt from the webpage here in the
> attachment as 
> a 
> picture.
> 
> I just couldn't resist posting it here. May be they are referring to
> SQL 
> Server DBAs?
> 
> Arup Nanda
> 
> 
> 
> 
> 
> 
> 
> _
> The new MSN 8: advanced junk mail protection and 2 months FREE* 
> http://join.msn.com/?page=features/junkmail
> 
>  
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> 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).
> 


__
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.com
-- 
Author: Rachel Carmichael
  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: Using RECYCLE pool?

[mantfield]

Hi Dennis

I try to not think of the pool names as being descriptive of what they 
should be allocated for. I regard them as pool 1 (default), of which I can 
configure two other pools, (pool 2 and pool 3).

For Siebel applications (probably works similar for PSOFT [Joe, you in on 
this thread ?] and SAP), knowing the application and what it does, the 
repository tables, like the tables that define position based access, 
views, responsibilities, position relationships (team-based visibility in 
Siebel), broadcast messages, workflow rules and rule items, I put them into 
a separate smaller but very frequently accessed pool, knowing they are 
going to get hit at least a few times every minute with a few hundred users 
logged on.

Then I try to identify those tables that DO get FTS, and if I cannot tune 
the query by placing relevant indices (sometimes it is better to have FTS 
than large index range scan to reduce logical IO, the big performance 
killer), put these into a separate pool, and leave the rest in default. 
Alternatively, the hot smaller tables go into one pool, the indices in 
another and the rest of the tables stay in default. There are various 
tricks for this. Oracle 9 makes things easier because you can identify 
which indexes are beig used, and then not waste your time with the others.

Just remember, you will get much further distance from reducing logical 
IO's than playing with various buffer pools, though there is a minimal 
argument for playing with buffer pools, once logical IO's have been 
decreased.

Real-life example : using Siebel EIM, by placing EIM tables into separate 
buffer pools, I saw a small advantage, say 5 - 10 % in buffer cache latch 
reduction and more efficient use of cached IO. But after tuning the 
structures so that I reduced logical IO's, I saw a 2000% throughput 
improvement of EIM, to the amazement of all skeptics on the project (also 
bumped up initrans and ran multiple parallel streams). So prioritize where 
you spend your tuning efforts. Reduction of logical IO = biggest bang for 
buck !

Getting off my soapbox now. Lots to do.
Ciao :

Ferenc Mantfeld

-Original Message-
From:   DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]]
Sent:   Wednesday, November 27, 2002 8:30 AM
To: Multiple recipients of list ORACLE-L
Subject:Using RECYCLE pool?

Is anyone using the Oracle RECYCLE buffer pool? What was your criteria to
select tables? The application I am considering RECYCLE for doesn't perform
table scans, so that eliminates one common suggestion.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: mantfield
  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: Advanced Rep between 9.2.0 and 8.1.7?

[OraCop]

Yes, I am replicating between 8.1.7.3 and 9iR2
without any issues.

 Need help?

OraCop


--- Michael Barger <[EMAIL PROTECTED]> wrote:
> Has anyone heard of or experienced a successful
> implementation of Advanced Replication between an
> 8.1.7 database and a 9.2.0 db?
> 
> 
> 
> __
> Do you Yahoo!?
> Yahoo! Web Hosting - Let the expert host your site
> http://webhosting.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Michael Barger
>   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.com
-- 
Author: OraCop
  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: Mass updates to production tables (NULL to non-NULL)

[Larry Elkins]

Yes you can, but I still like to see it in action with my own eyes to verify
;-) Another cool thing that came of all this is that I wasn't aware of the
"select * from table(dbms_xplan.display());" used by 9iR2. I play with the
9i's off and on when I have time, but not dealing with 9iR2 (or R1) on a
daily basis I had no idea about the DBMS_XPLAN package in 9iR2.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Deshpande,
> Kirti
> Sent: Tuesday, November 26, 2002 1:04 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Mass updates to production tables (NULL to non-NULL)
>
>
> Larry,
>
> What I found was the explain plan in 9iR2 will tell you if the
> DML is getting parallelized.
>
> Here is what I see:
> SQL> alter session enable parallel dml;
> Session altered.
> SQL> explain plan for
>   2  update /*+ parallel (KED, 4) */ KED
>   3  set secondary = 'X';
> Explained.
> SQL> @?/rdbms/admin/utlxplp
> PLAN_TABLE_OUTPUT
> --
> -
> --
> -
> | Id  | Operation|  Name   | Rows  | Bytes | Cost
>  |  TQ  |IN-OUT| PQ Distrib |
> --
> -
> |   0 | UPDATE STATEMENT | |  1520K|  2969K|
> 707 |  |  ||
> |   1 |  UPDATE  | KED |   |   |
>  |Q8,00 | P->S | QC (RANDOM)|
> |   2 |   TABLE ACCESS FULL  | KED |  1520K|  2969K|
> 707 |Q8,00 | PCWP ||
> --
> -
>
> In 9i R1 I did not see any TQ (memory queue), IN-OUT and PQ
> Distribution information for UPDATE operation, indicating that it
> was carried on serially, however, the TABLE ACCESS FULL operation
> was performed in parallel as expected.
>
> Regards,
>
> - Kirti

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  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: [Q] ORACLE 9i remote export "insufficient previlege"?

[OraCop]

Very true... But * IF * John's solution does not work
then reason for mentioned error is because U probably
ran "CATEXP.sql" AGAIN after running CATPROC.SQL.
Anyway to correct this simply run
"%oracle_home%\rdbms\admin\CATRM.SQL" script connected
as SYS or INTERNAL.  

If CATEXP.SQL was rerun after CATPROC.SQL (this runs
CATRM.SQL) the  modification made to the
EXP_FULL_DATABASE role by CATRM.SQL no longer exist 
since CATEXP.SQL drops and creates the
EXP_FULL_DATABASE role.The privileges granted to
the role by CATRM.SQL are necessary to export the 
resource manager objects. 

OraCop

--- John Thomas <[EMAIL PROTECTED]> wrote:
> I think you need a password file on the 9i server to
> connect remotely 
> with SYSDBA privilege.
> 
> Go to $ORACLE_HOME/dbs and use orapwd. You may need
> to set an init.ora 
> parameter along the lines of
> remote_password_exclusive or shared 
> depending upon whether you want multiple users (or
> was it databases?) 
> using the same file to log in remotely as well...
> 
> Cheers,
> 
> John Thomas
> 
> In message
> <[EMAIL PROTECTED]>, dist
> cash 
> <[EMAIL PROTECTED]> writes
> >I am doing the ORACLE 9iR2 database test and tried
> to export form a 8i
> >computer to 9iR2 server.  I got "ORA-01031:
> insufficient privileges".
> >
> >my export parfile on 8i server is:
> >
> >
> >userid='sys/passwd1@db92 as sysdba'
> >file=/tmp/exp_db8i_full.dmp
> >buffer=6400
> >compress=n
> >grants=y
> >indexes=y
> >rows=y
> >feedback=1
> >constraints=y
> >consistent=y
> >log=exp_db8i_full.log
> >full=y
> >
> >
> >
> >
> >I got:
> >
> >Export: Release 8.1.7.2.0 - Production on Tue Nov
> 26 14:02:57 2002
> >
> >(c) Copyright 2000 Oracle Corporation.  All rights
> reserved.
> >
> >
> >EXP-00056: ORACLE error 1031 encountered
> >ORA-01031: insufficient privileges
> >Username:
> >
> >
> >Does anyone know why??
> >
> >Thanks.
> >
>
>_
> >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.com
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: John Thomas
>   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.com
-- 
Author: OraCop
  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: Recipe for application design to run on RAC

[Cary Millsap]

If two or more RAC instances will be trying to cache the same data
blocks, then this causes the performance problems that you'll see show
up as lots of time spent on the event called "global cache cr request".
If you can partition your application so that RAC nodes don't have to
share blocks very often through the cache fusion mechanism, then your
system will scale a lot better.


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

Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
Sent: Tuesday, November 26, 2002 3:34 PM
To: Multiple recipients of list ORACLE-L

Dear List,

Number of times I've seen that one of prerequsites for
switching from single node DB to OPS/RAC is to have an
application specifically designed / architectured to
run on RAC.
Can somebody elaborate? Is it something "visible" on 
ERD? That is by looking at the model can RAC guru tell
that it wouldn't work well on RAC?
Or put it another way can one conclude based on the
ERD that app was modeled to run on RAC?

What's the recepie for app design for RAC?

TIA

__ 
Post your free ad now! http://personals.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Boris Dali
  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.com
-- 
Author: Cary Millsap
  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: redo log file setup with mirrored drives

[Khedr, Waleed]

I agree. And this is why Oracle has the capability to manage many redo log
members in the same group and many copies of the control file. It does not
offer the same for regular data files.

Regards,
Waleed

-Original Message-
Sent: Tuesday, November 26, 2002 6:05 PM
To: Multiple recipients of list ORACLE-L


"While all this is true, this is all based on the forgone conclusion that
mirroring outside Oracle will result in file loss.  It is that conclusion
with which I disagree."

I believe the forgone conclusion you are talking about is that "mirroring
outside of Oracle MAY result in data loss"  MAY is a very important word.
The multiplexing of redo logs across multiple disks and controllers is a
simple way protect your database from potential failure.  It is simply
irresponsible to dismiss it out of hand.  Sure you might cite performance
concerns, but for most databases in most enterprises redo log multiplexing
does not constitute a performance bottleneck.

Your position appears to be that hardware mirroring, software mirroring,
RAID hardware, and the controllers feeding them all are infallible.
Multiplexing redo logs is simply a form of insurance, and should be
considered a default element of Oracle database design.

Steve

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve McClure
  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.com
-- 
Author: Khedr, Waleed
  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: Urgent: ORA-02067 transaction or savepoint rollback required (Distributed transaction)

[OraCop]

Hello,
Are u performing any exception handeling in Ur 
code? Try to capture oracle err mesg using 
"USER DEFINED EXCEPTIONS". For more detail look 
into "DBMS_ERROR_CODE" and "DBMS_ERROR_TEXT "

OraCOP

--- Charu Joshi <[EMAIL PROTECTED]> wrote:
> Hi Arup,
> 
> Thanks for the reply.
> 
> I have checked the trigger code thoroughly and
> surely it's not a mutating
> trigger. Nor is there any cascade effect or the
> trace files would have shown
> the statements within that trigger.
> 
> There is some other error occurring, but we are not
> able to trap it because
> the front-end application just doesn't do error
> trapping.
> 
> Is there any way to see the error occurred within
> another session? We are
> checking trace logs but they don't show any error.
> 
> Thanks & regards,
> Charu
> 
> -Original Message-
> Sent: Thursday, November 21, 2002 3:32 PM
> To: Multiple recipients of list ORACLE-L
> required (Distributed transaction)
> 
> Charu,
> 
> I guess the error you are getting is "Table or
> Trigger is mutating". and
> perhaps you are using an after insert row trigger.
> This is a common and
> expected problem. There are ways to get around it.
> You have not mentioned
> the version of Oracle; I would assume 8.1.7 or
> later.
> 
> Inside an after insert row trigger, you can not
> operate on the table on
> which trigger is defined. for instance in your case
> the trigger (say,
> TRA_LT) on the table LT can't insert into LT inside
> it. However if you
> define the same in a BEFORE INSERT row trigger, it's
> allowed.
> 
> Caution, though, the insert through the trigger will
> fire the BEFORE INSERT
> trigger too, which in turn will insert a row in LT
> and then the trigger
> fires again..a vicious cycle. Fortuanately Oracle
> breaks the cascading
> trigger after 17 atetmpts. So, you need to have a
> limiting condition while
> defining the trigger WHEN (NEW.STATUS='OLD') or
> similar to make sure you can
> differentiate between the trigger inserted and the
> user inserted rows.
> 
> Finally, if you insert into the table LT using a
> construct like INSERT INTO
> LT VALUES (...) it works; but if you use INSERT INTO
> LT SELECT .. FROM
> ANOTHER_TABLE, it does not. this is not documented
> anywhere; I found this by
> accident.
> 
> Hope this helps.
> 
> Arup Nanda
> www.proligence.com
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Thursday, November 21, 2002 9:18 AM
> (Distributed transaction)
> 
> > Gurus,
> >
> > Desperately need some ideas to solve this one.
> >
> > Following is the sequence of events as it happens:
> >
> > 1. Front-end application (VC++) queries from a
> view V. V fetches data from
> a
> > remote database RD via a database link.
> > 2. Front-end inserts a row in a local table LT.
> > 3. The Insert trigger on this table queries view V
> to take a final stock.
> > 4a. If conditions match, the trigger inserts in
> remote table RT on RD. NO
> > PROBLEM in condition.
> > 4b. If conditions don't match, the front-end
> application tries to insert
> > another row in LT. The trigger starts again and
> tries to insert row in RT.
> > At this point we SEEM to get the error.
> >
> > We can't get anybody to debug the front-end
> application. We tried setting
> > SQL_TRACE TRUE, and the only thing made clear was
> that the trigger didn't
> go
> > beyond trying to insert into RT.
> >
> > We tried replicating the whole scenario by
> executing each and every
> > statement that the front-end fires through
> SQL*Plus as a script, and it
> > doesn't give any error.
> >
> > I realize that I have given only a fraction of the
> whole information, but
> > what is the best way to debug this situation?
> >
> > Are there any do's don'ts in distributed
> transactions which our code may
> not
> > be following?
> >
> > Thanks in advance,
> > Charu
> >
> 
>
*
> Disclaimer
> 
> This message (including any attachments) contains 
> confidential information intended for a specific 
> individual and purpose, and is protected by law. 
> If you are not the intended recipient, you should 
> delete this message and are hereby notified that 
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it, 
> is strictly prohibited.
> 
>
*
> Visit us at http://www.mahindrabt.com
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Charu Joshi
>   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 cont

RE: LGWR using lots of CPU time, low CPU usage

[Cary Millsap]

Maybe the following observation is relevant, maybe not...

Fyi, each 'log file sync' event duration includes the time consumed by
the following actions:

A. The delay between when the foreground process posts LGWR and when
LGWR wakes up and gets busy.
B. The duration that LGWR consumes doing its job.
C. The delay between when LGWR finishes and when the foreground clears
the CPU runqueue awaiting its return to User Mode.

The reflex action is to assume that B is the cause of long 'log file
sync' durations. However, because a process that post()s gets a low
priority in the CPU runqueue, it's often C that's the culprit. And C can
take a long time if the CPU runqueue is long, regardless of whether
there's an I/O latency issue or not.


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

Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
Waleed
Sent: Tuesday, November 26, 2002 5:05 PM
To: Multiple recipients of list ORACLE-L

Slow I/O will not increase the CPU time for the lgwr, since this will be
I/O
wait time. 
Did you try to truss the lgwr process?
What is the size of log_buffer?

Waleed

-Original Message-
Sent: Tuesday, November 26, 2002 3:35 PM
To: Multiple recipients of list ORACLE-L


Thanks for this paper--I will share it with our Sun engineer.

BTW, to clarify, I have 20 redo groups with 2 members each...I think I'm

saying it right:

LOGFILE Group 1 ('/disk1/log01.log',
  '/disk2/log01.log') size 100M,
 Group 2 ('/disk1/log02.log',
  '/disk2/log02.log') size 100M,
...
 Group 20 ('/disk1/log20.log',
   '/disk2/log20.log') size 100M

The oldest log in the group is dated yesterday; but more than half have 
today's date.  I had one period today where there were 4 switches less
than 
a minute apart!!

Debi

At 11:15 AM 11/26/2002 -0800, you wrote:
>So what is discussed in this paper is outdated alreadyuh! 
>www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages
13-14 
>talk about Oracle Redo Logs.
>
>As a first attempt, I would consider reducing the number of log members

>(from 20 to 4, or even 3) than removing them altogether. This will be
of 
>some help right away. But monitor further and decide if more Groups are

>needed to help archiver process.
>
>Do not change multiple things at the same time.
>
>Good Luck,
>
>- Kirti
>
>-Original Message-
>Sent: Tuesday, November 26, 2002 12:00 PM
>To: Multiple recipients of list ORACLE-L
>
>
>We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128
MB
>on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
>Periodically throughout the day the LGWR background process clocks 20+
>minutes of CPU time while actual CPU usage is quite low. I ran a
statspack
>report and for a 45-minute period that included the slow LGWR process.
>
>The top 5 timed events in my 45-minute report are:
>
>CPU time 1,295 60.41
>db file sequential read 392,516 341 15.91
>db file scattered read 70,245 168 7.85
>log file sync 26,916 133 6.22
>library cache pin 22 59 2.76
>
>(Now that the top 5 is "timed" events, 3 spots almost always include
CPU
>and the db file reads, so I only get two other events, usually log file
>sync, sometimes enqueue or latch free.)
>
>Statspack also shows the log file parallel write had 28,589 timeouts in
>that 45 minute period--rather typical for us.
>
>I have session_cached_cursors set to 150.
>
>I am considering the following:
>
>1. Removing my own redo log duplexing (mirroring) since redo logs are
on
>the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
>My sysadmin talked to the sun engineer yesterday and he said this is
>"old school" thinking that redo logs should not be on RAID5. He said
>because the RAID controller caches to memory all IO requests from
>the CPUs, all physical writes to disk are done behind the scenes
>(known as writebehind). He says the system is NOT waiting for IO.
>
>2. Increasing redo log size (again). For the most part, log switches
>average 2.5 per day, although there were 20 times in the last month of
3-7
>switches in a half hour. My logs are about 100 MB in 2 groups of 20
members
>each.
>
>3. Upping the session_cached_cursors to ? (in response to the library
cache
>pin event).
>
>Or is there a better option I'm overlooking?
>
>I would appreciate some advise on the best approach to resolve the slow
>LGWR process, especially your thoughts on option 1.
>
>Thanks,
>Debi
>Deborah Lorraine, DBA
>University of California, Davis
>[EMAIL PROTECTED]
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deborah Lorraine
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>---

RE: Oracle OS level security

[K Gopalakrishnan]

Jared:

Any one with a reasonable knowledge of Oracle Data Storage
Internals can use the Data block Editor (BBED) to update
anything in your database without the knowledge of the
RDBMS kernel auditing mechanisms.

Agreed,BBED is protected by a password in Windoze ports
and one need to explicitly make the executable in Unix
ports. But the point here is the hacker can do anything 
using the BBEd and this can be done even while your 
database is up and running !!

What is their take on this kind of attack(!)s?>


Best Regards,
K Gopalakrishnan

 


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, November 26, 2002 3:05 PM
To: Multiple recipients of list ORACLE-L


Dear list,

Let me toss a hypothetical situation at you.

Say some auditors looked at some of your primary systems,
and concluded that they had no assurance that someone with
admin access to the server had not changed financial information
to benefit themselves, or to falsify financial records for the
gain of the company.

Not that they might have any proof that something like that 
had been done, but rather, just not proof that it had *not*
been done.

I've been pondering this for a bit, and it seems to me that if
someone had good knowledge of both the OS and the 
database (Oracle), as well as having admin rights on the
server, there are few things you can do to prevent such a person
from changing data in the database, and completely 
covering his or her tracks.

The platforms in question are Unix, Windows NT and
Windows 2000.   I've limited it to those as most database
systems use one of those, and besides, that's all I know.  :)

Consider what steps you might take to audit unauthorized
transactions performed by an admin.

Oracle Auditing could be used, but someone with admin 
access to the server and database could easily alter the 
records created by system auditing.

You could create an audit table, using a trigger to audit
sensitive tables.  A materialized view on a remote database
could be created on sensitive tables to remotely log all
actions.

In the case of the audit table, that could easily be disabled,
and then re-enabled after the nefarious DML had completed.

The materialized views might be more difficult to circumvent.

If the remote end is using a dblink to the server employing a 
password that is *different* than that of it's own account at the 
remote server, it should be impossible for someone to completely
cover the traces of transactions created to falsify data.

The MV  Logs could be dropped, but without access to the MV's
at the remote server, the MV's would have to be left in place. 

These could be used as a reference to look for unauthorized transactions
in the primary server.  If this same admin has access to the remote
server where the MV's are, then this can also be circumvented.

There is also the logs created as when logging in as internal 
or sysdba. ( $ORACLE_HOME/rdms/audit/*.aud )

These can simply be deleted.  Some system could be used to save
these to a remote server, but it would have to run *very* frequently to
be effective.

Oracle password files could also be used. While this can prevent
someone from logging in as SYS or SYSTEM while in place, all it
takes is a change to init.ora, and a database bounce to fix that.

Make your bogus data changes, change the init.ora back and 
bounce the database again.

A somewhat clever person could set this up to automatically
take place the next time the DB is bounced.

The conclusion I have come to is that the only effective method 
that could be used to create an audit trail for such a scenario is
to create Materialized Views on sensitive tables, and create them
on a server that admins are guaranteed to not have access to.

Of course, I may be missing something.  I'm not always one to 
catch all the details right off.  Input, comments, suggestions, far
out ideas are all welcome.

If you've read this far, thanks!

Jared





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

RE: Autoextend WAIT statistic?

[Fink, Dan]

Dennis,
I did some quick & dirty testing by creating a very small(10M)
datafile with a large(2000m) autoextend clause. On the insert, the session
was waiting on 'file open' for most of the time. When I did a rollback and
reinserted the data, there were no waits (that I saw) on file open.

Interestingly, this wait event does not appear to be accurately
tracked in v$session_event. In v$session_wait the seconds in wait (last
trapped) was 132. In v$session_event, it shows 0. Okay, gurus, why? Am I
missing something in this?

select * from v$session_wait where sid = 14
  SID   SEQ# EVENT
-- --

P1TEXT   P1
P1RAW
 --

P2TEXT   P2
P2RAW
 --

P3TEXT   P3
P3RAW WAIT_TIME SECONDS_IN_WAIT
 --
 -- ---
STATE
---
14322 file open
fib  4327126592
000101EAB640
iov  4327069760
000101E9D840
0 0
00   -1 132
WAITED SHORT TIME

select * from v$session_event where sid = 14
   SID EVENT  TOTAL_WAITS TOTAL_TIMEOUTS
TIME_WAITED AVERAGE_WAIT   MAX_WAIT
-- -- --- --
---  --
14 rdbms ipc reply  4  1
210 52.5205
14 control file sequential read18  0
16   .9 15
14 local write wait 1  0
00  0
14 log buffer space72  0
124217.25 82
14 log file switch completion   6  0
250   41.667 72
14 log file sync4  0
6115.25 28
14 db file sequential read  7  0
1   .142857143  1
14 db file scattered read 164  0
152   .926829268  5
14 db file single write 2  0
1   .5  1
14 file identify4  0
00  0
14 file open6  0
00  0
14 SQL*Net message to client   41  0
00  0
14 SQL*Net message from client 40  0
67829 1695.725  19952


Dan Fink
-Original Message-
Sent: Tuesday, November 26, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L


Oracle says that when a file autoextends, there is a slight delay. Does
anyone know which Oracle WAIT statistic that would appear under?
  We have been using autoextend on OLTP production tables for awhile now,
and the results have been satisfactory. This is an ERP system, so the
critical performance time is at month-end. Some of the developers are
concerned that table autoextending may slow batch programs, and suggesting
that I should determine which tables are likely to autoextend during
month-end and add storage beforehand. I would like to ensure that I am
fixing a real problem (short on time, like most of you), so I am wondering
if autoextend was causing a delay, what wait statistic would it show up
under. Any ideas?

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

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

RE: Autoextend WAIT statistic?

[K Gopalakrishnan]

Dennis:

It depends whether you use Dictionary Management or
Local Management for Extent allocation. If you use 
dictionary management you will see the ST enqueue 
contention in X$KSQST (Kernel Services enQueue Statistic
Types) and if it is Locally Managed tablespace you may
find contention for BitMap Blocks (level1?) in the
V$WAITSTAT.


Best Regards,
K Gopalakrishnan

 


-Original Message-
WILLIAMS
Sent: Tuesday, November 26, 2002 1:30 PM
To: Multiple recipients of list ORACLE-L


Oracle says that when a file autoextends, there is a slight delay. Does
anyone know which Oracle WAIT statistic that would appear under?
  We have been using autoextend on OLTP production tables for awhile now,
and the results have been satisfactory. This is an ERP system, so the
critical performance time is at month-end. Some of the developers are
concerned that table autoextending may slow batch programs, and suggesting
that I should determine which tables are likely to autoextend during
month-end and add storage beforehand. I would like to ensure that I am
fixing a real problem (short on time, like most of you), so I am wondering
if autoextend was causing a delay, what wait statistic would it show up
under. Any ideas?

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  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: LGWR using lots of CPU time, low CPU usage

[Deshpande, Kirti]

Great! Please let us know what Sun Rep has to say. 

- Kirti

-Original Message-
Sent: Tuesday, November 26, 2002 2:35 PM
To: Multiple recipients of list ORACLE-L


Thanks for this paper--I will share it with our Sun engineer.

BTW, to clarify, I have 20 redo groups with 2 members each...I think I'm 
saying it right:

LOGFILE Group 1 ('/disk1/log01.log',
  '/disk2/log01.log') size 100M,
 Group 2 ('/disk1/log02.log',
  '/disk2/log02.log') size 100M,
...
 Group 20 ('/disk1/log20.log',
   '/disk2/log20.log') size 100M

The oldest log in the group is dated yesterday; but more than half have 
today's date.  I had one period today where there were 4 switches less than 
a minute apart!!

Debi

At 11:15 AM 11/26/2002 -0800, you wrote:
>So what is discussed in this paper is outdated alreadyuh! 
>www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14 
>talk about Oracle Redo Logs.
>
>As a first attempt, I would consider reducing the number of log members 
>(from 20 to 4, or even 3) than removing them altogether. This will be of 
>some help right away. But monitor further and decide if more Groups are 
>needed to help archiver process.
>
>Do not change multiple things at the same time.
>
>Good Luck,
>
>- Kirti
>
>-Original Message-
>Sent: Tuesday, November 26, 2002 12:00 PM
>To: Multiple recipients of list ORACLE-L
>
>
>We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
>on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
>Periodically throughout the day the LGWR background process clocks 20+
>minutes of CPU time while actual CPU usage is quite low. I ran a statspack
>report and for a 45-minute period that included the slow LGWR process.
>
>The top 5 timed events in my 45-minute report are:
>
>CPU time 1,295 60.41
>db file sequential read 392,516 341 15.91
>db file scattered read 70,245 168 7.85
>log file sync 26,916 133 6.22
>library cache pin 22 59 2.76
>
>(Now that the top 5 is "timed" events, 3 spots almost always include CPU
>and the db file reads, so I only get two other events, usually log file
>sync, sometimes enqueue or latch free.)
>
>Statspack also shows the log file parallel write had 28,589 timeouts in
>that 45 minute period--rather typical for us.
>
>I have session_cached_cursors set to 150.
>
>I am considering the following:
>
>1. Removing my own redo log duplexing (mirroring) since redo logs are on
>the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
>My sysadmin talked to the sun engineer yesterday and he said this is
>"old school" thinking that redo logs should not be on RAID5. He said
>because the RAID controller caches to memory all IO requests from
>the CPUs, all physical writes to disk are done behind the scenes
>(known as writebehind). He says the system is NOT waiting for IO.
>
>2. Increasing redo log size (again). For the most part, log switches
>average 2.5 per day, although there were 20 times in the last month of 3-7
>switches in a half hour. My logs are about 100 MB in 2 groups of 20 members
>each.
>
>3. Upping the session_cached_cursors to ? (in response to the library cache
>pin event).
>
>Or is there a better option I'm overlooking?
>
>I would appreciate some advise on the best approach to resolve the slow
>LGWR process, especially your thoughts on option 1.
>
>Thanks,
>Debi
>Deborah Lorraine, DBA
>University of California, Davis
>[EMAIL PROTECTED]
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deborah Lorraine
>   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.com
>--
>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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deborah Lorraine
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 htt

Re: LGWR using lots of CPU time, low CPU usage

[John Carlson]

I see there has already been a lot of discussion on this topic.  I would like to throw 
out one more possibility.  It could be related to bug 2564886.  If you read the bug on 
metalink, it probably won't make any sense because it is written for a specific 
customer.  However, I have a similar problem and Oracle has classified my tar as 
related to this bug.  Basically, if you use more than one log_arch_dest occasionally 
one of the archive process will just take forever.  You didn't mention if you were 
using that parameter or if you are using a standby database so it may not apply to 
you.  While oracle is working on this bug, we have disabled the second log_arch_dest 
and we have a script to manually check every minute and copy the archive logs to the 
other destination.  This has helped us.  Maybe it can help you to.

We are on Sun Solaris 7 with 9.2.0.1 but the bug goes back to 9.0.1.3 so it probably 
applies to 9.2.0.2 also.

HTH,
John

>>> [EMAIL PROTECTED] 11/26/02 10:00AM >>>
We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB 
on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.

Periodically throughout the day the LGWR background process clocks 20+ 
minutes of CPU time while actual CPU usage is quite low. I ran a statspack 
report and for a 45-minute period that included the slow LGWR process.

The top 5 timed events in my 45-minute report are:

CPU time 1,295 60.41
db file sequential read 392,516 341 15.91
db file scattered read 70,245 168 7.85
log file sync 26,916 133 6.22
library cache pin 22 59 2.76

(Now that the top 5 is "timed" events, 3 spots almost always include CPU 
and the db file reads, so I only get two other events, usually log file 
sync, sometimes enqueue or latch free.)

Statspack also shows the log file parallel write had 28,589 timeouts in 
that 45 minute period--rather typical for us.

I have session_cached_cursors set to 150.

I am considering the following:

1. Removing my own redo log duplexing (mirroring) since redo logs are on
the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
My sysadmin talked to the sun engineer yesterday and he said this is
"old school" thinking that redo logs should not be on RAID5. He said
because the RAID controller caches to memory all IO requests from
the CPUs, all physical writes to disk are done behind the scenes
(known as writebehind). He says the system is NOT waiting for IO.

2. Increasing redo log size (again). For the most part, log switches 
average 2.5 per day, although there were 20 times in the last month of 3-7 
switches in a half hour. My logs are about 100 MB in 2 groups of 20 members 
each.

3. Upping the session_cached_cursors to ? (in response to the library cache 
pin event).

Or is there a better option I'm overlooking?

I would appreciate some advise on the best approach to resolve the slow 
LGWR process, especially your thoughts on option 1.

Thanks,
Debi
Deborah Lorraine, DBA
University of California, Davis
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Deborah Lorraine
  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.com
-- 
Author: John Carlson
  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).

Imp of all users tables

[Ehresmann, David]

List,

How would I import all of one users tables (544) without listing all of them
in the tables= option?  I am using:

imp system/pwd fromuser=david touser=david tables=a,b,c,... ignore=y
file=expdat.dmp

How can I do all (and not a full=y) of the 544 tables for one user at one
time?

thanks,


David Ehresmann

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ehresmann, David
  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: LGWR using lots of CPU time, low CPU usage

[Stephen Lee]

H. I got to thinking about a previous reply which was while doing
something else:

>I don't think changing the logmembers will do much good
I agree.

> 4 groups, they are on the local drive.
BOOM!!

Then got to thinking  This is not right at all.  I think I was making a
subconscious interpretation based on the context of your usage of the terms.
If your groups look like
GROUP1
redo_01a.dbf_or_log
redo_01b.dbf_or_log
redo_01c.dbf_or_log

GROUP2
redo_02a.dbf_or_log
redo_02b.dbf_or_log
redo_02c.dbf_or_log

etc.

Then, this all this extra writing will definitely incur overhead.

Now, the part about it all being on "the local drive": That still is BOOM!!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: redo log file setup with mirrored drives

[Steve McClure]

"While all this is true, this is all based on the forgone conclusion that
mirroring outside Oracle will result in file loss.  It is that conclusion
with which I disagree."

I believe the forgone conclusion you are talking about is that "mirroring
outside of Oracle MAY result in data loss"  MAY is a very important word.
The multiplexing of redo logs across multiple disks and controllers is a
simple way protect your database from potential failure.  It is simply
irresponsible to dismiss it out of hand.  Sure you might cite performance
concerns, but for most databases in most enterprises redo log multiplexing
does not constitute a performance bottleneck.

Your position appears to be that hardware mirroring, software mirroring,
RAID hardware, and the controllers feeding them all are infallible.
Multiplexing redo logs is simply a form of insurance, and should be
considered a default element of Oracle database design.

Steve

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve McClure
  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: [Q] ORACLE 9i remote export "insufficient previlege"?

[John Thomas]

I think you need a password file on the 9i server to connect remotely 
with SYSDBA privilege.

Go to $ORACLE_HOME/dbs and use orapwd. You may need to set an init.ora 
parameter along the lines of remote_password_exclusive or shared 
depending upon whether you want multiple users (or was it databases?) 
using the same file to log in remotely as well...

Cheers,

John Thomas

In message <[EMAIL PROTECTED]>, dist cash 
<[EMAIL PROTECTED]> writes
I am doing the ORACLE 9iR2 database test and tried to export form a 8i
computer to 9iR2 server.  I got "ORA-01031: insufficient privileges".

my export parfile on 8i server is:


userid='sys/passwd1@db92 as sysdba'
file=/tmp/exp_db8i_full.dmp
buffer=6400
compress=n
grants=y
indexes=y
rows=y
feedback=1
constraints=y
consistent=y
log=exp_db8i_full.log
full=y




I got:

Export: Release 8.1.7.2.0 - Production on Tue Nov 26 14:02:57 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


EXP-00056: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
Username:


Does anyone know why??

Thanks.

_
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.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Thomas
 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 operating system compatibility

[Alexander Ordonez]

Hi gurus,
i need a matrix with oracle verson and O.S. version??



@lex
--
Lic. Alexander Ordóñez Arroyo
Soporte Tru64Unix & BD  Oracle 
Caja Costarricense del Seguro Social 
Telefono: 295-2004, San José, Costa Rica
[EMAIL PROTECTED]    Celular 397-0532 
--
The truth is out there in WWW

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexander Ordonez
  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: redo log file setup with mirrored drives

[Stephen Lee]

> -Original Message-
> Of course, you'll need Tom Kyte's binary conversion program 
> here to execute this very weak proof:

Yeah, well this didn't come from Stephen Hawking.  And let's not forget the
part about "in the natural integers".  Homey didn't take a bunch of 5000 and
6000 level math courses and come away entirely untrained.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: LGWR using lots of CPU time, low CPU usage

[Cary Millsap]

The ultimate sincerest form of flattery is for someone to attribute
something smart to you that you wish you had done but, alas, did not
actually do.

(It was Tim Gorman who posted the excellent analogy.)


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

Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, November 26, 2002 4:07 PM
To: Multiple recipients of list ORACLE-L

> >And "old school" is still right about not putting RedoLogs onto
RAID5.

> From what I'm being told, this is not your father's RAID5.  This is
what 

>they tell me:

> The CPU hands the IO to the disk controller and rather than do the
> physical disk IO while the process waits, the disk controller caches
> it to local memory and says done.  Therefore, effectively there is no
> wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1,
> the system is NOT waiting for the IO. He said the only time there
might
> be a delay is during the cache's battery refresh times. I checked your
> dates and it was not occurring during those times. Also, if you look
> at the iostat statistics under the 'wait' and '%w' headers you will
> see all zeros.

Debi, 

That is true, up to a point.

Think of the cache as a water tank.  You have a garden hose
filling up the tank.  You can keep increasing the water
pressure for a while.

But the outlet at the other end of the tank has a fixed
capacity.  It flows 10 GPM, and no more.

What happens when you increase the flow at the intake to
20 GPM?

The tank fills up. 

When the tank fills up, your intake flow will need to decrease,
because you can only flow 10 GPM at the outlet.

Now, think of the outlet as writing to disk, the RAID5 cache
is the water tank, and your database is the inlet that wants
to run at 20 GPM.

If your database activity will never be intensive enough to 
stress the cache like this, no problem.  But 'never' is a
very long time.

If any of this sound familiar, Cary Millsap posted a very similar
explanation a few weeks ago.

Plagierism is the sincerest form of flattery.  :)

Jared


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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.com
-- 
Author: Cary Millsap
  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: SQL tuning help

[Stephen Lee]

 
> This week it began to hang and I can't figure out why.

The first thing I would check are locks.  The statement is trying to update
a table.  Try something like the following while the statement appears to be
hung.  These are two different ways (and certainly not the only ways) of
checking for lockers and waiters.



SELECT substr(s1.username,1,12)"WAITING User",
   substr(s1.osuser,1,8)"OS User",
   substr(to_char(w.session_id),1,5)"Sid",
   P1.spid  "PID",
   substr(s2.username,1,12)"HOLDING User",
   substr(s2.osuser,1,8)"OS User",
   substr(to_char(h.session_id),1,5)"Sid",
   P2.spid  "PID"
FROM   sys.v_$process P1,   sys.v_$process P2,
   sys.v_$session S1,   sys.v_$session S2,
   sys.dba_lock w, sys.dba_lock h
WHERE  h.mode_held= 'None'
ANDh.mode_held= 'Null'
ANDw.mode_requested  != 'None'
ANDw.lock_type (+)= h.lock_type
ANDw.lock_id1  (+)= h.lock_id1
ANDw.lock_id2  (+)= h.lock_id2
ANDw.session_id   = S1.sid  (+)
ANDh.session_id   = S2.sid  (+)
ANDS1.paddr   = P1.addr (+)
ANDS2.paddr   = P2.addr (+)
/

-


set lines 150
set pages 600
col mode_held for a12
col mode_requested for a12

select /*+ all_rows */ a.osuser waiter, nvl(b.osuser,'NOBODY') blocker,
w.lock_type, h.mode_held, w.mode_requested
-- w.lock_id1, w.lock_id2
from dba_locks w, dba_locks h, v$session a, v$session b
where h.blocking_others = 'Blocking' and h.mode_held != 'None'
and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type =
h.lock_type
and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2
and w.session_id in (select sid from v$session where last_call_et > 100 and
sid > 10 and osuser is not null)
and w.session_id = a.sid and h.session_id = b.sid;

--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: LGWR using lots of CPU time, low CPU usage

[Stephen Lee]

>I don't think changing the logmembers will do much good

I agree.


> 4 groups, they are on the local drive.

BOOM!!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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 OS level security

[Jared . Still]

Dear list,

Let me toss a hypothetical situation at you.

Say some auditors looked at some of your primary systems,
and concluded that they had no assurance that someone with
admin access to the server had not changed financial information
to benefit themselves, or to falsify financial records for the
gain of the company.

Not that they might have any proof that something like that 
had been done, but rather, just not proof that it had *not*
been done.

I've been pondering this for a bit, and it seems to me that if
someone had good knowledge of both the OS and the 
database (Oracle), as well as having admin rights on the
server, there are few things you can do to prevent such a person
from changing data in the database, and completely 
covering his or her tracks.

The platforms in question are Unix, Windows NT and
Windows 2000.   I've limited it to those as most database
systems use one of those, and besides, that's all I know.  :)

Consider what steps you might take to audit unauthorized
transactions performed by an admin.

Oracle Auditing could be used, but someone with admin 
access to the server and database could easily alter the 
records created by system auditing.

You could create an audit table, using a trigger to audit
sensitive tables.  A materialized view on a remote database
could be created on sensitive tables to remotely log all
actions.

In the case of the audit table, that could easily be disabled,
and then re-enabled after the nefarious DML had completed.

The materialized views might be more difficult to circumvent.

If the remote end is using a dblink to the server employing a 
password that is *different* than that of it's own account at the 
remote server, it should be impossible for someone to completely
cover the traces of transactions created to falsify data.

The MV  Logs could be dropped, but without access to the MV's
at the remote server, the MV's would have to be left in place. 

These could be used as a reference to look for unauthorized transactions
in the primary server.  If this same admin has access to the remote
server where the MV's are, then this can also be circumvented.

There is also the logs created as when logging in as internal 
or sysdba. ( $ORACLE_HOME/rdms/audit/*.aud )

These can simply be deleted.  Some system could be used to save
these to a remote server, but it would have to run *very* frequently to
be effective.

Oracle password files could also be used. While this can prevent
someone from logging in as SYS or SYSTEM while in place, all it
takes is a change to init.ora, and a database bounce to fix that.

Make your bogus data changes, change the init.ora back and 
bounce the database again.

A somewhat clever person could set this up to automatically
take place the next time the DB is bounced.

The conclusion I have come to is that the only effective method 
that could be used to create an audit trail for such a scenario is
to create Materialized Views on sensitive tables, and create them
on a server that admins are guaranteed to not have access to.

Of course, I may be missing something.  I'm not always one to 
catch all the details right off.  Input, comments, suggestions, far
out ideas are all welcome.

If you've read this far, thanks!

Jared





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: LGWR using lots of CPU time, low CPU usage

[Khedr, Waleed]

Slow I/O will not increase the CPU time for the lgwr, since this will be I/O
wait time. 
Did you try to truss the lgwr process?
What is the size of log_buffer?

Waleed

-Original Message-
Sent: Tuesday, November 26, 2002 3:35 PM
To: Multiple recipients of list ORACLE-L


Thanks for this paper--I will share it with our Sun engineer.

BTW, to clarify, I have 20 redo groups with 2 members each...I think I'm 
saying it right:

LOGFILE Group 1 ('/disk1/log01.log',
  '/disk2/log01.log') size 100M,
 Group 2 ('/disk1/log02.log',
  '/disk2/log02.log') size 100M,
...
 Group 20 ('/disk1/log20.log',
   '/disk2/log20.log') size 100M

The oldest log in the group is dated yesterday; but more than half have 
today's date.  I had one period today where there were 4 switches less than 
a minute apart!!

Debi

At 11:15 AM 11/26/2002 -0800, you wrote:
>So what is discussed in this paper is outdated alreadyuh! 
>www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14 
>talk about Oracle Redo Logs.
>
>As a first attempt, I would consider reducing the number of log members 
>(from 20 to 4, or even 3) than removing them altogether. This will be of 
>some help right away. But monitor further and decide if more Groups are 
>needed to help archiver process.
>
>Do not change multiple things at the same time.
>
>Good Luck,
>
>- Kirti
>
>-Original Message-
>Sent: Tuesday, November 26, 2002 12:00 PM
>To: Multiple recipients of list ORACLE-L
>
>
>We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
>on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
>Periodically throughout the day the LGWR background process clocks 20+
>minutes of CPU time while actual CPU usage is quite low. I ran a statspack
>report and for a 45-minute period that included the slow LGWR process.
>
>The top 5 timed events in my 45-minute report are:
>
>CPU time 1,295 60.41
>db file sequential read 392,516 341 15.91
>db file scattered read 70,245 168 7.85
>log file sync 26,916 133 6.22
>library cache pin 22 59 2.76
>
>(Now that the top 5 is "timed" events, 3 spots almost always include CPU
>and the db file reads, so I only get two other events, usually log file
>sync, sometimes enqueue or latch free.)
>
>Statspack also shows the log file parallel write had 28,589 timeouts in
>that 45 minute period--rather typical for us.
>
>I have session_cached_cursors set to 150.
>
>I am considering the following:
>
>1. Removing my own redo log duplexing (mirroring) since redo logs are on
>the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
>My sysadmin talked to the sun engineer yesterday and he said this is
>"old school" thinking that redo logs should not be on RAID5. He said
>because the RAID controller caches to memory all IO requests from
>the CPUs, all physical writes to disk are done behind the scenes
>(known as writebehind). He says the system is NOT waiting for IO.
>
>2. Increasing redo log size (again). For the most part, log switches
>average 2.5 per day, although there were 20 times in the last month of 3-7
>switches in a half hour. My logs are about 100 MB in 2 groups of 20 members
>each.
>
>3. Upping the session_cached_cursors to ? (in response to the library cache
>pin event).
>
>Or is there a better option I'm overlooking?
>
>I would appreciate some advise on the best approach to resolve the slow
>LGWR process, especially your thoughts on option 1.
>
>Thanks,
>Debi
>Deborah Lorraine, DBA
>University of California, Davis
>[EMAIL PROTECTED]
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deborah Lorraine
>   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.com
>--
>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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.c

RE: How are rollback segments assigned?

[Jared . Still]

> In v7, the tie-breaker algorithm apparently changed to choose the 
segment
> that is listed earliest in the init.ora file.

What if the rollback segments are created PUBLIC, and are
not listed in init.ora?

Order of sys.undo$.us#?

Jared






"Cary Millsap" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 11/26/2002 11:15 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: How are rollback segments assigned?


Exactly. A guy might have to poll V$ROLLSTAT 100+ times per second to
see the allocations happening on a system with well-designed
transactions. You just can't do that with SQL.

Raj, you *can* test the behavior by using some artificially *bad*
transactions. On a quiescent system (nothing else running) with n
rollback segments, open n+1 sqlplus sessions. In each session, update a
row but don't commit. After each session's update, look at V$ROLLSTAT. 

To the best of my knowledge, the assignment algorithm has always been to
assign a new txn to the segment with the smallest number of active txns
in it. There are of course lots of ties (e.g., lots of segments might
have 0 active txns in them). The tie-breaker in v6 was to assign in
round-robin order (as the segments are listed in the init.ora file). In
v7, the tie-breaker algorithm apparently changed to choose the segment
that is listed earliest in the init.ora file. I don't know whether it
changed again in 8 or 9. You'll know if you do the test.


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

Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, November 26, 2002 12:25 PM
To: Multiple recipients of list ORACLE-L

Raj,

You're assuming that the transactions are lasting long enough
for you to catch them in the rollback segments.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 11/26/2002 09:42 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc: 
Subject:Re: How are rollback segments assigned?



Thanks Jared, thats not what I see. I have 20 rollback segments, and at
no
point in time, there were more than
4 transactions in the rollback segments. In fact, I happened to see it a
couple of minutes ago.

select usn, xacts from v$rollstat showed me 0 in 19 rollback segments,
and
2 in one of them. And I have got alerts in place
to alert me when there are more than 4 transactions in all of the
rollback
segments. And yeah, the rollback segments
are online.

Remains a mystery to me.

Raj






 
Jared.Still@r 
adisys.com   To: Multiple recipients of 
list ORACLE-L <[EMAIL PROTECTED]> 
Sent by: cc: 
root@fatcity.Subject: Re: How are
rollback 
segments assigned? 
com 
 
 
November 25, 
2002 08:59 PM 
Please 
respond to 
ORACLE-L 
 
 




Here's one scenario:

4 transactions, 2 rollback segs

tx 1 - rbs1
tx 2 - rbs 2
tx 3  - rbs 1
tx 4 - rbs 2

tx1 and tx3 finish.

tx2 and tx4 have not yet committed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 11/25/2002 07:39 AM
 Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
cc:
Subject:How are rollback segments assigned?


Hello Friends,

Oracle Parallel Server 8.0.6.2.0 on Solaris 2.6 Nodes

We were having some locking and rollback issues, and I set up some
scripts
to alert me in case there are more than 4 transactions in the rollback
segments, and more than 1 in any of the rollback segments.

Select sum(xacts) from v$rollstat  --- Alert if more than 4, condition
set
to exclude system rollback
where usn != 0

Select count(*) from v$rollstat  --- Alert if more than 0, where
condition to exclude system rollback
where xacts >=2
andusn != 0

We have 20 rollback segments, and there is very minimal DML activity on
this database. I happened to see a scenario where all rollback segments
had
0 transactions, except for one which had 2 in them. Under what scenario,
can this happen?

I thought the criteria for assigning rollback segments to transactions
was
1. If object in system tablespace, use system rolback segment
2. Use the one with the least number of active transactions
3. If 2 or more rollback segments fit the second criteria, use the LRU
algorithm.

Thanks
Raj

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  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 future DBAs?

[Jared . Still]

Personally, I like Data Architecture.

And data modeling.  I never could get enough
of that.  The hard part is explaining to people that
don't quite understand the concept.

Dave Hay rules!
http://www.amazon.com/exec/obidos/tg/detail/-/0932633293

Being the sole DBA for the company, I don't get
nearly enough opportunities for this anymore, and
don't have the time for much of it anyway.

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 11/26/2002 10:04 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re:The future DBAs?


Well, I give MicroSlop pretty poor grades for predicting the future and
Monster.com is absolutely useless (naw make that less than) at job stuff 
in
general.  I will agree with the person who wrote the article on one point. 
 The
job of being a DBA is changing and we all need to remain flexible to 
remain
useful in the marketplace.  That in some cases means spreading our wings 
from
the historical role of DBA.  We may need to become part time (or full 
time) data
architects, reporting tool experts, etc...  But in the end, I don't see us
degrading to the level of an order entry clerk nor order entry clerks 
upgrading
to DBA's.  As usual the MicroSlop propaganda machine is at work again. 

Dick Goulet

Reply Separator
Author: "Arup Nanda" <[EMAIL PROTECTED]>
Date:   11/25/2002 5:48 PM

Fellow DBAs and other DBA wannabes,

Ever wondered the best path into a DBA career? Microsoft offers a 
brilliant 
way. MSN Careers at http://editorial.careers.msn.com/articles/nofuture/ 
suggests some jobs are effectively dead, like farmers and sewing machine 
operators and how the experts in that field can progress to the next 
logical 
career move. Guess which profession's logical career move is database 
administrator? See the excerpt from the webpage here in the attachment as 
a 
picture.

I just couldn't resist posting it here. May be they are referring to SQL 
Server DBAs?

Arup Nanda







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

 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: LGWR using lots of CPU time, low CPU usage

[Fink, Dan]

Debi,
Is there a way to even out the 'spikey' i/o activity? What processes
are doing this activity and can they be spread out? Is the slow LGWR process
causing user-visible performance problems? Is the LGWR issue a symptom and
not the real problem?

Sorry, no answers, just some questions to think about...

-Original Message-
Sent: Tuesday, November 26, 2002 1:35 PM
To: Multiple recipients of list ORACLE-L


Thanks for this paper--I will share it with our Sun engineer.

BTW, to clarify, I have 20 redo groups with 2 members each...I think I'm 
saying it right:

LOGFILE Group 1 ('/disk1/log01.log',
  '/disk2/log01.log') size 100M,
 Group 2 ('/disk1/log02.log',
  '/disk2/log02.log') size 100M,
...
 Group 20 ('/disk1/log20.log',
   '/disk2/log20.log') size 100M

The oldest log in the group is dated yesterday; but more than half have 
today's date.  I had one period today where there were 4 switches less than 
a minute apart!!

Debi

At 11:15 AM 11/26/2002 -0800, you wrote:
>So what is discussed in this paper is outdated alreadyuh! 
>www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14 
>talk about Oracle Redo Logs.
>
>As a first attempt, I would consider reducing the number of log members 
>(from 20 to 4, or even 3) than removing them altogether. This will be of 
>some help right away. But monitor further and decide if more Groups are 
>needed to help archiver process.
>
>Do not change multiple things at the same time.
>
>Good Luck,
>
>- Kirti
>
>-Original Message-
>Sent: Tuesday, November 26, 2002 12:00 PM
>To: Multiple recipients of list ORACLE-L
>
>
>We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
>on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
>Periodically throughout the day the LGWR background process clocks 20+
>minutes of CPU time while actual CPU usage is quite low. I ran a statspack
>report and for a 45-minute period that included the slow LGWR process.
>
>The top 5 timed events in my 45-minute report are:
>
>CPU time 1,295 60.41
>db file sequential read 392,516 341 15.91
>db file scattered read 70,245 168 7.85
>log file sync 26,916 133 6.22
>library cache pin 22 59 2.76
>
>(Now that the top 5 is "timed" events, 3 spots almost always include CPU
>and the db file reads, so I only get two other events, usually log file
>sync, sometimes enqueue or latch free.)
>
>Statspack also shows the log file parallel write had 28,589 timeouts in
>that 45 minute period--rather typical for us.
>
>I have session_cached_cursors set to 150.
>
>I am considering the following:
>
>1. Removing my own redo log duplexing (mirroring) since redo logs are on
>the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
>My sysadmin talked to the sun engineer yesterday and he said this is
>"old school" thinking that redo logs should not be on RAID5. He said
>because the RAID controller caches to memory all IO requests from
>the CPUs, all physical writes to disk are done behind the scenes
>(known as writebehind). He says the system is NOT waiting for IO.
>
>2. Increasing redo log size (again). For the most part, log switches
>average 2.5 per day, although there were 20 times in the last month of 3-7
>switches in a half hour. My logs are about 100 MB in 2 groups of 20 members
>each.
>
>3. Upping the session_cached_cursors to ? (in response to the library cache
>pin event).
>
>Or is there a better option I'm overlooking?
>
>I would appreciate some advise on the best approach to resolve the slow
>LGWR process, especially your thoughts on option 1.
>
>Thanks,
>Debi
>Deborah Lorraine, DBA
>University of California, Davis
>[EMAIL PROTECTED]
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deborah Lorraine
>   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.com
>--
>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 r

Recipe for application design to run on RAC

[Boris Dali]

Dear List,

Number of times I've seen that one of prerequsites for
switching from single node DB to OPS/RAC is to have an
application specifically designed / architectured to
run on RAC.
Can somebody elaborate? Is it something "visible" on 
ERD? That is by looking at the model can RAC guru tell
that it wouldn't work well on RAC?
Or put it another way can one conclude based on the
ERD that app was modeled to run on RAC?

What's the recepie for app design for RAC?

TIA

__ 
Post your free ad now! http://personals.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Boris Dali
  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: Primary Key Constraints

[Krishna Rao Kakatur]

select table_name from user_tables
minus
select table_name from user_constraints where constraint_type='P';

HTH, Krishna


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 11:25 AM


> Is there an easy query to get a list of tables that don't have any primary
> key?
>
> I've tried a couple of different ones, but none of them work quite right.
> Seems like this should be easy.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Sardin
>   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.com
-- 
Author: Krishna Rao Kakatur
  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 are rollback segments assigned?

[Fink, Dan]

Raj,
Not to be too obvious, but... Are there any transactions that are
being explicitly assigned to the rollback segments by the user/application?

-Original Message-
Sent: Tuesday, November 26, 2002 10:42 AM
To: Multiple recipients of list ORACLE-L



Thanks Jared, thats not what I see. I have 20 rollback segments, and at no
point in time, there were more than
4 transactions in the rollback segments. In fact, I happened to see it a
couple of minutes ago.

select usn, xacts from v$rollstat showed me 0 in 19 rollback segments, and
2 in one of them. And I have got alerts in place
to alert me when there are more than 4 transactions in all of the rollback
segments. And yeah, the rollback segments
are online.

Remains a mystery to me.

Raj






 

Jared.Still@r

adisys.com   To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:

root@fatcity.Subject: Re: How are rollback
segments assigned?   
com

 

 

November 25,

2002 08:59 PM

Please

respond to

ORACLE-L

 

 





Here's one scenario:

4 transactions, 2 rollback segs

tx 1 - rbs1
tx 2 - rbs 2
tx 3  - rbs 1
tx 4 - rbs 2

tx1 and tx3 finish.

tx2 and tx4 have not yet committed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 11/25/2002 07:39 AM
 Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:How are rollback segments assigned?


Hello Friends,

Oracle Parallel Server 8.0.6.2.0 on Solaris 2.6 Nodes

We were having some locking and rollback issues, and I set up some scripts
to alert me in case there are more than 4 transactions in the rollback
segments, and more than 1 in any of the rollback segments.

Select sum(xacts) from v$rollstat  --- Alert if more than 4, condition set
to exclude system rollback
where usn != 0

Select count(*) from v$rollstat  --- Alert if more than 0, where
condition to exclude system rollback
where xacts >=2
andusn != 0

We have 20 rollback segments, and there is very minimal DML activity on
this database. I happened to see a scenario where all rollback segments
had
0 transactions, except for one which had 2 in them. Under what scenario,
can this happen?

I thought the criteria for assigning rollback segments to transactions was
1. If object in system tablespace, use system rolback segment
2. Use the one with the least number of active transactions
3. If 2 or more rollback segments fit the second criteria, use the LRU
algorithm.

Thanks
Raj

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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.com
--
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.com
-- 
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.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

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

Re: LGWR using lots of CPU time, low CPU usage

[Jared . Still]

> >And "old school" is still right about not putting RedoLogs onto RAID5.

> From what I'm being told, this is not your father's RAID5.  This is what 

>they tell me:

> The CPU hands the IO to the disk controller and rather than do the
> physical disk IO while the process waits, the disk controller caches
> it to local memory and says done.  Therefore, effectively there is no
> wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1,
> the system is NOT waiting for the IO. He said the only time there might
> be a delay is during the cache's battery refresh times. I checked your
> dates and it was not occurring during those times. Also, if you look
> at the iostat statistics under the 'wait' and '%w' headers you will
> see all zeros.

Debi, 

That is true, up to a point.

Think of the cache as a water tank.  You have a garden hose
filling up the tank.  You can keep increasing the water
pressure for a while.

But the outlet at the other end of the tank has a fixed
capacity.  It flows 10 GPM, and no more.

What happens when you increase the flow at the intake to
20 GPM?

The tank fills up. 

When the tank fills up, your intake flow will need to decrease,
because you can only flow 10 GPM at the outlet.

Now, think of the outlet as writing to disk, the RAID5 cache
is the water tank, and your database is the inlet that wants
to run at 20 GPM.

If your database activity will never be intensive enough to 
stress the cache like this, no problem.  But 'never' is a
very long time.

If any of this sound familiar, Cary Millsap posted a very similar
explanation a few weeks ago.

Plagierism is the sincerest form of flattery.  :)

Jared


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

Using RECYCLE pool?

[DENNIS WILLIAMS]

Is anyone using the Oracle RECYCLE buffer pool? What was your criteria to
select tables? The application I am considering RECYCLE for doesn't perform
table scans, so that eliminates one common suggestion.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: redo log file setup with mirrored drives

[Jesse, Rich]

Theory: 2 + 2 = 15

SELECT 2 + 2 FROM DUAL;
SELECT 1+1 + 1+1 FROM DUAL;
SELECT TO_CHAR(1)||TO_CHAR(1)||TO_CHAR(1)||TO_CHAR(1) FROM DUAL;
SELECT to_dec(TO_CHAR(1)||TO_CHAR(1)||TO_CHAR(1)||TO_CHAR(1),2) FROM DUAL;

Of course, you'll need Tom Kyte's binary conversion program here to execute
this very weak proof:

http://govt.oracle.com/~tkyte/hexdec/hexdec.sql

;)

Rich
Beer-free for almost 17 hours.


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

> -Original Message-
> From: Fink, Dan [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 26, 2002 2:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: redo log file setup with mirrored drives
> 
> 
> Stephen,
>   Nothing is gained by personal attacks in this forum. 
> This forum is
> intended to be a learning experience for all (myself 
> included). I suggest
> that you review the archived list and examine the quality of 
> posts by Kirti,
> Jared, et.al. They speak for themselves.
> 
>   BTW, 2 + 2 does equal 15 for very large values of 2 and 
> very small
> values of 15. ;)
> Dan Fink
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: SQL tuning help

[Sergei]

Title: RE: SQL tuning help









Let
me clearfy.

I am running Oracle 8.1.6 on solaris 8

 

Fastcash has 50M
record

tmp_brian_metareward1 has 600
records.

 

I was able to tune the query so it runs fast now.

I created a combined index on subsite_id
and attempt and I added a hint

 

UPDATE tmp_brian_metareward1 tmp

   SET offers_seen  = (

   SELECT
/*+ INDEX(f IN_FASTCASH_SIDATMP) */ count(f.fastcash_id) FROM metareward.fastcash
f


WHERE f.subsite_id = tmp.subsite_id

  
and attempt >= trunc(sysdate-1)

  
and attempt < trunc(sysdate)


group by tmp.subsite_id);

 

I am still having a problem with a similar query below:

IN_FASTCASH_SIDFIDVER – index is for subsite_id fastcash_id and
verified

 

UPDATE tmp_brian_metareward1 tmp

   SET
revenue = (SELECT /*+ INDEX(f IN_FASTCASH_SIDFIDVER)
*/ sum(f.mr_amount)/100 FROM metareward.fastcash
f, metareward.transaction_fastcash tf


WHERE f.subsite_id = tmp.subsite_id

  
and f.fastcash_id = tf.fastcash_id

  
and f.verified >= trunc(sysdate-1)

       and f.verified < trunc(sysdate)


group by tmp.subsite_id);

 

 

transaction_fastcash table has
2.5M records

fastcash table has  
50M record

 

 

Thank you

Sergei

 

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Whittle
Jerome Contr NCI
Sent: Tuesday, November 26, 2002
12:04 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: SQL tuning help

 

Sergei, 

How many records in each
table? What indexes are in these tables? What version of Oracle? 

What do you mean by
'began to hang'? 

I'd try making the
attempts in the WHERE clause into a Between. I'd also try grouping by
f.subsite_id. 

You could always throw a
Rule hint at it and see what happens. 

Jerry Whittle 
ACIFICS DBA 
NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145 

-Original Message- 
From:   Sergei [SMTP:[EMAIL PROTECTED]]


Hello everybody, 

I have the following query that runs every
week.  

UPDATE tmp_brian_metareward1 tmp 
 
SET offers_seen  = (SELECT count(f.fastcash_id) FROM 
metareward.fastcash
f 

WHERE f.subsite_id = tmp.subsite_id 
  
and attempt >= trunc(sysdate-1) 
  
and attempt < trunc(sysdate) 

group by tmp.subsite_id); 

This week it began to hang and I can't figure out
why.  No changes were 
made
to a database.  Please advise me on how I can tune it, which hints

to
add, or anything else I can do. 

Thank you 
Sergei

RE: 9i Lite

[Saira Somani]

Yes. Here is how it works [in a nutshell]:

PDA (Lite Client) --> Oracle Mobile Server --> Oracle Server Repository 

-Original Message-
Sent: November 26, 2002 3:15 PM
To: Multiple recipients of list ORACLE-L

Hi Dennis,

This is a completely separate product, it is designed for mobile devices

like pda's etc.

Regards,
John

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

Autoextend WAIT statistic?

[DENNIS WILLIAMS]

Oracle says that when a file autoextends, there is a slight delay. Does
anyone know which Oracle WAIT statistic that would appear under?
  We have been using autoextend on OLTP production tables for awhile now,
and the results have been satisfactory. This is an ERP system, so the
critical performance time is at month-end. Some of the developers are
concerned that table autoextending may slow batch programs, and suggesting
that I should determine which tables are likely to autoextend during
month-end and add storage beforehand. I would like to ensure that I am
fixing a real problem (short on time, like most of you), so I am wondering
if autoextend was causing a delay, what wait statistic would it show up
under. Any ideas?

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: redo log file setup with mirrored drives

[Stephen Lee]

> -Original Message-
> Stephen,
>   Nothing is gained by personal attacks in this forum. 

Please enlighten me.  Exactly what personal attack was made?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Primary Key Constraints

[Stephane Paquette]

Another way :

select t.owner, t.table_name
from   dba_tables t
minus
select c.owner, c.table_name
from   dba_constraints c
where  c.constraint_type='P'
;

 --- "Mercadante, Thomas F" <[EMAIL PROTECTED]>
a écrit : > Mike,
> 
> How about:
> 
> select table_name from user_tables a
>   where not exists(select 1 from user_constraints b
>  where a.table_name =
> b.table_name
>  and   b.constraint_type = 'P');
> 
> 
> Constraint_type values are:
> 
> P = Primary Key
> C = Check Constraint
> R = Referential Constraint (Foreign Key)
> U = Unique Constraint
> 
> There might be others.
> 
> Hope this helps.
> 
> Tom Mercadante
> Oracle Certified Professional
> 
> 
> -Original Message-
> Sent: Tuesday, November 26, 2002 2:25 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Is there an easy query to get a list of tables that
> don't have any primary
> key?  
> 
> I've tried a couple of different ones, but none of
> them work quite right. 
> Seems like this should be easy.
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Mike Sardin
>   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.com
> -- 
> Author: Mercadante, Thomas F
>   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).
>  

=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  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: SQL tuning help

[Mark Richard]

Sergei,

By "hang" I'm going to assume that I can replace that with the phrase
"running really slow".  If it is actually "hanging" then I think a call to
Oracle is in order.

I'm guessing that perhaps statistics were updated or one of the tables
changed in size enough to convince the optimisor to take a different
approach to the query.  Try to get an explain plan for the query.

Looking at the query I have a couple of other questions...

1)  Since the subquery is updating a single row and joins to that row using
subsite_id, I see no need for the group by clause - there is only 1
subsite_id that's going to appear.  If there is something I don't
understand here let me know - I have seen this type of query before and
questioned its significance.

2)  It looks like the ideal indexes are metareward.fastcash(subsite_id) and
metareward.fastcash(attempt), or perhaps a concatenated index with
subsite_id as the leading column.  What indexes currently exist?  Is the
query using them (hence the need for an explain plan)?  Have you analyzed
the tables recently?

Hopefully I have given you something to work from.  To help you further
we'd need the explain plan, the row counts and perhaps table/index
statistics (details in user_tables and user_indexes, etc).  It's difficult
to suggest a hint without knowing further details.  Finally how many
different subsite_id's exist in fastcash and how many of those are you
gathering details about?  Perhaps a CTAS that calculates details for every
subsite_id at once would be much faster if you are looking at most of the
fastcash table anyway.

Regards,
 Mark.



   

"Sergei"   

   
p.com>   cc:   

Sent by: Subject: SQL tuning help  

[EMAIL PROTECTED] 

om 

   

   

27/11/2002 

05:54  

Please respond 

to ORACLE-L

   

   





Hello everybody,

I have the following query that runs every week.

UPDATE tmp_brian_metareward1 tmp
  SET offers_seen  = (SELECT count(f.fastcash_id) FROM
metareward.fastcash f
 WHERE f.subsite_id = tmp.subsite_id
   and attempt >= trunc(sysdate-1)
   and attempt < trunc(sysdate)
 group by tmp.subsite_id);

This week it began to hang and I can't figure out why.  No changes were
made to a database.  Please advise me on how I can tune it, which hints
to add, or anything else I can do.

Thank you
Sergei


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




<<>>
   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by r

Re: Primary Key Constraints

[Arup Nanda]

it's sure easy enough

SELECT (OWNER, TABLE_NAME)
FROM DBA_TABLES
WHERE (OWNER, TABLE_NAME) NOT IN
(SELECT OWNER, TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P')

HTH

Arup Nanda

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 2:25 PM


> Is there an easy query to get a list of tables that don't have any primary
> key?
>
> I've tried a couple of different ones, but none of them work quite right.
> Seems like this should be easy.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Sardin
>   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.com
-- 
Author: Arup Nanda
  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: Mass updates to production tables (NULL to non-NULL)

[Khedr, Waleed]

Iterations takes longer because you have to (provided we will update all the
rows):
1) read 10,000 rows to update the first 10,000.
2) read 20,000 rows to update the second 10,000.
3) so on

If we have N runs, each updates R rows, then 
The number of rows need to be read =
 1 * R + 2 * R + 3 * R+ .+ N * R =  R * (1+2++N)= R * (N / 2) * (N +
1)

And  N * R = total number of tows in the table (T)

So the number of rows need to be read to update the whole table =

 (T / N) * (N /2) * (N + 1) = (T / 2) * (N + 1)

the number of rows need to be read to update the whole table = T * (N + 1) /
2

T = Table rows number
N = number of iterations to update the whole table.


This was fun!


Waleed
 

-Original Message-
Sent: Tuesday, November 26, 2002 2:25 PM
To: Multiple recipients of list ORACLE-L



Point (1)
As Larry Elkins pointed out to me in an offline post,
I had forgotten to highlight the fact that even null CHAR
columns do still use a length byte (unless they are
trailing nulls - i.e. there are no following non-null columns).
Apart from this, a row still needs:
two byte entry in the row index in the block
one lock byte
one byte column count -  (guess how Oracle
manages rows with more than 255 columns)
one byte flags

Also, Oracle assumes that a row MAY have to
migrate at some time, requiring enough space
to be reserved for a 6-byte rowid.  So the maximum
rowcount in a block is (roughly) blocksize / 11.

Point (2)
Each iteration through the outer loop, or each
iteration of the 'update 10,000'.  Apart from the
1555, the main problem with a counted loop is
that (in theory) it does a lot more work to achieve
the same result as a 'proper' update statement.
In practice, it may be possible to introduce side-effects
on bulk update strategies that cause worse problems
than the loop, though. For example, the 'each iteration
takes longer than the last' is likely to be related to
a mixture of delayed block cleanout (particularly
in indexes), attempts at read-consistency, and
cyclic block flushing.

Note - ORA-01555 need not matter, if you have
a mechanism that can respond to it gracefully.


Regards

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

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

Denver___December 2/4
England__January 21/23


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





-Original Message-
To: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Cc: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Date: 26 November 2002 18:16


>Hey Jonathon,
>
>Two questions about your response:
>
>1)  Yes, you are obviously correct.  My test was flawed.  So, if
NULLs use
>no space, then why does many NULL rows cause a table to extend?  Is
it
>because of the row directory in the data block header?  Egad...going
back to
>DBA school here.My apologies to my Oracle DBA Instructor!
I've
>tried testing this theory, but I'm not having any luck.
>
>2)  The update works fine, except that each iteration takes
progressively
>longer to run to the point that it's not feasible to run in
production.  So,
>what's wrong with the counted cursor loop, other than the possibility
of
>ORA-1555?
>
>Thx!  :)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  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: SQL tuning help

[Krishna Rao Kakatur]

Remove the group by clause. It does nothing.

Also, if the cardinality for subsite_id in the table tmp_brian_metareward1
is low,
you may use a PL/SQL block instead of a single update statement.

HTH, Krishna

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 10:54 AM


> Hello everybody,
>
> I have the following query that runs every week.
>
> UPDATE tmp_brian_metareward1 tmp
>   SET offers_seen  = (SELECT count(f.fastcash_id) FROM
> metareward.fastcash f
>  WHERE f.subsite_id = tmp.subsite_id
>and attempt >= trunc(sysdate-1)
>and attempt < trunc(sysdate)
>  group by tmp.subsite_id);
>
> This week it began to hang and I can't figure out why.  No changes were
> made to a database.  Please advise me on how I can tune it, which hints
> to add, or anything else I can do.
>
> Thank you
> Sergei
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Krishna Rao Kakatur
  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: Mass updates to production tables (NULL to non-NULL)

[Jared . Still]

Glad you're enjoying yourself.  :)

Jared





"Khedr, Waleed" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 11/26/2002 01:29 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: Mass updates to production tables (NULL to non-NULL)


Iterations takes longer because you have to (provided we will update all 
the
rows):
1) read 10,000 rows to update the first 10,000.
2) read 20,000 rows to update the second 10,000.
3) so on

If we have N runs, each updates R rows, then 
The number of rows need to be read =
 1 * R + 2 * R + 3 * R+ .+ N * R =  R * (1+2++N)= R * (N / 2) * (N 
+
1)

And  N * R = total number of tows in the table (T)

So the number of rows need to be read to update the whole table =

 (T / N) * (N /2) * (N + 1) = (T / 2) * (N + 1)

the number of rows need to be read to update the whole table = T * (N + 1) 
/
2

T = Table rows number
N = number of iterations to update the whole table.


This was fun!


Waleed
 

-Original Message-
Sent: Tuesday, November 26, 2002 2:25 PM
To: Multiple recipients of list ORACLE-L



Point (1)
As Larry Elkins pointed out to me in an offline post,
I had forgotten to highlight the fact that even null CHAR
columns do still use a length byte (unless they are
trailing nulls - i.e. there are no following non-null columns).
Apart from this, a row still needs:
two byte entry in the row index in the block
one lock byte
one byte column count -  (guess how Oracle
manages rows with more than 255 columns)
one byte flags

Also, Oracle assumes that a row MAY have to
migrate at some time, requiring enough space
to be reserved for a 6-byte rowid.  So the maximum
rowcount in a block is (roughly) blocksize / 11.

Point (2)
Each iteration through the outer loop, or each
iteration of the 'update 10,000'.  Apart from the
1555, the main problem with a counted loop is
that (in theory) it does a lot more work to achieve
the same result as a 'proper' update statement.
In practice, it may be possible to introduce side-effects
on bulk update strategies that cause worse problems
than the loop, though. For example, the 'each iteration
takes longer than the last' is likely to be related to
a mixture of delayed block cleanout (particularly
in indexes), attempts at read-consistency, and
cyclic block flushing.

Note - ORA-01555 need not matter, if you have
a mechanism that can respond to it gracefully.


Regards

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

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

Denver___December 2/4
England__January 21/23


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





-Original Message-
To: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Cc: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Date: 26 November 2002 18:16


>Hey Jonathon,
>
>Two questions about your response:
>
>1)  Yes, you are obviously correct.  My test was flawed.  So, if
NULLs use
>no space, then why does many NULL rows cause a table to extend?  Is
it
>because of the row directory in the data block header?  Egad...going
back to
>DBA school here.My apologies to my Oracle DBA Instructor!
I've
>tried testing this theory, but I'm not having any luck.
>
>2)  The update works fine, except that each iteration takes
progressively
>longer to run to the point that it's not feasible to run in
production.  So,
>what's wrong with the counted cursor loop, other than the possibility
of
>ORA-1555?
>
>Thx!  :)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  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.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Netwo

RE: redo log file setup with mirrored drives

[Brian Haas]

On Tue, 2002-11-26 at 10:50, Stephen Lee wrote:
 
 And from another post ...
> > Because my OS/hardware IS reliable a corrupted 
> > log file that is mirrored outside of Oracle will be corrupt - 
> > the original is corrupt, so is the mirror.
> 
> In one sentence you have claimed that your "OS/hardware IS reliable" and
> talk about it corrupting log files.  Are we having a problem with the
> definition of "reliable" here?
> 

Hmmm..I don't think the original poster is saying the OS corrupted the
log file. The post is merely mentioning that if a non-oracle mirrored
redo log was to become corrupted(by any means) the mirror copy will be
corrupted as well, but an Oracle multiplexed copy might not be.

> This goes back to an old post of using NT versus Unix.  If you recall, my
> reply was that security on NT was so bad, that it is not a good choice.
> This stems primarily from the fact that NT is essentially a single-user OS,
> built around the administrator, with some multi-user extensions kludged on
> to support non-administrator pseudo-users.

> Going back to the original post on this topic: There was nothing that
> suggested they were in a pathological environment.  For what it's worth,
> when we had databases on NT, we followed a strict directory naming routine
> and made it clear to the NT admins that any directory with certain names
> were not to be touched.  If anything needed to be done with those
> directories, they were to page us.

Last time I checked most SA's have root access. Therefore they can
delete(accidentally or otherwise) any of your Oracle files. Granted, Joe
Schmoe user won't be able to do this, but how often is user Joe Schmoe
logging on to an NT Oracle box? So I would say your NT point is moot.
The same security precautions from admins is needed in a Unix
environment as well.

If you read the original post again, the poster asked if there was any
"danger" to just using OS mirroring. I think the potential dangers were
addressed and the question was answered. I don't think anyone was
questioning the reliability of OS mirroring, but as an administrator, I
think it is in my best interest to take worst case scenarios into
account. 

> Concluding remarks:
> While the scenarios of gloom and doom that have been painted by some seem to
> be credible, I've have yet to witness, in my years of personal experience as
> a sys admin and a database admin the unreliability that some claim to exist.
> That being the case, I must go with the arrangement that I think offers
> fault tolerance with the best performance.

As with any database installation the old "it depends" works every time.
There are best practices, but each situation is different and has
different requirements for reliability, uptime,security, etc. If just
using OS mirroring is the best choice for your installation that's
great, but it might not be the best choice for all. 


-Brian

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian Haas
  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: Primary Key Constraints

[Magaliff, Bill]

try this:

select table_name from user_tables
minus
select table_name from user_constraints where constraint_type = 'P';

-Original Message-
Sent: Tuesday, November 26, 2002 2:25 PM
To: Multiple recipients of list ORACLE-L


Is there an easy query to get a list of tables that don't have any primary
key?  

I've tried a couple of different ones, but none of them work quite right. 
Seems like this should be easy.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Sardin
  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.com
-- 
Author: Magaliff, Bill
  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 Environment Documentation

[Erik Williams]

I need to document a number of Oracle environments for a client. I think
that this is similar in scope to what Oracle Consulting calls an Operational
Readiness Assessment. Has anyone on the list been through this sort of
exercise? Can anyone share any documents that would be appropriate for this?
I had a good base document to work from at one time, but cant find it now
that I need it. 

Thanks
Erik
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Erik 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: LGWR using lots of CPU time, low CPU usage

[Fink, Dan]

These are out of left field, but have bit me in the past. Take with a big
ol' grain of salt.

Is the application issuing a 'COMMIT' after each 'SELECT'? This will cause a
commit entry to be written to the log buffer and the buffer to be flushed.
It could explain why LGWR is consuming time and not much usage. I also
believe that this causes all of the process memory slots to be examined for
pending commits.

Has someone changed the priority of the LGWR process?

Other issues (# of members, size, etc) have already been addressed
accurately.

-Original Message-
Sent: Tuesday, November 26, 2002 11:00 AM
To: Multiple recipients of list ORACLE-L


We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB 
on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.

Periodically throughout the day the LGWR background process clocks 20+ 
minutes of CPU time while actual CPU usage is quite low. I ran a statspack 
report and for a 45-minute period that included the slow LGWR process.

The top 5 timed events in my 45-minute report are:

CPU time 1,295 60.41
db file sequential read 392,516 341 15.91
db file scattered read 70,245 168 7.85
log file sync 26,916 133 6.22
library cache pin 22 59 2.76

(Now that the top 5 is "timed" events, 3 spots almost always include CPU 
and the db file reads, so I only get two other events, usually log file 
sync, sometimes enqueue or latch free.)

Statspack also shows the log file parallel write had 28,589 timeouts in 
that 45 minute period--rather typical for us.

I have session_cached_cursors set to 150.

I am considering the following:

1. Removing my own redo log duplexing (mirroring) since redo logs are on
the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
My sysadmin talked to the sun engineer yesterday and he said this is
"old school" thinking that redo logs should not be on RAID5. He said
because the RAID controller caches to memory all IO requests from
the CPUs, all physical writes to disk are done behind the scenes
(known as writebehind). He says the system is NOT waiting for IO.

2. Increasing redo log size (again). For the most part, log switches 
average 2.5 per day, although there were 20 times in the last month of 3-7 
switches in a half hour. My logs are about 100 MB in 2 groups of 20 members 
each.

3. Upping the session_cached_cursors to ? (in response to the library cache 
pin event).

Or is there a better option I'm overlooking?

I would appreciate some advise on the best approach to resolve the slow 
LGWR process, especially your thoughts on option 1.

Thanks,
Debi
Deborah Lorraine, DBA
University of California, Davis
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deborah Lorraine
  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.com
-- 
Author: Fink, Dan
  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: Mass updates to production tables (NULL to non-NULL)

[Jesse, Rich]

1) In a few trials on 8K blocks, I've been able to get a maximum 662 rows of
CHAR(1) (and CHAR(2) and CHAR(4))populated with spaces in a single block.
As these weren't exactly scientific, I don't remember if I had kept PCTFREE
at 2 thru my trials.  Is this info documented somewhere?  The only non-v7
reference I've found on Metalink was Oracle Support saying in a forum post
that they pulled the info from Metalink because they got tired of complaints
that the algorithm wasn't accurate enough to estimate table sizes.

2)  Problem solved here.  Since you've steered me in the right direction (I
*do* have to worry about row chaining), I'm rebuilding the table and it's
many, icky indexes (that's another story).  On the rebuild, I'll populate
the NULL columns.

Thanks, Jonathan!  :)
Rich


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

> -Original Message-
> From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 26, 2002 1:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Mass updates to production tables (NULL to non-NULL)
> 
> 
> 
> Point (1)
> As Larry Elkins pointed out to me in an offline post,
> I had forgotten to highlight the fact that even null CHAR
> columns do still use a length byte (unless they are
> trailing nulls - i.e. there are no following non-null columns).
> Apart from this, a row still needs:
> two byte entry in the row index in the block
> one lock byte
> one byte column count -  (guess how Oracle
> manages rows with more than 255 columns)
> one byte flags
> 
> Also, Oracle assumes that a row MAY have to
> migrate at some time, requiring enough space
> to be reserved for a 6-byte rowid.  So the maximum
> rowcount in a block is (roughly) blocksize / 11.
> 
> Point (2)
> Each iteration through the outer loop, or each
> iteration of the 'update 10,000'.  Apart from the
> 1555, the main problem with a counted loop is
> that (in theory) it does a lot more work to achieve
> the same result as a 'proper' update statement.
> In practice, it may be possible to introduce side-effects
> on bulk update strategies that cause worse problems
> than the loop, though. For example, the 'each iteration
> takes longer than the last' is likely to be related to
> a mixture of delayed block cleanout (particularly
> in indexes), attempts at read-consistency, and
> cyclic block flushing.
> 
> Note - ORA-01555 need not matter, if you have
> a mechanism that can respond to it gracefully.
> 
> 
> Regards
> 
> Jonathan Lewis
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Primary Key Constraints

[John Shaw]

how about select table_name from user_tables where table_name not 
in (select table_name from user_constraints where constraint_type = 
'P');>>> [EMAIL PROTECTED] 11/26/02 01:25PM 
>>>Is there an easy query to get a list of tables that don't have 
any primarykey?  I've tried a couple of different ones, but 
none of them work quite right. Seems like this should be 
easy.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Mike 
Sardin  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).

RE: Primary Key Constraints

[mantfield]

select owner, table_name from all_tables A where not exists
(select owner, table_name from all_constraints B where b.owner=A.owner and 
b.table_name=A.table_name
 and b.constraint_type='P')

That ought to do it. Cheers :

Ferenc Mantfeld

-Original Message-
From:   Mike Sardina [SMTP:[EMAIL PROTECTED]]
Sent:   Wednesday, November 27, 2002 6:25 AM
To: Multiple recipients of list ORACLE-L
Subject:Primary Key Constraints

Is there an easy query to get a list of tables that don't have any primary
key?  

I've tried a couple of different ones, but none of them work quite right. 
Seems like this should be easy.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Sardin
  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.com
-- 
Author: mantfield
  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: Slightly OT - Who would you take with you...

[Gabriel Aragon]

Hey guys, in the name of all the persons (me too) who
are not experts, genius, gurus, etc... Can we stop
this useless discussion? I mean, the one that wants,
just answer to Robert and let's to the rest of us to
continue learning Oracle!

Just a comment ;-)
Gabriel


--- "MacGregor, Ian A." <[EMAIL PROTECTED]> wrote:
> I did read your post.  I stated you were taking a
> poll, the "popularity" characterization is no where
> to be found in my message.  Nor is there any
> implication in my post that you were running a
> "beauty contest".   Perhaps my choice of the word
> important instead of useful or informative was what
> caused the confusion.  
> 
> Once one has acquired a certain amount of Oracle
> expertise most posts are mere confirmations of what
> one already knows, and  things one learns from this
> forum are added  to that expertise increasing the
> likelihood that future posts will be
> reconfirmations.  There are certainly persons whose
> expertise greatly exceeds mine.  These people are
> most likely to post messages I find worth saving. 
> There are folks at roughly the same level.  Nearly
> all of their posts are  reconfirmations, but there
> are still many  worth saving.  There are those who I
> would judge to have a poorer understanding of most
> of Oracle, but have a better grasp on certain
> specifics.  Very few of their posts are worth
> saving, but those which are, are  as informative as 
> "expertise greatly exceeds" group.
> 
> Along the same lines, no one on this group is
> infallible.   There are lurkers  who post very
> infrequently, but when they do  demonstrate a very
> detailed knowledge of a facet of Oracle when they
> correct or complete  answers given by folks rightly
> held in high esteem.   Your polling method would
> miss these important posts.
> 
> We are presently moving toward RMAN.  I'm currently
> saving many of your posts, but as I acquire more
> knowledge and the new becomes second nature many of
> those posts will lose their importance and be
> discarded.  There is a definite temporal element.
> 
> All this indicates there is little use in saving all
> the posts of anyone nor is discarding posts based
> solely on who sent wise.
> 
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]  
> 
> 
> 
> -Original Message-
> Sent: Tuesday, November 26, 2002 7:24 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Read the post Ian, this isn't a popularity poll, I
> have no interest in those. I *HAVE* to cull out my
> mail box for certain reasons I can't discuss at the
> *moment*. I offered to provide a summary of the
> results if the group was interested, but that's not
> the purpose.
> 
> The truth of the matter is that I have some 20,000
> messages to cull out, so I need to establish some
> criteria on what to keep and what not to keep. My
> thinking was that there are those who consistently
> provide the most insight. That being the case, I
> would make sure that they are the ones that get
> stored for certain. 
> 
> >> Our levels of expertise on  different aspects of
> Oracle are eclectic.
> Amen!
> 
> 
> 
> Robert G. Freeman - Oracle OCP
> Oracle Database Architect
> CSX Midtier Database Administration
> Author of several Oracle books you can find on
> Amazon.com!
> 
> Londo Mollari: Ah, arrogance and stupidity all in
> the same package. How efficient of you. 
> 
>  
> 
> 
> 
> -Original Message-
> Sent: Monday, November 25, 2002 8:44 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> You are taking a poll to find out which messages are
> most important and
> worth saving?   How does that work?   I keep
> messages containing helpful
> scripts, messages which eloquently and precisely
> explain and  or demonstrate Oracle concepts and
> programming,  messages on areas of which I am
> ignorant,
> and messages which disprove beliefs I have long
> held.   Even if we all used
> the same criteria we would save different messages. 
> Our levels of expertise on  different aspects of
> Oracle are eclectic.  
> 
> 
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]  
> 
> 
> -Original Message-
> Sent: Monday, November 25, 2002 2:05 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Board - 
> 
> If you had to choose the 10 top posters here at
> Oracle-L who provided the biggest input and 
> knowledge, who would they be? Who are the top 10
> Oracle Guru's on this news group?  I'm asking
> because my mailbox is FULL, and I have to trash a
> large amount of the stored stuff thats here. I'm
> going to archive some of it to CD, but I have
> limited space, so I want to archive those people who
> are constantly offering the most insightful advice
> (I know it's available online, but I'm not always
> online and it's nice to be able to search these
> emails for targeted content).
> 
> Anyone want to take a crack at a list? You are
> welcome to email me private if you are afraid you
> would hurt someone's feelings. I

RE: Primary Key Constraints

[Whittle Jerome Contr NCI]

Title: RE: Primary Key Constraints







SELECT owner, table_name 

FROM DBA_TABLES t

WHERE NOT EXISTS

  (SELECT 'X'

  FROM DBA_CONSTRAINTS c

  WHERE c.owner = t.owner

  AND c.table_name = t.table_name

  AND c.constraint_type = 'P')

ORDER BY 1,2


Take off the ORDER BY to speed things up.


Jerry Whittle

ACIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   Mike Sardina [SMTP:[EMAIL PROTECTED]]


Is there an easy query to get a list of tables that don't have any primary

key?  


I've tried a couple of different ones, but none of them work quite right. 

Seems like this should be easy.

RE: 9i Lite

[Richard Ji]

I thought Oracle lite is a different code base from Oracle PE, SE, EE.
So it's very different.

-Original Message-
Sent: Tuesday, November 26, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L


Saira - I agree with OraCop, just ask questions here. My assumption is that
there are few differences between Oracle Lite and other Oracle versions. I
think it is based on the same Oracle code, with a few features removed. Here
is the note from the FAQ for this list:
http://www.orafaq.com/faqpol.htm#COMPAT

If I am wrong and it is entirely different, I would appreciate knowing that.


Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, November 26, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L


No one uses 9i Lite? Gurus, help me out!!!

Saira

-Original Message-
Somani
Sent: November 25, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L

I wonder if there are any mailing lists out there for Oracle 9i Lite. Or
for that matter, if any of you have used in the past or are using it now
and would like to brainstorm once in a while on this topic. I struggle
with its administration sometimes.

If you can point me in a direction (other than the Oracle forums on
their website), I would appreciate it.

Thanks,

Saira Somani
IT Support/Analyst
Hospital Logistics Inc. 

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

Re: Primary Key Constraints

[Dennis M. Heisler]

select owner, table_name from dba_tables where not exists (select 'a'
from dba_constraints where constraint_type = 'P'
and owner = dba_tables.owner and table_name = dba_tables.table_name)
order by owner, table_name;


Mike Sardina wrote:
> 
> Is there an easy query to get a list of tables that don't have any primary
> key?
> 
> I've tried a couple of different ones, but none of them work quite right.
> Seems like this should be easy.
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Sardin
>   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.com
-- 
Author: Dennis M. Heisler
  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: Primary Key Constraints

[Fink, Dan]

SQL> select table_name
  2  from dba_tables
  3  minus
  4  select table_name
  5  from dba_constraints
  6  where constraint_type = 'R';

This should work (does in my 8.1.7 test db).

What methods have you tried? and what was not working?

-Original Message-
Sent: Tuesday, November 26, 2002 12:25 PM
To: Multiple recipients of list ORACLE-L


Is there an easy query to get a list of tables that don't have any primary
key?  

I've tried a couple of different ones, but none of them work quite right. 
Seems like this should be easy.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Sardin
  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.com
-- 
Author: Fink, Dan
  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: Primary Key Constraints

[Igor Neyman]

select owner, table_name from dba_tables where (owner, table_name) not in
(select owner, table_name from dba_constraints where constraint_type = 'P');


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 2:25 PM


> Is there an easy query to get a list of tables that don't have any primary
> key?
>
> I've tried a couple of different ones, but none of them work quite right.
> Seems like this should be easy.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Sardin
>   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.com
-- 
Author: Igor Neyman
  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: Primary Key Constraints

[Mercadante, Thomas F]

Mike,

How about:

select table_name from user_tables a
  where not exists(select 1 from user_constraints b
 where a.table_name = b.table_name
 and   b.constraint_type = 'P');


Constraint_type values are:

P = Primary Key
C = Check Constraint
R = Referential Constraint (Foreign Key)
U = Unique Constraint

There might be others.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, November 26, 2002 2:25 PM
To: Multiple recipients of list ORACLE-L


Is there an easy query to get a list of tables that don't have any primary
key?  

I've tried a couple of different ones, but none of them work quite right. 
Seems like this should be easy.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Sardin
  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.com
-- 
Author: Mercadante, Thomas F
  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: SQL tuning help

[Arup Nanda]

Sergei,

When the query is running try to collect some stats, especially session
waits, from v$session_wait and see where the waits are happening.

Or you could do this from command line

alter session set event '10046 trace name context forever, level 8';
<< your query>>
alter session set event '10046 trace name context off';

This will produce a trace file in user_dump_dest directory. Tkprof that fiel
to see the explain plans and all, see if everything is as per expectation.
>From the raw trace file you could see the wait events occuring and where
they occur.

My guess is you have seen buffer busy waits on most cases. Increase the
initrans, maxtrans, freelist and freelist groups parameter of the indexes
used in this query and rebuild them. This will alleviate several problems.

Did someone chaneg the optimizer_goal? Did you have RULE before and CHOOSE
now?

If you use RULE, did someone analyzed any of the tables, including SYS owner
tables?

HTH

Arup Nanda
www.proligence.com



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 1:54 PM


> Hello everybody,
>
> I have the following query that runs every week.
>
> UPDATE tmp_brian_metareward1 tmp
>   SET offers_seen  = (SELECT count(f.fastcash_id) FROM
> metareward.fastcash f
>  WHERE f.subsite_id = tmp.subsite_id
>and attempt >= trunc(sysdate-1)
>and attempt < trunc(sysdate)
>  group by tmp.subsite_id);
>
> This week it began to hang and I can't figure out why.  No changes were
> made to a database.  Please advise me on how I can tune it, which hints
> to add, or anything else I can do.
>
> Thank you
> Sergei
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sergei
>   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.com
-- 
Author: Arup Nanda
  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: LGWR using lots of CPU time, low CPU usage

[Deborah Lorraine]

Thanks for this paper--I will share it with our Sun engineer.

BTW, to clarify, I have 20 redo groups with 2 members each...I think I'm 
saying it right:

LOGFILE Group 1 ('/disk1/log01.log',
 '/disk2/log01.log') size 100M,
Group 2 ('/disk1/log02.log',
 '/disk2/log02.log') size 100M,
...
Group 20 ('/disk1/log20.log',
  '/disk2/log20.log') size 100M

The oldest log in the group is dated yesterday; but more than half have 
today's date.  I had one period today where there were 4 switches less than 
a minute apart!!

Debi

At 11:15 AM 11/26/2002 -0800, you wrote:
So what is discussed in this paper is outdated alreadyuh! 
www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14 
talk about Oracle Redo Logs.

As a first attempt, I would consider reducing the number of log members 
(from 20 to 4, or even 3) than removing them altogether. This will be of 
some help right away. But monitor further and decide if more Groups are 
needed to help archiver process.

Do not change multiple things at the same time.

Good Luck,

- Kirti

-Original Message-
Sent: Tuesday, November 26, 2002 12:00 PM
To: Multiple recipients of list ORACLE-L


We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.

Periodically throughout the day the LGWR background process clocks 20+
minutes of CPU time while actual CPU usage is quite low. I ran a statspack
report and for a 45-minute period that included the slow LGWR process.

The top 5 timed events in my 45-minute report are:

CPU time 1,295 60.41
db file sequential read 392,516 341 15.91
db file scattered read 70,245 168 7.85
log file sync 26,916 133 6.22
library cache pin 22 59 2.76

(Now that the top 5 is "timed" events, 3 spots almost always include CPU
and the db file reads, so I only get two other events, usually log file
sync, sometimes enqueue or latch free.)

Statspack also shows the log file parallel write had 28,589 timeouts in
that 45 minute period--rather typical for us.

I have session_cached_cursors set to 150.

I am considering the following:

1. Removing my own redo log duplexing (mirroring) since redo logs are on
the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
My sysadmin talked to the sun engineer yesterday and he said this is
"old school" thinking that redo logs should not be on RAID5. He said
because the RAID controller caches to memory all IO requests from
the CPUs, all physical writes to disk are done behind the scenes
(known as writebehind). He says the system is NOT waiting for IO.

2. Increasing redo log size (again). For the most part, log switches
average 2.5 per day, although there were 20 times in the last month of 3-7
switches in a half hour. My logs are about 100 MB in 2 groups of 20 members
each.

3. Upping the session_cached_cursors to ? (in response to the library cache
pin event).

Or is there a better option I'm overlooking?

I would appreciate some advise on the best approach to resolve the slow
LGWR process, especially your thoughts on option 1.

Thanks,
Debi
Deborah Lorraine, DBA
University of California, Davis
[EMAIL PROTECTED]

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

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

Re: 9i Lite

[Ora NT DBA]

Hi Dennis,

This is a completely separate product, it is designed for mobile devices 
like pda's etc.

Regards,
John

DENNIS WILLIAMS wrote:

Saira - I agree with OraCop, just ask questions here. My assumption is that
there are few differences between Oracle Lite and other Oracle versions. I
think it is based on the same Oracle code, with a few features removed. Here
is the note from the FAQ for this list:
http://www.orafaq.com/faqpol.htm#COMPAT

If I am wrong and it is entirely different, I would appreciate knowing that.


Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, November 26, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L


No one uses 9i Lite? Gurus, help me out!!!

Saira

-Original Message-
Somani
Sent: November 25, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L

I wonder if there are any mailing lists out there for Oracle 9i Lite. Or
for that matter, if any of you have used in the past or are using it now
and would like to brainstorm once in a while on this topic. I struggle
with its administration sometimes.

If you can point me in a direction (other than the Oracle forums on
their website), I would appreciate it.

Thanks,

Saira Somani
IT Support/Analyst
Hospital Logistics Inc. 

 




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ora NT DBA
 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: LGWR using lots of CPU time, low CPU usage

[Rajesh . Rao]

Debi,

A log file sync event usually indicates that the application is probably
committing too often, and LGWR cannot keep pace with it. You might be
experiencing contention or I/O issues, on the disks where the redo log
files are placed. Moving your redo log files away from RAID 5 is a step in
the right direction. Else, try reducing the number of commits in the
application. If thats not the problem, then you probably have a large value
for log buffer.

My experience is that the top 5 events can be misleading. Sometimes, you
resolve an event at a lower level, and the top ones resolve on their own.

Regards
Raj




   
   
"Deshpande, Kirti" 
   

rizon.com> cc: 
   
Sent by:   Subject: RE: LGWR using lots of CPU 
time, low CPU usage
[EMAIL PROTECTED]   
   
   
   
   
   
November 26, 2002  
   
02:15 PM   
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   




So what is discussed in this paper is outdated alreadyuh!
www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14
talk about Oracle Redo Logs.

As a first attempt, I would consider reducing the number of log members
(from 20 to 4, or even 3) than removing them altogether. This will be of
some help right away. But monitor further and decide if more Groups are
needed to help archiver process.

Do not change multiple things at the same time.

Good Luck,

- Kirti

-Original Message-
Sent: Tuesday, November 26, 2002 12:00 PM
To: Multiple recipients of list ORACLE-L


We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.

Periodically throughout the day the LGWR background process clocks 20+
minutes of CPU time while actual CPU usage is quite low. I ran a statspack
report and for a 45-minute period that included the slow LGWR process.

The top 5 timed events in my 45-minute report are:

CPU time 1,295 60.41
db file sequential read 392,516 341 15.91
db file scattered read 70,245 168 7.85
log file sync 26,916 133 6.22
library cache pin 22 59 2.76

(Now that the top 5 is "timed" events, 3 spots almost always include CPU
and the db file reads, so I only get two other events, usually log file
sync, sometimes enqueue or latch free.)

Statspack also shows the log file parallel write had 28,589 timeouts in
that 45 minute period--rather typical for us.

I have session_cached_cursors set to 150.

I am considering the following:

1. Removing my own redo log duplexing (mirroring) since redo logs are on
the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
My sysadmin talked to the sun engineer yesterday and he said this is
"old school" thinking that redo logs should not be on RAID5. He said
because the RAID controller caches to memory all IO requests from
the CPUs, all physical writes to disk are done behind the scenes
(known as writebehind). He says the system is NOT waiting for IO.

2. Increasing redo log size (again). For the most part, log switches
average 2.5 per day, although there were 20 times in the last month of 3-7
switches in a half hour. My logs are about 100 MB in 2 groups of 20 members

each.

3. Upping the session_cached_cursors to ? (in response to the library cache

pin event).

Or is there a better option I'm overlooking?

I would appreciate some advise on the best approach to resolve the slow
LGWR process, especially your thoughts on option 1.

Thanks,
Debi
Deborah Lorraine, DBA
University of California, Davis
[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Service

RE: Slightly OT - Who would you take with you...

[MacGregor, Ian A.]

I did read your post.  I stated you were taking a poll, the "popularity" 
characterization is no where to be found in my message.  Nor is there any implication 
in my post that you were running a "beauty contest".   Perhaps my choice of the word 
important instead of useful or informative was what caused the confusion.  

Once one has acquired a certain amount of Oracle expertise most posts are mere 
confirmations of what one already knows, and  things one learns from this forum are 
added  to that expertise increasing the likelihood that future posts will be 
reconfirmations.  There are certainly persons whose expertise greatly exceeds mine.  
These people are most likely to post messages I find worth saving.  There are folks at 
roughly the same level.  Nearly all of their posts are  reconfirmations, but there are 
still many  worth saving.  There are those who I would judge to have a poorer 
understanding of most of Oracle, but have a better grasp on certain specifics.  Very 
few of their posts are worth saving, but those which are, are  as informative as  
"expertise greatly exceeds" group.

Along the same lines, no one on this group is infallible.   There are lurkers  who 
post very infrequently, but when they do  demonstrate a very detailed knowledge of a 
facet of Oracle when they correct or complete  answers given by folks rightly held in 
high esteem.   Your polling method would miss these important posts.

We are presently moving toward RMAN.  I'm currently saving many of your posts, but as 
I acquire more knowledge and the new becomes second nature many of those posts will 
lose their importance and be discarded.  There is a definite temporal element.

All this indicates there is little use in saving all the posts of anyone nor is 
discarding posts based solely on who sent wise.

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]  



-Original Message-
Sent: Tuesday, November 26, 2002 7:24 AM
To: Multiple recipients of list ORACLE-L


Read the post Ian, this isn't a popularity poll, I have no interest in those. I *HAVE* 
to cull out my mail box for certain reasons I can't discuss at the *moment*. I offered 
to provide a summary of the results if the group was interested, but that's not the 
purpose.

The truth of the matter is that I have some 20,000 messages to cull out, so I need to 
establish some criteria on what to keep and what not to keep. My thinking was that 
there are those who consistently provide the most insight. That being the case, I 
would make sure that they are the ones that get stored for certain. 

>> Our levels of expertise on  different aspects of Oracle are eclectic.
Amen!



Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!

Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of 
you. 

 



-Original Message-
Sent: Monday, November 25, 2002 8:44 PM
To: Multiple recipients of list ORACLE-L


You are taking a poll to find out which messages are most important and
worth saving?   How does that work?   I keep messages containing helpful
scripts, messages which eloquently and precisely explain and  or demonstrate Oracle 
concepts and programming,  messages on areas of which I am ignorant,
and messages which disprove beliefs I have long held.   Even if we all used
the same criteria we would save different messages.  Our levels of expertise on  
different aspects of Oracle are eclectic.  


Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]  


-Original Message-
Sent: Monday, November 25, 2002 2:05 PM
To: Multiple recipients of list ORACLE-L


Board - 

If you had to choose the 10 top posters here at Oracle-L who provided the biggest 
input and 
knowledge, who would they be? Who are the top 10 Oracle Guru's on this news group?  
I'm asking because my mailbox is FULL, and I have to trash a large amount of the 
stored stuff thats here. I'm going to archive some of it to CD, but I have limited 
space, so I want to archive those people who are constantly offering the most 
insightful advice (I know it's available online, but I'm not always online and it's 
nice to be able to search these emails for targeted content).

Anyone want to take a crack at a list? You are welcome to email me private if you are 
afraid you would hurt someone's feelings. I'd be happy to compile the lists and report 
back to the group the overall answers, but all email to me will be treated as strictly 
confidential.


Robert


Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!

Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of 
you. 

 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Freeman, Robert
  INET: [EMAIL PROTECTED]

Fat City Network Services-

RE: redo log file setup with mirrored drives

[Jesse, Rich]

Precisely why I name all Oracle files with ".dbf" extension.  Who ever
thought naming a redo log with ".log" was a good idea???  With all DB files
the same, it's a simple rule:  Don't touch .dbf files.  No confusion there.
If you need to mess with them (backups, etc), write and test a script.  Try
to prevent yourself from making a mistake.

"If at first you don't succeed, you had better be in test."

My $.02,
Rich


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

> -Original Message-
> From: Fink, Dan [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 26, 2002 10:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: redo log file setup with mirrored drives
> 

[snip] 

> not protect against the accidental deletion of the file. I 
> have had to deal
> with situations where people deleted the redo logs (disk 
> space at 90%, let's
> clear out the log files...). Another copy on another device 

[snip]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).

[Q] ORACLE 9i remote export "insufficient previlege"?

[dist cash]

I am doing the ORACLE 9iR2 database test and tried to export form a 8i
computer to 9iR2 server.  I got "ORA-01031: insufficient privileges".

my export parfile on 8i server is:


userid='sys/passwd1@db92 as sysdba'
file=/tmp/exp_db8i_full.dmp
buffer=6400
compress=n
grants=y
indexes=y
rows=y
feedback=1
constraints=y
consistent=y
log=exp_db8i_full.log
full=y




I got:

Export: Release 8.1.7.2.0 - Production on Tue Nov 26 14:02:57 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


EXP-00056: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
Username:


Does anyone know why??

Thanks.

_
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.com
--
Author: dist cash
 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: LGWR using lots of CPU time, low CPU usage

[John Shaw]

I don't think changing the logmembers will do much good - I 
have a v880 with 16 GB and a Hitachi san. I have only 3 logmembers  - 4 
groups, they are on the local drive. I have some kind of performance issue with 
periodic slowdowns (47 minutes lgwr cpu in one day on a very low transaction 
system) - still working on the tar. A very odd scenario - an export file which 
takes 3 minutes to import on my laptop db - takes 8 minutes on to import on the 
production db if it's on the san. if I move the dmp to the local drive 
and do the import it takes 20 minutes.
>>> [EMAIL PROTECTED] 11/26/02 01:15PM 
>>>So what is discussed in this paper is outdated alreadyuh! 
www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14 talk 
about Oracle Redo Logs.  As a first attempt, I would consider 
reducing the number of log members (from 20 to 4, or even 3) than removing them 
altogether. This will be of some help right away. But monitor further and decide 
if more Groups are needed to help archiver process. Do not change 
multiple things at the same time. Good Luck,- Kirti   
-Original Message-Sent: Tuesday, November 26, 2002 12:00 
PMTo: Multiple recipients of list ORACLE-LWe are on 9.2.0.2, 
Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB on a 
hardware-controlled, mirrored RAID5 StorEdge T-3 Array.Periodically 
throughout the day the LGWR background process clocks 20+ minutes of CPU 
time while actual CPU usage is quite low. I ran a statspack report and for a 
45-minute period that included the slow LGWR process.The top 5 timed 
events in my 45-minute report are:CPU time 1,295 60.41db file 
sequential read 392,516 341 15.91db file scattered read 70,245 168 
7.85log file sync 26,916 133 6.22library cache pin 22 59 
2.76(Now that the top 5 is "timed" events, 3 spots almost always include 
CPU and the db file reads, so I only get two other events, usually log file 
sync, sometimes enqueue or latch free.)Statspack also shows the log 
file parallel write had 28,589 timeouts in that 45 minute period--rather 
typical for us.I have session_cached_cursors set to 150.I am 
considering the following:1. Removing my own redo log duplexing 
(mirroring) since redo logs are onthe mirrored, hardware-controlled RAID5 
disk array. (I know, I know)My sysadmin talked to the sun engineer yesterday 
and he said this is"old school" thinking that redo logs should not be on 
RAID5. He saidbecause the RAID controller caches to memory all IO requests 
fromthe CPUs, all physical writes to disk are done behind the 
scenes(known as writebehind). He says the system is NOT waiting for 
IO.2. Increasing redo log size (again). For the most part, log switches 
average 2.5 per day, although there were 20 times in the last month of 3-7 
switches in a half hour. My logs are about 100 MB in 2 groups of 20 members 
each.3. Upping the session_cached_cursors to ? (in response to the 
library cache pin event).Or is there a better option I'm 
overlooking?I would appreciate some advise on the best approach to 
resolve the slow LGWR process, especially your thoughts on option 
1.Thanks,DebiDeborah Lorraine, DBAUniversity of California, 
Davis[EMAIL PROTECTED] -- Please see the official ORACLE-L 
FAQ: http://www.orafaq.com-- Author: 
Deborah Lorraine  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).-- Please see the 
official ORACLE-L FAQ: http://www.orafaq.com-- Author: 
Deshpande, Kirti  INET: [EMAIL PROTECTED]Fat City 
Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).

RE: Slightly OT - Who would you take with you...

[Steve McClure]

>And everything I've learned about single malts, I've learned from this
list.
And save in my Teetotaller folder ;)

This post is not near off topic enough...please forward this folder

Steve McClure

-Original Message-
Michael T
Sent: Tuesday, November 26, 2002 10:17 AM
To: Multiple recipients of list ORACLE-L



And everything I've learned about single malts, I've learned from this list.
And save in my Teetotaller folder ;)


-Original Message-

You're saving all my fly-fishing emails, right?

Man, I wish I was popular.

--Walt Weaver
  Bozeman, Montana

+**+
This transmission is intended only for use by the addressee(s) named herein
and may contain information that is proprietary, confidential and/or legally
privileged. If you are not the intended recipient, you are hereby notified
that any disclosure, copying, distribution, or use of the information
contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If
you received this transmission in error, please immediately contact the
sender and destroy the material in its entirety, whether in electronic or
hard copy format. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hand, Michael T
  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.com
-- 
Author: Steve McClure
  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: redo log file setup with mirrored drives

[Fink, Dan]

Stephen,
Nothing is gained by personal attacks in this forum. This forum is
intended to be a learning experience for all (myself included). I suggest
that you review the archived list and examine the quality of posts by Kirti,
Jared, et.al. They speak for themselves.

BTW, 2 + 2 does equal 15 for very large values of 2 and very small
values of 15. ;)
Dan Fink

-Original Message-
Sent: Tuesday, November 26, 2002 11:50 AM
To: Multiple recipients of list ORACLE-L



I was going to let the differences of opinion stand, but I suppose this
requires an answer.

> -Original Message-
> 
> Redo and archived redo logs are the most important files in 
> the database. 
> Lose a datafile? You can still recover the database.
> Lose all controlfiles? They can be recreated.
> Lose a single redo entry? Your recovery is terminated. Yes, there are
> unsupported methods to bypass this condition, but they are 
> kludges and may
> be very, very expensive.

While all this is true, this is all based on the forgone conclusion that
mirroring outside Oracle will result in file loss.  It is that conclusion
with which I disagree.

> So, why do I still multiplex my redo logs (even on my 'test' 
> Win2k databases
> at home)? O/S level mirroring protects against some failures, 
> but it does
> not protect against the accidental deletion of the file. I 
> have had to deal
> with situations where people deleted the redo logs (disk 
> space at 90%, let's
> clear out the log files...). Another copy on another device 
> (usually with a
> separate controller), saved the database.

In your case, your problems are not related to mirroring technique. Yours
deal with how best to handle pathological situations, shops with
non-existent security, and incompetent administration.  That's certainly a
valid topic for discussion, but isn't the topic of my discussion.

> 
> Considering the small size of the redo logs and their 
> critical importance to
> the database, I'll both multiplex (oracle) and mirror (o/s).
> 

The redo logs on our production databases are from 100 Mb to 1 Gb.  Hence,
the issue is not just one of how bullet proof things can be made, but one of
performance too.

> 
> "1.  This is pure speculation."
> Kirti is one of the many people on this list who has shown 
> time and time
> again that he does not engage in "pure speculation".

In this case he was.  He was speculating about how OS's and RAIDing hardware
go about their business and how reliably they do it.  He was speculating
that, if one does not mirror via Oracle, then one will get bad redo files.

As is commonly the case, there is some tendency to assign human qualities to
computer things; things such as "knowing" about something; and the capacity
to reason and make decisions; and the ability to have a moment of
inattention when it just "forgot" to do something right.  Computers (other
than ones named HAL) don't work this way.  The original question was posed
by someone who we can safely assume is NOT running a database on some
crapola OS with rickety, unreliable, and outdated hardware (in which case, I
don't think ANY kind of mirroring will help).  If this discussion were ten
years ago, you might have a point.  But this isn't ten years ago.

> While a skeptical
> attitude is good and helps you develop, I tend to accept 
> Kirti's posts (and
> Cary's, Tim's, Jared's, Robert's, and others on the 10 list) 
> at face value
> until I put together a test case and can prove it or disprove it.

It sounds to me like you don't intend to do either.

If Stephen Hawking attempted to tell me that, in the natural integers, 2 + 2
= 15, I would know immediately that what he was telling me was incorrect.  I
have no knowledge of Kirti's expertise in computer operating systems and
hardware.  But I do know the facts.  In my past life as a Unix sys admin, I
worked with OS's and RAID hardware enough to know that file maintenance
isn't the roll of the dice that you are making it out to be.  The suggestion
that an EMC array can't be trusted to properly mirror files raises the
question: Why did you spend a million bucks on it then?

And from another post ...
> Because my OS/hardware IS reliable a corrupted 
> log file that is mirrored outside of Oracle will be corrupt - 
> the original is corrupt, so is the mirror.

In one sentence you have claimed that your "OS/hardware IS reliable" and
talk about it corrupting log files.  Are we having a problem with the
definition of "reliable" here?

And from another post
> Ditto.
> 
> The biggest problem with non-Oracle-mirrored  redo log is
> a personnel issue.
> 
> Take it from someone who's experienced a SA deleting all
> files from a 500 Gig DW during the middle of the day.

This goes back to an old post of using NT versus Unix.  If you recall, my
reply was that security on NT was so bad, that it is not a good choice.
This stems primarily from the fact that NT is essentially a single-user OS,
built around the administrator, wit

Re: Mass updates to production tables (NULL to non-NULL)

[Jonathan Lewis]

Point (1)
As Larry Elkins pointed out to me in an offline post,
I had forgotten to highlight the fact that even null CHAR
columns do still use a length byte (unless they are
trailing nulls - i.e. there are no following non-null columns).
Apart from this, a row still needs:
two byte entry in the row index in the block
one lock byte
one byte column count -  (guess how Oracle
manages rows with more than 255 columns)
one byte flags

Also, Oracle assumes that a row MAY have to
migrate at some time, requiring enough space
to be reserved for a 6-byte rowid.  So the maximum
rowcount in a block is (roughly) blocksize / 11.

Point (2)
Each iteration through the outer loop, or each
iteration of the 'update 10,000'.  Apart from the
1555, the main problem with a counted loop is
that (in theory) it does a lot more work to achieve
the same result as a 'proper' update statement.
In practice, it may be possible to introduce side-effects
on bulk update strategies that cause worse problems
than the loop, though. For example, the 'each iteration
takes longer than the last' is likely to be related to
a mixture of delayed block cleanout (particularly
in indexes), attempts at read-consistency, and
cyclic block flushing.

Note - ORA-01555 need not matter, if you have
a mechanism that can respond to it gracefully.


Regards

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

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

Denver___December 2/4
England__January 21/23


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





-Original Message-
To: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Cc: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Date: 26 November 2002 18:16


>Hey Jonathon,
>
>Two questions about your response:
>
>1)  Yes, you are obviously correct.  My test was flawed.  So, if
NULLs use
>no space, then why does many NULL rows cause a table to extend?  Is
it
>because of the row directory in the data block header?  Egad...going
back to
>DBA school here.My apologies to my Oracle DBA Instructor!
I've
>tried testing this theory, but I'm not having any luck.
>
>2)  The update works fine, except that each iteration takes
progressively
>longer to run to the point that it's not feasible to run in
production.  So,
>what's wrong with the counted cursor loop, other than the possibility
of
>ORA-1555?
>
>Thx!  :)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: LGWR using lots of CPU time, low CPU usage

[Deborah Lorraine]

At 10:50 AM 11/26/2002 -0800, you wrote:

Second, if what you are telling ("logs are about 100 MB in 2 groups of 20
members each") is accurate, then this is your main problem.  If you have
your log switches on avg 2.5 per day, change your RedoLog configuration 
You will have more log switches
per day, but it's perfectly fine as long, as don't have them every 5 min.


Oracle tells me to set redo logs according to the busiest period of log 
switching, even if they happen once a week.  At least 20 times in the last 
month I had log switches with 1-3 minutes between switches.  Accordingly to 
Oracle, I should increase their size again!

And "old school" is still right about not putting RedoLogs onto RAID5.


From what I'm being told, this is not your father's RAID5.  This is what 
they tell me:

The CPU hands the IO to the disk controller and rather than do the
physical disk IO while the process waits, the disk controller caches
it to local memory and says done.  Therefore, effectively there is no
wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1,
the system is NOT waiting for the IO. He said the only time there might
be a delay is during the cache's battery refresh times. I checked your
dates and it was not occurring during those times. Also, if you look
at the iostat statistics under the 'wait' and '%w' headers you will
see all zeros.

I did run iostat while this logwriter process was clunking
away and they were all zeros.  I have a bottleneck, but there is no 
indication it is in disk.

Debi

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deborah Lorraine
 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: Slightly OT - Who would you take with you...

[Jesse, Rich]

Schlitz gives me the Blatz.  (But Blatz gives me the Schlitz)

And most beers, even watered down American lagers (like malt liquors), use
multiple types of grains in their malts.

Olde English 800???!??  In the words of the immortal Don Martin, "Blech!".

And where's the venerable Colt 45?

Of course, Mickey's Big Mouth was the Official Beer of Nintendo...

:)

Rich


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


> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 26, 2002 1:04 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Slightly OT - Who would you take with you...
> 
> 
> Well, there's Schlitz, King Cobra, Olde English, Mickey's Big 
> Mouth, et al.
> Single Malt Liquor at its finest!
> 
> Scott Shafer
> San Antonio, TX
> 210.581.6217
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Mass updates to production tables (NULL to non-NULL)

[Jonathan Lewis]

You do have to be careful in version 9, though,
since the really is an MBRC - which is the
system stat which is the average size of
multi block read count actually achieved over
a time period, rather than the value requested
in the db_file_multiblock_read_count parameter.

(The real MBRC is used for costing when 
the new cpu_costing method is switched on, 
an adjusted dbf_mbrc is used when the 
traditional io_costing method is the only thing
in place).


Regards

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

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

Denver___December 2/4
England__January 21/23


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: 26 November 2002 18:18


>multiblock_read_count as in db_file_multiblock_read_count. 
>
>- Kirti
>
>-Original Message-
>Sent: Tuesday, November 26, 2002 11:24 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>MBRC stands for what ?
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: SQL tuning help

[Whittle Jerome Contr NCI]

Title: RE: SQL tuning help






Sergei,


How many records in each table? What indexes are in these tables? What version of Oracle?


What do you mean by 'began to hang'?


I'd try making the attempts in the WHERE clause into a Between. I'd also try grouping by f.subsite_id.


You could always throw a Rule hint at it and see what happens.


Jerry Whittle

ACIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   Sergei [SMTP:[EMAIL PROTECTED]]


Hello everybody,


I have the following query that runs every week.  


UPDATE tmp_brian_metareward1 tmp

  SET offers_seen  = (SELECT count(f.fastcash_id) FROM

metareward.fastcash f

 WHERE f.subsite_id = tmp.subsite_id

   and attempt >= trunc(sysdate-1)

   and attempt < trunc(sysdate)

 group by tmp.subsite_id);


This week it began to hang and I can't figure out why.  No changes were

made to a database.  Please advise me on how I can tune it, which hints

to add, or anything else I can do.


Thank you

Sergei 

RE: LGWR using lots of CPU time, low CPU usage

[Deborah Lorraine]

Interesting; but would the specific performance affect of a high number of 
session_cached_cursors involve the LGWR process?


At 11:25 AM 11/26/2002 -0800, you wrote:

In some of our benchmarks with our hybrid application on Oracle 8.1.7 , 
Oversizing session_cached_cursors would HARM performance greatly . Our 
Optimal Value is 50


-Original Message-
Sent: Wednesday, November 27, 2002 12:20 AM
To: Multiple recipients of list ORACLE-L


Deborah,

First, don't remove Oracle's RedoLog duplexing, you may regret about it
later (see recent thread on this issue).

Second, if what you are telling ("logs are about 100 MB in 2 groups of 20
members each") is accurate, then this is your main problem.  If you have
your log switches on avg 2.5 per day, change your RedoLog configuration to
be: 3 (or 4) groups, 3 members each (if you can put them on separate
"physical" devices, if not - 2 members should suffice), and you can make
them smaller, like 50Mb (or even smaller).  You will have more log switches
per day, but it's perfectly fine as long, as don't have them every 5 min.

And "old school" is still right about not putting RedoLogs onto RAID5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 1:00 PM


> We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
> on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
> Periodically throughout the day the LGWR background process clocks 20+
> minutes of CPU time while actual CPU usage is quite low. I ran a statspack
> report and for a 45-minute period that included the slow LGWR process.
>
> The top 5 timed events in my 45-minute report are:
>
> CPU time 1,295 60.41
> db file sequential read 392,516 341 15.91
> db file scattered read 70,245 168 7.85
> log file sync 26,916 133 6.22
> library cache pin 22 59 2.76
>
> (Now that the top 5 is "timed" events, 3 spots almost always include CPU
> and the db file reads, so I only get two other events, usually log file
> sync, sometimes enqueue or latch free.)
>
> Statspack also shows the log file parallel write had 28,589 timeouts in
> that 45 minute period--rather typical for us.
>
> I have session_cached_cursors set to 150.
>
> I am considering the following:
>
> 1. Removing my own redo log duplexing (mirroring) since redo logs are on
> the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
> My sysadmin talked to the sun engineer yesterday and he said this is
> "old school" thinking that redo logs should not be on RAID5. He said
> because the RAID controller caches to memory all IO requests from
> the CPUs, all physical writes to disk are done behind the scenes
> (known as writebehind). He says the system is NOT waiting for IO.
>
> 2. Increasing redo log size (again). For the most part, log switches
> average 2.5 per day, although there were 20 times in the last month of 3-7
> switches in a half hour. My logs are about 100 MB in 2 groups of 20
members
> each.
>
> 3. Upping the session_cached_cursors to ? (in response to the library
cache
> pin event).
>
> Or is there a better option I'm overlooking?
>
> I would appreciate some advise on the best approach to resolve the slow
> LGWR process, especially your thoughts on option 1.
>
> Thanks,
> Debi
> Deborah Lorraine, DBA
> University of California, Davis
> [EMAIL PROTECTED]
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deborah Lorraine
 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 are rollback segments assigned?

[Cary Millsap]

Exactly. A guy might have to poll V$ROLLSTAT 100+ times per second to
see the allocations happening on a system with well-designed
transactions. You just can't do that with SQL.

Raj, you *can* test the behavior by using some artificially *bad*
transactions. On a quiescent system (nothing else running) with n
rollback segments, open n+1 sqlplus sessions. In each session, update a
row but don't commit. After each session's update, look at V$ROLLSTAT. 

To the best of my knowledge, the assignment algorithm has always been to
assign a new txn to the segment with the smallest number of active txns
in it. There are of course lots of ties (e.g., lots of segments might
have 0 active txns in them). The tie-breaker in v6 was to assign in
round-robin order (as the segments are listed in the init.ora file). In
v7, the tie-breaker algorithm apparently changed to choose the segment
that is listed earliest in the init.ora file. I don't know whether it
changed again in 8 or 9. You'll know if you do the test.


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

Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, November 26, 2002 12:25 PM
To: Multiple recipients of list ORACLE-L

Raj,

You're assuming that the transactions are lasting long enough
for you to catch them in the rollback segments.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 11/26/2002 09:42 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc: 
Subject:Re: How are rollback segments assigned?



Thanks Jared, thats not what I see. I have 20 rollback segments, and at
no
point in time, there were more than
4 transactions in the rollback segments. In fact, I happened to see it a
couple of minutes ago.

select usn, xacts from v$rollstat showed me 0 in 19 rollback segments,
and
2 in one of them. And I have got alerts in place
to alert me when there are more than 4 transactions in all of the
rollback
segments. And yeah, the rollback segments
are online.

Remains a mystery to me.

Raj






  
Jared.Still@r  
adisys.com   To: Multiple recipients of 
list ORACLE-L <[EMAIL PROTECTED]> 
Sent by: cc:   
root@fatcity.Subject: Re: How are
rollback 
segments assigned? 
com  
  
  
November 25,  
2002 08:59 PM  
Please  
respond to  
ORACLE-L  
  
  




Here's one scenario:

4 transactions, 2 rollback segs

tx 1 - rbs1
tx 2 - rbs 2
tx 3  - rbs 1
tx 4 - rbs 2

tx1 and tx3 finish.

tx2 and tx4 have not yet committed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 11/25/2002 07:39 AM
 Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
cc:
Subject:How are rollback segments assigned?


Hello Friends,

Oracle Parallel Server 8.0.6.2.0 on Solaris 2.6 Nodes

We were having some locking and rollback issues, and I set up some
scripts
to alert me in case there are more than 4 transactions in the rollback
segments, and more than 1 in any of the rollback segments.

Select sum(xacts) from v$rollstat  --- Alert if more than 4, condition
set
to exclude system rollback
where usn != 0

Select count(*) from v$rollstat  --- Alert if more than 0, where
condition to exclude system rollback
where xacts >=2
andusn != 0

We have 20 rollback segments, and there is very minimal DML activity on
this database. I happened to see a scenario where all rollback segments
had
0 transactions, except for one which had 2 in them. Under what scenario,
can this happen?

I thought the criteria for assigning rollback segments to transactions
was
1. If object in system tablespace, use system rolback segment
2. Use the one with the least number of active transactions
3. If 2 or more rollback segments fit the second criteria, use the LRU
algorithm.

Thanks
Raj

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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.com
--
Author:
  INET: [EMAIL PROTECTED]

Primary Key Constraints

[Mike Sardina]

Is there an easy query to get a list of tables that don't have any primary
key?  

I've tried a couple of different ones, but none of them work quite right. 
Seems like this should be easy.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Sardin
  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: LGWR using lots of CPU time, low CPU usage

[VIVEK_SHARMA]

In some of our benchmarks with our hybrid application on Oracle 8.1.7 , Oversizing 
session_cached_cursors would HARM performance greatly . Our Optimal Value is 50


-Original Message-
Sent: Wednesday, November 27, 2002 12:20 AM
To: Multiple recipients of list ORACLE-L


Deborah,

First, don't remove Oracle's RedoLog duplexing, you may regret about it
later (see recent thread on this issue).

Second, if what you are telling ("logs are about 100 MB in 2 groups of 20
members each") is accurate, then this is your main problem.  If you have
your log switches on avg 2.5 per day, change your RedoLog configuration to
be: 3 (or 4) groups, 3 members each (if you can put them on separate
"physical" devices, if not - 2 members should suffice), and you can make
them smaller, like 50Mb (or even smaller).  You will have more log switches
per day, but it's perfectly fine as long, as don't have them every 5 min.

And "old school" is still right about not putting RedoLogs onto RAID5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 26, 2002 1:00 PM


> We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
> on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
> Periodically throughout the day the LGWR background process clocks 20+
> minutes of CPU time while actual CPU usage is quite low. I ran a statspack
> report and for a 45-minute period that included the slow LGWR process.
>
> The top 5 timed events in my 45-minute report are:
>
> CPU time 1,295 60.41
> db file sequential read 392,516 341 15.91
> db file scattered read 70,245 168 7.85
> log file sync 26,916 133 6.22
> library cache pin 22 59 2.76
>
> (Now that the top 5 is "timed" events, 3 spots almost always include CPU
> and the db file reads, so I only get two other events, usually log file
> sync, sometimes enqueue or latch free.)
>
> Statspack also shows the log file parallel write had 28,589 timeouts in
> that 45 minute period--rather typical for us.
>
> I have session_cached_cursors set to 150.
>
> I am considering the following:
>
> 1. Removing my own redo log duplexing (mirroring) since redo logs are on
> the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
> My sysadmin talked to the sun engineer yesterday and he said this is
> "old school" thinking that redo logs should not be on RAID5. He said
> because the RAID controller caches to memory all IO requests from
> the CPUs, all physical writes to disk are done behind the scenes
> (known as writebehind). He says the system is NOT waiting for IO.
>
> 2. Increasing redo log size (again). For the most part, log switches
> average 2.5 per day, although there were 20 times in the last month of 3-7
> switches in a half hour. My logs are about 100 MB in 2 groups of 20
members
> each.
>
> 3. Upping the session_cached_cursors to ? (in response to the library
cache
> pin event).
>
> Or is there a better option I'm overlooking?
>
> I would appreciate some advise on the best approach to resolve the slow
> LGWR process, especially your thoughts on option 1.
>
> Thanks,
> Debi
> Deborah Lorraine, DBA
> University of California, Davis
> [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deborah Lorraine
>   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.com
-- 
Author: Igor Neyman
  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.com
-- 
Author: VIVEK_SHARMA
  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 P

RE: 9i Lite

[DENNIS WILLIAMS]

Saira - One other point. When you submit a message, it would be a really
good idea to mention that you are using Oracle9i Lite.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, November 26, 2002 1:19 PM
To: '[EMAIL PROTECTED]'


Saira - I agree with OraCop, just ask questions here. My assumption is that
there are few differences between Oracle Lite and other Oracle versions. I
think it is based on the same Oracle code, with a few features removed. Here
is the note from the FAQ for this list:
http://www.orafaq.com/faqpol.htm#COMPAT

If I am wrong and it is entirely different, I would appreciate knowing that.


Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, November 26, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L


No one uses 9i Lite? Gurus, help me out!!!

Saira

-Original Message-
Somani
Sent: November 25, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L

I wonder if there are any mailing lists out there for Oracle 9i Lite. Or
for that matter, if any of you have used in the past or are using it now
and would like to brainstorm once in a while on this topic. I struggle
with its administration sometimes.

If you can point me in a direction (other than the Oracle forums on
their website), I would appreciate it.

Thanks,

Saira Somani
IT Support/Analyst
Hospital Logistics Inc. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Saira Somani
  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.com
-- 
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: LGWR using lots of CPU time, low CPU usage

[Deshpande, Kirti]

So what is discussed in this paper is outdated alreadyuh! 
www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14 talk about 
Oracle Redo Logs.  

As a first attempt, I would consider reducing the number of log members (from 20 to 4, 
or even 3) than removing them altogether. This will be of some help right away. But 
monitor further and decide if more Groups are needed to help archiver process. 

Do not change multiple things at the same time. 

Good Luck,

- Kirti   

-Original Message-
Sent: Tuesday, November 26, 2002 12:00 PM
To: Multiple recipients of list ORACLE-L


We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB 
on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.

Periodically throughout the day the LGWR background process clocks 20+ 
minutes of CPU time while actual CPU usage is quite low. I ran a statspack 
report and for a 45-minute period that included the slow LGWR process.

The top 5 timed events in my 45-minute report are:

CPU time 1,295 60.41
db file sequential read 392,516 341 15.91
db file scattered read 70,245 168 7.85
log file sync 26,916 133 6.22
library cache pin 22 59 2.76

(Now that the top 5 is "timed" events, 3 spots almost always include CPU 
and the db file reads, so I only get two other events, usually log file 
sync, sometimes enqueue or latch free.)

Statspack also shows the log file parallel write had 28,589 timeouts in 
that 45 minute period--rather typical for us.

I have session_cached_cursors set to 150.

I am considering the following:

1. Removing my own redo log duplexing (mirroring) since redo logs are on
the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
My sysadmin talked to the sun engineer yesterday and he said this is
"old school" thinking that redo logs should not be on RAID5. He said
because the RAID controller caches to memory all IO requests from
the CPUs, all physical writes to disk are done behind the scenes
(known as writebehind). He says the system is NOT waiting for IO.

2. Increasing redo log size (again). For the most part, log switches 
average 2.5 per day, although there were 20 times in the last month of 3-7 
switches in a half hour. My logs are about 100 MB in 2 groups of 20 members 
each.

3. Upping the session_cached_cursors to ? (in response to the library cache 
pin event).

Or is there a better option I'm overlooking?

I would appreciate some advise on the best approach to resolve the slow 
LGWR process, especially your thoughts on option 1.

Thanks,
Debi
Deborah Lorraine, DBA
University of California, Davis
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deborah Lorraine
  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.com
-- 
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: Slightly OT - Who would you take with you...

[Scott . Shafer]

Well, there's Schlitz, King Cobra, Olde English, Mickey's Big Mouth, et al.
Single Malt Liquor at its finest!

Scott Shafer
San Antonio, TX
210.581.6217


> -Original Message-
> From: Hand, Michael T [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, November 26, 2002 12:17 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: Slightly OT - Who would you take with you...
> 
> 
> And everything I've learned about single malts, I've learned from this
> list.
> And save in my Teetotaller folder ;)
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).