手工創(chuàng)建DB實(shí)例
?
??? 作為一個(gè)DBA，手動(dòng)建庫(kù)是必須要了解的，即便現(xiàn)在使用DBCA建庫(kù)非常方便，但是了解建庫(kù)的過(guò)程，有助于深入得理解數(shù)據(jù)庫(kù)的原理和結(jié)構(gòu)。當(dāng)然有一個(gè)最好的方法，就是使用DBCA創(chuàng)建數(shù)據(jù)庫(kù)模板后，自動(dòng)生成建庫(kù)腳本，然后研究腳本，理解建庫(kù)的過(guò)程。如果需要精確的控制，只需要在原有腳本的基礎(chǔ)上進(jìn)行修改即可。因?yàn)橛泻芏嗟膮?shù)是在DBCA中無(wú)法進(jìn)行設(shè)置的。
?
1、安裝配置說(shuō)明：
?
OS環(huán)境：Windows XP PS2
Oracle：Oracle9I Release 9.2.0.1.0
Oracle_Home：D:\Oracle
?
?
2、手工創(chuàng)建相關(guān)目錄：
??? (基本上參照之前的實(shí)例進(jìn)行創(chuàng)建)
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、創(chuàng)建初始化啟動(dòng)參數(shù)文件：
?
D:\Oracle\admin\kaka\pfile\initkaka.ora
?
內(nèi)容如下：
--------------------------------------------------------------------------
###################################
# 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命令創(chuàng)建密碼文件：
?
> orapwd file=D:\Oracle\Ora92\DATABASE\PWDkaka.ora password=sys entries=5
?
?
5、oradim命令生成一個(gè)新的實(shí)例管理服務(wù)：
?
> set ORACLE_SID= kaka (也可以在注冊(cè)表中修改，用set只修改當(dāng)前會(huì)話)?
> oradim -new -sid kaka -startmode manual -pfile "D:\Oracle\admin\ora92\pfile\initkaka.ora"
?
--生成一個(gè)名為OracleServicekaka的手動(dòng)服務(wù)項(xiàng)
?
?
6、生成各種數(shù)據(jù)庫(kù)對(duì)象：
> sqlplus /nolog
?
Conn sys/lovehoney as sysdba
?
--創(chuàng)建數(shù)據(jù)庫(kù)
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;
?
控制文件、日志文件在上面語(yǔ)句執(zhí)行時(shí)生成
?
?
7、在創(chuàng)建過(guò)程中出現(xiàn)的問(wèn)題
?
在創(chuàng)建中遇到這個(gè)問(wèn)題：
?
CREATE DATABASE kaka
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
?
查看了日志文件D:\oracle\admin\kaka\bdump\alert_kaka.log發(fā)現(xiàn)：
?
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，但是又出現(xiàn)錯(cuò)誤：
?
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
?
在創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句中添加：
?
undo tablespace UNDOTBS01 datafile 'D:\Oracle\oradata\kaka\undotbs01.dbf' size 10m
?
問(wèn)題解決.
?
?
另：關(guān)于NATIONAL CHARACTER
?
之前配置的NATIONAL CHARACTER SET ZHS16GBK 報(bào)錯(cuò)，后來(lái)改成UTF8后就不再報(bào)錯(cuò)了具體原因不詳。
關(guān)于這個(gè)問(wèn)題以后再研究一下。
?
?
8、創(chuàng)建數(shù)據(jù)字典：
?
使用sysdba登錄數(shù)據(jù)庫(kù)
?
SQL>start D:\oracle\ora92\rdbms\admin\catalog.sql;
....
SQL>start D:\oracle\ora92\rdbms\admin\catproc.sql;
....
?
創(chuàng)建用戶
?
SQL>create user wangxiaoqi identified by wangxiaoqi
?
SQL>grant create session to wangxiaoqi;
?
SQL>grant dba to wangxiaoqi;
?
登錄是發(fā)現(xiàn)警告：
?
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用戶登錄，再執(zhí)行PUPBLD.SQL
?
SQL
> conn system/manager
Connected.
SQL> start D:\oracle\ora92\sqlplus\admin\pupbld.sql;
?
SQL
> conn wangxiaoqi/wangxiaoqi
Connected.
?
?
9、創(chuàng)建連接字符串
?
KAKA =
? (DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = hhz-0099)(PORT = 1521))
??? )
??? (CONNECT_DATA =
????? (SERVER = DEDICATED)
????? (SERVICE_NAME = kaka)
??? )
? )
?
?
?
到這里就差不多完成了，好累啊，記得下次要是想讓他自動(dòng)啟動(dòng)的話，還要配置SPFILE或者PFILE……
?
?
?
?