惜分飞

ORACLE 11.2.0.3 生成awr html文件报SYS.DBMS_WORKLOAD_REPOSITORY异常

发表于 2012 年 10 月 22 日由惜分飞

在想分析数据库性能的关键时刻,突然发现awr不能正常的工作,那就和你上了战场突然发现枪没有子弹一样的郁闷,今天就遇到了11.2.0.3在win的环境中awr生成html不能正常工作.通过查询mos发现该问题出现在各种平台中(win,linux,aix等),提醒大家注意该问题.
数据库版本

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 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

awr报错(html)

SQL> @?/rdbms/admin/awrrpt.sql
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: 在 line 1

设置errorstack

SQL> alter session set events '6502 trace name errorstack level 12';

会话已更改。

分析错误

----- Error Stack Dump -----
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
----- Current SQL Statement for this session (sql_id=572fbaj0fdw2b) -----
select output from table(dbms_workload_repository.awr_report_html( :dbid,
                                                            :inst_num,
                                                            :bid, :eid,
                                                            :rpt_options ))


----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
94348684       919  package body SYS.DBMS_WORKLOAD_REPOSITORY
983BAD54         1  anonymous block
----- Call Stack Trace -----
_skdstdst()+121      CALLrel  _kgdsdst()           19D99520 2
_ksedst1()+93        CALLrel  _skdstdst()          19D99520 0 1 485816 4863B2
                                                   485816
_ksedst()+49         CALLrel  _ksedst1()           0 1
_dbkedDefDump()+368  CALLrel  _ksedst()            0
6                                                  
_ksedmp()+44         CALLrel  _dbkedDefDump()      C 0
_dbkdaKsdActDriver(  CALLreg  00000000             C
)+4209                                             
…………

通过查询mos发现Bug 13575143一致，可以确定是该bug,但是通过进一步测试证明不光是awrrpt会出现该错误,awr的相关报告中,只要是展示html结果的都有可能出现类此错误(比如awrrpti.sql/awrddrpt.sql/awrddrpi.sql等等).同时这里通过进一步分析发现其实该bug的起源是Bug 6458801(REPLACE on a CLOB can corrupt multibyte data ID 6458801.8),不过该bug说明已经在11.2.0.1中修复,其实通过这里的分析发现并没有真正的在11.2.0.3中修复该bug,针对该问题没有官方没有提供较好解决方法,只能是用过WORKAROUND来临时解决

They are able to generate the AWR report in the .txt format

发表在 ORA-xxxxx | 标签为 ORA-06502, ORA-06512 | 3 条评论

重建DBMS_STATS包

发表于 2012 年 10 月 22 日由惜分飞

数据库版本

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

执行DBMS_STATS报错

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');
 
begin sys.dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');; end;
 
ORA-04063: package body "SYS.DBMS_STATS" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_STATS" 的程序单元
ORA-06512: 在 line 2

重建DBMS_STATS包

SQL> drop package DBMS_STATS;

Package dropped.

SQL> @?/rdbms/admin/dbmsstat.sql

Package created.

No errors.

Synonym created.


Grant succeeded.

create role gather_system_statistics
            *
ERROR at line 1:
ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another user or
role name



Grant succeeded.


Grant succeeded.


Library created.

SQL> @?/rdbms/admin/prvtstas.plb

Package created.

No errors.
SQL> @?/rdbms/admin/prvtstai.plb

Package body created.

No errors.
SQL> @?/rdbms/admin/prvtstat.plb

Package body created.

No errors.

重新执行DBMS_STATS

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

补充说明
1.建议数据库在restricted模式下执行重建DBMS_STATS相关脚本
2.对于11g以前版本,具体参考1310365.1

SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb

发表在 Oracle | 一条评论

TTS实现跨版本迁移数据

发表于 2012 年 10 月 17 日由惜分飞

