oracle10g之手工建库的方法

                                                      oracle10g之手工建库的方法

      一般来说，建库比较方便的工具是使用oracle自带的dbca工具，真的非常方便。但是，有些行业（比如：电信，金融）的生产库却有手工建库的要求，这同样也是考验一个DBA技术功底是否扎实的重要一环。 

      同时手工建库也是OCM考试的第一关，作为DBA或者准DBA熟练掌握oracle的手工建库技能也是必须的。

 

一、手工建库前的准备工作

1.操作系统环境：RHEL6.3 x86_64Bit

2.oracle版本：oracle10g Release 10.2.0.4.0

3.oacle软件：10201_database_linux_x86_64.cpio + p6810189_10204_Linux-x86-64.zip

4.设置好环境变量，并生效。

[root@oracle10g oracle]# cat /home/oracle/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
 . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

#for oracle
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
export ORACLE_SID=ORCL
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS

[oracle@oracle10g ~]$ source /home/oracle/.bash_profile

5.规划表空间及数据文件路径及大小

system表空间大小350M，sysaux表空间300M，temp临时表空间100M，undo表空间200M，users表空间100M。

创建相关文件的存储路径

[oracle@oracle10g u01]$mkdir -p /u01/oracle/oradata/ORCL

[oracle@oracle10g u01]$mkdir -p /u01/oracle/flash_recovery_area

[oracle@oracle10g u01]$mkdir -p /u01/oracle/admin/ORCL/{adump,bdump,cdump,udump}

 

二、手工建库过程

1.设置ORACLE_SID环境变量

[oracle@oracle10g u01]$ export ORACLE_SID=ORCL

2.修改oratab文件如下

[oracle@oracle10g etc]$ cat oratab
# 

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#增加以下内容：$ORACLE_SID:$ORACLE_HOME:<N|Y>:
ORCL:/u01/oracle/product/10.2.0/db_1:Y

3.创建orapwd远程登陆密码文件，路径为/u01/oracle/product/10.2.0/db_1/dbs

[oracle@oracle10g dbs]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> nosysdba=<y/n>

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA,
    force - whether to overwrite existing file (opt),
    nosysdba - whether to shut out the SYSDBA logon (opt for Database Vault only).
  There are no spaces around the equal-to (=) character.
[oracle@oracle10g dbs]$ orapwd file=orapwORCL password='sys' entries=5

[oracle@oracle10g dbs]$ ll
total 52
-rw-r--r-- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
--rw-r----- 1 oracle oinstall  2048 Aug  9 17:14 orapwORCL

4.创建pfile文件，可以根据模版创建或者拷贝其他的pfile文件修改

[root@oracle10g dbs]# cat initORCL.ora
ORCL.__db_cache_size=58720256
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__shared_pool_size=92274688
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/ORCL/adump'
*.background_dump_dest='/u01/oracle/admin/ORCL/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/oracle/oradata/ORCL/control01.ctl','/u01/oracle/oradata/ORCL/control02.ctl','/u01/oracle/oradata/ORCL/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/ORCL/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=52428800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=314572800
*.sga_target=167772160
*.shared_servers=1
*.undo_management='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/ORCL/udump'

5.编写数据库创建脚本，可以参看oracle10g官方文档中的例子

 [oracle@oracle10g ~]$ cat createdb.sql
CREATE DATABASE ORCL
      USER SYS IDENTIFIED BY "sys"
      USER SYSTEM IDENTIFIED BY "sys"
      LOGFILE
      GROUP 1 ('/u01/oracle/oradata/ORCL/redo01_1.log','/u01/oracle/oradata/ORCL/redo01_2.log') SIZE 50M,
      GROUP 2 ('/u01/oracle/oradata/ORCL/redo02_1.log','/u01/oracle/oradata/ORCL/redo02_2.log') SIZE 50M,
      GROUP 3 ('/u01/oracle/oradata/ORCL/redo03_1.log','/u01/oracle/oradata/ORCL/redo03_2.log') SIZE 50M
      MAXLOGFILES 10
      MAXLOGMEMBERS 5
      MAXLOGHISTORY 1
      MAXDATAFILES 100
      MAXINSTANCES 1
      CHARACTER SET ZHS16GBK
      NATIONAL CHARACTER SET AL16UTF16
      DATAFILE '/u01/oracle/oradata/ORCL/system01.dbf' SIZE 350M REUSE EXTENT MANAGEMENT LOCAL
      SYSAUX DATAFILE '/u01/oracle/oradata/ORCL/sysaux01.dbf' SIZE 300M REUSE
      DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/oradata/ORCL/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 32767M
      UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oracle/oradata/ORCL/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

