关于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–

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”