?今天學(xué)習(xí)了小布老師的手工建庫(kù)視頻，自己也做了一遍，下面是創(chuàng)建過(guò)程記錄：
本地環(huán)境oracle10.2.0.1
一.前期準(zhǔn)備工作
? ?1.設(shè)置環(huán)境變量? ? ? ? ?
[oracle@app dbs]$ vi bbk.env
[oracle@app dbs]$ cat bbk.env
ORACLE_SID=bbk
[oracle@app dbs]$ . ./bbk.env
[oracle@app dbs]$ env |grep ORA
ORACLE_SID=bbk
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
[oracle@app dbs]$ sqlplus / as sysdba
?
2.建立bbk.ora pfile參數(shù)
processes = 150
sga_target = 599785472
control_files = ('/u01/app/oracle/oradata/bbk/control01.ctl', '/u01/app/oracle/oradata/bbk/control02.ctl', '/u01/app/oracle/oradata/bbk/control03.ctl')
db_block_size = 8192
compatible = '10.2.0.1.0'
db_file_multiblock_read_count= 16
db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size= 2147483648
undo_management = 'AUTO'
undo_tablespace = 'UNDOTBS1'
remote_login_passwordfile= 'EXCLUSIVE'
db_domain =''
dispatchers = '(PROTOCOL=TCP) (SERVICE=bbkXDB)'
job_queue_processes = 10
background_dump_dest = '/u01/app/oracle/admin/bbk/bdump'
user_dump_dest = '/u01/app/oracle/admin/bbk/udump'
core_dump_dest = '/u01/app/oracle/admin/bbk/cdump'
audit_file_dest = '/u01/app/oracle/admin/bbk/adump'
db_name = 'bbk'
open_cursors = 300
pga_aggregate_target = 199229440
?
3.創(chuàng)建文件目錄
? ? 在/u01/app/oracle/admin/下創(chuàng)建空的bbk、bbk/*dump 幾個(gè)目錄
? ? 在/u01/app/oracle/oradata/下創(chuàng)建空的bbk目錄
?
4.創(chuàng)建建庫(kù)腳本
? 建庫(kù)db.sql腳本：
create database "bbk"
maxdatafiles 500
maxinstances 8
maxlogfiles 32
character set "UTF8"
national character set AL16UTF16
archivelog
datafile '/u01/app/oracle/oradata/bbk/system01.dbf' size 500M
sysaux datafile '/u01/app/oracle/oradata/bbk/sysaux01.dbf' size 200M
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/bbk/temp01.dbf' size 50M
undo tablespace "undotbs1" datafile '/u01/app/oracle/oradata/bbk/undotbs01.dbf' size 100M
logfile
group 1('/u01/app/oracle/oradata/bbk/redo01.rdo')size 50M,
group 2('/u01/app/oracle/oradata/bbk/redo02.rdo')size 50M,
group 3('/u01/app/oracle/oradata/bbk/redo03.rdo')size 50M
;
?
二.建庫(kù)過(guò)程
1.創(chuàng)建密碼文件
[oracle@app dbs]$ orapwd file=orapwbbk password=oracle entries=10
?
2.生成spfile
SQL> create spfile from pfile='bbk.ora';
File created.
?
3.建庫(kù) ? ??
[oracle@app dbs]$ env |grep ORA
ORACLE_SID=bbk
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
[oracle@app dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 27 11:12:51 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2022632 bytes
Variable Size 167772952 bytes
Database Buffers 427819008 bytes
Redo Buffers 2170880 bytes
SQL> @$ORACLE_HOME/dbs/db.sql
Database created.
?
4.初始化數(shù)據(jù)庫(kù)系統(tǒng)
在上面db.sql執(zhí)行成功后，執(zhí)行下面的系統(tǒng)腳本
SQL>@?/rdbms/admin/catalog.sql?? ?--創(chuàng)建系統(tǒng)的數(shù)據(jù)字典腳本
SQL>@?/rdbms/admin/catproc.sql ? ?--創(chuàng)建pl/sql需要的環(huán)境腳本
SQL>@?/rdbms/admin/pupbld.sql ? ?--創(chuàng)建sql*plus需要的環(huán)境腳本
我上面的這3個(gè)腳本是參照9i的，其實(shí)前2個(gè)是必須要執(zhí)行的。
?
如果腳本執(zhí)行沒(méi)有報(bào)錯(cuò)，那么至此手工數(shù)據(jù)庫(kù)創(chuàng)建完成；重啟數(shù)據(jù)庫(kù)查看一下即可。
?
三.建庫(kù)過(guò)程中的問(wèn)題處理
1.SQL> @$ORACLE_HOME/dbs/db.sql
create database "bbk"
*
ERROR at line 1:
ORA-13504: No SYSAUX datafile clause specified
?
10G的db.sql里面必須要有sysaux tablespace的定義。
?
2.SQL> @$ORACLE_HOME/dbs/db.sql
datafile temporary tablespace temp tempfile 'u01/app/oracle/oradata/bbk/temp01.dbf' size 50M
*
ERROR at line 11:
ORA-02164: DATAFILE clause specified more than once
?
db.sql里面的書寫錯(cuò)誤：datafile 應(yīng)該是 default
?
3.SQL> @$ORACLE_HOME/dbs/db.sql
create database "bbk"
*
ERROR at line 1:
ORA-25146: EXTENT MANAGEMENT option already specified
?
之前表空間的定義后面是帶了?? datafile '/u01/app/oracle/oradata/bbk/system01.dbf' size 100M extent management local
?
--出現(xiàn)這個(gè)錯(cuò)誤的原因，可能是在Oracle10.2.0.1中，這幾個(gè)表空間的extent管
理必須是local方式的，這樣，在創(chuàng)建時(shí)就不可指定extent參數(shù)。例如初始安裝后，
查詢?nèi)缦拢?
SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from user_tablespaces;
TABLESPACE_NAME EXTENT_MANAGEMENT
-----------------------------------
SYSTEM?????????? LOCAL
UNDO???????????? LOCAL
SYSAUX?????????? LOCAL
TEMP???????????? LOCAL
USERS??????????? LOCAL
?
4.如果執(zhí)行db.sql 過(guò)程中失敗了
SQL> @$ORACLE_HOME/dbs/db.sql
create database "bbk"
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
你需要重新連接，并且把已經(jīng)創(chuàng)建的數(shù)據(jù)文件手工刪除。?
?
5.如果執(zhí)行系統(tǒng)腳本出錯(cuò)
?
SQL> @?/rdbms/admin/catalog.sql ?? ?-- 創(chuàng)建系統(tǒng)的數(shù)據(jù)字典腳本
SQL>@?/rdbms/admin/catproc.sql ? ?--創(chuàng)建pl/sql需要的環(huán)境腳本
SQL>@?/rdbms/admin/pupbld.sql ? ?--創(chuàng)建sql*plus需要的環(huán)境腳本
（最后這個(gè)腳本是小布老師9i上說(shuō)要執(zhí)行的，其實(shí)非必須執(zhí)行。但是上面2個(gè)是必須要執(zhí)行的）
?
SQL> startup
ORACLE instance started.
?
Total System Global Area 599785472 bytes
Fixed Size 2022632 bytes
Variable Size 184550168 bytes
Database Buffers 411041792 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
?
SQL> startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
查看了日志
?
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Sun Oct 27 21:03:57 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 5074
ORA-1092 signalled during: ALTER DATABASE OPEN...
?
----解決辦法：
若重新啟動(dòng)數(shù)據(jù)庫(kù)時(shí)報(bào)錯(cuò)ORA-01092?可以使用如下方法解決：?
SQL>shutdown?immediate?
SQL>?startup?upgrade?
SQL>@?/rdbms/admin/catupgrd.sql?
然后重新啟動(dòng)數(shù)據(jù)庫(kù)即可。?
如果執(zhí)行catupgrd.sql?過(guò)程中有錯(cuò)誤（
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade re-run not supported from version
ORA-06512: at "SYS.VERSION_SCRIPT", line 45
）；完成后仍然無(wú)法正常啟動(dòng)，則這個(gè)腳本不可用，換其他兩個(gè)腳本執(zhí)行?
SQL>?startup?upgrade?
SQL>@?/rdbms/admin/catalog.sql?
SQL>@?/rdbms/admin/catproc.sql?然后重啟數(shù)據(jù)庫(kù)即可。
?
四.測(cè)試數(shù)據(jù)庫(kù)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
?
SQL> startup
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2022632 bytes
Variable Size 192938776 bytes
Database Buffers 402653184 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
?
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
bbk OPEN
SQL> create table x2(id int,name varchar2(10));
Table created.
SQL> insert into x2 values(1,'223');
1 row created.
SQL> commit?;
Commit complete.
SQL> select * from x2;
ID NAME
---------- ----------
1 223
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
