在同一個oracle_用戶下創(chuàng)建多個oracle實例的方法

1、前言 本文主要介紹了同一個oracle 用戶下面如何重建多個oracle 實例。本文的測試環(huán)境為redhat 5.4，但該方法適應(yīng)用大部分unix 系統(tǒng)（aix、hp-ux、suse linux等），本人也在AIX 5.3 系統(tǒng)上面實踐過，通過此方法創(chuàng)建實例不存在任何問題。一、環(huán)境介紹系統(tǒng)：rhel 5.4oracle 版本：原實例：用戶：oracleoracle sid: orcloracle_base:/oracle/inst1oracle_home: /oracle/inst1/product/10.2.0/db_1數(shù)據(jù)文件：/oracle/inst1/oradata/o

2、rcl參數(shù)文件：/oracle/inst1/product/10.2.0/db_1/dbs控制文件：/oracle/inst1/oradata/orcl歸檔路徑：/oracle/inst1/arch/orcl新實例：用戶：oracleoracle sid: orcl3oracle home: /oracle/inst1/ product/10.2.0/db_1oracle_base:/oracle/inst1oracle_home: /oracle/inst1/product/10.2.0/db_1數(shù)據(jù)文件：/oracle/inst1/oradata/orcl3參數(shù)文件：/oracle/ins

3、t1/product/10.2.0/db_1/dbs控制文件：/oracle/inst1/oradata/orcl3歸檔路徑：/oracle/inst1/arch/orcl3二、設(shè)置用戶環(huán)境變量說明：這里我們把新的oracle sid 加到用戶的配置文件中（.bash_profile），其實不加入也沒關(guān)系，加入只是為了便于其他管理和維護人員能夠更好的理解和維護。添加新的實例的SID# Get the aliases and functionsif -f /.bashrc ; then . /.bashrcfi# User specific environment and startup pro

4、gramsPATH=$PATH:$HOME/binexport PATHTMP=/tmp;export TMPTMPDIR=$TMP;export TMPDIRORACLE_BASE=/oracle/inst1;export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;export ORACLE_HOMEORACLE_SID=orcl;ORACLE_SID=orcl3;export ORACLE_SIDORACLE_TERM=xterm;export ORACLE_TERMPATH=/usr/sbin:$PATH;export

5、 PATHPATH=$ORACLE_HOME/bin:$PATH;export PATHLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;export CLASSPATHunset USERNAME注意：oracle sid 環(huán)境變量為默認的環(huán)境變量，即默認環(huán)境變量為orcl3，配置文件中放在后面的設(shè)置會覆蓋前面的設(shè)置。三、創(chuàng)建新實例的參數(shù)文件oraclerhel-2 d

6、bs$ lshc_orcl2.dat initdw.ora initorcl.ora lkORCL2 orapworcl2 spfileorcl.orahc_orcl.dat init.ora lkORCL orapworcl spfileorcl2.oraoraclerhel-2 dbs$ pwd/oracle/inst1/product/10.2.0/db_1/dbsoraclerhel-2 dbs$ cp initorcl.ora initorcl3.ora注：這里我們通過拷貝原實例參數(shù)文件的方法進行創(chuàng)建，然后再進行修改。修改新實例的參數(shù)文件orcl._db_cache_size=184

7、549376orcl._java_pool_size=4194304orcl._large_pool_size=4194304orcl._shared_pool_size=88080384orcl._streams_pool_size=0*.audit_file_dest='/oracle/inst1/admin/orcl3/adump'*.background_dump_dest='/oracle/inst1/admin/orcl3/bdump'*.compatible='.0'*.control_files='/ora

8、cle/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

9、'*.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=9437

10、1840*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=285212672*.undo_management='AUTO'*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/oracle/inst1/admin/orcl3/udump'創(chuàng)建新實例的相關(guān)目錄：oraclerhel-2 $ cd /oracle/inst1/admin/oraclerhel-2 admin$ mkdir orcl3oracle

11、rhel-2 admin$ lsorcl orcl2 orcl3oraclerhel-2 admin$ cd orcl3oraclerhel-2 orcl3$ mkdir adump bdump cdump udumporaclerhel-2 orcl3$ cd /oracle/inst1/oradata/oraclerhel-2 oradata$ mkdir orcl3oraclerhel-2 oradata$ lsorcl orcl2 orcl3oraclerhel-2 oradata$ pwd/oracle/inst1/oradataoraclerhel-2 oradata$ lsorc

12、l orcl2 orcl3oraclerhel-2 oradata$ cd .oraclerhel-2 inst1$ lsadmin arch database flash_recovery_area libXp-1.0.0-8.1.el5.i386.rpm oradata oraInventory productoraclerhel-2 inst1$ cd archoraclerhel-2 arch$ lsorcl orcl2oraclerhel-2 arch$ mkdir orcl3oraclerhel-2 arch$ lsorcl orcl2 orcl3啟動實例測試oraclerhel-

13、2 arch$ export ORACLE_SID=orcl3oraclerhel-2 arch$ sqlplus /nologSQL*Plus: Release .0 - Production on Wed Dec 5 17:46:40 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdba;Connected to an idle instance.SQL> startup nomount;ORACLE instance started.Total System

14、 Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytesSQL> select instance_name from v$instance;INSTANCE_NAME-orcl3創(chuàng)建密碼文件：oraclerhel-2 arch$ orapwd file=/oracle/inst1/product/10.2.0/db_1/dbs/orapworcl3 password=or

15、acle entries=10;四、創(chuàng)建數(shù)據(jù)庫4.1、編寫數(shù)據(jù)庫創(chuàng)建腳本下面為創(chuàng)建數(shù)據(jù)庫的腳本：CREATE DATABASE orcl3    USER SYS IDENTIFIED BY oracle   USER SYSTEM IDENTIFIED BY manager   LOGFILE GROUP 1 ('/oracle/inst1/oradata/orcl3/redo01.log') SIZE 20M,         

16、;  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   M

17、AXDATAFILES 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/oradat

18、a/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'   

19、0;  SIZE 20M REUSE   UNDO TABLESPACE undotbs1      DATAFILE '/oracle/inst1/oradata/orcl3/undotbs01.dbf'      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED/4.2、創(chuàng)建數(shù)據(jù)庫4.3、驗證是否創(chuàng)建成功查詢數(shù)據(jù)庫狀態(tài)SQL> select status from v$instance;STAT

20、US-OPENSQL>4.4、運行后續(xù)腳本，創(chuàng)建數(shù)據(jù)字典及相關(guān)視圖SQL>conn sys as sysdbaSQL>?/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/oracleSQL>?/sqlplus

21、/admin/pupbld.sql;SQL>?/sqlplus/admin/help/hlpbld.sql helpus.sql五、客戶端連接5.1、監(jiān)聽文件配置（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 = (DESCRI