Oracle 10g: Error when creating database manually

Question

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.

Answer 1

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.