oracle多实例

oracle多实例.docx

Oracle_多实例启动实验.doc

oracle_用户下创建多个oracle实例的方法.docx

Oracle 多实例启动实验

 

目录

 TOC \o "1-3" \h \z \u

 

一、目的

在同一系统下启动多个oracle实例。

 

二、环境

vmware虚拟出一台linux

1 .linux Ver：Red Hat Enterprise Linux AS release 4 (Nahant Update 8)( 2.6.9-89.ELsmp) 32BIT

2. Oracle Ver：Release 10.2.0.1.0

3. Instance one: oral

     Instance two: orcl

三、过程

1.系统

a./etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1               localhost.localdomain localhost

 

192.168.0.42            dbserver

2.手动启动多实例

a. 启动监听

 [oracle@dbserver ~]$ /u01/app/oracle/product/10.2.0/db_1/bin/lsnrctl start

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-SEP-2010 10:44:24

 

Copyright (c) 1991, 2005, 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.1.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=dbserver)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                18-SEP-2010 10:44:24

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=dbserver)(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...

The command completed successfully

b. 启动oral

[oracle@dbserver ~]$ export ORACLE_SID=oral

[oracle@dbserver ~]$ env | grep SID   

ORACLE_SID=oral

[oracle@dbserver ~]$ /u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog  

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 18 10:46:02 2010

 

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

 

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              75499764 bytes

Database Buffers           88080384 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL>

c. 启动orcl

[oracle@dbserver ~]$ export ORACLE_SID=orcl   

[oracle@dbserver ~]$ env | grep SID   

ORACLE_SID=orcl

[oracle@dbserver ~]$ /u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 18 10:47:51 2010

 

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

 

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              92276980 bytes

Database Buffers           71303168 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL>

d. 查看运行实例

 [oracle@dbserver ~]$ /u01/app/oracle/product/10.2.0/db_1/bin/lsnrctl status

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-SEP-2010 10:48:35

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                18-SEP-2010 10:44:24

Uptime                    0 days 0 hr. 4 min. 10 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=dbserver)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "LHYAOO" has 1 instance(s).

  Instance "oral", status READY, has 1 handler(s) for this service...

Service "LHYAOO_XPT" has 1 instance(s).

  Instance "oral", status READY, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "PSCSH" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "PSCSH_XPT" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "oralXDB" has 1 instance(s).

  Instance "oral", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@dbserver ~]$

3.自动启动多实例

a. 配备oratab

 [oracle@dbserver ~]$ cat /etc/oratab

#

 

 

 

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by the Database Configuration Assistant when creating

# a database.

 

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME::

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

#

oral:/u01/app/oracle/product/10.2.0/db_1:Y

orcl:/u01/app/oracle/product/10.2.0/db_1:Y

b. 配置自启动服务

[root@dbserver init.d]# cat /etc/init.d/oracle

#!/bin/sh

. /etc/rc.d/init.d/functions

 

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

LSNR_PORT=1521

ORACLE_USER=oracle

 

start()

{

    su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl start"

    su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbstart"

}

 

stop()

{

    su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbshut"

    su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl stop"

}

 

status()

{

    su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl status"

}

 

case "$1" in

  start)

        start

        ;;

  stop)

        stop

        ;;

  restart)

        stop

        start

        ;;

  status)

        status oracle

        ;;

  *)

        echo $"Usage: $0 {start|stop|restart|status}"

        exit 1

esac

 

exit 0

 

c. 运行服务

 [root@dbserver init.d]# /etc/init.d/oracle start

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-SEP-2010 10:58:14

 

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

 

TNS-01106: Listener using listener name LISTENER has already been started

Failed to auto-start Oracle Net Listene using /ade/vikrkuma_new/oracle/bin/tnslsnr

Processing Database instance "oral": log file /u01/app/oracle/product/10.2.0/db_1/startup.log

Processing Database instance "orcl": log file /u01/app/oracle/product/10.2.0/db_1/startup.log

d. 查看运行实例

[root@dbserver init.d]# /etc/init.d/oracle status

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-SEP-2010 10:58:39

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                18-SEP-2010 10:44:24

