ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 使用命令行创建数据库

使用命令行创建数据库

原创 Oracle 作者：TaihangMeng 时间：2016-05-02 00:49:52 0 删除 编辑
今天针对如何在Linux操作系统上利用命令行创建Oracle Database进行一个总结及实践。
下面是官方文档“Database Administrator's Guide”中所讲的步骤。Step5针对的是Windows操作系统，本次实践不会涉及。

  1. Creating a Database with the CREATE DATABASE Statement
  2. Step 1: Specify an Instance Identifier (SID)
  3. Step 2: Ensure That the Required Environment Variables Are Set
  4. Step 3: Choose a Database Administrator Authentication Method
  5. Step 4: Create the Initialization Parameter File
  6. Step 5: (Windows Only) Create an Instance
  7. Step 6: Connect to the Instance
  8. Step 7: Create a Server Parameter File
  9. Step 8: Start the Instance
  10. Step 9: Issue the CREATE DATABASE Statement
  11. Step 10: Create Additional Tablespaces
  12. Step 11: Run Scripts to Build Data Dictionary Views
现在按照上面的步骤进行创建数据库。

Step 1: Specify an Instance Identifier (SID)



  1. [oracle@TaylonMeng ~]$ export ORACLE_SID=ZYX
  2. [oracle@TaylonMeng ~]$ echo $ORACLE_SID
  3. ZYX

注：此处ORACLE_SID应该与后续口令文件、参数文件的命名中的SID大小写保持一致，否则会在Step7报ORA-01078的错误。


  1. SQL> create spfile from pfile;
  2. create spfile from pfile
  3. *
  4. ERROR at line 1:
  5. ORA-01078: failure in processing system parameters
  6. LRM-00109: could not open parameter file
  7. '/oracle/product/11.2.0/dbs/initzyx.ora'

Step 2: Ensure That the Required Environment Variables Are Set

必须设定的几个变量有ORACLE_SID、ORACLE_HOME、PATH变量中包含ORACLE_HOME/bin目录。

  1. [oracle@TaylonMeng ~]$ echo $ORACLE_SID
  2. ZYX
  3. [oracle@TaylonMeng ~]$ echo $ORACLE_HOME
  4. /oracle/product/11.2.0
  5. [oracle@TaylonMeng ~]$ echo $PATH
  6. /oracle/product/11.2.0/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin

Step 3: Choose a Database Administrator Authentication Method

  • With a password file

  • With operating system authentication

这里我选择口令文件验证。所以要建立口令文件。口令文件的默认位置为$ORACLE_HOME/dbs，命名规则为 ORAPW<ORACLE_SID>

  1. [oracle@TaylonMeng ~]$ orapwd file=$ORACLE_HOME/dbs/orapwZYX password=oracle entries=30

 [oracle@TaylonMeng ~]$ cd $ORACLE_HOME/dbs
 [oracle@TaylonMeng dbs]$ ls
 hc_MTH.dat           initMTH.ora   lkMTH             orapwMTH   spfileMTH.ora
 hc_ORCL.dat          init.ora      lkORCL            orapwORCL  spfileORCL.ora
 initDBUA4624925.ora  initORCL.ora  orapwDBUA4624925  orapwZYX

Step 4: Create the Initialization Parameter File

在$ORACLE_HOME/dbs下，init.ora文件是pfile的模板，可以通过修改该文件的内容，生成所需要的pfile。文本参数文件的默认位置为$ORACLE_HOME/dbs，命名规则为 init<ORACLE_SID>.ora

  1. [oracle@TaylonMeng dbs]$ cat init.ora | grep -v ^# | grep -v ^$ > initZYX.ora
  2. [oracle@TaylonMeng dbs]$ vi initZYX.ora
  3. db_name='ORCL'
  4. memory_target=1G
  5. processes = 150
  6. audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
  7. audit_trail ='db'
  8. db_block_size=8192
  9. db_domain=''
  10. db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
  11. db_recovery_file_dest_size=2G
  12. diagnostic_dest='<ORACLE_BASE>'
  13. dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
  14. open_cursors=300
  15. remote_login_passwordfile='EXCLUSIVE'
  16. undo_tablespace='UNDOTBS1'
  17. control_files = (ora_control1, ora_control2)
  18. compatible ='11.2.0'
