Re: patches
Title: Pro*C for Oracle 817 on Win2000? We only apply patches as needed and where needed. For example: I had a problem with export taking a long time on one system. I installed a patch for this problem (after testing in test environment of course) only on that database. My motto is: If it ain't broken do not fix it. I have seen too many follow up fixes to install something I do not need. Yechiel AdarMehish - Original Message - From: Boivin, Patrice J To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 27, 2002 9:05 PM Subject: patches I am wondering how your sites handle patching production servers. I just did a search in MetaLink, since 8174 was released there have been 48 patches (if I just select RDBMS). If I select other items in my search,I get upwards of 70 additional bug fixes. How do high reliability sites handle patching? I assume they would rather fix potential problems (testing the patches on a testbed of course) rather than just apply bug fixes as problems are encountered on production servers. regards, Patrice Boivin Systems Analyst (Oracle Certified DBA)
Re: question: about table(s) ?
On windows you can use the DBA console/OEM console (on 9i): select the database then go to space-tablespaces. Right click on tablespaces you get: Create. Or - read about the create tablespace command in the manual. (which you need to do anyway to understand what the GUI is doing). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 29, 2002 3:43 AM can we arrange tables in a heirarchy form, just like we have folders and under folders we have files. so this way we sort of divide workspace. say for company_A i create folder A and in it we can place files for that company. and similarly we can create a folder for company_B. Hence we can separate workspaces for better organization and management etc. so how can we accomplish as above, when we work with database ? is there a way we can arrange tables (of the database) in a heirarchy similar to folders and files ? say i have one installation of oracle on a particular computer. so how does one create separate table spaces, say for two different company or projects ? (say company_A and company_B are unrelated to each other) __ 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: john 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: Yechiel Adar 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).
Performance on TRU64 Cluster
Hi Guys, We are in the process of configuring Oracle 9i Release 2 on TRU64 Cluster (2 ES40s with RA3000, with SCSI cluster N/W no FC Channel ). So far we have done just the installation( No database creation). The problem is that System looks very slow. Perform is perfectly OK when you perform some activity on single node, but the moment you try to do same thing on both the nodes, it is having problems and takes hell lot of time. For example while starting gsd domain, from one nodes it takes fraction of second, while from other it takes atleast 10-15 seconds. Even while running root.sh this was damn slow on both the nodes, and took at least 10 good minutes. Is IT Normal??? I can't imagine this to be a normal behavior. Well, here are my Kernal Parameters, that I tried to change before installation... proc: max_per_proc_address_space = 2 gig per_proc_address_space = 2gig per_proc_stack_size = 33554432 max_per_proc_stack_size = 500m per_proc_data_size = 201326592 vm: new_wire_method = 0 vm_swap_eager = 1 Earlier this was set as vm-swap-eager = 1 and not vm_swap_eager =1 ubc_maxpercent = 70 gh_chunks = 518 I tried setting this to 518 but after booting this comes out to be 0 vfs fifo_do_adaptive defaults = 0 ipc: shm_max = 2139095040 shm_mni = 256 shm_min = 1 shm_seg = 1024 ssm_threshold = 0 I tried setting this to be 0 but after booting this has become a very large value (atleast 20 digit value) rdg: msg_size = 32768 max_objs = 5120 max_async_req = 256 max_sessions = 200 rdg_max_auto_msg_wires = 0 rdg_auto_msg_wires = 0 rt: aio_task_max_num = 300 I tried setting this to be 300 but after booting this has become 307 Hope someone would provide me their Kernal Parameter values and help trouble-shooting me. Could this performance problem be related to some hardware problem?? So far Hardware is also not fully tested, and this is a brand new setup. While bringing the system up we see some errors messages related to Eager Swap mode and Asynch I/O. Appreciate your help, 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: redo log file setup with mirrored drives
Hello Guang From your note about weekly one day long import I think that you are dealing with DW. 1) Am I correct? 2) Are there other updates to the database while the import is in progress? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 6:34 PM Hi: I am the original poster and thanks for all your inputs on this topic. Now I know more about what might happen if something goes wrong. The main purpose of we thinking doing this was to gain some performance. We have a weekly schema imp process which takes about a day to finish. We hope by eliminating redo log multiplex, but with OS mirroring we can speed up this loading process. We are going to do some tests to see how much we would gain. BTW, our unix system admin is very good, I can trust him that we would never delete any redo log files or any oracle files. So the only practical danger is that the redo file might get corrupted. This means we need to balance the performance vs file curruption. Thanks again. Guang _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guang Mei 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Replication Manager
Building replication is usually somewhat lengthy process. I spent sometime with an Oracle expert and built a skeleton script to build my replication via sqlplus. However, I use the replication manager to track the progress of the build process and to check the results. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 25, 2002 7:39 PM Has anyone used Replication Manager with success? Any feedback on issues you may have run into would be greatly appreciated. Does it come bundled with OEM or is it typically on a separate CD as a Management Pack? -- 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: Yechiel Adar 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: Security Focus Link - SQL Injection White Paper
Interesting. Thanks. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 5:54 PM SQL Injection and Oracle - By Pete Finnigan This is the first article in a two-part series that will examine SQL injection attacks against Oracle databases. The bjective of this series is to introduce Oracle users to some of the dangers of SQL injection and to suggest some simple ways of protecting against these types of attack. http://online.securityfocus.com/infocus/1644 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Yechiel Adar 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: Monitoring Tool Evaluation methodology
Hi there, If you are interested I can send you a word document which I used to select and evaluate an Oracle monitoring tool solution which might be useful for adapting to your needs. We're an NT/W2K site. BTW, we choose Quest I/Watch as our solution. Did not come across eHurkha product during our evaluation. HTH, - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] From: VIVEK_SHARMA [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 28, 2002 5:44 PM To: Multiple recipients of list ORACLE-L Subject: Monitoring Tool Evaluation methodology We are evaluating a monitoring Tool (eGurkha) for Unix/NT/Oracle monitoring What features should be Looked into while Evaluating ? Are there any Best practices for doing this kind of Evaluation ? Any Comments on this tool in particular by any who might have used this tool ? etc.. This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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).
FW: logical tuning
Dennis Ferenc, Your discussion is a good read ... You guys are able to understand how your applications are working WITH Oracle, like using RULE/COST optimizer , Table Scans and also how it is using the Oracle capabilities. I also wanted to know more about the application running on top of Oracle . Would you guys GUIDE me with some steps ( may be top 10 and how to do that ) , or you have any document which you have prepared in the past will be great help for guys like me who wanted to know more :))- This LIST is always been a great HELP for me... Happy Thanks giving to YOU ALL. Thanks Madhu -Original Message- To: Multiple recipients of list ORACLE-L Sent: 11/27/2002 4:28 PM Ferenc Thanks so much for providing an insight into what you do. Lawson uses Oracle in quite a simpler method. No joins, just individual table access. No table scans, each access is hinted to use a specific index. Crude but effective. The first issue is that it doesn't use all of Oracle's capabilities. The second issue is that it provides little opportunity for Oracle tuning experts such as yourself. But customers keep pressing for better use of Oracle, so there is hope yet. ;-) Based on what I've seen out of Lawson and wait statistics, I'm applying my efforts to reducing physical I/O. I just configured several tables for the KEEP and RECYCLE pools. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 27, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Dennis as you know, there is no 'follow these steps to get a better performing application' guide when it comes to tuning. An intimate knowledge of what the application does is a must. I sell myself (tried the street corners but was not getting much intrest) as a Siebel performance tuning specialist, so when customers say 'Oh, you are an Oracle DBA !', I respond with 'No, Oracle DBA is just one of the things I do in order to get my job done'. there are plenty of DBA's out there, (and DBB's too), but understnading how the application (in my case Siebel) works and what it is trying to accomplish from a functional perspective helps me to know immediately what is the framework of limitations I can work in. For instance, Siebel is written for RBO, so when someone comes spouting partitions and bitmap indexes, I buzz them out on try 1. now for Siebel specific EIM (Enterprise Integration Manager) type tuning , when I see that index range scans are killing me, I try to reduce the batch size first so that it will not have to go through as many records per value (think of a batch size of 20,000 records where it is doing a correlated subquery on just the batch_id). Now change this into 100 batches of 200 rows each, and immediately you have a huge saving in logical IO, since each time excpet the first iteration, the index blocks and table blocks should be found in DBBC (Also see Cary's paper on www.hotsos.com which goes into deeper details on the latches needed and the recursive calls for buffer hits.) Other things include looking at SQL where you can see it is using an index to look up a row in the table to get a single value (column). In this case, for a large load, it may be beneficial to recreate this same index with the column concatenated on the end, and avoid the table lookup altogether. Also knowing EXACTLY how RBO works (there are only about 20 rules and in reality only 5 or 6 get used in an application), will help you to know when it may even be beneficial to DROP an index (gasp ! can he be serious ? Youbetcha ! ). anyway, that is it for today, class dismissed. Have a great day ! Ferenc Mantfeld -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 28, 2002 3:40 AM To: Multiple recipients of list ORACLE-L Subject:RE: Using RECYCLE pool? Thanks Denny, Connor, and Ferenc for your helpful suggestions. Ferenc - I particularly appreciated your insights. This is also a packaged app where I can't tune the SQL. It does no table scans (long story, but that is the way this app works). My logic is that the biggest wait (85% of wait) is db file sequential read, and the BHR is fairly low, about 80%. So my thought is to increase the buffer, and while I was at it, thought I would try the KEEP and RECYCLE pools. But I find your comment about logical tuning very interesting. Can you explain more, in case I'm missing something basic? Thanks. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 26, 2002 7:29 PM To: Multiple recipients of list ORACLE-L 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),
Oracle on windows and shadow thread file access
Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of them, or none of them? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grant Allen 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: a PL/SQL design question.
Similarly, if you only want the procedure to be run IF the base transaction does a commit then you can use dbms_job (because the job submission process is part of the same txn) hth connor --- Stephane Faroult [EMAIL PROTECTED] wrote: Andrey Bronfin wrote: Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! Keyword = AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Effect of Upgrading O/S to the 817 database !!!
Hi We are currently running Oracle 817 database on a Windows NT, version 5, service pack 6. We need to upgrade O/S to Windows 2000. What should we do on the database side, do we need to do a new Oracle 817 software installation after upgrading O/S and try to startup the database or do we need to do everything from scratch, i.e. install software, create database and import ? I tought this should not have an effect on the database, if that the case, do we then need to just try to startup the database after O/S upgrade ? Please help ...your response will be highly appreciated. Desperado ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jackson Dumas 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: Do user processes apply against shmmax limit?
Jay, My understanding is that the PGA is contained within the SGA, and that shmmax is the maximum size of a single shared memory segment. If you set shmmax to 256MB and configure 1GB SGA, you should see it allocate 4 shared memory segments for that purpose. Some Unix variants have limitations on the number of shared memory segments which can either be created (AIX) or simultaneously accessed (HP-UX). I haven't done much with Sun in the last few years so don't specifically know of the Solaris limitations, but I'm sure there is probably something there to consider. That's typically why you want to set shmmax as high as you realistically can -- to reduce the NUMBER of segments you need to allocate for shared memory. Your sysadmin also mentions turning on priority paging to give the user processes access to the memory before the file cache (aka buffer cache). Again I'm not sure about Solaris, but AIX and HP-UX both ship with their buffer cache set to something like 10% - 20% of total memory by default, which is a pretty good guess for a generic system when the vendor has no idea what you'll be using it for specifically... however, for large Oracle systems, I typically tune this back a bit, depending on the memory in the system. Normally something in the 2-8% or 3-10% range is sufficient. Remember, Oracle does all it's own buffering via DB_BLOCK_BUFFERS so doesn't really need to rely on the system buffer cache, even using filesystems (of course, raw devices completely bypass the system buffer cache). You might want to see what he's got the two parameters set to which control the size of the system buffer cache; sometimes reducing that will help quite a bit with paging/swapping. Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Miller, Jay Sent: Saturday, November 23, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Do user processes apply against shmmax limit? Hi everyone, I was always under the impression that the only concern with shmmax was that it be large enough for the SGA to fit into it. One of my System Administrators has just told me that the individual user processes (i.e., the PGA since we're not using multi-threaded server) get added to the SGA and if that SGA + user processes shmmax the system will start swapping. I haven't found anything to specifically address this issue on Metalink so I though I'd throw it open. We've started experiencing system slowdown and he says that increasing shmmax could resolve it. I'm skeptical (he also suggested increasing SGA to decrease swapping which I told him in no uncertain terms was nonsense). If anyone has a link to a note or white paper I'd appreciate that too. I've appended his email at the bottom. This slowdown seems to occur even when there's virtually on oracle activity so I'm suspecting some other cause. Thanks, Jay Miller nycsun1 and njsun7 has 6 GB of memory and only 2 GB of share memory. This morning nycsun1 was very slow and I noticed that there was lots of swaping. see vmstst and iostat below in red: procs memorypage disk faults cpu r b w swap free re mf pi po fr de sr s2 s4 s4 sd in sy cs us sy id 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 3330 974 11 8 81 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 847 416 3 1 96 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 2183 670 13 4 84 0 0 23 4361568 95488 9 2264 0 76 964 95520 136 0 0 0 0 979 4065 607 12 6 82 0 0 23 4362384 96080 1 6 4 8 8 77376 0 0 0 0 0 975 465 457 2 1 97 0 0 23 4361944 95712 4 730 92 48 532 95520 64 0 0 0 0 1040 1859 734 8 3 89 0 0 23 4360424 95480 4 41 36 40 100 77376 7 0 0 0 0 986 1250 542 6 0 94 0 0 23 4361304 96096 3 264 76 36 88 88496 7 0 0 0 0 1037 942 665 5 3 92 0 0 23 4359680 95784 2 449 4 28 84 95520 8 0 0 0 0 922 1047 374 4 1 95 0 0 23 4359936 95464 2 544 4 20 332 95520 44 0 0 0 0 931 1095 384 2 2 96 /s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 0.00.00.0 0.0 0.00.00.0 0 0 c2t6d0 0.0 34.50.0 270.0 0.2 13.86.7 399.5 6 44 c5t12d0 -- swap disk 0.0 34.50.0 270.0 0.5 10.7 15.5 309.4 18 39 c5t13d0 -- swap disk This shows that the system is not effectively using memory. I suggest increasing the share memory to 4 GB so that DBAs can increase their memory usage. Also set priority paging on. Priority paging will give application first priority then free memory will be allocated to file cache( Solaris 2.6 and 7. Solaris 8 is set dynamically). * ORACLE CONFIGS set shmsys:shminfo_shmmax =204800 -- increase to 409600 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=300 set shmsys:shminfo_shmseg=30 set semsys:seminfo_semmap=500 set semsys:seminfo_semmni=200 set semsys:seminfo_semmns=2000
Re: Oracle on windows and shadow thread file access
None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Cheers Jeff Herrick On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of them, or none of them? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grant Allen 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: Jeff Herrick 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: Do user processes apply against shmmax limit?
Jay, My column counting skills might be off, but it looks like the 'sr' stat is 0 most of the time, and scan rate is the stat that I use to see if a machine is memory starved. Priority paging is a very good idea, but you'll probably see even more benefit if you can mount your oracle file systems as direct io hth connor --- Rich Holland [EMAIL PROTECTED] wrote: Jay, My understanding is that the PGA is contained within the SGA, and that shmmax is the maximum size of a single shared memory segment. If you set shmmax to 256MB and configure 1GB SGA, you should see it allocate 4 shared memory segments for that purpose. Some Unix variants have limitations on the number of shared memory segments which can either be created (AIX) or simultaneously accessed (HP-UX). I haven't done much with Sun in the last few years so don't specifically know of the Solaris limitations, but I'm sure there is probably something there to consider. That's typically why you want to set shmmax as high as you realistically can -- to reduce the NUMBER of segments you need to allocate for shared memory. Your sysadmin also mentions turning on priority paging to give the user processes access to the memory before the file cache (aka buffer cache). Again I'm not sure about Solaris, but AIX and HP-UX both ship with their buffer cache set to something like 10% - 20% of total memory by default, which is a pretty good guess for a generic system when the vendor has no idea what you'll be using it for specifically... however, for large Oracle systems, I typically tune this back a bit, depending on the memory in the system. Normally something in the 2-8% or 3-10% range is sufficient. Remember, Oracle does all it's own buffering via DB_BLOCK_BUFFERS so doesn't really need to rely on the system buffer cache, even using filesystems (of course, raw devices completely bypass the system buffer cache). You might want to see what he's got the two parameters set to which control the size of the system buffer cache; sometimes reducing that will help quite a bit with paging/swapping. Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Miller, Jay Sent: Saturday, November 23, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Do user processes apply against shmmax limit? Hi everyone, I was always under the impression that the only concern with shmmax was that it be large enough for the SGA to fit into it. One of my System Administrators has just told me that the individual user processes (i.e., the PGA since we're not using multi-threaded server) get added to the SGA and if that SGA + user processes shmmax the system will start swapping. I haven't found anything to specifically address this issue on Metalink so I though I'd throw it open. We've started experiencing system slowdown and he says that increasing shmmax could resolve it. I'm skeptical (he also suggested increasing SGA to decrease swapping which I told him in no uncertain terms was nonsense). If anyone has a link to a note or white paper I'd appreciate that too. I've appended his email at the bottom. This slowdown seems to occur even when there's virtually on oracle activity so I'm suspecting some other cause. Thanks, Jay Miller nycsun1 and njsun7 has 6 GB of memory and only 2 GB of share memory. This morning nycsun1 was very slow and I noticed that there was lots of swaping. see vmstst and iostat below in red: procs memorypage disk faults cpu r b w swap free re mf pi po fr de sr s2 s4 s4 sd in sy cs us sy id 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 3330 974 11 8 81 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 847 416 3 1 96 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 2183 670 13 4 84 0 0 23 4361568 95488 9 2264 0 76 964 95520 136 0 0 0 0 979 4065 607 12 6 82 0 0 23 4362384 96080 1 6 4 8 8 77376 0 0 0 0 0 975 465 457 2 1 97 0 0 23 4361944 95712 4 730 92 48 532 95520 64 0 0 0 0 1040 1859 734 8 3 89 0 0 23 4360424 95480 4 41 36 40 100 77376 7 0 0 0 0 986 1250 542 6 0 94 0 0 23 4361304 96096 3 264 76 36 88 88496 7 0 0 0 0 1037 942 665 5 3 92 0 0 23 4359680 95784 2 449 4 28 84 95520 8 0 0 0 0 922 1047 374 4 1 95 0 0 23 4359936 95464 2 544 4 20 332 95520 44 0 0 0 0 931 1095 384 2 2 96 /s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 0.00.00.0 0.0 0.00.00.0 0 0 c2t6d0 0.0 34.50.0 270.0 0.2 13.86.7 399.5 6 44 c5t12d0 -- swap disk 0.0 34.50.0 270.0 0.5 10.7 15.5 309.4 18 39 c5t13d0 -- swap disk This shows that the system is not effectively using
Re: question: about table(s) ?
I would not advise anybody to create objects using a graphical tools, rely on scripts instead. You can separate tables in a physical way and in a logical way. Physically you can use schemas and tablespaces to separate tables. Logically , use a good naming convention and then organise your stuff. --- Yechiel Adar [EMAIL PROTECTED] a écrit : On windows you can use the DBA console/OEM console (on 9i): select the database then go to space-tablespaces. Right click on tablespaces you get: Create. Or - read about the create tablespace command in the manual. (which you need to do anyway to understand what the GUI is doing). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 29, 2002 3:43 AM can we arrange tables in a heirarchy form, just like we have folders and under folders we have files. so this way we sort of divide workspace. say for company_A i create folder A and in it we can place files for that company. and similarly we can create a folder for company_B. Hence we can separate workspaces for better organization and management etc. so how can we accomplish as above, when we work with database ? is there a way we can arrange tables (of the database) in a heirarchy similar to folders and files ? say i have one installation of oracle on a particular computer. so how does one create separate table spaces, say for two different company or projects ? (say company_A and company_B are unrelated to each other) __ 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: john 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: Yechiel Adar 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: Oracle on windows and shadow thread file access
Thanks Jeff. The more I thought of it, the more I thought this had to be the case (e.g. only SMON, PMON, ARCH, etc. actually handled file access), but the topic raised just enough curiosity in my mind to seek another opinion. Ciao Fuzzy :-) None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Cheers Jeff Herrick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grant Allen 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
I am that specific customer for whom that bug was opened. If you need more information on this then let me know. This issue is still being worked on by the group which wrote 9idataguard and the problem is not diagnosed yet. What we noticed was that some archiver-rfs transfer processes become extremely slow in sending data while others were ok. So I implemented a job which runs every 10 minutes, looks for archvielog transfers which have taken more than 25 minutes. Kills those processes and then rfs spawns new processes which work just fine. We are using 3 log archive dests 1) local 2) local standby 3) DR standby 2000 miles away. We generate more than 300GB archive logs/day I do not receive messages that I send to the list, can someone help me out with this problem. Thanks Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 3:44 PM 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
Re: Oracle on windows and shadow thread file access
On Fri, 29 Nov 2002, Jeff Herrick wrote: None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Uh, I'm probably not going to be the only one to point out this isn't true. I don't know about Win32 thread architecture, but in Unix and unix-like operating systems, the shadow (server) processes each open whatever files they need for write. It is true that they also open the shared memory segments in order to write and read from the SGA, but they do the reading from disk. Otherwise, which process do you think is reading from the datafiles? A sample lsof of a typical server process: unixhost# lsof -p 29290 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME oracleorc 29290 oracle cwd VDIR 64,0x10002 22528 10090 /oracle/product/8.1.7/dbs oracleorc 29290 oracle mem VREG 64,0x7532 20465 /var/spool/pwgr/status oracleorc 29290 oracle txt VREG 64,0x10002 3855 22842 /oracle/product/8.1.7/bin/oracle oracleorc 29290 oracle mem VREG 64,0x6 13215 3024 /usr/lib/tztab oracleorc 29290 oracle mem VREG 64,0x61572640 6873 /usr/lib/pa20_64/libc.2 oracleorc 29290 oracle mem VREG 64,0x6 274664 8414 /usr/lib/pa20_64/libm.2 oracleorc 29290 oracle mem VREG 64,0x6 24032 8484 /usr/lib/pa20_64/libdl.1 oracleorc 29290 oracle mem VREG 64,0x6 23336 2688 /usr/lib/pa20_64/libnss_dns.1 oracleorc 29290 oracle mem VREG 64,0x6 131264 19010 /usr/lib/pa20_64/libpthread.1 oracleorc 29290 oracle mem VREG 64,0x6 24896 2671 /usr/lib/pa20_64/librt.2 oracleorc 29290 oracle mem VREG 64,0x10002 40600 3388 /oracle/product/8.1.7/lib64/libdsbtsh8.sl oracleorc 29290 oracle mem VREG 64,0x100027101192 3390 /oracle/product/8.1.7/lib64/libjox8.sl oracleorc 29290 oracle mem VREG 64,0x6 289000 8482 /usr/lib/pa20_64/dld.sl oracleorc 29290 oracle0r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle1w VREG 64,0x5 1177 6173 /tmp/listener_L_ORCL_start.out oracleorc 29290 oracle2w VREG 64,0x5 1177 6173 /tmp/listener_L_ORCL_start.out oracleorc 29290 oracle3r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle4r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle5r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle6u inet 0x4ecd06680t0 TCP *:* (IDLE) oracleorc 29290 oracle7r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle8u unix 0x4a1c8e000t0 /var/spool/sockets/pwgr/client29290 oracleorc 29290 oracle9r VREG 64,0x10002 360448 2274 /oracle/product/8.1.7/rdbms/mesg/oraus.msb oracleorc 29290 oracle 10u VCHR 64,0x3000e 0x512bc000 2233 /dev/data3/rorclsession_item-01 oracleorc 29290 oracle 11u inet 0x515d3a68 0t1684264 TCP unixhost.corporation.com:1541-clienthost.corporation.com:1577 (ESTABLISH ED) oracleorc 29290 oracle 12u VCHR 64,0x3000f 0x842c000 2237 /dev/data3/rorclts1_idx-02 oracleorc 29290 oracle 13u VCHR 64,0x10078 0xaacc000 2197 /dev/data1/rorclts1-02 oracleorc 29290 oracle 14u VCHR 64,0x2006a 0t59826176 2203 /dev/data2/rorclts1_idx-01 oracleorc 29290 oracle 15u VCHR 64,0x1006d 0xad0a000 2050 /dev/data1/rorclts1-01 oracleorc 29290 oracle 16u VCHR 64,0x20078 0t89505792 2231 /dev/data2/rorclts2-01 oracleorc 29290 oracle 17u VCHR 64,0x30015 0x16aa2000 2248 /dev/data3/rorclts3_idx-02 oracleorc 29290 oracle 18u VCHR 64,0x20073 0x6a144000 2221 /dev/data2/rorclts3_idx-01 oracleorc 29290 oracle 19u VCHR 64,0x30010 0x3819c000 2239 /dev/data3/rorclts4_idx-02 oracleorc 29290 oracle 20u VCHR 64,0x20072 0x375a8000 2219 /dev/data2/rorclts4_idx-01 oracleorc 29290 oracle 21u VCHR 64,0x1006f 0x77b6a000 2179 /dev/data1/rorclts3-01 oracleorc 29290 oracle 22u VCHR 64,0x10079 0x75c94000 2199 /dev/data1/rorclts3-02 -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of
RE: Recipe for application design to run on RAC
No one here [including me !] knows Oracle Names or OID. There's a profusion of TNSNAMES.ORA files for various databases and applications but not Oracle Names. I've been thinking and thinking of Oracle Names for a year and haven't got around to it . [I guess you'll think twice before hiring me as a DBA : ] Hemant At 09:14 AM 27-11-02 -0800, you wrote: The first thing to do is quit using tnsnames.ora on the client PC's. Use Oracle names or Oracle Internet Directory. Jared Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/27/2002 07:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Recipe for application design to run on RAC Hmm. Oracle says that with the improved Cache Fusion in 9i, any current application can be taken as is and run on 9iRAC. But yes, you are right. It really depends on the speed at which the two instances can share the same block and this can never be the same as two sessions accessing the same block in one instance [one SGA]. We are currently running and 8.1.5 OPS [ouch !] environment and testing 9iR2 RAC. The 8.1.5 OPS runs such that the Application Servers [Pro*C servers which get transactions from remote devices through a message bus] all connect to one node and direct PCs using VB/MSQuery connect to the other. Time and again I've asked for the PCs also to connect to the same node but no ... the effort to update the TNSNAMES.ORA and ODBC setup on the PCs would be too much I am told. In 9iRAC we are testing both BASIC and PRECONNECT Failover for TAF and will most certainly be using both nodes of the cluster for transactions. Even the Application Servers will be connecting across both nodes. Cross-fingers, touch-wood and wish me luck ! Hemant At 03:59 PM 26-11-02 -0800, you wrote: 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
RE: question: about table(s) ?
Hi, I think you could use different schema's to distinguish between different companies. Jack -Original Message- Sent: vrijdag 29 november 2002 2:44 To: Multiple recipients of list ORACLE-L can we arrange tables in a heirarchy form, just like we have folders and under folders we have files. so this way we sort of divide workspace. say for company_A i create folder A and in it we can place files for that company. and similarly we can create a folder for company_B. Hence we can separate workspaces for better organization and management etc. so how can we accomplish as above, when we work with database ? is there a way we can arrange tables (of the database) in a heirarchy similar to folders and files ? say i have one installation of oracle on a particular computer. so how does one create separate table spaces, say for two different company or projects ? (say company_A and company_B are unrelated to each other) __ 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: john 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: Jack van Zanen 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
No. Multiple application servers are for redundancy and load balancing at the ApplicationServer level. Thus, all 4 application servers do the same job and transactions coming in to them are load balanced' by the application. However, all 4 come in to the one database. The idea is to use both nodes of the database server in the same manner as using all 4 application server nodes -- concurrently, instead of keeping one idle. Hemant At 07:59 AM 27-11-02 -0800, you wrote: Couldn't you partitioned your database to accomplish the same thing and thus still be application-independent? - costs $$ licensing but ... -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 27, 2002 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: Recipe for application design to run on RAC Hmm. Oracle says that with the improved Cache Fusion in 9i, any current application can be taken as is and run on 9iRAC. But yes, you are right. It really depends on the speed at which the two instances can share the same block and this can never be the same as two sessions accessing the same block in one instance [one SGA]. We are currently running and 8.1.5 OPS [ouch !] environment and testing 9iR2 RAC. The 8.1.5 OPS runs such that the Application Servers [Pro*C servers which get transactions from remote devices through a message bus] all connect to one node and direct PCs using VB/MSQuery connect to the other. Time and again I've asked for the PCs also to connect to the same node but no ... the effort to update the TNSNAMES.ORA and ODBC setup on the PCs would be too much I am told. In 9iRAC we are testing both BASIC and PRECONNECT Failover for TAF and will most certainly be using both nodes of the cluster for transactions. Even the Application Servers will be connecting across both nodes. Cross-fingers, touch-wood and wish me luck ! Hemant At 03:59 PM 26-11-02 -0800, you wrote: 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
Re: Oracle on windows and shadow thread file access
Yes, I meant files they need for read. No matter how many times you proofread before sending... A shadow server process would only write if it were using direct path insert /*+append*/ or sqlldr or sorting to TEMP. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, Jeremiah Wilton wrote: On Fri, 29 Nov 2002, Jeff Herrick wrote: None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Uh, I'm probably not going to be the only one to point out this isn't true. I don't know about Win32 thread architecture, but in Unix and unix-like operating systems, the shadow (server) processes each open whatever files they need for write. It is true that they also open the shared memory segments in order to write and read from the SGA, but they do the reading from disk. Otherwise, which process do you think is reading from the datafiles? A sample lsof of a typical server process: unixhost# lsof -p 29290 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME oracleorc 29290 oracle cwd VDIR 64,0x10002 22528 10090 /oracle/product/8.1.7/dbs oracleorc 29290 oracle mem VREG 64,0x7532 20465 /var/spool/pwgr/status oracleorc 29290 oracle txt VREG 64,0x10002 3855 22842 /oracle/product/8.1.7/bin/oracle oracleorc 29290 oracle mem VREG 64,0x6 13215 3024 /usr/lib/tztab oracleorc 29290 oracle mem VREG 64,0x61572640 6873 /usr/lib/pa20_64/libc.2 oracleorc 29290 oracle mem VREG 64,0x6 274664 8414 /usr/lib/pa20_64/libm.2 oracleorc 29290 oracle mem VREG 64,0x6 24032 8484 /usr/lib/pa20_64/libdl.1 oracleorc 29290 oracle mem VREG 64,0x6 23336 2688 /usr/lib/pa20_64/libnss_dns.1 oracleorc 29290 oracle mem VREG 64,0x6 131264 19010 /usr/lib/pa20_64/libpthread.1 oracleorc 29290 oracle mem VREG 64,0x6 24896 2671 /usr/lib/pa20_64/librt.2 oracleorc 29290 oracle mem VREG 64,0x10002 40600 3388 /oracle/product/8.1.7/lib64/libdsbtsh8.sl oracleorc 29290 oracle mem VREG 64,0x100027101192 3390 /oracle/product/8.1.7/lib64/libjox8.sl oracleorc 29290 oracle mem VREG 64,0x6 289000 8482 /usr/lib/pa20_64/dld.sl oracleorc 29290 oracle0r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle1w VREG 64,0x5 1177 6173 /tmp/listener_L_ORCL_start.out oracleorc 29290 oracle2w VREG 64,0x5 1177 6173 /tmp/listener_L_ORCL_start.out oracleorc 29290 oracle3r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle4r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle5r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle6u inet 0x4ecd06680t0 TCP *:* (IDLE) oracleorc 29290 oracle7r VCHR 3,0x20t066 /dev/null oracleorc 29290 oracle8u unix 0x4a1c8e000t0 /var/spool/sockets/pwgr/client29290 oracleorc 29290 oracle9r VREG 64,0x10002 360448 2274 /oracle/product/8.1.7/rdbms/mesg/oraus.msb oracleorc 29290 oracle 10u VCHR 64,0x3000e 0x512bc000 2233 /dev/data3/rorclsession_item-01 oracleorc 29290 oracle 11u inet 0x515d3a68 0t1684264 TCP unixhost.corporation.com:1541-clienthost.corporation.com:1577 (ESTABLISH ED) oracleorc 29290 oracle 12u VCHR 64,0x3000f 0x842c000 2237 /dev/data3/rorclts1_idx-02 oracleorc 29290 oracle 13u VCHR 64,0x10078 0xaacc000 2197 /dev/data1/rorclts1-02 oracleorc 29290 oracle 14u VCHR 64,0x2006a 0t59826176 2203 /dev/data2/rorclts1_idx-01 oracleorc 29290 oracle 15u VCHR 64,0x1006d 0xad0a000 2050 /dev/data1/rorclts1-01 oracleorc 29290 oracle 16u VCHR 64,0x20078 0t89505792 2231 /dev/data2/rorclts2-01 oracleorc 29290 oracle 17u VCHR 64,0x30015 0x16aa2000 2248 /dev/data3/rorclts3_idx-02 oracleorc 29290 oracle 18u VCHR 64,0x20073 0x6a144000 2221 /dev/data2/rorclts3_idx-01 oracleorc 29290 oracle 19u VCHR 64,0x30010 0x3819c000 2239 /dev/data3/rorclts4_idx-02 oracleorc 29290 oracle 20u VCHR 64,0x20072 0x375a8000 2219 /dev/data2/rorclts4_idx-01 oracleorc 29290 oracle 21u VCHR 64,0x1006f 0x77b6a000 2179 /dev/data1/rorclts3-01 oracleorc 29290 oracle 22u VCHR 64,0x10079 0x75c94000 2199 /dev/data1/rorclts3-02 -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them),
Re: Oracle on windows and shadow thread file access
You'd hit nfile limits on HPUX [or at least HPUX 10.xx] pretty quickly. Hemant At 06:43 AM 29-11-02 -0800, you wrote: None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Cheers Jeff Herrick On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of them, or none of them? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grant Allen 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: Jeff Herrick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Monitoring Tool Evaluation methodology
Hello Sean, Would you mind sending me your evaluation? I'm in the process of performing a tool evaluation also, and this material would be invaluable. Thanks, Todd O'Neill, Sean [EMAIL PROTECTED] on 11/29/2002 05:34:00 AM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi there, If you are interested I can send you a word document which I used to select and evaluate an Oracle monitoring tool solution which might be useful for adapting to your needs. We're an NT/W2K site. BTW, we choose Quest I/Watch as our solution. Did not come across eHurkha product during our evaluation. HTH, - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] From: VIVEK_SHARMA [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 28, 2002 5:44 PM To: Multiple recipients of list ORACLE-L Subject: Monitoring Tool Evaluation methodology We are evaluating a monitoring Tool (eGurkha) for Unix/NT/Oracle monitoring What features should be Looked into while Evaluating ? Are there any Best practices for doing this kind of Evaluation ? Any Comments on this tool in particular by any who might have used this tool ? etc.. This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle on windows and shadow thread file access
Jeremiah I _did_ say the background oracle 'processes' meaning lgwr,dbwr,ckpt threads on Win32 specifically. My understanding from the question was that he was wondering whether each user's process in a dedicated-server configuration opened all of the datafiles too but I might have mis-understood the question. On Fri, 29 Nov 2002, Jeremiah Wilton wrote: On Fri, 29 Nov 2002, Jeff Herrick wrote: None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Uh, I'm probably not going to be the only one to point out this isn't true. I don't know about Win32 thread architecture, but in Unix and unix-like operating systems, the shadow (server) processes each open whatever files they need for write. It is true that they also open the shared memory segments in order to write and read from the SGA, but they do the reading from disk. Otherwise, which process do you think is reading from the datafiles? [snip] On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of them, or none of them? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Herrick 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).
ORA-1653: unable to extend table - Why?
Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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
If you're thinking of going ONAMES, consider OID. There are downsides to each, however, that you'll need to consider. 1) There is no mechanism in ONAMES to modify an alias. As per Oracle Support, you'll need to drop and recreate the alias instead. (Or you can modify the repository directly, but that's not encouraged) 2) Replication on OID is one huge PAIN! Because of the way OID works, the two OID DBs are not fully replicated. Instead, it's an AR hack. As of 9.0.1 at least, replication was unstable enough for us to dump OID completely. That and the fact that Oracle Support was of very little help, the documentation is HORRIBLE, and apparently RH 7.1 Linux isn't the best platform for OID 9.0.1. Just some things to consider... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Friday, November 29, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Recipe for application design to run on RAC No one here [including me !] knows Oracle Names or OID. There's a profusion of TNSNAMES.ORA files for various databases and applications but not Oracle Names. I've been thinking and thinking of Oracle Names for a year and haven't got around to it . [I guess you'll think twice before hiring me as a DBA : ] Hemant -- 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: ORA-1653: unable to extend table - Why?
Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: ORA-1653: unable to extend table - Why?
did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: ORA-1653: unable to extend table - Why?
How are the inserts being done? Are you doing an insert with append hint? -Original Message- Sent: Friday, November 29, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: logical tuning
Madhu - Here is what I understand you to be asking: How do I understand how my 3rd-party application works with Oracle. Here would be my tips: 1. Search for any books besides the vendor documentation. 2. Read the vendor documentation VERY carefully. Often the vendor explains very clearly how they work with Oracle, but it may take a few reads to understand what they are saying. 3. Search V$SQL, V$SQL_TEXT. Pull out the SQL text and run explain plan on it. 4. Search the Internet for others like yourselves. Today you can often locate email lists like this one that relate to your product. Maybe you'll have to start one. Pooled knowledge is a powerful tool. This forum is also a good place to make contacts. Often vendor developers will participate in these groups, as long as you don't embarrass them by saying stuff like why do you work for such a stupid company?. 5. My best tip. Try to understand WHY the vendor designed their Oracle interface the way they did. Don't just assume they are a bunch of idiots because you are so smart you'd have designed it better. Often the vendor must work under severe limitations like porting to several databases, or can't rewrite their whole product overnight just to suit Oracle, or they assume that most of their sites won't have an Oracle DBA, or they don't want to make all their programmers PL/SQL experts, etc. Hope that helps. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 29, 2002 5:34 AM To: Multiple recipients of list ORACLE-L Dennis Ferenc, Your discussion is a good read ... You guys are able to understand how your applications are working WITH Oracle, like using RULE/COST optimizer , Table Scans and also how it is using the Oracle capabilities. I also wanted to know more about the application running on top of Oracle . Would you guys GUIDE me with some steps ( may be top 10 and how to do that ) , or you have any document which you have prepared in the past will be great help for guys like me who wanted to know more :))- This LIST is always been a great HELP for me... Happy Thanks giving to YOU ALL. Thanks Madhu -Original Message- To: Multiple recipients of list ORACLE-L Sent: 11/27/2002 4:28 PM Ferenc Thanks so much for providing an insight into what you do. Lawson uses Oracle in quite a simpler method. No joins, just individual table access. No table scans, each access is hinted to use a specific index. Crude but effective. The first issue is that it doesn't use all of Oracle's capabilities. The second issue is that it provides little opportunity for Oracle tuning experts such as yourself. But customers keep pressing for better use of Oracle, so there is hope yet. ;-) Based on what I've seen out of Lawson and wait statistics, I'm applying my efforts to reducing physical I/O. I just configured several tables for the KEEP and RECYCLE pools. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 27, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Dennis as you know, there is no 'follow these steps to get a better performing application' guide when it comes to tuning. An intimate knowledge of what the application does is a must. I sell myself (tried the street corners but was not getting much intrest) as a Siebel performance tuning specialist, so when customers say 'Oh, you are an Oracle DBA !', I respond with 'No, Oracle DBA is just one of the things I do in order to get my job done'. there are plenty of DBA's out there, (and DBB's too), but understnading how the application (in my case Siebel) works and what it is trying to accomplish from a functional perspective helps me to know immediately what is the framework of limitations I can work in. For instance, Siebel is written for RBO, so when someone comes spouting partitions and bitmap indexes, I buzz them out on try 1. now for Siebel specific EIM (Enterprise Integration Manager) type tuning , when I see that index range scans are killing me, I try to reduce the batch size first so that it will not have to go through as many records per value (think of a batch size of 20,000 records where it is doing a correlated subquery on just the batch_id). Now change this into 100 batches of 200 rows each, and immediately you have a huge saving in logical IO, since each time excpet the first iteration, the index blocks and table blocks should be found in DBBC (Also see Cary's paper on www.hotsos.com which goes into deeper details on the latches needed and the recursive calls for buffer hits.) Other things include looking at SQL where you can see it is using an index to look up a row in the table to get a single value (column). In this case, for a large load, it may be beneficial to recreate this same index with the column concatenated on the end, and avoid the table lookup altogether. Also knowing EXACTLY how RBO works (there are only about 20
RE: ORA-1653: unable to extend table - Why?
Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: question: about table(s) ?
John - How many tables are we talking about here? In Oracle, you normally use separate schemas (usernames) to separate unrelated objects. I was just discussing this issue with developers recently. In past projects, sometimes we've ended up with many tables, but no dividing principle. Other times, we've ended up with many schemas with just a few tables and spent a lot of time granting permissions and creating synonyms. I don't know that there is any magic answer. Sometimes you just have to make the best decisions you can based on the information everyone begins the projects with. If there is any chance of a future division, say that one application will be moved to a different server, then for heaven's sake, keep those in separate schemas. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 28, 2002 7:44 PM To: Multiple recipients of list ORACLE-L can we arrange tables in a heirarchy form, just like we have folders and under folders we have files. so this way we sort of divide workspace. say for company_A i create folder A and in it we can place files for that company. and similarly we can create a folder for company_B. Hence we can separate workspaces for better organization and management etc. so how can we accomplish as above, when we work with database ? is there a way we can arrange tables (of the database) in a heirarchy similar to folders and files ? say i have one installation of oracle on a particular computer. so how does one create separate table spaces, say for two different company or projects ? (say company_A and company_B are unrelated to each other) __ 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: john 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).
Was: Recipe for application design to run on RAC, Now: Oracle nam
Hemant, After having rolled out ONAMES twice in two large organizationations, it was apparent that the technicalities of ONAMES in itself is simple. It is in the compilation and reconciliation of the various entries, their variations and having to deal with the different default_domain/zone names as well as rolling out of the updated SQLNET.ORA to the desktop lies the challenge. You will _have_ to get the developers and other parties to co-operate and agree on both implementing and going forward with ONAMES. I would suggest a pilot first with a few developers and then a larger rollout, so the developers can see the benefits. I used a Server partitioning project (in 10.7 Apps) to push this through. I was fortunate that we had centralized the TNS files on NT fileservers in the previous organization and had the luxury of using SMS in the latter. The key, as I said before, is getting all the entries in and getting everyone to co-operate in using the new service (and not deviating by creating local TNS entries). I do agree with Rich about the alias/entry modification - you need to capture the _exact_ entry for deregistration/registration. I did not go OID because of instability and the need to cater to older 7.3 databases/homes. Although Oracle has been threatening to drop ONAMES, the story is similar to the RBO - it lives on, even in 10i, although in an unsupported mode. And the current ONS has an option of export to OID (not sure about import?). There are not many knowledgable people within Oracle support who can help on ONAMES btw - you are pretty much on your own in many ways. I am still amazed by the large number of even large organizations that do not use ONAMES, let alone OID, so don't feel bad. And learn Perl - I forced myself to learn and use Perl when I was handling and merging all the different TNSNAMES.ORA files. Let me know offline if you need specifics. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Friday, November 29, 2002 9:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Recipe for application design to run on RAC If you're thinking of going ONAMES, consider OID. There are downsides to each, however, that you'll need to consider. 1) There is no mechanism in ONAMES to modify an alias. As per Oracle Support, you'll need to drop and recreate the alias instead. (Or you can modify the repository directly, but that's not encouraged) 2) Replication on OID is one huge PAIN! Because of the way OID works, the two OID DBs are not fully replicated. Instead, it's an AR hack. As of 9.0.1 at least, replication was unstable enough for us to dump OID completely. That and the fact that Oracle Support was of very little help, the documentation is HORRIBLE, and apparently RH 7.1 Linux isn't the best platform for OID 9.0.1. Just some things to consider... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Friday, November 29, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Recipe for application design to run on RAC No one here [including me !] knows Oracle Names or OID. There's a profusion of TNSNAMES.ORA files for various databases and applications but not Oracle Names. I've been thinking and thinking of Oracle Names for a year and haven't got around to it . [I guess you'll think twice before hiring me as a DBA : ] Hemant -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: Effect of Upgrading O/S to the 817 database !!!
Jackson - It is a holiday for most of the U.S. folks, but hopefully someone will get back to you. I am on Unix myself, so of no help to you. My laptop was recently upgraded from NT to 2000 Professional, and the PC administrators said that their experience was to not upgrade the system, but to reformat the hard drive, fresh install Windows 2000 and then reinstall everything. I think your existing Oracle software should be good to reinstall, but you should check the compatibility on http://metalink.oracle.com. And of course you will be doing this on your test machine before you jeopardize your production system. You don't say which 8.1.7 version you are using - 8.1.7.4? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 29, 2002 7:04 AM To: Multiple recipients of list ORACLE-L Hi We are currently running Oracle 817 database on a Windows NT, version 5, service pack 6. We need to upgrade O/S to Windows 2000. What should we do on the database side, do we need to do a new Oracle 817 software installation after upgrading O/S and try to startup the database or do we need to do everything from scratch, i.e. install software, create database and import ? I tought this should not have an effect on the database, if that the case, do we then need to just try to startup the database after O/S upgrade ? Please help ...your response will be highly appreciated. Desperado ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jackson Dumas 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: How to transfer data in different NLS_CHARACTER
Martin - Since I don't see any reply to your question (U.S. holiday), I would start with the Oracle National Language Support Guide. From past postings on this list, I believe that you can export the data from your American (8-bit characters) language database and import it into your Chinese (16-bit characters) language database. Here is a link that offers some information. http://www.desy.de/asg/oracle/impexp/impexp.html I also see notes that suggest you can do this with a SQL*Net link. I believe in Oracle9i, you will be encouraged to migrate to one of the Unicode character sets. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 27, 2002 9:24 PM To: Multiple recipients of list ORACLE-L Dear all , How to transfer data from 2 oracle server with different NLS_LANG ? NLS_LANG=Traditional Chinese_Taiwan.ZHT16BIG5 NLS_LANG=American_America.WE8ISO8859P1 Thanks in advance. Martin Chen -- 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: 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: ORA-1653: unable to extend table - Why?
how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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). __ 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: Oracle on windows and shadow thread file access
On Friday 29 November 2002 08:43, Jeff Herrick wrote: My understanding from the question was that he was wondering whether each user's process in a dedicated-server configuration opened all of the datafiles too Maybe not all of the data files, but the users dedicated server process will open datafiles as needed to read data into the block buffer. Now I don't know if I've helped any, or just added to the confusion. Jared but I might have mis-understood the question. On Fri, 29 Nov 2002, Jeremiah Wilton wrote: On Fri, 29 Nov 2002, Jeff Herrick wrote: None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Uh, I'm probably not going to be the only one to point out this isn't true. I don't know about Win32 thread architecture, but in Unix and unix-like operating systems, the shadow (server) processes each open whatever files they need for write. It is true that they also open the shared memory segments in order to write and read from the SGA, but they do the reading from disk. Otherwise, which process do you think is reading from the datafiles? [snip] On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of them, or none of them? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import Table from Full Backup
Hi Listers, Is it true that I can do an import of a table from a full backup that was done with RMAN? That was a comment I heard today and doesn't make sense to me, any comments? Saludos, Veronica Levin Enriquez Compañía Cervecera de Nicaragua -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Veronica Levin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Recipe for application design to run on RAC
To be more precise, the real problems in application-partitioning for OPS/RAC are UPDATE, SELECT ... FOR UPDATE, and DELETE statements due to their WHERE clauses... A SELECT statement does not force exclusive access to a database block and so does not directly cause contention for a block in OPS/RAC. An INSERT statement also does not cause contention on tables because the use of FREELIST GROUPS can keep blocks utilized by different instances separate from one another. If the INSERT is inserting a monotonically-ascending data value into an associated index, then contention on the highest-value leaf block can be reduced by using REVERSE indexes. If the INSERT is not inserting monotonically-ascending data values into any indexes, then contention during parallel cache management between indexes should be minimal. So SELECTs are inherently benign and INSERT operations can be controlled with mechanisms to prevent inter-instance contention for blocks. It is the UPDATE, SELECT ... FOR UPDATE, and DELETE statements which truly require consideration in making an OPS/RAC-based application scaleable. If these statements, which operate on database blocks according to their respective WHERE clauses generated by application logic, do not have some form of awareness of assignment of certain data values to specific database instances, then one can expect problems in scaling. Neither OPS nor RAC has any mechanism to minimize contention between instances for block buffers (as with INSERT statements), so it is up to the application itself (which controls the generation of the WHERE clause) to segregate the application somehow. Whether it is by major application module (i.e. sales and marketing versus order entry and inventory versus general ledger, etc) as Boris had illustrated, or by some other mechanism (i.e. all customers whose names start with A-M on one node, all whose names start with N-Z on the other node, etc), the application must be able to partition. --- In Oracle7 OPS and Oracle8 OPS and Oracle8i OPS, the mechanism (i.e. pinging performed through the I/O subsystem) was quite slow on most platforms, resulting in huge latencies. The major exception to this rule was DEC/Compaq/HP OpenVMS, where the performance of the pinging mechanism is so fast as to be quite unnoticeable. Not surprising if one considers the history of VMS and OpenVMS... Beginning with pieces of the cache-coherency mechanisms in Oracle8i OPS and fully implemented in Oracle9i RAC, the cache-fusion mechanism still performs the same locking and data-transfer of database block buffers between instances, only faster. How much faster is dependent on the OS and configuration. But the additional latency is still there. Obviously, if the inter-connect mechanism between nodes is not fast or misconfigured, then cache-fusion cannot be fast either... --- When Oracle states that applications can be migrated to RAC without modification, they are saying so in the faith that the reduced latencies in the cache-fusion mechanism and other improvements in the sharing/modification of global enqueues will result in almost-zero latency, or at least latency that is within the tolerance of the end-users. As the old saying goes, your mileage may vary or YMMV. As OpenVMS and its near zero-latency pinging mechanism shows, the choice and configuration of platform really matters also! --- In order to assess if an application is likely to scale effectively when migrating from non-RAC to RAC, I would pay close attention the nature, frequency, and volume of UPDATE, SELECT ... FOR UPDATE, and DELETE statements generated by the application. While still in its non-RAC implementation, I would recommend collecting and examining such SQL statements generated by application and understanding what program modules are generating each statement and why. I would then prioritize these statements by their volume and the business criticality of the generating program module. Last, according to this prioritization, I would examine how the WHERE clauses and the data values used in them can be controlled by application logic. For example, if a business-critical online form is generating lots of UPDATE and SELECT ... FOR UPDATE statements, is it possible to determine whether those statements are generated against rows previously INSERTed by the same session? Or, does that online form perform UPDATE and SELECT ... FOR UPDATE operations against any data in the database at all? Some applications are really quite partitionable under the covers, and only a small amount of such analysis can assure you that RAC is feasible. Other applications are so dreadfully complex that only by load-testing with real-world data values can the scalability be determined... Oracle has correctly identified all of the major bottlenecks in inter-instance contention and has improved each of these areas in RAC since OPS. The question is whether the improvements are sufficient for your
RE: Monitoring Tool Evaluation methodology
Hi Sean, Me too I'm interested into your evaluation methodology. Thanks, Kader --- [EMAIL PROTECTED] wrote: Hello Sean, Would you mind sending me your evaluation? I'm in the process of performing a tool evaluation also, and this material would be invaluable. Thanks, Todd O'Neill, Sean [EMAIL PROTECTED] on 11/29/2002 05:34:00 AM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi there, If you are interested I can send you a word document which I used to select and evaluate an Oracle monitoring tool solution which might be useful for adapting to your needs. We're an NT/W2K site. BTW, we choose Quest I/Watch as our solution. Did not come across eHurkha product during our evaluation. HTH, - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] From: VIVEK_SHARMA [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 28, 2002 5:44 PM To: Multiple recipients of list ORACLE-L Subject: Monitoring Tool Evaluation methodology We are evaluating a monitoring Tool (eGurkha) for Unix/NT/Oracle monitoring What features should be Looked into while Evaluating ? Are there any Best practices for doing this kind of Evaluation ? Any Comments on this tool in particular by any who might have used this tool ? etc.. This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Kader Ben 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: Import Table from Full Backup
Veronica - No RMAN uses its own format, so only RMAN can do anything with the files it creates. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 29, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Hi Listers, Is it true that I can do an import of a table from a full backup that was done with RMAN? That was a comment I heard today and doesn't make sense to me, any comments? Saludos, Veronica Levin Enriquez Compañía Cervecera de Nicaragua -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Veronica Levin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.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: Do user processes apply against shmmax limit?
Rich, The PGA is never contained within the SGA. In all architectures and on all platforms, it resides separate. The PGA holds process-specific data structures such as the sort area, the hash area, and some work areas used during bitmap-index operations. In shared-server (formerly multi-threaded server or MTS) architecture, the UGA (a.k.a. session/user global area, contains cursor-state and session-state info) can reside in the SGA, either in the Large Pool (if configured) or in the Shared Pool (by default). In dedicated-server architecture, the UGA is separate from the SGA. Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 29, 2002 6:48 AM Jay, My understanding is that the PGA is contained within the SGA, and that shmmax is the maximum size of a single shared memory segment. If you set shmmax to 256MB and configure 1GB SGA, you should see it allocate 4 shared memory segments for that purpose. Some Unix variants have limitations on the number of shared memory segments which can either be created (AIX) or simultaneously accessed (HP-UX). I haven't done much with Sun in the last few years so don't specifically know of the Solaris limitations, but I'm sure there is probably something there to consider. That's typically why you want to set shmmax as high as you realistically can -- to reduce the NUMBER of segments you need to allocate for shared memory. Your sysadmin also mentions turning on priority paging to give the user processes access to the memory before the file cache (aka buffer cache). Again I'm not sure about Solaris, but AIX and HP-UX both ship with their buffer cache set to something like 10% - 20% of total memory by default, which is a pretty good guess for a generic system when the vendor has no idea what you'll be using it for specifically... however, for large Oracle systems, I typically tune this back a bit, depending on the memory in the system. Normally something in the 2-8% or 3-10% range is sufficient. Remember, Oracle does all it's own buffering via DB_BLOCK_BUFFERS so doesn't really need to rely on the system buffer cache, even using filesystems (of course, raw devices completely bypass the system buffer cache). You might want to see what he's got the two parameters set to which control the size of the system buffer cache; sometimes reducing that will help quite a bit with paging/swapping. Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Miller, Jay Sent: Saturday, November 23, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Do user processes apply against shmmax limit? Hi everyone, I was always under the impression that the only concern with shmmax was that it be large enough for the SGA to fit into it. One of my System Administrators has just told me that the individual user processes (i.e., the PGA since we're not using multi-threaded server) get added to the SGA and if that SGA + user processes shmmax the system will start swapping. I haven't found anything to specifically address this issue on Metalink so I though I'd throw it open. We've started experiencing system slowdown and he says that increasing shmmax could resolve it. I'm skeptical (he also suggested increasing SGA to decrease swapping which I told him in no uncertain terms was nonsense). If anyone has a link to a note or white paper I'd appreciate that too. I've appended his email at the bottom. This slowdown seems to occur even when there's virtually on oracle activity so I'm suspecting some other cause. Thanks, Jay Miller nycsun1 and njsun7 has 6 GB of memory and only 2 GB of share memory. This morning nycsun1 was very slow and I noticed that there was lots of swaping. see vmstst and iostat below in red: procs memorypage disk faults cpu r b w swap free re mf pi po fr de sr s2 s4 s4 sd in sy cs us sy id 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 3330 974 11 8 81 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 847 416 3 1 96 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 2183 670 13 4 84 0 0 23 4361568 95488 9 2264 0 76 964 95520 136 0 0 0 0 979 4065 607 12 6 82 0 0 23 4362384 96080 1 6 4 8 8 77376 0 0 0 0 0 975 465 457 2 1 97 0 0 23 4361944 95712 4 730 92 48 532 95520 64 0 0 0 0 1040 1859 734 8 3 89 0 0 23 4360424 95480 4 41 36 40 100 77376 7 0 0 0 0 986 1250 542 6 0 94 0 0 23 4361304 96096 3 264 76 36 88 88496 7 0 0 0 0 1037 942 665 5 3 92 0 0 23 4359680 95784 2 449 4 28 84 95520 8 0 0 0 0 922 1047 374 4 1 95 0 0 23 4359936 95464 2 544 4 20 332 95520 44 0 0 0 0 931 1095 384 2 2 96 /s w/s kr/s kw/s wait actv wsvc_t asvc_t %w
Re: Oracle OS level security
Nothing can prevent an SA from wreaking havoc. The best we can do is narrow the number of people who can and DBAs can be removed from that group, if desired... - Original Message - From: Jared Still [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED] Sent: Thursday, November 28, 2002 5:45 PM Subject: Re: Oracle OS level security On Thursday 28 November 2002 12:03, Tim Gorman wrote: My $0.02... Oracle9i provides the AUDIT_SYS_OPERATIONS parameter, which will audit only to the OS audit trail. Thus, anything that SYSDBA does can be audited. The reason for the OS audit-trail only? Because SYSDBA can always erase a DB audit trail (even if the act of erasure is still audited). All SYSDBA however, can be prevented from reading or modifying the OS audit trail. This doesn't prevent a SA with DBA knowledge from wreaking havoc. I believe the only secure configuration for an Oracle database has the software owner (typically named oracle) and OS_SYSDBA and OS_SYSOPER groups under control of SysAdmins only. Those with SYSDBA do not need access to that OS account or those OS groups. SA's still a problem. The real problem is DBAs ourselves, who seem to treasure day-to-day usage of the Oracle software owner and membership of private accounts in the OS_SYSDBA and OS_SYSOPER groups... Personally, I log into the 'oracle' or 'root' account only as needed. Except on NT of course, where I need admin access to do my job properly. Maybe in a larger shop that wouldn't be necessary, but in a small shop it's very difficult to have an SA at your side when needed for admin level access. Jared - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 28, 2002 4:53 AM Jared, Very interested in the thread you hypothetical raised. I'm working in a pharamceutical site which is subject to FDA and other regualtions part of which is the whole buisness of audit trails. We has a Standard Operating Procedure which states that whilst DBA's have a access to data they will not change it. A recognition of the DBA's capabilties but stating on paper company trust they will behave themselves. On a more practical point with NT/W2K Oracle audit trail can be set to write audit trail records to the event logs. DBA's can be prevented from changing the event logs. So now it would take at least 2 people to instigate a fraud. Hey this might foster even better relations between DBA's and SA's ;) Just my 2 cent worth :) - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] From: [EMAIL PROTECTED] Date: Tue, 26 Nov 2002 14:40:24 -0800 Subject: Oracle OS level security Dear list, Let me toss a hypothetical situation at you. etc. etc. This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Tim Gorman 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
RE: ORA-1653: unable to extend table - Why?
Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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). __ 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
Re: Oracle on windows and shadow thread file access
Jared, You're right. There's a cool diagram in the Server Concepts manual. So back to the original issue, scalabilty could be affected in a dedicated server configuration depending on how many files needed to be opened. I guess the problem could be mitigated by fewer/larger datafiles and/or MTS Cheers On Fri, 29 Nov 2002, Jared Still wrote: On Friday 29 November 2002 08:43, Jeff Herrick wrote: My understanding from the question was that he was wondering whether each user's process in a dedicated-server configuration opened all of the datafiles too Maybe not all of the data files, but the users dedicated server process will open datafiles as needed to read data into the block buffer. Now I don't know if I've helped any, or just added to the confusion. Jared but I might have mis-understood the question. On Fri, 29 Nov 2002, Jeremiah Wilton wrote: On Fri, 29 Nov 2002, Jeff Herrick wrote: None...only the oracle background processes (threads in Winblows) access the datafiles/logfiles etc. All other communication is done through the SGA. On some Unix variants you _can_ reach a file_open max kernel parameter because each process (in a dedicated server scenario) opens it's own stdin/stdout/stderr. I guess the same could be true of processes running under windows too. So in the limit...you could hit a wall but only due to the per-process overhead. Uh, I'm probably not going to be the only one to point out this isn't true. I don't know about Win32 thread architecture, but in Unix and unix-like operating systems, the shadow (server) processes each open whatever files they need for write. It is true that they also open the shared memory segments in order to write and read from the SGA, but they do the reading from disk. Otherwise, which process do you think is reading from the datafiles? [snip] On Fri, 29 Nov 2002, Grant Allen wrote: Saw an interesting post in comp.databases.oracle.server postulating that if a shadow thread needed an open file handle on all files in a instance (or even some of them), the process handle limit in windows could constrain user scalability (e.g. too many users would result in ora-12500 unable to spawn errors and the like). (Let's ignore MTS/shared server mode for the moment) Sounded interesting, but I thought I'd ask if anyone knows whether a shadow thread (or process under unix) does open a handle on each file (control, data, redo), some of them, or none of them? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Herrick 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: Import Table from Full Backup
Veronica, Ah, rumors are a wonderful thing, especially if they turn out to be true in our favor. :-) Not unless it happens to be the only segment in a given tablespace. A tablespace is as granular as an RMAN restore/backup can be set. Even then, if you were to restore the tablespace to a previous point in time, the database would not be happy since it would be different than the control file scn. HTH, -Ron- -Original Message- Levin Sent: Friday, November 29, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Hi Listers, Is it true that I can do an import of a table from a full backup that was done with RMAN? That was a comment I heard today and doesn't make sense to me, any comments? Saludos, Veronica Levin Enriquez Compañía Cervecera de Nicaragua -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Veronica Levin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.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: logical tuning
Madhu To be perfectly honest, I had an unfair advantge as I worked in Siebel Expert Services for 2.5 years, flying all over the world, with a broom in one hand and a mop in the other, cleaning mess after mess at customer sites,where usually the integrator stuffed things up mainly due to ignorance on almost all fronts. I then spent a good portion of this year in Siebel Engineering where I was their lead performance engineer for the Siebel Analytics and Marketing products on Oracle.Then in August, I finally had enough and quit. I don't know that there is a top 10 list. But always the 3 golden rules for being a good DBA: 1. know your data. 2. know your data. 3. know your data. Everything is supplementary after that. Regards : Ferenc Mantfeld -Original Message- From: Reddy, Madhusudana [SMTP:[EMAIL PROTECTED]] Sent: Friday, November 29, 2002 10:34 PM To: Multiple recipients of list ORACLE-L Subject:FW: logical tuning Dennis Ferenc, Your discussion is a good read ... You guys are able to understand how your applications are working WITH Oracle, like using RULE/COST optimizer , Table Scans and also how it is using the Oracle capabilities. I also wanted to know more about the application running on top of Oracle . Would you guys GUIDE me with some steps ( may be top 10 and how to do that ) , or you have any document which you have prepared in the past will be great help for guys like me who wanted to know more :))- This LIST is always been a great HELP for me... Happy Thanks giving to YOU ALL. Thanks Madhu -Original Message- To: Multiple recipients of list ORACLE-L Sent: 11/27/2002 4:28 PM Ferenc Thanks so much for providing an insight into what you do. Lawson uses Oracle in quite a simpler method. No joins, just individual table access. No table scans, each access is hinted to use a specific index. Crude but effective. The first issue is that it doesn't use all of Oracle's capabilities. The second issue is that it provides little opportunity for Oracle tuning experts such as yourself. But customers keep pressing for better use of Oracle, so there is hope yet. ;-) Based on what I've seen out of Lawson and wait statistics, I'm applying my efforts to reducing physical I/O. I just configured several tables for the KEEP and RECYCLE pools. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 27, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Dennis as you know, there is no 'follow these steps to get a better performing application' guide when it comes to tuning. An intimate knowledge of what the application does is a must. I sell myself (tried the street corners but was not getting much intrest) as a Siebel performance tuning specialist, so when customers say 'Oh, you are an Oracle DBA !', I respond with 'No, Oracle DBA is just one of the things I do in order to get my job done'. there are plenty of DBA's out there, (and DBB's too), but understnading how the application (in my case Siebel) works and what it is trying to accomplish from a functional perspective helps me to know immediately what is the framework of limitations I can work in. For instance, Siebel is written for RBO, so when someone comes spouting partitions and bitmap indexes, I buzz them out on try 1. now for Siebel specific EIM (Enterprise Integration Manager) type tuning , when I see that index range scans are killing me, I try to reduce the batch size first so that it will not have to go through as many records per value (think of a batch size of 20,000 records where it is doing a correlated subquery on just the batch_id). Now change this into 100 batches of 200 rows each, and immediately you have a huge saving in logical IO, since each time excpet the first iteration, the index blocks and table blocks should be found in DBBC (Also see Cary's paper on www.hotsos.com which goes into deeper details on the latches needed and the recursive calls for buffer hits.) Other things include looking at SQL where you can see it is using an index to look up a row in the table to get a single value (column). In this case, for a large load, it may be beneficial to recreate this same index with the column concatenated on the end, and avoid the table lookup altogether. Also knowing EXACTLY how RBO works (there are only about 20 rules and in reality only 5 or 6 get used in an application), will help you to know when it may even be beneficial to DROP an index (gasp ! can he be serious ? Youbetcha ! ). anyway, that is it for today, class dismissed. Have a great day ! Ferenc Mantfeld -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 28, 2002 3:40 AM To: Multiple recipients of list ORACLE-L Subject:RE: Using RECYCLE pool? Thanks Denny, Connor, and Ferenc for your helpful suggestions. Ferenc - I particularly appreciated your insights. This is also a packaged app where I
RE: ORA-1653: unable to extend table - Why?
Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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 -
Table Locks
Hi, I would like to send an alert message to a client when a data row is locked for more than a certain period of time. For this can I write triggers on the system tables. If so on which table should I write a trigger to retrieve the table lock information. Are there any implications on writing triggers on the system tables. The alert message should be sent automatically in the sense, can I write an alert and signal it from a trigger written on some system table where the lock information is available? Any thoughts here... thanks
Re: Table Locks
Seems to me you should just have your program try to lock tables in exclusive mode. If it succeeds, then rollback. If it fails (timeout), it opens another session while the 'lock table' is waiting, and finds the blocker. Otherwise, if you are only interested in sessions that are actually blocking other sessions, just look in v$lock where block = 1. As interesting as it seems, I think you won't succeed in trying to put triggers on x$kgllk or anything like that. They're not real tables - just table-like accessors for memory structures in the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, [EMAIL PROTECTED] wrote: I would like to send an alert message to a client when a data row is locked for more than a certain period of time. For this can I write triggers on the system tables. If so on which table should I write a trigger to retrieve the table lock information. Are there any implications on writing triggers on the system tables. The alert message should be sent automatically in the sense, can I write an alert and signal it from a trigger written on some system table where the lock information is available? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Effect of Upgrading O/S to the 817 database !!!
If ur upgrading from nt 5 to 2000 then he first opt should work. Just install oracle on w2000 and start up the db coz the services are n nt. but if ur goin in for a fresh install of w2000, it is better to create a fresh db and import. Alternatively u can create a db with the same config of existig db and copy the old db folder with that of ur new one. hope this should work. wishes. Regards, Sathyanarayanan |+--- || Jackson | || Dumas | || tjaros@webma| || il.co.za| || | || 29/11/2002 | || 18:33| || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: Effect of Upgrading O/S to the 817| | database !!! | --| Hi We are currently running Oracle 817 database on a Windows NT, version 5, service pack 6. We need to upgrade O/S to Windows 2000. What should we do on the database side, do we need to do a new Oracle 817 software installation after upgrading O/S and try to startup the database or do we need to do everything from scratch, i.e. install software, create database and import ? I tought this should not have an effect on the database, if that the case, do we then need to just try to startup the database after O/S upgrade ? Please help ...your response will be highly appreciated. Desperado ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jackson Dumas 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).
