使用命令列建立資料庫

TaihangMeng發表於2016-05-02
命令列資料庫
今天針對如何在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部落格 ” ，連結：http://blog.itpub.net/30776559/viewspace-2091852/，如需轉載，請註明出處，否則將追究法律責任。

相關文章