Info, path
SQL> show parameter LOG_ARCHIVE_DES;
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ORCL/ARH
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
How much size generated per day by archivelogs
SQL> select to_char(first_time, 'dd-mm-yyyy') datum, round(sum(blocks*block_size)/1024/1024/1024,2) "Gb Archives" from v$archived_log where first_time> trunc(sysdate)-10 group by to_char(first_time, 'dd-mm-yyyy') order by 1;
DATUM Gb Archives
---------------------------- -----------
11-06-2016 28.87
12-06-2016 11.61
13-06-2016 37.79
14-06-2016 36.72
15-06-2016 40.93
16-06-2016 43.18
17-06-2016 40.16
18-06-2016 98.99
19-06-2016 27.79
20-06-2016 38.99
21-06-2016 14.37
Show DB mode
SQL> select log_mode from v$database;
Switch on archivelog
SQL>
shutdown immediate;
startup mount exclusive;
alter database archivelog;
alter database open;
select log_mode from v$database;
SQL> select log_mode from v$database;
When ARCHIVELOG mode is enabled, after redo-log switches, the copy is archiving and saving on the disk. It gives us to forward database to the time in the past.
Also we can create copies of the database without stop it, but for this mode we need to use more server resources. By the default this option is disabled.
Switch off archivelog
SQL>
shutdown immediate;
startup mount exclusive;
alter database noarchivelog;
alter database open;
select log_mode from v$database;
Archivelog deletion policy
RMAN> show archivelog deletion policy;
RMAN> show all;
SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG';
SQL> select open_mode,database_role from v$database;
SQL> show parameter log_archive_dest_1
RMAN> configure archivelog deletion policy to backed up 2 times to disk;
RMAN> configure archivelog deletion policy to none;
RMAN> configure archivelog deletion policy to applied on standby;
RMAN> configure archivelog deletion policy to applied on all standby;
RMAN> configure archivelog deletion policy to shipped on standby;
RMAN> configure archivelog deletion policy to shipped on all standby;
Which archived logs are reclaimable
SQL>
select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
,count(*),min(sequence#),max(sequence#)
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES' and name is not null
group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/
APPLIED DELETED RECLAIMABLE COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES NO YES 429 5938 6366
YES NO NO 37 6367 6403
IN-MEMORY NO NO 1 6404 6404
All applied archived logs are reclaimable and the FRA will never be full
You can check the primary as well. Are you sure that Oracle will never delete an archived log that has not been backed up ? Check your deletion policy
Here is the full query I use for that:
SQL>
column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
,decode(rectype,11,'YES','NO') reclaimable,count(*)
,to_char(min(completion_time),'dd-mon hh24:mi') first_time
,to_char(max(completion_time),'dd-mon hh24:mi') last_time
,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/
APPLIED DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME LAST_TIME FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
NO YES 1 NO 277 15-jan 17:56 19-jan 09:49 5936 6212
NO NO 1 YES 339 19-jan 10:09 22-jan 21:07 6213 6516
NO NO 0 NO 33 22-jan 21:27 23-jan 07:57
APPLIED DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME LAST_TIME FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
YES YES 0 NO 746 07-jan 13:27 17-jan 11:17 5320 6065
YES NO 0 YES 477 17-jan 11:37 23-jan 05:37 6066 6542
YES NO 0 NO 8 23-jan 05:57 23-jan 08:14 6543 6550
IN-MEMORY NO 0 NO 1 23-jan 08:15 23-jan 08:15
Multiplexing
SQL> HOST mkdir -p /u02/oracle/oradata/${ORACLE_SID}/archives
SQL> HOST mkdir -p /u03/oracle/oradata/${ORACLE_SID}/archives
Set volumes for archivelogs
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=/u02/oracle/oradata/orcl/archives mandatory' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='location=/u03/oracle/oradata/orcl/archives mandatory' scope=both;
SQL> show parameter LOG_ARCHIVE_DEST;
Changing the archivelog files format
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%R.log
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
%R resetlogs ID, zero filled
%d Database ID
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%R.arc' scope=spfile;