Unable to Drop ASM DiskGroup Giving ORA-15027

SCENARIO:

When I tried to remove all the contents of a database using ASM I encountered an error from acmca. Even tried with sqlplus and ended up with the same below error.

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15027: active use of diskgroup "DATAGRP1" precludes its dismount

REASON:

• ASM parameter file was residing in the Diskgroup DATAGRP1. This was the reason behind.

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME             TYPE           TOTAL_MB    FREE_MB  REQUIRED_MIRROR_FREE_MB    USABLE_FILE_MB

——————— —————- ———-——— ————–   ————————————————   —————————

DATAGRP1      EXTERN      7168000      7167858                       0                             7167858

FRAGRP1        EXTERN      512000        511222                         0                             511222

SQL> column HEADER_STATUS format a20

SQL> set pages 100

SQL> col PATH format a25

SQL> SELECT name, header_status, path FROM V$ASM_DISK where name like '%DATA%';

NAME             HEADER_STATUS       PATH

——————  ——————————– ————————-

DATA_0001        MEMBER              /dev/rdisk/disk01

DATA_0002        MEMBER              /dev/rdisk/disk02

DATA_0003        MEMBER              /dev/rdisk/disk03

DATA_0004        MEMBER              /dev/rdisk/disk04

DATA_0005        MEMBER              /dev/rdisk/disk05

5 rows selected.

• I tried to drop the diskgroup DATAGRP1 but I couldn't drop it.

SQL> DROP DISKGROUP datagrp1 INCLUDING CONTENTS;

DROP DISKGROUP datagrp INCLUDING CONTENTS

*

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15027: active use of diskgroup "DATAGRP1" precludes its dismount

SQL>

SQL> DROP DISKGROUP datagrp1 FORCE INCLUDING CONTENTS;

DROP DISKGROUP datagrp1 FORCE INCLUDING CONTENTS

*

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15230: diskgroup 'DATAGRP1' does not require the FORCE option

• I created a pfile from spfile and restarted the ASM Instance using this created pfile "init_orcldb.ora".

SQL> create pfile='/usr2/grid/init_asm.ora' from spfile;

File created.

SQL> shutdown immediate;

ASM diskgroups dismounted

ASM instance shutdown

• When I started the ASM Instance the diskgroup did not get mounted.

SQL> startup pfile='/usr2/grid/init_asm.ora';

ASM instance started

Total System Global Area  283930624 bytes

Fixed Size                  2169104 bytes

Variable Size             256595696 bytes

ASM Cache                  25165824 bytes

ORA-15110: no diskgroups mounted

SQL>  DROP DISKGROUP datagrp1 INCLUDING CONTENTS;

DROP DISKGROUP datagrp1 INCLUDING CONTENTS

*

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15001: diskgroup "DATAGRP1" does not exist or is not mounted

• Finally used FORCE option to drop the diskgroup and it's been dropped successfully.

SQL> DROP DISKGROUP datagrp1 FORCE INCLUDING CONTENTS;

Diskgroup dropped.

SQL>

Oracle 11g RMAN VIRTUAL PRIVATE CATALOG

Earlier before 11g, we use to have N number of databases registered with Only one catalog database. So the administration of the whole RECOVERY CATALOG becomes harder to maintain. 

To overcome this Oracle 11g has bought in a new feature called VIRTUAL PRIVATE CATALOG.

One of the New Features in Oracle 11g is the RMAN VIRTUAL PRIVATE CATALOG. 

Using this feature we can grant restricted access to the RMAN CATALOG to some users, so that they can only access a limited number of databases which are registered with the RMAN CATALOG.

In the below scenario, there are two databases namely GGDB1 and GGDB2 registered with one catalog owned by the user RMAN. 

SQL> select * from rman.rc_database;

 DB_KEY    DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
-------------    -----------------   ---------------   --------- ---------------------------------    ------------------
1          2         4150396312 GGDB2        995548    12-JUN-14
     417      418         3580803551 GGDB1        995548    12-JUN-14

Now we will restrict the access to the GGDB1 database to a single user RMAN_GGDB1. In order to proceed with further, firstly we need to create a user called RMAN_GGDB1.


SQL> create user rman_ggdb1 identified by rman_ggdb1
  2  default tablespace TBSRMAN
  3  temporary tablespace temp
  4  quota unlimited on tbsrman 
  5  /

User created.

SQL> grant recovery_catalog_owner to rman_ggdb1;

Grant succeeded.

Connect to catalog as catalog owner and grant permissions on the one database "GGDB1".

