I have just realised that 2 databases that I have inhereted has a srcipt that is used to shut the database down everyday with the abort option.
I have asked everybody but no one seems to be able to tell me why the abort option is being used? The dba that I inhereted the database from was a pretty good one so I can't imagine why the abort option is being used insted of the immediate option. I am thinking about changing the script but I am wondering if it's going to have a negative impact since I don't know why the abort option was chosen instead of the immediate option.
Question 1 .I know this is "crazy" question but can anybody imagine why the abort option is used instead of immediate for every day shut down?
Question 2. Can any body tell me the disadvantges involved in shutting down the database with an abort option before a backup is done? 2 of the databases are in archive log mode and 2 are in no achive log mode.
NOTE: I have tried to contact the old DBA but with no success.
This z what Oracle Says: See you have the following situations with your Application/Database requirements. I don't think so... and I don't think that you will be having -ve impact if you change 'abort' with 'immediate'. Database can be shut down instantaneously by aborting the database's instance.
--Perform this type of shutdown only in the following situations:
*The database or one of its applications is functioning irregularly and neither of the other types of shutdown works.
*You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
*You experience problems when starting a database instance.
--Aborting an instance shuts down a database and yields the following results:
*Current client SQL statements being processed by Oracle are immediately terminated.
*Uncommitted transactions are not rolled back.
*Oracle does not wait for users currently connected to the database to disconnect; Oracle implicitly disconnects all connected users.
*The next startup of the database will require instance recovery procedures.
If both the normal and immediate shutdown options do not work, abort the current database instance immediately by issuing the SHUTDOWN command with the ABORT option.
--Conclusion from my discussion with co-dba`s:
Use it when you want to kick out users instantly ignoring transactions and transaction statuses. Couple of shops use 'Abort' option for this purpose.
I forgot to mention you, what maachan is saying TRUE. If you need to use 'ABORT' use it and make sure that you bring it up and shutdown the database using 'IMMEDIATE' / 'NORMAL' before taking backup.
Check the script carefully.
Does it do a shutdown abort followed by a startup and shutdown immediate?
A cold backup taken after that would be perfectly fine.
This sequence allows for a controlled timeline in scripted after-hours backups. If there was a pending transaction that was taking hours to complete, then an immediate shutdown would take hours to complete. By shutting down with abort option, and then doing a clean startup/shutdown sequence, the time it takes for the backup to complete would be predictable and controlled.
1)No new connections are allowed after the statement is issued
2)Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database.
3)The next startup of the database will not require any instance recovery procedures.
1)Current client SQL statements being processed by Oracle are terminated immediately.
2) Any uncommitted transactions are rolled back.
(IF LONG UNCOMMITTED TRANSACTIONS EXIST, THIS METHOD OF SHUTDOWN MIGHT NOT COMPLETE QUICKLY , DESPITE ITS NAME.)
3)Oracle does not wait for users currently connected to the database to disconnect;
4)Oracle implicitly rolls back active transactions and disconnects all connected users.
5)The next startup of the database will not require any instance recovery procedures.
1)Current client SQL statements being processed by Oracle are immediately terminated.
2) Uncommitted transactions are not rolled back.
3) Oracle does not wait for users currently connected to the database to disconnect.
4)Oracle implicitly disconnects all connected users.
5)The next STARTUP of the database will require instance recovery procedures; however, instance recovery will be automatically performed during database startup.
If your database is very active and has long running transactions, shutdown immediate may take longer than acceptable.
Shutdown abort is OK if your redo logs are well protected
and is usually faster than shutdown immediate.
Instead of waiting for the database to be cleanly shut down, you can shutdown abort, startup restrict, then shutdown normal or immediate. These three steps achieve the same goal as one step shutdown immediate but are proven faster in general.