什么鬼？主备同步正常，备库查询表空间使用结果却是空的？

"叮铃铃，叮铃铃”，一阵电话铃声响起，原来是客户打电话过来了，跟我说他在备库查看表空间使用情况，发现返回的结果是空的，但是在主库查询是正常的，让帮忙看看！挂掉电话，下意识是不是备库不正常了，数据不同步了？

查看主备同步情况

远程到客户的环境，查看了备库的alert日志，发现同步是正常的，也没有什么报错：

Mon Sep 23 15:33:38 2024
RFS[4]: Selected log 5 for thread 1 sequence 404 dbid 260591189 branch 1159358359
Mon Sep 23 15:33:38 2024
Archived Log entry 9 added for thread 1 sequence 403 ID 0x109c5354 dest 1:
Mon Sep 23 15:33:38 2024
Media Recovery Waiting for thread 1 sequence 404 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 404 Reading mem 0
  Mem# 0: /u01/oradata/dgora11g/stdredo02.log
Mon Sep 23 15:34:12 2024
Archived Log entry 10 added for thread 1 sequence 404 ID 0x109c5354 dest 1:
Mon Sep 23 15:34:12 2024
RFS[4]: Selected log 4 for thread 1 sequence 405 dbid 260591189 branch 1159358359
Mon Sep 23 15:34:12 2024
Media Recovery Waiting for thread 1 sequence 405 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 405 Reading mem 0
  Mem# 0: /u01/oradata/dgora11g/stdredo01.log
Mon Sep 23 15:34:29 2024
RFS[4]: Selected log 5 for thread 1 sequence 406 dbid 260591189 branch 1159358359
Mon Sep 23 15:34:29 2024
Archived Log entry 11 added for thread 1 sequence 405 ID 0x109c5354 dest 1:
Mon Sep 23 15:34:29 2024
Media Recovery Waiting for thread 1 sequence 406 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 406 Reading mem 0
  Mem# 0: /u01/oradata/dgora11g/stdredo02.log
Mon Sep 23 15:35:15 2024
RFS[4]: Selected log 4 for thread 1 sequence 407 dbid 260591189 branch 1159358359
Mon Sep 23 15:35:15 2024
Archived Log entry 12 added for thread 1 sequence 406 ID 0x109c5354 dest 1:
Mon Sep 23 15:35:15 2024
Media Recovery Waiting for thread 1 sequence 407 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 407 Reading mem 0
  Mem# 0: /u01/oradata/dgora11g/stdredo01.log

复制

备库没问题，数据同步正常，alert日志也没报错。心里也有点疑惑，两边查出来的结果怎么不一样，难道遇到bug 了 ？

问题重现

于是找了客户，问了他用的什么语句查询，客户给的查询语句如下：

SELECT dt.tablespace_name as tablespace,
       dt.contents as type,
       dt.block_size * dtum.used_space as bytes,
       dt.block_size * dtum.tablespace_size as max_bytes,
       dt.block_size * (dtum.tablespace_size - dtum.used_space) as free
  FROM dba_tablespace_usage_metrics dtum, dba_tablespaces dt
 WHERE dtum.tablespace_name = dt.tablespace_name
 order by tablespace;
复制

上述语句，在主库查询进行查询，结果如下：

TABLESPACE		       TYPE	      BYTES  MAX_BYTES	     FREE
------------------------------ --------- ---------- ---------- ----------
GGTBS			       PERMANENT    4980736  275046400	270065664
SYSAUX			       PERMANENT  685047808  946135040	261087232
SYSTEM			       PERMANENT  801177600 1040506880	239329280
TBS			       PERMANENT    1048576  233103360	232054784
TEMP			       TEMPORARY	  0  282558464	282558464
UNDOTBS1		       UNDO	   20185088  301432832	281247744
USERS			       PERMANENT    1703936  235724800	234020864

7 rows selected.

复制

把它放到备库查询，确实返回的结果为空：

