ORA-31003 Tips

Question:  I am getting this ORA-31003 error with a ORA-06512 error:

ORA-31003: Parent /sys/acls/File_name.xml already contains child entry

How do I resolve this ORA-31003 error?

Answer:  The oerr utility show this for the error, an attempt to insert a duplicate child entry:

ORA-31003: Parent %s already contains child entry %s

Cause: An attempt was made to insert a duplicate child into the XDB hierarchical resolver.

Action: Insert a unique name into the container.

The solution is easy, just execute dbms_network_acl drop_acl procedure:

exec dbms_network_acl_admin.drop_acl('/u01/sys/acls/myfile_name.xml');
commit;

Re-execute script

ORA-24247: network access denied by access control list (ACL)

ORA-24247: network access denied by access control list (ACL)

SQL> begin
send_mail(‘test’,’azarmohds@gmail.com’);
end;
2 3 4
5 /
begin
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 19
ORA-06512: at “SYS.UTL_TCP”, line 280
ORA-06512: at “SYS.UTL_SMTP”, line 163
ORA-06512: at “SYS.UTL_SMTP”, line 199
ORA-06512: at “SCOTT.SEND_MAIL”, line 8
ORA-06512: at line 2

This error message means that no access control list has been assigned to the host you (your application) are trying to access, or no required privileges have been granted to the user by adding user to the ACL.

Solution :

Step 1:

SQL> grant execute on utl_http to scott;

Grant succeeded.

Step 2:  Conn as sysdba

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ‘scottdev.xml’,
description => ‘Permissions to access  mail’,
principal => ‘SCOTT’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

PL/SQL procedure successfully completed.

Step 3:

SQL> begin
2 DBMS_NETWORK_acl_ADMIN.ADD_PRIVILEGE(
3 acl => ‘scottdev.xml’,
4 principal => ‘SCOTT’,
5 is_grant => true,
6 privilege => ‘resolve’
7 );
8 COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

Step 4:

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ‘scottdev.xml’,
host => ‘*’);
COMMIT;
END;
/

Step 5:

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES

Step 6: Send mail as scott user

SQL> begin
send_mail(‘test’,’azarmohds@gmail.com’);
end; 2 3
4 /

PL/SQL procedure successfully completed.

Drop ACL :

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => ‘scottdev.xml”);
COMMIT;
END;
/

Alert: After SAN Firmware Upgrade, ASM Diskgroups ( Using ASMLIB) Cannot Be Mounted Due To ORA-15085: ASM disk "" has inconsistent sector size. (Doc ID 1500460.1)

Alert: After SAN Firmware Upgrade, ASM Diskgroups ( Using ASMLIB) Cannot Be Mounted Due To ORA-15085: ASM disk "" has inconsistent sector size. (Doc ID 1500460.1)

To Bottom

---

