DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: SHUTDOWN IMMEDIATE

  1. #1
    Join Date
    Jun 2002
    Posts
    64
    Hi All,

    Is it advisable to use SHUTDOWN IMMEDIATE on a production database?

    Let's say a developer writes a pl/sql code which is suppose to insert records into multiple tables, where he inserts records into one table and commits and then he inserts into the next table and so on. (Example)

    Now, Just after inserting into first table and before inserting into second table, if the DBA issues SHUTDOWN IMMEDIATE, the first table will have the committed records, but the second table will not have the records, because uncommitted transactions will get rolled back when issuing SHUTDOWN IMMEDIATE.

    This makes the database logically inconsistent!

    Would it be ideal, if I use SHUTDOWN TRANSACTIONAL instead of using SHUTDOWN IMMEDIATE in a situation like this on a production database.


    Please share your exp.


    Thanks in advance
    Najmal



  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Shutdown immediate will wait for active transaction to complete (sometimes you could see this in the alert.log when shutting down a database with immediate)

    Read this: http://technet.oracle.com/doc/server...7/ch3.htm#2879

    Cheers

    Angel

  3. #3
    Join Date
    Jun 2002
    Posts
    64
    Hi Angel,

    Shutdwon Immediate WILL NOT wait for active transactions to complete !!!

    Immediately you issue SHUTDOWN IMMEDIATE, oralle will terminate currently executing process and rollback all uncommitted transactions.

    Test the scenario I explained in my question.


    Anybody else have comments on using SHUTDOWN TRANSACTIONAL please.......


    Cheers


  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by najmalp
    Hi Angel,

    Shutdwon Immediate WILL NOT wait for active transactions to complete !!!

    Immediately you issue SHUTDOWN IMMEDIATE, oralle will terminate currently executing process and rollback all uncommitted transactions.

    Test the scenario I explained in my question.


    Anybody else have comments on using SHUTDOWN TRANSACTIONAL please.......


    Cheers

    Hi Najmalp,

    From the document above:

    Note:

    The SHUTDOWN IMMEDIATE statement disconnects all existing idle connections and shuts down the database. If, however, you've submitted processes (for example, inserts, selects or updates) that are awaiting results, the SHUTDOWN IMMEDIATE statement allows the process to complete before disconnecting you.


    Cheers

    Angel

  5. #5
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    Shutdown IMMEDIATE will NOT wait for transactions to complete (A transaction may contain multiple statements).

    Shutdown TRANSACTIONAL will wait until the next COMMIT or ROLLBACK (A transaction concludes with a COMMIT or ROLLBACK).

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    IF THERE ARE ACTIVE CALLS IN A DATABASE, SHUTDOWN IMMEDIATE WILL WAIT TO FINISH THIS ACTIVE CALLS TO SHUTDOWN THE DATABASE.

    Cheers

    Angel

  7. #7
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    Keyword... ACTIVE CALL (i.e The current statement)

    As soon as the current statement (Not to be confused with a transaction) completes "That's it". No new statements will be accepted. The current session is rolled back without regard to the current state of the transaction.

    Shutdown transactional will continue to accept statements in an active session until a COMMIT or ROLLBACK is issued.

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Immediate vs. transactional

    The difference between immediate and transactional:
    Transactional waits for active transactions to complete.
    Immediate implictly rollback all noncommited transaction.

    In your case it's the programming that is wrong.
    You would need shutdown normal to pull it through.

    In reality there are situations other than shutdowns that would force your pl/sql code to make your database logically inconsistant.

    Look, you have transactions A, B and C which insert rows into tables A, B and C.
    Let's say that in this moment transaction B is active.
    If you issue shutdown immediate, transaction B will be stopped and rolled back. So you have 'new' A, and 'old' B and C tables.
    When you issue shutdown transactional, oracle will wait for transaction B to complete, but won't allow transaction C to start. So, you would get 'new' A and B, but still 'old' C tables.

    The work you do in a transaction, must be logically consistent itself. I think, this is exactly why transactions are there for us.

    I hope I was clear enough...

    force a developer to listen to you
    Tomaz

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Immediate vs. transactional

    Originally posted by TomazZ

    In your case it's the programming that is wrong.
    I definitely agree. If the statements aren't in a transaction, how do you expect Oracle to know how to handle them?
    Jeff Hunter

  10. #10
    Join Date
    Jun 2002
    Posts
    64
    Hi alapps,

    You are getting into the point.

    Have you seen the question which I initiated? Based on our discussion, what I conclude is that we should not use SHUTDOWN IMMEDIATE unless there is an unavoiadable situation . Isn't it?

    About SHUTDOWN TRANSACTIONAL

    What happens if a user keeps inserting and not doing a commit or rollback, will Oracle wait until he issues a commit or rollback?


    Cheers







Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width