Solved

running script

Posted on 2013-01-01
16
527 Views
Last Modified: 2013-01-11
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;
0
Comment
Question by:walkerdba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
16 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38735026
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38735034
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
 

Author Comment

by:walkerdba
ID: 38736163
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 38736345
>>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
 
LVL 35

Expert Comment

by:YZlat
ID: 38736435
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38738348
what is in your initNEWDATA.ora file ?
0
 

Author Comment

by:walkerdba
ID: 38738971
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
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 38739345
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
 
LVL 35

Expert Comment

by:YZlat
ID: 38740109
first delete all the old files that might have been already created and then run the script again
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38740117
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740129
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
 

Author Comment

by:walkerdba
ID: 38741637
Sorry I noticed the mistakes I will keep in mind...

Thank you ..
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38741648
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
 
LVL 35

Expert Comment

by:YZlat
ID: 38741914
slightwv is right, he should have had his answer accepted
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question