手动创建数据库大致分为以下几个步骤:
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
执行这三个脚本需要很长的时间
创建成功,最后重启数据库就完成整个数据库的创建了