Reclaim Archive Dest Space from Physical standby.

 

Step1: Validate the timestamp of the last applied archive on physical standby.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

Step 2: Based on last applied time stamp remove archives from the filesystem or ASM.

a.) Check archive dest location.

select dest_name, status, destination from v$archive_dest;

or 

Archive Log List;

b.)  Use either of the commands to delete archive logs.

delete archivelog until time 'SYSDATE-8/24'; 

 or

delete archivelog until time 'SYSDATE-30/48';

or 

delete archivelog until time 'SYSDATE-1'; 

or

delete force archivelog until time 'sysdate-8/24';

Step 3: Validate size of the archive dest.

df -h /u01/oracle/database/archive

or

SELECT name,free_mb/1024 as Free_GB, total_mb/1024,(total_mb - free_mb)/1024 used_gb,ROUND((1- (free_mb / total_mb))*100, 2)  pct_used FROM v$asm_diskgroup where name='&DISKGROUP_NAME';

or (use below sql incase archive destination is pointing to Recovery area.)

column name for a10

select name,SPACE_LIMIT/1024/1024/1024 as "LIMIT",space_used/1024/1024/1024 as "USED" from v$recovery_file_dest;

Step4: (Optional) incase above steps do not resolve then delete archives from ASM disk group or filesystem.

find *.arc -mtime +15 -exec rm {} \;  

(reduce or increase -mtime based on number of days you wish to ignore while deleting files.

or

ASM>cd +FRA/archive

ASM>ls

ASM> 2019-08-02  2019-08-03  2019-08-04

ASM> cd 2019-08-04

ASM> rm *

Step5: Validate physical standby status.

select process,status from v$managed_standby; (check for MRP process)

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;