您现在的位置是:网站首页> 内容页

Oracle SPA取报告阶段xml解析失败解决方案

  • 杏彩平台手机网页版
  • 2019-07-17
  • 292人已阅读
简介SPA的整体测试过程可以参考:记录一则完整的SPA(10g->11g)测试过程故障描述:数据库字符集:US7ASCII,在SPA分析阶段正常,但在取报告阶段xml解析失败,具体现

SPA的整体测试过程可以参考:

记录一则完整的SPA(10g->11g)测试过程

故障描述:数据库字符集:US7ASCII,在SPA分析阶段正常,但在取报告阶段xml解析失败,具体现象如下:

SQL>ALTER SESSION SET EVENTS="31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400";SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITEDSPOOL error.htmlSELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK("SPA_TASK_201806","HTML","ERRORS","ALL",NULL,1000,"COMPARE_ET_201806")).GETCLOBVAL(0,0) FROM DUAL;spool offERROR:ORA-31011: XML parsing failedORA-19202: Error occurred in XML processingLPX-00216: invalid character 207 (0xCF)Error at line 26ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211ORA-06512: at "SYS.DBMS_SQLPA", line 515ORA-06512: at line 1

这个invalid character 207,之前是别的无效字符,用一些方法删除掉重新SPA解包分析,又会报207无效字符,看起来是大量不合规的sql_text,基本判定删不完,而且就算能删除干净也会导致SPA的分析结果集缺失严重,所以我们需要更好的解决方案。经过系列测试,和几位专家沟通,最终决定尝试独立恢复出这个库,测试修改字符集,参考命令如下:

SQL> shutdown immediate;startup mount;select logins, active_state from v$instance;alter system enable restricted session;alter system set job_queue_processes=0;alter database open;select userenv("language") from dual;--alter database character set zhs16gbk;--注意:Oracle转化字符集alter database character set internal_convert zhs16gbk;--注意:Oracle会自动转换含有CLOB的--alter database character set internal_use us7ascii;--注意:Oracle会强制转换,会造成部分数据乱码,慎用--alter database national character set zhs16gbk;--注意:修改国家字符集,我这里不需要修改。shutdown immediate;startupselect userenv("language") from dual;select logins, active_state from v$instance;alter system disable restricted session;alter system set job_queue_processes=1000;

再次尝试获取SPA报告:

ALTER SESSION SET EVENTS="31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400";SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITEDSPOOL error.htmlSELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK("SPA_TASK_201806","HTML","ERRORS","ALL",NULL,1000,"COMPARE_ET_201806")).GETCLOBVAL(0,0) FROM DUAL;spool off

可以成功执行取出报告。

另外注意,字符集不能反复修改,实际测试,如果此时改成us7ascii,再改回zhs16gbk,再尝试取SPA报告也不再成功。我这里测试会有如下的报错:

ERROR:ORA-51705: XML DOM error: 217 "LPX-00217: invalid character 0 (U+0000)"ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211ORA-06512: at "SYS.DBMS_SQLPA", line 515ORA-06512: at line 1

所以,这也是为什么要RMAN恢复一个独立的测试库进行取报告的原因。

文章评论

Top