oracle 手工建库

最新推荐文章于 2021-04-03 08:30:50 发布

ciruo0637

最新推荐文章于 2021-04-03 08:30:50 发布

阅读量86

点赞数

文章标签：数据库

相对来说，oracle中平时我们大多采用dbca图形化建库方式，所以手工建库或许比较有些难度，这个也是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;