oracle手动创建数据库

手动创建数据库大致分为以下几个步骤：
1.创建实例

2.创建初始化参数文件

3.启动实例到nomount创建数据库（注意这里要设置ORACLE_SID环境变量）

4.使用create database创建数据库

5.用catalog.sql脚本创建数据字典基表和数据字典视图

6.用catproc.sql脚本安装Oracle系统包

7.用pupbld.sql脚本安装PRODUCT_USER_PROFILE表

 

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

 

1.创建实例

在cmd下：

oradim -new -sid zbcxy -intpwd xin

创建实例zbcxy，密码为xin

这时候你打开服务可以看到OracleServicezbcxy

 

 

2.创建初始化参数文件

这里可以参考oracle提供标准初始化参数文件init.ora，这里参考我的文件位置：
D:\app\lenovo\product\11.2.0\dbhome_1\srvm\admin

 

当然如果你以前使用了DBCA创建了一个数据库，这时候你可以根据它的spfile生成一个pfile作为你初始化参数文件的参考：
sql>create pfile from spfile

注意：

spfile默认名为spfile+orace_sid.ora

参考我的路径为：D:\app\lenovo\product\11.2.0\dbhome_1\database\SPFILEORCL.ORA

 

这里生成的pfile默认名为：INIT+oracle_sid.ora，如果不指定路径，默认与spfile路径相同

下面我贴出我的INITorcl.ora:

orcl.__db_cache_size=1413480448

orcl.__java_pool_size=16777216

orcl.__large_pool_size=12582912

orcl.__oracle_base='D:\app\lenovo'#ORACLE_BASEset from environment

orcl.__pga_aggregate_target=1438646272

orcl.__sga_target=1979711488

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=520093696

orcl.__streams_pool_size=0

*.audit_file_dest='D:\app\lenovo\admin\orcl\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='D:\APP\LENOVO\ORADATA\ORCL\CONTROL01.CTL','D:\APP\LENOVO\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL','D:\APP\LENOVO\ADMIN\ORCL\CONTROL03.CTL'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='D:\app\lenovo\flash_recovery_area'

*.db_recovery_file_dest_size=4102029312

*.diagnostic_dest='D:\app\lenovo'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'

*.local_listener='LISTENER_ORCL'

*.log_archive_dest='D:\oracle\backup1'

*.log_archive_duplex_dest='D:\oracle\backup2'

*.memory_target=3415212032

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=734003200

*.sga_target=734003200

*.undo_tablespace='UNDOTBS1'

 

我们可以参考以上的初始化参数来设定我们自己的初始化参数列表，当然你也可以参考oracle为你提供的init.ora

 

下面列出我的初始化参数文件initZBCXY.ora:
db_name='zbcxy'

instance_name='zbcxy'

memory_target=500M

processes=100

audit_file_dest='D:\app\lenovo\admin\zbcxy\adump'

audit_trail='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='D:\app\lenovo\flash_recovery_area'

db_recovery_file_dest_size=100M

diagnostic_dest='D:\app\lenovo'

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

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files='D:\APP\LENOVO\ORADATA\ZBCXY\CONTROL01.CTL','D:\APP\LENOVO\FLASH_RECOVERY_AREA\ZBCXY\CONTROL02.CTL'

compatible='11.2.0.0.0'

 

这里不详解每个初始化参数，想了解的请移步：

http://blog.csdn.net/u012512575/article/details/14051861

当然这里面的参数个数不是固定的，如果不是在生产环境中，oracle允许你至少设置一个参数：db_name

 

3.启动实例到nomount创建数据库

接着刚刚打开的cmd，设置临时环境变量：
SET ORACLE_SID=ZBCXY;

 

SQL>SQLPLUS /NOLOG

SQL>conn sys/xin as sysdba;

SQL>STARTUP ‘D:\APP\LENOVO\ORADATA\ZBCXY\initZBCXY.ora’nomount;

这里注意，我故意将初始化参数文件放在这里，通过指定初始化参数文件的位置将数据库启动到nomount

如果不指定路径，oracle首先会去：D:\app\lenovo\product\11.2.0\dbhome_1\database目录下面查找

 

实例启动成功

这时候去警告日志文件中查看相关信息，我的警告文件位置：

D:\app\lenovo\diag\rdbms\zbcxy\zbcxy\trace\alert_zbcxy.log

 

下面贴出我的信息：
Fri Nov 01 18:08:28 2013

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Shared memory segment for instance monitoringcreated

Picked latch-free SCN scheme 3

Fri Nov 01 18:09:50 2013

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter defaultvalue as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =18

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options.

Using parameter settings in client-sidepfile D:\APP\LENOVO\ORADATA\ZBCXY\INITZBCXY.ORA on machine LENOVO-PC

