+RECO disk group filled up on standby server by 78%:
You have received alert mail from OEM saying that
OEM Alert : RECO Disk group is 78% used on oracle@Shamstech server....
[oracle@Shamstech ~]$ . oraenv
ORACLE_SID = [Shams1] ? Shams1
The Oracle base remains unchanged with value /u01/app/oracle
Check if the standby database is in sync with prod:
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#;
THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF
---------- ---------- ----------- --------- ----------
1 10703 10702 04-AUG-20 1
2 12420 12420 04-AUG-20 0
[oracle@Shamstech ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 4 23:02:25 2023
Version 19.14.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: Shams1 (DBID=438985516, not open)
RMAN>crosscheck archivelog all;
Crosschecked 215 objects
RMAN>delete noprompt archivelog until time 'sysdate -1';
......
deleted archived log
RMAN>crosscheck archivelog all;
......
Crosschecked 210 objects
Query to check space check on asm diskgroup:
SQL> select distinct (NAME) "DISKGROUP",round(TOTAL_MB/1024) "Total Size in GB",round(FREE_MB/1024)
"Free Space in GB",round(FREE_MB/TOTAL_MB*100) "Pct Free",round((TOTAL_MB-FREE_MB)/TOTAL_MB*100) "Pct used" from v$ASM_DISKGROUP ;
Before usage:
DISKGROUP Total Size in GB Free Space in GB Pct Free Pct used
------------------------------ ---------------- ---------------- ---------- ---------------------------------------------
RECO 16384 12779.52 10 78
SQL> !hostname
Shamstech.standby.com
After usage:
DISKGROUP Total Size in GB Free Space in GB Pct Free Pct used
------------------------------ ---------------- ---------------- ---------- ---------------------------------------------
RECO 16384 5646 34 66
SQL> !hostname
Shamstech.standby.com
Note1: Here the standby database is in sync with primary and there is no archive gap and we deleted the applied archive logs applied on standby database keeping 1 day logs.
Note2: If the standby database is not in sync with primary and if there is a archive gap and then we have to use RMAN> delete noprompt archivelog until time 'sysdate -3';
it means we are deleting applied archive logs and keeping 2days log
You can use the below options as well
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE =62012;
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2';
RMAN>DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE sbt;
Enjoy the work and do like me if you like this article!!!
