第一次手工建数据库-CSDN博客

【这是我第一次建数据库的日志】

今天为了手工建一个database，搞到现在。。。

几乎遇到了所有的情况。。。现特此转载一篇我觉得还是比较完整的网上文摘说明在unix下手工建立database的步骤，最后，再谈谈自己的tips：

zz开始：

[oracle@netpower oracle]$ echo $ORACLE_HOME
/opt/ora9/product/9.2
[oracle@netpower oracle]$ echo $ORACLE_BASE
/opt/ora9
[oracle@netpower oracle]$ export ORACLE_SID=mwhdata
[oracle@netpower oracle]$ echo $ORACLE_SID
mwhdata
#建立操作系统目录,用于存放第二个ORACLE9I数据库物理文件.
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/admin/$ORACLE_SID
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/admin/$ORACLE_SID/bdump
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/admin/$ORACLE_SID/cdump
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/admin/$ORACLE_SID/create
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/admin/$ORACLE_SID/pfile
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/admin/$ORACLE_SID/udump
[oracle@netpower oracle]$ mkdir $ORACLE_BASE/oradata/$ORACLE_SID [oracle@netpower oracle]$ ls $ORACLE_BASE/admin/$ORACLE_SID
bdump cdump create pfile udump #建立密码文件.
#切换到$ORACLE_HOME/bin目录,执行orapwd指令建立密码文件,文件名为orapwmwhdata,密码为chinaunix:
[oracle@netpower bin]$ orapwd file=$ORACLE_HOME/dbs/orapwmwhdata password=chinaunix entries=5 #建立初始参数文件.
#将原SID为ora9i的数据库的初始参数文件复制到$ORACLE_BASE/admin/mwhdata/pfile目录下,并?命名为initmwhdata.ora.
[oracle@netpower bin]$cd /opt/ora9/admin/ora9i/pfile
[oracle@netpower pfile]$ cp initora9i.ora.32200662355 $ORACLE_BASE/admin/mwhdata/pfile/initmwhdata.ora
#编辑新的初始化文件,将文件中所有的ora9i(原来的SID)替代为mwhdata(新的SID).
[oracle@netpower pfile]$ vi initmwhdata.ora ##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
############################################################################## ###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=33554432
db_file_multiblock_read_count=16 ###########################################
# Cursors and Library Cache
###########################################
open_cursors=300 ###########################################
# Database Identification
###########################################
db_domain=""
db_name=mwhdata ###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/opt/ora9/admin/mwhdata/bdump
core_dump_dest=/opt/ora9/admin/mwhdata/cdump
timed_statistics=TRUE
user_dump_dest=/opt/ora9/admin/mwhdata/udump ###########################################
# File Configuration
###########################################
control_files=("/opt/ora9/oradata/mwhdata/control01.ctl", "/opt/ora9/oradata/mwhdata/
control02.ctl", "/opt/ora9/oradata/mwhdata/control03.ctl") ###########################################
# Instance Identification
###########################################
instance_name=mwhdata ###########################################
# Job Queues
###########################################
job_queue_processes=10 ###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=mwhdataXDB)" ###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0 ###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE ###########################################
# Pools
###########################################
java_pool_size=83886080
large_pool_size=16777216
shared_pool_size=83886080 ###########################################
# Processes and Sessions
###########################################
processes=150 ###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300 ###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE ###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288 ###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1 [oracle@netpower pfile]$ export ORACLE_SID=mwhdata #在/home/oracle目录下编辑建立数据库的CreateDB.sql脚本文件.
[oracle@netpower oracle]$ vi CreateDB.sql
create database mwhdata
maxinstances 1
maxloghistory 1
maxlogfiles 5
maxlogmembers 5
maxdatafiles 100
datafile '/opt/ora9/oradata/mwhdata/system01.dbf' size 420M reuse autoextend on next
10240K maxsize 32767M
extent management local
default temporary tablespace
temp tempfile '/opt/ora9/oradata/mwhdata/temp01.dbf' size 40M reuse autoextend on nex
t 640K maxsize 32767M
undo tablespace "undotbs1"
datafile '/opt/ora9/oradata/mwhdata/undotbs01.dbf' size 200M reuse autoextend on next
5129K maxsize 32767M
character set ZHS16CGB231280
national character set AL16UTF16
logfile group 1 ('/opt/ora9/oradata/mwhdata/redo01.log') size 102400K,
        group 2 ('/opt/ora9/oradata/mwhdata/redo02.log') size 102400K,
        group 3 ('/opt/ora9/oradata/mwhdata/redo03.log') size 102400K; #启动ORACLE9I执行项次.
#建立初始参数文件后,必须先启动ORACLE9I执行项次才能建立数据库.
#先以/nolog选项启动SQL*Plus,再用SYS(密码为chinaunix)帐号以SYSDBA身份登录ORACLE9I.
#执行STARTUP NOMOUNT指令启动ORACLE9I执行项次,此时必须在STARTUP指令后面加上PFILE选项,
#以指定初始参数文件的位置. [oracle@netpower pfile]$ sqlplus /nolog SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 8 16:06:14 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> conn sys/chinaunix as sysdba
Connected to an idle instance. SQL> startup nomount pfile=$ORACLE_BASE/admin/mwhdata/pfile/initmwhdata.ora
ORACLE instance started. Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes #用存放在/home/oracle目录下的编辑好的CreateDB.sql脚本创建新的数据库.
SQL> @/home/oracle/CreateDB.sql Database created.
#成功建立数据库后,ORACLE9I会自动将数据库开启到OPEN状态.
#现执行SHUTDOWN指令将数据库关闭,然后执行EXIT指令离开SQL*PLUS,将数据库重新启动.
#这一次STARTUP指令只提供PFILE选项,直接将数据库开启至OPEN状态.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@netpower pfile]$ sqlplus /nolog SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 8 16:06:14 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> conn sys/chinaunix as sysdba
Connected to an idle instance. SQL> startup pfile=$ORACLE_BASE/admin/mwhdata/pfile/initmwhdata.ora
ORACLE instance started. Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened. #执行下列指令可以确认目前的ORACLE9I执行项次名称:
SQL> select instance_name from v$instance; INSTANCE_NAME
----------------
mwhdata
##########建立其他表空间###########
#在/home/oracle目录下编辑CreateDBFiles.sql脚本. create tablespace "indx" logging
datafile '/opt/ora9/oradata/mwhdata/indx01.dbf' size 50M reuse autoextend on next 1280K maxsize 32767M blocksize 8192
extent management local
segment space management auto;
create tablespace "tools" logging
datafile '/opt/ora9/oradata/mwhdata/tools01.dbf' size 5M reuse autoextend on next 320K maxsize 32767M blocksize 8192
extent management local
segment space management auto;
create tablespace "users" logging
datafile '/opt/ora9/oradata/mwhdata/users01.dbf' size 100M reuse autoextend on next 1280K maxsize 32767M blocksize 8192
extent management local
segment space management auto;
#执行CreateDBFiles.sql脚本.
SQL> @/home/oracle/CreateDBFiles.sql
Tablespace created.
Tablespace created.
Tablespace created. #建立数据字典视图,成功执行这两个脚本后,ORACLE9I数据库的建立就算完成了.
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql;
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;

zz完毕

对于我个人这次经验来说，有几点要特别注意：

1.最开始进入时要source .login查看环境变量，看看一共有几个database在服务器上；然后vi .login添加自己将要建立的database的环境变量。

2.进入自己的环境变量中，用上文提到的echo命令查看ORACLE_SID等重要环境变量是否正确。若错误，用export 导入。

3.建立几个必要文件夹，例如bdump，udump，cdump。注意后面建立database失败，一定要rm -r删除这些文件夹，然后重建才行。

4.千万别忘了用orapwd建立密码文件！！！我就是载在这上面的！

5.initSID.ora文件建立

6.CreateDB.sql脚本写好，例如今天我写的文件为：

connect / as SYSDBA
set echo on
spool /export/home/oracle/products/10203/dbs/haozhuCreate.log
startup nomount pfile="/export/home/oracle/products/10203/dbs/inithaozhu.ora";
CREATE DATABASE haozhu
MAXINSTANCES 18
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oracle/GBPGW/home/admin/haozhu/bdump/system.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY
TABLESPACE TEMP TEMPFILE '/oracle/GBPGW/home/admin/haozhu/bdump/temp.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs01
DATAFILE '/oracle/GBPGW/home/admin/haozhu/bdump/undotbs01.dbf' SIZE 40M
REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oracle/GBPGW/home/admin/haozhu/bdump/log01.rdo') SIZE 10M,
GROUP 2 ('/oracle/GBPGW/home/admin/haozhu/bdump/log02.rdo') SIZE 10M;
spool off

7.CreateDB很有可能会失败，这时一定要看alert文件，知道错在什么地方！

8.CreateDB成功后，关闭，退出，再进入，open状态，@CreateCatalog.sql和@CreateCatProc.sql，这样系统视图建好就大功告成了！！

恩，当时就是这样！

2007.8.21补充///

10G的创建有点不一样~~~~：

注意sysaux的建立；

需要用的脚本是：

connect "SYS"/"&&sysPassword" as SYSDBA
set termout off
set echo on
spool /home/oracle/createdb/CreateDBCatalog.log
@/home/oracle/product/10g/rdbms/admin/catalog.sql;
@/home/oracle/product/10g/rdbms/admin/catblock.sql;
@/home/oracle/product/10g/rdbms/admin/catproc.sql;
@/home/oracle/product/10g/rdbms/admin/catoctk.sql;
@/home/oracle/product/10g/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/home/oracle/product/10g/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /home/oracle/createdb/sqlPlusHelp.log
@/home/oracle/product/10g/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off