一个oracle 用户下面创建多个实例
2012/12/10
v1.0
前言
本文主要介绍了同一个oracle 用户下面如何重建多个oracle 实例。本文的测试环境为redhat 5.4，但该方法适应用大部分unix 系统（aix、hp-ux、suse linux等），本人也在AIX 5.3 系统上面实践过，通过此方法创建实例不存在任何问题。
一、环境介绍
系统：rhel 5.4
oracle 版本：10.2.0.1
原实例：
用户：oracle
oracle sid: orcl
oracle_base:/oracle/inst1
oracle_home: /oracle/inst1/product/10.2.0/db_1
数据文件：/oracle/inst1/oradata/orcl
参数文件：/oracle/inst1/product/10.2.0/db_1/dbs
控制文件：/oracle/inst1/oradata/orcl
归档路径：/oracle/inst1/arch/orcl
新实例：
用户：oracle
oracle sid: orcl3
oracle home: /oracle/inst1/ product/10.2.0/db_1
oracle_base:/oracle/inst1
oracle_home: /oracle/inst1/product/10.2.0/db_1
数据文件：/oracle/inst1/oradata/orcl3
参数文件：/oracle/inst1/product/10.2.0/db_1/dbs
控制文件：/oracle/inst1/oradata/orcl3
归档路径：/oracle/inst1/arch/orcl3
二、设置用户环境变量
说明：这里我们把新的oracle sid 加到用户的配置文件中（.bash_profile），其实不加入也没关系，加入只是为了便于其他管理和维护人员能够更好的理解和维护。
添加新的实例的SID
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
TMP=/tmp;
export TMP
TMPDIR=$TMP;
export TMPDIR
ORACLE_BASE=/oracle/inst1;
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export ORACLE_HOME
ORACLE_SID=orcl;
ORACLE_SID=orcl3;
export ORACLE_SID
ORACLE_TERM=xterm;
export ORACLE_TERM
PATH=/usr/sbin:$PATH;
export PATH
PATH=$ORACLE_HOME/bin:$PATH;
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
unset USERNAME
注意：oracle sid 环境变量为默认的环境变量，即默认环境变量为orcl3，配置文件中放在后面的设置会覆盖前面的设置。
用Xmanager 连接到服务器上。Su – oracle
[oracle@10.14.54.199 dbs]# su – oracle
[oracle@10.14.54.199 dbs]$dbca 创建2个数据库
三、创建新实例的参数文件
[oracle@10.14.54.199 dbs]$ ls
hc_orcl2.dat initdw.ora initorcl.ora lkORCL2 orapworcl2 spfileorcl.ora
hc_orcl.dat init.ora lkORCL orapworcl spfileorcl2.ora
[oracle@10.14.54.199 dbs]$ pwd
/oracle/inst1/product/10.2.0/db_1/dbs
[oracle@10.14.54.199 dbs]$ cp initorcl.ora initorcl3.ora
注：这里我们通过拷贝原实例参数文件的方法进行创建，然后再进行修改。
修改新实例的参数文件
orcl.__db_cache_size=184549376
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=88080384
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/inst1/admin/orcl3/adump'
*.background_dump_dest='/oracle/inst1/admin/orcl3/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/inst1/oradata/orcl3/control01.ctl','/oracle/inst1/oradata/orcl3/control02.ctl','/oracle/inst1/oradata/orcl3/control03.ctl'
*.core_dump_dest='/oracle/inst1/admin/orcl3/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl3'
*.db_recovery_file_dest='/oracle/inst1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/oracle/inst1/arch/orcl3'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/inst1/admin/orcl3/udump'
创建新实例的相关目录：
[oracle@10.14.54.199 ~]$ cd /oracle/inst1/admin/
[oracle@10.14.54.199 admin]$ mkdir orcl3
[oracle@10.14.54.199 admin]$ ls
orcl orcl2 orcl3
[oracle@10.14.54.199 admin]$ cd orcl3
[oracle@10.14.54.199 orcl3]$ mkdir adump bdump cdump udump
[oracle@10.14.54.199 orcl3]$ cd /oracle/inst1/oradata/
[oracle@10.14.54.199 oradata]$ mkdir orcl3
[oracle@10.14.54.199 oradata]$ ls
orcl orcl2 orcl3
[oracle@10.14.54.199 oradata]$ pwd
/oracle/inst1/oradata
[oracle@10.14.54.199 oradata]$ ls
orcl orcl2 orcl3
[oracle@10.14.54.199 oradata]$ cd ..
[oracle@10.14.54.199 inst1]$ ls
admin arch database flash_recovery_area libXp-1.0.0-8.1.el5.i386.rpm oradata oraInventory product
[oracle@10.14.54.199 inst1]$ cd arch
[oracle@10.14.54.199 arch]$ ls
orcl orcl2
[oracle@10.14.54.199 arch]$ mkdir orcl3
[oracle@10.14.54.199 arch]$ ls
orcl orcl2 orcl3
启动实例测试
[oracle@10.14.54.199 arch]$ export ORACLE_SID=orcl3
[oracle@10.14.54.199 arch]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 5 17:46:40 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba;
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl3
创建密码文件：
oracle@10.14.54.199 arch]$ orapwd file=/oracle/inst1/product/10.2.0/db_1/dbs/orapworcl3 password=oracle entries=10;
四、创建数据库
4.1、编写数据库创建脚本
下面为创建数据库的脚本：
CREATE DATABASE orcl3
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/oracle/inst1/oradata/orcl3/redo01.log') SIZE 20M,
GROUP 2 ('/oracle/inst1/oradata/orcl3/redo02.log') SIZE 20M,
GROUP 3 ('/oracle/inst1/oradata/orcl3/redo03.log') SIZE 20M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracle/inst1/oradata/orcl3/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/inst1/oradata/orcl3/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1 datafile '/oracle/inst1/oradata/orcl3/tbs_1.dbf' size 50m
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/oracle/inst1/oradata/orcl3/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/oracle/inst1/oradata/orcl3/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
/
4.2、创建数据库
4.3、验证是否创建成功
查询数据库状态
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>
4.4、运行后续脚本，创建数据字典及相关视图
SQL>conn sys as sysdba
SQL>@?/rdbms/admin/catalog.sql;
此过程可能需要10分钟左右
SQL>@?/rdbms/admin/catproc.sql;
此过程可能需要15分钟左右
SQL>@?/rdbms/admin/catblock.sql;
SQL>@?/rdbms/admin/catoctk.sql;
SQL>@?/rdbms/admin/owminst.plb;
SQL>conn system/oracle
SQL>@?/sqlplus/admin/pupbld.sql;
SQL>@?/sqlplus/admin/help/hlpbld.sql helpus.sql
五、客户端连接
5.1、监听文件配置（listener.ora）
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/inst1/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/inst1/product/10.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl3)
(ORACLE_HOME = /oracle/inst1/product/10.2.0/db_1)
(SID_NAME = orcl3)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))
)
)
5.2、本地服务名称配置（tnsnames.ora）
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl2)
)
)
ORCL3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl3)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
