Solved

running script

Posted on 2013-01-01
16
521 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
  • 4
  • 4
  • 3
  • +1
16 Comments
 
LVL 76

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 36

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
 
LVL 76

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 36

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now