诗檀软件专业数据库修复团队
ORA-01167
oerr ora 1167
01167, 00000, "two files are the same file/group number or the same file"
// *Cause: There is an overlap of file numbers in the files specified on
// the command line or the same file is specified twice. If they are
// not the exact same file then one is likely to be a backup of the
// other. If they are two members of the same log they must be specified
// together in a group file spec. This message will also appear if
// the same control file appears more than once in the control_files
// parameter in the init.ora file. If this happens, check for
// additional error messages.
// *Action: Confirm that the file mentioned is not a repeat of a file already
// mentioned in the command. If they are different files then omit the
// earlier backup. If they are members of the same log, insure they are
// in the same group file specification. If this message appears because
// of a duplicate control file, check the control_files parameter in the
// init.ora file and see if a file is specified more than once.
// If all files names appear to be unique, check to make sure that the
// actual control files themselves are unique. For example, in UNIX
// check for a symbolic or a hard link to another control file in the list.
症状
当尝试使用数据库的操作系统副本创建一个新的控制文件时，收到错误：
CREATE CONTROLFILE set DATABASE hhr2 RESETLOGS NOARCHIVELOG
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01167: two files are the same file/group number or the same file
ORA-01110: data file 504: '/oravol60/dbloc/oravol53/hhp2_data_ework_a'
ORA-01110: data file 504: '/oravol59/dbloc/oravol54/hhr2/hhp2_data_xxxl_04c'
原因
在ORA-1110 错误中提到的两个数据文件有重复文件号，这是无效的情况。
解决方案
在源数据库上创建控制文件的二进制备份：
alter database backup controlfile to '<new controlfile filename>';
将新的备份控制文件复制到新的服务器，然后将在错误中列出的两个文件分类。
在新服务器上，mount新的备份控制文件，然后：
RMAN> catalog datafilecopy '/oravol60/dbloc/oravol53/hhp2_data_ework_a';
RMAN> catalog datafilecopy '/oravol59/dbloc/oravol54/hhr2/hhp2_data_xxxl_04c';
然后我们可以列出数据文件的副本：
RMAN> list copy of database;
使用目标数据库控制文件代替恢复目录
数据文件副本的列表
键 文件 S 完成时间 Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
286 504 A 21-DEC-2007 33162314637 26-JAN-2007
/oravol59/dbloc/oravol54/hhr2/hhp2_data_xxxl_04c
285 504 A 21-DEC-2007 9683156967 29-NOV-2004
/oravol60/dbloc/oravol53/hhp2_data_ework_a
一个数据文件是29-NOV-2004 oravol60/dbloc/oravol53/hhp2_data_ework_a
还有一个是 26-JAN-2007 /oravol59/dbloc/oravol54/hhr2/hhp2_data_xxxl_04c
这是无效的情况，每个数据文件都需要有自己的文件号。
在将数据库从源数据库重新复制到新服务器后，创建控制文件成功，数据文件显示从2004的检查点，因此数据文件504肯定在之前的复制时损坏。