This discussion is archived
4 Replies Latest reply: Sep 21, 2013 6:48 AM by GTS (DBA) RSS

DB creation error

GTS (DBA) Journeyer
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points