I know how to enable archivelog mode, but what is the reason that to enable archive log we need database restart. which process forces to do that.
Sorry, I saw your message in the thread you hijacked before I saw this one. See my reply there ..... changing archive log mode without shutting down database
Why do we have archive log mode?
To be able to do recovery. The database is always in a fuzzy mode unless it is shut down. This is so the database can implement a consistency model among multiple users. If the database were to experience some kind of media failure, you would need to fix that failure and then re-do the transactions involved, using the redo logs that have been archived - that is media recovery. You can't do that in noarchivelog mode, because you didn't keep the logs. So to switch to archived log mode, you have to start over. You can't just say "start archiving now and everything going forward will be archived" unless you finish any outstanding transactions. Which happens to be a shutdown.
Please read the concepts guide about redo architecture, transactions and multi-version consistency control.
Why do you ask?
Thanks a lot EdStevens for ur prompt response...I need something that satisfies my managers query.
If the question has originated with the PHB, then my flippant answer is even more relevant ...
PHB's are incapable of understanding the technical details, so I prefer to give them the 'executive summary':
"Because that's the way Oracle is written. We can argue over the technical merits of such a requirement 'till the cows come home. But at the end of the day, we have to shut down the database because the 'alter database archivelog' command will only succeed when the database is in mount (not open) mode. Now, let's work out how we can achieve our objective of putting the database in archivelog mode with minimal and appropriately scheduled down time."
As for the actual down time, it's about as minimal as you can get. How long does it take your database to complete a shutdown once the command is given? How long does it take you to type "startup mount"? How long does it take you to type "alter database archivelog"? to type "alter database open;"? For the database to complete the 'open' process? You could even script it so as to eliminate the keyboard typing time.
PHB's aren't incapable of understanding. They just hear a small percentage of what you say. The trick is in predicting what tidbits they will pick up on as they relate that percentage to what they think they understand.
Production databases should be in ArchiveLog mode from day 1. I can understand why a manager would ask questions if a DBA needs a shutdown *after* the go-live.
Exceptions to this rule need justification. Thus, NOT setting ArchiveLog mode must be explained and accepted. Setting ArchiveLog is a "given".
Hemant K Chitale