Oracle 10g 多实例配置一例

浏览数：24 / 时间：2015年06月12日

背景: 一次甲方组织的部署方案讨论会上，各开发商都提出了自己的服务器要求，甲方只给一台数据库先临时过渡使用，要求整合几家应用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

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

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

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