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–

ORA-29538:Java not installed

the developer meets the error message as trying to create a java object using sqlplus:

*****************************************
ORA-29538: Java not installed
*****************************************

//I double that the jvm components is not installed in oracle,check it out..

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 160
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select host_name,instance_name from v$instance;

HOST_NAME                                                  INSTANCE_NAME
---------------------------------------------------            ----------------
RLJXDBSCBG                                                      orcl

SQL> select distinct owner,name from dba_source where lower(NAME)='dbms_java';

no rows selected

//check the installed components

SQL> select comp_name, version, status from dba_registry;
 COMP_NAME                          VERSION                       STATUS
 ------------------------- ------------------------------ ----------------------
 Oracle Enterprise Manager                  11.2.0.1.0                   VALID
 Oracle Workspace Manager                   11.2.0.1.0                   VALID
 Oracle Database Catalog Views              11.2.0.1.0                  VALID
 Oracle Database Packages and Types          11.2.0.1.0                  VALID

//find another db based on 10205 and check the name ‘dbms_java’ in dba_source view

SQL> select distinct owner,name from dba_source where lower(NAME)=’dbms_java’;

OWNER NAME
————————–      ——————————
SYS                                    DBMS_JAVA
// the dbms_java has been installed

//check the installed components

SQL> select comp_name,version,status from dba_registry;

COMP_NAME                              VERSION                    STATUS
------------------------------------ ---------------------    ----------------
Oracle Database Catalog Views         10.2.0.5.0                  VALID
Oracle Database Packages and Types    10.2.0.5.0                  VALID
Oracle Workspace Manager              10.2.0.5.0                  VALID
JServer JAVA Virtual Machine          10.2.0.5.0                  VALID
Oracle XDK                            10.2.0.5.0                  VALID
Oracle Database Java Packages         10.2.0.5.0                  VALID
Oracle Expression Filter              10.2.0.5.0                  VALID
Oracle Data Mining                    10.2.0.5.0                  VALID
Oracle Text                           10.2.0.5.0                  VALID
Oracle XML Database                   10.2.0.5.0                  VALID
Oracle Rules Manager                  10.2.0.5.0                  VALID

COMP_NAME                                  VERSION            STATUS
-------------------------              ----------------     ----------------------
Oracle interMedia                       10.2.0.5.0               VALID
OLAP Analytic Workspace                 10.2.0.5.0               VALID
Oracle OLAP API                         10.2.0.5.0               VALID
OLAP Catalog                            10.2.0.5.0               VALID
Spatial                                 10.2.0.5.0               VALID
Oracle Enterprise Manager               10.2.0.5.0               VALID

Continue reading “ORA-29538:Java not installed”

change the path of datafile,redolog,controlfile

the customer need to relocate the path of $ORADATA to diskarray partition which means that you need to change the path of controlfile,redolog,datafiles.

there is the example to solve this case:

1.generate a  new pfile using the spfile and change the path of controlfiles

remember to copy the controlfile to the new location

2.startup mount pfile=’/new_path/new_pfile.ora’

3.path mapping

/home/oracle/oradata/trade/system01.dbf ----> /oradata/trade/system01.dbf

/home/oracle/oradata/trade/sysaux01.dbf----->/oradata/trade/sysaux01.dbf

/home/oracle/oradata/trade/users01.dbf------> /oradata/trade/users01.dbf

/home/oracle/oradata/trade/temp01.dbf----->/oradata/trade/temp01.dbf

/home/oracle/oradata/trade/TRADE.dbf----->/oradata/trade/TRADE.dbf

/home/oracle/oradata/trade/undotbs01.dbf---->/oradata/trade/undotbs01.dbf

copy all the datafile to new path

Continue reading “change the path of datafile,redolog,controlfile”

lots of “[kdl_trim]: newlen: 0” message in trace file

one of the customer reports that there are lots of trace file which contain the message as below:

*** ACTION NAME:() 2013-05-09 01:29:26.334
*** MODULE NAME:(osh@dsappaixkf (TNS V1-V3)) 2013-05-09 01:29:26.334
*** SERVICE NAME:(clirs) 2013-05-09 01:29:26.334
*** SESSION ID:(1011.22135) 2013-05-09 01:29:26.334
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0
[kdl_trim]: newlen: 0

Continue reading “lots of “[kdl_trim]: newlen: 0” message in trace file”

restore the control file from different machine

