OCR/Vote disk 维护操作

数据库版本

SQL>  select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

ocr测试(可以online处理)

rac2-> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     160396
         Used space (kbytes)      :       4376
         Available space (kbytes) :     156020
         ID                       : 1302494786
         Device/File Name         : /dev/raw/raw11
                                    Device/File integrity check succeeded
                                    Device/File not configured
         Cluster registry integrity check succeeded
rac2-> more /etc/oracle/ocr.loc
ocrconfig_loc=/dev/raw/raw11
local_only=false
--增加ocr镜像
[root@rac2 bin]# ./ocrconfig -replace ocrmirror /dev/raw/raw12
rac2-> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     160396
         Used space (kbytes)      :       4376
         Available space (kbytes) :     156020
         ID                       : 1302494786
         Device/File Name         : /dev/raw/raw11
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/raw/raw12
                                    Device/File integrity check succeeded
         Cluster registry integrity check succeeded
rac2-> more /etc/oracle/ocr.loc
#Device/file  getting replaced by device /dev/raw/raw12
ocrconfig_loc=/dev/raw/raw11
ocrmirrorconfig_loc=/dev/raw/raw12
local_only=false
--删除ocr
[root@rac2 bin]# ./ocrconfig -replace ocr
rac2-> more /etc/oracle/ocr.loc
#Device/file /dev/raw/raw11 being deleted
ocrconfig_loc=/dev/raw/raw12
local_only=false
rac2-> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     160396
         Used space (kbytes)      :       4376
         Available space (kbytes) :     156020
         ID                       : 1302494786
         Device/File Name         : /dev/raw/raw12
                                    Device/File integrity check succeeded
                                    Device/File not configured
         Cluster registry integrity check succeeded
--补充删除ocr镜像
[root@rac2 bin]# ./ocrconfig -replace ocrmirror

Vote disk测试(10g offline/11g online)

--关闭crs
[root@rac2 bin]# ./crsctl stop crs
[root@rac1 bin]# ./crsctl stop crs
--查询vote disk
rac2-> crsctl query css votedisk
 0.     0    /dev/raw/raw31
--增加vote disk
[root@rac2 bin]# ./crsctl add css votedisk /dev/raw/raw23 -force
Now formatting voting disk: /dev/raw/raw23
successful addition of votedisk /dev/raw/raw23.
[root@rac2 bin]# ./crsctl add css votedisk /dev/raw/raw33 -force
Now formatting voting disk: /dev/raw/raw33
successful addition of votedisk /dev/raw/raw33.
[root@rac2 bin]# ./crsctl add css votedisk /dev/raw/raw32 -force
Now formatting voting disk: /dev/raw/raw32
successful addition of votedisk /dev/raw/raw32.
rac2-> crsctl query css votedisk
 0.     0    /dev/raw/raw31
 1.     0    /dev/raw/raw23
 2.     0    /dev/raw/raw33
 3.     0    /dev/raw/raw32
located 4 votedisk(s).
--删除vote disk
[root@rac2 bin]# ./crsctl delete css votedisk /dev/raw/raw33 -force
successful deletion of votedisk /dev/raw/raw33.
--启动crs
[root@rac2 bin]# ./crsctl start crs
[root@rac1 bin]# ./crsctl start crs

补充官方操作说明[ID 428681.1]
http://www.xifenfei.com/wp-content/uploads/2012/04/OCR_Vote_disk_Maintenance_Operations.pdf

RAC 10g升级到10.2.0.5

1.Back Up database
一般情况下rman备份

2.备份ocr和vote disk

[root@rac2 bin]# ./ocrconfig -export /tmp/ocr_export.bak
[root@rac2 bin]# more /etc/oracle/ocr.loc
ocrconfig_loc=/dev/raw/raw11
local_only=FALSE
[root@rac2 bin]# dd if=/dev/raw/raw11 of=/tmp/ocr_dd.bak
[root@rac2 bin]# dd if=/dev/raw/raw31 of=/tmp/vote_dd.bak

