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 rest
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 = 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).
Urgent - Query Optimization
Hi, I have a report which was taking 48 minutes. So I added rule hint in that now it is taking 14-15 min but, still it's not acceptable.. Is there any way I can elimintae these nested loops or Can I replace them with hash joins. or is there any other way to optimize this query. I don't have always_semi_join to hash_joins in the init.ora file and I don't want to set that in init.ora. Is there any session level setting like any rule which enforces the hash join. Regards, SELECT STATEMENT Optimizer=HINT: RULE SORT (UNIQUE) NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS TABLE ACCESS (FULL) OF TABLE_EMP_CERT TABLE ACCESS (BY INDEX ROWID) OF TABLE_CERTIFICATION INDEX (UNIQUE SCAN) OF CERTIFICATION_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_PART_CLASS INDEX (UNIQUE SCAN) OF PART_CLASS_NAME_INDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_EMPLOYEE INDEX (UNIQUE SCAN) OF EMPLOYEE_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_PART_NUM INDEX (RANGE SCAN) OF IND_PART_NUM2PART_CLASS (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_MOD_LEVEL INDEX (RANGE SCAN) OF IND_PART_INFO2PART_NUM (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE_PART INDEX (RANGE SCAN) OF IND_SITE_PART2PART_INFO (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_CASE INDEX (RANGE SCAN) OF IND_CASE_PROD2SITE_PART (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_ADDRESS INDEX (UNIQUE SCAN) OF ADDRESS_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_X_CASE_INFORMATION INDEX (RANGE SCAN) OF CASE_OBJIDINDEX (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_DEMAND_HDR INDEX (RANGE SCAN) OF IND_DEMANDHDR_CASEINFO2CASE (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_DEMAND_DTL INDEX (RANGE SCAN) OF IND_DEMAND_DTL2DEMAND_HDR (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_CONDITION INDEX (UNIQUE SCAN) OF CONDITION_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_GBST_ELM INDEX (UNIQUE SCAN) OF GBST_ELM_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_MOD_LEVEL INDEX (UNIQUE SCAN) OF MOD_LEVEL_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_PART_NUM INDEX (UNIQUE SCAN) OF PART_NUM_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE) -- 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).
