Archive Logs

Add

1. ALTER SYSTEM SET log_archive_dest_n='LOCATION=File_path_and_name' SCOPE=both;
2. Bounce database.

 ALTER SYSTEM SET log_archive_dest_3='LOCATION=X:\oradata\arc' SCOPE=both;
 shutdown immediate
 startup

Display

-- Log Archive Destinations

 show parameter log_archive

-- Show Archived Log Files

 SELECT name,sequence#,status, 
        to_char(completion_time,'YYYY-MM-DD HH24:MI') "Completed"
 FROM v$archived_log
 ORDER BY completion_time DESC;

-- Show Recent Archived Log Files

 COL name FORMAT a75
 SELECT * FROM  (
    SELECT recid, 
       name, to_char(completion_time,'YYYY-MM-DD HH24:MI') "Completed" 
       FROM v$archived_log 
       ORDER BY completion_time DESC
 ) WHERE rownum <= 25;

-- Mode

 SELECT log_mode FROM v$database;

Drop

ALTER SYSTEM SET <log_archive_dest_n to drop>='' SCOPE=both;

 ALTER SYSTEM SET log_archive_dest_2='' SCOPE=both;

Enable/Disable

-- ArchiveLogMode.enable.sql

shutdown immediate
connect / as sysdba
startup mount
alter database archivelog;
alter database open;
select log_mode from v$database;

-- ArchiveLogMode.disable.sql

shutdown immediate
connect / as sysdba
startup mount
alter database noarchivelog;
alter database open;
select log_mode from v$database;

In some environments you may need to connect as shown below after a shutdown immediate:

OS> sqlplus /nolog
SQLPlus> connect sys/MyPassword as sysdba

Format

The name format of archive logs is controlled via the LOG_ARCHIVE_FORMAT parameter.

%t  thread number as part of the file name
%s  log sequence number
%r  to include the resetlogs ID (a timestamp value represented in ub4).

Use capital letters (S, and %R) to pad the file name to the left with zeroes. Example:

{DB_NAME}_%T_%S_%r.arc

Specifying %r causes the database to capture the resetlogs ID in the archive log file name, enabling you to more easily perform recovery from a backup of a previous database incarnation.

Change Format

  ALTER SYSTEM SET log_archive_format='{DB_NAME}_S_%r.arc' SCOPE=spfile;
  shutdown immediate
  startup

Move

ALTER SYSTEM SET log_archive_dest_n='LOCATION=new_file_path' SCOPE=both; 

 ALTER SYSTEM SET log_archive_dest_1='LOCATION=c:\oradata\arc' SCOPE=both;

Copy archive log files from original to new path if required.

<- System