ORA-15028: ASM file '<datafile>' not dropped; currently being accessed

From: Sameer Choudhari <sameer.choudhari_at_ezidebit.com.au>
Date: Thu, 28 Nov 2019 11:04:44 +1000
Message-ID: <CAMzOyFE-XEJt=T9+80ZADfsKgaz4ZNpys0KTXcLV1aUMmsBefg_at_mail.gmail.com>

Hello

*Setup :*

Primary DB                           : EZPRDEBS1 ,  EZPRDEBS2 ,  EZPRDEBS3

Standby DB (single instance) : PRDEBSDR
Second Standby DB (single instance) : EZPRDEBSDR

[oraprod_at_prd-db-601 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Nov 28 08:48:18 2019

connected to target database: EZPRDEBS (DBID=2217119773)

RMAN> show all;

using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name EZPRDEBS are: CONFIGURE RETENTION POLICY TO REDUNDANCY 7; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 16;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO 32 G;
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+HYB_FRA/EZPRDEBS/snapcf_EZPRDEBS.f';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+HYB_FRA/EZPRDEBS/snapcf_ezprdebs.f';

RMAN> exit

===================Standby PRDEBSDR====================================

RMAN> show all;

using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name PRDEBSDR are: CONFIGURE RETENTION POLICY TO REDUNDANCY 7; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 48;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO 16 G;
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+DATA/EZPRDEBSDR/snapcf_EZPRDEBSDR.f';

RMAN> =============STANDBY

EZPRDEBSDR===============================================

RMAN> show all;

RMAN configuration parameters for database with db_unique_name EZPRDEBSDR are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 48;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO 16 G;
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+DATA/EZPRDEBSDR/snapcf_EZPRDEBSDR.f';

RMAN> Set up below cronjob to delete archive log on a daily basis on both standby server.

RMAN command "delete archivelog all completed before 'sysdate -3';" .

While deleting archivelog from either of the standby DB's for past 3 days (delete archivelog all completed before 'sysdate -3') throws below error

PSDRPC returns significant error 1013.
PSDRPC returns significant error 1013.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of delete command on ORA_DISK_30 channel at 11/28/2019 09:04:08
ORA-15028: ASM file
'+DATA/EZPRDEBSDR/ARCHIVELOG/2019_11_23/thread_2_seq_260708.767.1025066629'
not dropped; currently being accessed

Recovery Manager complete.

Upon checking the standby database for this thread thread_2_seq_260708.767.1025066629' it shows its already applied on 23rd Nov and completed, also for the other standby this thread does not exist anymore (probably this got deleted)

DR-DB-801:

SQL> select NAME,APPLIED,STATUS,COMPLETION_TIME from v$archived_log where name like '%thread_2_seq_260708.767.1025066629%';

NAME

APPLIED S COMPLETIO
--------- - ---------
+DATA/EZPRDEBSDR/ARCHIVELOG/2019_11_23/thread_2_seq_260708.767.1025066629 YES A 23-NOV-19

Second Standby: dr-db-803

SQL> select name from v$archived_log where name like '%thread_2_seq_260708.767.1025066629%';

no rows selected

Did below to resolve but no luck

ASMCMD LSOF does not list any files that are currently being accessed.
Tried restarting ASM instance

Cheers,
Sameer

-- 
This is an email from Ezidebit Pty Ltd. The email and any attachments may 
be confidential, legally privileged and/or subject to copyright. If you are 
not the intended recipient, you must not disclose or use the information 
contained in this e-mail. If you have received this email in error, please 
notify us immediately and delete the email and all copies. We do not 
guarantee that this email and any attachments are free from virus or other 
errors. We will not be responsible for loss or damage resulting (either 
directly or indirectly) from any such virus or error. We will only be 
responsible for a change to a document if we made the change. The content 
of and opinions expressed in non-business emails are not that of Ezidebit 
Pty Ltd and are that of the sender.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 28 2019 - 02:04:44 CET

This message: [ Message body ]
Next message: Sameer Choudhari: "Re: ORA-15028: ASM file '<datafile>' not dropped; currently being accessed"
Previous message: Mladen Gogala: "Re: Database Appliance"
Next in thread: Sameer Choudhari: "Re: ORA-15028: ASM file '<datafile>' not dropped; currently being accessed"
Reply: Sameer Choudhari: "Re: ORA-15028: ASM file '<datafile>' not dropped; currently being accessed"
Reply: Sameer Choudhari: "Re: ORA-15028: ASM file '<datafile>' not dropped; currently being accessed"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message