Avatar of walkerdba
walkerdba
 asked on

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;
Oracle Database

Avatar of undefined
Last Comment
YZlat

8/22/2022 - Mon
slightwv (䄆 Netminder)

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?
Geert G

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

walkerdba

ASKER
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>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
YZlat

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

Geert G

what is in your initNEWDATA.ora file ?
walkerdba

ASKER
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>
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
YZlat

first delete all the old files that might have been already created and then run the script again
YZlat

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
slightwv (䄆 Netminder)

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
walkerdba

ASKER
Sorry I noticed the mistakes I will keep in mind...

Thank you ..
slightwv (䄆 Netminder)

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
YZlat

slightwv is right, he should have had his answer accepted
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.