0

My situation is that I have to create a new Oracle database using only scripts (so please do not advice me using DBCA)

My script is as below:

CreateDB.bat

set ORACLE_SID=testdb
oradim -new -sid %ORACLE_SID% -intpwd test -startmode M
copy init.ora D:\oracle\product\10.2.0\db_1\database\inittestdb.ora
sqlplus sys/test as sysdba @D:\Script\CreateDB.sql

==> "init.ora" is the file I copy from the DB created using DBCA

CreateDB.sql

CREATE SPFILE='D:\oracle\product\10.2.0\db_1\dbs\spfiletestdb.ora' FROM
       PFILE='D:\oracle\product\10.2.0\db_1\database\inittestdb.ora';
startup nomount PFILE='D:\oracle\product\10.2.0\db_1\database\inittestdb.ora';
create database testdb
  logfile   group 1 ('D:\oracle\product\10.2.0\oradata\testdb\redo1.log') size 100M,
            group 2 ('D:\oracle\product\10.2.0\oradata\testdb\redo2.log') size 100M,
            group 3 ('D:\oracle\product\10.2.0\oradata\testdb\redo3.log') size 100M
  character set          JA16SJIS
  national character set AL16UTF16
  datafile 'D:\oracle\product\10.2.0\oradata\testdb\system01.dbf' 
            size 500M
            autoextend on 
            next 100M maxsize unlimited
            extent management local
  sysaux datafile 'D:\oracle\product\10.2.0\oradata\testdb\sysaux01.dbf' 
            size 100M
            autoextend on 
            next 100M 
            maxsize unlimited
  DEFAULT TABLESPACE tbs_1
            DATAFILE 'D:\oracle\product\10.2.0\oradata\testdb\tbs01.dbf'
            SIZE 200M REUSE
  DEFAULT temporary TABLESPACE tempts1
            tempfile 'D:\oracle\product\10.2.0\oradata\testdb\temp01.dbf'
            SIZE 200M REUSE
  undo tablespace undotbs
            datafile 'D:\oracle\product\10.2.0\oradata\testdb\undotbs01.dbf'
            SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

The above return error [ORA-30012: undo tablespace 'UNDOTBS01.DBF' does not exist or of wrong type].

At first, as i check the init.ora file, I found out that the parameter for UNTO tablespace have different name for UNDO file, so I change it to match the file name in my script.

Old:

undo_management=AUTO
undo_tablespace=UNDOTBS1.DBF

New:

undo_management=AUTO
undo_tablespace=UNDOTBS01.DBF

and delete the database (yes, even when error, the database is still created, just cannot be used) and re-run everthing again, and same error occurs.

I have research for 2 days and still cannot find any solution.

All the script above is following the guide on Oracle website: http://docs.oracle.com/cd/B19306_01/server.102/b14231/create.htm

The OS is Windows Server 2003.

Database is Oracle 10g Standard Edition.

UPDATED 1:

Thank Mat, UNDO tablespace problem is solved :D

Now, next error pop-up.

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Wed May 21 09:11:38 2014
Errors in file d:\oracle\product\10.2.0\admin\testdb\udump\testdb_ora_3416.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name

However, it is actually due to the missing of datafile option for [DEFAULT TABLESPACE tbs_1].

I have update the CREATE script above.

UPDATED 2:

A new problem occurred when I create a user for this database. I have created a new topic for it on this link: Oracle 10g: ORA-12154 when connect to database with newly created user

Any help will be appreciated.

1 Answer 1

2

The undo_tablespace parameter takes the name of the tablespace, not of the datafile.

You should have

undo_tablespace=UNDOTBS

in your parameter file to match your database creation script.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.