Summary Disk Group FRA is 90.3% used.
rman target /
crosscheck archivelog all;
delete force noprompt archivelog all;
Symptoms:
channel ORA_DISK_1: SID=189 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_02_11/thread_1_seq_62749.16269.967838261 thread=1 sequence=62749
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_02_11/thread_1_seq_62750.16270.967838261 thread=1 sequence=62750
------------------------
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN> **end-of-file**
RMAN> resync catalog from db_unique_name all;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of resync from db_unique_name command at 02/24/2018 04:10:14
RMAN-05037: FOR DB_UNIQUE_NAME option cannot be used in nocatalog mode
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name LCP1DR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 33 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/backup/autobackup/lcp1/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXOPENFILES 1 FORMAT '/localnfs/orabkps/sch/rman/lcp1/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'standby_lcp1' CONNECT IDENTIFIER 'standby_lcp1';
CONFIGURE DB_UNIQUE_NAME 'lcp1' CONNECT IDENTIFIER 'lcp1evt';
CONFIGURE DB_UNIQUE_NAME 'lcp1dr' CONNECT IDENTIFIER 'lcp1bv';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oracle/gls/11.2.0.4/dbs/snapcf_lcp1dr.f'; # default
RMAN>
RMAN> show all;
RMAN configuration parameters for database with db_unique_name UC4AMPRD2DR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/backup/autobackup/uc4amprd2/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXOPENFILES 1 FORMAT '/localnfs/orabkps/sch/rman/uc4amprd2/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'uc4amprd2' CONNECT IDENTIFIER 'uc4amprd2evt';
CONFIGURE DB_UNIQUE_NAME 'uc4amprd2dr' CONNECT IDENTIFIER 'uc4amprd2bv';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oracle/product/11.2.0.4/uc4amprd2/dbs/snapcf_uc4amprd2dr.f'; # default
-------------------------
Tried 1) CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
AFTER above---, got below warnings
MAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_11_10/thread_1_seq_55079.8203.959645895 thread=1 sequence=55079
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_11_10/thread_1_seq_55080.8229.959646789 thread=1 sequence=55080
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_11_10/thread_1_seq_55081.8232.959646789 thread=1 sequence=55081
------------------------
Tried 2) CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
Warning still the same
RMAN> delete noprompt archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_08_02/thread_1_seq_47138.804.950998263 thread=1 sequence=47138
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_08_02/thread_1_seq_47139.1644.950999769 thread=1 sequence=47139
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_08_02/thread_1_seq_47140.396.951001213 thread=1 sequence=47140
-----------------
In this case FRA of standby database was not applying the archive logs due to log difference.
Ideally we should recreate the standby or roll forward the standby.
But if not then, we can follow below steps to clear the archive logs and free up FRA space.
1) Follow-How To Delete Archive Log Files Out Of +Asm? (Doc ID 300472.1)
1. Run the following SQL to find the full path for the archivelog files.
SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc;
2. When the file is created by Oracle the format in +ASM is:
DISKGROUP_NAME/db_name/file_type/creation_date/<file_name>.
This SQL will generate the SQL necessary to delete all archivelogs out of +ASM.
Note: Change the <diskgroup> and <dbname> to the actual values from what is returned from previous SQL output.
select 'alter diskgroup DSKGRP1 drop file
''<diskgroup>/<dbname>/ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';'
from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;
as sys--
select 'alter diskgroup FRA drop file
''+FRA/UC4AMPRD2DR/ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';' from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;
spool it and run the spool as sys user, it would generate similar file like below and run it,
It would drop all the archive logs and would clear up FRA, it went down by 60%.
alter diskgroup DSKGRP1 drop file
'+FRA/uc4amprd2dr/archivelog/2018_03_01/thread_1_seq_66425.17646.969614389';
15941 rows selected.
------------------------
After this current situation:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
Tried below and worked :
rman target /
crosscheck archivelog all;
RMAN> delete noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64380.16457.969688449 thread=1 sequence=64380
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64381.16463.969690371 thread=1 sequence=64381
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64382.16375.969690371 thread=1 sequence=64382
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64383.16504.969692507 thread=1 sequence=64383
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64384.16502.969692509 thread=1 sequence=64384
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64385.16395.969695381 thread=1 sequence=64385
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64386.16141.969695381 thread=1 sequence=64386
----
USE force option
RMAN> delete force noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
List of Archived Log Copies for database with db_unique_name UC4AMPRD2DR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
53243 1 64380 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64380.16457.969688449
53244 1 64381 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64381.16463.969690371
53245 1 64382 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64382.16375.969690371
53246 1 64383 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64383.16504.969692507
53247 1 64384 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64384.16502.969692509
53248 1 64385 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64385.16395.969695381
53249 1 64386 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64386.16141.969695381
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64380.16457.969688449 RECID=53243 STAMP=969689283
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64381.16463.969690371 RECID=53244 STAMP=969690370
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64382.16375.969690371 RECID=53245 STAMP=969691502
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64383.16504.969692507 RECID=53246 STAMP=969692508
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64384.16502.969692509 RECID=53247 STAMP=969693744
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64385.16395.969695381 RECID=53248 STAMP=969695380
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64386.16141.969695381 RECID=53249 STAMP=969696421
Deleted 7 objects
RMAN>
rman target /
crosscheck archivelog all;
delete force noprompt archivelog all;
Symptoms:
RMAN> delete noprompt archivelog all;
allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=189 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_02_11/thread_1_seq_62749.16269.967838261 thread=1 sequence=62749
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_02_11/thread_1_seq_62750.16270.967838261 thread=1 sequence=62750
------------------------
RMAN> @/dba/rman/uc4amprd2dr_cfg.cmd
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED
ON ALL STANDBY;
old RMAN configuration parameters:CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN> **end-of-file**
RMAN> resync catalog from db_unique_name all;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of resync from db_unique_name command at 02/24/2018 04:10:14
RMAN-05037: FOR DB_UNIQUE_NAME option cannot be used in nocatalog mode
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name LCP1DR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 33 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/backup/autobackup/lcp1/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXOPENFILES 1 FORMAT '/localnfs/orabkps/sch/rman/lcp1/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'standby_lcp1' CONNECT IDENTIFIER 'standby_lcp1';
CONFIGURE DB_UNIQUE_NAME 'lcp1' CONNECT IDENTIFIER 'lcp1evt';
CONFIGURE DB_UNIQUE_NAME 'lcp1dr' CONNECT IDENTIFIER 'lcp1bv';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oracle/gls/11.2.0.4/dbs/snapcf_lcp1dr.f'; # default
RMAN>
RMAN> show all;
RMAN configuration parameters for database with db_unique_name UC4AMPRD2DR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/backup/autobackup/uc4amprd2/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXOPENFILES 1 FORMAT '/localnfs/orabkps/sch/rman/uc4amprd2/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'uc4amprd2' CONNECT IDENTIFIER 'uc4amprd2evt';
CONFIGURE DB_UNIQUE_NAME 'uc4amprd2dr' CONNECT IDENTIFIER 'uc4amprd2bv';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oracle/product/11.2.0.4/uc4amprd2/dbs/snapcf_uc4amprd2dr.f'; # default
-------------------------
Tried 1) CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
AFTER above---, got below warnings
MAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_11_10/thread_1_seq_55079.8203.959645895 thread=1 sequence=55079
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_11_10/thread_1_seq_55080.8229.959646789 thread=1 sequence=55080
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_11_10/thread_1_seq_55081.8232.959646789 thread=1 sequence=55081
------------------------
Tried 2) CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
Warning still the same
RMAN> delete noprompt archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_08_02/thread_1_seq_47138.804.950998263 thread=1 sequence=47138
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_08_02/thread_1_seq_47139.1644.950999769 thread=1 sequence=47139
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2017_08_02/thread_1_seq_47140.396.951001213 thread=1 sequence=47140
-----------------
In this case FRA of standby database was not applying the archive logs due to log difference.
Ideally we should recreate the standby or roll forward the standby.
But if not then, we can follow below steps to clear the archive logs and free up FRA space.
1) Follow-How To Delete Archive Log Files Out Of +Asm? (Doc ID 300472.1)
1. Run the following SQL to find the full path for the archivelog files.
SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc;
2. When the file is created by Oracle the format in +ASM is:
DISKGROUP_NAME/db_name/file_type/creation_date/<file_name>.
This SQL will generate the SQL necessary to delete all archivelogs out of +ASM.
Note: Change the <diskgroup> and <dbname> to the actual values from what is returned from previous SQL output.
select 'alter diskgroup DSKGRP1 drop file
''<diskgroup>/<dbname>/ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';'
from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;
as sys--
select 'alter diskgroup FRA drop file
''+FRA/UC4AMPRD2DR/ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';' from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;
spool it and run the spool as sys user, it would generate similar file like below and run it,
It would drop all the archive logs and would clear up FRA, it went down by 60%.
alter diskgroup DSKGRP1 drop file
'+FRA/uc4amprd2dr/archivelog/2018_03_01/thread_1_seq_66425.17646.969614389';
15941 rows selected.
------------------------
After this current situation:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
Tried below and worked :
rman target /
crosscheck archivelog all;
RMAN> delete noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64380.16457.969688449 thread=1 sequence=64380
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64381.16463.969690371 thread=1 sequence=64381
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64382.16375.969690371 thread=1 sequence=64382
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64383.16504.969692507 thread=1 sequence=64383
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64384.16502.969692509 thread=1 sequence=64384
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64385.16395.969695381 thread=1 sequence=64385
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64386.16141.969695381 thread=1 sequence=64386
----
USE force option
RMAN> delete force noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
List of Archived Log Copies for database with db_unique_name UC4AMPRD2DR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
53243 1 64380 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64380.16457.969688449
53244 1 64381 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64381.16463.969690371
53245 1 64382 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64382.16375.969690371
53246 1 64383 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64383.16504.969692507
53247 1 64384 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64384.16502.969692509
53248 1 64385 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64385.16395.969695381
53249 1 64386 A 02-MAR-18
Name: +FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64386.16141.969695381
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64380.16457.969688449 RECID=53243 STAMP=969689283
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64381.16463.969690371 RECID=53244 STAMP=969690370
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64382.16375.969690371 RECID=53245 STAMP=969691502
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64383.16504.969692507 RECID=53246 STAMP=969692508
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64384.16502.969692509 RECID=53247 STAMP=969693744
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64385.16395.969695381 RECID=53248 STAMP=969695380
deleted archived log
archived log file name=+FRA/uc4amprd2dr/archivelog/2018_03_02/thread_1_seq_64386.16141.969695381 RECID=53249 STAMP=969696421
Deleted 7 objects
RMAN>
