-
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
-
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
-
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
-
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
-
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).
-
IF THERE ARE ACTIVE CALLS IN A DATABASE, SHUTDOWN IMMEDIATE WILL WAIT TO FINISH THIS ACTIVE CALLS TO SHUTDOWN THE DATABASE.
Cheers
Angel
-
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.
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|