db user fail logon counts

oracle 10g中,缺省的profile中有10次fail logon attemps,如何查看消耗了几次”失败登录”呢?

user$记录了其中的信息,lcount /* count of failed login attempts */

也就是,lcount记录了”失败的尝试登录次数”

SQL> select name,lcount from user$ where name='TEST';

NAME                               LCOUNT
------------------------------ ----------
TEST                                    0

当前,test用户的失败登录次数为0,人为制造一个失败登录记录。

SQL> conn test/dadada
ERROR:
ORA-01017: invalid username/password; logon denied

查看test用户的失败登录次数记录。

SQL> select name,lcount from user$ where name='TEST';

NAME                               LCOUNT
------------------------------ ----------
TEST                                    1

再使用test登录,输入正确的密码,制造成功登录后,lcount会归零。

SQL> conn test/test
Connected.
SQL> conn / as sysdba
Connected.

SQL> select name,lcount from user$ where name='TEST';

NAME                               LCOUNT
------------------------------ ----------
TEST                                    0

–EOF–

oracle 11203 RAC on OEL 6 using udev based on ASM

Oracle 11G RAC在OEL6平台上的部署与OEL5基本差不多,但仍然有两个需要额外注意的地方:
1.建议使用udev的方式来管理裸磁盘分区
其实在oel5平台上,我仍然建议使用linux的udev来管理设备,而不是asmlib。而到了oel6(rhel6)平台上,asmlib干脆没有了。

2.建议安装oracle-rdbms-preinstall rpm包,搞定基本的安装前参数配置
oel5平台上,可以借鉴oracle-validated包来搞定一些安装前的配置工作,例如:/etc/sysctl.conf文件中的参数设置。而到了oel6上,那个rpm包变成了oracle-rdbms-preinstall,可以google一下,即可得到答案。 Continue reading “oracle 11203 RAC on OEL 6 using udev based on ASM”

sqlserver 2005 移动用户数据库的路径

sqlserver 2005中想要移动用户数据库的路径,可以参考如下,大概这么几个过程:

1.查询用户数据库数据文件所在的路径,包括:名称、物理名称

2.将目标数据库offline(脱机)

3.用户数据库offline之后,将原来的数据文件、事务日志文件拷贝到新的路径下

4.更改用户数据库的数据文件路径、事务日志文件路径

5.将数据库设置为online,然后查询数据文件、事务日志文件是否都在新的路径上了

详细可参考:sqlserver移动用户数据库

–EOF–

oracle 10g rac add node

目的就是为现有的一个10205双节点RAC集群再添加一个node,从当前的两个节点扩充至三个节点

1.环境的检查

首先查看双节点上os、disk space等信息。

当前rac集群的一些信息。

[oracle@node1 ~]$ olsnodes -p
node1 node1-priv
node2 node2-priv
[oracle@node1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....a1.inst application ONLINE ONLINE node1
ora....a2.inst application ONLINE ONLINE node2
ora.aaa.db application ONLINE ONLINE node1
ora....E1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application ONLINE ONLINE node1
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip application ONLINE ONLINE node1
ora....E2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application ONLINE ONLINE node2
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip application ONLINE ONLINE node2
[oracle@node1 ~]$

1.1 os版本
[oracle@node1 ~]$ echo $ORACLE_HOME
/opt/app/oracle/product/10.2.0/db_1

[oracle@node1 ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel r on an m

再准备一个os,版本为oel5u5。 Continue reading “oracle 10g rac add node”

oracle instance and listener auto start

这里记录一下oracle单实例情况下的instance和listener的自动启动,因为RAC环境中,当os启动的时候,crs会协调好自启动的事情,不用管理员额外操心。在win平台,instance和listener是以服务的形式存在于os上,缺省也是自动随着os启动的。linux环境中,管理员需要额外的操心,写个简单的脚本,让instance和listener随着os能启动起来。

$ORACLE_HOME/bin中有这么一个文件,dbstart。这个其实就是oracle准备好的现成启动脚本,其中包含了:instance、listener部分,甚至包含了asm instance部分。管理员只需将dbstart脚本中做很少的改动,然后放到linux启动顺序执行的脚本中即可,或者放到/etc/rc.local中。

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

除了/etc/oratab中将”开关”至于”Y”上,dbstart中需要额外注意两部分。

listener部分:将ORACLE_HOME_LISTNER赋予相应的值即可,这部分负责监听程序的启动。

# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=/home/oracle/product/10.2.0
if [ ! $ORACLE_HOME_LISTNER ] ; then
  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
  echo "Usage: $0 ORACLE_HOME"
else
  LOG=$ORACLE_HOME_LISTNER/listener.log

  # Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
  # a different ORACLE_HOME for each entry in the oratab.
  export ORACLE_HOME=$ORACLE_HOME_LISTNER

  # Start Oracle Net Listener
  if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
    echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1
    $ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &
    export VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`
  else
    echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
  fi
fi

instance部分:确保$ORACLE_HOME参数正确即可。

# Starts a Database Instance
startinst() {
  # Called programs use same database ID
  export ORACLE_SID

  # Put $ORACLE_HOME/bin into PATH and export.
  PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH
  # add for bug # 652997
  LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib ; export LD_LIBRARY_PATH
  PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
  SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
  SPFILE1=${ORACLE_HOME}/dbs/spfile.ora

  echo ""
  echo "$0: Starting up database "$ORACLE_SID""
  date
  echo ""

  checkversionmismatch

  # See if it is a V6 or V7 database
  VERSION=undef
  if [ -f $ORACLE_HOME/bin/sqldba ] ; then
    SQLDBA=sqldba
    VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
      /SQL*DBA: (Release|Version)/ {split($3, V, ".") ;
      print V[1]}'`
    case $VERSION in
      "6") ;;
      *) VERSION="internal" ;;
    esac
  else
    if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
      SQLDBA=svrmgrl
      VERSION="internal"
    else
      SQLDBA="sqlplus /nolog"
    fi
  fi

  STATUS=1
  if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf ] ; then
    STATUS="-1"

可以手工运行dbstart,看是否能正确的把instance和listener启动起来。

–EOF–

oracle 11.2.0.1 rac upgrade to 11.2.0.3

目标:
将现有的oracle 11.2.0.1 rac升级至11.2.0.3。使用out-of-space方式来升级。使用rolling upgrade方式升级。

rolling upgrade的好处就是:确保至少有一个node是能够对外提供服务的。

1.来自mos上的相关文档

mos上有文档关于11201升级至11203的内容,过程与从11201至11202是稍有区别的,详细参考:

Pre-requisite for 11.2.0.1 to 11.2.0.2 ASM Rolling Upgrade (Doc ID 1274629.1)

------------------------------------------------------------------------------
Upgrade to 11.2.0.3

If you are upgrading from previous version to 11.2.0.3, notice that upgrade process to 11.2.0.3 has been improved that CVU is used to verify the Oracle prerequisite patches before starting the upgrade. The source home patches are checked during the installer OUI. The target home patches are checked during rootupgrade.sh. cluvfy -upgrade command line can also be used to check the upgrade pre-requsite check.

For example, if cluvfy says it's missing patch for bug 9413827, then you need to apply patch 9413827 (instead of patch 9706490), because cluvfy on some platform only check for patch 9413827.

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

改进的地方在CVU,可以使用cluvfy来做升级之前的检查。 Continue reading “oracle 11.2.0.1 rac upgrade to 11.2.0.3”

oracle 11gR2 RAC Installation guide

打算部署oracle 11g r2 rac ASM

//操作系统的准备

1.os 版本
oel 5u9 x86_64;物理内存2G;swap 4G。安装oracle-validated包

2.oel rpm package选择

将如下的部分组件中的package全部选中安装
————————————————
Desktop Environments > GNOME Desktop Environment
Applications > Editors
Applications > Graphical Internet
Development > Development Libraries
Development > Development Tools
Servers > Server Configuration Tools
Base System > Administration Tools
Base System > Base
Base System > System Tools
Base System > X Window System
————————————————
需要额外注意:pdksh和unixODBC的rpm包,确保32位、64位的包都安装正确就可以了。

3.编辑主机的/etc/hosts文件,将其中的内容设置为类似如下: Continue reading “oracle 11gR2 RAC Installation guide”

oracle 11gR2 RAC admin(1)

1.关闭某个节点

[root@oel5-112-rac1 bin]# ./crsctl stop cluster -n oel-112-rac2
CRS-4405: The following nodes are unknown to Oracle High Availability Services:
oel-112-rac2
[root@oel5-112-rac1 bin]# ./crsctl stop cluster -n oel5-112-rac2
CRS-2673: Attempting to stop 'ora.crsd' on 'oel5-112-rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.gsd' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.OCR_VOTE.dg' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.racdb.db' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.gsd' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'oel5-112-rac2' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'oel5-112-rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.oel5-112-rac2.vip' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.oel5-112-rac2.vip' on 'oel5-112-rac2' succeeded
CRS-2672: Attempting to start 'ora.oel5-112-rac2.vip' on 'oel5-112-rac1'
CRS-2676: Start of 'ora.scan1.vip' on 'oel5-112-rac1' succeeded
CRS-2676: Start of 'ora.oel5-112-rac2.vip' on 'oel5-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'oel5-112-rac1'
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'oel5-112-rac1' succeeded
CRS-2677: Stop of 'ora.OCR_VOTE.dg' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.racdb.db' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.FLASHAREA.dg' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.DATA.dg' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.FLASHAREA.dg' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.asm' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.ons' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.ons' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.net1.network' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.eons' on 'oel5-112-rac2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'oel5-112-rac2' has completed
CRS-2677: Stop of 'ora.crsd' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.evmd' on 'oel5-112-rac2'
CRS-2673: Attempting to stop 'ora.asm' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.cssdmonitor' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'oel5-112-rac2' succeeded
CRS-2677: Stop of 'ora.asm' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.cssd' on 'oel5-112-rac2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'oel5-112-rac2'
CRS-2677: Stop of 'ora.diskmon' on 'oel5-112-rac2' succeeded

在oel5-112-rac2上的resource应该都关闭掉了,可以在另外一个节点oel5-112-rac1上看到关闭的resource都启动到另外节点了。

从下面这条信息可以看出,rac2节点上的crs被关闭了 Continue reading “oracle 11gR2 RAC admin(1)”

oracle 11gR2 RAC “oc4j” and “gsd” resource offline

11gR2 RAC安装完毕后,缺省oc4j这个resource是offline的。