In this Document Description Ask Questions, Get Help, And Share Your Experiences With This Article Occurrence Symptoms Workaround Patches History References APPLIES TO: Linux OS Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2] Linux x86-64 Haansoft Linux x86 DESCRIPTION After upgrade  SAN "IBM NSeries N7950T - Data ONTAP 8.0.2P3" firmware to "BM NSeries N7950T - Data ONTAP 8.1.1" firmware, the ASM diskgroups cannot be mounted due to the next errors: SYS@+ASM2> alter diskgroup DATA11G mount; alter diskgroup DATA11G mount * ERROR at line 1: ORA-15032: not all alterations performed ORA-15017: diskgroup "DATA11G" cannot be mounted ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA11G" ORA-15085: ASM disk "" has inconsistent sector size. ORA-15085: ASM disk "" has inconsistent sector size. ORA-15085: ASM disk "" has inconsistent sector size. ORA-15085: ASM disk "" has inconsistent sector size. ORA-15085: ASM disk "" has inconsistent sector size. Ask Questions, Get Help, And Share Your Experiences With This Article Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts? Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article. Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Install/Upgrade. OCCURRENCE A) Original configuration: ASM RAC 11.2.0.3 . ASMLIB API installed Linux 64-bit IBM NSeries N7950T - Data ONTAP 8.0.2P3 (firmware upgrade) Diskgroups were created on 512 bytes sector size disks. B) The problem started on the new configuration "IBM NSeries N7950T - Data ONTAP 8.1.1": ASM RAC 11.2.0.3 . ASMLIB API installed Linux 64-bit IBM NSeries N7950T - Data ONTAP 8.1.1 (firmware upgrade) Diskgroups were created on 512 bytes sector size disks. Note 1: The problem could occur on any ASM release (10.2.0.1X to 11.2.0.X). Note 2: The problem could occur on any ASMLIB release. Note 3: This problem could occur with any other Storage Vendor, if the firmware upgrade modifies/changes the physical_block_size = 4096 bytes (4kb). C) This problem occurred due to the disks are now presented with "logical_block_size" = 512 bytes and "physical_block_size" = 4096 bytes (4kb) due to the SAN firmware upgrade follows: Before the the storage device firmware upgrade:  # cd /sys/block/dm-21/queue  # more *block_size  ::::::::::::::  logical_block_size  ::::::::::::::  512  ::::::::::::::  physical_block_size  ::::::::::::::  512 After the the storage device firmware upgrade:  # cd /sys/block/dm-21/queue  # more *block_size  ::::::::::::::  logical_block_size  ::::::::::::::  512  ::::::::::::::  physical_block_size  ::::::::::::::  4096 This problem occurred due to the storage device firmware was upgraded. The old firmware reported 512 bytes logical block size / 512 bytes physical block size, then after the upgrade, the new firmware reported 512 bytes logical block size / 4096 bytes physical block size. Consequently, a diskgroup created with 512-byte blocks will be now suddenly running on a device reporting 4096-byte sectors. For this reason ASM will refuse to import/mount the diskgroup(s). In other words, the logical sector size is presented = 512 bytes and the physical sector size is presented = 4096 bytes (4kb), this is not supported by ASMLIB API at this moment. E) Despite ASM diskgroups were created on 512 bytes sector size disks, ASM detects the ASMLIB disks with a sector size = 4096 bytes (4kb) instead of a sector size = 512 bytes, this inconsistency generates the problem (ORA-15085: ASM disk "" has inconsistent sector size.):  +ASM> select group_Number ,disk_number,path, sector_size from v$asm_disk .  GROUP_NUMBER DISK_NUMBER PATH                           SECTOR_SIZE  ------------ ----------- ------------------------------ -----------  ...             0          75 ORCL:TMSTS01_LUN010                   4096 E) But if the ASMLIB is disabled/bypassed (ASM_DISKSTRING = '/dev/oracleasm/disks/*'), then the disks are presented/detected with a sector size = 512 bytes:  +ASM> select group_Number ,disk_number,path, sector_size from v$asm_disk .  GROUP_NUMBER DISK_NUMBER PATH                                     SECTOR_SIZE  ------------ ----------- ---------------------------------------- -----------  ...             0          75 /dev/oracleasm/disks/TMSTS01_LUN010                  512 D) This problem is due to the SAN Firware upgrade and due to the next ASMLIB bug (ASMLIB enhancement): Bug : 13999609 - PHYSICAL BLOCK SIZE REPORTED CAN CAUSE ISSUES WITH 10G DATABASES oracleasm driver (ASMLIB) as of today works with the expectation that logical block size and physical block size are 512/512 bytes. This particular storage/firmware appears doesn't do that, so the configuration won't work with oracleasm driver. We understand that 4K sector size disks need to be supported, so we are working on an ASMLIB enhancement fix. Therefore, at this moment this SAN firmware upgrade is not certified and is not supported with ASMLIB. SYMPTOMS Error Description: 15085, 00000, "ASM disk \"%s\" has inconsistent sector size." // *Cause:  An attempt to mount a diskgroup failed because a disk reported //          inconsistent sector size value. // *Action: Use disks with sector size consistent with Diskgroup sector size, //          or make sure the operating system can accurately report the disk  //          sector size. // WORKAROUND ********* Warning !!!!!  ********* : Never set the "_disk_sector_size_override"=TRUE parameter in the ASM instance(s) as a workaround, since this parameter will corrupt the ASM diskgroups. Workaround 1 Therefore, the only valid workarounds are as follow: 1) Downgrade/rollback the SAN firmware upgrade. 2) Or disable/bypass the ASMLIB as follows: ASM_DISKSTRING = '/dev/oracleasm/disks/*' Workaround 2 NetApp has also provided a workaround for versions 8.0.5, 8.1.3 and 8.2 of Data ONTAP 7-Mode. The workaround allows specified LUNs to continue do not report the logical blocks per physical block value. This work around should only be applied to LUNs used by Oracle ASMlib with the symptoms described in this article. Example: From the Data ONTAP 7-Mode CLI, enter the following commands: > lun set report-physical-size <path> disable PATCHES 3) Or install the new “oracleasm-support-2.1.8-1” ASMLIB RPM package (which contains the permanent fix) as follows: Step #1: Shutdown the ASM instance(s): a) On RAC configurations you need to stop the CRS stack on all the nodes (as root user): # <Grid Infrastructure Oracle Home>/bin/crsctl stop crs b) On Restart/Standalone configurations you need to stop the HAS stack on all the nodes (as root user): # <Grid Infrastructure Oracle Home>/bin/crsctl stop has Step #2: Stop the ASMLIB API on all the nodes as root user: # /etc/init.d/oracleasm stop Step #3: Obtain and install the new “oracleasm-support-2.1.8-1” ASMLIB RPM package via  "Oracle Unbreakable Linux Network" as follows: [grid@asmlnx1 sbin]$ su - Password: [root@asmlnx1 ~]# yum update oracleasm-support Loaded plugins: aliases, changelog, downloadonly, kabi, presto, refresh-packagekit, security, tmprepo, verify, versionlock Loading support for kernel ABI ol6_UEK_latest                                                                                               | 1.2 kB     00:00    ol6_UEK_latest/primary                                                                                       | 7.0 MB     00:24    ol6_UEK_latest                                                                                                              162/162 ol6_latest                                                                                                   | 1.4 kB     00:00    ol6_latest/primary                                                                                           |  27 MB     01:18    ol6_latest                                                                                                              21277/21277 Setting up Update Process Resolving Dependencies --> Running transaction check ---> Package oracleasm-support.x86_64 0:2.1.5-1.el6 will be updated ---> Package oracleasm-support.x86_64 0:2.1.8-1.el6 will be an update --> Finished Dependency Resolution Dependencies Resolved ====================================================================================================================================  Package                              Arch                      Version                         Repository                     Size ==================================================================================================================================== Updating:  oracleasm-support                    x86_64                    2.1.8-1.el6                     ol6_latest                     73 k Transaction Summary ==================================================================================================================================== Upgrade       1 Package(s) Total download size: 73 k Is this ok [y/N]: Y Downloading Packages: Setting up and reading Presto delta metadata Processing delta metadata Package(s) data still to download: 73 k oracleasm-support-2.1.8-1.el6.x86_64.rpm                                                                     |  73 kB     00:00    warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY Retrieving key from http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 Importing GPG key 0xEC551F03:  Userid: "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"  From  : http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 Is this ok [y/N]: Y Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Warning: RPMDB altered outside of yum.   Updating   : oracleasm-support-2.1.8-1.el6.x86_64                                                                             1/2 warning: /etc/sysconfig/oracleasm created as /etc/sysconfig/oracleasm.rpmnew   Cleanup    : oracleasm-support-2.1.5-1.el6.x86_64                                                                             2/2   Verifying  : oracleasm-support-2.1.8-1.el6.x86_64                                                                             1/2   Verifying  : oracleasm-support-2.1.5-1.el6.x86_64                                                                             2/2 Updated:   oracleasm-support.x86_64 0:2.1.8-1.el6                                                                                           Complete! [root@asmlnx1 ~]# Note 1: Alternatively, you can download the new “oracleasm-support-2.1.8-1” ASMLIB RPM package from the following sites: Oracle ASMLib   And also from the "Oracle Unbreakable Linux Network": Getting Oracle ASMLib via the Unbreakable Linux Network    Step #4: Verify the current configuration in the  “/etc/sysconfig/oracleasm” file (the “ORACLEASM_USE_LOGICAL_BLOCK_SIZE” parameter is not set): [root@asmlnx1 ~]# cat /etc/sysconfig/oracleasm # # This is a configuration file for automatic loading of the Oracle # Automatic Storage Management library kernel driver.  It is generated # By running /etc/init.d/oracleasm configure.  Please use that method # to modify this file # # ORACLEASM_ENABELED: 'true' means to load the driver on boot. ORACLEASM_ENABLED=true # ORACLEASM_UID: Default user owning the /dev/oracleasm mount point. ORACLEASM_UID=grid # ORACLEASM_GID: Default group owning the /dev/oracleasm mount point. ORACLEASM_GID=asmadmin # ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot. ORACLEASM_SCANBOOT=true # ORACLEASM_SCANORDER: Matching patterns to order disk scanning ORACLEASM_SCANORDER="" # ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan ORACLEASM_SCANEXCLUDE="" Step #5: Then configure the new “ORACLEASM_USE_LOGICAL_BLOCK_SIZE” feature: [root@asmlnx1 ~]# /usr/sbin/oracleasm configure -p Writing Oracle ASM library driver configuration: done Step #6: Check the new configuration in the  “/etc/sysconfig/oracleasm” file: [root@asmlnx1 ~]# cat /etc/sysconfig/oracleasm # # This is a configuration file for automatic loading of the Oracle # Automatic Storage Management library kernel driver.  It is generated # By running /etc/init.d/oracleasm configure.  Please use that method # to modify this file # # ORACLEASM_ENABLED: 'true' means to load the driver on boot. ORACLEASM_ENABLED=true # ORACLEASM_UID: Default user owning the /dev/oracleasm mount point. ORACLEASM_UID=grid # ORACLEASM_GID: Default group owning the /dev/oracleasm mount point. ORACLEASM_GID=asmadmin # ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot. ORACLEASM_SCANBOOT=true # ORACLEASM_SCANORDER: Matching patterns to order disk scanning ORACLEASM_SCANORDER="" # ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan ORACLEASM_SCANEXCLUDE="" # ORACLEASM_USE_LOGICAL_BLOCK_SIZE: 'true' means use the logical block size # reported by the underlying disk instead of the physical. The default # is 'false' ORACLEASM_USE_LOGICAL_BLOCK_SIZE=false Step #7: By default “ORACLEASM_USE_LOGICAL_BLOCK_SIZE” is set = “FALSE”, therefore you will need to set it to “TRUE”: [root@asmlnx1 ~]# /usr/sbin/oracleasm configure -b Writing Oracle ASM library driver configuration: done Step #8: Check the new configuration in the  “/etc/sysconfig/oracleasm” file (now "ORACLEASM_USE_LOGICAL_BLOCK_SIZE" is set to "TRUE"): [root@asmlnx1 ~]# cat /etc/sysconfig/oracleasm # # This is a configuration file for automatic loading of the Oracle # Automatic Storage Management library kernel driver.  It is generated # By running /etc/init.d/oracleasm configure.  Please use that method # to modify this file # # ORACLEASM_ENABLED: 'true' means to load the driver on boot. ORACLEASM_ENABLED=true # ORACLEASM_UID: Default user owning the /dev/oracleasm mount point. ORACLEASM_UID=grid # ORACLEASM_GID: Default group owning the /dev/oracleasm mount point. ORACLEASM_GID=asmadmin # ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot. ORACLEASM_SCANBOOT=true # ORACLEASM_SCANORDER: Matching patterns to order disk scanning ORACLEASM_SCANORDER="" # ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan ORACLEASM_SCANEXCLUDE="" # ORACLEASM_USE_LOGICAL_BLOCK_SIZE: 'true' means use the logical block size # reported by the underlying disk instead of the physical. The default # is 'false' ORACLEASM_USE_LOGICAL_BLOCK_SIZE=true Note 2: Alternatively you manually set “ORACLEASM_USE_LOGICAL_BLOCK_SIZE” to 'FALSE' or 'TRUE' in “/etc/sysconfig/oracleasm”, where 'TRUE' means to use the “logical block size” and 'FALSE' the “physical block size”. The default is 'FALSE'. Step #9: Start the ASMLIB API on all the nodes as root user: # /etc/init.d/oracleasm start Step #10: Start the ASM instance(s):on all the nodes as root user: a) On RAC configurations you need to start the CRS stack on all the nodes (as root user): # <Grid Infrastructure Oracle Home>/bin/crsctl start crs b) On Restart/Standalone configurations you need to start the HAS stack on all the nodes (as root user): # <Grid Infrastructure Oracle Home>/bin/crsctl start has Appendix A: After implement the “oracleasm-support-2.1.8” RPM fix, the SECTOR_SIZE is v$ASM_DISK will be 4096 as follows:    SQL> SELECT NAME, PATH, SECTOR_SIZE FROM V$ASM_DISK; NAME                           PATH                                               SECTOR_SIZE ------------------------------ -------------------------------------------------- ----------- ASMDISK2                       ORCL:ASMDISK2                                              4096 ASMDISK3                       ORCL:ASMDISK3                                              4096 ASMDISK4                       ORCL:ASMDISK4                                              4096 ASMDISK5                       ORCL:ASMDISK5                                              4096 ASMDISK6                       ORCL:ASMDISK6                                              4096       Appendix B: Apart from “oracleasm-support-2.1.8” RPM, if you are an Oracle Linux customer, then you will need uek2 kernel 2.6.39-400.4.0 and above to implement this functionality. If you are a SUSE Linux customer then you will need SLES11 kernels.