System parameters with non-default values:

 processes                = 100

 memory_target            = 500M

  control_files            ="D:\APP\LENOVO\ORADATA\ZBCXY\CONTROL01.CTL"

 control_files            ="D:\APP\LENOVO\FLASH_RECOVERY_AREA\ZBCXY\CONTROL02.CTL"

 db_block_size            = 8192

 compatible               ="11.2.0.0.0"

 db_recovery_file_dest    ="D:\app\lenovo\flash_recovery_area"

 db_recovery_file_dest_size= 100M

 undo_tablespace          ="UNDOTBS1"

 remote_login_passwordfile= "EXCLUSIVE"

 db_domain                =""

 instance_name            ="zbcxy"

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

 audit_file_dest          ="D:\APP\LENOVO\ADMIN\ZBCXY\ADUMP"

 audit_trail              ="DB"

 db_name                  ="zbcxy"

 open_cursors             = 300

 diagnostic_dest          ="D:\APP\LENOVO"

Fri Nov 01 18:09:51 2013

PMON started with pid=2, OS id=15404

Fri Nov 01 18:09:51 2013

VKTM started with pid=3, OS id=2568 atelevated priority

VKTM running at (10)millisec precision withDBRM quantum (100)ms

Fri Nov 01 18:09:51 2013

GEN0 started with pid=4, OS id=13064

Fri Nov 01 18:09:51 2013

DIAG started with pid=5, OS id=12440

Fri Nov 01 18:09:51 2013

DBRM started with pid=6, OS id=15252

Fri Nov 01 18:09:51 2013

PSP0 started with pid=7, OS id=8676

Fri Nov 01 18:09:51 2013

DIA0 started with pid=8, OS id=15904

Fri Nov 01 18:09:51 2013

MMAN started with pid=9, OS id=1656

Fri Nov 01 18:09:51 2013

DBW0 started with pid=10, OS id=16020

Fri Nov 01 18:09:51 2013

LGWR started with pid=11, OS id=11740

Fri Nov 01 18:09:51 2013

CKPT started with pid=12, OS id=4644

Fri Nov 01 18:09:51 2013

SMON started with pid=13, OS id=6852

Fri Nov 01 18:09:51 2013

RECO started with pid=14, OS id=3248

Fri Nov 01 18:09:51 2013

MMON started with pid=15, OS id=15616

Fri Nov 01 18:09:51 2013

MMNL started with pid=16, OS id=15472

starting up 1 dispatcher(s) for networkaddress '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment =D:\app\lenovo

 

这里面有你指定的初始化参数信息以及一些后台进程的创建
PMON started with pid=2, OS id=15404

这里的pid代表该进程在数据库内部的标示符编号，从2开始，编号为1的进程是PSEUDO进程，这个进程被认为是初始化数据库的进程，这里的os id则代表该进程在操作系统上的进程编号

 

 

4. 使用create database创建数据库

在D:\APP\LENOVO\SCRIPT\下新建脚本createDB.sql 这里路径自定义，无需跟着我的操作

create database zbcxy

maxinstances 3

maxloghistory 1

maxlogfiles 10

maxlogmembers 5

maxdatafiles 10

character set ZHS16GBK

national character set AL16UTF16

datafile'D:\app\lenovo\oradata\zbcxy\system01.dbf' size 500M

extent management local

SYSAUX datafile'D:\app\lenovo\oradata\zbcxy\sysaux01.dbf' size 500M

default temporary tablespace temp01

tempfile'D:\app\lenovo\oradata\zbcxy\temp01.dbf' size 100M

undo tablespace UNDOTBS1

datafile'D:\app\lenovo\oradata\zbcxy\UNDOTBS1.dbf' size 200M

default tablespace users

datafile 'D:\app\lenovo\oradata\zbcxy\users01.dbf'size 100M

logfile group 1

'D:\app\lenovo\oradata\zbcxy\redo01.log'size 100M,

group 2

'D:\app\lenovo\oradata\zbcxy\redo02.log'size 100M;

 

创建完成后，回到cmd下：
SQL>@ D:\APP\LENOVO\SCRIPT\createDB.sql

 

这里需要注意的问题：

如果遇到相关错误去警告日志文件中查看，当然成功了也可以去查看里面是怎么执行的，下面列举我创建时遇见的两个错误

ORA-01501: CREATE DATABASE

这时候找到已存在的文件将其全部删除，再一次执行创建数据库的脚本
ORA-1092 signalled during: create database zbcxy

这时候你需要去查看参数文件中指定的undo表空间：

undo_tablespace='UNDOTBS1'是否和createdatabase脚本中的指定的默认表空间相同

 

 

5.用catalog.sql脚本创建数据字典基表和数据字典视图

SQL>@D:\app\lenovo\product\11.2.0\dbhome_1\RDBMS\ADMIN\catalog.sql

 

6.用catproc.sql脚本安装Oracle系统包

SQL>@D:\app\lenovo\product\11.2.0\dbhome_1\RDBMS\ADMIN\catproc.sql

 

7.用pupbld.sql脚本安装PRODUCT_USER_PROFILE表

SQL>@D:\app\lenovo\product\11.2.0\dbhome_1\sqlplus\admin\pupbld.sql

 

执行这三个脚本需要很长的时间

 

创建成功，最后重启数据库就完成整个数据库的创建了