This note is maily tell you how to restore the controlfile from different machine..


//construct a failure case of losing controlfile

[oracle@hundsun trade]$ ls -lrt
total 3118452
-rw-r–r– 1 oracle dba        930 Apr 13 06:46 init_trade.ora
-rw-r—– 1 oracle dba  524296192 Apr 16 22:00 temp01.dbf
-rw-r—– 1 oracle dba    5251072 May  7 21:07 users01.dbf
-rw-r—– 1 oracle dba 2147491840 May  7 21:07 TRADE.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo03.log
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo02.log
-rw-r—– 1 oracle dba  335552512 May  7 21:18 system01.dbf
-rw-r—– 1 oracle dba  241180672 May  7 21:20 sysaux01.dbf
-rw-r—– 1 oracle dba  277880832 May  7 21:20 undotbs01.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:20 redo01.log
-rw-r—– 1 oracle dba    7061504 May  7 21:20 control03.ctl
-rw-r—– 1 oracle dba    7061504 May  7 21:20 control02.ctl
-rw-r—– 1 oracle dba    7061504 May  7 21:20 control01.ctl
[oracle@hundsun trade]$ rm -rf control01.ctl
[oracle@hundsun trade]$ rm -rf control02.ctl
[oracle@hundsun trade]$ rm -rf control03.ctl


[oracle@hundsun trade]$ ls -lrt
total 3097728
-rw-r–r– 1 oracle dba        930 Apr 13 06:46 init_trade.ora
-rw-r—– 1 oracle dba  524296192 Apr 16 22:00 temp01.dbf
-rw-r—– 1 oracle dba    5251072 May  7 21:07 users01.dbf
-rw-r—– 1 oracle dba 2147491840 May  7 21:07 TRADE.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo03.log
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo02.log
-rw-r—– 1 oracle dba  335552512 May  7 21:18 system01.dbf
-rw-r—– 1 oracle dba  241180672 May  7 21:20 sysaux01.dbf
-rw-r—– 1 oracle dba  277880832 May  7 21:20 undotbs01.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:20 redo01.log
 

 

Continue reading “restore the control file from different machine”

failover_mode中的backup选项测试

在RAC client端的tnsnames.ora配置中,常常遇到failover的配置,参数failover_mode,其中很多的参数,例如:backup,type,method,retries,delay。这四个参数都可以被叫做TAF的failover policy,后面三个是经常见到的,而backup比较少见,这篇文章主要介绍一下backup这个选项。下面是online doc上关于backup选项的说明解释。

—————————————————————————————————-

BACKUP 

Specify a different net service name for backup connections.

A backup should be specified when using preconnect to pre-establish connections. 

—————————————————————————————————-

也很容易理解,backup一般和preconnect一起使用,下面介绍一个例子,看看具体的切换情况。

client端的tnsnames.ora配置片段如下:

———————————————————————-

RAC1 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = rac.world)

      (INSTANCE_NAME = rac1)

      (FAILOVER_MODE =

        (BACKUP = rac2)

        (TYPE = SELECT)

        (METHOD = PRECONNECT)

      )

    )

  )

 

RAC2 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = rac.world)

      (INSTANCE_NAME = rac2)

      (FAILOVER_MODE =

        (BACKUP = rac1)

        (TYPE = SELECT)

        (METHOD = PRECONNECT)

      )

    )

  )

———————————————————————–

测试场景:

client端通过RAC1这个片段连接到service_name=rac.world的instance rac1上,也就是rac1节点上。

 

 

Continue reading “failover_mode中的backup选项测试”

oracle service with “PREFERRED” instance test

客户需要将一个原本健康的oracle 10g rac更改成为单节点运行,理由是app并未在rac的环境设计、测试过,各个节点的global cache有点多,存在性能的问题。需求就是要求RAC前端的所有session连接,都只连接到一个指定的节点上,也就是将"load balance"关闭掉。如果通过oracle service的方式也可以达到这样的效果,无需修改tnsnames中的loadbalance项和rac端的remote_listener参数,下面是一个实验的环境,仔细观察。

1.create the service named xjapan

//add a service named xjapan,rac1 as pre_list member and rac2 as avail_list member, the TAF policy is BASIC

srvctl add service -d rac -s xjapan -r "rac1" -a "rac2" -P BASIC

[oracle@node1 ~]$ srvctl add service -d rac -s xjapan -r "rac1" -a "rac2" -P BASIC

