• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

error manual creation

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL> @/home/oracle/Desktop/dat.sql
 DATAFILE ‘/u01/app/oracle/oradata/dat/system01.dbf’ SIZE 100M
          *
ERROR at line 7:
ORA-02236: invalid file name


SQL>



here is the create database script file

dat.sql


initdat.ora
is


dat.__db_cache_size=704643072
dat.__java_pool_size=16777216
dat.__large_pool_size=16777216
dat.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dat.__pga_aggregate_target=687865856
dat.__sga_target=1023410176
dat.__shared_io_pool_size=0
dat.__shared_pool_size=268435456
dat.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dat/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dat/control01.ctl','/u01/app/oracle/flash_recovery_area/dat/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='dat'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=datXDB)'
*.memory_target=1698693120
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
0
tomvv
Asked:
tomvv
  • 11
  • 8
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Those look like smart quotes.  Try single quotes:

'/u01/app/oracle/oradata/dat/system01.dbf' SIZE 100M

Also make sure the folder exists.
0
 
tomvvAuthor Commented:
now the error is

SQL> @/home/oracle/Desktop/dat.sql
 UNDO TABLESPACE UNDOTBS DATAFILE ‘/u01/app/oracle/oradata/dat/UNDOTBS1.dbf’ SIZE 150M REUSE       AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
                                  *
ERROR at line 9:
ORA-02236: invalid file name


SQL>
0
 
slightwv (䄆 Netminder) Commented:
Looks like smart quotes again...  you need to rpelace them all.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tomvvAuthor Commented:
here is dat.sql

CREATE DATABASE dat
 LOGFILE GROUP 1('/u01/app/oracle/oradata/dat/redo01.log') SIZE 5M,  
 GROUP 2('/u01/app/oracle/oradata/dat/redo02.log') SIZE 5M,
 GROUP 3('/u01/app/oracle/oradata/dat/redo03.log') SIZE 5M
 CHARACTER SET US7ASCII
 NATIONAL CHARACTER SET AL16UTF16
 DATAFILE '/u01/app/oracle/oradata/dat/system01.dbf’ SIZE 100M
 SYSAUX DATAFILE ‘/u01/app/oracle/oradata/dat/sysaux01.dbf' size 100M
 UNDO TABLESPACE UNDOTBS DATAFILE ‘/u01/app/oracle/oradata/dat/UNDOTBS1.dbf’ SIZE 150M REUSE       AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
0
 
slightwv (䄆 Netminder) Commented:
I've noticed your previous questions.  Can I ask why you cannot use the DB Creation Assistant (DBCA)?
0
 
slightwv (䄆 Netminder) Commented:
>>here is dat.sql

Replace all the quotes with single quotes.  There are a few in there that are not normal single quotes.
0
 
tomvvAuthor Commented:
here is the new error

SQL> startup nomount
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL> @/home/oracle/Desktop/dat.sql
CREATE DATABASE dat
*
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 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 13945
Session ID: 1 Serial number: 3


SQL>
0
 
slightwv (䄆 Netminder) Commented:
>>ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

Check your pfile against what you created.  They need to match.  The file name doesn't matter.  It is the tablespace name that does.

UNDO TABLESPACE UNDOTBS DATAFILE ‘/u01/app/oracle/oradata/dat/UNDOTBS1.dbf’ SIZE 150M

*.undo_tablespace='UNDOTBS1'
0
 
slightwv (䄆 Netminder) Commented:
I'm still curious why you cannot use dbca?
0
 
tomvvAuthor Commented:
I am doing this for some personal purpose.. with the dbca database is ready in minutes...

this is the error just now..

SQL> @/home/oracle/Desktop/dat.sql
CREATE DATABASE dat
*
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 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 7212
Session ID: 1 Serial number: 3


SQL>
0
 
slightwv (䄆 Netminder) Commented:
Manually remove any exising files that might have been already created in previous attempts.

Especially: /u01/app/oracle/oradata/dat/UNDOTBS1.dbf
0
 
tomvvAuthor Commented:
I am cleaning all that files every time I run the script (create database)

also in flash_recovery area..location
0
 
slightwv (䄆 Netminder) Commented:
I'm not seeing the issue.  I'll try to get some time later today to try your script on my system to see if I can find the issue.

One last thing to try as a test:  remove the UNDO tablespace line from the create script and rerun it.  Maybe there is an issue somewhere else in the script and the error you are getting is misleading.
0
 
tomvvAuthor Commented:
I followed what you said..

here is the new script  with out that...


