【這是我第一次建資料庫的日誌】

今天為了手工建一個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

第一次手工建資料庫

相關文章