oracle 10g手工建库全过程

在平时的oracle建库中我们一般都是采用GUI建库方式，所以手工建库相对来说比较有些难度，这个也是OCM考试中需要关注的地方，特别做了测试，主要是版本是9i, 10g

1.修改/etc/oratab
oral:/opt/oracle/database:N
2.创建sys口令认证文件
orapwd file=$ORACLE_HOME/dbs/orapworal password=system entries=5
3.设置oracle用户环境变量实例名
vi .bash_profile
export ORACLE_SID=oral
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/database
export PATH=$PATH:$ORALCE_HOME/bin:.

4.创建目录
oracle@marvelyu:~/> mkdir -p /opt/oracle/admin/oral/bdump
oracle@marvelyu:~/> mkdir -p /opt/oracle/admin/oral/udump
oracle@marvelyu:~/> mkdir -p /opt/oracle/admin/oral/cdump
oracle@marvelyu:~/> mkdir -p /opt/oracle/admin/oral/adump
5.创建pfile
cd $ORACLE_HOME/dbs
cat init.ora |grep -v ^# |grep -v ^$>initoral.ora
修改参数文件内容,一些关键参数修改
*.audit_file_dest=’/opt/oracle/admin/oral/adump’
*.background_dump_dest=’/opt/oracle/admin/oral/bdump’
*.compatible=’9.2.0.2′
*.control_files=’/opt/oracle/oradata/control01.ctl’,'/opt/oracle/oradata/control02.ctl’
*.core_dump_dest=’/opt/oracle/admin/oral/cdump’
*.db_cache_size=80M
*.db_name=’oral’
*.global_names=TRUE
*.log_buffer=32768
*.shared_pool_size=3500000
*.undo_management=’AUTO’
*.user_dump_dest=’/opt/oracle/admin/oral/udump’
完成后记得create spfile from pfile;来创建spfile文件。

6.运行建库脚本

启动数据库到nomount
>sqlplus “/as sysdba”
>stratup nomount pfile=’$ORACLE_HOME/dbs/initoral.ora’

CREATE DATABASE  oral
USER SYS IDENTIFIED BY system
USER SYSTEM IDENTIFIED by system
LOGFILE
GROUP 1 (‘/opt/oracle/oradata/redo01.log’) SIZE 100M,
GROUP 2 (‘/opt/oracle/oradata/redo02.log’) SIZE 100M,
GROUP 3 (‘/opt/oracle/oradata/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/opt/oracle/oradata/system01.dbf’ SIZE 325M
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE ‘/opt/oracle/oradata/temp01.dbf’
SIZE 20M
UNDO TABLESPACE undotbs
DATAFILE ‘/opt/oracle/oradata/undotbs01.dbf’
SIZE 200M  AUTOEXTEND ON MAXSIZE UNLIMITED;
7.创建其他系统表空间
CREATE TABLESPACE users LOGGING
DATAFILE ‘/opt/oracle/oradata/users01.dbf’
SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE indx LOGGING
DATAFILE ‘/opt/oracle/oradata/indx01.dbf’
SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
8.运行创建数据字典脚本
$ORACLE_HOME/rdbms/admin/catalog.sql
$ORACLE_HOME/rdbms/admin/catproc.sql

对于10g,也仅仅是多了个sysaux的文件:

CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY system
USER SYSTEM IDENTIFIED BY system

LOGFILE GROUP 1 (‘/opt/oracle/oradata/redo01.log’) SIZE 100M,
GROUP 2 (‘/opt/oracle/oradata/redo02.log’) SIZE 100M,
GROUP 3 (‘/opt/oracle/oradata/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/opt/oracle/oradata/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/opt/oracle/oradata/oral/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE user
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE ‘/opt/oracle/oradata/temp01.dbf’
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘/opt/oracle/oradata/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;