02_如何創(chuàng)建Oracle數(shù)據(jù)庫(kù)(手動(dòng)執(zhí)行CreateDataBase命令創(chuàng)建)

1、方少ORACLE DBA之路如何創(chuàng)建Oracle數(shù)據(jù)庫(kù)(手工創(chuàng)建)在上一章里，方少演示了如何利用DBCA來(lái)創(chuàng)建ORACLE數(shù)據(jù)庫(kù)，但是在這一章里方少要告訴大家如何在命令行下通過(guò)執(zhí)行CREATE DATABASE命令來(lái)創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，之所以演示命令行手工創(chuàng)建數(shù)據(jù)庫(kù)給大家是因?yàn)樵贠CM的實(shí)驗(yàn)考試?yán)锩媸遣辉试S用DBCA來(lái)建庫(kù)的。要?jiǎng)?chuàng)建一個(gè)數(shù)據(jù)庫(kù)，必須按照正確的順序執(zhí)行以下步驟：(1) 創(chuàng)建一個(gè)參數(shù)文件和口令文件。(2) 使用創(chuàng)建的參數(shù)文件在內(nèi)存中構(gòu)建一個(gè)實(shí)例。(3) 執(zhí)行 CREATE DATABASE 命令。(4) 運(yùn)行一系列SQL腳本生成數(shù)據(jù)字典視圖和補(bǔ)充的PL/SQL程序包。(5) 運(yùn)行相應(yīng)S

2、QL腳本生成 Enterprise Manager Database Control以及該數(shù)據(jù)庫(kù)需要的任 何選項(xiàng)。下面，我們就按照上面的步驟創(chuàng)建一個(gè)名為ORCL的數(shù)據(jù)庫(kù)。在創(chuàng)建參數(shù)文件之前，我們得先創(chuàng)建一些orci數(shù)據(jù)庫(kù)要用到的目錄：mkdir -pORACLE_BASE/admin/orcl/adumpmkdir -pORACLE_BASE/admin/orcl/bdumpmkdir -pORACLE_BASE/admin/orcl/cdumpmkdir -pORACLE_BASE/admin/orcl/dpdumpmkdir -pORACLE_BASE/admin/orcl/udumpmk

