How-to: ARCHIVELOG mode

Any Oracle database that contains important data should be running in ARCHIVELOG mode. Running in Archive log mode enables you to take hot backups and perform point-in-time recovery.

Alter the database to run in Archivelog mode: Standard edition, single node instance:

ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest='/u01/oradata/MYSID/archive/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
alter system switch logfile;

Alter the database to run in Archivelog mode: Enterprise edition, single node instance

ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
alter system switch logfile;

Alter the database to run in Archivelog mode: Enterprise edition, multiple node instance - Real Application Clusters (RAC)

ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/oradata/MYSID/archive/ MANDATORY' SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_2='LOCATION=/raid2/oracle/admin/MYSID/arch OPTIONAL' scope=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;
alter system switch logfile;

-- First take down the cluster
ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;
srvctl stop database -d MYSID

STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;

--startup the cluster again
ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE;
srvctl start database -d MYSID

In all the commands above, adjust the path and database SID (shown in bold) to match your environment.

The following variables can be used in log_archive_format:

%s Log sequence number
%t Thread number
%a Activation ID
%d Database ID
%r Resetlogs ID - will ensure unique names are constructed for the archived log files across multiple incarnations of the database.

Using uppercase letters (for example, %S) will make the value fixed length and padded to the left with zeros: 00064

NOARCHIVELOG

There are times when Archive log mode is not needed, for example a development environment is unlikely to need Archivelog mode - you are more likely to be concerned with backing up code than backing up the data.

Be aware that if a disk failure occurs while in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup.

To switch back from archivelog to noarchivelog mode, just repeat the relevant steps above specifying: ALTER DATABASE NOARCHIVELOG;

Related

select log_mode from v$database; - Display the current Archive log mode