文章目录
前言
在“年轻”时候,由于操作错误导致一个数据库的数据文件存在了遗留问题,近期各备份厂商进行备份测试,无法备份这个库:原因是这个表空间无法进行备份。
展示下之前的操作步骤:
一、处理办法:
处理故障产生过程和处理办法:
1.模拟创建文件后删除offline drop
2.rm删除文件
3.delete删除字典记录file$
4.尝试rman备份,果然预期报错
5.把rm的文件创建回去alter database create datafile 26 as ‘/u01/app/oracle/oradata/ORCL/jyc/users10.dbf’;
6.测试rman备份正常。
二、模拟操作:
1.创建文件后删除offline drop
SQL> alter session set container=DBCENTER;
Session altered.
SQL> select file_id,file_name,ONLINE_STATUS from dba_data_files;
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
10
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/system.649.1056480121
SYSTEM
11
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/sysaux.650.1056480121
ONLINE
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
12
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/undotbs1.644.1056480121
ONLINE
13
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/undo_2.661.1056480135
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
ONLINE
14
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.652.1056480135
ONLINE
51
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.860.1120400973
ONLINE
52
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.861.1120400977
ONLINE
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
50
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/dbcenter.706.1056569261
ONLINE
60
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/test.865.1120403261
ONLINE
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
9 rows selected.
SQL> select tablespace_name,file_id,file_name,ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
SYSTEM 10
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/system.649.1056480121
SYSTEM
SYSAUX 11
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/sysaux.650.1056480121
ONLINE
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
UNDOTBS1 12
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/undotbs1.644.1056480121
ONLINE
UNDO_2 13
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/undo_2.661.1056480135
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
ONLINE
USERS 14
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.652.1056480135
ONLINE
USERS 51
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.860.1120400973
ONLINE
USERS 52
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.861.1120400977
ONLINE
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
DBCENTER 50
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/dbcenter.706.1056569261
ONLINE
TEST 60
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/test.865.1120403261
ONLINE
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
9 rows selected.
SQL> alter tablespace users add datafile 'DATA' size 1M;
Tablespace altered.
SQL> select tablespace_name,file_id,file_name,ONLINE_STATUS from dba_data_files where tablespace_name='USERS';
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
USERS 14
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.652.1056480135
ONLINE
USERS 51
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.860.1120400973
ONLINE
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
USERS 52
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.861.1120400977
ONLINE
USERS 68
/oracle/app/oracle/product/19c/dbhome_1/dbs/DATA
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
ONLINE
SQL> select file_name,ONLINE_STATUS from dba_data_files where FILE_ID=68;
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
/oracle/app/oracle/product/19c/dbhome_1/dbs/DATA
ONLINE
SQL> alter database datafile '/oracle/app/oracle/product/19c/dbhome_1/dbs/DATA' OFFLINE DROP;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 129
Next log sequence to archive 130
Current log sequence 130
SQL> select file_name,ONLINE_STATUS from dba_data_files where FILE_ID=68;
FILE_NAME
--------------------------------------------------------------------------------
ONLINE_
-------
/oracle/app/oracle/product/19c/dbhome_1/dbs/DATA
RECOVER
SQL> select file#,status from v$datafile where file#=68;
FILE# STATUS
---------- -------
68 RECOVER
2.rm 删除文件
SQL> !rm /oracle/app/oracle/product/19c/dbhome_1/dbs/DATA
SQL> !ls -l /oracle/app/oracle/product/19c/dbhome_1/dbs/DATA
ls: cannot access /oracle/app/oracle/product/19c/dbhome_1/dbs/DATA: No such file or directory
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
3.delete删除字典记录file$
SQL> delete from file$ where file#=68;
1 row deleted.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
4.尝试rman备份
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 13 15:26:41 2024
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DW (DBID=3474552567)
RMAN> backup database;
Starting backup at 2024-06-13 15:26:46
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=233 instance=dw1 device type=DISK
RMAN-06169: could not read file header for datafile 68 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/13/2024 15:26:48
RMAN-06056: could not access datafile 68
RMAN> exit
5.把rm的文件创建回去
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 13 15:28:30 2024
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> alter session set container=DBCENTER;
Session altered.
SQL> alter database create datafile 68 as '+DATA';
Database altered.
SQL> select file#,status from v$datafile where file#=68;
FILE# STATUS
---------- -------
68 RECOVER
SQL> select file#,status,name from v$datafile where file#=68;
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
68 RECOVER
+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.942.1171553363
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
此处需要注意一点:create 的文件大小要和之前的文件大小一致才可以。
1310720blocks*8k/1024=10240M
6.测试rman备份正常
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 13 15:29:47 2024
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DW (DBID=3474552567)
RMAN> backup database;
Starting backup at 2024-06-13 15:29:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=233 instance=dw1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/DW/DATAFILE/system.648.1056479069
input datafile file number=00004 name=+DATA/DW/DATAFILE/undotbs1.643.1056479109
input datafile file number=00003 name=+DATA/DW/DATAFILE/sysaux.668.1056479095
input datafile file number=00009 name=+DATA/DW/DATAFILE/undotbs2.667.1056479729
input datafile file number=00057 name=+DATA/DW/DATAFILE/test.862.1120403003
input datafile file number=00007 name=+DATA/DW/DATAFILE/users.657.1056479111
input datafile file number=00059 name=+DATA/DW/DATAFILE/test.864.1120403025
input datafile file number=00058 name=+DATA/DW/DATAFILE/test.863.1120403023
channel ORA_DISK_1: starting piece 1 at 2024-06-13 15:29:52
channel ORA_DISK_1: finished piece 1 at 2024-06-13 15:30:07
piece handle=/oracle/app/oracle/product/19c/dbhome_1/dbs/3n2t8v3g_1_1 tag=TAG20240613T152952 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/sysaux.650.1056480121
input datafile file number=00010 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/system.649.1056480121
input datafile file number=00012 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/undotbs1.644.1056480121
input datafile file number=00013 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/undo_2.661.1056480135
input datafile file number=00068 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.942.1171553363
input datafile file number=00052 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.861.1120400977
input datafile file number=00051 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.860.1120400973
input datafile file number=00014 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/users.652.1056480135
input datafile file number=00050 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/dbcenter.706.1056569261
input datafile file number=00060 name=+DATA/DW/B40FCB825F88345DE053B634A8C0D6E5/DATAFILE/test.865.1120403261
channel ORA_DISK_1: starting piece 1 at 2024-06-13 15:30:08
channel ORA_DISK_1: finished piece 1 at 2024-06-13 15:30:15
piece handle=/oracle/app/oracle/product/19c/dbhome_1/dbs/3o2t8v3v_1_1 tag=TAG20240613T152952 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00016 name=+DATA/DW/B412D1E327904CCBE053B534A8C003EB/DATAFILE/sysaux.656.1056493151
input datafile file number=00015 name=+DATA/DW/B412D1E327904CCBE053B534A8C003EB/DATAFILE/system.645.1056493151
input datafile file number=00017 name=+DATA/DW/B412D1E327904CCBE053B534A8C003EB/DATAFILE/undotbs1.646.1056493151
input datafile file number=00018 name=+DATA/DW/B412D1E327904CCBE053B534A8C003EB/DATAFILE/undo_2.660.1056493151
input datafile file number=00019 name=+DATA/DW/B412D1E327904CCBE053B534A8C003EB/DATAFILE/users.662.1056493151
input datafile file number=00061 name=+DATA/DW/B412D1E327904CCBE053B534A8C003EB/DATAFILE/test.866.1120403309
channel ORA_DISK_1: starting piece 1 at 2024-06-13 15:30:15
channel ORA_DISK_1: finished piece 1 at 2024-06-13 15:30:22
piece handle=/oracle/app/oracle/product/19c/dbhome_1/dbs/3p2t8v47_1_1 tag=TAG20240613T152952 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00036 name=+DATA/DW/B412E5F265BD55CAE053B534A8C0CB7D/DATAFILE/sysaux.692.1056493625
input datafile file number=00035 name=+DATA/DW/B412E5F265BD55CAE053B534A8C0CB7D/DATAFILE/system.691.1056493625
input datafile file number=00037 name=+DATA/DW/B412E5F265BD55CAE053B534A8C0CB7D/DATAFILE/undotbs1.689.1056493625
input datafile file number=00038 name=+DATA/DW/B412E5F265BD55CAE053B534A8C0CB7D/DATAFILE/undo_2.690.1056493625
input datafile file number=00039 name=+DATA/DW/B412E5F265BD55CAE053B534A8C0CB7D/DATAFILE/users.688.1056493625
channel ORA_DISK_1: starting piece 1 at 2024-06-13 15:30:22
channel ORA_DISK_1: finished piece 1 at 2024-06-13 15:30:29
piece handle=/oracle/app/oracle/product/19c/dbhome_1/dbs/3q2t8v4e_1_1 tag=TAG20240613T152952 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00041 name=+DATA/DW/B412E5F265BF55CAE053B534A8C0CB7D/DATAFILE/sysaux.698.1056493701
input datafile file number=00040 name=+DATA/DW/B412E5F265BF55CAE053B534A8C0CB7D/DATAFILE/system.697.1056493701
input datafile file number=00042 name=+DATA/DW/B412E5F265BF55CAE053B534A8C0CB7D/DATAFILE/undotbs1.695.1056493701
input datafile file number=00043 name=+DATA/DW/B412E5F265BF55CAE053B534A8C0CB7D/DATAFILE/undo_2.696.1056493701
input datafile file number=00044 name=+DATA/DW/B412E5F265BF55CAE053B534A8C0CB7D/DATAFILE/users.694.1056493701
channel ORA_DISK_1: starting piece 1 at 2024-06-13 15:30:29
channel ORA_DISK_1: finished piece 1 at 2024-06-13 15:30:32
piece handle=/oracle/app/oracle/product/19c/dbhome_1/dbs/3r2t8v4l_1_1 tag=TAG20240613T152952 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00046 name=+DATA/DW/B412E5F265C155CAE053B534A8C0CB7D/DATAFILE/sysaux.704.1056493713
input datafile file number=00045 name=+DATA/DW/B412E5F265C155CAE053B534A8C0CB7D/DATAFILE/system.703.1056493713
input datafile file number=00047 name=+DATA/DW/B412E5F265C155CAE053B534A8C0CB7D/DATAFILE/undotbs1.701.1056493713
input datafile file number=00048 name=+DATA/DW/B412E5F265C155CAE053B534A8C0CB7D/DATAFILE/undo_2.702.1056493713
input datafile file number=00049 name=+DATA/DW/B412E5F265C155CAE053B534A8C0CB7D/DATAFILE/users.700.1056493713