oracle不能访问管理页面,Oracle Grid Control CONSOLE无法打开9i数据库的管理维护页面...

今天在Solaris平台的测试环境上安装了Oracle Grid control 10.2.0.1，安装及配置完成后，发现在登录9i数据库的tablespace维护页面时，页面处于长时间的等待状况。最终返回错误信息。该页面在打开其他较小的数据库的页面时，均能正常访问。

解决：

在9i数据库的后台，查找到以下的SQL脚本：

SELECT   d.tablespace_name, NVL (a.BYTES / 1024 / 1024, 0),

DECODE (d.CONTENTS,

'UNDO', NVL (u.BYTES, 0) / 1024 / 1024,

NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024

),

DECODE (d.CONTENTS,

'UNDO', NVL (u.BYTES / a.BYTES * 100, 0),

NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0)

),

DECODE (d.CONTENTS,

'UNDO', NVL (a.BYTES - NVL (u.BYTES, 0), 0) / 1024 / 1024,

NVL (f.BYTES, 0) / 1024 / 1024

),

d.status, a.COUNT, d.CONTENTS, d.extent_management,

d.segment_space_management

FROM SYS.dba_tablespaces d,

(SELECT   tablespace_name, SUM (BYTES) BYTES, COUNT (file_id) COUNT

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT   tablespace_name, SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name) f,

(SELECT   tablespace_name, SUM (BYTES) BYTES

FROM dba_undo_extents

WHERE status IN ('ACTIVE', 'UNEXPIRED')

GROUP BY tablespace_name) u

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND d.tablespace_name = u.tablespace_name(+)

AND NOT (d.extent_management = 'LOCAL' AND d.CONTENTS = 'TEMPORARY')

AND d.tablespace_name LIKE :b1

UNION ALL

SELECT   d.tablespace_name, NVL (a.BYTES / 1024 / 1024, 0),

NVL (t.BYTES, 0) / 1024 / 1024, NVL (t.BYTES / a.BYTES * 100, 0),

(NVL (a.BYTES, 0) / 1024 / 1024 - NVL (t.BYTES, 0) / 1024 / 1024),

d.status, a.COUNT, d.CONTENTS, d.extent_management,

d.segment_space_management

FROM SYS.dba_tablespaces d,

(SELECT   tablespace_name, SUM (BYTES) BYTES, COUNT (file_id) COUNT

FROM dba_temp_files

GROUP BY tablespace_name) a,

(SELECT   ss.tablespace_name,

SUM ((ss.used_blocks * ts.BLOCKSIZE)) BYTES

FROM gv$sort_segment ss, SYS.ts$ ts

WHERE ss.tablespace_name = ts.NAME

GROUP BY ss.tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management = 'LOCAL'

AND d.CONTENTS = 'TEMPORARY'

AND d.tablespace_name LIKE :b1

ORDER BY 1;

进一步分析后发现以下语句的SQL解析有问题：

SELECT   tablespace_name, SUM (BYTES) BYTES

FROM dba_undo_extents

WHERE status IN ('ACTIVE', 'UNEXPIRED')

GROUP BY tablespace_name

在metalink上查找到解决方法，找到以下两个BUG：

Bug 5029820: POOR PERFORMANCE WHEN ACCESSING TABLESPACE PAGE IN GRID CONTROL FOR APPS DB

Bug 5745040: QUERY AGANST DBA_UNDO_EXTENTS IS VERY SLOWBug 5745040: QUERY AGANST DBA_UNDO_EXTENTS IS VERY SLOW

Oracle建议安装补丁5562287，但是该补丁没有for10.2.0.1的。没有办法，下载p3731593_10202_SOLARIS64.zip先升级GC到10.2.0.2,然后安装5562287_10202_GENERIC.zip补丁。

注意，安装前。需要执行export ORACLE_HOME=$OMS_HOME

$ORACLE_HOME/OPatch/opatch apply

当补丁安装成功后，再次登录管理表空间的维护页面，系统登录正常。

至此，问题终于解决。