6.执行数据库创建脚本，开始创建数据库；创建数据库，必须有SYSDBA系统权限，本例中使用SYS用户来创建数据库。

[oracle@oracle10g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 17:36:51 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size      2083656 bytes
Variable Size    247465144 bytes
Database Buffers    58720256 bytes
Redo Buffers      6303744 bytes
SQL> @createdb.sql

Database created.

7.数据库创建成功后，查看一下相关数据文件是否创建成功 

[root@oracle10g ORCL]# ll /u01/oracle/oradata/ORCL/
total 1197936
-rw-r----- 1 oracle oinstall   6897664 Aug  9 17:49 control01.ctl
-rw-r----- 1 oracle oinstall   6897664 Aug  9 17:49 control02.ctl
-rw-r----- 1 oracle oinstall   6897664 Aug  9 17:49 control03.ctl
-rw-r----- 1 oracle oinstall  52429312 Aug  9 17:49 redo01_1.log
-rw-r----- 1 oracle oinstall  52429312 Aug  9 17:49 redo01_2.log
-rw-r----- 1 oracle oinstall  52429312 Aug  9 17:38 redo02_1.log
-rw-r----- 1 oracle oinstall  52429312 Aug  9 17:38 redo02_2.log
-rw-r----- 1 oracle oinstall  52429312 Aug  9 17:38 redo03_1.log
-rw-r----- 1 oracle oinstall  52429312 Aug  9 17:38 redo03_2.log
-rw-r----- 1 oracle oinstall 314580992 Aug  9 17:39 sysaux01.dbf
-rw-r----- 1 oracle oinstall 367009792 Aug  9 17:45 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug  9 17:39 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Aug  9 17:44 undotbs01.dbf

8.创建用户表空间users

SQL> CREATE TABLESPACE "USERS" DATAFILE  '/u01/oracle/oradata/ORCL/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Tablespace created.

9.修改users表空间为用户默认表空间

SQL> alter database default tablespace users;

Database altered.

10.运行脚本，重建数据字典视图

SQL>@/u01/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

SQL>@/u01/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql

SQL>@//u01/oracle/product/10.2.0/db_1/sqlplus/admin/pupbld.sql
如果initORCL.ora文件中设置的参数不是*.compatible='10.2.0.3.0'，则需要执行以下命令升级数据库

SQL>startup upgrade

SQL>@/u01/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd.sql

11.设置listener

[oracle@oracle10g admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle10g.cluster.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

三、测试数据库

1.创建测试用户test

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> select default_tablespace,temporary_tablespace from dba_users where username='TEST';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
USERS                          TEMP

SQL> conn test/test
Connected.
SQL> show user
USER is "TEST"
SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> select id from test;

        ID
----------
         1

2.使用rman工具来备份数据库

[oracle@oracle10g dbs]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Aug 21 15:50:34 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1319428512)

RMAN> backup database;

Starting backup at 2012-08-21 15:53:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/oradata/ORCL/system01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/ORCL/sysaux01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/ORCL/undotbs01.dbf
input datafile fno=00004 name=/u01/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-08-21 15:53:58

channel ORA_DISK_1: finished piece 1 at 2012-08-21 15:55:13
piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2012_08_21/o1_mf_nnndf_TAG20120821T155357_836hnp7g_.bkp tag=TAG20120821T155357 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2012-08-21 15:55:15
channel ORA_DISK_1: finished piece 1 at 2012-08-21 15:55:18
piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2012_08_21/o1_mf_ncsnf_TAG20120821T155357_836hq3vw_.bkp tag=TAG20120821T155357 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 2012-08-21 15:55:18

RMAN> quit

Recovery Manager complete.

四、结束语

　　通过学习和掌握oracle10g的手工建库的方法，可以让您更深入的了解oracle的体系结构；熟练掌握相关技能后会让您日常的数据库管理变得非常轻松。