主库 drop tablespace后备库存储空间不释放案例一则

上周,应用人员误操作在平台上tablespace name 打错名字,从而想删除表空间进行重新建立。重建后此数据库备库触发了文件系统满的报警,备库文件系统使用率已经为100%,但是数据库值班人员观察到备库之前建立错误的数据文件已经不在,为何文件系统存储中文件却没有删掉?之前怎么没有出现如此的情况?

我随后做了一个实验给他,原因是在drop tablespace时,没有加上including contents and datafiles

--主库:
SQL> select username,count(*) from gv$session group by username order by 2 desc;

USERNAME                         COUNT(*)
------------------------------ ----------
                                      174
ZABBIX                                  6
SYS                                     5
DBSNMP                                  4
PUBLIC                                  1

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create tablespace albert datafile '+DG_DATA' size 1g;

Tablespace created.


SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT --刚刚创建的表空间

     FILE# NAME
---------- -----------------------------------------------------------------
         1 +DG_DATA/Travelskydba/datafile/system.259.1002299725
         2 +DG_DATA/Travelskydba/datafile/sysaux.260.1002299741
         3 +DG_DATA/Travelskydba/datafile/undotbs1.261.1002299753
         4 +DG_DATA/Travelskydba/datafile/undotbs2.263.1002299769
         5 +DG_DATA/Travelskydba/datafile/users.264.1002299781
         6 +DG_DATA/Travelskydba/datafile/tbs_test_1025_1.269.1022603323
         7 +DG_DATA/Travelskydba/datafile/tbs_test_1025_2.270.1022603429
         8 +DG_DATA/Travelskydba/datafile/albert.271.1035217783


--备库:
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT


SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert.271.1035217783

8 rows selected.

一、加入including contents and datafiles

--主库删除:
SQL> drop tablespace albert including contents and datafiles;

Tablespace dropped.



--备库确认,正常删除掉
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

7 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected.


total 68780320
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:35 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl01.ctl

此时文件已经正常删除掉。


二、主库不加including contents and datafiles;

--主库创建文件:
SQL> create tablespace albert_leung datafile '+DG_DATA' size 2g;

Tablespace created.

--备库确认:

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert_leung.271.1035218283

8 rows selected.

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT_LEUNG


drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:38 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl01.ctl


--主库删除文件:
SQL> drop tablespace albert_leung;

Tablespace dropped.

--备库数据字典:

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected

SQL> select FILE#,NAME from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

--可以看到,备库中的数据字典中已经删除掉,也就是说controlfile中该记录已经被删除

--但是观察到,备库文件系统,物理的数据文件还在,并没有删除。
[ora11g@vm-vmw45661-ora Travelskydba]$ ls -l
total 68780320
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283   --主库CHECKPOINT后不更新
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl01.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:41 system.259.1002299725
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
[ora11g@vm-vmw45661-ora Travelskydba]$ 

--此时,可以使用操作系统命令rm删除掉文件系统中命名错误的数据文件,因为controlfile中已经没有此记录,所以不会涉及数据库的实例恢复。

这个实验,没有加上including contents and datafiles 我只是回答了他第一个问题,但是第二个问题:“为什么之前我删除tablespace的时候,也没有加,怎么就没事呢?

根因在于Oracle Managed Files,这次出问题的此套备库没有使用OMF特性,也就是drop tablespace后,Oracle不会帮你自动的去清理物理文件,只会删除controlfile中的记录,但是之前同事所提到的没事的状况,是因为备库为RAC+ASM架构,默认采用了OMF管理数据文件,所以及时没有添加 including contents and datafiles 也会帮你自动清理文件。

官方原文如下:

1、 Unlike files that are not managed by the database, when an Oracle managed data file or temp file is dropped, the filename is removed from the control file and the file is automatically deleted from the file 

2、 When recovery of a standby database encounters redo for the deletion of a tablespace, it deletes any Oracle managed data files in the local file system. Note that this is independent of the INCLUDING DATAFILES option issued at the primary database.