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