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

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

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

查看主备同步情况

远程到客户的环境，查看了备库的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 0Mem# 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 0Mem# 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 0Mem# 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 0Mem# 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 freeFROM dba_tablespace_usage_metrics dtum, dba_tablespaces dtWHERE dtum.tablespace_name = dt.tablespace_nameorder 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	2340208647 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 freeFROM d  2    3    4    5    6  ba_tablespace_usage_metrics dtum, dba_tablespaces dtWHERE dtum.tablespace_name = dt.tablespace_nameorder by tablespace;  7    8  no rows selected

查找问题根源

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

sys@dgora11g> select count(*) from dba_tablespaces;COUNT(*)
----------7sys@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) * 100FROM	sys.ts$ t, x$kttets tstatWHEREt.online$ != 3 andt.bitmapped <> 0 andt.contents$ = 0 andbitand(t.flags, 16) <> 16 andt.ts# = tstat.kttetstsn
unionSELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),(sum(f.allocated_space)/sum(f.file_maxsize))*100FROM sys.ts$ t, v$filespace_usage fWHEREt.online$ != 3 andt.bitmapped <> 0 andt.contents$ <> 0 andf.flag = 6 andt.ts# = f.tablespace_idGROUP BY t.name, f.tablespace_id, t.ts#
unionSELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),(sum(f.allocated_space)/sum(f.file_maxsize))*100FROM sys.ts$ t, gv$filespace_usage f, gv$parameter paramWHEREt.online$ != 3 andt.bitmapped <> 0 andf.inst_id = param.inst_id andparam.name = 'undo_tablespace' andt.name = param.value andf.flag = 6 andt.ts# = f.tablespace_idGROUP BY t.name, f.tablespace_id, t.ts#

通过上面的代码，我们可以看到，DBA_TABLESPACE_USAGE_METRICS说能查询所有类型的表空间，其本质也是进行了3个union。其关键数据还是出自：v $filespace_usage 视图。v$ filespace_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 usedfrom dba_data_files tgroup by t.tablespace_name) dbfleft join (select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespacefrom dba_free_space ttgroup by tt.tablespace_name) dfson dbf.tablespace_name = dfs.tablespace_name;

关注我，学习更多的数据库知识！
请添加图片描述