How to Set archive mode and non-archive mode for Oracle databases

OracleIn database operations, the database can be setArchive ModeAndNon-archive Mode. Archive mode stores all transaction logs, including redolog and archivelog, but not archive mode only records redolog. We often set it to archive mode and non-archive mode based on the needs of our work. In this article, we will introduce their setup process. Let's take a look at it.

-View the oracle archiving Mode

 
 

  
  
SQL> conn evan/evan (dba)
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> archive log list
  
  
 
  
  
ORA-01031: insufficient privileges
  
  
 
  
  
SQL> conn/as sysdba -- archive log list must be executed by sysdba
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> archive log list
  
  
 
  
  
Database log mode No Archive Mode
  
  
 
  
  
Automatic archival Disabled
  
  
 
  
  
Archive destination USE_DB_RECOVERY_FILE_DEST
  
  
 
  
  
Oldest online log sequence 2
  
  
 
  
  
Current log sequence 4
  
  
 
  
  
Query v $ database
  
  
 
  
  
SQL> select name, log_mode from v $ database;
  
  
 
  
  
NAME LOG_MODE
  
  
 
  
  
---------------------
  
  
 
  
  
ORALIFE NOARCHIVELOG
 
 

Ii. Modify the archive Mode

Archive log location. Oracle 10 Gb can generate multiple identical logs and store multiple locations to prevent accidents.

 
 

  
  
SQL> alter system set log_archive_dest_1 = 'location =/oracle/10g/oracle/log/archive_log ';
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> alter system set log_archive_dest_2 = 'location =/oracle/10g/oracle/log/archive_log2 ';
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> shutdown immediate
  
  
 
  
  
ORA-01031: insufficient privileges
  
  
 
  
  
SQL> conn/as sysdba
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> shutdown immediate
  
  
 
  
  
Database closed.
  
  
 
  
  
Database dismounted.
  
  
 
  
  
ORACLE instance shut down.
  
  
 
  
  
SQL> startup mount
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 163578104 bytes
  
  
 
  
  
Database Buffers 356515840 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
  
  
 
  
  
SQL> alter database archivelog; -- sets the archive mode.
  
  
 
  
  
Database altered.
  
  
 
  
  
SQL> alter database open;
  
  
 
  
  
Database altered.
 
 

Configure the archive file format (starting from oracle 10 Gb, it must contain % s, % t, % r)

 
 

  
  
SQL> alter system set log_archive_format = "archive _ % t _ % s _ % r. arclog" scope = spfile;
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> shutdown immediate
  
  
 
  
  
Database closed.
  
  
 
  
  
Database dismounted.
  
  
 
  
  
ORACLE instance shut down.
  
  
 
  
  
SQL> startup mount
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 163578104 bytes
  
  
 
  
  
Database Buffers 356515840 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
  
  
 
  
  
SQL> archive log list -- check whether archive exists
  
  
 
  
  
Database log mode Archive Mode
  
  
 
  
  
Automatic archival Enabled -- Automatic archiving Enabled
  
  
 
  
  
Archive destination/oracle/10g/oracle/log/archive_log2
  
  
 
  
  
Oldest online log sequence 2
  
  
 
  
  
Next log sequence to archive 4
  
  
 
  
  
Current log sequence 4
  
  
 
  
  
SQL> select destination from v $ archive_dest; -- view the location of the archived log
  
  
 
  
  
DESTINATION
  
  
 
  
  
--------------------------------------------------------------------------------
  
  
 
  
  
/Oracle/10g/oracle/log/archive_log
  
  
 
  
  
/Oracle/10g/oracle/log/archive_log2
  
  
 
  
  
10 rows selected.
 
 

You can also configure the number of archiving processes

 
 

  
  
alter system set log_archive_max_processes=n 
 
 

3. Change to non-archive Mode

 
 

  
  
SQL> startup mount
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 167772408 bytes
  
  
 
  
  
Database Buffers 352321536 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
  
  
 
  
  
SQL> alter database noarchivelog;
  
  
 
  
  
Database altered.
  
  
 
  
  
SQL> alter system set log_archive_dest_1 = '';
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> alter system set log_archive_dest_2 = '';
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> alter system set log_archive_dest_10 = 'location = USE_DB_RECOVERY_FILE_DEST '; -- restore to original
  
  
 
  
  
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 6
  
  
 
  
  
Current log sequence 8
  
  
 
  
  
SQL> shutdown immediate
  
  
 
  
  
ORA-01109: database not open
  
  
 
  
  
Database dismounted.
  
  
 
  
  
ORACLE instance shut down.
  
  
 
  
  
SQL> startup mount
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 167772408 bytes
  
  
 
  
  
Database Buffers 352321536 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
  
  
 
  
  
SQL> archive log list
  
  
 
  
  
Database log mode No Archive Mode
  
  
 
  
  
Automatic archival Disabled
  
  
 
  
  
Archive destination USE_DB_RECOVERY_FILE_DEST
  
  
 
  
  
Oldest online log sequence 6
  
  
 
  
  
Current log sequence 8
 
 

There are so many settings for Oracle Database archive mode and non-archive mode. If you want to know