(1)使用‘zyx'替换’orcl',大小写都替换。

  1. :%s@orcl@zyx@gi
(2)用目录替换<ORACLE_BASE>

  1. :%s@<ORACLE_BASE>@/oracle/product@g
(3)修改control_files变量

  1. control_files = (/orasys/ZYX/control01.ctl,/oraidx/ZYX/control02.ctl )
(4)创建参数文件中所涉及到的目录

  1. [oracle@TaylonMeng dbs]$ mkdir -p /oracle/product/admin/zyx/adump
  2. [oracle@TaylonMeng dbs]$ mkdir -p /oracle/product/flash_recovery_area
  3. [oracle@TaylonMeng dbs]$ mkdir -p /orasys/ZYX/
  4. [oracle@TaylonMeng dbs]$ mkdir -p /oraidx/ZYX/
最后，经过修改的initZYX.ora的内容如下：

  1. [oracle@TaylonMeng dbs]$ more initZYX.ora
  2. db_name='zyx'
  3. memory_target=1G
  4. processes = 150
  5. audit_file_dest='/oracle/product/admin/zyx/adump'
  6. audit_trail ='db'
  7. db_block_size=8192
  8. db_domain=''
  9. db_recovery_file_dest='/oracle/product/flash_recovery_area'
  10. db_recovery_file_dest_size=2G
  11. diagnostic_dest='/oracle/product'
  12. dispatchers='(PROTOCOL=TCP) (SERVICE=zyxXDB)'
  13. open_cursors=300
  14. remote_login_passwordfile='EXCLUSIVE'
  15. undo_tablespace='UNDOTBS1'
  16. control_files = (/orasys/ZYX/control01.ctl,/oraidx/ZYX/control02.ctl )
  17. compatible ='11.2.0'

Step 6: Connect to the Instance


  1. [oracle@TaylonMeng ~]$ sqlplus sys as sysdba

  3. SQL*Plus: Release 11.2.0.4.0 Production on Sun May 1 18:53:30 2016

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  7. Enter password:
  8. Connected to an idle instance.

Step 7: Create a Server Parameter File


  1. SQL> create spfile from pfile;

  3. File created.
可以看到在$ORACLE_HOME/dbs下多了spfileZYX.ora这样一个文件。

  1. [oracle@TaylonMeng dbs]$ ls
  2. hc_MTH.dat init.ora lkORCL orapwZYX
  3. hc_ORCL.dat initORCL.ora orapwDBUA4624925 spfileMTH.ora
  4. initDBUA4624925.ora initZYX.ora orapwMTH spfileORCL.ora
  5. initMTH.ora lkMTH orapwORCL spfileZYX.ora

Step 8: Start the Instance

在启动实例的过程中，报MEMORY_TARGET设定的错误。我将MEMORY_TARGET的值由1G改为了700M，重新生成spfileZYX.ora，之后启动到nomount状态。
  1. SQL> startup nomount
  2. ORA-00845: MEMORY_TARGET not supported on this system
  3. [oracle@TaylonMeng dbs]$ vi initZYX.ora
    db_name='ZYX'
    memory_target=700M
    processes = 150
    audit_file_dest='/oracle/product/admin/zyx/adump'
    audit_trail ='db'
    db_block_size=8192
    db_domain=''
    db_recovery_file_dest='/oracle/product/flash_recovery_area'
    db_recovery_file_dest_size=2G
    diagnostic_dest='/oracle/product'
    dispatchers='(PROTOCOL=TCP) (SERVICE=zyxXDB)'
    open_cursors=300
    remote_login_passwordfile='EXCLUSIVE'
    undo_tablespace='UNDOTBS1'
    control_files = (/orasys/ZYX/control01.ctl,/oraidx/ZYX/control02.ctl )
    compatible ='11.2.0'
    [oracle@TaylonMeng dbs]$ sqlplus sys as sysdba


    SQL*Plus: Release 11.2.0.4.0 Production on Sun May 1 19:17:19 2016


    Copyright (c) 1982, 2013, Oracle.  All rights reserved.


    Enter password:


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> create spfile from pfile;

    File created.

    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area  730714112 bytes
    Fixed Size                  2256832 bytes
    Variable Size             478150720 bytes
    Database Buffers          243269632 bytes
    Redo Buffers                7036928 bytes

