系统环境
操作系统:Windows Xp
数据库:Oracle 9i
安装路径:C:\Oracle
1、手工创建相关目录
C:\Oracle\admin\mydb
C:\Oracle\admin\mydb\bdump
C:\Oracle\admin\mydb\udump
C:\Oracle\admin\mydb\pfile
C:\Oracle\admin\mydb\cdump
C:\Oracle\admin\mydb\create
D:\Oracle\oradata\mydb
D:\Oracle\oradata\mydb\archive
2、手工创建初始化参数文件c:\oracle\admin\mydb\pfile\init.ora,内容可以copy别的实例init.ora文件后修改。
db_block_size=4096
db_cache_size=20971520
background_dump_dest=C:\oracle\admin\mydb\bdump
core_dump_dest=C:\oracle\admin\mydb\bdump
timed_statistics=TRUE
user_dump_dest=C:\oracle\admin\mydb\udump
control_files=("D:\oracle\oradata\mydb\CONTROL01.CTL",
"D:\oracle\oradata\mydb\CONTROL02.CTL",
"D:\oracle\oradata\mydb\CONTROL03.CTL")
log_archive_dest_1='LOCATION=d:\oracle\admin\mydb\archive'
log_archive_format=%t_s%.dbf
log_archive_start=true
compatible=9.2.0.0.0
db_name=mydb
db_domain=" "
remote_login_passwordfile=EXCLUSIVE
instance_name=mydb
java_pool_size=31457280
large_pool_size=1048576
shared_pool_size=52428800
processes=150
fast_start_mttr_target=300
resource_manager_plan=system_plan
sort_area_size=524288
undo_management=AUTO
undo_tablespace=undotbs
3、手工创建initmydb.ora文件,内容:ifile= c:\oracle\admin\mydb\pfile\init.ora
4、使用orapwd.exe命令,创建口令文件pwdmydb.ora,命令格式如下:
orapwd.exe file=c:\oracle\ora92\database\pwdmydb.ora password=mydbDBA entries=5
5、通过oradim.exe命令,在服务里生成一个新的实例管理服务,启动方式为手工
C:\oracle\ora92\bin\oradim -new -sid mydb -startmode m
-pfile C:\oracle\ora92\database\initmydb.ora
C:\oracle\ora92\bin\net start OracleServiceMYDB
6、声明ORACLE_SID值
set ORACLE_SID=mydb
7、连接到Oracle服务
C:\Oracle\ora92\bin\sqlplus/nolog
SQL>connect SYS/caihuain as SYSDBA
8、启动数据库实例
SQL>STARTUP NOMOUNT
PFILE=”C:\oracle\admin\mydb\pfile\initmydb.ora”
9、创建数据库,执行mydb.sql脚本命令
SQL>@mydb.sql
mydb.sql脚本内容如下:
create database mydb
maxinstances 1
maxloghistory 1
maxlogfiles 5
maxlogmembers 5
maxdatafiles 100
DATAFILE 'e:\oracle\oradata\mydb\system01.dbf'
SIZE 325M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE UNDOTBS
DATAFILE 'e:\oracle\oradata\mydb\undotbs01.dbf'
SIZE 150M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temps1
TEMPFILE 'E:\oracle\oradata\mydb\temptbs01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('e:\oracle\oradata\mydb\redo01.log') SIZE 100M,
GROUP 2 ('e:\oracle\oradata\mydb\redo02.log') SIZE 100M,
GROUP 3 ('e:\oracle\oradata\mydb\redo03.log') SIZE 100M;
10、创建数据库数据文件,执行脚本createfiles.sql
SQL>@createfiles.sql
Createfiles.sql脚本内容如下:
set echo on
spool C:\oracle\admin\mydb\create\CreateDBFiles.log
CREATE TABLE SPACE"INDX" LOGGING DATAFILE 'C:\oracle\oradata\mydb\indx01.dbf'
SIZE 25M REUSE
AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE 'C:\oracle\oradata\mydb\temp01.dbf'
SIZE 40M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE 'C:\oracle\oradata\mydb\tools01.dbf'
SIZE 10M REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE "USERS" LOGGING DATAFILE 'C:\oracle\oradata\mydb\users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE 'C:\oracle\oradata\mydb\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K;
spool off
exit;
11、创建数据字典,运行createdbcatalog.sql脚本命令
SQL>@createdbcatalog.sql
Createdbcatalog.sql脚本内容如下:
@C:\oracle\ora92\rdbms\admin\catalog.sql;
@C:\oracle\ora92\rdbms\admin\catproc.sql;
12、创建用户
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
TEMPORARY TABLESPACE temp;
SQL>GRANT CREATE SESSION,CREATE TABLE
To username;
SQL>connect SYS/caihuain as SYSDBA