Manually generate AWR in HTML format when encountering Bug 13527323

The AWR report in Oracle is currently the best performance analysis and diagnosis tool officially provided. With the help of a continuous snapshot analysis, we can quickly obtain the overall performance analysis indicators and locate the problem occurrence points comprehensively.

In any Oracle version, we may encounter various types of bugs. After a Bug is detected, it matches with the officially published content to find a solution or circumvent it. This is a capability that database users should possess.

1. Environment Introduction and fault occurrence

In the 11.2.0.3 environment, the author generates an AWR report for inspection and wants to view the workload during peak business hours.

SQL> select * from v $ version;

 

BANNER

------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for IBM/aix risc System/6000: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

Use the sqlplus command line to generate a report and call awrrpt. SQL.

D: \> sqlplus/nolog

SQL * Plus: Release 11.2.0.1.0Production on Thursday February 27 09:07:02 2014

Copyright (c) 1982,201 0, Oracle. All rights reserved.

SQL> @? /Rdbms/admin/awrrpt. SQL

Current Instance

~~~~~~~~~~~~~~~~

 

DB Id DB Name Inst Num Instance

-------------------------------------------

1083126127 COGDB 1 cogdb

 

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Wocould you like an HTML report, or a plain text report?

Enter 'html' for an html report, or 'text' for plain text

AWR currently supports two formats: html and text. The text format is extended from statspack, the predecessor of AWR. The format is simple, the generated load is small, but the readability is not strong. Html format is our usual choice.

However, an error occurred in this report.

 

8 </a> </td>

<Td class = 'awrnc '> & #160;

</Td>

<Td class = 'awrnc '> SELECT dbin. instance_number, d... </td> </tr>

</Table> <p/>

ERROR:

ORA-06502: PL/SQL: Number or value error: string buffer is too small

ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 919

ORA-06512: In line 1

 

Report written to awrrpt_00009583_9584.html

Report Generation failed. Although a report file exists in the directory or can be opened, the generated file is incomplete.

2. check and confirm the problem

Generally, client scripts and server programs do not match each other, which is a very important reason for script execution. For example, we use the old 10g client script to call the new program of the 11g server, which may cause a fault. A typical process is to restore the data dictionary.
 

The AWR generation script awrrpt. SQL is on the client, but is only responsible for parameter data collection and input. The key program for generating an AWR report is the dbms_workload_repository package. awrrpt. SQL does not directly deal with the AWR data dictionary. Therefore, due to version differences, the possibility of this case is very low.

To prevent this, we generate reports on the server.

[Oracle @ MISDB: ~] $ Cd/tmp

[Oracle @ MISDB:/tmp] $ sqlplus/nolog

 

SQL * Plus: Release 11.2.0.3.0 Production on Thu Feb 27 09:13:23 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.

 

SQL> conn/as sysdba

Connected.

 

SQL> @? /Rdbms/admin/awrrpt. SQL

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

-------------------------------------------

1083126127 COGDB 1 cogdb

 

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Wocould you like an HTML report, or a plain text report?

Enter 'html' for an html report, or 'text' for plain text

Ults to 'html'

Enter value for report_type:

An error still occurs when an html report is generated.

<Td class = 'awrnc '> & #160;

</Td>

<Td class = 'awrnc '> SELECT dbin. instance_number, d... </td> </tr>

</Table> <p/>

ERROR:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS. DBMS_WORKLOAD_REPOSITORY", line 919

ORA-06512: at line 1

Report written to awrrpt_00009583_9584.html

This type of scenario is indeed abnormal, and we cannot really track line 919 due to code encryption. In this case, you may need to turn to the Official Experience collection MOS.

After query, there is an introduction to Bug13527323 in MOS Article No.: ORA-6502 generating html awr report using awrrpt. SQL in Multibyte characterset database (Document ID 13527323.8 ).
 
In the article, Oracle says sometimes using awrrpt. SQL scripts to generate reports throws an error ora-6502. The affected version is 11.2.0.3, and Oracle considers that errors may occur at the beginning of version 12.1. This description is the same as the current situation encountered by the author.
 

For the cause of failure, Oracle interpretation is: In case of multibyte characters in SQL text, sometime AWR report generation fails with error ORA-6502. also sending more than 4000 bytes of single varchar data to JDBC api's causes truncation of data.
 
Rediscovery Notes:

If html AWR report generation fails with ORA-6502 or generating

AWR report using JDBC api's causes truncation of data then this bug

As probably been rediscovered.

If multiple language representations exist in SQL text, an exception ora-6502 is thrown during data output.

This is basically the problem. How can this problem be solved? Oracle does not specify a patch set or the like for policies, but it is accidental.

3. Problem Solving

Although the problem is located, it still needs to be solved. If there is a problem with the html format, will the simpler text format not throw an exception?

SQL> @? /Rdbms/admin/awrrpt. SQL

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

-------------------------------------------

1083126127 COGDB 1 cogdb

 

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Wocould you like an HTML report, or a plain text report?

Enter 'html' for an html report, or 'text' for plain text

Ults to 'html'

Input report_type value: text

Type Specified: text

 

The final result is successfully generated:

 

Parameter Name Begin value (if different)

----------------------------------------------------------------------------

Control_files/home/oracle/controlfile/cogdb/co

/Oradb/app/oracle/oradata/control

---------------------------------------------

Dynamic Remastering Stats DB/Inst: COGDB/cogdb Snaps: 9583-9584

No data exists for this section of the report.

-----------------------------------------

 

End of Report

Report written to awrrpt_00009583_9584.txt

 

The validation report is also complete. In the SQL Text section, we did find the "multi-language" situation.

 

-> Captured PL/SQL account for 0.0% of Total DB Time (s): 3,165

Elapsed Time

Time (s) Executions per Exec (s) % Total % CPU % io SQL Id

--------------------------------------------------------------------------
 
298.0 1 297.99 9.4 50.6 26.6 4wfkj936k5qhq
 
Insert into f_cx_cmain (select T1. "POO" as ticket No., T1. "CLDE"

Class Code, T1. "RDE" as code, T1. "CDE" as company, T1

. "BUODE" as source code, T1. "SE" as start time, T1. "EE" as End Time, T1. "UNDDDATE" as passed, T2. "STATDATE"

It does match the Bug description. In addition to the text format, the author believes that the AWR generation module in OEM products can also avoid such problems.

4. Conclusion

Bugs are hard to avoid when using any software. When the operation result is different from our expectation and we judge from experience that it is not an operation error, we can consider the cause of the Bug.