Oracle通过Rman的"copy datafile"转移数据文件后不要使用sqlplus来重命名文件位置和文件名

2021-11-25 04:24:21

 

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

 

上一篇：Linux shell 知识心得10 三剑客之awk


下一篇：sed命令详解