[oracle@ogg2 ~]$ rman catalog rman/rman@ggdb2 target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:17:32 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> grant catalog for database GGDB1 to rman_ggdb1;

Grant succeeded.

Now connect as the RMAN_GGDB1 user and create the VIRTUAL PRIVATE CATALOG.

[oracle@ogg2 ~]$ rman target / catalog rman_ggdb1/rman_ggdb1@GGDB2

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:18:24 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN

Connect as a Original RMAN user and issue the command LIST INCARNATION.

[oracle@ogg2 ~]$ rman catalog rman/rman@ggdb2 target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:30:12 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key   DB Name   DB ID            STATUS     Reset SCN    Reset Time
-------       -----------  -------------  --------------     -------------    --------------    -----------------
417          434           GGDB1      3580803551    PARENT            1               17-SEP-11
417          418           GGDB1      3580803551    CURRENT     995548         12-JUN-14
    1            18           GGDB2      4150396312    PARENT            1               17-SEP-11
    1              2           GGDB2      4150396312    CURRENT     995548         12-JUN-14

Connect as the rman_ggdb1 user and check the command LIST INCARNATION.

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:30:36 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key   DB Name   DB ID            STATUS     Reset SCN    Reset Time
-------       -----------  -------------  --------------     -------------    --------------    -----------------
417          434           GGDB1      3580803551    PARENT            1               17-SEP-11
417          418           GGDB1      3580803551    CURRENT     995548         12-JUN-14

From the above output we can clearly see that only one database is registered in this catalog when we connect as RMAN_GGDB1.

Migrate database from one RMAN catalog to another RMAN catalog

IMPORT CATALOG is a new feature of RMAN in Oracle 11g. We can move or merge schemas of different RECOVERY CATALOG of different databases into a centralized repository.

In a nut shell It is nothing but importing a CATALOG from one database to another, or in other words, "moving" or "migrating" CATALOG.

SOURCE DATABASE - GGDB1 

TARGET DATABASE - GGDB2

ORACLE VERSION - 11.2.0.3.0

OS VERSION - Linux 6.0 X86_64

The below scenario describes the steps to move a CATALOG from GGDB1 (Source) to GGDB2 (Target).

In GGDB1 - The catalog is registered.

In GGDB2 - Check the CATALOG for the registered database.

In GGDB2 - Connect to the destination database that will receive the new CATALOG data.

Issue the IMPORT CATALOG command specifying the owner of the source catalog and Check the registered database in the catalog in GGDB2 (Target database).

We can see GGDB1 is also registered to this CATALOG.

In GGDB1 - Check the source catalog (GGDB1) for the registered database.,

On the Source Catalog, the database will be automatically Unregistered after the IMPORT CATALOG.

If you need to retain the catalog on the source side even after the import then a keyword should be added to the IMPORT CATALOG command.

Connect to the RMAN prompt and issue the IMPORT CATALOG command with the keyword NO UNREGISTER.,

Check whether the source database (GGDB1) is registered to the New target catalog database (GGDB2).

Now check on the Source (GGDB1) side whether the imported database exists. 

You can clearly see that the database is not Unregistered from the Source side.

Restoring With RMAN and Using The DATABASE RECOVERY ADVISOR (DRA)

The Database Recovery Advisor (DRA) is a new feature introduced in Oracle 11g to help us to diagnose any persistent failure with the database.
The main advantage of this feature is, it helps us to fix the issue taking away some of the stress always associated with this type of operation.

Database Recovery Advisor is useful only if we use RMAN for our backups.

The main benefits of this feature DRA is below.,

1. It simplifies the diagnosis, analysis and recovery steps we perform.
2. It allows us to easily detect and check the failures happened. 
3. It reduces the recovery time.
4. Reduces Human Errors.
5. It provides us with suggestions, steps and advices to repair the failure occur.
6. We can generate a repair script and recover the failure using the generated script on requirement.

Database Recovery Advisor can be used via OEM or through RMAN command line interface. The below are some of the commands used in RMAN command line interface.

1. LIST FAILURE : List all failures that the database encountered.

2. ADVISE FAILURE : Analyzes all the backups and provide a recovery script to repair the failure occur.

3. CHANGE FAILURE : This command changes the priority or status of the failures.

4. REPAIR FAILURE : Repairs the failure occured with the automatically generated script (which is previously generated by the ADVISE FAILURE command).

As I mentioned earlier, Database Recovery Advisor is useful only if we use RMAN for our backups. I have taken full backups before working on the scenarios.

SCENARIO - Recovery of DATAFILE:

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------



As I have taken the backups before, the RMAN requires no further backup is required.
I have taken the USERS tablespace offline and removed it's datafile as below.,

