第二步：oracle建库

之前提到的博客中，作者使用了“静默创建数据库”，我这里就不说了，我这里重点就是“作死”·····”命令行手动建立库”。

[使用命令行手工建立Oracle11gR2数据库]

原因：通过命令行建立一个数据库可以帮助更好的了解数据库的建库流程
下面是我的建库流程：
测试环境：

• OS：centos7.4 x86_64
• DB：Oracle database 11g R2（11.2.0.1.0）
• 建立数据库名：jkdata

---

一.配置环境变量（仅供参考）
之前配置有提过，这里不变，但需修改一些参数

• 1.设置环境变量

[oracle@DATA1~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;export CLASSPATH
PATH=$ORACLE_HOME/bin:/usr/sbin:/sbin:$PATH; export PATH

• 2.生效环境变量

[oracle@DATA1 ~]$ source ~/.bash_profile

二.建立相关目录

• 1.建立建立adump目录，并且赋予权限(切换oracle用户建立目录权限自动匹配)
  dump目录和10g相比，这里只需要建立adump一个目录就可以了

[oracle@DATA1 admin]$ mkdir -p /db/app/oracle/admin/jkdata/{adump,dpdump,pfile}

• 2.数据文件及控制文件的存放目录

[oracle@DATA1 admin]$ mkdir -p /db/app/oracle/oradata/jkdata

• 3.建立密码文件

[oracle@DATA1 ~]$ cd $ORACLE_HOME/dbs
[oracle@DATA1 dbs]$ orapwd file=orapwjkdata password=oracle entries=30

• 4.创建参数文件
  a、从init.ora文件中获得示例参数

[oracle@DATA1 ~]$ cd $ORACLE_HOME/dbs
[oracle@DATA1 dbs]$ cat init.ora |grep -v ^# |grep -v ^$ >initjkdata.ora

b、修改参数文件中的信息
1：db_name
2：memory_target内存区大小
3：替换<ORACLE_BASE>标签为当前的$ORACLE_BASE中的内容
4：快速恢复区路径设置
5：修改diagnostic_dest
6：修改dispatchers中的SERVICE为PRODXDB
7：添加控制文件的文件名

db_name='jkdata'
memory_target=1G
processes = 150
audit_file_dest='/db/app/oracle/admin/jkdata/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/db/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/db/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=JKDATAXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/db/app/oracle/oradata/jkdata/control01.ctl,/db/app/oracle/oradata/jkdata/control02.ctl)
compatible ='11.2.0'

• 5.使用之前创建的参数文件启动数据库到nomount状态下

[oracle@DATA1 ~]$ export ORACLE_SID=jkdata
[oracle@DATA1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 24 18:26:51 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SYS@prod>create spfile from pfile;
File created.
SYS@prod>startup nomount;
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             373297144 bytes
Database Buffers          247463936 bytes
Redo Buffers                3350528 bytes

• 6..在10gR2中本来是需要手动修改undo_management，job_queue_processes等参数，但是show parameter后发现这些参数在11gR2版本中的值都已经符合要求，也就不需要修改了。

SQL> show parameter undo_management

NAME                     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
undo_management              string
AUTO
SQL> show parameter job_queue_processes

NAME                     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
job_queue_processes          integer
1000
SQL> show parameter _dump_

NAME                     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
background_dump_dest             string
/db/app/oracle/diag/rdbms/jkda
ta/jkdata/trace
core_dump_dest               string
/db/app/oracle/diag/rdbms/jkda
ta/jkdata/cdump
max_dump_file_size           string
unlimited
user_dump_dest               string

NAME                     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
/db/app/oracle/diag/rdbms/jkda
ta/jkdata/trace

• 7.创建数据库
  a.在官方文档中找到建库语句，位置：Administrator's Guide 11g Release 2 (11.2) => 2 Creating and Configuring an Oracle Database => Creating a Database with the CREATE DATABASE Statement => “CREATE DATABASE mynewdb”
  b.修改示例的建库语句，需要注意UNDO TABLESPACE的名字和默认的不相同。

[oracle@DATA1 ~]$vim /hom/oracle/jkdata.sql
CREATE DATABASE jkdata
    USER SYS IDENTIFIED BY sys_password
    USER SYSTEM IDENTIFIED BY system_password
    LOGFILE GROUP 1 ('/db/app/oracle/JKDATA_1_logs/redo01a.log','/db/app/oracle/JKDATA_2_logs/redo01b.log') SIZE 100M BLOCKSIZE 512,
            GROUP 2 ('/db/app/oracle/JKDATA_1_logs/redo02a.log','/db/app/oracle/JKDATA_2_logs/redo02b.log') SIZE 100M BLOCKSIZE 512,
            GROUP 3 ('/db/app/oracle/JKDATA_1_logs/redo03a.log','/db/app/oracle/JKDATA_2_logs/redo03b.log') SIZE 100M BLOCKSIZE 512
    MAXLOGFILES 30
    MAXLOGMEMBERS 5
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    CHARACTER SET UTF8
    NATIONAL CHARACTER SET UTF8
    EXTENT MANAGEMENT LOCAL
    DATAFILE '/db/app/oracle/oradata/jkdata/system01.dbf' SIZE 325M REUSE
    SYSAUX DATAFILE '/db/app/oracle/oradata/jkdata/sysaux01.dbf' SIZE 325M REUSE
    DEFAULT TABLESPACE users
        DATAFILE '/db/app/oracle/oradata/jkdata/users01.dbf'
        SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
        TEMPFILE '/db/app/oracle/oradata/jkdata/temp01.dbf'
        SIZE 20M REUSE
    UNDO TABLESPACE undotbs1
        DATAFILE '/db/app/oracle/oradata/jkdata/undotbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

c.执行建库脚本，需要在nomount下执行

SQL>@/home/oracle/jkdata.sql

Database created.

d.修改数据文件自动扩展

SQL>alter database datafile 1 autoextend on;

Database altered.

SQL> alter database datafile 2 autoextend on;

Database altered.

SQL> alter database datafile 3 autoextend on;

Database altered.

SQL> alter database datafile 4 autoextend on;

Database altered.

SQL> alter database tempfile 1 autoextend on;

Database altered.

e.查询数据库状态并确认创建成功。

[oracle@DATA1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 11:06:12 2018

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


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

SQL> 
SQL> select instance_name,status from v$instance;

INSTANCE_NAME            STATUS
-------------------------------- ------------------------
jkdata  
SQL> col tablespace_name for a10
SQL> col tablespace_file for a60
SQL> select a.name tablespace_name,b.name tablespace_file from v$tablespace a,v$datafile b where a.ts#=b.ts#;

TABLESPACE TABLESPACE_FILE
---------- ------------------------------------------------------------
SYSTEM     /db/app/oracle/oradata/jkdata/system01.dbf
SYSAUX     /db/app/oracle/oradata/jkdata/sysaux01.dbf
UNDOTBS1   /db/app/oracle/oradata/jkdata/undotbs01.dbf
USERS      /db/app/oracle/oradata/jkdata/users01.dbf
JK_KTHB    /db/app/oracle/jkdata/jkdata.dbf

f.执行建立数据字典脚本

SQL>@?/rdbms/admin/catalog.sql
......
SQL>@?/rdbms/admin/catproc.sql
......

到这里，手工建立11gR2数据库就基本完成了，可以再跑一些额外的脚本如catoctk.sql来完善数据库的功能，总体来说手工建立11gR2数据库的过程和建立10gR2数据库基本思路相同，主要是参数文件和建库语句有一定区别，可以说11gR2数据库的手工建立过程比10gR2要少一些修改动作，这或许是因为随着数据库版本的升级，其设计越来越完善的缘故吧。