3.Update Oracle Time Zone Definitions
Actions for the DSTv4 update in the 10.2.0.5 patchset [ID 1086400.1]

4.Stopping All Processes
滚动升级关闭一个节点所有进程,非滚动升级关闭所有进程

$ isqlplusctl stop
$ emctl stop dbconsole
$ srvctl stop service -d db_name [-s service_name_list [-i inst_name]]
$ srvctl stop instance -d db_name -i inst_name
$ srvctl stop asm -n node
$ srvctl stop listener -n node [-l listenername]
$ srvctl stop nodeapps -n node
# CRS_home/bin/crsctl stop crs(root执行,滚动升级不需要关闭)

5.Back Up the System
$ORACLE_BASE中文件,主要包括(db和crs安装文件/oraInventory文件)

6.升级crs软件
执行./runInstaller选择crs目录

执行下面命令
# CRS_home/bin/crsctl stop crs
# CRS_home/install/root102.sh

7.升级db软件
关闭crs和db所有进程(步骤同4)
执行./runInstaller选择db目录

执行下面命令
# ORACLE_HOME/root.sh

8.升级数据库
8.1)检查数据库升级需要满足条件,对存在不合适之处,进行修正
How to Download and Run Oracle’s Database Pre-Upgrade Utility [ID 884522.1]

SQL> STARTUP UPGRADE
SQL> SPOOL upgrade_info.log
SQL> @/rdbms/admin/utlu102i.sql
SQL> SPOOL OFF
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
--其他根据upgrade_info.log中提示修改
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE

8.2)启动监听
srvctl start listener -n node

8.3)升级数据库

SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
--检查patch.log,发现有错误查找原因,重新执行catupgrd.sql脚本
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;
--包括其他修改调整参数
SQL> SHUTDOWN IMMEDIATE
--使用rac管理相关命令,启动需要启动资源

9.修改相关目录权限
# ORACLE_HOME/install/changePerm.sh

具体操作步骤请阅读README.html

永久表空间出现临时段不能扩展原因探讨

数据库版本

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建5M测试表空间

SQL> CREATE TABLESPACE T_1652 DATAFILE '/tmp/t_1652_01.dbf' size 5M
  2  AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  3  SEGMENT SPACE MANAGEMENT AUTO blocksize 8192;
Tablespace created.

测试CTAS

SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
create table CHF.T_XIFENFEI TABLESPACE T_1652 as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3000;
Table created.

测试CREATE INDEX

SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
    5242880          0 /tmp/t_1652_01.dbf           5242880
SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=2000;
Table created.
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
    5242880    1048576 /tmp/t_1652_01.dbf           4194304
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 10M;
Database altered.
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
Index created.

测试MOVE

SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
Table created.
SQL> alter table chf.t_xifenfei move;
alter table chf.t_xifenfei move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
   10485760    4194304 /tmp/t_1652_01.dbf           6291456
SQL>  ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 15M;
Database altered.
SQL> alter table chf.t_xifenfei move;
Table altered.

这里可以发现CTAS,CREATE INDEX,MOVE操作都有个共同点:需要一次性创建一个较大SEGMENT,但是这个SEGMENT的创建过程是在数据库中逐渐实现(非初始化指定大小)。
也就是说,ORACLE对这些对象的处理方法是:对于这样的segment先当作临时段处理,当处理完成后,再把这些在永久表空间中的临时段转换为永久段;所以当这些永久表空间中的临时段在扩展的时候,遇到该永久表空间不足,而该段目前还是临时段(在永久表空间中的临时段),就出现了ORA-01652提示一个永久表空间unable to extend temp segment

表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程

online表空间出现ORA-00600[kcbz_check_objd_typ]