3、dir -pORACLE_BASE/admin/orcl/pfilemkdir -pORACLE_BASE/oradata/orclmkdir pORACLE_BASE/flash_recovery_areaexport ORACLE_SID=orcl接下來(lái)，我們要在/etc/oratab里加入如下所示的一條記錄orcl:/u01/app/oracle:Y下面，我們要?jiǎng)?chuàng)建口令文件：orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=10 force=yoracXe(3OCP10g *5 orapwdUsage: orap

4、wd file=<fname> password=<password> entries=<users> force=<y/n>wherefile - name of password file (mand). password - password for SYS (mand)F entries - max丄muni number of d丄Et丄net DBA and force - whether to overwrit e existing file (opt)r OPERs (opt),There are no spaces around

5、 the equal-to (=) character.在Oracle中，所有參數(shù)被分為"基本”參數(shù)和"高級(jí)”參數(shù)。通常，我們需要設(shè)置20多個(gè)“基本”參數(shù)。啟動(dòng)參數(shù)文件init.ora應(yīng)與下面給出的代碼相似：If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If

6、If If If If If If If If If If If IfII II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II# Copyright (c) 1991, 2001,2002 by Oracle Co

7、rporationIf If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If IfII II II II II II II II II II II II II II II II II II II II II II II I

8、I II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II方少 ORACLE DBA 之路#II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II IIa a

9、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Cache and I/Oa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a adb_block_size=8192 db_file_multibl

10、ock_read_count=16a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Cursors and Library Cachea a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa

11、 a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aopen_cursors=300a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# DatabaseIdentificationa a a

12、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a adb_domain="" db_name=orcla a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a

13、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Diagnostics and Statisticsa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a background_dump_dest=/u01/app/ad

14、min/orcl/bdump core_dump_dest=/u01/app/admin/orcl/cdump user_dump_dest=/u01/app/admin/orcl/udumpa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# File Configurationa a a a a a a

15、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a acontrol_files=("/u01/app/oradata/orcl/control01.ctl", "/u01/app/oradata/orcl/control02.ctl","/u01/app/oradata/orcl/cont

16、rol03.ctl") db_recovery_file_dest=/u01/app/flash_recovery_area db_recovery_file_dest_size=2147483648a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Job Queuesa a a a a a a

17、 a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a ajob_queue_processes=10a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a

18、a a a a a a a a a a a a a a a a a a a a a a a a a# Miscellaneousa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a acompatible=10.2.0.1.0方少 ORACLE DBA 之路II II II II II II II II II II

19、 II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II IIa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# NLSa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a

20、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a anls_territory="AMERICA"a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Processesand Sessionsa a a a a a a a

21、 a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aprocesses=150a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a

22、 a a a a a a a a a a a a a a a a a a a# SGA Memorya a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a asga_target=205520896a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a

23、a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Security and Auditinga a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a

24、a aaudit_file_dest=/u01/app/admin/orcl/adump remote_login_passwordfile=EXCLUSIVEa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Shared Servera a a a a a a a a a a a a a a a a a

25、 a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a adispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a

26、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# Sort, Hash Joins, Bitmap Indexesa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a apga_aggregate_target=68157440a a a

27、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# System Managed Undo and Rollback Segmentsa a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aa a a a

28、a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a aundo_management=AUTO undo_tablespace=UNDOTBS1準(zhǔn)備好 init.ora 參數(shù)文件之后，我們就要用它在內(nèi)存中構(gòu)建一個(gè)實(shí)例方少ORACLE DBA之路好了，實(shí)例已經(jīng)啟動(dòng)成功，接下來(lái)要做的就是創(chuàng)建數(shù)據(jù)庫(kù)啦。1 CREATE DATABASE "orcl"2 MAXINSTANCES g3 HAXLOGHISTORY 14 HAXLDGFILES 165 MAXLOGMEMBERS 36

29、 MAXDATAFILES 1067 datafile '/yei/pp/oradata/orci/systemeiB' size 3een autoextend on next iqm maxsize unlim ITED8 EXTENT MANAGEMENT LOCAL9 SYSAUX DATAFILE '/uOl/app/oradata/Drcl/sysauxeidbf1 SIZE 206M AUTOEXTEND ON NEXT 10M MAXSIZ E UNLIMITED19 SMALL FILE DEFAULT TEMPO-RARY TABLESPACE TE

30、MP TEMPFILE ' /uO 1 / app/ o rad a t a/o rc 1/1 em pG 1. d b f1 SIZE 2GM AUTOEXTEND ON NEXT 5M IWCSIZE UNLIMITED11 SMALLFILE UNDO TABLESPACE UND0TBS1 DATAFILE '/u01/app/oradata/orel/undotbs01rdbf1 SIZE 50M A UTOEXTEND ON NEXT 10M IWCSIZE UNLIMITED12 CHAFWCTER SET AL32UTF813 NATIONAL CHARACTE

31、R SET AL16U(TF1614 LOGFILE GROUP 1 ( p/U61/app/oraclata/orcl/rei1ci01,log') SIZE 59MR15 GROUP 2 ( /U01/app/oradata/orcl/redo02.log") SIZE 59肌16 右加LIP 3 ( Vudl/app/Dradata/Drcl/redoOS Aog') SIZE 5&M17 USER SYS IDENTIFIED BY oracle18* USER SYSTEM IDENTIFIED oracle5QLa /Database create

32、d.SQL> select open mode froim vsdatabase;OPEN_MODEREAD WRITESQL>至此，數(shù)據(jù)庫(kù)已經(jīng)成功創(chuàng)建，接下來(lái)要做的就是運(yùn)行一系列SQL腳本以生成數(shù)據(jù)字典視圖和PL/SQL程序包。在這之前，我們還要再做一件事，那就是為數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)默認(rèn)的用戶表空間USERS。方少ORACLE DBA之路下面以SYS用戶連接數(shù)據(jù)庫(kù)并執(zhí)行以下SQL文件中的代碼:$ORACLE_HOME/rdbms/admin/ catalog.sql$ORACLE_HOME/rdbms/admin/ catblock.sql$ORACLE_HOME/rdbms/adm

33、in/ catproc.sql$ORACLE_HOME/rdbms/admin/ catoctk.sql $ORACLE_HOME/rdbms/admin/ owminst.plbSQL> conn sys/oracle as sysdba Connected SQL> 0$ORACLE_HOME/rdbms/admin/catalog,sqVSQL> 0SORACLE_HOME/rdbms/admin/catbl.ockt5qtSOL> ORACLE HOME/rdbms/admin/cat proc , sqlSQL> 0SORACLE_ HOME/rdbms

34、/admln/catoctK.sqlSQL> ORACLE HOME/rdbms/admin/owminst.plb以SYSTEM用戶連接數(shù)據(jù)庫(kù)并執(zhí)行以下 SQL文件：$ORACLE_HOME/sqlplus/admi n/pupbld.sql$ORACLE_HOME/sqlplus/admi n/help/hlpbld.sqlhelpus.sql以SYS用戶登錄并執(zhí)行以下 SQL腳本：$ORACLE_HOME/javavm/in stall/in itjvm.sql; $ORACLE_HOME/xdk/admi n/in itxml.sql; $ORACLE_HOME/xdk/admi

35、 n/xmlja.sql; $ORACLE_HOME/rdbms/admi n/catjava.sql;方少 ORACLE DBA 之路$ORACLE_HOME/rdbms/admin/catexf.sql;以 SYS 用戶連接并執(zhí)行以下： $ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP; $ORACLE_HOME/ctx/admin/catctx change_on_install SYSAUX TEMP NOLOCK; connect "CTXSYS"/"change_on_install"$ORACL

36、E_HOME/ctx/admin/defaults/dr0defin.sql "AMERICAN"以 SYS 用戶連接并執(zhí)行以下： $ORACLE_HOME/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP; connect / as SYSDBA$ORACLE_HOME/rdbms/admin/catxdbj.sql; $ORACLE_HOME/rdbms/admin/catrul.sql;以 SYS 用戶連接并執(zhí)行以下： $ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;以 SYS 用戶連接并執(zhí)行以下： $ORACLE_HOME/ord/im/admin/iminst.sql;以 SYS 用戶連接并執(zhí)行以下： $ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;以 SYS 用戶連接并執(zhí)行以下： $ORACL