?今天學習了小布老師的手工建庫視頻,自己也做了一遍,下面是創建過程記錄:
本地環境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
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