Fri Mar 30 14:09:24 2012
alter tablespace xff offline
Fri Mar 30 14:09:28 2012
Completed: alter tablespace xff offline
Fri Mar 30 17:49:59 2012
alter tablespace xff rename datafile '/oradataa/xifenfei.dbf' to '/oradatab/xifenfei_bak.dbf'
Fri Mar 30 17:50:03 2012
Completed: alter tablespace xff rename datafile '/oradataa/xifenfei.dbf' to '/oradatab/xifenfei_bak.dbf'
Fri Mar 30 17:50:03 2012
alter tablespace coweb_bak_new online
Fri Mar 30 17:53:08 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_21275.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

分析trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/ora10/product
System name:	Linux
Node name:	FH-DB-01
Release:	2.6.18-92.el5
Version:	#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:	x86_64
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 182
Unix process pid: 21275, image: oracle@FH-DB-01 (TNS V1-V3)
*** 2012-03-30 17:50:16.469
*** ACTION NAME:() 2012-03-30 17:50:15.939
*** MODULE NAME:(sqlplus@FH-DB-01 (TNS V1-V3)) 2012-03-30 17:50:15.939
*** SERVICE NAME:(SYS$USERS) 2012-03-30 17:50:15.939
*** SESSION ID:(921.23041) 2012-03-30 17:50:15.939
*** SESSION ID:(921.23041) 2012-03-30 17:50:15.939
OBJD MISMATCH typ=6, seg.obj=-2, diskobj=294051, dsflg=0, dsobj=294044, tid=294044, cls=1
Input data (nil), 0, 0
Formatted dump of block:
buffer tsn: 10 rdba: 0x0435c094 (1024/70631572)
scn: 0x0b2d.2b4f8874 seq: 0x01 flg: 0x04 tail: 0x88740601
frmt: 0x02 chkval: 0x5626 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000006DBB2000 to 0x000000006DBB4000
06DBB2000 0000A206 0435C094 2B4F8874 04010B2D  [......5.t.O+-...]
06DBB2010 00005626 00000002 00047CA3 2B4F8874  [&V.......|..t.O+]
06DBB2020 00000B2D 00320002 0435C091 00000000  [-.....2...5.....]
06DBB2030 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
06DBB2060 00000000 02800000 00000000 00240000  [..............$.]
06DBB2070 1F3C1F60 00000000 00000000 00000000  [`.<.............]
06DBB2080 00000000 00001F60 00001F5C 00000000  [....`...\.......]
06DBB2090 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
06DBB3FF0 00000000 00000000 00000000 88740601  [..............t.]
Block header dump:  0x0435c094
 Object id on Block? Y
 seg/obj: 0x47ca3  csc: 0xb2d.2b4f8874  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x435c091 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

这里可以得出几个结论:
1)版本平台信息
2)通过下面信息都是知道,data_object_id=294044的对象出现异常(seg.obj=-2)

OBJD MISMATCH typ=6, seg.obj=-2, diskobj=294051, dsflg=0, dsobj=294044, tid=294044, cls=1
Block header dump:  0x0435c094
 Object id on Block? Y
 seg/obj: 0x47ca3  csc: 0xb2d.2b4f8874  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x435c091 ver: 0x01 opc: 0
     inc: 0  exflg: 0

3)结合dba_objects视图查询出来是xifenfei_index 索引出现异常

问题原因猜测
因为没有找到权威解释,对于这里在online表空间的时候出现ORA-00600[kcbz_check_objd_typ]错误,个人的猜测可能是在online的时候,验证数据字典中关于该表空间中对象的相关记录和该表空间总的存储数据比较,然后发现不匹配,所以出现了该错误。

处理方法
虽然该表空间离线,但是我们可以使用drop操作直接删除数据字典中index的记录,然后再online表空间,这样可以绕过数据字典和表空间中的存储比较。当表空间online成功后,然后再创建index

ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]

