Re: RE: Running/Licensing Hyperthreaded Intel Pentium4 Xeon CPU's on
Orr, Steve, Sorry for my late feedback. I went back home and did not receive the mail. As i said, i have two node running 9i rac, with the same load from the middleware(load balancing is done with hardware alton and middle ware tuxedo). The following is one day's data after node rac1 is configured to use logical cpu and node rac2 is left with this feature off. [oracle@rac1 log]$ tail sar021123.log 22:20:00 all 11.43 0.00 0.19 88.38 22:30:00 all 10.43 0.00 0.17 89.41 22:40:00 all 10.81 0.00 0.43 88.76 22:50:00 all 10.45 0.00 0.17 89.38 23:00:00 all 10.55 0.00 0.17 89.28 23:10:00 all 10.65 0.00 0.38 88.96 23:20:00 all 11.58 0.00 0.18 88.24 23:30:01 all 10.90 0.00 0.14 88.96 23:40:00 all 8.48 0.00 0.30 91.21 Average: all 6.62 0.00 0.15 93.23 [oracle@rac1 log]$ rsh rac2 "tail log/sar021123.log" 22:20:00 all 23.88 0.00 0.38 75.74 22:30:00 all 19.91 0.00 0.33 79.76 22:40:00 all 19.93 0.00 0.74 79.33 22:50:01 all 19.68 0.00 0.30 80.02 23:00:00 all 19.27 0.00 0.31 80.42 23:10:00 all 21.58 0.00 0.75 77.67 23:20:00 all 19.88 0.00 0.30 79.81 23:30:00 all 20.12 0.00 0.27 79.61 23:40:00 all 17.26 0.00 0.61 82.14 Average: all 12.45 0.00 0.27 87.28 As we can see, the node rac1 has average 93.2 idle cpu in one days' time, and the second node has 97.28 average idle cpu. So, from my test result, hyperthread does give out better performance(at least more cpu power,can accept more concurrent user), and 6.8% average cpu usage vs 12.8 average cpu usage. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) === 2002-11-20 07:08:00 ,you wrote£º=== >I was off yesterday and came in this morning anticipating lots of feedback >on this but now I'm surprised by the lack of response from all you seasoned >gurus. :-) I guess this is a very new thing and you aren't aware of it yet. > > >Anyway, here's what I've garnered so far: > >There was a discussion on slashdot where one guy said Oracle was wanting to >charge licenses on the virtual CPU's instead of the actual number of >physical CPU's. Then someone else said he had checked with Oracle for a >definitive answer BEFORE implementing it because it was a make or break deal >for him. He said Oracle was only going to charge for actual physical CPU's >and not the virtual hyperthreaded CPU's so he went ahead with his >implementation and says he got a 30 performance boost. Not the same >performance boost as would be expected from adding real CPU's but >significant nonetheless. He said it was a 30 "free" boost because he didn't >have to buy anything; all he had to do was reconfigure Linux. In another >discussion on the SUSE/Oracle list it was stated that the performance boost >is about 20 of what you would expect from adding a real processor and that >Oracle once again said the license charge would be by virtual processor and >not the actual number of CPU's in the server. It was also stated that the >max number of processors for the standard edition is 4 as decreed by his >Oracle highness so with hyperthreading you could only have 2 real CPU's. >Finally, I only got 3 hits on Metalink where Oracle support drones said you >didn't have to do anything Oracle-wise to adjust for doubling the number of >O/S visible CPU's. They did not respond to init.ora tuning questions and >they refered us to our Oracle sales drones for licensing questions as they >were clueless in Seatle. > >As usual there is controversy regarding Oracle's license confusion. Is there >anything in the licensing legalese which specifically mentions licensing on >virtual or multithreaded CPU's? Regardless, seems to me we need to have a >Boston Tea Party type rebellion and refuse to pay for licenses on virtual >CPU's. > > >With righteous indignation and virtuous rebellion, >Steve Orr >Bozeman, Montana > > > >-Original Message- >Sent: Wednesday, November 20, 2002 2:23 AM >To: Multiple recipients of list ORACLE-L >on Linu > > >Orr, Steve, > I am using Xeon MP as well on my DELL 6650 , and i have two >nodes. I will patch my database next evening and later turn on the logical >cpu feature on one node and leave the other node as it is. > I will compare the cpu load/application responce time change and >give you the feedback. > Good luck > >Regards >zhu chao >Eachnet DBA >86-21-32174588-667 >[EMAIL PROTECTED] >www.happyit.net >www.cnoug.org(Chinese Oracle User Group) > >=== 2002-11-18 14:25:00 ,you wrote£º=== > >>OK, I've got this new Dell Linux server with 4 "Hyperthreaded" Pentium4 >Xeon >>CPU's. There are 4 physical CPU's but with hyperthreading the O/S sees 8 >>CPU's as is reported in top. I've installed Oracle on this machine and it >>seems to run as if there really were 8 CPU's. Could that be true? Is there >a >>performance gain for Oracle? --As if I really did have 8 CPU's? How does >>thi
RE: Do user processes apply against shmmax limit?
Good morning everyone, Thanks for confirming my belief. He was so definite I was starting to doubt myself (surely a Unix SA must know how the Unix parameters work right?). And the problem has been tracked down to a bad network switch (so I'm in the office again today to switch to our standby box while they work on it and then switch back when they're done). Jay Miller -Original Message- Sent: Saturday, November 23, 2002 9:44 PM To: Multiple recipients of list ORACLE-L Jay, I would suggest that your SA look at the 'w' column under procs. This shows that _since_ UNIX restart 23 jobs were continuously in the wait queue. Maybe something starts up on system reboot... >procs memorypagedisk > r b w swap free re mf pi po fr de sr s2 s4 s4 sd in > 0 0 23 4366736 97528 1 2186 16 12 12 95520 0 0 0 0 0 1104 > 0 0 23 4365992 96056 1 451 16 24 52 85968 3 0 0 0 0 935 > 0 0 23 4364712 95512 2 310 36 24 492 85968 68 0 0 0 0 1036 Also, could he show you 'sar -q' stats? This should show any swapping (as opposed to paging). 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: Miller, Jay [mailto:[EMAIL PROTECTED]] >Sent: Saturday, November 23, 2002 10:49 AM >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 memorypagedisk >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 >set semsys:seminfo_semmsl=1000 >set semsys:seminfo_semmnu=500 >set semsys:seminfo_semume=150 > > > >-- >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,
Re: instance shutdown problem ? (please help)
Ora-00205 means that oracle can not find one or more control files. Check your init.ora parameters. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Sunday, November 24, 2002 1:23 AM > ver 8i NT4.o > all services have been set to automatic and start fine > > however the NT Event Viwer and the oracle "alert.log" > show that the instance being shutdown just after the > box boots up. > > how do we rectify this problem ? > > below is from the alert.log > > ORA-205 signalled during: alter database mount exclusive.. > Shutting down instance (abort) > License high water mark = 5 > Instance terminated by USER, pid = 144 > --- > please note: the shutdwon was not initiated by any admin > or user but done by the system itself after the box boots > up. > > __ > 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).
A way to restore a DB
A way to restore a DB
A way to restore a DB
Dear gurus! We are evaluating a "strange" way to "recover" a production DB. This is a 3.7 TB database, still growing, with LOTS of data inserted every 5 minutes (and several partitions belonging to several tables get dropped each day - we keep historical data for 90 days back), which we used to backup with RMAN to HP Omniback controlled tapes. We had a failure, and it took almost 48 hours to restore/recover the DB (in addition to backed DB files, I had to restore thousands of archived logs from tapes and apply them to the DB). We can not afford to have the DB down for 2 days. Now, we decided to give up RMAN hot backup, actually to give up backup (in it's classic meaning) at all. I'll explain why: It's very important for us to get the DB up & running ASAP after a crash. We want the DB operating much more than we care about historical data that reside in our DB. Our goal is to enable the inserts (which run every 5 minutes, as I have stated) ASAP. So, we plan to do the following backup/recovery procedures: 1) We want to generate a script that will build the basics of the DB - create the DB and the instance, build tablespaces, users, grants, roles etc... 2) We want to create a daily export of small (but the most important) configuration tables, which can be imported very quickly after the crash and rebuild of DB. At this point the DB is operational. 3) We can now import the large data tables - partition by partition. And I don't care if THIS step will take 2 weeks. What do you say? Any reviews of my steps and the idea in general? And, do you have a script that can re-engineer a DB (as in my step 1)? Thanks a lot in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 way to restore a DB
Hello Andrey You can use the DataBee (www.databee.com) to generate scripts for the creation of the database. I think that you can create a small database, with the same sid and directory structure, on another machine and apply all the scripts there. Then you copy the files to a backup directory on the main server and let it wait. In case of need you just copy the files over the production database and restart the instance and you have a working database in a few seconds. Then you run the import and you are ready for production. You can then create the big tablespaces and import the historical data. Do not forget - the more time it take to import the historical data the less you have to do since some days already passed :-). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Sunday, November 24, 2002 7:23 PM > Dear gurus! > We are evaluating a "strange" way to "recover" a production DB. > This is a 3.7 TB database, still growing, with LOTS of data inserted every 5 > minutes (and several partitions belonging to several tables get dropped each > day - we keep historical data for 90 days back), which we used to backup > with RMAN to HP Omniback controlled tapes. > We had a failure, and it took almost 48 hours to restore/recover the DB (in > addition to backed DB files, I had to restore thousands of archived logs > from tapes and apply them to the DB). > We can not afford to have the DB down for 2 days. > Now, we decided to give up RMAN hot backup, actually to give up backup (in > it's classic meaning) at all. > I'll explain why: It's very important for us to get the DB up & running ASAP > after a crash. We want the DB operating much more than we care about > historical data that reside in our DB. Our goal is to enable the inserts > (which run every 5 minutes, as I have stated) ASAP. > So, we plan to do the following backup/recovery procedures: > 1) We want to generate a script that will build the basics of the DB - > create the DB and the instance, build tablespaces, users, grants, roles > etc... > 2) We want to create a daily export of small (but the most important) > configuration tables, which can be imported very quickly after the crash and > rebuild of DB. > At this point the DB is operational. > 3) We can now import the large data tables - partition by partition. > And I don't care if THIS step will take 2 weeks. > What do you say? > Any reviews of my steps and the idea in general? > And, do you have a script that can re-engineer a DB (as in my step 1)? > Thanks a lot in advance. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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).
please help with control file
please help me with this one the location mentioned in the init.ora for control files does not have any control file. since this is a new install, please let me know how to generate or rather recreate control file(s). [since there are no control file the system is unable to start] since instance is getting shutdown, i cannot enter sql as well. how do i create these control files ? __ 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).
Re: please help with control file
If you haven't created the database yet, look at the 'create database' command. The controlfiles get created when you create the database. If you already created a database, what happened to your controlfiles? Anyway, if you lost them, you can always use the 'create controlfile' command to get them back. Also, you might want to have a look at the Oracle Concepts Manual. It explains all about creating a database and controlfiles. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 24 Nov 2002, john wrote: > the location mentioned in the init.ora for control files does not > have any control file. > since this is a new install, please let me know how to generate or > rather recreate control file(s). > [since there are no control file the system is unable to start] > > since instance is getting shutdown, i cannot enter sql as well. how > do i create these control files ? -- 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: instance shutdown problem ? (please help)
Hmm, I think the error is right there in a snippet fo the alert log in the poster's original message. ORA-205 "Error in identifying controlfile." The database will not start because Oracle cannot find or access one or more of the controlfiles. Are all the postings today about controlfiles? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sat, 23 Nov 2002, Arup Nanda wrote: > Well, system does not do a shutdown abort. Oracle finds a problem and then > instance aborts. > > A few things you can do here. > > (1) See if a trace file is generated around that time in user_dump_dest or > background_dump_dest that might provide clues. If you see a core fiel in any > directory named core_* in the core_dump_dest, it has cored dumped. > > (2) Start the database from command line. From NT services panel, make the > services OracleServiceXXX manual, from automatic. Then reboot the box. Start > the service manually and then see what happens in the process. > > (3) Start the instance and service in a command prompt window using oradim > comamnd. I am not sure of the options; but the oradim command gives a help. > > At some point you would hit a problem that is decipherable. Please let us > know what came out of it. > > >From: john <[EMAIL PROTECTED]> > > > >ver 8i NT4.o > >all services have been set to automatic and start fine > > > >however the NT Event Viwer and the oracle "alert.log" > >show that the instance being shutdown just after the > >box boots up. > > > >how do we rectify this problem ? > > > >below is from the alert.log > > > >ORA-205 signalled during: alter database mount exclusive.. > >Shutting down instance (abort) > >License high water mark = 5 > >Instance terminated by USER, pid = 144 > >--- > >please note: the shutdwon was not initiated by any admin > >or user but done by the system itself after the box boots > >up. -- 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: A way to restore a DB
Looks like your database is playing two roles. OLTP for heavily insertion and keeping historical data as well for 90 days. I would recommend separate the two functionalities. A small OLTP database for insertion which only keeps a day or so worth of data. This way you can recover it quickly. A large database for historical data. You can transport data between the two using varies methods depends on how soon you want it to happen. -Original Message- Sent: Sunday, November 24, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Dear gurus! We are evaluating a "strange" way to "recover" a production DB. This is a 3.7 TB database, still growing, with LOTS of data inserted every 5 minutes (and several partitions belonging to several tables get dropped each day - we keep historical data for 90 days back), which we used to backup with RMAN to HP Omniback controlled tapes. We had a failure, and it took almost 48 hours to restore/recover the DB (in addition to backed DB files, I had to restore thousands of archived logs from tapes and apply them to the DB). We can not afford to have the DB down for 2 days. Now, we decided to give up RMAN hot backup, actually to give up backup (in it's classic meaning) at all. I'll explain why: It's very important for us to get the DB up & running ASAP after a crash. We want the DB operating much more than we care about historical data that reside in our DB. Our goal is to enable the inserts (which run every 5 minutes, as I have stated) ASAP. So, we plan to do the following backup/recovery procedures: 1) We want to generate a script that will build the basics of the DB - create the DB and the instance, build tablespaces, users, grants, roles etc... 2) We want to create a daily export of small (but the most important) configuration tables, which can be imported very quickly after the crash and rebuild of DB. At this point the DB is operational. 3) We can now import the large data tables - partition by partition. And I don't care if THIS step will take 2 weeks. What do you say? Any reviews of my steps and the idea in general? And, do you have a script that can re-engineer a DB (as in my step 1)? Thanks a lot in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Do user processes apply against shmmax limit?
>if that SGA + user processes > shmmax the system will start swapping. That's not true. If your SGA is bigger than shmmax, it just means the SGA will be fit into multiple shared memory segments. Doesn't necessary mean the system will start swapping. Is the scan rate going up? Richard -Original Message- Sent: Saturday, November 23, 2002 1:49 PM To: Multiple recipients of list ORACLE-L 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 memorypagedisk 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 set semsys:seminfo_semmsl=1000 set semsys:seminfo_semmnu=500 set semsys:seminfo_semume=150 -- 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: instance shutdown problem ? (please help)
Yes, the poster clarified that it was a missing controlfile. I have a reply that catered to the missing controlfile issue. From: Jeremiah Wilton <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: instance shutdown problem ? (please help) Date: Sun, 24 Nov 2002 15:23:35 -0800 MIME-Version: 1.0 Received: from newsfeed.cts.com ([209.68.248.164]) by mc6-f42.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Sun, 24 Nov 2002 16:13:22 -0800 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id QAA10715;Sun, 24 Nov 2002 16:06:45 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050A562; Sun, 24 Nov 2002 15:23:35 -0800 Message-ID: <[EMAIL PROTECTED]> X-Comment: Oracle RDBMS Community Forum X-Sender: Jeremiah Wilton <[EMAIL PROTECTED]> Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 25 Nov 2002 00:13:22.0422 (UTC) FILETIME=[772A6160:01C29417] Hmm, I think the error is right there in a snippet fo the alert log in the poster's original message. ORA-205 "Error in identifying controlfile." The database will not start because Oracle cannot find or access one or more of the controlfiles. Are all the postings today about controlfiles? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sat, 23 Nov 2002, Arup Nanda wrote: > Well, system does not do a shutdown abort. Oracle finds a problem and then > instance aborts. > > A few things you can do here. > > (1) See if a trace file is generated around that time in user_dump_dest or > background_dump_dest that might provide clues. If you see a core fiel in any > directory named core_* in the core_dump_dest, it has cored dumped. > > (2) Start the database from command line. From NT services panel, make the > services OracleServiceXXX manual, from automatic. Then reboot the box. Start > the service manually and then see what happens in the process. > > (3) Start the instance and service in a command prompt window using oradim > comamnd. I am not sure of the options; but the oradim command gives a help. > > At some point you would hit a problem that is decipherable. Please let us > know what came out of it. > > >From: john <[EMAIL PROTECTED]> > > > >ver 8i NT4.o > >all services have been set to automatic and start fine > > > >however the NT Event Viwer and the oracle "alert.log" > >show that the instance being shutdown just after the > >box boots up. > > > >how do we rectify this problem ? > > > >below is from the alert.log > > > >ORA-205 signalled during: alter database mount exclusive.. > >Shutting down instance (abort) > >License high water mark = 5 > >Instance terminated by USER, pid = 144 > >--- > >please note: the shutdwon was not initiated by any admin > >or user but done by the system itself after the box boots > >up. -- 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). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Problem doing RMAN backup of Clone?
Someone has just told me you can't do an RMAN backup of a clone because it has the same database id as the original. Is this true or not? If so, how to get around it? Thanks, Doug -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Doug C INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A way to restore a DB
Andrey, I am not sure I understand "We can now import the large data tables - partition by partition..And I don't care if THIS step will take 2 weeks.". Didn't you mention that you couldn't afford 48 hours of recovery? Import is extremely slow, and I would not recommend exp/imp for backup recovery. Or is it that some tables you could wait 2 weeks for recovery and some more urgent tables should be recoved ASAP? Also, you mentioned a lots of partions are dropped annd new ones are created every day. Are all the partitions of the table updated everyday? It seemsto me you are inserting into partition for today and dropping partition for sysdate-90 days. The other 89 partitions seem like they are intact. So why back them up every day? Back them up once and recover them only when time permits. Here is an example scenario for a table called STOCKS partioned along trading_day column. You have partitions named in format STOCKS_YYMMDD and each one is its own tablespace. At the beginning of 11/25/02, you would drop partition stocks_020827 and add stocks_021125. Assuming that all other partitons are not manipulated anyway, the hot backup will pick up changes to stocks_021125 only. Now you had a crash. You would restore ONLY the datafiles of the tablespace that contained the stocks_021125 partition and recover that. This should be fairly easy. Then you would restore and recover other partitions leisurely. You also mentioned you could build these tables from some external database. So you may decide to forego backups all together; but that's a business decision, not tehnical one. In one of the datawarehouses (actually a datamart) I manage, lack of tape drives prompted us to stop backups completely. If we have to recover from a disater, we have scripts that build the tablespaces, tables, populate control tables, etc and then refresh it from the regular sources. This works out to be longer but less expensive. HTH. Arup Nanda www.proligence.com - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, November 24, 2002 12:23 PM > Dear gurus! > We are evaluating a "strange" way to "recover" a production DB. > This is a 3.7 TB database, still growing, with LOTS of data inserted every 5 > minutes (and several partitions belonging to several tables get dropped each > day - we keep historical data for 90 days back), which we used to backup > with RMAN to HP Omniback controlled tapes. > We had a failure, and it took almost 48 hours to restore/recover the DB (in > addition to backed DB files, I had to restore thousands of archived logs > from tapes and apply them to the DB). > We can not afford to have the DB down for 2 days. > Now, we decided to give up RMAN hot backup, actually to give up backup (in > it's classic meaning) at all. > I'll explain why: It's very important for us to get the DB up & running ASAP > after a crash. We want the DB operating much more than we care about > historical data that reside in our DB. Our goal is to enable the inserts > (which run every 5 minutes, as I have stated) ASAP. > So, we plan to do the following backup/recovery procedures: > 1) We want to generate a script that will build the basics of the DB - > create the DB and the instance, build tablespaces, users, grants, roles > etc... > 2) We want to create a daily export of small (but the most important) > configuration tables, which can be imported very quickly after the crash and > rebuild of DB. > At this point the DB is operational. > 3) We can now import the large data tables - partition by partition. > And I don't care if THIS step will take 2 weeks. > What do you say? > Any reviews of my steps and the idea in general? > And, do you have a script that can re-engineer a DB (as in my step 1)? > Thanks a lot in advance. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line c
Re: partitioning questions
Actually, even without the date field, queries will still benefit from the partition-wise join on the charge_id column. You would see something like this (partition hash all) in the plan: SELECT STATEMENT CHOOSE (Cost=178026) PARTITION HASH ALL 1:4:1 HASH JOIN PARTITION RANGE ALL 1:13:3 TABLE ACCESS FULL TAB_5 * 1:52:3 PARTITION RANGE ALL 1:13:5 TABLE ACCESS FULL TAB_6 * 1:52:5 >>> [EMAIL PROTECTED] 11/23/02 03:19a.m. >>> > 3) If we range-partition by date, subpartition by hash (charge_id), >would queries that do not reference the date field, but do join >the tables by charge_id still benefit? No for the same reason as above. It would be also interesting to check whether you should rather have a LOCAL or GLOBAL index on charge_id in this case. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: help with connecting to sqlplus
Just check for the Oracle services in Control Panel-> Services. ur Instance,TNS should be started. if not start these services and try connecting. Regards, Sathyanarayanan |+---> || john | || | || | || 23/11/2002 | || 22:43| || Please | || respond to | || ORACLE-L | || | |+---> >--| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: help with connecting to sqlplus | >--| ver 8i on windows NT4 chose the Typical install in 8i during installation hence i guess a starter db is created by the installation via svrmgrl did startup trying to connect to sqlplus both gui as well as command line (on the same server box itself) tried with just sqlplus and it challenges for Enter user-name: i entered sys / as sysdba asks for password: i entered change_on_install error ORA-01033: Oracle initialization in progress. waited for 20 minutes, same error. did a shutdown and startup again, same error --- other clues/messages shutdown, shutdown normal show this Error ORA-01507: database not mounted connected to sqlplus with nolog and creating a sample table with CREATE TABLE or do a DESCRIBE TABLE for this shows "not connected" __ 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).