oracle 11g手动创建数据库
说明，首先要先在Oracle官网下载好，Oracle11g的安装包，在安装时，由于是自己试验部属，所以最好选安装，个人版，不过企业版，标准版也可以，安上后选择单实例就好，并且切记是只安装软件，不能选择配置数据库，我们这里要讲的是手动安装的方法。完上上面工作之后，下面开始
1 准备参数文件initMYDB.ora 路径E:\app\wangxiaobo\admin\mydb\pfile\initMYDB.ora
内容如下：
##############################################################################
# Copyright (c) 1991, 2001, 2012 by Oracle Corporation
##############################################################################
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP)(SERVICE=ORCLXDB)"
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=E:\app\wangxiaobo
memory_target=1288699904
###########################################
# Security and Auditing
###########################################
audit_file_dest=E:\app\wangxiaobo\admin\mydb\adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
###########################################
# Database Identification
###########################################
db_domain=""
db_name='mydb'
###########################################
# File Configuration
###########################################
control_files=("E:\app\wangxiaobo\oradata\mydb\control01.ctl","E:\app\wangxiaobo\flash_recovery_area\mydb\control02.ctl")
db_recovery_file_dest=E:\app\wangxiaobo\flash_recovery_area
db_recovery_file_dest_size=4039114752
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Cache and I/O
###########################################
db_block_size=8192
2 准备建库教本createdb.sql 路径（可以任意放的只要是绝对路径）：C盘下
内容如下：
Create database "mydb"
maxinstances 4
maxloghistory 1
maxlogfiles 16
maxlogmembers 3
maxdatafiles 10
logfile group 1'E:\app\wangxiaobo\oradata\mydb\redo01.log' size 10M,group 2'E:\app\wangxiaobo\oradata\mydb\redo02.log' size 10M
datafile 'E:\app\wangxiaobo\oradata\mydb\system01.dbf'size 50M
autoextend on next 10M extent management local
sysaux datafile'E:\app\wangxiaobo\oradata\mydb\sysaux01.dbf' size 50M
autoextend on next 10M
default temporary tablespace temp
tempfile 'E:\app\wangxiaobo\oradata\mydb\temp.dbf' size 10M autoextendon next 10M
undo tablespace UNDOTBS1 datafile'E:\app\wangxiaobo\oradata\mydb\undotbs1.dbf' size 20M
character set ZHS16GBK
national character set AL16UTF16
user sys identified by sys
user system identified by system ;
3 创建必要的目录用批处理文档
mkdir E:\app\hemingway\admin\mydb\adump
mkdir E:\app\hemingway\admin\mydb\bdump
mkdir E:\app\hemingway\admin\mydb\cdump
mkdir E:\app\hemingway\admin\mydb\dpdump
mkdir E:\app\hemingway\admin\mydb\udump
mkdir E:\app\hemingway\admin\mydb\pfile
mkdir E:\app\hemingway\oradata\mydb
mkdir E:\app\hemingway\flash_recovery_area\mydb
4 设置环境变量
set ORACLE_SID=mydb
5 创建密码文件
orapwdfile=E:\app\wangxiaobo\product\11.2.0\dbhome_1\database\PWDMYDB.orapassword=rootroot force=y
6 创建服务
oradim -NEW -SID mydb -intpwd rootroot -STARTMODE auto-SRVCSTART system -PFILE E:\app\wangxiaobo\admin\mydb\pfile\initMYDB.ora
实例已创建。
7 以nomount方式启动数据库
C:\Documents and Settings\Administrator>sqlplus/nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 28 20:15:15 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
修改E:\app\wangxiaobo\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora添加为：
# This is a sample listener.ora that contains the NET8parameters that are
# needed to connect to an HS Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME= CLRExtProc)
(ORACLE_HOME= E:\app\wangxiaobo\product\11.2.0\dbhome_1)
(PROGRAM =extproc)
(ENVS ="EXTPROC_DLLS=ONLY:E:\app\wangxiaobo\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME= mydb)
(SID_NAME= mydb)
(ORACLE_HOME= E:\app\wangxiaobo\product\11.2.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION=
(ADDRESS =(PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = E:\app\wangxiaobo
#CONNECT_TIMEOUT_LISTENER = 0
另外还要修改
E:\app\wangxiaobo\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora为
# This is a sample tnsnames.ora that contains the NET8parameters that are
# needed to connect to an HS Agent
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
LISTENER_ORCL =
(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS =(PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID =CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME = orcl)
)
MYDB =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME = mydb)
)
)
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
LISTENER_MYDB =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
SQL> conn SYS/rootroot as sysdba
已连接到空闲例程。
shutdown abort; 关闭其他例程
要在E:\app\wangxiaobo\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora中添加
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
LISTENER_MYDB =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
才可以正常的启动例程
SQL> startup pfile='E:\app\wangxiaobo\admin\mydb\pfile\initMYDB.ora' nomount
ORACLE 例程已经启动。
Total System Global Area 732874456 bytes
Fixed Size 1344808 bytes
Variable Size 234483372 bytes
Database Buffers 54230912 bytes
Redo Buffers 5256264 bytes
SQL>
8 调用建库脚本创建数据库
SQL> @C:\createdb.sql
数据库已创建。
SQL>
SQL>@E:\app\wangxiaobo\product\11.2.0\dbhome_1\rdbms\admin\catalog.sql;
SQL> ................
SQL>
SQL>@E:\app\wangxiaobo\product\11.2.0\dbhome_1\rdbms\admin\catproc.sql;
SQL> Rem Indicate CATPROC load complete and checkvalidity
SQL> BEGIN
2 dbms_registry.update_schema_list('CATPROC',
3 dbms_registry.schema_list_t('SYSTEM','OUTLN', 'DBSNMP', 'DIP',
4 'ORACLE_OCM', 'APPQOSSYS'));
5 dbms_registry.loaded('CATPROC');
6 dbms_registry_sys.validate_catproc;
7 dbms_registry_sys.validate_catalog;
8 END;
9 /
PL/SQL 过程已成功完成。
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('CATPROC')AS timestamp FROM DUAL;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATPROC 2014-02-28 19:50:28
已选择 1 行。
SQL>
SQL> SET SERVEROUTPUT OFF
SQL>
SQL>
10 创建tns
通过oracle net configuration assistant来创建监听器。
11 创建spfile
SQL> create spfile frompfile='E:\app\wangxiaobo\admin\mydb\pfile\initMYDB.ora';
文件已创建。
SQL> shutdown
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> connSYS/rootroot as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 234882472 bytes
Database Buffers 536870912 bytes
Redo Buffers 5259264 bytes
数据库装载完毕。
数据库已经打开。
12 用命令show parameter db_name查看数据库名
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_name string mydb
SQL>
注：rdbms文件里的内容来E:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN的复制。别的相应文件有的也是来自复制sample示例，后经过修改
SQL> create table st(sno int,m char);
表已创建。
SQL> desc st;
名称是否为空? 类型
------------------------------------------------- ----------------------
SNO NUMBER(38)
M CHAR(1)
SQL>
