Oracle RMAN备份时出现RMAN-06056: could not access datafile 6及其引发的另外两个问题
用rman备份数据库时出现以下错误：
[oracle@mzl orcl]$ rman target/
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 13 15:36:06
Copyright (c) 1982, 2005, . All rights reserved.
connected to target database: ORCL (DBID=1172558471)
RMAN> backup database;
Starting backup at 13-FEB-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
could not read file header for datafile 6 error reason 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/13/2008 15:36:14
RMAN-06056: could not access datafile 6
1 查看数据6文件是什么
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/orcl/system01.dbf
2
/u01/app/oracle/oradata/orcl/undotbs01.dbf
3
/u01/app/oracle/oradata/orcl/sysaux01.dbf
FILE#
----------
NAME
--------------------------------------------------------------------------------
4
/u01/app/oracle/oradata/orcl/users01.dbf
5
/u01/app/oracle/oradata/orcl/example01.dbf
6
/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006
解决办法：
看MISSING00006基于那个表空间，然后删除该表空间
select a.file#,a.name,b.name
from v$datafile a,v$tablespace b
where a.ts#=b.ts#
看到file 6 对应的表空间是users.
SQL> drop tablespace users;
但是，此时出现了个问题：ORA-12919: Can not drop the default permanent tablespace
sys@ora10g> select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------------- -------------- ------------------------------------
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
此系统的默认永久表空间是USERS
.默认永久表空间的删除方法
既然是因为是“默认”的永久表空间不可以删除，我们仅需将默认的永久表空间指定到其他表空间，然后USERS表空间便可以被顺利删除。
1）将数据库系统的默认永久表空间调整到TBS_SEC_D
sys@ora10g> alter database default tablespace TBS_SEC_D;
Database altered.
2）确认调整效果
sys@ora10g> select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------------- -------------- ------------------------------------
DEFAULT_PERMANENT_TABLESPACE TBS_SEC_D Name of default permanent tablespace
3）删除原默认永久表空间USERS
sys@ora10g> drop tablespace USERS including contents and datafiles;
此时，无奈的是，又出现了一个新的问题：
SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces
解决办法：
SQL> select owner,table_name,tablespace_name from dba_lobs where tablespace_name='USERS';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
OE PURCHASEORDER USERS
OE PURCHASEORDER USERS
OE PURCHASEORDER USERS
OE PURCHASEORDER USERS
OE PURCHASEORDER USERS
OE PURCHASEORDER USERS
OE PURCHASEORDER USERS
OE ACTION_TABLE USERS
OE LINEITEM_TABLE USERS
OE LINEITEM_TABLE USERS
SCOTT PLAN_TABLE USERS
11 rows selected.
SQL> select count(*) from oe.purchaseorder;
COUNT(*)
----------
132
SQL> drop user oe cascade;
User dropped.
SQL> drop tablespace users including contents;
Tablespace dropped.
