Oracle 10g:通过脚本手动创建数据库时出错 [英] Oracle 10g: Error when creating database manually by scripts
问题描述
我的情况是我必须仅使用脚本来创建一个新的Oracle数据库(所以请不要使用DBCA建议我)
My situation is that I have to create a new Oracle database using only scripts (so please do not advice me using DBCA)
我的脚本如下:
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"是我从使用DBCA创建的数据库中复制的文件
==> "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;
上述返回错误[ORA-30012:撤消表空间'UNDOTBS01.DBF'不存在或类型错误.
The above return error [ORA-30012: undo tablespace 'UNDOTBS01.DBF' does not exist or of wrong type].
首先，当我检查init.ora文件时，我发现UNTO表空间的参数具有与UNDO文件不同的名称，因此我将其更改为与脚本中的文件名匹配.
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.
旧:
undo_management=AUTO
undo_tablespace=UNDOTBS1.DBF
新功能:
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.
我研究了2天，仍然找不到任何解决方案.
I have research for 2 days and still cannot find any solution.
以上所有脚本均遵循Oracle网站上的指南: http://docs.oracle.com/cd/B19306_01/server.102/b14231/create.htm
All the script above is following the guide on Oracle website: http://docs.oracle.com/cd/B19306_01/server.102/b14231/create.htm
操作系统是Windows Server 2003.
The OS is Windows Server 2003.
数据库是Oracle 10g标准版.
Database is Oracle 10g Standard Edition.
更新1:
感谢Mat，UNDO表空间问题已解决:D
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
但是，这实际上是由于缺少[DEFAULT TABLESPACE tbs_1]的datafile选项所致.
However, it is actually due to the missing of datafile option for [DEFAULT TABLESPACE tbs_1].
我已经更新了上面的CREATE脚本.
I have update the CREATE script above.
更新2:
当我为此数据库创建用户时发生了一个新问题. 我在此链接上为此创建了一个新主题: Oracle 10g:连接时为ORA-12154使用新创建的用户访问数据库
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
任何帮助将不胜感激.
推荐答案
undo_tablespace参数采用表空间的名称，而不是数据文件的名称.
The undo_tablespace parameter takes the name of the tablespace, not of the datafile.
您应该拥有
undo_tablespace=UNDOTBS
在参数文件中
匹配数据库创建脚本.
in your parameter file to match your database creation script.
这篇关于Oracle 10g:通过脚本手动创建数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