CREATE DATABASE dat
 LOGFILE GROUP 1('/u01/app/oracle/oradata/dat/redo01.log') SIZE 5M,  
 GROUP 2('/u01/app/oracle/oradata/dat/redo02.log') SIZE 5M,
 GROUP 3('/u01/app/oracle/oradata/dat/redo03.log') SIZE 5M
 CHARACTER SET US7ASCII
 NATIONAL CHARACTER SET AL16UTF16
 DATAFILE '/u01/app/oracle/oradata/dat/system01.dbf' SIZE 100M
 SYSAUX DATAFILE '/u01/app/oracle/oradata/dat/sysaux01.dbf' size 100M
 AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;



error is


SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL> @/home/oracle/Desktop/dat.sql
CREATE DATABASE dat
*
ERROR at line 1:
ORA-30045: No undo tablespace name specified


SQL>
0
 
slightwv (䄆 Netminder) Commented:
Do you still have smart quotes in your create script?

Here is a working test case on my system (Windows) based on your setup.  The only difference should be the PATH names.  I'm running 10.2.0.3 but it should work fine on 11.2.

my init:
control_files='c:\junkdb\control01.ctl'
db_block_size=8192
db_name='junkdb'
undo_tablespace='UNDOTBS1'
undo_management='AUTO'

Open in new window


my create script:
CREATE DATABASE junkdb
 LOGFILE GROUP 1('c:\junkdb\redo01.log') SIZE 5M,
 	 GROUP 2('c:\junkdb\redo02.log') SIZE 5M
 CHARACTER SET US7ASCII
 NATIONAL CHARACTER SET AL16UTF16
 DATAFILE 'c:\junkdb\system01.dbf' SIZE 50M
 SYSAUX DATAFILE 'c:\junkdb\sysaux01.dbf' size 50M  AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 UNDO TABLESPACE UNDOTBS1 DATAFILE 'c:\junkdb\UNDOTBS1.dbf' SIZE 50M;

Open in new window



My actual output:
SQL> startup nomount pfile='c:\junkdb\initjunkdb.ora';
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1289196 bytes
Variable Size              58721300 bytes
Database Buffers           50331648 bytes
Redo Buffers                2904064 bytes
SQL> CREATE DATABASE junkdb
  2   LOGFILE GROUP 1('c:\junkdb\redo01.log') SIZE 5M,
  3      GROUP 2('c:\junkdb\redo02.log') SIZE 5M
  4   CHARACTER SET US7ASCII
  5   NATIONAL CHARACTER SET AL16UTF16
  6   DATAFILE 'c:\junkdb\system01.dbf' SIZE 50M
  7   SYSAUX DATAFILE 'c:\junkdb\sysaux01.dbf' size 50M  AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  8   UNDO TABLESPACE UNDOTBS1 DATAFILE 'c:\junkdb\UNDOTBS1.dbf' SIZE 50M;

Database created.

Open in new window

0
 
tomvvAuthor Commented:
At last made it..
yours was windows still it helped mine is linux


This was the script I used
Thanks a lot for your help..

CREATE DATABASE dat
 LOGFILE GROUP 1('/u01/app/oracle/oradata/dat/redo01.log') SIZE 5M,  
 GROUP 2('/u01/app/oracle/oradata/dat/redo02.log') SIZE 5M,
 GROUP 3('/u01/app/oracle/oradata/dat/redo03.log') SIZE 5M
 CHARACTER SET US7ASCII
 NATIONAL CHARACTER SET AL16UTF16
 DATAFILE '/u01/app/oracle/oradata/dat/system01.dbf' SIZE 50M
 SYSAUX DATAFILE '/u01/app/oracle/oradata/dat/sysaux01.dbf' SIZE 50M
 UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/dat/UNDOTBS1.dbf' SIZE 50M
 AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED ;
0
 
slightwv (䄆 Netminder) Commented:
Glad you finally got there.  Was it smart quotes?
0
 
tomvvAuthor Commented:
seriously I do not know the terminology smart quotes..
0
 
slightwv (䄆 Netminder) Commented:
Smart quotes are a general term for non-standard quotes.  Oracle needs the good old-fashioned single (straight) quotes.

http://en.wikipedia.org/wiki/Quotation_mark_glyphs

Curved and straight quotes are also sometimes referred to as smart quotes (“…”) and dumb quotes ("…") respectively; these names are in reference to the name of a function found in several word processors that automatically converts straight quotes typed by the user into curved quotes. This function, known as “educating quotes”, was developed for systems that lack separate open- and close-quote keyboard keys.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now