发现错误ORA-00600 [ktspfupdst-1],trace关键内容如下

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 389
Unix process pid: 1896900, image: oracle@zwq_crm1 (TNS V1-V3)
----------------------------------------------
*** 2012-03-31 02:50:48.509
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktspfupdst-1], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
Current SQL statement for this session:
INSERT INTO XIFENFEI
SELECT * FROM T_XFF
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014
kgerinv+00e8         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000001 ? 000000000 ?
                                                   000000005 ? 000000001 ?
                                                   000000001 ?
ktspfupdst+0540      bl       kgeasnmierr          110006308 ? 1103994E8 ?
                                                   102A9239C ? 000000000 ?
                                                   000000005 ? 000000010 ?
                                                   000000020 ? 000000006 ?
ktspstchg+00e4       bl       ktspfupdst           000000060 ? 300000004 ?
                                                   FFFFFFFFFFF6E48 ?
                                                   50601CE000000ED ?
                                                   3B401B34C5D02F2A ?
                                                   B92000004000020 ?
kdoiur+062c          bl       ktspstchg            000000000 ? 700000C39D779E8 ?
                                                   000000000 ?
kcoubk+00e4          bl       _ptrgl
ktundo+0988          bl       kcoubk               1010CCD80 ? FFFFFFFFFFF76C0 ?
                                                   100ED51C0 ? FFFFFFFFFFF7150 ?
                                                   1101FAF78 ? 1102567C0 ?
                                                   700000C396A1300 ? 000000002 ?
ktubko+03bc          bl       ktundo               1840DFB30 ?
                                                   3B401B3400000002 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFF85D8 ?
                                                   700000C80A1E880 ? 2FFFF8540 ?
                                                   FFFFFFFFFFF8780 ?
ktuabt+0638          bl       ktubko               DF000000DF ?
                                                   FFFFFFFFFFF8690 ? 000000000 ?
                                                   FFFFFFFFFFF85D8 ? 102973880 ?
                                                   700000C844FA418 ?
ktcrab+02b4          bl       ktuabt               700000C80A1E840 ? 200017CD8 ?
ktcrsp+026c          bl       ktcrab               100F698E4 ? 000000001 ?
ksures+0074          bl       ktcrsp               700000C844FA448 ?
opiexe+3380          bl       01FD4138
opiall0+102c         bl       opiexe               400000000 ? 110002A48 ?
                                                   FFFFFFFFFFFA0A0 ?
kpoal8+0a78          bl       opiall0              5EFFFFBED4 ? 22103A43F8 ?
                                                   FFFFFFFFFFFA5B8 ? 000000000 ?
                                                   FFFFFFFFFFFA508 ? 1103A4B00 ?
                                                   6FF00000738 ?
                                                   24000000007FFF ?
opiodr+08cc          bl       _ptrgl
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF7B0 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF7B0 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

这里可以得到信息如下:
1)系统平台aix 5.3,数据库版本9.2.0.8 rac
2)这个错误可能和一个insert select操作,然后取消有关系

表空间管理方式

SQL> SELECT tablespace_name, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces
  2  where tablespace_name=
  3  (select tablespace_name from dba_tables where table_name='XIFENFEI');
TABLESPACE_NAME                SEGMEN
------------------------------ ------
CUSTSERV                       AUTO

查看MOS发现和[ID 388599.1]相符
错误原因:

1. An insert or update on a table causes the addition of a new extent
   and the operation is cancelled.
2. The segment uses Automatic Segment Space Management (ASSM).
3. The call stack in the associated trace file resembles:
    ktspfupdst ktspstchg kdoiur kcoubk ktundo ktubko ktuabt ktcrab ktcrsp

解决方法:
官方给出方案就是升级到新版本,不过可以采取一个比较折中的处理方案,先想办法导出或者备份该对象数据,然后trunate或者重建表和相关index操作,解决该问题

DB2入门操作之二

查看db2版本
db2 => select * from sysibm.sysversions
列出所有实例
[db2inst1@xifenfei ~]$ db2ilist
db2inst1
列出当前实例
[db2inst1@xifenfei ~]$ db2 get instance
 The current database manager instance is:  db2inst1
