• Arun Kumar

Oracle Archivelog Mode

Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode.

Checking Archivelog Mode

Use below command to check the archivelog mode inside the oracle database

SQL> archive log list;

You can also use below command


Set Archivelog Destination

You must set a destination for archivelog files

SQL> alter system set log_archive_dest_1='location=/u01/proddb/arch'

Enable Archivelog Mode

Please note that in order to enable archivelog mode, you must bounce the database

SQL> Shut immediate;
SQL> Startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

Disable Archivelog Mode

The database must bounced even when you want to disable archivelog mode

SQL> shut immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list;

Performing Log Switch

While your database is running in archivelog mode, you can perform force log switch. This will archive the current redo log file and force LGWR to start over-writing other redo log member

SQL> alter system switch logfile;

Enable Archivelog Mode in RAC

Let su check the db_recovery_file_dest_size parameter and add space to it

SQL> show parameter recovery;
SQL> alter system set db_recovery_file_dest_size = '20G' scope=both sid='*';
  • If DB_RECOVERY_FILE_DEST is set to disk group, LOG_ARCHIVE_FORMAT is ignored

  • If DB_RECOVERY_FILE_DEST is set to disk group location, LOG_ARCHIVE_FORMAT comes in effect

On Node 1

ALTER SYSTEM SET log_archive_dest_1='location=+FRA/RAC/ARCH/' SCOPE=spfile;

ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

./srvctl stop database -d RAC

sqlplus / as sysdba
startup mount;
alter database archivelog;
alter database open;
select log_mode from v$database;

Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis