Re: Fav. Urban Legend...Mem vs Disk
That does add an interesting element to the version 9 max_aggregate_pga (or max_pga_aggregate or pga_max_aggregate .. permute as needed until the database restarts because I can't remember the order the words are in). If we set pga_aggregate_max to 500MB then Oracle tries to allocate all working sizes (hash, sort, bitmap create/merge) on demand so that the current sum doesn't exceed the aggregate max. Presumably, however, it does this by looking at the v$sesstat figures of current sessions, and tracking their current pga memory size, NOT their pga max memory size - but we all know that the O/S often still reports the process's memory as the pga max, long after Oracle has issued the free() call to release it. So how long will it be before we get - I've set the pga_agg_max to 500MB, but when I add up the memory from 'ps', or 'top' Oracle is obviously ignoring the limit. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 22 March 2002 07:58 |UNIX problem ? If memory is allocated from the HEAP it is never really returned |to the OS when we do a free. The free only happens when the process exits. So |the question is: Do we need the 200 MB or is it somekind of memory leak ? If we |need the 200 MB, remember that we are running on a demand paging systems these |days. So we only bring in memory what we need, you need to think about your |swap space though ... | |Anjo. | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
UNIX problem ? If memory is allocated from the HEAP it is never really returned to the OS when we do a free. The free only happens when the process exits. So the question is: Do we need the 200 MB or is it somekind of memory leak ? If we need the 200 MB, remember that we are running on a demand paging systems these days. So we only bring in memory what we need, you need to think about your swap space though ... Anjo. Jack C. Applewhite wrote: A most enjoyable book Venus on the Half Shell, written by Philip Jose Farmer under the pseudonym Kilgore Trout, who was a character in many novels - particularly Breakfast of Champions - by Kurt Vonnegut, who was a good friend of Mr. Farmer - also the author of the Riverworld trilogy (quadrilogy) and others. Oh, the connections! But the REAL purpose of this post (to keep it On Topic) is to report that my problem with excessive SNPx memory use turned out to be an artifact of upgrading from 8.1.6.0.0 - in which the interMedia Text indexing functions were handled by ExtProc - to 8.1.7.2.5 - in which the iM Text functions are incorporated into the RDBMS kernel. In 8.1.6 the 200MB used during index resyncs was released by ExtProc when the process finished. In 8.1.7 the SNP job runs the resync process and the 200MB used is not released. Periodically stopping and restarting the SNPx processes releases that memory and is an OK workaround. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Eskridge Sent: Wednesday, March 20, 2002 9:33 PM To: Multiple recipients of list ORACLE-L ... Hmmph. More kowtowing to Douglas Adam's cheap rip off on Kilgore Trout's epic, Venus on the Half Shell. Check the name of the FTL drive in the latter and compare it to The Question. Curious though, how the answer is just one more than the maximum ITL slots with 2k blocks... (he says in a desperate attempt to get back on topic) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am
Re: Fav. Urban Legend...Mem vs Disk
Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjørn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1:
Re: Fav. Urban Legend...Mem vs Disk
I always thought 42 was a good number. Perhaps I was wrong. Bjrn Engsig wrote: Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-)Rgds, Bjrn.On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz,512M RAM, typical single disk)a) visiting a single block via 4,000,000 logical IO'sgot me approx 35000 gets/secb) repeated full table scans similar system got meapprox 350 phys reads/secAfter this extensive, thorough and exhaustiveexercise, I can definitely say that memory accessversus disk access (as it pertains to Oracle) is 100times faster on this machine in single user modeI think we can generalise this to be the rule for allservers under all conditions :-)Connor --- "Freeman, Robert " [EMAIL PROTECTED]wrote: I've heard the disk vs. memory argumentsbefore, but never have seenquantifiable data either way... if anyone has any,I'd love to see it.RFRobert G. Freeman - Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database AdministrationThe Cigarette Smoking Man: Anyone who can appease aman's conscience cantake his freedom away from him.-Original Message-Sent: Monday, March 18, 2002 5:54 PMTo: Multiple recipients of list ORACLE-LRobert - So THAT is the title of your next book. I'mprimed to buy italready.I just recalled a legend, maybe. "Disk is 10,000times slower than memory,so memory access times are infinitesimal compared todisk access". CaryMillsap covers this in his Hotsos Clinic. He has runtests that prove "ain'tso". The point is that you can't just use ratios totune Oracle, but need tolook at wait times.Dennis WilliamsDBALifetouch, Inc.[EMAIL PROTECTED]-Original Message-Sent: Monday, March 18, 2002 1:04 PMTo: Multiple recipients of list ORACLE-LSo, does the CoO (Church of Oracle) have aninfallibility doctrine then???... From the Book of Oracle, chapter 5 ..and the DBA did look upon his database, and hesaw it was good.His tablespace datafiles being distributed titherand fro, spread amongsttheplatters of his disks. And he did complete thatwhich was calleddocumentation,and then he rested from his labors, and drankMountain Dew Code Red...:-)Robert G. Freeman - Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database AdministrationThe Cigarette Smoking Man: Anyone who can appease aman's conscience cantake his freedom away from him.-Original Message-Sent: Monday, March 18, 2002 12:25 PM< br>To: Multiple recipients of list ORACLE-LHey, you're an author!I expect perfection, grace and infallibility. ;)JaredOn Monday 18 March 2002 07:33, Freeman, Robertwrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-)))RFRobert G. Freeman - Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database AdministrationThe Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him.-Original Message-Sent: Sunday, March 17, 2002 6:43 PMTo: Multiple recipients of list ORACLE-LOn Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup(hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has beenissued with archived redo logs. I discussed it in my DBA World Tour backup andrecovery presentation. To do this, you MUST have the control file forthe database from BEFORE the resetlogs operation, and backup of the control
Re: Fav. Urban Legend...Mem vs Disk
My dear friend Mogens, I am so glad we share the same kind of bedtime reading in our own parts of the world. I totally agree with you, every cache hit ratio and performance metric within Oracle needs to be 42, for us to be in a sublime and happy state...;-). After all 42 does solve all of life's problems!! Cheers, Gaja --- Mogens Nørgaard [EMAIL PROTECTED] wrote: I always thought 42 was a good number. Perhaps I was wrong. Bjørn Engsig wrote: Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjørn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. stuff deleted = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
Depends on the question. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mogens Nørgaard [SMTP:[EMAIL PROTECTED]] I always thought 42 was a good number. Perhaps I was wrong. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
Connor - Cary Millsap presented the results of 10 trace files in a Hotsos seminar I attended. The ratio ranged from a high of 108.57 down to a low of 0.79. The point is that the ratio is nowhere near the oft-quoted 10,000. This means that logical I/Os are not insignificant. Even if physical I/O were eliminated (all blocks cached, 100% cache hit ratio), response time would not drop to zero. This is why the emphasis in tuning is shifting from simple ratios to examining wait times. If the most significant wait time is physical I/O, then changing that will improve overall performance. But if the most significant wait time lies in another area, then you may make significant improvements in physical I/O and still not improve overall performance. I certainly wouldn't claim to be an Oracle tuning expert, but I believe that the new ideas on tuning that are emerging provide a significant step forward in making Oracle tuning more of a logical process than a collection of rules of thumb. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 20, 2002 3:49 AM To: Multiple recipients of list ORACLE-L Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF
RE: Fav. Urban Legend...Mem vs Disk
Not quite !! 99.999% or 'the five nines' sounds much better ;-) With 64-bit computing we can address SGA sizes in the order of few TBs (if not PBs), why worry about disk I/Os except for two occasions ;-)) Then the 'five nines' can be 'nine nines'. Wow!! That sounds even better.. much much better ;-))) - Kirti -Original Message- Sent: Wednesday, March 20, 2002 5:48 AM To: Multiple recipients of list ORACLE-L Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjørn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Does this also mean that the preferred number of disks, tablespaces, and extents should also be 42? ; -Original Message- Krishna Vaidyanatha Sent: 20 March 2002 14:03 To: Multiple recipients of list ORACLE-L My dear friend Mogens, I am so glad we share the same kind of bedtime reading in our own parts of the world. I totally agree with you, every cache hit ratio and performance metric within Oracle needs to be 42, for us to be in a sublime and happy state...;-). After all 42 does solve all of life's problems!! Cheers, Gaja --- Mogens Nxrgaard [EMAIL PROTECTED] wrote: I always thought 42 was a good number. Perhaps I was wrong. Bjxrn Engsig wrote: Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjxrn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. stuff deleted = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: Fav. Urban Legend...Mem vs Disk
Yapp, feel the new tuning force ;-) DENNIS WILLIAMS wrote: Connor - Cary Millsap presented the results of 10 trace files in a Hotsos seminar I attended. The ratio ranged from a high of 108.57 down to a low of 0.79. The point is that the ratio is nowhere near the oft-quoted 10,000. This means that logical I/Os are not insignificant. Even if physical I/O were eliminated (all blocks cached, 100% cache hit ratio), response time would not drop to zero. This is why the emphasis in tuning is shifting from simple ratios to examining wait times. If the most significant wait time is physical I/O, then changing that will improve overall performance. But if the most significant wait time lies in another area, then you may make significant improvements in physical I/O and still not improve overall performance. I certainly wouldn't claim to be an Oracle tuning expert, but I believe that the new ideas on tuning that are emerging provide a significant step forward in making Oracle tuning more of a logical process than a collection of rules of thumb. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 20, 2002 3:49 AM To: Multiple recipients of list ORACLE-L Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation,
RE: Fav. Urban Legend...Mem vs Disk
And the preferred work week only be 42 hours!! -Original Message- Sent: Wednesday, March 20, 2002 8:58 AM To: Multiple recipients of list ORACLE-L Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Does this also mean that the preferred number of disks, tablespaces, and extents should also be 42? ; -Original Message- Krishna Vaidyanatha Sent: 20 March 2002 14:03 To: Multiple recipients of list ORACLE-L My dear friend Mogens, I am so glad we share the same kind of bedtime reading in our own parts of the world. I totally agree with you, every cache hit ratio and performance metric within Oracle needs to be 42, for us to be in a sublime and happy state...;-). After all 42 does solve all of life's problems!! Cheers, Gaja --- Mogens Nxrgaard [EMAIL PROTECTED] wrote: I always thought 42 was a good number. Perhaps I was wrong. Bjxrn Engsig wrote: Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjxrn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. stuff deleted = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
OT -- 42 [RE: Fav. Urban Legend...Mem vs Disk]
And 'preferred' rate : $42/hour? $42/minute? $42/42 seconds (That will solve some (42?) of my life's problems :) Gaja is finally back (after 42 weeks of disappearance;) Welcome Back :) I will now go back to packing my cube(in 42 boxes :) to move to a new small cube (42 SQ FT. And that's not a joke. New cubes are 7' X 6'). So Dgmt can import tigthly pack more rows err.. folks from another rental office and save money. New space will follow uniform sized cubes policy.. sort of what's described in the Paper #117. Smallest cubes for us, little bigger for Managers. Directors can have their own databases... err... rooms !! 42 of these OT mails, and Jared will boot us (may be 42 of us??) to the OT list ;) - Kirti -Original Message- Sent: Wednesday, March 20, 2002 9:38 AM To: Multiple recipients of list ORACLE-L And the preferred work week only be 42 hours!! -Original Message- Sent: Wednesday, March 20, 2002 8:58 AM To: Multiple recipients of list ORACLE-L Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Does this also mean that the preferred number of disks, tablespaces, and extents should also be 42? ; -Original Message- Krishna Vaidyanatha Sent: 20 March 2002 14:03 To: Multiple recipients of list ORACLE-L My dear friend Mogens, I am so glad we share the same kind of bedtime reading in our own parts of the world. I totally agree with you, every cache hit ratio and performance metric within Oracle needs to be 42, for us to be in a sublime and happy state...;-). After all 42 does solve all of life's problems!! Cheers, Gaja --- Mogens Nxrgaard [EMAIL PROTECTED] wrote: I always thought 42 was a good number. Perhaps I was wrong. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
On 3/20/02, Mark Leith [EMAIL PROTECTED] wrote: Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Because, as Homer Simpson said, Ahhh booze. The cause of and the solution to all of life's problems. ;^) -- Alan Davey [EMAIL PROTECTED] On 3/20/02, Mark Leith [EMAIL PROTECTED] wrote: Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Does this also mean that the preferred number of disks, tablespaces, and extents should also be 42? ; -Original Message- Krishna Vaidyanatha Sent: 20 March 2002 14:03 To: Multiple recipients of list ORACLE-L My dear friend Mogens, I am so glad we share the same kind of bedtime reading in our own parts of the world. I totally agree with you, every cache hit ratio and performance metric within Oracle needs to be 42, for us to be in a sublime and happy state...;-). After all 42 does solve all of life's problems!! Cheers, Gaja --- Mogens Nxrgaard [EMAIL PROTECTED] wrote: I always thought 42 was a good number. Perhaps I was wrong. Bjxrn Engsig wrote: Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjxrn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. stuff deleted = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Fav. Urban Legend...Mem vs Disk
I think you may have missed my sarcasm - I've been on the anti-cache hit ratio bandwagon for a long time... Cheers Connor --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Connor - Cary Millsap presented the results of 10 trace files in a Hotsos seminar I attended. The ratio ranged from a high of 108.57 down to a low of 0.79. The point is that the ratio is nowhere near the oft-quoted 10,000. This means that logical I/Os are not insignificant. Even if physical I/O were eliminated (all blocks cached, 100% cache hit ratio), response time would not drop to zero. This is why the emphasis in tuning is shifting from simple ratios to examining wait times. If the most significant wait time is physical I/O, then changing that will improve overall performance. But if the most significant wait time lies in another area, then you may make significant improvements in physical I/O and still not improve overall performance. I certainly wouldn't claim to be an Oracle tuning expert, but I believe that the new ideas on tuning that are emerging provide a significant step forward in making Oracle tuning more of a logical process than a collection of rules of thumb. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 20, 2002 3:49 AM To: Multiple recipients of list ORACLE-L Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it
RE: Fav. Urban Legend...Mem vs Disk
If anyone's after a nice high hit ratio, you can download the source for procedure choose_hit_ratio from my site...Some examples: SQL exec choose_a_hit_ratio(90); Current ratio is: 86.24731 Another 79053 consistent gets needed... Current ratio is: 90.5702 PL/SQL procedure successfully completed. SQL exec choose_a_hit_ratio(98,true); Current ratio is: 90.5709 Another 1141299 consistent gets needed... Magic for consultants like myself.. You want a hit ratio of 98%... No problem :-) --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Not quite !! 99.999% or 'the five nines' sounds much better ;-) With 64-bit computing we can address SGA sizes in the order of few TBs (if not PBs), why worry about disk I/Os except for two occasions ;-)) Then the 'five nines' can be 'nine nines'. Wow!! That sounds even better.. much much better ;-))) - Kirti -Original Message- Sent: Wednesday, March 20, 2002 5:48 AM To: Multiple recipients of list ORACLE-L Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjørn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
For those who aren't familiar with the book, the question of Life, The Universe, and Everything turned out to be: What is six times nine ? (And coincidentally, or so the author claimed, 6 x 9 = 42 if you are working in base 13). Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 March 2002 14:32 Depends on the question. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mogens Nørgaard [SMTP:[EMAIL PROTECTED]] I always thought 42 was a good number. Perhaps I was wrong. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Mem vs Disk
I can testify to that. Connor might not be 100% Danish, but he's sort of OK anyway. My personal story regarding the wait stuff (the Wait Interface as I usually call it) dates back to my days in Oracle many moons ago. On a big, internal list there, Kyle Hailey (who now works for Quest) talked about this new way of finding bottlenecks. Next, I stumbled upon Anjo's YAPP paper and that was it. I nearly fell off the ferry I was on while reading it. Since then, it has never ceased to surprise me that people are still reading and beleiving the books (like the ultimate books from the ultimate people, and all the others) where ratio after ratio after ratio is discussed. Where pure guesswork and magic are described as being scientific. Yuk. Cary Millsap, I think, first coined the phrase "Checklist tuning". That's exactly what most tuners, DBA's and other nice people are wasting their time doing, instead of following the simple rules of looking at where the time goes. Without knowing where the time goes, how can anyone conclude anything? But man, it gives us lots of work as long as people are reading those books. Perhaps the OakTable should write a book full of bad advise (why shouldn't we if anybody else can get away with it) and then lean back and wait for readers to call us :-))). Mogens Connor McDonald wrote: I think you may have missed my sarcasm - I've been onthe anti-cache hit ratio bandwagon for a long time...CheersConnor --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Connor - Cary Millsap presented the results of 10trace files in a Hotsosseminar I attended. The ratio ranged from a high of108.57 down to a low of0.79. The point is that the ratio is nowhere nearthe oft-quoted 10,000.This means that logical I/Os are not insignificant.Even if physical I/Owere eliminated (all blocks cached, 100% cache hitratio), response timewould not drop to zero. This is why the emphasis intuning is shifting fromsimple ratios to examining wait times. If the mostsignificant wait time isphysical I/O, then changing that will improveoverall performance. But ifthe most significant wait time lies in another area,then you may makesignificant improvements in physical I/O and stillnot improve overallperformance. I certainly wouldn't claim to be anOracle tuning expert, but Ibelieve that the new ideas on tuning that areemerging provide a significantstep forw ard in making Oracle tuning more of alogical process than acollection of rules of thumb.Dennis WilliamsDBALifetouch, Inc.[EMAIL PROTECTED]-Original Message-Sent: Wednesday, March 20, 2002 3:49 AMTo: Multiple recipients of list ORACLE-LSome rudimentary testing on a laptop here (500Mhz,512M RAM, typical single disk)a) visiting a single block via 4,000,000 logicalIO'sgot me approx 35000 gets/secb) repeated full table scans similar system got meapprox 350 phys reads/secAfter this extensive, thorough and exhaustiveexercise, I can definitely say that memory accessversus disk access (as it pertains to Oracle) is 100times faster on this machine in single user modeI think we can generalise this to be the rule forallservers under all conditions :-)Connor --- "Freeman, Robert " [EMAIL PROTECTED]wrote: I've heard the disk vs. memory argumentsbefore, but never have seenquantifiable data either way... if anyone has any,I'd love to see it.RFRobert G. Freeman - Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database AdministrationThe Cigarette Smoking Man: Anyone who can appease a man's conscience cantake his freedom away from him.-Original Message-Sent: Monday, March 18, 2002 5:54 PMTo: Multiple recipients of list ORACLE-LRobert - So THAT is the title of your next book. I'm primed to buy italready.I just recalled a legend, maybe. "Disk is 10,000times slower than memory,so memory access times are infinitesimal compared to disk access". CaryMillsap covers this in his Hotsos Clinic. He has run tests that prove "ain'tso". The point is that you can't just use ratios to tune Oracle, but need tolook at wait times.Dennis WilliamsDBALifetouch, Inc.[EMAIL PROTECTED]-Original Message-Sent: Monday, March 18, 2002 1:04 PMTo: Multiple recipients of list ORACLE-LSo, does the CoO (Church of Oracle) have aninfallibility doctrine then???... From the Book of Oracle, chapter 5 ..and the DBA did look upon his database, and hesaw it was good.His tablespace datafiles being distributed titherand fro, spread amongsttheplatters of his disks. And he did complete thatwhich was calleddocumentation,and then he rested from his labors, and drankMountain Dew Code Red...:-)Robert G. Freeman - Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database AdministrationThe Cigarette Smoking Man: Anyone who can appease a
RE: Fav. Urban Legend... NOBACKUP!!
The 9i doco contains a section called Recovering from a Backup Created Before a RESETLOGS in the User Managed Recovery guide. I haven't tried it :-) hth connor --- Jeremiah Wilton [EMAIL PROTECTED] wrote: Gopal, Would you mind posting the procedure for recovering past resetlogs? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 18 Mar 2002, K Gopalakrishnan wrote: I have once recovered a 8.0.5 database on Solaris using the backup (pre RESETLOGS) and the archive logs of pre and post resetlogs. THis recovery is based on SCN and Log Sequence has no role here. THis is two part recovery (I have given the outline in a private mail) and Oracle will not support this kind of recovery... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Yea, Right On a dba salary?? Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: bill thater [SMTP:[EMAIL PROTECTED]] Sent: Mon, March 18, 2002 9:33 PM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... nope he drank single malt scotch, neat.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. I used to have a life, then I got v32bis! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
9i Release 2 has a newer setting _MAKE_SQL_RUN_FASTER= 0 to 100 unlimited Ludicrous speed Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:38 PM To: Multiple recipients of list ORACLE-L init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database
RE: Fav. Urban Legend...
We were just reminiscing today about how PCs used to have a Turbo button. I recall the non-turbo was for 8008 compatibility for some early programs. One of the users suggested that we push the turbo button on the server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 19, 2002 10:18 AM To: Multiple recipients of list ORACLE-L 9i Release 2 has a newer setting _MAKE_SQL_RUN_FASTER= 0 to 100 unlimited Ludicrous speed Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:38 PM To: Multiple recipients of list ORACLE-L init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend... NOBACKUP!!
The procedure in the 9i docs is limited, and does not include recovery of a whole database, or any read/write datafiles up to and past resetlogs. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 19 Mar 2002, [iso-8859-1] Connor McDonald wrote: The 9i doco contains a section called Recovering from a Backup Created Before a RESETLOGS in the User Managed Recovery guide. I haven't tried it :-) hth connor --- Jeremiah Wilton [EMAIL PROTECTED] wrote: Gopal, Would you mind posting the procedure for recovering past resetlogs? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 18 Mar 2002, K Gopalakrishnan wrote: I have once recovered a 8.0.5 database on Solaris using the backup (pre RESETLOGS) and the archive logs of pre and post resetlogs. THis recovery is based on SCN and Log Sequence has no role here. THis is two part recovery (I have given the outline in a private mail) and Oracle will not support this kind of recovery... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
True story (I kid you not)... Guy at (previous site) work was running the worst SQL's in the world and then comes steaming in on regular occasions with There is a problem with the server, to which our obvious reply used to maybe its the 37 table join you've written... After this had no effect, I told him one day that I had added enhanced_performance = true in the database parameters and that this would take effect the following day... Later that afternoon, I saw him scanning through manuals looking to see what benefit this parameter would give him... --- Freeman, Robert [EMAIL PROTECTED] wrote: 9i Release 2 has a newer setting _MAKE_SQL_RUN_FASTER= 0 to 100 unlimited Ludicrous speed Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:38 PM To: Multiple recipients of list ORACLE-L init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
I can smell the rubber now. Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Tuesday, March 19, 2002 11:59 AM To: Multiple recipients of list ORACLE-L We were just reminiscing today about how PCs used to have a Turbo button. I recall the non-turbo was for 8008 compatibility for some early programs. One of the users suggested that we push the turbo button on the server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 19, 2002 10:18 AM To: Multiple recipients of list ORACLE-L 9i Release 2 has a newer setting _MAKE_SQL_RUN_FASTER= 0 to 100 unlimited Ludicrous speed Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:38 PM To: Multiple recipients of list ORACLE-L init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Now'a'days you have to go into the SETUP during boot and set 'RUN SYSTEM FASTER' in the CMOS. DENNIS WILLIAMS To: Multiple recipients of list ORACLE-L DWILLIAMS [EMAIL PROTECTED] @LIFETOUCH.COcc: M Subject: RE: Fav. Urban Legend... Sent by: root 03/19/2002 11:58 AM Please respond to ORACLE-L We were just reminiscing today about how PCs used to have a Turbo button. I recall the non-turbo was for 8008 compatibility for some early programs. One of the users suggested that we push the turbo button on the server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 19, 2002 10:18 AM To: Multiple recipients of list ORACLE-L 9i Release 2 has a newer setting _MAKE_SQL_RUN_FASTER= 0 to 100 unlimited Ludicrous speed Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:38 PM To: Multiple recipients of list ORACLE-L init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include
RE: Fav. Urban Legend... (Memory vs. Disk speeds)
The memory vs. disk speed argument most likely stems from hardware access times. Memory is rated in nanoseconds (10E-9), and disk access times are in milliseconds (10E-6). Hence, memory is rated at 1000 times faster than disk (if these ratings are accurate...). Alan Alan Aschenbrenner Oracle DBA IHS Group -Original Message- Sent: Tuesday, March 19, 2002 9:29 AM To: Multiple recipients of list ORACLE-L I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a
RE: Fav. Urban Legend...
A I only knew about it (and tried it) in 8i+... thanks for the info!! Rf Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 1:08 PM To: Multiple recipients of list ORACLE-L Robert: I was about to tell that. You hit the reply before me (that too on Sunday!!) BTW THis method is available from 7.x onwards. TO be precious from 7.3.3 Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Robert Sent: Sunday, March 17, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Given this situation (which I face from time to time), you have a couple of options. 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
RE: Fav. Urban Legend...
And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also
RE: Fav. Urban Legend...
Point taken... I assume you mean a cold backup in ARCHIVELOG mode... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: Fav. Urban Legend...
Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend... Hot vs Cold
Hi all, Notice the renamed heading - this thread has really taken off! Coming back to the issue: While I agree that you should not open the database until after you backup (hot or cold), I am still rooting for a hot backup. If you know the application well enough, you can perform 'selective hot backup' of a required set of tablespaces that you know will be changed, and continue hot backup of the others after the database is opened up. In a cold backup situation, the whole database is down for backup (including large TEMP tablespaces - 13Gb out of 130 Gb on one of my Production Apps databases!) for a longer duration than is necessary, which does not look too good on your availability reports. (And also remember to switch logfiles so that archivelogs are generated prior to release to users). Having said all that, a soft mirror that can be used exclusively for backup is invaluable. The whole database can be put in backup mode for a short time, the mirror 'broken' and the database backup ended. The mirror can then be used for backup to tape. In addition to this, you also have an online backup available (until the resilver starts) and you work off this disk backup for producting clones. Let me say that again - Invaluable! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California--
RE: Fav. Urban Legend... Hot vs Cold
John, Yes, and I could kick myself for not thinking of this, mirrors can be a most excellent alternative to backups both production and after a recovery. Of course, it can be an expensive alternative as it requires you to have 2x disk space... :-) But disk is cheap, right...? Or is that yet another Urban Legend??? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Hi all, Notice the renamed heading - this thread has really taken off! Coming back to the issue: While I agree that you should not open the database until after you backup (hot or cold), I am still rooting for a hot backup. If you know the application well enough, you can perform 'selective hot backup' of a required set of tablespaces that you know will be changed, and continue hot backup of the others after the database is opened up. In a cold backup situation, the whole database is down for backup (including large TEMP tablespaces - 13Gb out of 130 Gb on one of my Production Apps databases!) for a longer duration than is necessary, which does not look too good on your availability reports. (And also remember to switch logfiles so that archivelogs are generated prior to release to users). Having said all that, a soft mirror that can be used exclusively for backup is invaluable. The whole database can be put in backup mode for a short time, the mirror 'broken' and the database backup ended. The mirror can then be used for backup to tape. In addition to this, you also have an online backup available (until the resilver starts) and you work off this disk backup for producting clones. Let me say that again - Invaluable! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing
RE: Fav. Urban Legend... NOBACKUP!!
John !! Notice the change in subject again !! WARNING** ORACLE WILL NOT SUPPORT THIS KIND OF RECOVERY. * I don't agree with you about the MANDATORY backup. You can still recover the database COMPLETELY if you have all the archives from your old backup (before RESETLOGS) and the NEW ARCHIVES (after RESETLOGS) as long as if you have both (pre/post resetlogs) control files undamaged and your database version is 7.3.3 and above. I have once recovered a 8.0.5 database on Solaris using the backup (pre RESETLOGS) and the archive logs of pre and post resetlogs. THis recovery is based on SCN and Log Sequence has no role here. THis is two part recovery (I have given the outline in a private mail) and Oracle will not support this kind of recovery... For the arguement sake I can tell YOU DON:T NEED ANY BACKUP AFTER RESETLOGS. But since this is an unsupported I strongly suggest Jared' point.. A COMPLETE COLD BACKUP AFTER RESETLOGS. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Kanagaraj Sent: Monday, March 18, 2002 10:18 AM To: Multiple recipients of list ORACLE-L Hi all, Notice the renamed heading - this thread has really taken off! Coming back to the issue: While I agree that you should not open the database until after you backup (hot or cold), I am still rooting for a hot backup. If you know the application well enough, you can perform 'selective hot backup' of a required set of tablespaces that you know will be changed, and continue hot backup of the others after the database is opened up. In a cold backup situation, the whole database is down for backup (including large TEMP tablespaces - 13Gb out of 130 Gb on one of my Production Apps databases!) for a longer duration than is necessary, which does not look too good on your availability reports. (And also remember to switch logfiles so that archivelogs are generated prior to release to users). Having said all that, a soft mirror that can be used exclusively for backup is invaluable. The whole database can be put in backup mode for a short time, the mirror 'broken' and the database backup ended. The mirror can then be used for backup to tape. In addition to this, you also have an online backup available (until the resilver starts) and you work off this disk backup for producting clones. Let me say that again - Invaluable! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace -
Re: Fav. Urban Legend...
[EMAIL PROTECTED] wrote: platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... nope he drank single malt scotch, neat.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. I used to have a life, then I got v32bis! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend... Hot vs Cold
Well, if you ask many damagement types, they would say it's a myth. :) -Original Message- Sent: Monday, March 18, 2002 12:58 PM To: Multiple recipients of list ORACLE-L John, Yes, and I could kick myself for not thinking of this, mirrors can be a most excellent alternative to backups both production and after a recovery. Of course, it can be an expensive alternative as it requires you to have 2x disk space... :-) But disk is cheap, right...? Or is that yet another Urban Legend??? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Hi all, Notice the renamed heading - this thread has really taken off! Coming back to the issue: While I agree that you should not open the database until after you backup (hot or cold), I am still rooting for a hot backup. If you know the application well enough, you can perform 'selective hot backup' of a required set of tablespaces that you know will be changed, and continue hot backup of the others after the database is opened up. In a cold backup situation, the whole database is down for backup (including large TEMP tablespaces - 13Gb out of 130 Gb on one of my Production Apps databases!) for a longer duration than is necessary, which does not look too good on your availability reports. (And also remember to switch logfiles so that archivelogs are generated prior to release to users). Having said all that, a soft mirror that can be used exclusively for backup is invaluable. The whole database can be put in backup mode for a short time, the mirror 'broken' and the database backup ended. The mirror can then be used for backup to tape. In addition to this, you also have an online backup available (until the resilver starts) and you work off this disk backup for producting clones. Let me say that again - Invaluable! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
RE: Fav. Urban Legend... NOBACKUP!!
Gopal, Would you mind posting the procedure for recovering past resetlogs? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 18 Mar 2002, K Gopalakrishnan wrote: I have once recovered a 8.0.5 database on Solaris using the backup (pre RESETLOGS) and the archive logs of pre and post resetlogs. THis recovery is based on SCN and Log Sequence has no role here. THis is two part recovery (I have given the outline in a private mail) and Oracle will not support this kind of recovery... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend... NOBACKUP!!
As I said earlier, I agree 100% with KG. Recovery after RESETLOGS is possible I've done several of these types of recoveries in preping for some talks. KG is also right in that this is not supported by Oracle (but is taught in their internal backup and recovery classes!!) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 2:04 PM To: Multiple recipients of list ORACLE-L John !! Notice the change in subject again !! WARNING** ORACLE WILL NOT SUPPORT THIS KIND OF RECOVERY. * I don't agree with you about the MANDATORY backup. You can still recover the database COMPLETELY if you have all the archives from your old backup (before RESETLOGS) and the NEW ARCHIVES (after RESETLOGS) as long as if you have both (pre/post resetlogs) control files undamaged and your database version is 7.3.3 and above. I have once recovered a 8.0.5 database on Solaris using the backup (pre RESETLOGS) and the archive logs of pre and post resetlogs. THis recovery is based on SCN and Log Sequence has no role here. THis is two part recovery (I have given the outline in a private mail) and Oracle will not support this kind of recovery... For the arguement sake I can tell YOU DON:T NEED ANY BACKUP AFTER RESETLOGS. But since this is an unsupported I strongly suggest Jared' point.. A COMPLETE COLD BACKUP AFTER RESETLOGS. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Kanagaraj Sent: Monday, March 18, 2002 10:18 AM To: Multiple recipients of list ORACLE-L Hi all, Notice the renamed heading - this thread has really taken off! Coming back to the issue: While I agree that you should not open the database until after you backup (hot or cold), I am still rooting for a hot backup. If you know the application well enough, you can perform 'selective hot backup' of a required set of tablespaces that you know will be changed, and continue hot backup of the others after the database is opened up. In a cold backup situation, the whole database is down for backup (including large TEMP tablespaces - 13Gb out of 130 Gb on one of my Production Apps databases!) for a longer duration than is necessary, which does not look too good on your availability reports. (And also remember to switch logfiles so that archivelogs are generated prior to release to users). Having said all that, a soft mirror that can be used exclusively for backup is invaluable. The whole database can be put in backup mode for a short time, the mirror 'broken' and the database backup ended. The mirror can then be used for backup to tape. In addition to this, you also have an online backup available (until the resilver starts) and you work off this disk backup for producting clones. Let me say that again - Invaluable! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at
Re: Fav. Urban Legend...
init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend... Hot vs Cold
Bob, Yes... For Business Continuance (HP) or SRDF (EMC) kinda solutions it is becoming an urban Legend. I will give you an example... 1.2 TB usable disk spsce by HP's XP disk solution costs us almost 800 K USD , that too after all kinds of discounts. Like John said, considering it saves a whole lot of trouble and kinda fun to have around, it is invaluable...provided you can persuade damagement to part with that kinda money..;) Cheers, RS --- Freeman, Robert [EMAIL PROTECTED] wrote: John, Yes, and I could kick myself for not thinking of this, mirrors can be a most excellent alternative to backups both production and after a recovery. Of course, it can be an expensive alternative as it requires you to have 2x disk space... :-) But disk is cheap, right...? Or is that yet another Urban Legend??? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Hi all, Notice the renamed heading - this thread has really taken off! Coming back to the issue: While I agree that you should not open the database until after you backup (hot or cold), I am still rooting for a hot backup. If you know the application well enough, you can perform 'selective hot backup' of a required set of tablespaces that you know will be changed, and continue hot backup of the others after the database is opened up. In a cold backup situation, the whole database is down for backup (including large TEMP tablespaces - 13Gb out of 130 Gb on one of my Production Apps databases!) for a longer duration than is necessary, which does not look too good on your availability reports. (And also remember to switch logfiles so that archivelogs are generated prior to release to users). Having said all that, a soft mirror that can be used exclusively for backup is invaluable. The whole database can be put in backup mode for a short time, the mirror 'broken' and the database backup ended. The mirror can then be used for backup to tape. In addition to this, you also have an online backup available (until the resilver starts) and you work off this disk backup for producting clones. Let me say that again - Invaluable! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed
RE: Fav. Urban Legend...
Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W):
RE: Fav. Urban Legend...
And here I am trying to make things so complicated ;-) And for repeat billings, create an invisible DBMS_JOB (_invisible_jobs = TRUE) that calls a routine that resets the value to 1. Schedule to run 6 months once your contract ends. Sure to get a call. Larry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Joseph S Testa Sent: Monday, March 18, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... init.ora parm: _MAKE_SQL_RUN_FASTER= valid values: 8.0 = true/false 8.1 = x where x is a number between 1 and 100 9.0 = x or unlimited -- this gives you the ultimate speed in queries joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
We'll remember that Jared when your book comes out;-) -Original Message- Sent: Monday, March 18, 2002 9:25 AM To: Multiple recipients of list ORACLE-L Hey, you're an author! I expect perfection, grace and infallibility. ;) Jared On Monday 18 March 2002 07:33, Freeman, Robert wrote: And hey, it was Sunday morning at 0700 something... what do you expect from me anyways??? :-))) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 6:43 PM To: Multiple recipients of list ORACLE-L On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
Re: Fav. Urban Legend...
Layyr, I can't remember the details of what examples I have tried so far, but it's certainly been entertaining trying to map all the things that the optimizer will do. Like Stefane, I really try to avoid fixing local problems with init.ora parameters (especially hidden ones) because of global side-effects, and I also prefer to avoid hints simply because they might stop Oracle from finding an even better path in the next release. However, I do think that hints are a safe option - when used judiciously and with full knowledge of the data - because stored execution paths (outlines) depend on them so much. So, in case you haven't spotted them yet in 9i, I wonder if the rmain reason why the anti/semi join parameters have disappeared is because the following 6 hints are now published: hash_aj merge_aj nl_aj hash_sj merge_sj nl_sj Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 March 2002 18:55 |Things start to get *really* interesting with the way the CBO |can transform and choose access paths for NOT IN / NOT EXISTS and IN / |EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a HASH or |SEMI anti-join. Don't think that was possible in earlier versions (or at |least I couldn't make it happen) | |This also has a downside in a way. For example, in 8i with always_anti_join |set to hash, if I *know* a correlated nested loops anti-join approach is |preferred, I can code a correlated NOT EXISTS and rely upon a nested loops |anti-join. On the other hand, if I *know* the criteria and data is such that |a hash anti-join is preferable for that query, I would code the query using |a NOT IN, and assuming the condition for a hash anti join are met, I would |get the hash anti join. I can't depend on that in 9i unless I set the |_always_anti_join parameter. Hopefully the CBO will make the right choices |and I will not have to set it or worry about it. | |Larry G. Elkins | | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Larry Elkins | INET: [EMAIL PROTECTED] | |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(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: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Given this situation (which I face from time to time), you have a couple of options. 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
I have to say to all that this has been a really fun thread thanks to you all!! RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Sunday, March 17, 2002 5:13 AM To: Multiple recipients of list ORACLE-L Layyr, I can't remember the details of what examples I have tried so far, but it's certainly been entertaining trying to map all the things that the optimizer will do. Like Stefane, I really try to avoid fixing local problems with init.ora parameters (especially hidden ones) because of global side-effects, and I also prefer to avoid hints simply because they might stop Oracle from finding an even better path in the next release. However, I do think that hints are a safe option - when used judiciously and with full knowledge of the data - because stored execution paths (outlines) depend on them so much. So, in case you haven't spotted them yet in 9i, I wonder if the rmain reason why the anti/semi join parameters have disappeared is because the following 6 hints are now published: hash_aj merge_aj nl_aj hash_sj merge_sj nl_sj Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 March 2002 18:55 |Things start to get *really* interesting with the way the CBO |can transform and choose access paths for NOT IN / NOT EXISTS and IN / |EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a HASH or |SEMI anti-join. Don't think that was possible in earlier versions (or at |least I couldn't make it happen) | |This also has a downside in a way. For example, in 8i with always_anti_join |set to hash, if I *know* a correlated nested loops anti-join approach is |preferred, I can code a correlated NOT EXISTS and rely upon a nested loops |anti-join. On the other hand, if I *know* the criteria and data is such that |a hash anti-join is preferable for that query, I would code the query using |a NOT IN, and assuming the condition for a hash anti join are met, I would |get the hash anti join. I can't depend on that in 9i unless I set the |_always_anti_join parameter. Hopefully the CBO will make the right choices |and I will not have to set it or worry about it. | |Larry G. Elkins | | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Larry Elkins | INET: [EMAIL PROTECTED] | |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(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: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
(which didn't solve the problem), I would be semi-retired, and the 2 SQL statements that were causing all the problems would have been fixed. Everybody happy then ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan Lewis Sent: Sunday, March 17, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... Layyr, I can't remember the details of what examples I have tried so far, but it's certainly been entertaining trying to map all the things that the optimizer will do. Like Stefane, I really try to avoid fixing local problems with init.ora parameters (especially hidden ones) because of global side-effects, and I also prefer to avoid hints simply because they might stop Oracle from finding an even better path in the next release. However, I do think that hints are a safe option - when used judiciously and with full knowledge of the data - because stored execution paths (outlines) depend on them so much. So, in case you haven't spotted them yet in 9i, I wonder if the rmain reason why the anti/semi join parameters have disappeared is because the following 6 hints are now published: hash_aj merge_aj nl_aj hash_sj merge_sj nl_sj Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 March 2002 18:55 |Things start to get *really* interesting with the way the CBO |can transform and choose access paths for NOT IN / NOT EXISTS and IN / |EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a HASH or |SEMI anti-join. Don't think that was possible in earlier versions (or at |least I couldn't make it happen) | |This also has a downside in a way. For example, in 8i with always_anti_join |set to hash, if I *know* a correlated nested loops anti-join approach is |preferred, I can code a correlated NOT EXISTS and rely upon a nested loops |anti-join. On the other hand, if I *know* the criteria and data is such that |a hash anti-join is preferable for that query, I would code the query using |a NOT IN, and assuming the condition for a hash anti join are met, I would |get the hash anti join. I can't depend on that in 9i unless I set the |_always_anti_join parameter. Hopefully the CBO will make the right choices |and I will not have to set it or worry about it. | |Larry G. Elkins -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Yep Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Wednesday, March 13, 2002 5:05 PM To: Multiple recipients of list ORACLE-L sometimes you just have to shove it in their faces, don't you? --- Freeman, Robert [EMAIL PROTECTED] wrote: I actually had that one already... thats one of my favs... Also, there is that customer I had who was certain that they could take an export, recover it and then apply archived redo logs for point in time recovery. Their DBA and I had an hour long argument over this before I finally pulled out my laptop, and demonstrated the folly of his recovery scheme. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Tuesday, March 12, 2002 11:28 PM To: Multiple recipients of list ORACLE-L oh man, mine has to be what is probably Jeremiah's as well: the myth that Oracle doesn't write to the database files when you are in hot backup mode --- Freeman, Robert [EMAIL PROTECTED] wrote: I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 --
RE: Fav. Urban Legend...
Robert: I was about to tell that. You hit the reply before me (that too on Sunday!!) BTW THis method is available from 7.x onwards. TO be precious from 7.3.3 Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Robert Sent: Sunday, March 17, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Given this situation (which I face from time to time), you have a couple of options. 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K
Re: Fav. Urban Legend...
On Sunday 17 March 2002 07:53, Freeman, Robert wrote: 1. You do not open the database to users until AFTER you do a backup (hot or cold, dosen't mater) at point t2. Well, yeah, that was the point. It doesn't have to be a cold backup, but since you can't do any work, it may as well be a cold backup. Jared 2. There is a method of recovering a database (8i +) after RESETLOGS has been issued with archived redo logs. I discussed it in my DBA World Tour backup and recovery presentation. To do this, you MUST have the control file for the database from BEFORE the resetlogs operation, and backup of the control file from AFTER the same operation. I've done this about 3 times in testing and it works fine but it very very picky about the control file images. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Friday, March 15, 2002 7:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Just remembered this one: If you update a column which has a bitmap index, then the entire index is locked, which means the entire table is locked. And here's a prediction for Urban Legends 2003/4 Always, always, always use bind variables. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Jonathan Lewis wrote: Just remembered this one: If you update a column which has a bitmap index, then the entire index is locked, which means the entire table is locked. And here's a prediction for Urban Legends 2003/4 Always, always, always use bind variables. You're cheating. Most advice containing 'always', even only once, stands a big chance of becoming a legend very soon. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
On that line, I've just had a note from Gaja about my commentary on the line: Rewrite all correlated subqueries using in-line views. He was concerned that I may not think the advice valid. So let me say quite categorically that the comment was not a criticism of the technique. Converting a subquery into a join is a strategy which will very often result in significant performance benefits and Gaja is, I think, the first person I have seen make a very special mention of it in a public paper. (No doubt someone will correct me on that quite soon). My point was that although it is not the automatic, the only, or even necessarily the correct solution to the sight of a correlated subquery, Gaja's paper will, one day, be quoted as the definitive proof that you should ALWAYS do it. And such is the stuff of the urban legend. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 March 2002 09:27 | |You're cheating. Most advice containing 'always', even only once, stands |a big chance of becoming a legend very soon. |-- |Regards, | |Stephane Faroult |Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend... Data Warehouse first hand experience
Okay... mine...
Just because Oracle allows 1000 columns, you don't have to feel obliged to
make use of that feature for every table.
ALSO, While sometimes if you put every data elelment in a table that you are
likely to query together [(right up to that 1000 column mark) so you can
avoid those costly joins] it might speed up queries [especially if you
heavily index], it isn't necessarily a good thing.
Faster queries are good, but that isn't a prerequisite for a data warehouse.
If you can't load it they are going to fly anyway.
April Wells
Corporate Systems
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
%;G0N#0H
end
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: April Wells
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Fav. Urban Legend...
Another comment that Gaja made in his note was that he didn't like using underscore parameters such as _unnest_subquery = true. especially since you can't be sure of the impact of using a 'functionality' hint globally. And I totally agree - particularly in this case where we know that unnesting can make the performacne worse, and we can't know whether, in its version 8 form, the parameter forces unnesting unconditionally even when the optimizer would otherwise cost against it. However - the fact that the parameter is there reminded me that Oracle 9 has a hint UNNEST - so I thought I'd check if Oracle 8 has got it as well. It isn't in my 8.1.5 manuals, (anyone care to check the 8.1.7 for me) but it's there and it works. So - when you get to that tricky query which looks as if it could be unnested, but you can't quite figure out how, maybe all you need to do is turn: select from where . (select colx from where ... ) into select from where . (select /*+ unnest */ colx from where ... ) and if Oracle can unnest the query, Oracle will unnest the query; for example, in the case of the SQL Gaja's used in paper, the subquery SQL will produce an execution plan matching the join SQL, with a line VW_SQ_1 as one of the 'tables' in the hash join. (Actually Oracle 8.1.7 will do this for some subquery operations without the hint - but so far none of the ones I've seen it in are correlated subqueries) Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 March 2002 10:37 | | |On that line, I've just had a note from Gaja |about my commentary on the line: | |Rewrite all correlated subqueries using in-line views. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Jonathan Lewis wrote: So - when you get to that tricky query which looks as if it could be unnested, but you can't quite figure out how, maybe all you need to do is turn: select from where . (select colx from where ... ) into select from where . (select /*+ unnest */ colx from where ... ) and if Oracle can unnest the query, Oracle will unnest the query; Jonathan, beware, you are sowing urban legends of your own. I think that if you can't figure out how to unnest, you'd rather try harder or subcontract to somebody else. You know like me that most applications currently running started development with Oracle 7 or Oracle 6, but for those which were migrated from Oracle 5 or earlier. Which means that today's developments will probably last till Oracle 12 or beyond. I wouldn't bet on the future of hints, when you see how few (documented) init.ora parameters have survived since the early times. I appreciate the magical hint as much as anybody, especially in the hit-and-run kind of consulting assignment. But when you rely on hints or init.ora parameters for performance at some critical sections, you're taking a risk from a development (as opposed to crisis tuning) point of view. When the next Oracle release is applied, I am ready to bet that the folks who will try to solve the performance problem then (long after the departure of the initial developers), under stress and in a stormy atmosphere will spend much more time that those lazy programmers would have in the first place. Moreover, I am not sure that everybody makes a difference, even on this list, between 'I cannot figure out how to unnest, let's add a hint' and 'I cannot figure out how to remove duplicates, let's add a DISTINCT'. As far as urban legends are concerned, I don't think that _all_ deserve the scorn which has been heaped on them so far. There are some fallacies which are such only because they are taken as an absolute truth. Some may be true 80% of the time, which is not that bad, and most may be idiotic but innocuous (granted, that's when you come to problem solving that you are lost). Of course taking first degree approximations for the ultimate truth is wrong, but as long as you stay on the straight and narrow path ... Newtonian mechanics has it flaws, but in any case was enough for sending men to the Moon. My 0.02 euros. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan Lewis Sent: Saturday, March 16, 2002 8:58 AM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... and if Oracle can unnest the query, Oracle will unnest the query; for example, in the case of the SQL Gaja's used in paper, the subquery SQL will produce an execution plan matching the join SQL, with a line VW_SQ_1 as one of the 'tables' in the hash join. (Actually Oracle 8.1.7 will do this for some subquery operations without the hint - but so far none of the ones I've seen it in are correlated subqueries) You can see something similar in 8.1.7 with correlated EXISTS if always_semi_join = hash. 8.1.7 (and back through 7.3 I believe) can turn EXISTS correlated sub-queries away from an NL approach into a HASH SEMI JOIN (or merge semi join depending upon the parameter). The plan will not read quite the same as the UNNEST hint approach: SQL alter session set always_semi_join = hash; Session altered. SQL select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7089 Card=99900 Bytes=1498500) 10 HASH JOIN (SEMI) (Cost=7089 Card=99900 Bytes=1498500) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=10 Bytes=110) 31 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=214 Card=299600 Bytes=1198400) SQL alter session set always_semi_join = nested_loops; Session altered. With the UNNEST hint, I assume you were seeing something similar to the following: 1 select * 2 from code_master cm 3 where exists (select /*+ UNNEST */ null 4from code_detail cd 5* where cm.code = cd.code) SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4070 Card=99900 Bytes=2397600) 10 MERGE JOIN (SEMI) (Cost=4070 Card=99900 Bytes=2397600) 21 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=340 Card=10 Bytes=110) 32 INDEX (FULL SCAN) OF 'CODE_MASTER_PK' (UNIQUE) (Cost=188 Card=10) 41 SORT (UNIQUE) (Cost=3516 Card=299600 Bytes=3894800) 54 VIEW OF 'VW_SQ_1' (Cost=214 Card=299600 Bytes=3894800) 65 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=214 Card=299600 Bytes=1198400) And since you mentioned you hadn't seen the unnesting of correlated sub-queries in 8.1.7, I assume you *have* seen it in 9i where the always_semi_join and always_anti_join parameters became undocumented parameters? Things start to get *really* interesting with the way the CBO can transform and choose access paths for NOT IN / NOT EXISTS and IN / EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a HASH or SEMI anti-join. Don't think that was possible in earlier versions (or at least I couldn't make it happen) This also has a downside in a way. For example, in 8i with always_anti_join set to hash, if I *know* a correlated nested loops anti-join approach is preferred, I can code a correlated NOT EXISTS and rely upon a nested loops anti-join. On the other hand, if I *know* the criteria and data is such that a hash anti-join is preferable for that query, I would code the query using a NOT IN, and assuming the condition for a hash anti join are met, I would get the hash anti join. I can't depend on that in 9i unless I set the _always_anti_join parameter. Hopefully the CBO will make the right choices and I will not have to set it or worry about it. Larry G. Elkins -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
In that same vein, ( or is it vane, or maybe even vain? ;) I tried removing a correlated subquery from a bit of SQL just yesterday. It was duly replaced with an inline view, and the time to run the query increased by %20. :) Jared On Saturday 16 March 2002 01:53, Jonathan Lewis wrote: On that line, I've just had a note from Gaja about my commentary on the line: Rewrite all correlated subqueries using in-line views. He was concerned that I may not think the advice valid. So let me say quite categorically that the comment was not a criticism of the technique. Converting a subquery into a join is a strategy which will very often result in significant performance benefits and Gaja is, I think, the first person I have seen make a very special mention of it in a public paper. (No doubt someone will correct me on that quite soon). My point was that although it is not the automatic, the only, or even necessarily the correct solution to the sight of a correlated subquery, Gaja's paper will, one day, be quoted as the definitive proof that you should ALWAYS do it. And such is the stuff of the urban legend. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 March 2002 09:27 |You're cheating. Most advice containing 'always', even only once, stands |a big chance of becoming a legend very soon. |-- |Regards, | |Stephane Faroult |Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Yes, I've seen that also. Bugs 299259 and BUG:207590 describe this change in behavior, and why it was done, but those bugs are not accessible on MetaLink. Jared On Thursday 14 March 2002 15:03, Jonathan Lewis wrote: I hate to perpetuate a legend, but I THINK there was an early version where 'delete any table' was good enough. I seem to remember a period where there was a big fuss from people saying I've upgraded to version 7.0.16-ish and my truncates are not longer working - and the problem was that they had granted 'delete any table' and the upgrade required 'drop any table'. I also have a vague memory of seeing a release note (readme.doc) which highlighted this issue. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 14 March 2002 21:26 |the docs are (finally) correct. | |you have ALWAYS needed drop any table to truncate someone else's |table. The docs have always said you needed delete any table. Docs |(horrors! impossible to believe!) were wrong -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
but those bugs are not accessible on MetaLink. ALways seems like the bugs I go looking for end up having base bugs which are not publicly accessible on Metalink... What I find really funny is that once in a while in the bug reports that are publicly available, you will find the contact (customer) name and phone number... and sometimes you will find the phone number of the developer working on the bug. I haven't had the heart to call any of those that I've seen yet and tell them RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
John, You listed as an urban myth: * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
How about a datafile is lost before it is backed up during the hot backup. Where will you restore the file from? *You cannot use the backup from before; the logs have been reset. You could, however, use a cold backup. Once the hot backup is finished the cold one is not needed, but until then you are vulnerable. * I suppose one could get the transactions from logminer, go back to this backup, reset the logs and then apply the transactions. I wonder if that's been tested. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, March 15, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Hi Jared, * You *have* to take a COLD backup of the database after using resetlogs. (Not required - a Hot backup and archive logs is adequate. All hot backups / archive logs prior to that are invalid, though...) Consider the following: Time: t0: database restored t1: database opened with RESETLOGS t2: hot backup started ( database in archive log mode ) t3: users input very important transactions t4: database crashes, and must be restored How will you recover the transactions from time t3? As long as the online redologs are available, this should be no problem. I have successfully recovered databases where a log switch did not occur and recovery had to use an online redo log. (I am assuming that the lost datafiles will be restored from this hot backup fresh off the tapes) On the other hand, if the online redolog is hosed you have lost the transactions anyway, _regardless_ of the fact that a Cold backup was taken. Then you will have to go back to the _previous_ incarnation and redo the restore and then perform a ccf/resetlogs (i.e. back to square one). If you have a Cold backup, you restore the cold backup and go on with life. I.e. in both cases (availability of cold or hot backup, lost online redo log), you have lost transactions... Additionally, with a Hot backup and depending on what was lost, you can at least perform tablespace/datafile recovery . With a cold backup, you will have to restore the whole database The point I was trying to make was that a Cold backup after a RESETLOGS does not serve anything. Maybe there is still a gotcha I have not been able to figure out, so Backup/restore Gurus: take a bash at this logic! I would love to be corrected. (Btw, the previous recovery scenario was on 7.3.4 - things could have changed since, and I have not been able to test that out...) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
another possible source of the max 5 tables in a join myth could be that Sybase and SQLServer's query optimizer would only consider all possible join orders for up to 5 tables. this was true through at least vers 11.5 for Sybase. do the math - there are 120 possible join orders for 5 tables, 720 for 6, 5040 for 7 - an optimizer has to draw the line somewhere or we would spend more time optimizing than executing. anybody know how Oracle draws that line? -Original Message- Sent: Wednesday, March 13, 2002 5:09 PM To: Multiple recipients of list ORACLE-L Never true. I think the reason it sprang into existence was that on the AND-EQUAL path, which combines single-column indexes to access a single table, the maximum number of indexes that can be combined is five. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 13 March 2002 21:22 | My next to favourite legends. | Oracle can only use 5 indexes in a query | |Was this ever true, on v6 or v7? | |I was told by Oracle support about six years ago about the 5 index max. It |seemed to work for me. On a six table query, with five indexes used, when I |disabled one index Oracle started using another and this helped performance. |So it seemed like the max of 5 was true. | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Oracle eliminates lots of options by tracking 'best cost so far'. The frist step of optimisation is 'single table access path' i.e. if I make each table in turn the driving table for the query, how much does it cost to get all the data I need from just that table. Then assume that the cost of the full query is 88 if the order of tables is A,B,C,D,E but the cost of the single table access path into E was 92, then Oracle can spot that there is no point in trying any access paths that start with table E. That's just eliminated 24 paths out of 120. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 14 March 2002 15:26 |another possible source of the max 5 tables in a join myth could be that |Sybase and SQLServer's query optimizer would only consider all possible join |orders for up to 5 tables. this was true through at least vers 11.5 for |Sybase. do the math - there are 120 possible join orders for 5 tables, 720 |for 6, 5040 for 7 - an optimizer has to draw the line somewhere or we would |spend more time optimizing than executing. | |anybody know how Oracle draws that line? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Also, optimizer_search_limit (I think hidden in 8.1) defaults to 5, which means, consider all permutations, including Cartesian product joins, if the # of tables in the from clause is 5 or less. For more than 5 tables, Cartesian products are not considered initially. Another parameter is optimizer_max_permutations which defaults to 80,000 which is the max no. of join orders. Don't know how close anybody has got to this though. Jonathan? Paul -Original Message- Sent: Thursday, March 14, 2002 10:49 AM To: Multiple recipients of list ORACLE-L Oracle eliminates lots of options by tracking 'best cost so far'. The frist step of optimisation is 'single table access path' i.e. if I make each table in turn the driving table for the query, how much does it cost to get all the data I need from just that table. Then assume that the cost of the full query is 88 if the order of tables is A,B,C,D,E but the cost of the single table access path into E was 92, then Oracle can spot that there is no point in trying any access paths that start with table E. That's just eliminated 24 paths out of 120. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 14 March 2002 15:26 |another possible source of the max 5 tables in a join myth could be that |Sybase and SQLServer's query optimizer would only consider all possible join |orders for up to 5 tables. this was true through at least vers 11.5 for |Sybase. do the math - there are 120 possible join orders for 5 tables, 720 |for 6, 5040 for 7 - an optimizer has to draw the line somewhere or we would |spend more time optimizing than executing. | |anybody know how Oracle draws that line? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
But they need to be smarter. A normal OCB will no longer do the trick. -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 13, 2002 7:18 AM To: Multiple recipients of list ORACLE-L Subject: RE: Fav. Urban Legend... That as Oracle software becomes larger, fewer DBAs are required. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 13, 2002 12:28 AM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... oh man, mine has to be what is probably Jeremiah's as well: the myth that Oracle doesn't write to the database files when you are in hot backup mode --- Freeman, Robert [EMAIL PROTECTED] wrote: I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Gotta be this one. I wouldn't love it so much except for the part about the USS Nimitz. When you place a tablespace in backup mode, the Oracle instance notes that a backup is being performed and internally compensates for it. As you know, it is impossible to make an authentic copy of a database file that is being written to. On receipt of the command to begin the backup, however, Oracle ceases to make direct changes to the database file. It uses a complex combination of rollback segments, buffers, redo logs, and archive logs to store the data until the end backup command is received and the database files are brought back in sync. Simplifying a hot backup in this way is tantamount to classifying the USS Nimitz as a boat. The complexity of the actions taken by the Oracle RDBMS under a hot backup could consume an entire chapter and is beyond the scope of this book. What you should understand is the trade-off for taking a hot backup is increased use of rollback segments, redo logs, archive logs, and internal buffer areas within the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 14 Mar 2002, Freeman, Robert wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
#3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -Original Message- Sent: Thursday, March 14, 2002 12:21 PM To: Multiple recipients of list ORACLE-L Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
[EMAIL PROTECTED] wrote: So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? if you have a hit ratio of less than 90%, your database is in desperate need of tuning. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. A journey of a thousand miles starts with a single step. - Chinese Proverb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
You had to unleash that one on us. -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L Gotta be this one. I wouldn't love it so much except for the part about the USS Nimitz. When you place a tablespace in backup mode, the Oracle instance notes that a backup is being performed and internally compensates for it. As you know, it is impossible to make an authentic copy of a database file that is being written to. On receipt of the command to begin the backup, however, Oracle ceases to make direct changes to the database file. It uses a complex combination of rollback segments, buffers, redo logs, and archive logs to store the data until the end backup command is received and the database files are brought back in sync. Simplifying a hot backup in this way is tantamount to classifying the USS Nimitz as a boat. The complexity of the actions taken by the Oracle RDBMS under a hot backup could consume an entire chapter and is beyond the scope of this book. What you should understand is the trade-off for taking a hot backup is increased use of rollback segments, redo logs, archive logs, and internal buffer areas within the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
It's a DDL command. You need alter table for this. Delete any table in a DML privilege. Rodd On Thu, 2002-03-14 at 13:35, Post, Ethan wrote: Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Quotable quotes
..increasing the buffer hit ratio from 95 to 99 percent can yield performance gains of over 400 percent Retrieving data from memory is over 1 times faster than retrieving it from disk A more efficient method is to have the database write to the redo logs only when all the log buffers are filled or when a commit is issued. This happens when log_checkpoint_interval is set to zero This allowed the O/S to dedicate a specific background process to moving the log buffers upon checkpoint to the redo files At times, specifying multiple hints can mysteriously cause the query to use none of the hints The INDEX_DESC hint causes indexes to be sorted in descending order...This index is overwritten when the query has multiple tables Cheers Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
From the Oracle9i docs@ http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st atements_108a.htm#2067573 http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/s tatements_108a.htm#2067573 Under TRUNCATE To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. Is this a documentation bug? Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 2:49 PM To: Multiple recipients of list ORACLE-L It's a DDL command. You need alter table for this. Delete any table in a DML privilege. Rodd On Thu, 2002-03-14 at 13:35, Post, Ethan wrote: Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
drop any table One of the oldest bugs in Oracle Docs :) Finally got fixed in 8.1.7 Docs. - Kirti -Original Message- Sent: Thursday, March 14, 2002 1:36 PM To: Multiple recipients of list ORACLE-L Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
it's still the one about Oracle not writing to the datafiles if a tablespace is in hot backup mode :0 --- Freeman, Robert [EMAIL PROTECTED] wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Last time I read the doc's this was true... (and that was about a second ago!). is there some magic I do not know about? (or are you alluding to creating PL/SQL to do this?) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
the docs are (finally) correct. you have ALWAYS needed drop any table to truncate someone else's table. The docs have always said you needed delete any table. Docs (horrors! impossible to believe!) were wrong --- Freeman, Robert [EMAIL PROTECTED] wrote: From the Oracle9i docs@ http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st atements_108a.htm#2067573 http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/s tatements_108a.htm#2067573 Under TRUNCATE To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. Is this a documentation bug? Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 2:49 PM To: Multiple recipients of list ORACLE-L It's a DDL command. You need alter table for this. Delete any table in a DML privilege. Rodd On Thu, 2002-03-14 at 13:35, Post, Ethan wrote: Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Quotable quotes
This does make you wonder about the recently awarded Master's certification given by Oracle. (Not taking anything away from Jeremiah though) Paul -Original Message- Sent: Thursday, March 14, 2002 3:20 PM To: Multiple recipients of list ORACLE-L ..increasing the buffer hit ratio from 95 to 99 percent can yield performance gains of over 400 percent Retrieving data from memory is over 1 times faster than retrieving it from disk A more efficient method is to have the database write to the redo logs only when all the log buffers are filled or when a commit is issued. This happens when log_checkpoint_interval is set to zero This allowed the O/S to dedicate a specific background process to moving the log buffers upon checkpoint to the redo files At times, specifying multiple hints can mysteriously cause the query to use none of the hints The INDEX_DESC hint causes indexes to be sorted in descending order...This index is overwritten when the query has multiple tables Cheers Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...Quotable quotes
Ofcourse they are all true ! ;;-) Connor McDonald wrote: ..increasing the buffer hit ratio from 95 to 99 percent can yield performance gains of over 400 percent Retrieving data from memory is over 1 times faster than retrieving it from disk A more efficient method is to have the database write to the redo logs only when all the log buffers are filled or when a commit is issued. This happens when log_checkpoint_interval is set to zero This allowed the O/S to dedicate a specific background process to moving the log buffers upon checkpoint to the redo files At times, specifying multiple hints can mysteriously cause the query to use none of the hints The INDEX_DESC hint causes indexes to be sorted in descending order...This index is overwritten when the query has multiple tables Cheers Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Holy cow. That is outrageous! Gets my vote. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: Gotta be this one. I wouldn't love it so much except for the part about the USS Nimitz. When you place a tablespace in backup mode, the Oracle instance notes that a backup is being performed and internally compensates for it. As you know, it is impossible to make an authentic copy of a database file that is being written to. On receipt of the command to begin the backup, however, Oracle ceases to make direct changes to the database file. It uses a complex combination of rollback segments, buffers, redo logs, and archive logs to store the data until the end backup command is received and the database files are brought back in sync. Simplifying a hot backup in this way is tantamount to classifying the USS Nimitz as a boat. The complexity of the actions taken by the Oracle RDBMS under a hot backup could consume an entire chapter and is beyond the scope of this book. What you should understand is the trade-off for taking a hot backup is increased use of rollback segments, redo logs, archive logs, and internal buffer areas within the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 14 Mar 2002, Freeman, Robert wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Ok, I understand what you were saying then... thanks! Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 4:16 PM To: Multiple recipients of list ORACLE-L the docs are (finally) correct. you have ALWAYS needed drop any table to truncate someone else's table. The docs have always said you needed delete any table. Docs (horrors! impossible to believe!) were wrong --- Freeman, Robert [EMAIL PROTECTED] wrote: From the Oracle9i docs@ http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st atements_108a.htm#2067573 http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/s tatements_108a.htm#2067573 Under TRUNCATE To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. Is this a documentation bug? Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 2:49 PM To: Multiple recipients of list ORACLE-L It's a DDL command. You need alter table for this. Delete any table in a DML privilege. Rodd On Thu, 2002-03-14 at 13:35, Post, Ethan wrote: Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
One of these days my eyes will distinguish the word DROP and the word DELETE... For now, just chalk it up to age :-)) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 3:21 PM To: Multiple recipients of list ORACLE-L From the Oracle9i docs@ http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st atements_108a.htm#2067573 http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/s tatements_108a.htm#2067573 Under TRUNCATE To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. Is this a documentation bug? Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 14, 2002 2:49 PM To: Multiple recipients of list ORACLE-L It's a DDL command. You need alter table for this. Delete any table in a DML privilege. Rodd On Thu, 2002-03-14 at 13:35, Post, Ethan wrote: Whoa, #3 is a new one on me! What privs do allow that one? -Original Message- Sent: Thursday, March 14, 2002 12:57 PM To: Multiple recipients of list ORACLE-L #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Funny, I was just told that by a Network Appliance rep today. Had to set him straight. :) I hate doing that. ;) Jared On Thursday 14 March 2002 12:42, Rachel Carmichael wrote: it's still the one about Oracle not writing to the datafiles if a tablespace is in hot backup mode :0 --- Freeman, Robert [EMAIL PROTECTED] wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
that one's been around for years! Jeremiah has corrected, and corrected and corrected it, but it persists just like those baby alligators in the NYC sewer system rumors... --- Paul Baumgartel [EMAIL PROTECTED] wrote: Holy cow. That is outrageous! Gets my vote. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: Gotta be this one. I wouldn't love it so much except for the part about the USS Nimitz. When you place a tablespace in backup mode, the Oracle instance notes that a backup is being performed and internally compensates for it. As you know, it is impossible to make an authentic copy of a database file that is being written to. On receipt of the command to begin the backup, however, Oracle ceases to make direct changes to the database file. It uses a complex combination of rollback segments, buffers, redo logs, and archive logs to store the data until the end backup command is received and the database files are brought back in sync. Simplifying a hot backup in this way is tantamount to classifying the USS Nimitz as a boat. The complexity of the actions taken by the Oracle RDBMS under a hot backup could consume an entire chapter and is beyond the scope of this book. What you should understand is the trade-off for taking a hot backup is increased use of rollback segments, redo logs, archive logs, and internal buffer areas within the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 14 Mar 2002, Freeman, Robert wrote: Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Sports - live college hoops coverage http://sports.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
Some more from me! (if they have already not been stated) The 'alter database backup controlfile' creates a backup of the controlfile that is used in 'recover database using backup controlfile' Backup the controlfiles using 'shutdown abort' to stop the database damages it beyond repair John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 10:57 AM To: Multiple recipients of list ORACLE-L Subject: RE: Fav. Urban Legend...take two #3 One has to have 'delete any table' privilege to truncate someone else's tables. #2 Oracle does not write to the data file when the tablespace is in backup mode. And... #1 High Cache Hit Ratios (in the upper 90s) are always good. - Kirti -Original Message- Sent: Thursday, March 14, 2002 12:21 PM To: Multiple recipients of list ORACLE-L Ok... one of my favorite Urban Legends is this one: The book is always right. In other words, if it's written down in a book we bought off of Amazon, it must be so. So, I'd like to ask, without anyone taking potshots at specific authors, what is the dumbest, silliest, or most technically incorrect thing you have ever seen in an Oracle book? RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...take two
I hate to perpetuate a legend, but I THINK there was an early version where 'delete any table' was good enough. I seem to remember a period where there was a big fuss from people saying I've upgraded to version 7.0.16-ish and my truncates are not longer working - and the problem was that they had granted 'delete any table' and the upgrade required 'drop any table'. I also have a vague memory of seeing a release note (readme.doc) which highlighted this issue. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 14 March 2002 21:26 |the docs are (finally) correct. | |you have ALWAYS needed drop any table to truncate someone else's |table. The docs have always said you needed delete any table. Docs |(horrors! impossible to believe!) were wrong | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
That as Oracle software becomes larger, fewer DBAs are required. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 13, 2002 12:28 AM To: Multiple recipients of list ORACLE-L Subject:Re: Fav. Urban Legend... oh man, mine has to be what is probably Jeremiah's as well: the myth that Oracle doesn't write to the database files when you are in hot backup mode --- Freeman, Robert [EMAIL PROTECTED] wrote: I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
My favorite (or at least the one that annoys me the most) is Every object in the INITIAL extension. After that would be OFA where the DBA doesn't get the difference between an LV and a physical drive. Freeman, Robert To: Multiple recipients of list ORACLE-L Robert_Freem[EMAIL PROTECTED] an cc: @csx.comSubject: Fav. Urban Legend... Sent by: root 03/12/2002 03:43 PM Please respond to ORACLE-L I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
And I forgot... * Index space is never reused ever (Corollary: set PCTFREE=0 on all indexes) * Bind variables simply serve to add complexity to the application (a Tom Kyte special) --- Jonathan Lewis [EMAIL PROTECTED] wrote: My next to favourite legends. Small tables don't need to be indexed Small tables are anything 4 blocks or less Oracle copies the entire row into the rollback segment when updating a single column. Oracle copies the entire block into the rollback segment when updating a row. Oracle can only use 5 indexes in a query (though I haven't heard that one much recently) Setting tablespace default pctincrease to 1 reduces fragmentation problems. (but that's going out of fashion too) But I guess you, Jeremiah and Rachel have already got those covered. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 12 March 2002 22:54 |Everyone, some great ideas | |RF | |Robert G. Freeman - Oracle8i OCP |Oracle DBA Technical Lead |CSX Midtier Database Administration -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
oh yes, one more, I got this from a vendor, as an explanation of why they didn't do a lookup table but instead added columns to the row for EVERY possible value in the lookup table: Oracle doesn't handle joins with more than 4 tables very well --- Jonathan Lewis [EMAIL PROTECTED] wrote: My next to favourite legends. Small tables don't need to be indexed Small tables are anything 4 blocks or less Oracle copies the entire row into the rollback segment when updating a single column. Oracle copies the entire block into the rollback segment when updating a row. Oracle can only use 5 indexes in a query (though I haven't heard that one much recently) Setting tablespace default pctincrease to 1 reduces fragmentation problems. (but that's going out of fashion too) But I guess you, Jeremiah and Rachel have already got those covered. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 12 March 2002 22:54 |Everyone, some great ideas | |RF | |Robert G. Freeman - Oracle8i OCP |Oracle DBA Technical Lead |CSX Midtier Database Administration -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Yep, had that one... another of my fav's! Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Wednesday, March 13, 2002 8:28 AM To: Multiple recipients of list ORACLE-L And the time honoured favourite... All performance problems can be resolved by modification of init.ora parameters, in particular, any parameter that starts with an underscore --- Jonathan Lewis [EMAIL PROTECTED] wrote: My next to favourite legends. Small tables don't need to be indexed Small tables are anything 4 blocks or less Oracle copies the entire row into the rollback segment when updating a single column. Oracle copies the entire block into the rollback segment when updating a row. Oracle can only use 5 indexes in a query (though I haven't heard that one much recently) Setting tablespace default pctincrease to 1 reduces fragmentation problems. (but that's going out of fashion too) But I guess you, Jeremiah and Rachel have already got those covered. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 12 March 2002 22:54 |Everyone, some great ideas | |RF | |Robert G. Freeman - Oracle8i OCP |Oracle DBA Technical Lead |CSX Midtier Database Administration -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
My humble suggestions 1. CBO doesn't work ... (well it failed 3 years ago when I compiled a Forms 3.0 form against 803 database, so it is STILL true). 2. Writing reusable code is not good, it is waste of time. 3. Exception handling ... Oracle is pretty good are reporting them ... 4. We need to get a larger box with 32 CPUs and 16TB of disk and 32GB of RAM, that will make our application run faster 5. Application code is already optimal since I wrote it in 7.2, no need to revisit the code for our upgrade to 9i, it will optimize itself. 6. You DBA guys are overly overrated ... 7. Once Table with 32 columns, 28 are indexed in 17 different indexes on the table and this (highly transactional inserts, updates) table is used by at-least 10 users at any given time ... the developer thinks this design is perfect Whew ... I feel better now ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: Fav. Urban Legend...
Hey Guys,Did anyone have a look at Gaja's new paper on myths and folklore about Oracle at Craig Shallahamer's site? Wonderful reading.RajHallas John [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]03/13/2002 06:28 AM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: RE: Fav. Urban Legend... Patrice, Given the current state of the job market I am not sure if this is myth or fact John -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 12:18 To: Multiple recipients of list ORACLE-L Subject: RE: Fav. Urban Legend... That as Oracle software becomes larger, fewer DBAs are required. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Rgion des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 13, 2002 12:28 AM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... oh man, mine has to be what is probably Jeremiah's as well: the myth that Oracle doesn't write to the database files when you are in hot backup mode --- Freeman, Robert [EMAIL PROTECTED] wrote: I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). =This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately.= -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
RE: Fav. Urban Legend...
Oracle doesn't handle joins with more than 4 tables very well maybe because they wrote their application for sql server?? Tell them if you set _allow_tables_to_join = n hidden parameter it WILL allow you to join n tables, but not n+1. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: Fav. Urban Legend...
If you buffer cache hit ratio is 90% you're experiencing poor performance and you're a DBA wimp. :-) -Original Message- Sent: Tuesday, March 12, 2002 1:43 PM To: Multiple recipients of list ORACLE-L I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fav. Urban Legend...
Reading it right now.
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, March 13, 2002 11:28
AM
Subject: RE: Fav. Urban Legend...
Hey Guys,
Did anyone have a look at Gaja's new paper on myths and folklore
about Oracle at Craig Shallahamer's site? Wonderful reading.
Raj
Hallas John [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]03/13/2002 06:28 AM PSTPlease respond to
ORACLE-LTo: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]cc: bcc: Subject: RE: Fav. Urban
Legend...
Patrice, Given the current state of the job
market I am not sure if this is myth or fact
John -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 12:18 To: Multiple recipients of list ORACLE-L
Subject: RE: Fav. Urban Legend... That
as Oracle software becomes larger, fewer DBAs are required.
Regards, Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin Operations | Admin. et Exploit. des
systèmes Technology
Services | Services
technologiques Informatics
Branch | Direction de
l'informatique Maritimes Region, DFO |
Région des Maritimes, MPO E-Mail:
[EMAIL PROTECTED] -Original
Message- Sent: Wednesday, March 13,
2002 12:28 AM To: Multiple
recipients of list ORACLE-L
Subject: Re: Fav. Urban
Legend... oh man, mine has to be what is probably
Jeremiah's as well: the myth that Oracle doesn't
write to the database files when you are in hot backup
mode --- "Freeman, Robert "
[EMAIL PROTECTED] wrote: I'm putting
the final touches on my IOUG-A presentation (I got an
extension for those who realize that I'm late on it!)... I'm doing Oracle
Urban Legends. I've got several in my
presentation but I thought I'd ask here, before I put
the presentation to bed, what
your favorite (or the one you find the most
irritating) Oracle Urban legend was
RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead
CSX Midtier Database Administration The
Cigarette Smoking Man: Anyone who can appease a man's conscience
can take his freedom away from
him.-- Please see the
official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051
FAX: (858) 538-5051 San Diego,
California -- Public Internet access
/ Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L
(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!? Try FREE Yahoo! Mail - the
world's greatest free email! http://mail.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 FAX: (858)
538-5051 San Diego,
California -- Public Internet access
/ Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
message to: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (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: Boivin, Patrice J
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858)
538-5051 FAX: (858) 538-5051 San Diego,
California -- Public Internet access
/ Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
message to: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (or the name of mailing
list you want to be removed from). You may also
send the HELP command for other information (like subscribing).
=This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The
information is intended to be for the use of the
individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution
or use of the contents of this information is
prohibited. If you have received this electronic
message in error, please notify us by
RE: Fav. Urban Legend...
Bigger is better. This is the best biggest myth. Say that fast 3 times. :-) -Original Message- Sent: Wednesday, March 13, 2002 5:18 AM To: Multiple recipients of list ORACLE-L That as Oracle software becomes larger, fewer DBAs are required. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 13, 2002 12:28 AM To: Multiple recipients of list ORACLE-L Subject:Re: Fav. Urban Legend... oh man, mine has to be what is probably Jeremiah's as well: the myth that Oracle doesn't write to the database files when you are in hot backup mode --- Freeman, Robert [EMAIL PROTECTED] wrote: I'm putting the final touches on my IOUG-A presentation (I got an extension for those who realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've got several in my presentation but I thought I'd ask here, before I put the presentation to bed, what your favorite (or the one you find the most irritating) Oracle Urban legend was RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