察看示例配置文件
[db2inst1@xifenfei ~]$ db2 get dbm cfg|more
察看数据库配置参数信息
[db2inst1@xifenfei ~]$ db2 get db cfg for TOOLSDB|more
列出所有表空间的详细信息
[db2inst1@xifenfei ~]$ db2 list tablespaces show detail|more
连接数据库
[db2inst1@xifenfei ~]$ db2 connect to TOOLSDB
   Database Connection Information
 Database server        = DB2/LINUX 9.7.4
 SQL authorization ID   = DB2INST1
 Local database alias   = TOOLSDB
sql操作数据库
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"
查看端口号
[db2inst1@xifenfei ~]$ db2 get dbm cfg|grep SVCENAME
查看表结构
[db2inst1@xifenfei ~]$ db2 describe table t_xifenfei
查看某个表索引
[db2inst1@xifenfei ~]$ db2 describe indexes for table t_xff
显示当前活动数据库
[db2inst1@xifenfei ~]$ db2 list active databases
列出所有的系统表
[db2inst1@xifenfei ~]$ db2 list tables for system
列出表空间
[db2inst1@xifenfei ~]$ db2 list tablespaces
显示用户数据库的存取权限
[db2inst1@xifenfei ~]$ db2 GET AUTHORIZATIONS
检查 DB2 数据库管理程序配置
[db2inst1@xifenfei ~]$ db2 get dbm cfg

通过hash_value获取sql语句执行计划

当我们没有权限访问业务表,但是需要查看shared pool中部分sql语句的执行计划,原则上来说,查询v$sql_plan视图结合hash_value可以实现,但是因为这个是表格形式,看起来不太美观,和我们长看的执行计划有一定的出入,这里提供两个脚本,实现查看该种情况下的执行计划。
oracle 9i

[oracle@xifenfei ~]$ more get_plan.sql
set pagesize 0
set linesize 150
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set linesize 80
set verify on feedback on pagesize 1000

oracle 10g/11g

[oracle@xifenfei ~]$ more get_plan.sql
set pagesize 0
set linesize 150
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates,
       plan_id,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates,
       :hash_value,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set linesize 80
set verify on feedback on pagesize 1000

使用方法

SQL> SELECT hash_value FROM V$SQL WHERE SQL_TEXT
  2  LIKE 'SELECT * FROM SYS.SMON_SCN_TIME';
HASH_VALUE
----------
3019898357
SQL> @get_plan.sql
Enter value for hash_value: 3019898357
SELECT * FROM SYS.SMON_SCN_TIME
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| SMON_SCN_TIME |     1 |  1163 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

补充说明
其实9i和10g/11g中得出执行计划的出入就是在plan_table表上
在9i中:plan_table表需要通过脚本创建并且授权

SQL> connect / as sysdba;
SQL> @?/rdbms/admin/utlxplan.sql;
SQL> create public synonym plan_table for plan_table; --建立同义词
SQL> grant all on plan_table to public;--授权所有用户

在10g/11g中:plan_table表系统自带,不需要创建。因为plan_table表中含有plan_id列,而得出执行计划时该列不能为空,所以上面脚本中对于10/11g数据库必须要填充plan_id值

通过修改基表(link$)让非public dblink变为public

有些朋友创建了一个非public的dblink,现在该数据库的其他用户需要去使用该dblink,在正常情况下无访问权限,需要重新建一个dblink,或者将原dblink修改为public。但是由于忘记了原dblink的目标段的密码,使得创建或者修改dblink的步骤无法进行下去。这里通过修改基表(link$),解决该问题。

创建dblink

SQL> show user;
USER is "SYS"
SQL> create database link "xff_dblink"
  2  connect to TEST
  3  identified by "test"
  4  using '11.1.1.1:1521/mcrm';