Uptime                    0 days 0 hr. 14 min. 15 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=dbserver)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "LHYAOO" has 1 instance(s).

  Instance "oral", status READY, has 1 handler(s) for this service...

Service "LHYAOO_XPT" has 1 instance(s).

  Instance "oral", status READY, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "PSCSH" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "PSCSH_XPT" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "oralXDB" has 1 instance(s).

  Instance "oral", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

二种情况：
1、一个监听在一个IP一个端口上监听多个实例
2、一个监听在一个IP不同端口上监听多个实例

1.oracle服务端的配置

（1）首先是创建1个实例broada，默认有个实例orcl。修改配置文件listener.ora和tnsnames.ora

情况1：

listener.ora文件

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)

      (SID_NAME = orcl)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = broada)

      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)

      (SID_NAME = broada)

    )

  )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1521))

    )

  )

 

 

tnsnames.ora文件

BROADA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = broada)

    )

  )

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

情况2：

listener.ora文件

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)

      (SID_NAME = orcl)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = broada)

      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)

      (SID_NAME = broada)

    )

  )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1522))

    )

  )

 

 

tnsnames.ora文件

BROADA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = broada)

    )

  )

 

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = broada-testteam)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

 

（2）.一个侦听器侦听多个数据库，如下配置，数据库名和SID分别是orcl和broada，

 

（3）.在oracle\product\10.2.0\db_1\BIN里面运行LSNRCTL.EXE，先执行stop，再执行start。用status查看监听器的状态。

 

 

2.Oracle客户端的配置

情况1：

不需要改listener.ora文件

tnsnames.ora文件要增加如下内容

orcl =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

broada =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = broada)

    )

  )

 

情况2：

listener.ora文件要增加如下内容：

(ADDRESS = (PROTOCOL = TCP)(HOST = china-a32af0a11)(PORT = 1522))

 

tnsnames.ora文件要增加如下内容：

orcl =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

broada =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.126)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = broada)

    )

  )

 

有个问题，在客户端下只能用sqlplus system/system@ broada 或者sqlplus system/system@ orcl。如果加as sysdba的话就会报错ORA-01031: insufficient privileges

 

经过验证，oracle下是可以一个监听在一个IP一个端口上监听多个实例，一个监听在一个IP不同端口上监听多个实例同时启动的。BCC暂时没有监测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，配置文件中放在后面的设置会覆盖前面的设置。

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

[oracle@rhel-2 dbs]$ ls

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

hc_orcl.dat   init.ora    lkORCL        orapworcl  spfileorcl2.ora

[oracle@rhel-2 dbs]$ pwd

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

[oracle@rhel-2 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@rhel-2 ~]$ cd /oracle/inst1/admin/

[oracle@rhel-2 admin]$ mkdir orcl3

[oracle@rhel-2 admin]$ ls

orcl  orcl2  orcl3

[oracle@rhel-2 admin]$ cd orcl3

[oracle@rhel-2 orcl3]$ mkdir adump bdump cdump udump

[oracle@rhel-2 orcl3]$ cd /oracle/inst1/oradata/

[oracle@rhel-2 oradata]$ mkdir orcl3

[oracle@rhel-2 oradata]$ ls

orcl  orcl2  orcl3

[oracle@rhel-2 oradata]$ pwd

/oracle/inst1/oradata

[oracle@rhel-2 oradata]$ ls

orcl  orcl2  orcl3

[oracle@rhel-2 oradata]$ cd ..

[oracle@rhel-2 inst1]$ ls

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

[oracle@rhel-2 inst1]$ cd arch

[oracle@rhel-2 arch]$ ls

orcl  orcl2

[oracle@rhel-2 arch]$ mkdir orcl3

[oracle@rhel-2 arch]$ ls

orcl  orcl2  orcl3

 

 

 

启动实例测试

[oracle@rhel-2 arch]$ export ORACLE_SID=orcl3

[oracle@rhel-2 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@rhel-2 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 = rhel-2)(PORT = 1521))

    )

  )

 

 

 

 

5.2、本地服务名称配置（tnsnames.ora）

# Generated by Oracle configuration tools.

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rhel-2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

ORCL2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rhel-2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl2)

    )

  )

ORCL3 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rhel-2)(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)

    )

  )