RMAN> sql 'alter tablespace users offline';

sql statement: alter tablespace users offline
starting full resync of recovery catalog
full resync complete

[oracle@localhost ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     10236     7784                0            7784              0             N  DATA_GRP/
MOUNTED  EXTERN  N         512   4096  1048576     10236     9855                0            9855              0             N  FRA_GRP/

ASMCMD> cd DATA_GRP/PRODDB/DATAFILE

ASMCMD> ls

EXAMPLE.265.848660933
SYSAUX.257.848660835
SYSTEM.256.848660835
TBSRMAN.267.848663633
UNDOTBS1.258.848660835
USERS.259.848660835

ASMCMD> rm USERS.259.848660835

ASMCMD> ls

EXAMPLE.265.848660933
SYSAUX.257.848660835
SYSTEM.256.848660835
TBSRMAN.267.848663633
UNDOTBS1.258.848660835

From the above list we can clearly see that the USERS datafile has been removed.

Now we are going to recover this datafile using the DATABASE RECOVERY ADVISOR.

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
594        HIGH     OPEN      03-JUL-14     Tablespace 4: 'USERS' is offline
588        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are missing
582        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are offline

We can see that the LIST FAILURE command lists all the failures detected by the DRA. We can see the details of the failure by issuing the command LIST FAILURE <failure_id> DETAIL as below,

RMAN> list failure 588 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
588        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 588
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  591        HIGH     OPEN      03-JUL-14     Datafile 4: '+DATA_GRP/proddb/datafile/users.259.848660835' is missing
    Impact: Some objects in tablespace USERS might be unavailable



Now we will issue the ADVISE FAILURE command, so that the DRA suggests / advices to fix all the current failures.

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
588        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 588
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  591        HIGH     OPEN      03-JUL-14     Datafile 4: '+DATA_GRP/proddb/datafile/users.259.848660835' is missing
    Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file +DATA_GRP/proddb/datafile/users.259.848660835 was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm


From the above details, we can clearly see that the ADVISE FAILURE automatically command generated a repair script and stored it in the location /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm

If needed we can use the command REPAIR FAILURE PREVIEW to see the details of the script generated by the ADVISE FAILURE command before we order the DRA to repair the failures.

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;


Now issue the REPAIR FAILURE command to repair the failures.

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 03-JUL-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to +DATA_GRP/proddb/datafile/users.259.848660835
channel ORA_DISK_1: reading from backup piece /rman/bkup_0kpbok52_1_1
channel ORA_DISK_1: piece handle=/rman/bkup_0kpbok52_1_1 tag=TAG20140625T210329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-JUL-14

Starting recover at 03-JUL-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /arch/proddb/1_14_848660904.dbf
archived log for thread 1 with sequence 15 is already on disk as file /arch/proddb/1_15_848660904.dbf
archived log for thread 1 with sequence 16 is already on disk as file /arch/proddb/1_16_848660904.dbf
archived log file name=/arch/proddb/1_14_848660904.dbf thread=1 sequence=14
media recovery complete, elapsed time: 00:00:01
Finished recover at 03-JUL-14
repair failure complete
starting full resync of recovery catalog
full resync complete


From the above, we can clearly see that the USERS datafile is recovered.

ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    EXAMPLE.265.848660933
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    SYSAUX.257.848660835
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    SYSTEM.256.848660835
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    TBSRMAN.267.848663633
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    UNDOTBS1.258.848660835
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    USERS.259.851949575
ASMCMD>

RMAN> sql 'alter tablespace USERS online';

sql statement: alter tablespace USERS online
starting full resync of recovery catalog
full resync complete



SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME TABLESPACE_NAME  STATUS
------------------------------------------------------- -------------------- ---------
+DATA_GRP/proddb/datafile/users.259.851949575 USERS     AVAILABLE
+DATA_GRP/proddb/datafile/undotbs1.258.848660835 UNDOTBS1     AVAILABLE
+DATA_GRP/proddb/datafile/sysaux.257.848660835 SYSAUX     AVAILABLE
+DATA_GRP/proddb/datafile/system.256.848660835 SYSTEM     AVAILABLE
+DATA_GRP/proddb/datafile/example.265.848660933 EXAMPLE     AVAILABLE
+DATA_GRP/proddb/datafile/tbsrman.267.848663633 TBSRMAN     AVAILABLE

6 rows selected.


An important thing to be considered on DRA is, if we are not using the controlfile autobackup option when doing your backups and the recovery of the controlfile is required, the DRA will not be able to do a full automatic recovery process and which leads to a combination of automatic and manual recovery process to recover the failure during this situation.