How To Enable Archive Mode In RAC

How to enable the archive mode in three nodes RAC environment,this is what the page will talk about.

ref doc:How To Enable/Disable Archive Log Mode in Real Application Cluster Environment (Doc ID 235158.1)

1.the current information of RAC

[oracle@node3 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....a1.inst application    ONLINE    ONLINE    node1
ora....a2.inst application    ONLINE    ONLINE    node2
ora....a3.inst application    ONLINE    ONLINE    node3
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
ora....E3.lsnr application    ONLINE    ONLINE    node3
ora.node3.gsd  application    ONLINE    ONLINE    node3
ora.node3.ons  application    ONLINE    ONLINE    node3
ora.node3.vip  application    ONLINE    ONLINE    node3

2.prepare the arch directory for on each node.(3 directories for each node) Continue reading “How To Enable Archive Mode In RAC”

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–

windows server 2003 diskpart扩容

本测试的os基于windows server 2003 EE版本

1.磁盘的类型

windows server在磁盘管理方面,能够将磁盘分为两种大的类型:基本、动态。”基本”类型主要针对单个磁盘,而”动态”则更加灵活一些,具备一定的动态扩展能力,一般针对多个磁盘,多个磁盘可以组成”卷”,例如从磁盘1和磁盘2中各拿100M组成一个容量200M的卷。而当这个卷容量不够,快耗尽时(常见的E分区空间不足),可以再从磁盘1和磁盘2上取空间容量,添加到卷上(E分区),这些都是动态的。

而基本类型的磁盘,不具备这样的功能,如果E分区空间不足了,而临近的空间上还有剩余的裸空间没有分配,可以借助diskpart工具进行扩展,可以参考后续的附件例子。

2.分区的类型

windows server的分区样式主要分为两种:”MBR”模式、”GPT”模式。MBR模式在主分区上有限制,只能拥有4个主分区。而GPT模式不受这个的限制,能够有很多的主分区(至少大于4)。

但在通常实际情况下,磁盘都是由存储提供的,条带化、raid已经做过了,再用”动态”磁盘的形式已经累赘了,重复的”mirror”、”条带化”、”raid”显得多余了。唯一的理由我想是”扩展性”,这体现在当某个app使用的磁盘分区,例如:D没有空间时,该如何让ntfs来动态的扩容?当然,主流的存储都支持动态的扩展lun了,而问题是ntfs如何online识别呢?这对于aix、linux并不是问题,只要使用了lvm或者一些其他的FS,都支持在线扩展了。而留给NTFS的选择似乎只有diskpart了。

附件是一次diskpart进行扩容的简单例子:diskpart扩容案例

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)”