Home » Infrastructure » Linux » manual creation of database (ORACLE 11g R2 (11.2.0.1.0))
icon12.gif  manual creation of database [message #481427] Wed, 03 November 2010 08:19 Go to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hi All,

I am trying to create a manual database on ORACLE 11g R2 but I am getting some error when I am running the script that I have made for "CREATE DATABASE....". I am posting the contents of my pfile, create.sql and the error logs that I am getting.

"pfile contents"

control_files = (/u01/app/oracle/den/dbf/control.001.dbf,
/u01/app/oracle/den/dbf/control.002.dbf,
/u01/app/oracle/den/dbf/control.003.dbf)
db_name = den
db_block_size = 8192
pga_aggregate_target = 2500M
processes = 1000
sessions = 1200
open_cursors = 1024
undo_management = AUTO
undo_tablespace = undotbs
compatible = 11.2.0.1.0
sga_target = 1500M
nls_language = AMERICAN
nls_territory = AMERICA
db_recovery_file_dest =/u01/app/oracle/den/recovery
db_recovery_file_dest_size = 100G
core_dump_dest = /u01/app/oracle/den/cdump


"create.sql"

CREATE DATABASE den
LOGFILE GROUP 1 ('/u01/app/oracle/den/dbf/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/den/dbf/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/den/dbf/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/den/dbf/system01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/den/dbf/sysaux01.dbf' SIZE 500M
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u01/app/oracle/den/dbf/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/den/dbf/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

"Error Logs"

SQL> @/u01/app/oracle/den/create/create.sql
CREATE DATABASE den
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 20
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name
Process ID: 2903
Session ID: 1 Serial number: 3


Thanks in advance for the Help.

Regards
Deepak...

Report message to a moderator

Re: manual creation of database [message #481429 is a reply to message #481427] Wed, 03 November 2010 08:30 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
I haven't tested it, but do you need to specify a datafile for your default permanent tablespace?

Report message to a moderator

Re: manual creation of database [message #481435 is a reply to message #481427] Wed, 03 November 2010 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
ORA-02236: invalid file name
 *Cause:  A character string literal was not used in the filename list
            of a LOGFILE, DATAFILE, or RENAME clause.
 *Action: Use correct syntax.


Have a look at alert.log file to see the current statement executed by CREATE DATABASE.

Regards
Michel

Report message to a moderator

Re: manual creation of database [message #481444 is a reply to message #481427] Wed, 03 November 2010 09:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to include a datafile for default tablespace
or
Make use of DB_CREATE_FILE_DEST. With this setting, Oracle will create datafiles in location identified.

Report message to a moderator

Re: manual creation of database [message #481464 is a reply to message #481444] Wed, 03 November 2010 12:22 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Thanks Everyone for the reply, sure I'll will include datafile for the default database.

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

Can you tell me what is this error for?

Regards
Deepak.

Report message to a moderator

Re: manual creation of database [message #481466 is a reply to message #481464] Wed, 03 November 2010 12:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just look into that particular line in the said file
17 REM create the default permanent tablespace for the database
    18  REM
    19  "D_CRTDPTS"
    20  /
    21  REM
    22  REM set the default permanent tablespace for the database
    23  REM
    24  "D_ALTDPTS"
    25  /



oracle@kaapi#oerr ORA 01519
01519, 00000, "error while processing file '%s' near line %s"
// *Cause:  CREATE DATABASE encountered a problem while processing
//          specified file.  The specified file is bad.
// *Action: Retry your system installation procedure or contact
//          your customer support representative.

[Updated on: Wed, 03 November 2010 12:32]

Report message to a moderator

Re: manual creation of database [message #481468 is a reply to message #481464] Wed, 03 November 2010 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
dsharma wrote on Wed, 03 November 2010 18:22
Thanks Everyone for the reply, sure I'll will include datafile for the default database.

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

Can you tell me what is this error for?

Regards
Deepak.


Quote:
Have a look at alert.log file to see the current statement executed by CREATE DATABASE.


Regards
Michel

Report message to a moderator

Re: manual creation of database [message #481526 is a reply to message #481466] Wed, 03 November 2010 22:01 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hello Sir,

Can elaborate your post, it will be helpful for me to understand it better.Have I missed these lines in my create database script or there is something else that you want make me to understand.

Thanks
Deepak

Report message to a moderator

Re: manual creation of database [message #481529 is a reply to message #481468] Wed, 03 November 2010 22:24 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hello Sir,

I am posting the last logs of my alert log file. You can have look in the logs it further referring to one trace file whose logs I have posted below the logs of the alert log file.

I think I am getting the error because I have not specified the datafile for the Default tablespace but then It will be better if you check the logs.

"alert log file"

Wed Nov 03 12:18:57 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =167
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfileden.ora
System parameters with non-default values:
processes = 1000
sessions = 1522
nls_language = "AMERICAN"
nls_territory = "AMERICA"
sga_target = 1504M
control_files = "/u01/app/oracle/den/dbf/control.001.dbf"
control_files = "/u01/app/oracle/den/dbf/control.002.dbf"
control_files = "/u01/app/oracle/den/dbf/control.003.dbf"
db_block_size = 8192
compatible = "11.2.0.1.0"
db_recovery_file_dest = "/u01/app/oracle/den/recovery"
db_recovery_file_dest_size= 100G
undo_management = "AUTO"
undo_tablespace = "undotbs"
core_dump_dest = "/u01/app/oracle/den/cdump"
db_name = "den"
open_cursors = 1024
pga_aggregate_target = 2500M
Wed Nov 03 12:19:03 2010
PMON started with pid=2, OS id=4155
Wed Nov 03 12:19:03 2010
VKTM started with pid=3, OS id=4157 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Nov 03 12:19:03 2010
GEN0 started with pid=4, OS id=4164
Wed Nov 03 12:19:04 2010
DIAG started with pid=5, OS id=4166
Wed Nov 03 12:19:04 2010
DBRM started with pid=6, OS id=4168
Wed Nov 03 12:19:04 2010
PSP0 started with pid=7, OS id=4170
Wed Nov 03 12:19:04 2010
DIA0 started with pid=8, OS id=4172
Wed Nov 03 12:19:05 2010
MMAN started with pid=9, OS id=4178
Wed Nov 03 12:19:05 2010
DBW0 started with pid=10, OS id=4180
Wed Nov 03 12:19:05 2010
LGWR started with pid=11, OS id=4182
Wed Nov 03 12:19:05 2010
SMON started with pid=13, OS id=4186
Wed Nov 03 12:19:06 2010
RECO started with pid=14, OS id=4192
Wed Nov 03 12:19:06 2010
CKPT started with pid=12, OS id=4184
Wed Nov 03 12:19:06 2010
MMON started with pid=15, OS id=4194
ORACLE_BASE from environment = /u01/app/oracle
Wed Nov 03 12:19:08 2010
MMNL started with pid=16, OS id=4196
Wed Nov 03 12:19:54 2010
CREATE DATABASE den
LOGFILE GROUP 1 ('/u01/app/oracle/den/dbf/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/den/dbf/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/den/dbf/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/den/dbf/system01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/den/dbf/sysaux01.dbf' SIZE 500M
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u01/app/oracle/den/dbf/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/den/dbf/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Database mounted in Exclusive Mode
Lost write protection disabled
Wed Nov 03 12:20:06 2010
Successful mount of redo thread 1, with mount id 1862340970
Assigning activation ID 1862340970 (0x6f01116a)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/den/dbf/redo01.log
Successful open of redo thread 1
Wed Nov 03 12:20:07 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 03 12:20:07 2010
SMON: enabling cache recovery
processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile '/u01/app/oracle/den/dbf/system01.dbf' SIZE 500M

EXTENT MANAGEMENT LOCAL online
Wed Nov 03 12:20:41 2010
Completed: create tablespace SYSTEM datafile '/u01/app/oracle/den/dbf/system01.dbf' SIZE 500M

EXTENT MANAGEMENT LOCAL online
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/den/dbf/sysaux01.dbf' SIZE 500M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Wed Nov 03 12:21:18 2010
Completed: CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/den/dbf/sysaux01.dbf' SIZE 500M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/app/oracle/den/dbf/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/app/oracle/den/dbf/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u01/app/oracle/den/dbf/temp01.dbf'
SIZE 200M REUSE

Completed: CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u01/app/oracle/den/dbf/temp01.dbf'
SIZE 200M REUSE

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1
Errors in file /u01/app/oracle/diag/rdbms/den/den/trace/den_ora_4200.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name
Errors in file /u01/app/oracle/diag/rdbms/den/den/trace/den_ora_4200.trc:
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 20
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name
Error 1519 happened during database creation, shutting down database
USER (ospid: 4200): terminating the instance due to error 1519
Instance terminated by USER, pid = 4200
ORA-1092 signalled during: CREATE DATABASE den
LOGFILE GROUP 1 ('/u01/app/oracle/den/dbf/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/den/dbf/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/den/dbf/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/den/dbf/system01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/den/dbf/sysaux01.dbf' SIZE 500M
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u01/app/oracle/den/dbf/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/den/dbf/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED...
opiodr aborting process unknown ospid (4200) as a result of ORA-1092
Wed Nov 03 12:21:27 2010
ORA-1092 : opitsk aborting process
==========================================

"logs of den_ora_4200.trc"

Trace file /u01/app/oracle/diag/rdbms/den/den/trace/den_ora_4200.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: dbindia
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine: i686
Instance name: den
Redo thread mounted by this instance: 0 <none>
Oracle process number: 17
Unix process pid: 4200, image: oracle@dbindia (TNS V1-V3)


*** 2010-11-03 12:19:56.616
*** SESSION ID:(1.3) 2010-11-03 12:19:56.616
*** CLIENT ID:() 2010-11-03 12:19:56.616
*** SERVICE NAME:() 2010-11-03 12:19:56.616
*** MODULE NAME:(sqlplus@dbindia (TNS V1-V3)) 2010-11-03 12:19:56.616
*** ACTION NAME:() 2010-11-03 12:19:56.616

Control file created with size 580 blocks

*** 2010-11-03 12:21:25.648
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name
Offending statement at line 20 in file ?/rdbms/admin/dtxnspc.bsq
CREATE TABLESPACE TBS_1 DATAFILE SEGMENT SPACE MANAGEMENT MANUAL
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 20
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name

*** 2010-11-03 12:21:25.664
USER (ospid: 4200): terminating the instance due to error 1519
====================================================


Thanks & Regards
Deepak

Report message to a moderator

Re: manual creation of database [message #481539 is a reply to message #481529] Thu, 04 November 2010 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The answer was given by:
Mahesh Rajendran wrote on Wed, 03 November 2010 15:41
You need to include a datafile for default tablespace
or
Make use of DB_CREATE_FILE_DEST. With this setting, Oracle will create datafiles in location identified.


As you can see in the CREATE TABLESPACE above, data file is missing and so see Raj's message to fix your CREATE DATABASE statement accordingly.

Regards
Michel

[Updated on: Thu, 04 November 2010 01:18]

Report message to a moderator

Re: manual creation of database [message #481540 is a reply to message #481539] Thu, 04 November 2010 01:15 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Thanks for the reply SIR.

Regards
Deepak

Report message to a moderator

Re: manual creation of database [message #481566 is a reply to message #481540] Thu, 04 November 2010 05:27 Go to previous message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Thanks Everyone for your Effort. Mission Accomplished, database created successfully.

Regards
Deepak

Report message to a moderator

Previous Topic: ERROR IN INSTALLATION OF ORACLE 10g ON REDHAT 5.2
Next Topic: Increasing swap space
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 01 11:19:26 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.