<<Back to DB Administration Main Page
How to Enable Archive Log Mode in Oracle Database
Check if the Archivelog mode is enabled
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination ?#/dbs/arch
Oldest online log sequence 4146
Current log sequence 4148
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
Enable Archive log mode using Flash Recovery Area
How to Enable Flash Recovery Area
To enable Flash Recovery Area you need to set db_recovery_file_dest_size and db_recovery_file_dest parameter.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> alter system set db_recovery_file_dest_size=50G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest='+DG_TST_FRA' scope=both;
System altered.
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DG_TST_FRA
db_recovery_file_dest_size big integer 50G
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4146
Current log sequence 4148
NOTE:As soon as you enable the FRA the archiving location is implicitly set to FRA even if the Archivelog mode is not enabled
Put the database in mount mode and enable the achivelog mode
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 1912605984 bytes
Database Buffers 218103808 bytes
Redo Buffers 7979008 bytes
Database mounted.
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 4146
Next log sequence to archive 4148
Current log sequence 4148
If you don't want to use FRA you can instruct oracle to write the logs on some other location by setting log_archive_dest_ parameter.
SQL> alter system set log_archive_dest_1='location=/u01/dbatst1/stage/archive/';
System altered.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/dbatst1/stage/ar
chive/
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/dbatst1/stage/archive/
Oldest online log sequence 4147
Next log sequence to archive 4149
Current log sequence 4149
Very clear steps to enable archivelog mode in Oracle. Thanks for sharing.
ReplyDelete