ADG备库中某个PDB缺失temp文件

Alfred Zhao

发布于 2023-05-23 17:01:33

2460

发布于 2023-05-23 17:01:33

文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

之前认为缺失的temp文件在开库时会自动创建，但其实也有不能自动创建的场景，alert会有类似如下提示：

2023-05-11T20:35:35.974983+08:00
AWR(6):*********************************************************************
AWR(6):WARNING: The following temporary tablespaces in container(AWR)
AWR(6):         contain no files.
AWR(6):         This condition can occur when a backup controlfile has
AWR(6):         been restored.  It may be necessary to add files to these
AWR(6):         tablespaces.  That can be done using the SQL statement:
AWR(6):
AWR(6):         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
AWR(6):
AWR(6):         Alternatively, if these temporary tablespaces are no longer
AWR(6):         needed, then they can be dropped.
AWR(6):           Empty temporary tablespace: TEMP
AWR(6):*********************************************************************

查询tempfile，发现确实AWR那个PDB没有对应临时文件：

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATADG/DEMORAC/temp01.dbf
+DATADG/DEMORAC/pdbseed/temp01.dbf
+DATADG/DEMORAC/pdb1/temp01.dbf
+DATADG/DEMORAC/pdb2/temp01.dbf

进入缺少临时文件的PDB：AWR中，为其增加TEMPFILE：

SQL> alter session set container=awr;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATADG';

Tablespace altered.

添加后就可以查询到：

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATADG/DEMORAC/F6F2B4F2E4946043E0530401A8C063D6/TEMPFILE/temp.299.1136580247

SQL> conn / as sysdba
Connected.
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATADG/DEMORAC/temp01.dbf
+DATADG/DEMORAC/pdbseed/temp01.dbf
+DATADG/DEMORAC/pdb1/temp01.dbf
+DATADG/DEMORAC/pdb2/temp01.dbf
+DATADG/DEMORAC/F6F2B4F2E4946043E0530401A8C063D6/TEMPFILE/temp.299.1136580247
SQL>

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-05-11，如有侵权请联系 cloudcommunity@tencent.com 删除

sql