Solved

running script

Posted on 2013-01-01
16
526 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 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
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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

821 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