以前对Transportable Tablespaces(TTS)一直理解不深,今天无意中看到TTS可以实现数据库升级,今天测试了实现使用TTS 迁移9.2.0.4的一个表空间到11.2.0.3,平台均为Linux 32位
源端版本

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 tablespace tts_xff 
  2  datafile '/u01/oracle/oradata/xifenfei/tts_xifenfei01.dbf' size 10m autoextend on next 10m,
  3  '/u01/oracle/oradata/xifenfei/tts_xifenfei02.dbf' size 10m autoextend on next 10m
  4  ;

Tablespace created.

SQL> create user tts_xff identified by xifenfei;

User created.

SQL> grant dba to tts_xff;

Grant succeeded.

SQL> conn tts_xff/xifenfei
Connected.
SQL> create table t1 tablespace tts_xff
  2  as
  3  select * from dba_objects;

Table created.

SQL> create table t2 tablespace tts_xff
  2  as
  3  select * from dba_objects;

Table created.

SQL> create table t_xifenfei  tablespace tts_xff
  2  as
  3  select * from dba_objects;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_XIFENFEI                     TABLE

SQL> select count(*) from t1;

  COUNT(*)
----------
     30805

SQL> conn / as sysdba 
Connected.
SQL> alter tablespace tts_xff read only;

Tablespace altered.

导出并传输测试表空间

[oracle@xifenfei ~]$ exp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp transport_tablespace=y

Export: Release 9.2.0.4.0 - Production on Sun Oct 7 04:53:25 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS_XFF ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                             T1
. . exporting table                             T2
. . exporting table                     T_XIFENFEI
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@xifenfei ~]$ scp /tmp/tts_xff.dmp 192.168.1.10:/tmp/
oracle@192.168.1.10's password: 
tts_xff.dmp                                                       100%   16KB  16.0KB/s   00:00    
[oracle@xifenfei ~]$ scp /u01/oracle/oradata/xifenfei/tts_xifenfei* 192.168.1.10:/u01/oracle/oradata/ora11g/
oracle@192.168.1.10's password: 
tts_xifenfei01.dbf                                                100%   10MB   3.3MB/s   00:03    
tts_xifenfei02.dbf                                                100%   10MB   5.0MB/s   00:02

目标库版本

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

创建用户

SQL> create user tts_11g identified by xifenfei;

User created.

SQL> grant dba to tts_11g;

Grant succeeded.

导入表空间

[oracle@xifenfei ~]$ imp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp
> transport_tablespace=y datafiles=/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf, 
> /u01/oracle/oradata/ora11g/tts_xifenfei02.dbf fromuser=tts_xff touser=tts_11g

Import: Release 11.2.0.3.0 - Production on Sat Sep 29 04:18:04 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


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

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TTS_XFF's objects into TTS_11G
. . importing table                           "T1"
. . importing table                           "T2"
. . importing table                   "T_XIFENFEI"
Import terminated successfully without warnings.

测试数据

SQL> alter tablespace tts_xff read write;

Tablespace altered.

SQL> conn tts_11g/xifenfei
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_XIFENFEI                     TABLE

SQL> select count(*) from t1;

  COUNT(*)
----------
     30805

SQL> delete from t1;

30805 rows deleted.

SQL> commit;

Commit complete.

至此测试完成，证明使用tts可以实现跨版本迁移数据

补充说明
1.10g及其以上版本可以实现不同平台的tts迁移，可能需要使用rman convert转换
2.迁移前需要使用 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK检测依赖性

发表在 Oracle | 评论关闭

ORACLE 11.2.0.3 生成awr html文件报SYS.DBMS_WORKLOAD_REPOSITORY异常

重建DBMS_STATS包

TTS实现跨版本迁移数据

文章分类

最近发表

ORACLE 11.2.0.3 生成awr html文件报SYS.DBMS_WORKLOAD_REPOSITORY异常

重建DBMS_STATS包

TTS实现跨版本迁移数据

标签云

文章分类

最近发表