sys@dgora11g> SELECT dt.tablespace_name as tablespace,
       dt.contents as type,
       dt.block_size * dtum.used_space as bytes,
       dt.block_size * dtum.tablespace_size as max_bytes,
       dt.block_size * (dtum.tablespace_size - dtum.used_space) as free
  FROM d  2    3    4    5    6  ba_tablespace_usage_metrics dtum, dba_tablespaces dt
 WHERE dtum.tablespace_name = dt.tablespace_name
 order by tablespace;  7    8  

no rows selected

复制

查找问题根源

查询语句比较简单，就是dba_tablespace_usage_metrics 和 dba_tablespaces 进行关联查找，在备库上查看这两个视图数据时，发现了问题：

sys@dgora11g> select count(*) from dba_tablespaces;

  COUNT(*)
----------
	 7

sys@dgora11g>  select count(*) from  dba_tablespace_usage_metrics;

  COUNT(*)
----------
	 0

复制

dba_tablespace_usage_metrics 这个视图查出来的数据为0，当然那个查询语句返回结果也为空了，去主库上查了dba_tablespace_usage_metrics是正常的。

主库的dba_tablespace_usage_metrics数据有7条

sys@ora11g> select count(*) from  dba_tablespace_usage_metrics;

  COUNT(*)
----------
	 7


复制

查看dba_tablespace_usage_metrics视图的定义：

sys@ora11g> set pagesize 500
sys@ora11g> set long 999999
sys@ora11g> select text from dba_views where view_name='DBA_TABLESPACE_USAGE_METRICS';

TEXT
--------------------------------------------------------------------------------
SELECT	t.name,
	tstat.kttetsused,
	tstat.kttetsmsize,
	(tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM	sys.ts$ t, x$kttets tstat
  WHERE
	t.online$ != 3 and
	t.bitmapped <> 0 and
	t.contents$ = 0 and
	bitand(t.flags, 16) <> 16 and
	t.ts# = tstat.kttetstsn
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ <> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#

复制

通过上面的代码，我们可以看到，DBA_TABLESPACE_USAGE_METRICS能查询所有类型的表空间，其本质也是进行了3个union，其关键数据还是出自：vfilespace_usage 视图。vfilespace_usage在备库是没有数据的，而主库是正常的。

sys@dgora11g> select count(*) from v$filespace_usage;

  COUNT(*)
----------
	 0
复制

由于物理备库通常处于只读模式，某些动态性能视图在物理备库中不会更新。官方的这个文档DBA_TABLESPACE_USAGE_METRICS content on Standby Databases (Doc ID 2420176.1) 提到DBA_TABLESPACE_USAGE_METRICS 只在主库上进行更新的，并不认为这是一个bug。

知道问题的所在，就让客户换一个查询语句，用DBA_DATA_FILES、DBA_FREE_SPACE去关联查询，就能正常查出来结果了，查询语句如下：

select dbf.tablespace_name "Tablespace Name",
       round(dbf.totalspace,2) "Total(M)",
       round(dbf.used - dfs.freespace,2) "Use(M)",
       round(nvl(dfs.freespace, 0) + dbf.totalspace - dbf.used,2) "Free(M)",
       round(((dbf.used - nvl(dfs.freespace, 0)) / dbf.totalspace) * 100,2) "Use%",
       round(((nvl(dfs.freespace, 0) + dbf.totalspace - dbf.used) /
       dbf.totalspace) * 100,2) "Free%"
  from (select t.tablespace_name,
               sum(greatest(t.maxbytes, t.bytes)) / 1024 / 1024 as totalspace,
               sum(t.bytes) / 1024 / 1024 as used
          from dba_data_files t
         group by t.tablespace_name) dbf
  left join (select tt.tablespace_name,
                    sum(tt.bytes) / 1024 / 1024 freespace
               from dba_free_space tt
              group by tt.tablespace_name) dfs
    on dbf.tablespace_name = dfs.tablespace_name;
复制