背景: 一次甲方组织的部署方案讨论会上，各开发商都提出了自己的服务器要求，甲方只给一台数据库先临时过渡使用，要求整合几家应用oracle数据库; 我公司做为技术整合方虽然提出各种建议，最终无果，于是提出两种方案： 第一种：一个oracle数据库实例中多方案
背景: 一次甲方组织的部署方案讨论会上，各开发商都提出了自己的服务器要求，甲方只给一台数据库先临时过渡使用，要求整合几家应用oracle数据库; 我公司做为技术整合方虽然提出各种建议，最终无果，于是提出两种方案：
第一种：一个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'
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