开门见山:
一次偶然的机会看到以下语句:
- CREATE DATABASE ORCL
-
- USER SYS IDENTIFIED BY oracle
- USER SYSTEM IDENTIFIED BY oracle
- SET DEFAULT BIGFILE TABLESPACE
- LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/disk1/redo01a.log',
- '/u01/app/oracle/oradata/orcl/disk2/redo01b.log') SIZE 100M BLOCKSIZE 512,
- GROUP 2 ('/u01/app/oracle/oradata/orcl/disk2/redo02a.log',
- '/u01/app/oracle/oradata/orcl/disk3/redo02b.log') SIZE 100M BLOCKSIZE 512,
- GROUP 3 ('/u01/app/oracle/oradata/orcl/disk3/redo03a.log',
- '/u01/app/oracle/oradata/orcl/disk4/redo03b.log') SIZE 100M BLOCKSIZE 512
- MAXLOGFILES 50
- MAXLOGMEMBERS 5
- MAXLOGHISTORY 1
- MAXDATAFILES 300 -- Maybe you should think about it. 300 is not enough to a huge DB.
- CHARACTER SET ZHS16GBK
- NATIONAL CHARACTER SET AL16UTF16
- EXTENT MANAGEMENT LOCAL
- DATAFILE '/u01/app/oracle/oradata/orcl/disk1/system01.dbf' SIZE 500M REUSE autoextend on
- SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/disk2/sysaux01.dbf' SIZE 325M REUSE autoextend on
- DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/orcl/disk4/users01.dbf'
- SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
- smallfile DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/orcl/disk5/temp01.dbf'
- SIZE 20M REUSE autoextend on
- UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/orcl/disk3/undotbs1.dbf'
- SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
-
由于要准备OCM考试,顺手拿来练练手工建库。结果并未成功:
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 20 ORA-00604: error occurred at recursive SQL level 1 ORA-32772: BIGFILE is invalid option for this type of tablespace Process ID: 22927 Session ID: 1 Serial number: 3 |
A. 查看 ?/rdbms/admin/dtxnspc.bsq 第20行
# 创建永久表空间,说明已经到创建system表空间的时候报错,不支持BIGFILE???
# 经 cn.bing.com 搜索发现有人声称 system,sysaux 表空间不支持 bigfile tablespace( 没有官方声明连接,保留意见 )。
# 继续搜索发现一博客,用实验展示如何使用『set default bigfile tablespace』手工建库。
# 依样画葫芦,居然成功了。事实证明system, sysaux表空间不支持 bigfile tablespace的说法为臆断。
B. 分析,排错
# 认真分析成功的例子,使用了 db_create_file_dest 参数所以建库语句非常简洁( 保留,以便证实 )
# 将失败语句改装成OMF风格,再来,还是失败!
- create database PROD1
- user sys identified by oracle
- user system identified by oracle
- logfile group 1 size 50m,
- group 2 size 50m
- maxlogmembers 5
- maxloghistory 1
- maxlogfiles 50
- maxdatafiles 1000
- character set zhs16gbk
- national character set al16utf16
- set default bigfile tablespace
- extent management local
- datafile size 400m
- sysaux datafile size 300m
- default tablespace users
- smallfile default temporary tablespace temp1 tempfile size 100m
- undo tablespace undotbs1;
# (生产环境需要11g+rawdevice,11g已经不让在界面上使用rawdevice )的过程。
# 关键点是『 default tablespace users 』在dbca所产生的scripts目录中创建 users 表空间的语句,\
# 并非出现在 CreateDB.sql 中而是建库结束后单独一个文件出现
# 尝试删除 default tablespace users 那一行,再执行,成功。
C. 总结,猜想
# 从此次经历看来,由于 Create database 的内部机理所限,对非关键表空间的考虑似乎并不到位( 个人猜测未证实过 )。
# 然而,如果执行 Create Database 语句 成功后立刻马上创建 users 表空间是可以成功的。
# 综上所述,我们不妨把 default tablespace users 放在 Create database 语句的最后一行试试
- create database PROD1
- user sys identified by oracle
- user system identified by oracle
- logfile group 1 size 50m,
- group 2 size 50m
- maxlogmembers 5
- maxloghistory 1
- maxlogfiles 50
- maxdatafiles 1000
- character set zhs16gbk
- national character set al16utf16
- set default bigfile tablespace
- extent management local
- datafile size 400m
- sysaux datafile size 300m
- smallfile default temporary tablespace temp1 tempfile size 100m
- undo tablespace undotbs1
- default tablespace users
- SQL> col tablespace_name for a40 ;
- SQL> select bytes/1024/1024 f_size, tablespace_name from dba_data_files;
-
-
- F_SIZE TABLESPACE_NAME
- ---------- ----------------------------------------
- 400 SYSTEM
- 300 SYSAUX
- 180 UNDOTBS1
- 100 USERS
-
- 4 rows selected.
-
- SQL> select bytes/1024/1024 f_size, tablespace_name from dba_temp_files;
-
-
- F_SIZE TABLESPACE_NAME
- ---------- ----------------------------------------
- 100 TEMP1
-
- 1 row selected.
洋洋得意一下:
想起那名老话:实践出真知!
另外,不放弃,才有可能成功!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25521690/viewspace-2060379/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25521690/viewspace-2060379/