《在同一个oracle_用户下创建多个oracle实例的方法.pdf》由会员分享，可在线阅读，更多相关《在同一个oracle_用户下创建多个oracle实例的方法.pdf（16页珍藏版）》请在三一文库上搜索。
1、前言 本文主要介绍了同一个oracle 用户下面如何重建多个oracle 实 例。本文的测试环境为redhat 5.4 ，但该方法适应用大部分unix 系 统（aix 、hp-ux 、suselinux等） ，本人也在 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 数据文件： /ora
2、cle/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/in
3、st1/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 /.bash
4、rc ; 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;
5、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/r dbms/jlib; export CLASSPATH unset USER
6、NAME 注意： oracle sid 环境变量为默认的环境变量，即默认环境变量为 orcl3 ，配置文件中放在后面的设置会覆盖前面的设置。 三、创建新实例的参数文件 oraclerhel-2 dbs$ ls hc_orcl2.dat initdw.orainitorcl.ora lkORCL2 orapworcl2 spfileorcl.ora hc_orcl.dat init.oralkORCLorapworcl spfileorcl2.ora oraclerhel-2 dbs$ pwd /oracle/inst1/product/10.2.0/db_1/dbs oraclerhel-2
7、dbs$ cpinitorcl.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=
8、/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_multibl
9、ock_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
10、=94371840 *.processes=150 *.remote_login_passwordfile=EXCLUSIVE *.sga_target=285212672 *.undo_management=AUTO *.undo_tablespace=UNDOTBS1 *.user_dump_dest=/oracle/inst1/admin/orcl3/udump 创建新实例的相关目录： oraclerhel-2 $ cd /oracle/inst1/admin/ oraclerhel-2 admin$ mkdir orcl3 oraclerhel-2 admin$ ls orcl orc
11、l2 orcl3 oraclerhel-2 admin$ cd orcl3 oraclerhel-2 orcl3$ mkdiradumpbdumpcdumpudump oraclerhel-2 orcl3$ cd /oracle/inst1/oradata/ oraclerhel-2 oradata$ mkdir orcl3 oraclerhel-2 oradata$ ls orcl orcl2 orcl3 oraclerhel-2 oradata$ pwd /oracle/inst1/oradata oraclerhel-2 oradata$ ls orcl orcl2 orcl3 orac
12、lerhel-2 oradata$ cd oraclerhel-2 inst1$ ls admin arch database flash_recovery_area libXp-1.0.0-8.1.el5.i386.rpm oradataoraInventory product oraclerhel-2 inst1$ cd arch oraclerhel-2 arch$ ls orcl orcl2 oraclerhel-2 arch$ mkdir orcl3 oraclerhel-2 arch$ ls orcl orcl2 orcl3 启动实例测试 oraclerhel-2 arch$ ex
13、port ORACLE_SID=orcl3 oraclerhel-2 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 Ar
14、ea 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 创建密码文件： oraclerhel-2 arch$ orapwd file=/oracle/inst1/product/10.2.0/db_1/dbs/orapworcl3 password=oracle
15、 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/red
16、o03.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 S
17、IZE 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 M
18、AXSIZE UNLIMITED / 4.2、创建数据库 4.3、验证是否创建成功 查询数据库状态 SQL select status from v$instance; STATUS - OPEN SQL 4.4、运行后续脚本，创建数据字典及相关视图 SQLconn 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; S
19、QL?/rdbms/admin/owminst.plb; SQLconn system/oracle SQL?/sqlplus/admin/pupbld.sql; SQL?/sqlplus/admin/help/hlpbld.sqlhelpus.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)
20、) (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 = rhe
21、l-2)(PORT = 1521) ) ) 5.2、本地服务名称配置（tnsnames.ora ） # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rhel-2)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCL2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rhel-2)(
22、PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl2) ) ) ORCL3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rhel-2)(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) ) )