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

最新推荐文章于 2024-10-01 21:51:51 发布

老苏畅谈运维

最新推荐文章于 2024-10-01 21:51:51 发布

阅读量750

点赞数 11

分类专栏：故障诊断 oracle dataguard 文章标签： oracle 数据库 dataguard

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.csdn.net/zhirongsu/article/details/142463833

版权

oracle 同时被 3 个专栏收录

41 篇文章 2 订阅

订阅专栏

dataguard

10 篇文章 0 订阅

订阅专栏

故障诊断

7 篇文章 0 订阅

订阅专栏

作者介绍：老苏，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 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。其关键数据还是出自：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 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;

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