一个oracle 用户下面创建多个实例

一个oracle 用户下面创建多个实例


2012/12/10

v1.0

前言

本文主要介绍了同一个oracle 用户下面如何重建多个oracle 实例。本文的测试环境为redhat 5.4,但该方法适应用大部分unix 系统(aix、hp-ux、suse linux等),本人也在AIX 5.3 系统上面实践过,通过此方法创建实例不存在任何问题。

一、环境介绍

系统:rhel 5.4

oracle 版本:10.2.0.1

原实例:

用户:oracle

oracle sid: orcl

oracle_base:/oracle/inst1

oracle_home: /oracle/inst1/product/10.2.0/db_1

数据文件:/oracle/inst1/oradata/orcl

参数文件:/oracle/inst1/product/10.2.0/db_1/dbs

控制文件:/oracle/inst1/oradata/orcl

归档路径:/oracle/inst1/arch/orcl

新实例:

用户:oracle

oracle sid: orcl3

oracle home: /oracle/inst1/ product/10.2.0/db_1

oracle_base:/oracle/inst1

oracle_home: /oracle/inst1/product/10.2.0/db_1

数据文件:/oracle/inst1/oradata/orcl3

参数文件:/oracle/inst1/product/10.2.0/db_1/dbs

控制文件:/oracle/inst1/oradata/orcl3

归档路径:/oracle/inst1/arch/orcl3

二、设置用户环境变量

说明:这里我们把新的oracle sid 加到用户的配置文件中(.bash_profile),其实不加入也没关系,加入只是为了便于其他管理和维护人员能够更好的理解和维护。

添加新的实例的SID

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

TMP=/tmp;

export TMP

TMPDIR=$TMP;

export TMPDIR

ORACLE_BASE=/oracle/inst1;

export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;

export ORACLE_HOME

ORACLE_SID=orcl;

ORACLE_SID=orcl3;

export ORACLE_SID

ORACLE_TERM=xterm;

export ORACLE_TERM

PATH=/usr/sbin:$PATH;

export PATH

PATH=$ORACLE_HOME/bin:$PATH;

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

export CLASSPATH

unset USERNAME

注意:oracle sid 环境变量为默认的环境变量,即默认环境变量为orcl3,配置文件中放在后面的设置会覆盖前面的设置。

用Xmanager 连接到服务器上。Su – oracle

[oracle@10.14.54.199 dbs]# su – oracle

[oracle@10.14.54.199 dbs]$dbca 创建2个数据库

三、创建新实例的参数文件

[oracle@10.14.54.199 dbs]$ ls

hc_orcl2.dat  initdw.ora  initorcl.ora  lkORCL2    orapworcl2       spfileorcl.ora

hc_orcl.dat   init.ora    lkORCL        orapworcl  spfileorcl2.ora

[oracle@10.14.54.199 dbs]$ pwd

/oracle/inst1/product/10.2.0/db_1/dbs

[oracle@10.14.54.199 dbs]$ cp initorcl.ora initorcl3.ora

注:这里我们通过拷贝原实例参数文件的方法进行创建,然后再进行修改。

修改新实例的参数文件

orcl.__db_cache_size=184549376

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=88080384

orcl.__streams_pool_size=0

*.audit_file_dest='/oracle/inst1/admin/orcl3/adump'

*.background_dump_dest='/oracle/inst1/admin/orcl3/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/oracle/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'

*.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=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/oracle/inst1/admin/orcl3/udump'

创建新实例的相关目录:

[oracle@10.14.54.199 ~]$ cd /oracle/inst1/admin/

[oracle@10.14.54.199 admin]$ mkdir orcl3

[oracle@10.14.54.199 admin]$ ls

orcl  orcl2  orcl3

[oracle@10.14.54.199 admin]$ cd orcl3

[oracle@10.14.54.199 orcl3]$ mkdir adump bdump cdump udump

[oracle@10.14.54.199 orcl3]$ cd /oracle/inst1/oradata/

[oracle@10.14.54.199 oradata]$ mkdir orcl3

[oracle@10.14.54.199 oradata]$ ls

orcl  orcl2  orcl3

[oracle@10.14.54.199 oradata]$ pwd

/oracle/inst1/oradata

[oracle@10.14.54.199 oradata]$ ls

orcl  orcl2  orcl3

[oracle@10.14.54.199 oradata]$ cd ..

[oracle@10.14.54.199 inst1]$ ls

admin  arch  database  flash_recovery_area  libXp-1.0.0-8.1.el5.i386.rpm  oradata  oraInventory  product

[oracle@10.14.54.199 inst1]$ cd arch

[oracle@10.14.54.199 arch]$ ls

orcl  orcl2

[oracle@10.14.54.199 arch]$ mkdir orcl3

[oracle@10.14.54.199 arch]$ ls

orcl  orcl2  orcl3

启动实例测试

[oracle@10.14.54.199 arch]$ export ORACLE_SID=orcl3

[oracle@10.14.54.199 arch]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 5 17:46:40 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba;

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

orcl3

创建密码文件:

oracle@10.14.54.199 arch]$ orapwd file=/oracle/inst1/product/10.2.0/db_1/dbs/orapworcl3 password=oracle entries=10;

四、创建数据库

4.1、编写数据库创建脚本

下面为创建数据库的脚本:

CREATE DATABASE orcl3
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY manager
   LOGFILE GROUP 1 ('/oracle/inst1/oradata/orcl3/redo01.log') SIZE 20M,
           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
   MAXDATAFILES 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/oradata/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'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/oracle/inst1/oradata/orcl3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

/

4.2、创建数据库

4.3、验证是否创建成功

查询数据库状态

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL>

4.4、运行后续脚本,创建数据字典及相关视图

SQL>conn sys as sysdba

SQL>@?/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/oracle

SQL>@?/sqlplus/admin/pupbld.sql;

SQL>@?/sqlplus/admin/help/hlpbld.sql helpus.sql

五、客户端连接

5.1、监听文件配置(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 =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))

    )

  )

5.2、本地服务名称配置(tnsnames.ora)

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

ORCL2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl2)

    )

  )

ORCL3 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.54.199)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl3)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值