RE: shutdown abort / startup restrict / shutdown vs. shutdown imm
Inder Singh IT Management 1101 Pacific Ave. Ph:859-815-2460 Email: [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 25, 2002 12:49 PM To: Multiple recipients of list ORACLE-L imm My old argument against shutdown abort was a nasty little bug (it's been fixed in 9i). Here is how you simulate the bug: 1. Create a table, and insert a large number of records into it. do not commit. 2. shutdown abort. Startup the database. 3. Now, after the database is open and while Oracle is rolling back all of those inserts, truncate the table Watch an ora-600 appear and your database *crash*. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author Oracle9i RMAN Backup and Recovery (Oracle Press - Oct 2002) Oracle9i New Features (Oracle Press) Mastering Oracle8i (Sybex) The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Thursday, July 25, 2002 12:19 PM To: Multiple recipients of list ORACLE-L imm Let me share with you the reason that shutdown abort is not a good practice: One day, along time ago, a database on the mainframe (ADABAS in this case) come up after a power failure (don't ask, the UPS and the generators that are the backup power supply also failed) with a message that the power failure occurred while writing a block to the disk and the database is corrupted. SOP, restore and roll forward. The roll forward abended and we finished up restoring to the morning backup after 20 hours work. Net loss to the bank about 1/2 million dollars in lost revenues. My luck was that during the postmortem the supplier technical expert said I did the right thing. Anyway NOBODY assure you that the recovery process after abort will not fail and leave you with the need to restore and roll forward. As Tom said in the discussion about moving the clock back If I will suggest to my client to stop the DB for 1.25 hours So the 2-20 minutes savings can become a lengthy process. I will use abort in the rare cases where there is no other option but not as everyday practice. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 10:53 AM I'm not saying that the shutdown abort *caused* the redo log corruption, but the code that writes redo logs is, like any other software, prone to bugs. Redo logs are only ever read during a recovery of one sort or another, so the code only really gets tested then, and if it fails, there is no fallback. The code that reads and writes to datafiles, on the other hand, is tested all the time, and if *it* fails, you've always got the redo logs. We use a script that tries to do a shutdown immediate and if that fails to complete in a reasonable time, does a checkpoint/abort/startup restrict/shutdown immediate. In a perfect world, the latter wouldn't be necessary because I would have investigated and cured every possible cause for shutdown immediate to hang, but a) debugging these problems is difficult and b) the effort involved upgrading to a sensible version of Oracle is not worth the (supposedly) limited lifetime of this database. Regards David Lord -Original Message- From: Connor McDonald [mailto:[EMAIL PROTECTED]] Sent: 24 July 2002 23:44 To: Multiple recipients of list ORACLE-L Subject: RE: shutdown abort / startup restrict / shutdown vs. shutdown imm But if you are concerned that shutdown abort could corrupt your redo logs, then that is equivalent to mandating that all servers (that run oracle) must be on an infinite uninterruptible power supply. An instance failure (eg loss of power) is effectively a shutdown abort - so the only way to avoid that would be to have power available all the time. You couldn't have a UPS that is good for (say) 12 hours - because we can never guarantee that a shutdown immediate would finish in this amount of time - and you could not speed up the job with a shutdown abort because that is the cause of all the consternation in the first place If you're getting corrupt redo logs with shutdown abort, then you're exposed to corrupt redo logs anyway. Its not a shutdown abort problem, its a bug in either the oracle or OS layer. hth connor --- April Wells [EMAIL PROTECTED] wrote: That is EXACTLY what happened a week and a half ago. We had to do a shutdown abort because it wouldn't go down, and when we tried to restart it, it wouldn't come back... redo log corruption... and this being test... it isn't in archive log mode (another valid solution but no longer really an option in our case). After we can get back in to the building after the teeny little fire and vandalism thing we have going this morning and I can get all concerned parties in the same place (sans smoke and water) my suggestion is going to be that since we don't know
RE: delete from table question
Hi, I have this script which deletes the record from the master as well as all the Child tables also. May be this will help you. But I have never used this script before. Check this on Development before running it in Prod. To delete records from all the dependent tables and the master table for a particular record. Select 'Alter Table ' || Table_Name || ' Modify Consraint ' || Constraint_Name || ' On Delete Cascade;' From User_Constraints where Constraint_Type = 'R' and R_Constraint_Name = PK Constraint Name Regards, -Original Message- Sent: Tuesday, July 16, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hi All - I am trying to delete from a table that has 24 child tables. Because the constraints are not 'cascade delete', I am hitting the following: ORA-02292: integrity constraint (NVALET.FK_TOPOGROUPMEMBERNE2) violated - child record found If I want to go ahead and delete the child records as well, there seem to be 2 options: 1. Write a sql/plsql procedure to delete the child records first before each deleting each record from the parent table. 2. Recreate the constraints with 'cascade delete' and delete the records from parent table. After that recreate the constraints again without cascade delete. Either option requires some work. Does anybody have a better idea? Also can anybody have a script to recreate all constraints to share? TIA Dennis Meng Database Administrator Focal Communications Corp. -- 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). -- 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).
Urgent - Upgrade from 8.1.6 to 8.1.6.3
Hi, I have upgraded two databases from 8.1.6 to 8.1.6.3. After upgrade I ran catalogand catproc.sql for both of them. For first database ir ran fine. But for the second one it never started. So I cacelled that one. I started again and now it is been 20-25 minutes. But, stll there is no activity. Am I hitting some bug or something?? Regards -- 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).
Data Warehouse design
Hi, I am in the process of designing the data warehouse. My Question is can I define the relationship between dimension tables. Like I have country,customer and time zone dimension tables. Can I add relationship between customer,country and time zone just to validate the data before load whether the country and zip code is correct or should I integrate the country and time zone with customer itself. If I integrate this. Is it going to affect the performance. 'cos for every record it is going to validate all these things before load. Can I create sequences for primary keys(fact tables and for some of the dimenasion tables also). Is it OK to have a sequence as primary key. I need your expert views on this. Regards, -- 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: SQL*Loader question
I don't know of any such option in sqlloader. But, you can do one thing copy the 100 records from the file and create a new file and try to load that one. Hope this helps. Regards, Inder -Original Message- Sent: Tuesday, June 04, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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).
