【12c-建库篇】Oracle 12c利用Create database手工创建数据库

安装完Oracle 12c数据库软件后，就可以进行数据库的创建，可以使用SQL语句手工建库，也可以使用DBCA工具进行建库，上篇讲解了如何利用DBCA进行数据库的创建，本篇则主要讲解通过Create Database语句进行数据库的创建。

创建步骤如下：

1 指定实例SID

[oracle@strong ~]$ export ORACLE_SID=db01

2 创建密码文件

[oracle@strong ~]$ orapwd file='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwdb01' format=12.2

Enter password for SYS:

3 创建文本初始化参数文件

[oracle@strong ~]$ vim initdb01.ora

db_name='db01'
memory_target=400M
control_files='/u01/app/oracle/oradata/db01/control01.ctl','/u01/app/oracle/oradata/db01/control02.ctl'

4 连接实例

[oracle@strong ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 12 21:04:01 2018

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

SQL> conn / as sysdba
Connected to an idle instance.

5 创建服务器参数文件

SQL> create spfile from pfile='/home/oracle/initdb01.ora';

File created.

6 启动实例

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size		    8621424 bytes
Variable Size		  301990544 bytes
Database Buffers	  104857600 bytes
Redo Buffers		    3960832 bytes

7 创建目录

[oracle@strong ~]$ mkdir -p /u01/app/oracle/admin/db01/adump
[oracle@strong ~]$ mkdir -p /u01/app/oracle/admin/db01/dpdump
[oracle@strong ~]$ mkdir -p /u01/app/oracle/admin/db01/pfile
[oracle@strong ~]$ mkdir -p /u01/app/oracle/cfgtoollogs/dbca/db01
[oracle@strong ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/db01
[oracle@strong ~]$ mkdir -p /u01/app/oracle/oradata/db01

8 创建建库脚本

[oracle@strong ~]$ cat db01.sql 
CREATE DATABASE db01
USER SYS IDENTIFIED BY sys_pwd
USER SYSTEM IDENTIFIED BY system_pwd
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/db01/redo01.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/db01/redo02.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/db01/redo03.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/db01/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/db01/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/db01/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/db01/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/db01/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
USER_DATA TABLESPACE usertbs
DATAFILE '/u01/app/oracle/oradata/db01/usertbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

9 执行创建脚本语句

SQL> @db01.sql

Database created.

10 建库之后，数据库处于打开状态

SQL> select instance_name ,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
db01		 OPEN

11 创建数据字典视图

1）脚本说明

2）以sysdba权限执行

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

3）以SYSTEM用户执行

SQL> @?/sqlplus/admin/pupbld.sql

12 完善配置；

更新oratab文件，向其添加下面一行内容：

[oracle@strong ~]$ vim /etc/oratab
db01:/u01/app/oracle/product/12.2.0/dbhome_1:N

至此，使用Create Database语句手工建库完成。