4 Replies Latest reply: Sep 21, 2013 8:48 AM by GTS (DBA) RSS

    DB creation error

    GTS (DBA)

      Hello  Experts ;

       

      I am getting error  when creating db creation manually. i cannot resolve it.

      I have pasted required informations. please have a look and guide me to solve this

       

       

      initorcltest.ora

       

      background_dump_dest='/u01/app/oracle/admin/orcltest/bdump'

      compatible='10.2.0'

      control_files='/u01/app/oracle/oradata/orcltest/ctrl01.ctl','/u01/app/oracle/oradata/orcltest/ctrl02.ctl', '/u01/app/oracle/oradata/orcltest/ctrl03.ctl'

      core_dump_dest='/u01/app/oracle/admin/orcltest/cdump'

      db_block_size=8192

      db_file_multiblock_read_count=16

      db_name='orcltest'

      job_queue_processes=10

      open_cursors=1024

      pga_aggregate_target=300m

      processes=300

      remote_login_passwordfile='EXCLUSIVE'

      sessions=300

      sga_target=800m

      undo_management='AUTO'

      undo_tablespace='UNDOTBS'

      user_dump_dest='/u01/app/oracle/admin/orcltest/udump'

      audit_file_dest='/u01/app/oracle/admin/orcltest/adump'

      ~

       

      DB SCRIPT

       

      CREATE  DATABASE   orcltest

             USER SYS IDENTIFIED BY pz6r58

             USER SYSTEM IDENTIFIED BY y1tz5p

      LOGFILE  GROUP 1 ('/u01/app/oracle/oradata/orcltest/redo01.log') SIZE  100M  REUSE ,

            GROUP 2 ('/u01/app/oracle/oradata/orcltest/redo02.log') SIZE  100M  REUSE ,

            GROUP 3 ('/u01/app/oracle/oradata/orcltest/redo03.log') SIZE  100M  REUSE

         MAXLOGFILES  5

         MAXLOGMEMBERS  5

         MAXLOGHISTORY  1

         MAXDATAFILES  100

         MAXINSTANCES  1

        CHARACTER SET US7ASCII

        NATIONAL CHARACTER SET AL16UTF16

        ARCHIVELOG

      DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf' SIZE 400M REUSE

      EXTENT MANAGEMENT LOCAL

      SYSAUX DATAFILE '/u01/oracle/oradata/orcltest/sysaux01.dbf' SIZE 400M REUSE

      DEFAULT  TABLESPACE tbs1

      DEFAULT TEMPORARY TABLESPACE temp

      TEMPFILE '/u01/app/oracle/oradata/orcltest/temp01.dbf' SIZE 20M REUSE

      UNDO TABLESPACE undotbs

       

      SQL> startup pfile='/u01/app/oracle/admin/orcltest/pfile/initorcltest.ora' nomount;

      ORACLE instance started.

      Total System Global Area  838860800 bytes

      Fixed Size                  1222168 bytes

      Variable Size             213912040 bytes

      Database Buffers          620756992 bytes

      Redo Buffers                2969600 bytes

      SQL> @ /u01/app/oracle/admin/orcltest/script/create.sql;

      CREATE  DATABASE   orcltest

      * ERROR at line 1:

      ORA-01092: ORACLE instance terminated. Disconnection forced

       

      My alert log    i do NOT find any proper error 

       

      ORA-1092 signalled during: CREATE  DATABASE   orcltest

             USER SYS IDENTIFIED BY ********USER SYSTEM IDENTIFIED BY *LOGFILE  GROUP 1 ('/u01/app/oracle/oradata/orcltest/redo01.log') SIZE  100M  REUSE ,

            GROUP 2 ('/u01/app/oracle/oradata/orcltest/redo02.log') SIZE  100M  REUSE ,

            GROUP 3 ('/u01/app/oracle/oradata/orcltest/redo03.log') SIZE  100M  REUSE

         MAXLOGFILES  5

         MAXLOGMEMBERS  5

         MAXLOGHISTORY  1

         MAXDATAFILES  100

         MAXINSTANCES  1

         CHARACTER SET US7ASCII

         NATIONAL CHARACTER SET AL16UTF16

          ARCHIVELOG

      DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf' SIZE 400M REUSE

      EXTENT MANAGEMENT LOCAL

      SYSAUX DATAFILE '/u01/oracle/oradata/orcltest/sysaux01.dbf' SIZE 400M REUSE

      DEFAULT  TABLESPACE tbs1

      DEFAULT TEMPORARY TABLESPACE temp

      TEMPFILE '/u01/app/oracle/oradata/orcltest/temp01.dbf' SIZE 20M REUSE

      UNDO TABLESPACE undotbs

      DATAFILE '/u01/app/oracle/oradata/orcltest/undotbs.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED ...

       

      Checking physical file 


      cd /u01/app/oracle/oradata/orcltest/

      [oracle@oracle orcltest]$ ls -l

      total 942600

      -rw-r-----  1 oracle oinstall   6832128 Sep 21 01:30 ctrl01.ctl

      -rw-r-----  1 oracle oinstall   6832128 Sep 21 01:30 ctrl02.ctl

      -rw-r-----  1 oracle oinstall   6832128 Sep 21 01:30 ctrl03.ctl

      -rw-r-----  1 oracle oinstall 104858112 Sep 21 01:30 redo01.log

      -rw-r-----  1 oracle oinstall 104858112 Sep 21 01:29 redo02.log

      -rw-r-----  1 oracle oinstall 104858112 Sep 21 01:29 redo03.log

      -rw-r-----  1 oracle oinstall 419438592 Sep 21 01:30 system01.dbf

      -rw-r-----  1 oracle oinstall 209723392 Sep 21 01:30 undotbs.dbf

       

      i  cannot resolve this error, anyone can help me ? temporary tablespace file was not created but i cannot find any right reason.

      anyone help me to resolve my error


      SQL> shut immediate;

      ORA-24324: service handle not initialized

      ORA-01041: internal error. hostdef extension doesn't exist

        • 1. Re: DB creation error
          user11240176

          Can you try with below scripts.

           

          CREATE  DATABASE   orcltest
                 USER SYS IDENTIFIED BY pz6r58
                 USER SYSTEM IDENTIFIED BY y1tz5p
          LOGFILE  GROUP 1 ('/u01/app/oracle/oradata/orcltest/redo01.log') SIZE  100M  REUSE ,
                GROUP 2 ('/u01/app/oracle/oradata/orcltest/redo02.log') SIZE  100M  REUSE ,
                GROUP 3 ('/u01/app/oracle/oradata/orcltest/redo03.log') SIZE  100M  REUSE
             MAXLOGFILES  5
             MAXLOGMEMBERS  5
             MAXLOGHISTORY  1
             MAXDATAFILES  100
             MAXINSTANCES  1
            CHARACTER SET US7ASCII
            NATIONAL CHARACTER SET AL16UTF16
            ARCHIVELOG
          DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf' SIZE 400M REUSE
          EXTENT MANAGEMENT LOCAL
          SYSAUX DATAFILE '/u01/oracle/oradata/orcltest/sysaux01.dbf' SIZE 400M REUSE
          DEFAULT  TABLESPACE tbs1 DATAFILE '/u01/app/oracle/oradata/orcltest/tbs101.dbf' SIZE 100M REUSE
          DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/orcltest/temp01.dbf' SIZE 20M REUSE
          UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/orcltest/undotbs.dbf' SIZE 200M REUSE;

           

          Thanks,

          Asif Haliyal

          • 2. Re: DB creation error
            GTS (DBA)

            once agin i tried ,  

             

            SQL> conn /as sysdba

            Connected to an idle instance.

             

            SQL> startup pfile='/u01/app/oracle/admin/orcltest/pfile/initorcltest.ora' nomount;

            ORACLE instance started.

             

            Total System Global Area  838860800 bytes

            Fixed Size                  1222168 bytes

            Variable Size             213912040 bytes

            Database Buffers          620756992 bytes

            Redo Buffers                2969600 bytes

             

            SQL> @ /u01/app/oracle/admin/orcltest/script/create.sql;

            CREATE  DATABASE   orcltest

            *

            ERROR at line 1:

            ORA-01092: ORACLE instance terminated. Disconnection forced

             

            SQL> !

            $ pwd

            /u01/app/oracle/oradata/orcltest

            [oracle@oracle orcltest]$ ls -l

            total 1352688

            -rw-r-----  1 oracle oinstall   6832128 Sep 21 02:01 ctrl01.ctl

            -rw-r-----  1 oracle oinstall   6832128 Sep 21 02:01 ctrl02.ctl

            -rw-r-----  1 oracle oinstall   6832128 Sep 21 02:01 ctrl03.ctl

            -rw-r-----  1 oracle oinstall 104858112 Sep 21 02:01 redo01.log

            -rw-r-----  1 oracle oinstall 104858112 Sep 21 02:01 redo02.log

            -rw-r-----  1 oracle oinstall 104858112 Sep 21 02:01 redo03.log

            -rw-r-----  1 oracle oinstall 419438592 Sep 21 02:01 sysaux01.dbf

            -rw-r-----  1 oracle oinstall 419438592 Sep 21 02:01 system01.dbf

            -rw-r-----  1 oracle oinstall  20979712 Sep 21 02:01 temp01.dbf

            -rw-r-----  1 oracle oinstall 209723392 Sep 21 02:01 undotbs.dbf

             

            My alert.log showing

             

            Errors in file /u01/app/oracle/admin/orcltest/udump/orcltest_ora_6477.trc:

            ORA-00604: error occurred at recursive SQL level 1

            ORA-02236: invalid file name

            Sat Sep 21 02:01:31 2013

            Errors in file /u01/app/oracle/admin/orcltest/udump/orcltest_ora_6477.trc:

            ORA-01501: CREATE DATABASE failed

            ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 6262

            ORA-00604: error occurred at recursive SQL level 1

            ORA-02236: invalid file name

            Error 1519 happened during db open, shutting down database

            USER: terminating instance due to error 1519

            Sat Sep 21 02:01:31 2013

            Errors in file /u01/app/oracle/admin/orcltest/bdump/orcltest_pmon_6456.trc:

            ORA-01519: error while processing file '' near line

            • 3. Re: DB creation error
              GTS (DBA)

              SQL> startup pfile='/u01/app/oracle/admin/orcltest/pfile/initorcltest.ora' nomount;

              ORACLE instance started.

               

               

              Total System Global Area  838860800 bytes

              Fixed Size                  1222168 bytes

              Variable Size             213912040 bytes

              Database Buffers          620756992 bytes

              Redo Buffers                2969600 bytes

              SQL> @ /u01/app/oracle/admin/orcltest/script/create.sql;

              Database created.

               

              Thanks  Asif Haliyal

              • 4. Re: DB creation error
                GTS (DBA)

                Good Morning Everyone !

                 

                today i have created  database manually.

                now  i am trying to startup my database  i am getting error.

                 

                SQL> startup pfile='/u01/app/oracle/admin/orcltest/pfile/initorcltest.ora' ;

                ORACLE instance started.

                Total System Global Area  838860800 bytes

                Fixed Size                  1222168 bytes

                Variable Size             213912040 bytes

                Database Buffers          620756992 bytes

                Redo Buffers                2969600 bytes

                Database mounted.

                ORA-01092: ORACLE instance terminated. Disconnection forced

                 

                alertlog showing

                 

                LD_LIBRARY_PATH is not set!

                The default library directories are /lib and /usr/lib

                Unable to find dynamic library libocrb10.so in search paths

                Unable to find dynamic library libocrutl10.so in search paths

                Unable to find dynamic library libocrutl10.so in search paths

                LICENSE_MAX_SESSION = 0

                LICENSE_SESSIONS_WARNING = 0

                Picked latch-free SCN scheme 2

                Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/10.2.0/db_1/dbs/arch

                Autotune of undo retention is turned on.

                IMODE=BR

                ILAT =36

                LICENSE_MAX_USERS = 0

                SYS auditing is disabled

                ksdpec: called for event 13740 prior to event group initialization

                Starting up ORACLE RDBMS Version: 10.2.0.1.0.

                                                                      

                MY DB SCRIPT  having   archive.log  but  i did NOT specify any specific path  for archive destination.

                by default  , why it doesn't use oracle_home directory  ?

                 

                initorcltest.ora

                 

                background_dump_dest='/u01/app/oracle/admin/orcltest/bdump'

                compatible='10.2.0'

                control_files='/u01/app/oracle/oradata/orcltest/ctrl01.ctl','/u01/app/oracle/oradata/orcltest/ctrl02.ctl', '/u01/app/oracle/oradata/orcltest/ctrl03.ctl'

                core_dump_dest='/u01/app/oracle/admin/orcltest/cdump'

                db_block_size=8192

                db_file_multiblock_read_count=16

                db_name='orcltest'

                job_queue_processes=10

                open_cursors=1024

                pga_aggregate_target=300m

                processes=300

                remote_login_passwordfile='EXCLUSIVE'

                sessions=300

                sga_target=800m

                undo_management='AUTO'

                undo_tablespace='UNDOTBS'

                user_dump_dest='/u01/app/oracle/admin/orcltest/udump'

                audit_file_dest='/u01/app/oracle/admin/orcltest/adump'


                DBSCRIPT


                CREATE DATABASE orcltest

                  USER SYS IDENTIFIED BY pz6r58

                  USER SYSTEM IDENTIFIED BY y1tz5p

                LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcltest/redo01.log') SIZE 100M REUSE ,

                  GROUP 2 ('/u01/app/oracle/oradata/orcltest/redo02.log') SIZE 100M REUSE ,

                  GROUP 3 ('/u01/app/oracle/oradata/orcltest/redo03.log') SIZE 100M REUSE

                  MAXLOGFILES 5

                  MAXLOGMEMBERS 5

                  MAXLOGHISTORY 1

                  MAXDATAFILES 100

                  MAXINSTANCES 1

                  CHARACTER SET US7ASCII

                  NATIONAL CHARACTER SET AL16UTF16

                  ARCHIVELOG

                DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf' SIZE 400M REUSE

                EXTENT MANAGEMENT LOCAL

                SYSAUX DATAFILE '/u01/app/oracle/oradata/orcltest/sysaux01.dbf' SIZE 400M REUSE

                DEFAULT TABLESPACE tbs1

                DATAFILE '/u01/app/oracle/oradata/orcltest/tbs101.dbf' SIZE 100M REUSE

                DEFAULT TEMPORARY TABLESPACE temp

                TEMPFILE '/u01/app/oracle/oradata/orcltest/temp01.dbf' SIZE 20M REUSE

                UNDO TABLESPACE undotbs

                DATAFILE '/u01/app/oracle/oradata/orcltest/undotbs.dbf'

                 

                ---------

                 

                thanks all;