【这是我第一次建数据库的日志】
今天为了手工建一个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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15415488/viewspace-539757/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15415488/viewspace-539757/