Step 9: Issue the CREATE DATABASE Statement

编辑一个CreateDB.sql文件，copy官方文档中的CREATE DATABASE语句，并做修改。

  1. [oracle@TaylonMeng ~]$ vi CreateDB.sql
  2. CREATE DATABASE ZYX
  3.    USER SYS IDENTIFIED BY sys_password
  4.    USER SYSTEM IDENTIFIED BY system_password
  5.    LOGFILE GROUP 1 ('/oraidx/ZYX/redo01a.log','/oralog/ZYX/redo01b.log') SIZE 100M BLOCKSIZE 512,
  6.            GROUP 2 ('/oraidx/ZYX/redo02a.log','/oralog/ZYX/redo02b.log') SIZE 100M BLOCKSIZE 512,
  7.            GROUP 3 ('/oraidx/ZYX/redo03a.log','/oralog/ZYX/redo03b.log') SIZE 100M BLOCKSIZE 512
  8.    MAXLOGFILES 5
  9.    MAXLOGMEMBERS 5
  10.    MAXLOGHISTORY 1
  11.    MAXDATAFILES 100
  12.    CHARACTER SET AL32UTF8
  13.    NATIONAL CHARACTER SET AL16UTF16
  14.    EXTENT MANAGEMENT LOCAL
  15.    DATAFILE '/orasys/ZYX/system01.dbf' SIZE 325M REUSE
  16.    SYSAUX DATAFILE '/orasys/ZYX/sysaux01.dbf' SIZE 325M REUSE
  17.    DEFAULT TABLESPACE users
  18.       DATAFILE '/orasys/ZYX/users01.dbf'
  19.       SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  20.    DEFAULT TEMPORARY TABLESPACE tempts1
  21.       TEMPFILE '/orasys/ZYX/temp01.dbf'
  22.       SIZE 20M REUSE
  23.    UNDO TABLESPACE undotbs1
  24.       DATAFILE '/orasys/ZYX/undotbs01.dbf'
  25.       SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
(1)用‘ZYX’替换原本的‘mynewdb'
(2)修改SYS和SYSTEM用户的密码
(3)修改logfile、SYSTEM、SYSAUX、users、tempts1、undotbs数据文件的路径
(4)修改undo表空间的名称为undotbs1，与之前参数文件中的undo表空间名称一致
(5)创建所涉及的目录
(6)根据自己实际情况，修改语句的其他内容

执行CreateDB.sql文件

  1. SQL> @/home/oracle/CreateDB.sql

  3. Database created.
此时数据库已经是open状态了。

  1. SQL> select open_mode,name from v$database;

  3. OPEN_MODE NAME
  4. -------------------- ---------
  5. READ WRITE ZYX

Step 11: Run Scripts to Build Data Dictionary Views


In SQL*Plus, connect to your Oracle Database instance with the SYSDBA administrative privilege:
  1. @?/rdbms/admin/catalog.sql
  2. @?/rdbms/admin/catproc.sql
  3. @?/rdbms/admin/utlrp.sql

In SQL*Plus, connect to your Oracle Database instance as SYSTEM user:
  1. @?/sqlplus/admin/pupbld.sql

下面是四个脚本各自的作用。
Script Description
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. GrantsPUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
UTLRP.SQL Recompiles all PL/SQL modules that are in an invalid state, including packages, procedures, and types.
PUPBLD.SQL Required for SQL*Plus. Enables SQL*Plus to disable commands by user.

自此，就成功使用命令行创建好了Oracle数据库。














来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30776559/viewspace-2091852/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： /bin/bash 和 /bin/ksh使用的用户配置文件的比较
下一篇： 【故障处理】ORA-00119: invalid specification for system parameter LOCAL_LISTENER
请登录后发表评论 登录
全部评论
TaihangMeng

注册时间：2016-01-07

  • 博文量
    101
  • 访问量
    147092

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593