[oracle@node1 ~]$ crs_stat -t

Name           Type           Target    State     Host

————————————————————

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.rac.db     application    ONLINE    ONLINE    node2

ora….c1.inst application    ONLINE    ONLINE    node1

ora….c2.inst application    ONLINE    ONLINE    node2

ora….ice2.cs application    ONLINE    ONLINE    node1

ora….ac1.srv application    ONLINE    ONLINE    node2

ora….apan.cs application    OFFLINE   OFFLINE

ora….ac1.srv application    OFFLINE   OFFLINE

 

 

Continue reading “oracle service with “PREFERRED” instance test”

orakill和alter system kill session

 

在客户那遇到一例问题,一条开发人员的update没有commit,造成了资源的持有不放,阻塞了其他的session,而由于开发人员与db server之间有防火墙,相应的session已经被firewall重定向了,连commit或rollback的机会都没有了,db中跑着这个”僵尸”进程,通常情况下,这类问题就是定位相应的sql,然后kill掉,而windows平台上,由于所有与oracle相关的进程都仅仅体现在oracle.exe这一个进程中,想获得更详细的thread信息,一般需要借助微软提供的工具,例如:procexp64.exe。

另外,如果仅仅是alter system kill session来处理的话,相应的会话仅仅是被标识为”killed”状态,资源并不会立刻的释放。它需要等待pmon进程来做一些清理工作,没准还需要SQL>oradebug wakeup <pmon_pid>,来手工唤醒pmon来做清理工作,但实际的效果一般,其中pmon_pid是v$process.pid,所以在实际的问题处理环境中,还是建议直接orakill吧,见效快。
——————————————找到想要orakill的spid信息————————————————–
SQL> select s.sid,s.status,s.username,s.machine,p.spid,p.username from v$session s,v$process p where s.paddr=p.addr;

SID STATUS USERNAME MACHINE SPID USERNAME
———- ——– —————————— ———————————– ———— —————
170     ACTIVE     JACK     756        SYSTEM
169     ACTIVE     JACK      3148      SYSTEM
168     ACTIVE      JACK      3304     SYSTEM
167    ACTIVE      JACK       2672     SYSTEM
166    ACTIVE      JACK       3452     SYSTEM
165    ACTIVE      JACK       1844     SYSTEM
164     ACTIVE     JACK       3868     SYSTEM
163     ACTIVE     JACK       1824     SYSTEM
162     ACTIVE     JACK       2880    SYSTEM
161      ACTIVE    JACK       1224     SYSTEM
160     ACTIVE    JACK       1676     SYSTEM

SID      STATUS      USERNAME      MACHINE           SPID         USERNAME
———- ——– —————————— ———————————– ———— —————
113      ACTIVE      SYS      WORKGROUPJACK      2652      SYSTEM
154      ACTIVE     JACK                                           3316       SYSTEM
152      ACTIVE      JACK                                          1460       SYSTEM
128    INACTIVE  JIANKONG     NetGain                  2600        SYSTEM
149     INACTIVE JIANKONG     NetGain                 2636        SYSTEM
131      INACTIVE JIANKONG      NetGain                1580        SYSTEM
125     INACTIVE JIANKONG     NetGain                  1544        SYSTEM
135      INACTIVE JIANKONG      NetGain                3856        SYSTEM
130     INACTIVE   SCOTT         WORKGROUPJACK    2720    SYSTEM
143       INACTIVE SCOTT             HQ200708TMBG      716   SYSTEM
129        ACTIVE      JACK                                                        3228     SYSTEM

22 rows selected.
//定位了sid=143,spid=716的session,就是想要orakill的session

//用主机上的orakill命令,开始orakill操作
SQL> host
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.

C:Documents and SettingsAdministrator>orakill crdprodb 716

Kill of thread id 716 in instance crdprodb successfully signalled.

C:Documents and SettingsAdministrator>

//orakill成功了,查看被kill窗口的状态。

——————orakill v$process.spid之后,被kill的会话session窗口的现象。—————————–

C:Userstumin>sqlplus scott/tiger@192.168.201.123:1521/crdprodb

SQL*Plus: Release 10.2.0.1.0 – Production on 星期四 1月 17 16:33:33 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from user_tables;
select * from user_tables
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束

———————————————————————————————————–

可见,orakill的效果比较迅速、直接,建议在实际的问题处理中使用orakill。