问题描述：在Exadata迁移中使用的DG被直接用asmcmd从asm中rm掉了所有datafile.但是最后的一级目录无法删除,并且影响到了其他实例.在rm hang之后 diskgroup无法添加任何文件.原来的实例的所有add datafile drop directory操作均被hang住
[grid@dm02db01 ~]$ crsctl query crs softwareversion Oracle Clusterware version on node [dm02db01] is [11.2.0.3.0] SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production [root@dm02cel01 ~]# imageinfo Kernel version: 2.6.18-274.18.1.0.1.el5 #1 SMP Thu Feb 9 19:07:16 EST 2012 x86_64 Cell version: OSS_11.2.3.1.0_LINUX.X64_120304 Cell rpm version: cell-11.2.3.1.0_LINUX.X64_120304-1 Active image version: 11.2.3.1.0.120304 Active image activated: 2012-05-07 02:04:12 -0700 Active image status: success Active system partition on device: /dev/md5 Active software partition on device: /dev/md7 In partition rollback: Impossible Cell boot usb partition: /dev/sdm1 Cell boot usb version: 11.2.3.1.0.120304 Inactive image version: undefined Rollback to the inactive partitions: Impossible
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y YHDLOG/
Y EDWT/
ASMCMD> ls -l +data_dm02/edwt
Type Redund Striped Time Sys Name
Y DATAFILE/
Y ONLINELOG/
do this command:
ASMCMD> rm -rf edwt/
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
ERROR: alter diskgroup DATA_DM02 drop directory '+DATA_DM02/edwt' FORCE Thu Sep 20 17:12:06 2012 SQL> /* ASMCMD */alter diskgroup /*ASMCMD*/ "DATA_DM02" drop directory '+DATA_DM02/edwt/DATAFILE' ORA-15032: not all alterations performed ORA-15177: cannot operate on system aliases ERROR: /* ASMCMD */alter diskgroup /*ASMCMD*/ "DATA_DM02" drop directory '+DATA_DM02/edwt/DATAFILE' Thu Sep 20 17:12:22 2012 SQL> /* ASMCMD */alter diskgroup /*ASMCMD*/ "DATA_DM02" drop directory '+DATA_DM02/edwt/DATAFILE' ORA-15032: not all alterations performed ORA-15177: cannot operate on system aliases ERROR: /* ASMCMD */alter diskgroup /*ASMCMD*/ "DATA_DM02" drop directory '+DATA_DM02/edwt/DATAFILE' Thu Sep 20 17:59:07 2012 Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_67709.trc (incident=73026): ORA-04030: out of process memory when trying to allocate 184 bytes (callheap,kffd) Incident details in: /u01/app/grid/diag/asm/+asm/+ASM2/incident/incdir_73026/+ASM2_ora_67709_i73026.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Sep 20 17:59:40 2012 Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_67709.trc (incident=73027): ORA-04030: out of process memory when trying to allocate 48 bytes (kfui heap,kfuilst) ORA-04030: out of process memory when trying to allocate 184 bytes (callheap,kffd) Incident details in: /u01/app/grid/diag/asm/+asm/+ASM2/incident/incdir_73027/+ASM2_ora_67709_i73027.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details.
Solution:
receate database using DBCA and put datafiles in these directories and then using dbca to drop the database you created.
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y YHDLOG/
已经被删除掉了.
ORA-15032 ORA-15177 Deletion of System generated ASM files [ID 459161.1]
Modified:Aug 22, 2012Type:PROBLEMStatus:PUBLISHEDPriority:3
Comments (0)
In this Document
Symptoms
Cause
Solution
Applies to:
Oracle Server – Enterprise Edition – Version 10.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Feb-2011***
Symptoms
SQL> alter diskgroup TEMP_GROUP drop alias
‘+TEMP_GROUP/PDWH/DUMPSET/SYSTEMSYS_EXPORT_SCHEMA_01_1309498_1.259.6324066’;
alter diskgroup TEMP_GROUP drop alias
‘+TEMP_GROUP/PDWH/DUMPSET/SYSTEMSYS_EXPORT_SCHEMA_01_1309498_1.259.6324066’
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases
Cause
When the file was dropped from ASM/sqlplus, then it should also remove the System ASM file.
During the file drop through asmcmd , it is necessary to provide the untruncated file name.Since the NAME column in the fixed views like v$asm_alias is VARCHAR2(48), the fix would be to get the untruncated name and then drop the file explicitly.
No queries currently available to get the Actual file name and this is because of the way rows are present in V$ASM_ALIAS . This has to be achieved manually , as below .
Solution
Alternatively, use the below ” +diskgroup.fnum.incarn ” to drop the file like:
SQL > ALTER DISKGROUP DROP FILE ”;
example :
SQL > ALTER DISKGROUP TEMP_GROUP DROP FILE ‘+TEMP_GROUP.259.632406635’;
The “+diskgroup.fnum.incarn ” information , can be retrieved from the below script output , ASM_FILENAME ::
SQL> select f.file_number, a.name , (‘+’ || g.name||’.’||f.file_number||’.’||f.incarnation)
asm_filename,
2 f.type from v$asm_diskgroup g,
3 v$asm_file f,
4 v$asm_alias a
5 where g.name = ”
6 and g.group_number = f.group_number
7 and g.group_number = a.group_number
8 and f.file_number = a.file_number
9 order by f.file_number;
How To Remove An Empty ASM System Directory [ID 444812.1]
Modified:Feb 7, 2012Type:HOWTOStatus:PUBLISHEDPriority:3
Comments (0)
In this Document
Goal
Solution
References
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.2.0.1.0 – Release: 10.2 to 11.2
Information in this document applies to any platform.
Goal
Sometimes it can happen that the ASM contains empty system directories which cannot be removed. According to Note 3751057.8(ALTER DISKGROUP DROP DIRECTORY FORCE removes system aliases) the system generated directories should be removed automatically as soon as all it’s files are removed. However this does not happen sometimes and a manual directory removal will fail :
SQL> set pages 2000
SQL> SELECT level, dir, sys, substr(lpad(‘ ‘,2*level,’ ‘)||CONCAT(‘+’||gname,
SYS_CONNECT_BY_PATH(aname,’/’)),1,60) full_path
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 rtrim(ltrim(full_path))desc, level asc;
3 N Y +DATA/UDWH/DATAFILE/ADASTRA.376.624105133
2 Y Y +DATA/UDWH/DATAFILE
3 N Y +DATA/UDWH/CONTROLFILE/Current.486.624103637
3 N Y +DATA/UDWH/CONTROLFILE/Current.479.624103637
3 N Y +DATA/UDWH/CONTROLFILE/Current.434.624103637
2 Y Y +DATA/UDWH/CONTROLFILE
1 Y Y +DATA/UDWH
1 Y Y +DATA/TDWH
2 N N +DATA/IDWH/spfileIDWH.ora
2 N N +DATA/IDWH/control03.ctl
2 N N +DATA/IDWH/control02.ctl
2 N N +DATA/IDWH/control01.ctl
The TDWH directory from the above list is empty but cannot be removed :
ASMCMD> cd tdwh
ASMCMD> ls
ASMCMD> cd ..
ASMCMD> rm -rf tdwh
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
alter diskgroup DATA drop directory ‘+DATA/TDWH’ FORCE;
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
A solution to remove these directories is to recreate the ASM diskgroups but this is not something one would do very often especially when the ASM is used by production databases.
Solution
Either:
Create a new database with the DBCA having the same name as the old directory and subsequently drop the database with the DBCA.
Or:
Create a dummy tablespace having a datafile within the directory to be dropped, drop the tablespace and then remove the datafile using the ASMCMD tool. The old directory will be removed automatically.
There is a scenario when there are left behind directories after the database is dropped using RMAN’s command “drop database including backups [noprompt];”. When one falls into this there is no way out but to recreate the diskgroups. This problem is being investigated in Bug 6606689. To avoid this issue one should run the following two commands instead of “DROP DATABASE INCLUDING BACKUPS;”:
RMAN> delete backupset;
RMAN> drop database;
References
BUG:6110998 – UNABLE TO DELETE SYSTEM CREATED DIRECTORY
NOTE:3751057.8 – Bug 3751057 – ALTER DISKGROUP DROP DIRECTORY FORCE removes system aliases
BUG:6606689 – RMAN LEAVES BEHIND DIRECTORIES IN ASM THAT CANNOT BE REMOVED