Oracle通过Rman的"copy datafile"转移数据文件后不要使用sqlplus来重命名文件位置和文件名
Oracle通过Rman的"copy datafile"转移数据文件后不要使用sqlplus来重命名文件位置和文件名
如果在RAC中将表空间文件创建到了本地后，
使用 RMAN COPY 命令将数据库从文件系统复制到 ASM 存储。
其中，复制是通过 RMAN 完成的，但数据库文件的重命名是通过 SQLPlus 使用'alter database rename file 'X' to 'Y'完成的。
会导致如下问题：
1.警告日志有大量的报错警告信息：
[oracle@xxxxxx1 trace]$ grep "datafilecopy header validation failure for file" alert_xxxxx1.log
datafilecopy header validation failure for file +DATA/xxxxx/datafile/xxxx_xxxx.1510.1075797967
...删除110行...
datafilecopy header validation failure for file +DATA/xxxxx/datafile/xxxx_xxxx.1510.1075797967
2.Rman通过delete obsolete删除不符合“CONFIGURE RETENTION POLICY”策略的备份会有如下报错：
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy +DATA/xxxxx/datafile/xxxx_xxxx.1510.1075797967
问题重现
10:53:50 SYS@test1(469)> create tablespace zkm datafile 'data' size 10m; Tablespace created. Elapsed: 00:00:00.97 10:54:11 SYS@test1(469)> select file_id,file_name from dba_data_files where tablespace_name='ZKM'; FILE_ID ---------- FILE_NAME -------------------------------------------------------------------------------- 17 /u01/app/oracle/product/11.2.0/db_1/dbs/data Elapsed: 00:00:00.00 10:54:39 SYS@test1(469)> alter database datafile 17 offline; Database altered. Elapsed: 00:00:00.02 [oracle@dev-testdb dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 9 10:54:59 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: test (DBID=3292082733) RMAN> copy datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/data' to '+data'; Starting backup at 2021-10-09 10:55:45 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 instance=test1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00017 name=/u01/app/oracle/product/11.2.0/db_1/dbs/data output file name=+DATA/test/datafile/zkm.304.1085482545 tag=TAG20211009T105545 RECID=2 STAMP=1085482545 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2021-10-09 10:55:46 11:00:05 SYS@test1(469)> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/data' to '+DATA/test/datafile/zkm.304.1085482545'; Database altered. Elapsed: 00:00:00.01 11:00:18 SYS@test1(469)> recover datafile 17; Media recovery complete. 11:00:30 SYS@test1(469)> alter database datafile 17 online; Database altered. Elapsed: 00:00:00.01
Datafile Copies信息：
RMAN> list datafilecopy all; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 2 17 A 2021-10-09 10:55:45 33504214 2021-10-09 10:54:11 Name: +DATA/test/datafile/zkm.304.1085482545 Tag: TAG20211009T105545 RMAN> list copy of database; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 2 17 A 2021-10-09 10:55:45 33504214 2021-10-09 10:54:11 Name: +DATA/test/datafile/zkm.304.1085482545 Tag: TAG20211009T105545
此时通过crosscheck datafilecopy all后，警告日志即会出现类似的报错：
RMAN> crosscheck datafilecopy all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 instance=eportdb1 device type=DISK validation failed for datafile copy datafile copy file name=+DATA/test/datafile/zkm.304.1085482545 RECID=2 STAMP=1085482545 Crosschecked 1 objects
警告日志：
Sat Oct 09 11:15:09 2021 datafilecopy header validation failure for file +DATA/test/datafile/zkm.304.1085482545 datafilecopy header validation failure for file +DATA/test/datafile/zkm.304.1085482545
原因
根据MOS文件：RMAN Delete Obsolete Wants to Delete Live Datafile After File is Copied via RMAN and Renamed via SqlPlus (文档 ID 460365.1)
The renaming of the datafiles was done via SQL Plus and NOT via RMAN with SWITCH command.
When SQLPlus is used to rename the files, only the view v$datafile is updated with the new name.
However, from an RMAN perspective:
When RMAN COPY is executed, new entries in rc_datafile_copy (v$datafile_copy) are inserted for the new files - at this point the new files are considered to be datafilecopies.
The RMAN SWITCH command will switch them round so that the new files are now in rc_datafile
(v$datafile) and the original files now become themselves datafilecopies in rc_datafile_copy
(v$datafile_copy).
If the renaming is done via SQLPlus then this switch doesnt occur: the result is rc_datafile
(v$datafile) and rc_datafile_copy (v$datafile_copy) both pointing to the new datafiles. Now
the LIVE files are considered to be both datafiles AND datafilecopies.数据文件的重命名是通过 SQL Plus 完成的，而不是通过带有 SWITCH 命令的 RMAN。
当使用 SQLPlus 重命名文件时，只有视图 v$datafile 使用新名称更新。
但是，从 RMAN 的角度来看：
当执行 RMAN COPY 时，会为新文件插入 rc_datafile_copy (v$datafile_copy) 中的新条目——此时新文件被视为数据文件副本。
RMAN SWITCH 命令将切换它们，以便新文件现在位于 rc_datafile
(v$datafile) 中，而原始文件现在成为 rc_datafile_copy
(v$datafile_copy) 中的数据文件副本。
如果重命名是通过 SQLPlus 完成的，则不会发生此切换：结果是 rc_datafile
(v$datafile) 和 rc_datafile_copy (v$datafile_copy) 都指向新的数据文件。现在
LIVE 文件被认为是数据文件和数据文件副本。
查询数据库，
11:29:09 SYS@test1(469)> select FILE# ,NAME from v$datafile_copy where deleted='NO'; FILE# NAME ---------- ---------------------------------------------------------------------------------------------------- 17 +DATA/test/datafile/zkm.304.1085482545 Elapsed: 00:00:00.00
解决方法
1. 确认当前数据文件的名称：
RMAN> report schema;
2. 确认数据文件副本的名称（这将显示相同的数据文件列表）：
RMAN> list datafilecopy all; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 2 17 A 2021-10-09 10:55:45 33504214 2021-10-09 10:54:11 Name: +DATA/test/datafile/zkm.304.1085482545 Tag: TAG20211009T105545 RMAN> list copy of database; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 2 17 A 2021-10-09 10:55:45 33504214 2021-10-09 10:54:11 Name: +DATA/test/datafile/zkm.304.1085482545 Tag: TAG20211009T105545
3. 从 rman 存储库中删除 datafilecopy 条目（这不会删除物理文件，而只是更新 rman 存储库）：
案例：RMAN>change datafilecopy <key> uncatalog;
RMAN> change datafilecopy 2 uncatalog; uncataloged datafile copy datafile copy file name=+DATA/test/datafile/zkm.304.1085482545 RECID=2 STAMP=1085482545 Uncataloged 1 objects
4. 确认已删除副本：
RMAN> list copy of database; specification does not match any datafile copy in the repository RMAN> list datafilecopy all; specification does not match any datafile copy in the repository
再次查看视图v$datafile_copy：
14:28:10 SYS@test1(469)> select FILE# ,NAME from v$datafile_copy where deleted='NO'; no rows selected Elapsed: 00:00:00.00
正确的迁移本地文件至ASM磁盘的方法
14:29:43 SYS@test1(469)> drop tablespace zkm including contents and datafiles; Tablespace dropped. Elapsed: 00:00:01.10 14:30:22 SYS@test1(469)> create tablespace zkm datafile 'data' size 10m; Tablespace created. Elapsed: 00:00:00.98 14:30:37 SYS@test1(469)> col file_name for a100 14:30:43 SYS@test1(469)> select file_id,file_name from dba_data_files where tablespace_name='ZKM'; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------------------------------------- 17 /u01/app/oracle/product/11.2.0/db_1/dbs/data Elapsed: 00:00:00.00 14:41:04 SYS@test1(469)> alter database datafile 17 offline; Database altered. Elapsed: 00:00:00.02 RMAN> copy datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/data' to '+data'; Starting backup at 2021-10-09 14:41:54 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00017 name=/u01/app/oracle/product/11.2.0/db_1/dbs/data output file name=+DATA/test/datafile/zkm.304.1085496115 tag=TAG20211009T144154 RECID=4 STAMP=1085496114 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2021-10-09 14:41:55 14:42:28 SYS@test1(469)> select FILE# ,NAME from v$datafile_copy ; FILE# NAME ---------- ---------------------------------------------------------------------------------------------------- 17 17 17 17 +DATA/test/datafile/zkm.304.1085496115 Elapsed: 00:00:00.00 RMAN> switch datafile 17 to copy; datafile 17 switched to datafile copy "+DATA/test/datafile/zkm.304.1085496115" RMAN> list copy of database; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 5 17 A 2021-10-09 14:42:46 33512990 2021-10-09 14:38:15 Name: /u01/app/oracle/product/11.2.0/db_1/dbs/data RMAN> list datafilecopy all; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 5 17 A 2021-10-09 14:42:46 33512990 2021-10-09 14:38:15 Name: /u01/app/oracle/product/11.2.0/db_1/dbs/data 14:45:22 SYS@test1(469)> select FILE# ,NAME from v$datafile_copy ; FILE# NAME ---------- ---------------------------------------------------------------------------------------------------- 17 17 17 17 17 /u01/app/oracle/product/11.2.0/db_1/dbs/data Elapsed: 00:00:00.01 RMAN> delete datafilecopy all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 instance=test1 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 5 17 A 2021-10-09 14:42:46 33512990 2021-10-09 14:38:15 Name: /u01/app/oracle/product/11.2.0/db_1/dbs/data Do you really want to delete the above objects (enter YES or NO)? YES deleted datafile copy datafile copy file name=/u01/app/oracle/product/11.2.0/db_1/dbs/data RECID=5 STAMP=1085496166 Deleted 1 objects 14:46:00 SYS@test1(469)> select FILE# ,NAME from v$datafile_copy ; FILE# NAME ---------- ---------------------------------------------------------------------------------------------------- 17 17 17 17 17 Elapsed: 00:00:00.00 14:46:00 SYS@test1(469)> select file_id,file_name from dba_data_files where tablespace_name='ZKM'; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------------------------------------- 17 +DATA/test/datafile/zkm.304.1085496115 Elapsed: 00:00:00.00 14:58:09 SYS@test1(469)> recover datafile 17; Media recovery complete. 14:58:12 SYS@test1(469)> alter database datafile 17 online; Database altered. Elapsed: 00:00:00.02
不同之处在于使用了
switch datafile 17 to copy;
来命名新的17号文件的位置和文件名，并且和v$datafile_copy交换记录。
由于v$datafile_copy的记录变成了“/u01/app/oracle/product/11.2.0/db_1/dbs/data”，所以需要通过rman的
delete datafilecopy all;
来删除记录，并且同时物理删除/u01/app/oracle/product/11.2.0/db_1/dbs/data文件。
