手工创建DB实例
作为一个DBA，手动建库是必须要了解的，即便现在使用DBCA建库非常方便，但是了解建库的过程，有助于深入得理解数据库的原理和结构。当然有一个最好的方法，就是使用DBCA创建数据库模板后，自动生成建库脚本，然后研究脚本，理解建库的过程。如果需要精确的控制，只需要在原有脚本的基础上进行修改即可。因为有很多的参数是在DBCA中无法进行设置的。
1、安装配置说明：
OS环境：Windows XP PS2
Oracle：Oracle9I Release 9.2.0.1.0
Oracle_Home：D:\Oracle
2、手工创建相关目录：
(基本上参照之前的实例进行创建)
D:\Oracle\admin\kaka
D:\Oracle\admin\kaka\bdump
D:\Oracle\admin\kaka\cdump
D:\Oracle\admin\kaka\create
D:\Oracle\admin\kaka\pfile
D:\Oracle\admin\kaka\scripts
D:\Oracle\admin\kaka\udump
D:\Oracle\oradata\kaka
D:\Oracle\oradata\kaka\archive
3、创建初始化启动参数文件：
D:\Oracle\admin\kaka\pfile\initkaka.ora
内容如下：
--------------------------------------------------------------------------
###################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
###################################
# MTS
dispatchers="(PROTOCOL=TCP) (SERVICE=kakaXDB)"
# Optimizer
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
# Job Queues
job_queue_processes=10
# Instance Identification
instance_name=kaka
# Miscellaneous
compatible=9.2.0.0.0
# Security and Auditing
remote_login_passwordfile=EXCLUSIVE
# Sort, Hash Joins, Bitmap Indexes
pga_aggregate_target=25165824
sort_area_size=524288
# Database Identification
db_domain=""
db_name=kaka
# File Configuration
control_files=("D:\oracle\oradata\kaka\control01.ctl",
"D:\oracle\oradata\kaka\control02.ctl",
"D:\oracle\oradata\kaka\control03.ctl")
# Pools
java_pool_size=33554432
large_pool_size=8388608
shared_pool_size=50331648
# Cursors and Library Cache
open_cursors=300
# System Managed Undo and Rollback Segments
undo_management=AUTO
undo_retention=600
undo_tablespace=UNDOTBS01
# Diagnostics and Statistics
background_dump_dest=D:\oracle\admin\kaka\bdump
core_dump_dest=D:\oracle\admin\kaka\cdump
timed_statistics=TRUE
user_dump_dest=D:\oracle\admin\kaka\udump
# Processes and Sessions
processes=150
# Redo Log and Recovery
fast_start_mttr_target=300
# Cache and I/O
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
--------------------------------------------------------------------------
4、用orapwd命令创建密码文件：
> orapwd file=D:\Oracle\Ora92\DATABASE\PWDkaka.ora password=sys entries=5
5、oradim命令生成一个新的实例管理服务：
> set ORACLE_SID= kaka (也可以在注册表中修改，用set只修改当前会话)
> oradim -new -sid kaka -startmode manual -pfile "D:\Oracle\admin\ora92\pfile\initkaka.ora"
--生成一个名为OracleServicekaka的手动服务项
6、生成各种数据库对象：
> sqlplus /nolog
Conn sys/lovehoney as sysdba
--创建数据库
startup nomount pfile="D:\Oracle\admin\kaka\pfile\initkaka.ora"
CREATE DATABASE kaka
LOGFILE 'D:\Oracle\oradata\kaka\redo01.log' SIZE 2048K,
'D:\Oracle\oradata\kaka\redo02.log' SIZE 2048K,
'D:\Oracle\oradata\kaka\redo03.log' SIZE 2048K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'D:\Oracle\oradata\kaka\system01.dbf' SIZE 58M REUSE AUTOEXTEND ON NEXT 640K
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET UTF8;
控制文件、日志文件在上面语句执行时生成
7、在创建过程中出现的问题
在创建中遇到这个问题：
CREATE DATABASE kaka
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
查看了日志文件D:\oracle\admin\kaka\bdump\alert_kaka.log发现：
Mon Nov 10 16:54:06 2008
Errors in file d:\oracle\admin\kaka\udump\kaka_ora_288.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
修改了pfile文件中undo_tablespace=UNDOTBS，但是又出现错误：
Thu Nov 13 09:14:25 2008
Errors in file d:\oracle\admin\kaka\udump\kaka_ora_2228.trc:
ORA-01501: CREATE DATABASE failed
ORA-30045: No undo tablespace name specified
在创建数据库的语句中添加：
undo tablespace UNDOTBS01 datafile 'D:\Oracle\oradata\kaka\undotbs01.dbf' size 10m
问题解决.
另：关于NATIONAL CHARACTER
之前配置的NATIONAL CHARACTER SET ZHS16GBK 报错，后来改成UTF8后就不再报错了具体原因不详。
关于这个问题以后再研究一下。
8、创建数据字典：
使用sysdba登录数据库
SQL>start D:\oracle\ora92\rdbms\admin\catalog.sql;
....
SQL>start D:\oracle\ora92\rdbms\admin\catproc.sql;
....
创建用户
SQL>create user wangxiaoqi identified by wangxiaoqi
SQL>grant create session to wangxiaoqi;
SQL>grant dba to wangxiaoqi;
登录是发现警告：
SQL
> conn wangxiaoqi/wangxiaoqi
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
需要使用SYSTEM用户登录，再执行PUPBLD.SQL
SQL
> conn system/manager
Connected.
SQL> start D:\oracle\ora92\sqlplus\admin\pupbld.sql;
SQL
> conn wangxiaoqi/wangxiaoqi
Connected.
9、创建连接字符串
KAKA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hhz-0099)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kaka)
)
)
到这里就差不多完成了，好累啊，记得下次要是想让他自动启动的话，还要配置SPFILE或者PFILE……