第一种：一个oracle数据库实例中多方案(用户)方式部署，一个应用分配一个数据库帐号（用户）。 第二种：一个是采用多实例方式部署，一个应用一个单独实例。 两种方案各有利弊；因各开发商都说自己数据敏感，于是提出选择了多实例部署的方案，多实例的方式就是在一个oracle数据库服务器创建多个数据库实例，同时运行，应用数据库层面互不干扰。 多实例创建方式很多种，这里以两个实例为例： (1) 一个数据库实例创建好后，再直接采用DBCA创建另一个实例，适合全新安装，比较方便快速。 (2) 通过现有一个实例数据库克隆一个数据库实例。 (3) 通过rman备份恢复在本机恢复一个数据库实例。 安装多实例需要注意的问题，内存占用量大，两个数据库实例都要各自分配SGA，PGA等内存，对两个实例内存的分配注意控制，以免过多分配内存对主机系统造成影响。 本文第二种方式部署多实例，仅只做安装测试，对于sga内存分配等内容本例忽略掉了, 通过本文对数据库的物理结构进行了一次复习，该方式对11g for linux版本一样适用。 1. 数据库环境 (1) 现在一个数据库情况 操作系统版本 : OEL5.8 x64 数据库版本 : Oracle 10.2.0.5 x64 数据库名 : orcl 数据库SID : orcl 实例名 : orcl 数据库文件路径: /u01/app/oracle/oradata/orcl/ (2) 待克隆的数据库 数据库名 : abc 数据库SID : abc 实例名 : abc 数据库文件路径: /u01/app/oracle/oradata/abc/ 说明：两个数据库实例采用不同目录结构与数据库名称。 2. 准备abc实例的目录结构 # su - oracle $ mkdir -p /u01/app/oracle/admin/abc/{adump,bdump,cdump,dpdump,udump,pfile} $ mkdir -p /u01/app/oracle/oradata/abc/ 3. 准备abc实例的参数文件 通过现有orcl实例的参数文件进行修改。 $ sqlplus /nolog SQL> conn / as sysdba; SQL> create pfile from spfile; SQL> host cp $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initabc.ora SQL> host vi $ORACLE_HOME/dbs/initabc.ora #将orcl改为abc实例，注意路径是否正确。 abc.__db_cache_size=293601280 abc.__java_pool_size=4194304 abc.__large_pool_size=4194304 abc.__shared_pool_size=117440512 abc.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/abc/adump' *.background_dump_dest='/u01/app/oracle/admin/abc/bdump' *.compatible='10.2.0.5.0' *.control_files='/u01/app/oracle/oradata/abc/control01.ctl','/u01/app/oracle/oradata/abc/control02.ctl','/u01/app/oracle/oradata/abc /control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/abc/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='abc' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=abcXDB)' *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=141557760 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=425721856 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/abc/udump' 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 4. 创建控制文件SQL语句 由于控制文件中包括数据库文件位置，实例名等数据，需要重新根据新的实例名与数据文件及文件路径创建控制文件。 根据control文件跟踪文件创建控制文件。 SQL> alter database backup controlfile to trace; # 查看刚才创建的跟综文件的文件名: SQL> oradebug setmypid SQL> oradebug tracefile_name /u01/app/oracle/admin/orcl/udump/orcl_ora_4044.trc SQL> ! cat /u01/app/oracle/admin/orcl/udump/orcl_ora_4044.trc # 按如下格式，如果有其它数据文件，可以按此格式加入。 CREATE CONTROLFILE set DATABASE "ABC" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/abc/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/abc/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/abc/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/abc/system01.dbf', '/u01/app/oracle/oradata/abc/undotbs01.dbf', '/u01/app/oracle/oradata/abc/sysaux01.dbf', '/u01/app/oracle/oradata/abc/users01.dbf' CHARACTER SET ZHS16GBK ; 5. 创建密码文件 $ orapwd file=$ORACLE_HOME/dbs/orapwabc password=oracle entries=10 6. 多实例监听与服务名配置 (1) 添加静态监听配置,实现单IP,多实例 $ vi $ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = orcl) ) (SID_DESC = (SID_NAME = abc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = abc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ) (2) 命令服务配置 $ vi $ORACLE_HOME/network/admin/tnsnames.ora ABC = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521)) ) (CONNECT_DATA = (SID = abc) (SERVICE = DEDICATED) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl) (SERVICE = DEDICATED) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) $ lsnrctl stop $ lsnrctl start LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 14-11014 22:07:31 Copyright (c) 1991, 2010, Oracle. All rights reserved. Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.5.0 - Production System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production Start Date 14-11014 22:07:31 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "abc" has 1 instance(s). Instance "abc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully 7. 拷贝orcl实例的数据文件 (1) 数据库必须在一致性状态下进行拷贝，需要关闭orcl数据库实例。 $ sqlplus / as sysdba; SQL> shutdown immediate; SQL> quit (2) 拷贝orcl实例的所有数据文件 $ cp /u01/app/oracle/oradata/orcl/*.dbf /u01/app/oracle/oradata/abc/ $ ll /u01/app/oracle/oradata/abc/ total 738672 -rw-r----- 1 oracle oinstall 262152192 Nov 14 22:12 sysaux01.dbf -rw-r----- 1 oracle oinstall 461381632 Nov 14 22:12 system01.dbf -rw-r----- 1 oracle oinstall 20979712 Nov 14 22:12 temp01.dbf -rw-r----- 1 oracle oinstall 26222592 Nov 14 22:12 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Nov 14 22:12 users01.dbf $ 8. 正启开始创建abc实例 (1) 通过abc SID启动数据库 [oracle@node1 ~]$ export ORACLE_SID=abc [oracle@node1 ~]$ sqlplus / as sysdba; SQL*Plus: Release 10.2.0.5.0 - Production on 14 22:18:52 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> (2) 启动abc实例到nomount状态 因为有abc实例的pfile参数文件，可以将数据库启动到nomount状态. SQL> startup nomount; ORACLE instance started. Total System Global Area 427819008 bytes Fixed Size 2096792 bytes Variable Size 125829480 bytes Database Buffers 293601280 bytes Redo Buffers 6291456 bytes SQL> (3) 在nomount状态下创建生成控制文件，手动复制粘贴上面创建的生成控制文件语句。 SQL> CREATE CONTROLFILE set DATABASE "ABC" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/abc/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/abc/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/abc/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/abc/system01.dbf', '/u01/app/oracle/oradata/abc/undotbs01.dbf', '/u01/app/oracle/oradata/abc/sysaux01.dbf', '/u01/app/oracle/oradata/abc/users01.dbf' CHARACTER SET ZHS16GBK 18 ; Control file created. SQL> SQL> (4) resetlogs启动数据库 resetlogs启动数据库同时重新生成创建redo日志文件。 SQL> alter database open resetlogs; Database altered. (5) 通过abc实例的pfile文件创建spfile文件 SQL> create spfile from pfile; (6) 添加一个临时数据文件 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/abc/temp01.dbf' 9. 更改Oracle数据库DBID (1)在nid之前必须要shutdown immediate一次再mount状态; SQL> shutdown immediate; SQL> startup mount; SQL> quit (2) nid修改数据库名 [oracle@node1 ~]$ nid target=sys DBNEWID: Release 10.2.0.5.0 - Production on 14 22:46:50 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. Password: Connected to database ABC (DBID=1390560469) Connected to server version 10.2.0 Control Files in database: /u01/app/oracle/oradata/abc/control01.ctl /u01/app/oracle/oradata/abc/control02.ctl /u01/app/oracle/oradata/abc/control03.ctl Change database ID of database ABC? (Y/[N]) => y Proceeding with operation Changing database ID from 1390560469 to 1819805470 Control File /u01/app/oracle/oradata/abc/control01.ctl - modified Control File /u01/app/oracle/oradata/abc/control02.ctl - modified Control File /u01/app/oracle/oradata/abc/control03.ctl - modified Datafile /u01/app/oracle/oradata/abc/system01.dbf - dbid changed Datafile /u01/app/oracle/oradata/abc/undotbs01.dbf - dbid changed Datafile /u01/app/oracle/oradata/abc/sysaux01.dbf - dbid changed Datafile /u01/app/oracle/oradata/abc/users01.dbf - dbid changed Datafile /u01/app/oracle/oradata/abc/temp01.dbf - dbid changed Control File /u01/app/oracle/oradata/abc/control01.ctl - dbid changed Control File /u01/app/oracle/oradata/abc/control02.ctl - dbid changed Control File /u01/app/oracle/oradata/abc/control03.ctl - dbid changed Instance shut down (3) 验证数据库名修改，最终修改成功。 $ sqlplus /nolog SQL> conn / as sysdba; SQL> startup mount; SQL> alter database open resetlogs; SQL> select dbid,name from v$database; DBID NAME ---------- --------- 1819805470 ABC SQL> 10. 配置实例开机自启动 (1) 配置oracle开机启动，加入abc实例自启动开关项 # vi /etc/oratab orcl:/u01/app/oracle/product/10.2.0/db_1:Y abc:/u01/app/oracle/product/10.2.0/db_1:Y (2) 设置开机启动 # vi /etc/rc.local #!/bin/sh su - oracle -c "lsnrctl start" su - oracle -c "dbstart" (3) 登录到数据库服务器上手动的启动与关闭数据库实例方法 进入orcl实例的方法； $ export ORACLE_SID=orcl $ sqlplus /nolog SQL> conn /as sysdba SQL> startup; 进入abc实例的方法； $ export ORACLE_SID=abc $ sqlplus /nolog SQL> conn /as sysdba SQL> startup