Wednesday, August 26, 2009

How to put oracle database in Archive log mode


Archive log and No-Archive log
We have the option of running the database in archive log or no- archive log (default). The difference between archive log and no-archive log is that in no-archive log the online redo log group which are used in cyclic manner and not backup up. In archive log, the redo logs are archived and for each redo log an archive log file is created.
The advantage of archive log is that the backup of database along with archive logs provides the complete recovery of database. Also, hot/online backups are possible only if the database is in archive log mode. However
In no-archive log mode, the database can be recovered only till the last available backup. All the changes done after the backup was taken needs to be redone.
How to put the database in Archive log mode
Make the following optional changes in the init. ora. If these changes are not made then default format and destination will be used by oracle.
log_archive_dest_1='location=/u01/archivelog'
LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc
#log_archive_start=TRUE

Please note that in oracle 10g we don't need to give "log_archive_start=TRUE". It is required only in oracle 9i. Also we can specify upto 10 locations using the parameter "log_archive_dest_n" where n can have following values--1,2,..9. Also instead of local location we can specify a standby database using "service" instead of "location" parameter.
Now shutdown the database and mount it. Now execute the following command->
alter database archivelog;
Now open the database using
alter database open;
Now you can check whether the database is in archive log or not using
archive log list;
Also you can query the following tables->
v$log,v$archived_log

1 comment:

Rutuja Deshmukh said...

Hi
this blog seems to be good one n will try it.
oracle fussion middleware