running script

I tried to run the create database script

SQL> @/home/oracle/Desktop/newdata.sql;

SQL>

It is not working any syntax errors....




CREATE DATABASE "newdata"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newdata/redo1.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/newdata/redo2.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/newdata/redo3.log' SIZE 10M
DATAFILE
'/u01/app/oracle/oradata/newdata/system01.dbf' size 100m,
'/u01/app/oracle/oradata/newdata/user01.dbf'size 10m
sysaux datafile '/u01/app/oracle/oradata/newdata/sysaux01.dbf' size 100m
undo tablespace undotbs
datafile '/u01/app/oracle/oradata/newdata/undotbs01.dbf' size 50m
CHARACTER SET US7ASCII;
walkerdbaAsked:
Who is Participating?
 
Geert GConnect With a Mentor Oracle dbaCommented:
look at comment again from slightvw: ID: 38736345

from your init:
*.undo_tablespace='UNDOTBS1'

from your create database script:
undo tablespace undotbs
datafile '/u01/app/oracle/oradata/newdata/undotbs01.dbf' size 50m

the names of the undo tablespace are different
0
 
slightwv (䄆 Netminder) Commented:
First, you should never use double quotes on Oracle object names.

Second, what are you trying to do with the user01.dbf file?  I think there is an issue there based on the name of the file.

What are you seeing?  Does the script actually run?  Are the files created?
0
 
Geert GOracle dbaCommented:
what error are you getting ?

your datafile and tablespace looks wrong

you are trying to create a users tablespace
after the first datafile word, only create the system tablespace
http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_5004.htm#i2142339

you don't need a users tablespace created in the beginning
create it after that script has run

you don't have a temp tablespace ...

CREATE DATABASE "newdata"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newdata/redo1.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/newdata/redo2.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/newdata/redo3.log' SIZE 10M
DATAFILE
'/u01/app/oracle/oradata/newdata/system01.dbf' size 100m
sysaux datafile '/u01/app/oracle/oradata/newdata/sysaux01.dbf' size 100m
undo tablespace undotbs
datafile '/u01/app/oracle/oradata/newdata/undotbs01.dbf' size 50m
CHARACTER SET US7ASCII; 

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
walkerdbaAuthor Commented:
The error now is

SQL> @/home/oracle/Desktop/newdata.sql
CREATE DATABASE "newdata"
*
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: 4622
Session ID: 1 Serial number: 3


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

Check the pfile/spfile you created.  Your script uses UNDOTBS.  Looks like the init parameters isn't.
0
 
YZlatCommented:
try this

CREATE DATABASE "newdata"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newdata/redo1.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/newdata/redo2.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/newdata/redo3.log' SIZE 10M
DATAFILE '/u01/app/oracle/oradata/newdata/system01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/newdata/sysaux01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/newdata/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/newdata/undotbs01.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET US7ASCII; 

Open in new window

0
 
Geert GOracle dbaCommented:
what is in your initNEWDATA.ora file ?
0
 
walkerdbaAuthor Commented:
Here is the initnewdata.ora
newdata.__db_cache_size=4194304
newdata.__java_pool_size=4194304
newdata.__large_pool_size=16777216
newdata.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
newdata.__pga_aggregate_target=687865856
newdata.__sga_target=1023410176
newdata.__shared_io_pool_size=0
newdata.__shared_pool_size=71303168
newdata.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/newdata/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/newdata/control01.ctl','/u01/app/oracle/flash_recovery_area/newdata/control02.ctl'
*.db_block_size=8192
*.db_cache_size=4194304
*.db_domain=''
*.db_name='newdata'
*.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=newdataXDB)'
*.java_pool_size=4194304
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=71303168
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/usr/tmp'









This is the new error
SQL> startup nomount
ORACLE instance started.

Total System Global Area   83697664 bytes
Fixed Size                  1334660 bytes
Variable Size              75498108 bytes
Database Buffers            4194304 bytes
Redo Buffers                2670592 bytes
SQL> @/home/oracle/Desktop/newdata.sql
CREATE DATABASE "newdata"
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file:
'/u01/app/oracle/flash_recovery_area/newdata/control02.ctl'
ORA-27038: created file already exists
Additional information: 1


SQL>
0
 
YZlatCommented:
first delete all the old files that might have been already created and then run the script again
0
 
YZlatCommented:
looks like when you first ran your script, the installation errored out but some files were created, that's why you get an erro "file already exists" when you ran the installation again
0
 
slightwv (䄆 Netminder) Commented:
You can also specify REUSE for the controlfiles.  This will overwrite the file if it exists.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#ADMIN11287

If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE clause in the CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from the SIZE parameter of the new one, you cannot use the REUSE clause.
0
 
walkerdbaAuthor Commented:
Sorry I noticed the mistakes I will keep in mind...

Thank you ..
0
 
slightwv (䄆 Netminder) Commented:
walkerdba,

Can I ask why my post wasn't accepted since it was referenced/repeated in the post you accepted?

I pointed this out in my post: http:#a38736345
0
 
YZlatCommented:
slightwv is right, he should have had his answer accepted
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.