[grid@oel5-112-rac1 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    oel5...rac1
ora....AREA.dg ora....up.type ONLINE    ONLINE    oel5...rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    oel5...rac1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    oel5...rac2
ora....VOTE.dg ora....up.type ONLINE    ONLINE    oel5...rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    oel5...rac1
ora.eons       ora.eons.type  ONLINE    ONLINE    oel5...rac1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    oel5...rac1
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE
ora....SM1.asm application    ONLINE    ONLINE    oel5...rac1
ora....C1.lsnr application    ONLINE    ONLINE    oel5...rac1
ora....ac1.gsd application    OFFLINE   OFFLINE
ora....ac1.ons application    ONLINE    ONLINE    oel5...rac1
ora....ac1.vip ora....t1.type ONLINE    ONLINE    oel5...rac1
ora....SM2.asm application    ONLINE    ONLINE    oel5...rac2
ora....C2.lsnr application    ONLINE    ONLINE    oel5...rac2
ora....ac2.gsd application    OFFLINE   OFFLINE
ora....ac2.ons application    ONLINE    ONLINE    oel5...rac2
ora....ac2.vip ora....t1.type ONLINE    ONLINE    oel5...rac2
ora.ons        ora.ons.type   ONLINE    ONLINE    oel5...rac1
ora.racdb.db   ora....se.type ONLINE    ONLINE    oel5...rac1
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    oel5...rac2

尝试启动oc4j的resource

[grid@oel5-112-rac1 ~]$ srvctl start oc4j
OC4J could not be started
PRCR-1079 : Failed to start resource ora.oc4j
CRS-2501: Resource 'ora.oc4j' is disabled

需要先enable oc4j这个resource

[grid@oel5-112-rac1 ~]$ srvctl enable oc4j

再尝试启动oc4j这个resource

[grid@oel5-112-rac1 ~]$ srvctl start oc4j

[grid@oel5-112-rac1 ~]$ crs_stat -t|grep oc4j

ora.oc4j ora.oc4j.type ONLINE ONLINE oel5…rac1

还剩下gsd三个resource是offline的,接下来搞定gsd相关resource。

[grid@oel5-112-rac1 ~]$ srvctl status nodeapps
VIP oel5-112-rac1-vip is enabled
VIP oel5-112-rac1-vip is running on node: oel5-112-rac1
VIP oel5-112-rac2-vip is enabled
VIP oel5-112-rac2-vip is running on node: oel5-112-rac2
Network is enabled
Network is running on node: oel5-112-rac1
Network is running on node: oel5-112-rac2
GSD is disabled
GSD is not running on node: oel5-112-rac1
GSD is not running on node: oel5-112-rac2
ONS is enabled
ONS daemon is running on node: oel5-112-rac1
ONS daemon is running on node: oel5-112-rac2
eONS is enabled
eONS daemon is running on node: oel5-112-rac1
eONS daemon is running on node: oel5-112-rac2

可以看出:GSD is disabled,思路还是先enable GSD,然后启动

[grid@oel5-112-rac1 ~]$ srvctl enable nodeapps
PRKO-2415 : VIP is already enabled on node(s): oel5-112-rac1,oel5-112-rac2
PRKO-2416 : Network resource is already enabled.
PRKO-2417 : ONS is already enabled on node(s): oel5-112-rac1,oel5-112-rac2
PRKO-2418 : eONS is already enabled on node(s): oel5-112-rac1,oel5-112-rac2

再次查看nodeapps的enable情况

[grid@oel5-112-rac1 ~]$ srvctl status nodeapps
VIP oel5-112-rac1-vip is enabled
VIP oel5-112-rac1-vip is running on node: oel5-112-rac1
VIP oel5-112-rac2-vip is enabled
VIP oel5-112-rac2-vip is running on node: oel5-112-rac2
Network is enabled
Network is running on node: oel5-112-rac1
Network is running on node: oel5-112-rac2
GSD is enabled
GSD is not running on node: oel5-112-rac1
GSD is not running on node: oel5-112-rac2
ONS is enabled
ONS daemon is running on node: oel5-112-rac1
ONS daemon is running on node: oel5-112-rac2
eONS is enabled
eONS daemon is running on node: oel5-112-rac1
eONS daemon is running on node: oel5-112-rac2

启动GSD服务

[grid@oel5-112-rac1 ~]$ srvctl start nodeapps
PRKO-2421 : Network resource is already started on node(s): oel5-112-rac1,oel5-112-rac2
PRKO-2420 : VIP is already started on node(s): oel5-112-rac1,oel5-112-rac2
PRKO-2420 : VIP is already started on node(s): oel5-112-rac1,oel5-112-rac2
PRKO-2422 : ONS is already started on node(s): oel5-112-rac1,oel5-112-rac2
PRKO-2423 : eONS is already started on node(s): oel5-112-rac1,oel5-112-rac2

gsd相关的nodeapps都启动了

[grid@oel5-112-rac1 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    oel5...rac1
ora....AREA.dg ora....up.type ONLINE    ONLINE    oel5...rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    oel5...rac1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    oel5...rac2
ora....VOTE.dg ora....up.type ONLINE    ONLINE    oel5...rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    oel5...rac1
ora.eons       ora.eons.type  ONLINE    ONLINE    oel5...rac1
ora.gsd        ora.gsd.type   ONLINE    ONLINE    oel5...rac1
ora....network ora....rk.type ONLINE    ONLINE    oel5...rac1
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    oel5...rac1
ora....SM1.asm application    ONLINE    ONLINE    oel5...rac1
ora....C1.lsnr application    ONLINE    ONLINE    oel5...rac1
ora....ac1.gsd application    ONLINE    ONLINE    oel5...rac1
ora....ac1.ons application    ONLINE    ONLINE    oel5...rac1
ora....ac1.vip ora....t1.type ONLINE    ONLINE    oel5...rac1
ora....SM2.asm application    ONLINE    ONLINE    oel5...rac2
ora....C2.lsnr application    ONLINE    ONLINE    oel5...rac2
ora....ac2.gsd application    ONLINE    ONLINE    oel5...rac2
ora....ac2.ons application    ONLINE    ONLINE    oel5...rac2
ora....ac2.vip ora....t1.type ONLINE    ONLINE    oel5...rac2
ora.ons        ora.ons.type   ONLINE    ONLINE    oel5...rac1
ora.racdb.db   ora....se.type ONLINE    ONLINE    oel5...rac1
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    oel5...rac2

一个疑问:

缺省oracle 11g r2 rac安装完毕后,gsd和oc4j都是disable的?那干嘛放到crs中呢?

–EOF–

CRS-0184: Cannot communicate with the CRS daemon

客户那边发来了个报错信息:CRS-0184: Cannot communicate with the CRS daemon。这个报错太泛泛了。大致的意思是crs的通讯存在问题,也可能crs就没有启动。

客户的环境:oracle linux 5 update 9 +oracle 11.2.0.1 双节点 RAC。

远程登录用户的环境,执行了几条基本健康状况的查询。

查看CRS状态,CRS状态明显不对了。

[grid@oel5-112-rac1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

查看votedisk状态,votedisk状态正常。


[grid@oel5-112-rac1 ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   272b2097f2ef4fb4bf8cfd17fe8e7f69 (/dev/oracleasm/disks/OCR_VOTE03) [OCR_VOTE]
 2. ONLINE   9de9a00377c74ffdbf71248433b9f6c6 (/dev/oracleasm/disks/OCR_VOTE02) [OCR_VOTE]
 3. ONLINE   74b44406eb034f08bf3687d5a61af96d (/dev/oracleasm/disks/OCR_VOTE01) [OCR_VOTE]
Located 3 voting disk(s).

查看OCR的状态,OCR也是ok的。

[grid@oel5-112-rac1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2536
         Available space (kbytes) :     259584
         ID                       :   13378993
         Device/File Name         :  +ocr_vote
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

既然ocr和votedisk都是正常的,而ocr和votedisk都在ASM disk group->+OCR_VOTE上,很可能是asm instance没有启动。

启动asm instance,问题得到解决。

[grid@oel5-112-rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 17 13:48:33 2013

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

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size                  2212656 bytes
Variable Size             256552144 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

再次查看整个集群的状态

[grid@oel5-112-rac2 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    oel5...rac1
ora....AREA.dg ora....up.type ONLINE    ONLINE    oel5...rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    oel5...rac1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    oel5...rac2
ora....VOTE.dg ora....up.type ONLINE    ONLINE    oel5...rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    oel5...rac1
ora.eons       ora.eons.type  ONLINE    ONLINE    oel5...rac1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    oel5...rac1
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE
ora....SM1.asm application    ONLINE    ONLINE    oel5...rac1
ora....C1.lsnr application    ONLINE    ONLINE    oel5...rac1
ora....ac1.gsd application    OFFLINE   OFFLINE
ora....ac1.ons application    ONLINE    ONLINE    oel5...rac1
ora....ac1.vip ora....t1.type ONLINE    ONLINE    oel5...rac1
ora....SM2.asm application    ONLINE    ONLINE    oel5...rac2
ora....C2.lsnr application    ONLINE    ONLINE    oel5...rac2
ora....ac2.gsd application    OFFLINE   OFFLINE
ora....ac2.ons application    ONLINE    ONLINE    oel5...rac2
ora....ac2.vip ora....t1.type ONLINE    ONLINE    oel5...rac2
ora.ons        ora.ons.type   ONLINE    ONLINE    oel5...rac1
ora.racdb.db   ora....se.type ONLINE    ONLINE    oel5...rac2
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    oel5...rac2

OCR和VOTEDISK都在ASM diskgroup上,asm instance不启动,CRS肯定是无法通讯的。

–EOF–

关于index的rebuild

关于索引的rebuild,之前存在误区。读了MOS上的文章,有一些新的收获。

1.rebuild index并不是一件有意义的事情,相反可能带来隐患,尤其在错误的时间。

2.合并(index coalesce)是更推荐的做法,详细可以参考MOS上面的文章:

索引重建的必要性与影响 [ID 1525787.1]

3. 通常是优先考虑index coalesce(索引合并),而不是重建索引。索引合并有如下优点:

- 不需要占用近磁盘存储空间 2 倍的空间
- 可以在线操作
- 无需重建索引结构,而是尽快地合并索引叶块,这样可避免系统开销过大,请见第 2 点中的解释。
注意:例如,如要将索引转移到其他表空间,则需要重建索引。

综上所述,强烈建议不要定期重建索引,而应使用合适的诊断工具

–EOF–

SQL Server 2000 EE upgrade to SQL Server 2008R2

在论坛里面看到一个请求解决SQL Server 2000 EE升级到SQL Server 2008 R2 EE的case,其中遇到一些问题,我自己也不太确定是否能从SQL Server 2000这个版本升级到2008 R2,于是做了个简单的测试,这里记录一下,也许别人能够用到。
文章不打算描述过多的细节,只是把大致的过程粗略描述一下:

微软支持从SQL Server 2000 EE升级到SQL Server 2008R2这个升级路径,你可以从如下的link上找到支持建议:
http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx

最好将你的SQL Server 2000 EE补丁打到sp4,link里面给了明确的要求,照着做即可。

关于操作系统,我这里准备了windows server 2003 EE x86 sp2,操作系统方面的要求,微软也在下面这个link中给出了明确的要求:
http://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx
「参考:SQL Server 2008 R2 Enterprise (32-bit)段落」

大致的升级路径其实不复杂:

(1)SQL Server 2000 EE安装
(2)sp4 patch apply
(3)安装升级顾问,做precheck
(4).NET Framework 3.5 SP1

All other editions of SQL Server 2008 R2 — .NET Framework 3.5 SP1
Installation of .NET Framework requires a restart of the operating system. If Windows Installer installation also requires a restart, Setup will wait until .NET Framework and Windows Installer components have installed before restarting.
Note:
Installing .NET Framework 2.0 SP2: .NET Framework 2.0 SP2 is not available as a separate download. You will need to install .NET Framework 3.5 SP1 which includes .NET Framework 2.0 SP2.

(5)run setup of SQL Server 2008 R2 to upgrade the instance

完成上面的4步骤,就基本ok了,中途没有遇到什么问题。倒是有个报错与开启”VIA”相关,禁止掉它就ok了。

很多人建议与其这么跨版本的升级,不如重装SQL Server 2008R2,然后拿原库的数据恢复,这样会减少很多不必要的问题。

备注:上述是在没有数据的情况下,如果有数据,可能会遇到新的问题。

–EOF–