删除归档表空间、删除归档文件导致的报错 ORA-08881 ORA-01548



郑重声明：由于本人水平有限，如有不对的地方请大神指正不甚感激（[email protected]）

1.1.1删除归档表空间、删除归档文件导致的报错

1.1.1.1 问题及现象

 

RMAN> backup database;

 

Starting backup at 08-MAY-15

using channel ORA_DISK_1

RMAN-06169: could not read file header fordatafile 3 error reason 4

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGE STACKFOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of backup command at05/08/2015 15:53:41

RMAN-06056: could not access datafile 3

15:56:15 SQL> drop tablespace undotbs1 including contents and datafiles;

drop tablespace undotbs1 including contents anddatafiles

*

ERROR at line 1:

ORA-38881: Cannot drop tablespace UNDOTBS1 on primarydatabase due to guaranteed restore points.

16:01:57 SQL> drop tablespace undotbs1 including contents anddatafiles;

drop tablespace undotbs1 including contents anddatafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU1_1240252155$'found, terminate dropping tablespace

1.1.1.2 检查思路

在备份的时候发现报错，说是3号数据文件出问题，所以我首先检查3号文件对应的是什么表空间，检查后发现是undo表空间，并且数据库设置了还原点，所以需要用到这个undo表空间，然后我们就想办法不让他使用这个undo表空间即可。

1.1.1.3 出现问题的原因

由于是实验环境，刚做了数据库闪回、闪回归档等操作，在操作过程中创建了还原点并且有删除undo表空间的操作，导致数据库备份有问题。

1.1.1.4 解决方法

16:05:47SQL> select * from dba_data_files;

 

SP2-0734: unknowncommand beginning "16:33:05 S..." - rest of line ignored.

16:33:53 SQL>select file_id,file_name,tablespace_name from dba_data_files;

 

   FILE_ID FILE_NAME                                         TABLESPACE_NAME

---------- --------------------------------------------------------------------------------

         4/u01/app/oracle/oradata/oracle007/users01.dbf      USERS

         2/u01/app/oracle/oradata/oracle007/sysaux01.dbf     SYSAUX

         1/u01/app/oracle/oradata/oracle007/system01.dbf     SYSTEM

         5/u01/app/oracle/oradata/oracle007/example01.dbf    EXAMPLE

         8/u01/app/oracle/oradata/ORACLE007/datafile/o1_mf_t TS_16K

           s_16k_bnkltzqk_.dbf

 

         6/u01/app/oracle/oradata/oracle007/ts_16k.dbf       TS_16K

         7/u01/app/oracle/product/11.2.0/db_1/dbs/ts_mssm    TS_MSSM

         9/u01/app/oracle/oradata/ORACLE007/datafile/o1_mf_t TS_16K

           s_16k_bnklv0v6_.dbf

 

        10/u01/app/oracle/product/11.2.0/db_1/dbs/his_data_1 HIS_DATA_1

        11/u01/app/oracle/product/11.2.0/db_1/dbs/undo2.mdf  UNDOTBS2

                           3 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003        UNDOTBS1

11 rows selected.

可以看到3号文件是之前由于前段时间做实验的时候删除undo表空间导致的

我再次尝试直接删除

15:56:15 SQL>drop tablespace undotbs1 including contents and datafiles;

drop tablespaceundotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-38881: Cannot droptablespace UNDOTBS1 on primary database due to guaranteed restore points.

发现报ORA-38881错误，查ORACLE