Oracle 11g利用Create Database手工创建数据库

除了利用DBCA创建数据库之外，还可以利用Create Database语句手工创建数据库，不过Oracle建议利用DBCA建库，本篇主要探讨如何手工进行数据库的创建。

主要创建过程如下：

一、创建目录

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

二、创建Pfile

[oracle@sz oracle]$ vim product/11.2.0/dbhome_1/dbs/initdb01.ora ---->设置以下3个即可，其他保持默认值name='db01'memory_target=1Gcontrol_files = '/u01/app/oracle/oradata/db01/control01.ctl','/u01/app/oracle/oradata/db01/control02.ctl'

三、创建密码文件

[oracle@sz oracle]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb01 entries=10Enter password for SYS: [oracle@sz oracle]$ 

四、连接实例

[oracle@sz oracle]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 5 05:42:59 2017Copyright (c) 1982, 2013, Oracle.  All rights reservedConnected to an idle instance.

五、创建SPfile

SQL> create spfile from pfile;File created.

六、启动到NoMount状态

SQL> startup nomountORACLE instance started.Total System Global Area  521936896 bytesFixed Size    2254824 bytesVariable Size  314574872 bytesDatabase Buffers  201326592 bytesRedo Buffers    3780608 bytesSQL> 

七、创建数据库

SQL> create database db01  2  user SYS identified by oracle  3  user SYSTEM identified by oracle  4  logfile group 1 ('/u01/app/oracle/oradata/db01/redo01a.log') size 100M blocksize 512,  5          group 2 ('/u01/app/oracle/oradata/db01/redo02a.log') size 100M blocksize 512,  6          group 3 ('/u01/app/oracle/oradata/db01/redo03a.log') size 100M blocksize 512  7  maxlogfiles 5  8  maxlogmembers 5  9  maxloghistory 1 10  maxdatafiles 100 11  character set AL32UTF8 12  national character set AL16UTF16 13  extent management local 14  datafile '/u01/app/oracle/oradata/db01/system01.dbf' size 325M reuse 15  SYSAUX datafile '/u01/app/oracle/oradata/db01/sysaux01.dbf' size 325M reuse 16  default tablespace users 17  datafile '/u01/app/oracle/oradata/db01/users01.dbf' size 500M reuse autoextend on maxsize unlimited 18  default temporary tablespace tempts1 19  tempfile '/u01/app/oracle/oradata/db01/temp01.dbf' 20  size 50M reuse 21  undo tablespace undotbs1 22  datafile '/u01/app/oracle/oradata/db01/undotbs01.dbf' 23  size 100M reuse autoextend on maxsize unlimited;Database created.SQL> select instance_name,status from v$instance;--->创建完数据库，数据库自动打开INSTANCE_NAME STATUS---------------- ------------db01 OPENSQL> 

八、运行脚本建立数据字典视图

以SYSDBA管理权限运行下面的脚本：SQL> @?/rdbms/admin/catalog.sql --->创建数据字典视图、动态性能视图和同义词SQL> @?/rdbms/admin/catproc.sql --->运行所有PL/SQL需要或使用的脚本SQL> @?/rdbms/admin/utlrp.sql --->重新编译失效状态的PL/SQL模块，包括包、过程或类型 以SYSTEM用户执行下面的脚本：SQL> conn system/oracleConnected.SQL> @?/sqlplus/admin/pupbld.sql --->SQL*Plus相关的

九、利用netmgr配置监听

具体配置可参考Oracle 11g利用Netmgr配置监听器和服务

[oracle@sz oracle]$ more product/11.2.0/dbhome_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.DB01 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = sz.pri.com)(PORT = 1522))  )ADR_BASE_DB01 = /u01/app/oracleSID_LIST_SZ_PRI =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = szpri)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = szpri)    )  )SZ_PRI =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = sz.pri.com)(PORT = 1521))  )SID_LIST_DB01 =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = db01)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = db01)    )  )ADR_BASE_SZ_PRI = /u01/app/oracle[oracle@sz oracle]$ [oracle@sz oracle]$ lsnrctl start db01LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 09-SEP-2017 23:32:09Copyright (c) 1991, 2013, Oracle.  All rights reserved.Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionSystem parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraLog messages written to /u01/app/oracle/diag/tnslsnr/sz/db01/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sz.pri.com)(PORT=1522)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sz.pri.com)(PORT=1522)))STATUS of the LISTENER------------------------Alias                     db01Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date                09-SEP-2017 23:32:09Uptime                    0 days 0 hr. 0 min. 0 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/sz/db01/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sz.pri.com)(PORT=1522)))Services Summary...Service "db01" has 1 instance(s).  Instance "db01", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully[oracle@sz oracle]$