It’s a prime responsibility of Oracle DBA to keep database up 24×7 but there may be some chances when your database gets crash. If your database gets crash and you want to recover it using archive log files then your database must be in archive mode. If your database is in archive made then you can recover your database at a large extent.
We have taken Oracle 11g express as an example but the process is same for Oracle 10g and Oracle 11g standard/enterprise.
Note: It is strongly recommended to backup of the database after a normal shutdown prior to changing the archive mode of a database.
Follow the below mentioned process to enable archive log mode in Oracle 10g or 11g:
How to Enable Archive Log mode?
First verify the database archive log mode. Connect your oracle database from command prompt using following command.
C:\>sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 10 13:30:30 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 90 Current log sequence 91
Here you can see the archive log mode is in No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You can check the path by looking at the parameter DB_RECOVERY_FILE_DEST.
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\oraclexe\app\oracle\fast_re
covery_area
db_recovery_file_dest_size big integer 10G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0By default archive logs are written to the flash recovery area. If you do not want to write archive logs to the flash recovery area, use following command to change the archive logs location.
SQL> alter system set db_recovery_file_dest='D:\archivelog' scope=both; System altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 90 Current log sequence 91 SQL> show parameter recover NAME TYPE VALUE ------------------------------------ ----------- -------------- db_recovery_file_dest string D:\test\archivelog db_recovery_file_dest_size big integer 10G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0
Now shutdown the database and bring it in mount mode.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1071769376 bytes Fixed Size 1339352 bytes Variable Size 626295792 bytes Database Buffers 435767616 bytes Redo Buffers 4981616 bytes Database mounted.
Now issue Alter database command to set your database in archive log mode.
SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 90 Next log sequence to archive 91 Current log sequence 91
Here you can see that the database is in archive log mode and automatic archival is enabled as well.
If you want to check whether it is working or not, you can switch to the log file to see that an archive is written to the archive log location.
SQL> alter system switch logfile; System altered. SQL> show parameter recover NAME TYPE VALUE ------------------------------------ ----------- ----------------------------- db_recovery_file_dest string D:\test\archivelog db_recovery_file_dest_size big integer 10G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0
How to Disable Archive Log Mode?
Note: It is strongly recommended to backup of the database after a normal shutdown prior to changing the archive mode of a database.
Follow the below mentioned process to disable archive log mode in Oracle 10g or 11g:
First verify the database archive log mode. Connect your oracle database from command prompt using following command.
C:\>sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 10 13:52:42 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 91 Next log sequence to archive 92 Current log sequence 92
Here you can see the Database is in Archive log mode. Now we have to shut down the database and bring it up in mount stage.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1071769376 bytes Fixed Size 1339352 bytes Variable Size 626295792 bytes Database Buffers 435767616 bytes Redo Buffers 4981616 bytes Database mounted.
Now issue the following command to disable archive log mode and open the database.
SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 91 Current log sequence 92
Here you can see, ARCHIVELOG mode has been disabled. Now i hope you understood how to Enable/Disable Archive Log Mode in Oracle 10g/11g
