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 UNLIMITED
- SPOOL error.html
- SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','ERRORS','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
- spool off
- ERROR:
- ORA-31011: XML parsing failed
- ORA-19202: Error occurred in XML processing
- LPX-00216: invalid character 207 (0xCF)
- Error at line 26
- ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211
- ORA-06512: at "SYS.DBMS_SQLPA", line 515
- ORA-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;
- startup
- select 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 UNLIMITED
- SPOOL error.html
- SELECT 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 8211
- ORA-06512: at "SYS.DBMS_SQLPA", line 515
- ORA-06512: at line 1
所以, 这也是为什么要 RMAN 恢复一个独立的测试库进行取报告的原因.
来源: https://www.cnblogs.com/jyzhao/p/9232150.html