Database link created.
SQL> select * from dba_db_links where db_link like 'XFF_DBLINK%';
OWNER DB_LINK                                     USERN HOST               CREATED
----- ------------------------------------------- ----- ------------------ --------
SYS   XFF_DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM  TEST  11.1.1.1:1521/mcrm 29-MAR-12
SQL> select sysdate from dual@xff_dblink;
SYSDATE
---------
29-MAR-12
SQL> CONN TEST/TEST
Connected.
SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;
SELECT SYSDATE FROM DUAL@XFF_DBLINK
                         *
ERROR at line 1:
ORA-02019: connection description for remote database not found
--该dblink不是public的,所以test用户无权访问

dblink变为public类型

SQL> CONN / AS SYSDBA
Connected.
SQL> set long 1000
SQL> select  text from dba_views where view_name='DBA_DB_LINKS';
TEXT
-------------------------------------------------------------------
select u.name, l.name, l.userid, l.host, l.ctime
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
--查询出dblink相关的基表有link$和user$
SQL> desc sys.link$
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER#                        NOT NULL NUMBER
 NAME                          NOT NULL VARCHAR2(128)
 CTIME                         NOT NULL DATE
 HOST                                   VARCHAR2(2000)
 USERID                                 VARCHAR2(30)
 PASSWORD                               VARCHAR2(30)
 FLAG                                   NUMBER
 AUTHUSR                                VARCHAR2(30)
 AUTHPWD                                VARCHAR2(30)
 PASSWORDX                              RAW(128)
 AUTHPWDX                               RAW(128)
SQL> select owner# from sys.link$ where name like 'XFF_DBLINK%';
    OWNER#
----------
         0
--XFF_DBLINK对应的用户标识记录在link$.owner#中
SQL> SELECT USER#,NAME FROM USER$ WHERE name in ('SYS','PUBLIC');
     USER# NAME
---------- ------------------------------
         1 PUBLIC
         0 SYS
--现link$.owner#值为0,表示该dblink所属用户为SYS,现在让该dblink变为public
--现需要让该dblink变为public,需要做的是修改link$.owner#的值为1
SQL> UPDATE LINK$ SET OWNER#=1 WHERE name like 'XFF_DBLINK%';
1 row updated.
SQL> COMMIT;
Commit complete.
--需要刷新shared_pool
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
--查看dblink所属者,已经修改为public
SQL> select owner from dba_db_links where db_link like 'XFF_DBLINK%';
OWNER
----------
PUBLIC
--测试dblink是否成功
SQL> CONN TEST/TEST
Connected.
SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;
SYSDATE
---------
29-MAR-12

ORA-27103 when Memory target parameter is set to more than 3 GB(11.1.0.7)

朋友在数据库软件从11.1.0.6升级到11.1.0.7后,发现数据库无法打开,不能继续下一步升级
数据库启动

SQL> startup upgrade
ORA-03113: end-of-file on communication channel

alert日志

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilecenterdb.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  memory_target            = 12864M
  control_files            = "/u01/app/oracle/oradata/centerdb/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=centerdbXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/centerdb/adump"
  audit_trail              = "DB"
  db_name                  = "centerdb"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
