This blog will let you know, if we have some data on diskgroup and we drop that disk group, will it be possible to drop the disk containing data? and should it be possible to recover the same data by configure the diskgroup with the same name and same disks, should we get our data back?
No its not possible!!
-- Checking the database, its up and running
[oracle@testdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 1 11:14:44 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
-- Log in from Grid user
[grid@testdb ~]$ 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 NORMAL N 512 4096 1048576 10228 10052 0 5026 0 N ARCH/
MOUNTED NORMAL N 512 4096 1048576 10228 7036 0 3518 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 10228 9620 0 4810 0 N FRA/
ASMCMD>
-- checking the data in FRA disk group
Below folders and files are already moved to other Disk group (https://tauseefdba.blogspot.com/2018/01/moving-controlfile-in-asm-from-one.html):
ASMCMD> cd fra
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y TESTDB/
ASMCMD> cd testdb
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y CONTROLFILE/
ASMCMD>
-- Before dropping lets check that no DB parameter is pointing to this DG
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/testdb/controlfile/curre
nt.260.966829689, +ARCH/testdb
/controlfile/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +ARCH
db_recovery_file_dest_size big integer 3852M
recovery_parallelism integer 0
SQL> show parameter arch
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=+ARCH
log_archive_dest_10 string
-- To check any DB file is on this DG
select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking
union
select name from v$flashback_database_logfile;
-- Now its the time to drop Disk group
We can drop disks by 2 ways:
1) login through sysasm in grid user
[grid@testdb bin]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 1 11:42:17 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
SQL>
SQL>
SQL>
SQL> drop diskgroup FRA;
drop diskgroup FRA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "FRA" contains existing files
SQL> drop diskgroup FRA including contents;
Diskgroup dropped.
SQL>
-- Run below command to check the Disk Group is removed properly
login from Grid user:
cd /u01/app/11.2.0/grid/bin
[grid@testdb bin]$ ./srvctl config database -d testdb
Database unique name: testdb
Database name: testdb
Oracle home: /u01/app/oracle/product/11.2.0
Oracle user: grid
Spfile: +DATA/testdb/spfiletestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
PRCD-1012 : Failed to retrieve disk group list for database testdb.
PRCR-1035 : Failed to look up CRS resource ora.FRA.dg for testdb
PRCR-1001 : Resource ora.FRA.dg does not exist
-- Still we have FRA status not update we can do it as below
login from root user as it requires super user permissions:
[root@testdb ~]# cd /u01/app/11.2.0/grid/bin/
- Updated DG information
[root@testdb bin]# ./srvctl modify database -d testdb -a 'DATA,ARCH'
-- Now checking the status
[root@testdb bin]# ./srvctl config database -d testdb
Database unique name: testdb
Database name: testdb
Oracle home: /u01/app/oracle/product/11.2.0
Oracle user: grid
Spfile: +DATA/testdb/spfiletestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,ARCH
Services:
[root@testdb bin]#
2) We can also drop the disk group server control utility (srvctl) as well
Stopping the FRA DG resources
[root@testdb bin]# ./srvctl stop diskgroup -g FRA
Dropping the Disk
[root@testdb bin]# ./srvctl remove diskgroup -g FRA
Checking the Status
[root@testdb bin]# ./srvctl config database -d testdb
Database unique name: testdb
Database name: testdb
Oracle home: /u01/app/oracle/product/11.2.0
Oracle user: grid
Spfile: +DATA/testdb/spfiletestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,ARCH
Services:
[root@testdb bin]#
Login through Grid User:
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 NORMAL N 512 4096 1048576 10228 10022 0 5011 0 N ARCH/
MOUNTED NORMAL N 512 4096 1048576 10228 7036 0 3518 0 N DATA/
ASMCMD>
