Archivelog

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.

Advantages

1. You can perform hot backups (backups when the database is online).
2. The archive logs and the last full backup (offline or online) or an older backup can completely recover the database without losing any data because all changes made in the database are stored in the log file.

Check status of archivelolg mode

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list

We can enable archivelog mode by setting log_archive_dest or FRA(flash recovery area)

How to enable archivelog mode

mkdir -p /u01/admin/orcl/arch

Perform clean shut down of database.

SQL>SHUT IMMEDIATE

Edit pfile and below parameter

log_archive_dest='/u01/admin/orcl/arch'

Start database in mount state

SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

LOG_MODE
------------
ARCHIVELOG

SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

Enabling FRA - FLASH RECOVERY ARE

The name Recovery Area indicates that this is the place to find everything needed to do a successful recovery in case of a damage (or a logical mistake) in the Database Area.

This posting aims to show how deal with the Recovery Area in order to

a) Protect Controlfiles & Online Logfiles
b) ManageBackups
c) Manage Flashback Logs

Enable FRA

SQL> alter system set db_recovery_file_dest_size=10g scope=both;

SQL> alter system set db_recovery_file_dest='/u01/admin/orcl/fra' scope=both;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2089
Next log sequence to archive 2091
Current log sequence 2091

flashback : This will contain the file (.flb) containing the flashback logs for database.

archivelog: This will conatin the archivelogs generated in OMF format and a new folder will be creates automatically
for daily archives.

Apart from these folders, few more folders will be created depending upon you put backups in FRA or not.

backupset: This will contain the RMAN backup files.

datafile : This will contain the image copies backups.

autobackup: This will contain the controlfile autobackup.

controlfile: This will contain the controlfile, if you put one of the controlfiles in FRA

onlinelogs : This will contain online redo log member, if one member is put in FRA.

-- Utilisation (MB) du FRA

set lines 100

col name format a60

select

   name,

  floor(space_limit / 1024 / 1024) "Size MB",

  ceil(space_used / 1024 / 1024) "Used MB"

from v$recovery_file_dest;

 -- FRA Occupants

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;



-- Location and size of the FRA

show parameter db_recovery_file_dest

-- Size, used, Reclaimable 

SELECT

  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 

  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 

  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

  V$RECOVERY_FILE_DEST A,

  V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

  SPACE_LIMIT, 

  SPACE_USED , 

  SPACE_RECLAIMABLE ;



-- After that you can resize the FRA with:

-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;



-- Or change the FRA to a new location (new archives will be created to this new location):

-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';