Thu Mar 29 15:47:06 2012
PMON started with pid=2, OS id=16324
Thu Mar 29 15:47:06 2012
VKTM started with pid=3, OS id=16326 at elevated priority
VKTM running at (20)ms precision
Thu Mar 29 15:47:06 2012
DIAG started with pid=4, OS id=16330
Thu Mar 29 15:47:06 2012
DBRM started with pid=5, OS id=16332
Thu Mar 29 15:47:06 2012
PSP0 started with pid=6, OS id=16334
Thu Mar 29 15:47:06 2012
DIA0 started with pid=7, OS id=16336
Thu Mar 29 15:47:06 2012
MMAN started with pid=8, OS id=16338
Thu Mar 29 15:47:06 2012
DBW0 started with pid=9, OS id=16340
Thu Mar 29 15:47:06 2012
DBW1 started with pid=10, OS id=16342
Thu Mar 29 15:47:06 2012
DBW2 started with pid=11, OS id=16344
Thu Mar 29 15:47:06 2012
DBW3 started with pid=12, OS id=16346
Thu Mar 29 15:47:06 2012
DBW4 started with pid=13, OS id=16348
Thu Mar 29 15:47:06 2012
DBW5 started with pid=14, OS id=16350
Thu Mar 29 15:47:06 2012
LGWR started with pid=15, OS id=16352
Thu Mar 29 15:47:06 2012
CKPT started with pid=16, OS id=16354
Thu Mar 29 15:47:06 2012
SMON started with pid=17, OS id=16356
Thu Mar 29 15:47:06 2012
RECO started with pid=18, OS id=16358
Thu Mar 29 15:47:06 2012
MMON started with pid=19, OS id=16360
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Mar 29 15:47:06 2012
MMNL started with pid=20, OS id=16362
starting up 1 shared server(s) ...
Errors in file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc:
ORA-27103: internal error
Additional information: -1
Additional information: 1
MMAN (ospid: 16338): terminating the instance due to error 27103
Instance terminated by MMAN, pid = 16338

这里可以发现memory_target在12g以上

trace文件内容

[oracle@fcdb trace]$ more /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Trace file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      fcdb
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: centerdb
Redo thread mounted by this instance: 0 <none>
Oracle process number: 8
Unix process pid: 16338, image: oracle@fcdb (MMAN)
*** 2012-03-29 15:47:06.865
*** SESSION ID:(1648.1) 2012-03-29 15:47:06.865
*** CLIENT ID:() 2012-03-29 15:47:06.865
*** SERVICE NAME:() 2012-03-29 15:47:06.865
*** MODULE NAME:() 2012-03-29 15:47:06.865
*** ACTION NAME:() 2012-03-29 15:47:06.865
error 27103 detected in background process
ORA-27103: internal error
Additional information: -1
Additional information: 1
*** 2012-03-29 15:47:06.865
MMAN (ospid: 16338): terminating the instance due to error 27103

结合alert和trace文件查询MOS,发现ORA-27103 when Memory target parameter is set to more than 3 GB [ID 743012.1]描述相符,是由于Bug:7272646引起.
鉴于朋友的数据库还升级过程中,所以给出的处理建议是先把memory_target改为2.8G,执行完升级操作,然后打上Patch:7272646
同时官方还给出了另一种解决方案:设置SHMMAX小于4G,个人不推荐;如果系统内存比较大,会出现多个内存段,影响系统性能

模拟ORA-04043并解决

创建两张模拟表

SQL> select * from v$version;
BANNER
------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> create table sys_xifenfei as
  2  select * from dba_tables;
Table created.
SQL> create table chf.chf_xifenfei as
  2  select * from dba_tables;
Table created.

启动数据库到mount状态查询表

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist
SQL> desc sys_xifenfei
ERROR:
ORA-04043: object sys_xifenfei does not exist
SQL> desc chf.chf_xifenfei
ERROR:
ORA-04043: object chf.chf_xifenfei does not exist

打开数据库查询

SQL> alter database open;
Database altered.
SQL> select count(*) from sys_xifenfei;
select count(*) from sys_xifenfei
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from chf.chf_xifenfei;
  COUNT(*)
----------
       868
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

解决问题

SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from dba_tables;
  COUNT(*)
----------
       869
SQL> select count(*) from sys_xifenfei;
  COUNT(*)
----------
       867

MOS解释
ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]

Available workarounds are:
1) Don't describe the dba_* views at mount stage.
OR
2) If you issue DESC of any DBA_*views at mount stage,
    then shutdown and restart the DB instance.
OR
3) Flush the shared pool.
SQL> Alter system flush shared_pool;
and then reissue the failing command.

在10g中open库后提示也为类此ORA-04043: object dba_tables does not exist