Data Guard主备库中清理临时文件的正确步骤

前言

最近在项目中，发现磁盘空间不足，为了不进行数据文件迁移，想到将 Oracle 数据库中的临时表空间删除，再把临时表空间创建在其它磁盘目录下，由于该环境为 Oracle Data Guard，以前没进行删除临时表空间操作，并不清楚 DG 并不会像其他 DDL（数据定义语言）操作那样会通过重做日志同步到备库。所以在主库中删除临时文件时，备库不会自动删除对应的临时文件，这就需要手动进行删除临时表空间文件。以下将介绍临时表空间与如何在 DG 中删除主备库临时表空间。

临时表空间

在 Oracle 数据库中，临时表空间（Temporary Tablespace）是专门用于存储临时数据的表空间。这些临时数据主要用于以下场景：

• 排序操作：例如，ORDER BY、DISTINCT、GROUP BY 等操作可能需要排序数据，在没有足够内存的情况下，Oracle 会使用临时表空间来存储这些排序数据。
• 哈希连接：在进行哈希连接时，如果内存不足，Oracle 会使用临时表空间来存储部分数据。
• 创建索引：创建大索引时，Oracle 也可能使用临时表空间来存储中间结果。
• 大型查询的中间结果：一些大型查询可能生成临时结果集，这些结果集将存储在临时表空间中。

临时表空间的特点

• 在数据库重启后，所有的临时数据都会被清除。
• 当执行完操作后，Oracle 会自动清理临时表空间中的数据（但表数据文件大小不会自动收缩）。

临时表空间的管理

1. 创建临时表空间

   create tablespace db_temp
   tempfile '/目录/DB_TEMP.DBF' 
   size 500m
   autoextend on next 100m maxsize unlimited;
   

2. 修改临时表空间大小

   alter database tempfile '/目录/DB_TEMP.DBF' resize 1G;
   

3. 收缩临时表空间大小

   alter tablespace DB_TEMP shrink space keep 100M;
   

4. 查询临时空间容量使用情况

   SELECT * FROM dba_temp_files;
   SELECT * FROM dba_temp_free_space;
   
   --查看临时表空间的使用情况
   SELECT tablespace_name, file_name, bytes / 1024 / 1024 "file_size(M)",
          autoextensible
     FROM dba_temp_files;
     
   --查看当前会话使用的临时空间
   SELECT s.sid, s.serial#, s.username, t.tablespace_name, 
          SUM(t.blocks * 8192) / 1024 / 1024 "Used MB"
   FROM v$session s
   JOIN v$sort_usage t ON s.saddr = t.session_addr
   GROUP BY s.sid, s.serial#, s.username, t.tablespace_name;
   
   

5. 删除临时表空间

   drop tablespace DB_TEMP including contents and datafiles;
   alter database tempfile '/目录/DB_TEMP.DBF' drop;
   

DG 中删除主备库临时表空间

检查主库和备库的临时文件

在主库和备库分别运行以下 SQL 语句，检查临时文件的状态，确认主库与备库都有相应的临时文件。

-- 检查主与备库的临时文件
SELECT tablespace_name, file_name, bytes / 1024 / 1024 "file_size(M)",
       autoextensible
  FROM dba_temp_files;

删除主备库临时表空间

• 在主库上操作：

  删除临时表空间 DB_TEMP 及其文件：然后，执行删除表空间的命令，执行后 Oracle 将删除表空间 DB_TEMP 的定义和所有与该表空间关联的临时文件。

  drop tablespace db_temp including contents and datafiles;
  

  执行删除命令后，要检查临时表空间文件是否已经在操作系统中也物理删除，如果文件还存在，则有以下原因：

  • 文件正被使用：如果删除的临时表空间仍然有会话在使用（即文件正在被某个查询或会话占用），Oracle 可能无法删除该文件。在这种情况下，确保没有会话使用该临时表空间，或者重启数据库。可通过以下 SQL 语句查询哪些会话正在使用：

    --查询正在使用临时文件的会话，查出来之后需要停止这些会话
    --alter system kill session 'sid,serial#';
    SELECT s.sid, s.serial#, s.username, t.tablespace_name
    FROM v$session s
    JOIN v$sort_usage t ON s.saddr = t.session_addr
    WHERE t.tablespace_name = 'DB_TEMP';
    

    执行以上操作后，可以尝试在操作系统中删除对应的临时表空间文件 DB_TEMP.DBF，如果系统是 Windows 删除直接进入目录删除（如果不能删除则需要重启数据库服务再进行删除）；如果是 Linux 操作系统，则使用 rm -f /目录/DB_TEMP.DBF 强制删除文件。

  • 操作系统权限问题：确保 Oracle 进程具有足够的文件系统权限。

• 在备库上操作：

  • 停止备库应用日志

    alter database recover managed standby database cancel;
    

  • 在备库手动删除临时表空间

    drop tablespace db_temp including contents and datafiles;
    

  • 删除成功后重新启动备库日志应用

    alter database recover managed standby database using current logfile disconnect;
    

  • 验证主备临时表空间的一致性

    -- 检查主与备库的临时文件
    SELECT tablespace_name, file_name, bytes / 1024 / 1024 "file_size(M)",
           autoextensible
      FROM dba_temp_files;
    

总结

在 Oracle 中，临时表空间的差异不会影响数据的同步，总结出临时表空间有以下特点：

• 不同用户分别指定不同的临时表空间
• 临时表空间只存储临时对象（如排序区、哈希连接、排序和聚合操作等），会话结束后会自动清除
• 在 DG 中，临时表空间的差异不会影响主备库的数据一致性，但如果备库运行查询时需要排序等操作，缺少适当的临时表空间可能导致性能问题或错误。
• 在 DG 中，在备库添加临时文件时，路径和大小可以根据备库的存储需求进行调整，不一定与主库完全一致。