02_如何创建Oracle数据库(手动执行CreateDataBase命令创建)

1、方少ORACLE DBA之路如何创建Oracle数据库(手工创建)在上一章里，方少演示了如何利用DBCA来创建ORACLE数据库，但是在这一章里方少要告诉大家如何在命令行下通过执行CREATE DATABASE命令来创建一个数据库，之所以演示命令行手工创建数据库给大家是因为在OCM的实验考试里面是不允许用DBCA来建库的。要创建一个数据库，必须按照正确的顺序执行以下步骤：(1) 创建一个参数文件和口令文件。(2) 使用创建的参数文件在内存中构建一个实例。(3) 执行 CREATE DATABASE 命令。(4) 运行一系列SQL脚本生成数据字典视图和补充的PL/SQL程序包。(5) 运行相应S

2、QL脚本生成 Enterprise Manager Database Control以及该数据库需要的任 何选项。下面，我们就按照上面的步骤创建一个名为ORCL的数据库。在创建参数文件之前，我们得先创建一些orci数据库要用到的目录：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接下来，我们要在/etc/oratab里加入如下所示的一条记录orcl:/u01/app/oracle:Y下面，我们要创建口令文件：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中，所有参数被分为"基本”参数和"高级”参数。通常，我们需要设置20多个“基本”参数。启动参数文件init.ora应与下面给出的代码相似：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准备好 init.ora 参数文件之后，我们就要用它在内存中构建一个实例方少ORACLE DBA之路好了，实例已经启动成功，接下来要做的就是创建数据库啦。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>至此，数据库已经成功创建，接下来要做的就是运行一系列SQL脚本以生成数据字典视图和PL/SQL程序包。在这之前，我们还要再做一件事，那就是为数据库创建一个默认的用户表空间USERS。方少ORACLE DBA之路下面以SYS用户连接数据库并执行以下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用户连接数据库并执行以下 SQL文件：$ORACLE_HOME/sqlplus/admi n/pupbld.sql$ORACLE_HOME/sqlplus/admi n/help/hlpbld.sqlhelpus.sql以SYS用户登录并执行以下 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 用户连接并执行以下： $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 用户连接并执行以下： $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 用户连接并执行以下： $ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;以 SYS 用户连接并执行以下： $ORACLE_HOME/ord/im/admin/iminst.sql;以 SYS 用户连接并执行以下： $ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;以 SYS 用户连接并执行以下： $ORACL