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的体系结构；熟练掌握相关技能后会让您日常的数据库管理变得非常轻松。