11204 on OEL 6
Hi Guys,
I am trying to mess around with my dataguard configuration.
I have set the db_recovery_file_dest_size to 500M.
I run a huge query to see how the deletion policy kicks in when space pressure occurs.
As per my understanding the space should have got cleared, but it isn't
Here is what is happening. Can you please help me understand better?
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 143
Next log sequence to archive 143
Current log sequence 145
SQL> select * from v$flash_Recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 94.57 0 11
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
SQL> select * from V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------- ----------- ---------- ----------------- ---------------
/u01/app/oracle/fast_recovery_area 524288000 495827456 0 11
col name for a40
col cur_val for a40
set lines 180
select i.ksppinm name , v.ksppstvl cur_val,
v.ksppstdf default_val, v.ksppstvf
from x$ksppi i, x$ksppcv v
where i.indx = v.indx
and i.ksppinm in ('_log_deletion_policy')
6 /
NAME CUR_VAL DEFAULT_V KSPPSTVF
---------------------------------------- ---------------------------------------- --------- ----------
_log_deletion_policy ALL TRUE 4
FROM RMAN on PRIMARY
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name STBY
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
227 1 132 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_132_bt3f71vt_.arc
228 1 133 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_133_bt3f72w2_.arc
229 1 134 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_134_bt3f7475_.arc
233 1 135 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_135_bt3f7cng_.arc
235 1 136 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_136_bt3f7pd8_.arc
237 1 137 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_137_bt3f7y72_.arc
239 1 138 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_138_bt3f8h62_.arc
241 1 139 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_139_bt3f96os_.arc
243 1 140 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_140_bt3fbghz_.arc
245 1 141 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_141_bt3fbs7l_.arc
247 1 142 A 12-JUL-15
Name: /u01/app/oracle/fast_recovery_area/STBY/archivelog/2015_07_12/o1_mf_1_142_bt3fc4p3_.arc
RMAN> show ARCHIVELOG DELETION POLICY;
RMAN configuration parameters for database with db_unique_name STBY are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
RMAN>
And on standby,
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
142
So why are the ARCHIVE files up to sequence 142 not being marked as reclaimable??? Its showing 0 reclaimable in both views.
And yes, the primary DB is hung with the below message.
ORA-19815: WARNING: db_recovery_file_dest_size of 524288000 bytes is 94.57% used, and has 28460544 remaining bytes available.
P.S: can anyone be kind enough the tell me how to post with a formatted output such that the tab spaces are not broken and it makes my post more readable?
TIA
-A