Home >Database >Mysql Tutorial >Oracle10g多实例配置一例

Oracle10g多实例配置一例

WBOYOriginal: 2016-06-07 14:53:18958browse

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

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

(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

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：oracle中如何判断本表有多少张表对其有外键关联Next article：Oracle多表级联更新详解

See more