手動建立資料庫過程

1、設定環境變數：

[oracle@enmo ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=PROD

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

編輯後，使其生效：

. .bash_profile

2、建立密碼檔案（作用：允許遠端用密碼方式以sysdba身份登陸資料庫，可有可無）：

[oracle@enmo ~]$ cd $ORACLE_HOME/dbs

[oracle@enmo dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@enmo dbs]$ ls

init.ora  init.ora.bck

3、生成密碼檔案：

[oracle@enmo dbs]$ orapwd file=orapwPROD password=oracle

[oracle@enmo dbs]$ ls

init.ora  init.ora.bck  orapwPROD

[oracle@enmo dbs]$

4、生成pfile檔案：

[oracle@enmo dbs]$ cat init.ora |grep -v ^#|grep -v ^$ > initPROD.ora

[oracle@enmo dbs]$ ls

init.ora  init.ora.bck  initPROD.ora  orapwPROD

5、修改pfile檔案：

[oracle@enmo dbs]$ vi initPROD.ora

db_name='PROD'

memory_target=800M

processes = 150

audit_file_dest='/u01/app/oracle/admin/prod/adump'

#audit_trail ='db'

db_block_size=8192

db_domain='oracle.com'

#db_recovery_file_dest='/flash_recovery_area'

#db_recovery_file_dest_size=2G

#diagnostic_dest=''

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = (/u01/app/oracle/oradata/PROD/ora_control1.ctl,/u01/app/oracle/oradata/PROD/ora_control2.ctl)

compatible ='11.2.0'

6、根據pfile建立目錄adump目錄和控制檔案目錄：

[oracle@enmo ~]$  mkdir -p $ORACLE_BASE/admin/prod/adump

[oracle@enmo ~]$  mkdir -p $ORACLE_BASE/oradata/PROD/

7、透過pfile建立spfile：

[oracle@enmo dbs]$ ls

init.ora  init.ora.bck  initPROD.ora  orapwPROD

[oracle@enmo dbs]$ echo $ORACLE_SID

PROD

[oracle@enmo dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 5 23:29:53 2016

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> !ls

init.ora  init.ora.bck  initPROD.ora  orapwPROD  spfilePROD.ora

8、資料庫啟動到nomount（預設情況下，oracle會使用spfile啟動資料庫）：

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             541068368 bytes

Database Buffers          289406976 bytes

Redo Buffers                2371584 bytes

SQL> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /dbhome_1/dbs/spfilePROD.ora

SQL> select status from v$instance;

STATUS

------------

STARTED

 

9、手工建庫指令碼：
create database prod
   user sys identified by oracle
   user system identified by oracle
   logfile group 1('/u01/app/oracle/oradata/PROD/redo01.log') size 10M blocksize 512,
           group 2('/u01/app/oracle/oradata/PROD/redo02.log') size 10M blocksize 512,
  group 3('/u01/app/oracle/oradata/PROD/redo03.log') size 10M blocksize 512
   maxlogfiles 5
   maxlogmembers 5
   maxloghistory 1
   maxdatafiles 100
   character set US7ASCII
   national character set AL16UTF16
   extent management local
   datafile '/u01/app/oracle/oradata/PROD/system01.dbf' size 325M reuse
   sysaux datafile '/u01/app/oracle/oradata/PROD/sysaux01.dbf' size 325M reuse
   default tablespace users
     datafile '/u01/app/oracle/oradata/PROD/users01.dbf'
     size 50M reuse AUTOEXTEND ON MAXSIZE UNLIMITED
   default temporary tablespace tempts1
     tempfile  '/u01/app/oracle/oradata/PROD/temp01.dbf' size 20M reuse
   UNDO TABLESPACE UNDOTBS1
     datafile '/u01/app/oracle/oradata/PROD/undotbs01.dbf'
     size 50M reuse AUTOEXTEND ON MAXSIZE UNLIMITED;
10、檢視資料庫例項狀態：
SQL> select status from v$instance;

STATUS
------------
OPEN

11、檢視相關引數：
SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/PROD/o
                                                 ra_control1.ctl, /u01/app/orac
                                                 le/oradata/PROD/ora_control2.c
                                                 tl
SQL> show parameter undo  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilePROD.ora
SQL>


12、建立字典表及工具包：

--必執行指令碼
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/passwd
SQL> @?/sqlplus/admin/pupbld.sql
--可選指令碼
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/catoctk.sql
SQL> @?/rdbms/admin/owminst.plb

執行完以上的指令碼，手動建庫就完成了。手動建庫沒有hr與scott示例兩個使用者。