OracleDB‎ > ‎

Archivelog

to see if a database is in archive log mode:
 SQL> ARCHIVE LOG LIST
 Database log mode               Archive Mode
 Automatic archival              Enabled
 Archive destination             USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence      825
 Next log sequence to archive    827
 Current log sequence            827
 SQL>
in this example the location of the archive log files is the flash recovery area because flash recovery area has been setup and no LOG_ARCHIVE_DEST_n destination is specified expelicitely. One can explicitly set log_archive_dest_n to a certain destination and set log_archive_format to a certain format to customize where and using what naming convention the archivelogs are saved.


put database in archive log mode (simple)
 ALTER SYSTEM set LOG_ARCHIVE_DEST = '/dbarchlogs' scope=spfile;
 SHUTDOWN IMMEDIATE
 STARTUP MOUNT
 alter database archivelog;
 alter database open;

If Automatic archival is set to disable, run the following then restart Oracle.
 ALTER SYSTEM SET log_archive_start=true SCOPE=spfile;

Note that for manual live archiving run the following. This will archive logs once.
 Alter system archive log all;

To deleted expired archive logs
 rman target /
 crosscheck archivelog all;
 delete expired archivelog all;

Lookup the location of archivelogs
 SQL> show parameter log_archive_dest_1;


The following is a purge archive log script that can be run from cron weekly to clear space:
 #!/bin/bash
 
 source ~oracle/.oracle_profile
 
 cat <<EOF | rman target / nocatalog
 run {
  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-8' BACKED UP 1 TIMES TO DEVICE TYPE 'SBT';
  DELETE NOPROMPT BACKUP OF CONTROLFILE COMPLETED BEFORE 'SYSDATE-8';
 }
 EOF



If you decide to turn off archiving for some reason, you can do so by using the ALTER DATABASE NOARCHIVELOG command, as shown in the following extract, after first starting up with the
STARTUP
MOUNT command:

     SQL> ALTER DATABASE NOARCHIVELOG;
     Database altered.
     SQL> archive log list
     Database log mode                    No Archive Mode
     Automatic archival                   Disabled
     Archive destination                  /u02/app/oracle/oradata/nina/arch/
     Oldest online log sequence           47
     Current log sequence                 48
     SQL>


To force archiving the current redo logs
 alter system archive log current;


Enable automatic archiving (restart of db required)
 ALTER SYSTEM SET log_archive_start=true SCOPE=spfile;


Emergency archivelog backup to tape and deletion.
 $ rman target /
In rman run
 {
  allocate channel nvrest1 type 'sbt_tape';
  allocate channel nvrest2 type 'sbt_tape';
  allocate channel nvrest3 type 'sbt_tape';
  allocate channel nvrest4 type 'sbt_tape';
  allocate channel nvrest5 type 'sbt_tape';
  allocate channel nvrest6 type 'sbt_tape';
  allocate channel nvrest7 type 'sbt_tape';
  allocate channel nvrest8 type 'sbt_tape';
  allocate channel nvrest9 type 'sbt_tape';
  backup archivelog all completed before 'sysdate-2/24';
 }

The delete the logs that were backed up.
 crosscheck archivelog all;
 delete archivelog all backed up 1 times to device type sbt completed before 'sysdate-2/24';


Comments