ASM error : DBD ERROR: OCIStmtExecute

2 thoughts on “ASM error : DBD ERROR: OCIStmtExecute”

1. 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;

2. 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