Archivelog

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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;

Newsletter

Enter your email to join our mailing list

Quick Contact