?今天學習了小布老師的手工建庫視頻，自己也做了一遍，下面是創建過程記錄：

本地環境oracle10.2.0.1

一.前期準備工作

? ?1.設置環境變量? ? ? ? ?

[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參數

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.創建文件目錄

? ? 在/u01/app/oracle/admin/下創建空的bbk、bbk/*dump 幾個目錄

? ? 在/u01/app/oracle/oradata/下創建空的bbk目錄

?

4.創建建庫腳本

? 建庫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
;

?

二.建庫過程

1.創建密碼文件

[oracle@app dbs]$ orapwd file=orapwbbk password=oracle entries=10

?

2.生成spfile

SQL> create spfile from pfile='bbk.ora';

File created.

?

3.建庫 ? ??

[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.初始化數據庫系統

在上面db.sql執行成功后，執行下面的系統腳本

SQL>@?/rdbms/admin/catalog.sql?? ?--創建系統的數據字典腳本

SQL>@?/rdbms/admin/catproc.sql ? ?--創建pl/sql需要的環境腳本

SQL>@?/rdbms/admin/pupbld.sql ? ?--創建sql*plus需要的環境腳本

我上面的這3個腳本是參照9i的，其實前2個是必須要執行的。

?

如果腳本執行沒有報錯，那么至此手工數據庫創建完成；重啟數據庫查看一下即可。

?

三.建庫過程中的問題處理

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里面的書寫錯誤：datafile 應該是 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

?

--出現這個錯誤的原因，可能是在Oracle10.2.0.1中，這幾個表空間的extent管

理必須是local方式的，這樣，在創建時就不可指定extent參數。例如初始安裝后，

查詢如下：
SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from user_tablespaces;
TABLESPACE_NAME EXTENT_MANAGEMENT
-----------------------------------
SYSTEM?????????? LOCAL
UNDO???????????? LOCAL
SYSAUX?????????? LOCAL
TEMP???????????? LOCAL
USERS??????????? LOCAL

?

4.如果執行db.sql 過程中失敗了

SQL> @$ORACLE_HOME/dbs/db.sql
create database "bbk"
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

你需要重新連接，并且把已經創建的數據文件手工刪除。?

?

5.如果執行系統腳本出錯

?

SQL> @?/rdbms/admin/catalog.sql ?? ?-- 創建系統的數據字典腳本

SQL>@?/rdbms/admin/catproc.sql ? ?--創建pl/sql需要的環境腳本

SQL>@?/rdbms/admin/pupbld.sql ? ?--創建sql*plus需要的環境腳本

（最后這個腳本是小布老師9i上說要執行的，其實非必須執行。但是上面2個是必須要執行的）

?

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...

?

----解決辦法：

若重新啟動數據庫時報錯ORA-01092?可以使用如下方法解決：?
SQL>shutdown?immediate?

SQL>?startup?upgrade?
SQL>@?/rdbms/admin/catupgrd.sql?
然后重新啟動數據庫即可。?

如果執行catupgrd.sql?過程中有錯誤（

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 ）；完成后仍然無法正常啟動，則這個腳本不可用，換其他兩個腳本執行?

SQL>?startup?upgrade?
SQL>@?/rdbms/admin/catalog.sql?

SQL>@?/rdbms/admin/catproc.sql?然后重啟數據庫即可。

?

四.測試數據庫

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

?

Oracle:手工建庫