2 Replies Latest reply: Jul 21, 2008 2:11 PM by 588146 RSS

    Can't create database using ASM (SOLVED)

    588146
      Hi all

      I'm trying to use ASM for the first time, on Oracle 10.2.0.1 on Solaris x64.

      I have installed the ASM instance into /opt/oracle/asm/10.2.0 and created disk groups. I have cssd running OK. I am able to start and stop the ASM instance without problems, and I can select from v$asm_diskgroup to confirm that disks are mounted OK.

      I have then installed Oracle EE separately into /opt/oracle/server/10.2.0. I first did a software only install, and now I am trying to create a DB.

      The problems come when I try to use this ASM instance to host a new database. I first tried to use DBCA to create a new database, but on database creation I got the following errors:
      ORA-00200: control file could not be created
      ORA-00202: control file: '+DBLIVE1'
      ORA-17502: ksfdcre:4 Failed to create file +DBLIVE1
      ORA-15001: diskgroup "DBLIVE1" does not exist or is not mounted
      ORA-15055: Message 15055 not found; No message file for product=RDBMS, facility=ORA
      ORA-01031: insufficient privileges

      I then told DBCA just to create the DB creation scripts, and I tried manually running these with SQL*Plus.

      When doing it with SQL*PLus, I initially got the same error as shown above. But then something changed (sorry, not sure what), and now the error I get is:
      CREATE DATABASE "NEONREL1"
      *
      ERROR at line 1:
      ORA-01501: CREATE DATABASE failed
      ORA-00349: failure obtaining block size for '+DBLIVE1'
      ORA-01031: insufficient privileges

      I've put some debug info below, showing me succesfully connecting to the ASM instance and then attempting to create the DB using the db creation scripts, showing the error at the end. You can see that the oracle OS user is able to connect fine to ASM, then I swithc ORACLE_SID and ORACLE_HOME to the EE install and try to create the DB, at which point it apparently can't connect to ASM any more.

      I've tried the DB creation many times, and in between attempts I completely empty $ORACLE_HOME/admin/<dbname> and delete the files related to the attempted install from $ORACLE_HOME/dbs/ . I've also stopping/starting ASM, rebooting, and I've done the install of ASM and EE a couple of times over in case I made any mistakes in my earlier attempts.

      Any help would be much appreciated!


      Tom

      #####
      ##### CHECKING ASM
      #####

      oracle@neonrcom-db1:~$ uname -a
      SunOS neonrcom-db1 5.10 Generic_127128-11 i86pc i386 i86pc

      # css is running
      oracle@neonrcom-db1:~$ ps -ef | grep css
      oracle 498 1 0 21:46:40 ? 0:01 /opt/oracle/asm/10.2.0/bin/ocssd.bin

      # listener is running in the ASM instance
      oracle@neonrcom-db1:~$ ps -ef | grep tnsl
      oracle 1332 1 0 21:49:59 ? 0:00 /opt/oracle/asm/10.2.0/bin/tnslsnr LISTENER -inherit

      # ASM is only entry in /var/opt/oracle/oratab
      oracle@neonrcom-db1:~$ grep -v "^#" /var/opt/oracle/oratab
      +ASM:/opt/oracle/asm/10.2.0:N

      # I can connect to ASM fine, and it has diskgroups mounted.
      oracle@neonrcom-db1:~$ export ORACLE_HOME=/opt/oracle/asm/10.2.0
      oracle@neonrcom-db1:~$ export ORACLE_SID='+ASM'
      oracle@neonrcom-db1:~$ sqlplus "sys as sysdba"

      SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 21 20:53:10 2008

      Copyright (c) 1982, 2005, Oracle. All rights reserved.

      Enter password:

      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options

      SQL> set line 150
      SQL> select name, block_size, state, type, total_mb, free_mb from v$asm_diskgroup;

      NAME BLOCK_SIZE STATE TYPE TOTAL_MB FREE_MB
      ------------------------------ ---------- ----------- ------ ---------- ----------
      DBARCH1 4096 MOUNTED EXTERN 2096856 2096784
      DBLIVE1 4096 MOUNTED EXTERN 4193904 4193812

      ####
      #### Contents of init.ora for new DB
      ####
      db_create_file_dest=+DBLIVE1
      db_recovery_file_dest=+DBARCH1
      db_recovery_file_dest_size=2147483648

      #####
      ##### DB INSTALLATION ATTEMPT
      #####

      oracle@neonrcom-db1:~$ export ORACLE_HOME=/opt/oracle/server/10.2.0
      oracle@neonrcom-db1:~$ export ORACLE_SID='NEONREL1'
      oracle@neonrcom-db1:~$ export PATH=$ORACLE_HOME/bin:$PATH

      oracle@neonrcom-db1:~$ /opt/oracle/server/10.2.0/admin/NEONREL1/scripts/NEONREL1.sh
      You should Add this entry in the /var/opt/oracle/oratab: NEONREL1:/opt/oracle/server/10.2.0:Y

      SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 21 22:10:54 2008

      Copyright (c) 1982, 2005, Oracle. All rights reserved.

      specify a password for sys as parameter 1
      Enter value for 1: xxx
      specify a password for system as parameter 2
      Enter value for 2: xxx
      specify a password for sysman as parameter 3
      Enter value for 3: xxx
      specify a password for dbsnmp as parameter 4
      Enter value for 4: xxx
      specify ASM SYS user password as parameter 6
      Enter value for 6: xxx

      Connected to an idle instance.
      SQL> spool /opt/oracle/server/10.2.0/admin/NEONREL1/scripts/CreateDB.log
      SQL> startup nomount pfile="/opt/oracle/server/10.2.0/admin/NEONREL1/scripts/init.ora";
      ORACLE instance started.

      Total System Global Area 1.9294E+10 bytes
      Fixed Size 2054976 bytes
      Variable Size 2264925376 bytes
      Database Buffers 1.7012E+10 bytes
      Redo Buffers 14721024 bytes
      SQL> CREATE DATABASE "NEONREL1"
      2 MAXINSTANCES 8
      3 MAXLOGHISTORY 1
      4 MAXLOGFILES 16
      5 MAXLOGMEMBERS 3
      6 MAXDATAFILES 100
      7 DATAFILE SIZE 300M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
      8 EXTENT MANAGEMENT LOCAL
      9 SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
      10 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
      11 SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
      12 CHARACTER SET AL32UTF8
      13 NATIONAL CHARACTER SET UTF8
      14 LOGFILE GROUP 1 SIZE 51200K,
      15 GROUP 2 SIZE 51200K,
      16 GROUP 3 SIZE 51200K
      17 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
      CREATE DATABASE "NEONREL1"
      *
      ERROR at line 1:
      ORA-01501: CREATE DATABASE failed
      ORA-00349: failure obtaining block size for '+DBLIVE1'
      ORA-01031: insufficient privileges

      Message was edited by:
      tjobbins
        • 1. Re: Can't create database using ASM, 'insufficient privileges' error
          588146
          Update: I've worked out the difference between the two sets of errors I get.

          The basic error is this:
          ORA-00200: control file could not be created
          ORA-00202: control file: '+DBLIVE1'
          ORA-17502: ksfdcre:4 Failed to create file +DBLIVE1
          ORA-15001: diskgroup "DBLIVE1" does not exist or is not mounted
          ORA-15055: Message 15055 not found; No message file for product=RDBMS, facility=ORA
          ORA-01031: insufficient privileges

          However if my init.ora contains the line:
          control_files=/opt/oracle/server/10.2.0/dbs/cntrlNEONREL1.dbf

          then I instead get the second error:
          CREATE DATABASE "NEONREL1"
          *
          ERROR at line 1:
          ORA-01501: CREATE DATABASE failed
          ORA-00349: failure obtaining block size for '+DBLIVE1'
          ORA-01031: insufficient privileges

          So basically these must be the same error, just in the second case I'm not trying to put the control file on the ASM so it fails at a different point.

          But both errors must be because of the same cause, I suppose.
          • 2. Re: Can't create database using ASM (SOLVED)
            588146
            OS authentication wasn't working..

            I thought ASM was installed with the DB group set to 'oracle', but it was still at the default of 'dba'

            I created a dba group, added user Oracle to it, and all worked ok.

            thanks for listening :)