重建控制文件出现ORA-01159、ORA-01517告警

denglj

问题描述：将windows上的数据文件、控制文件拷贝到linux相应目录后，重建控制文件出现ORA-01159、ORA-01517告警，如下所示：

源端：windows2003 32位 + oracle 10.2.0.4 32位

目标端：centos 7.964位+ oracle 11.2.0.4 64位

SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

  2     MAXLOGFILES 16

  3     MAXLOGMEMBERS 3

  4     MAXDATAFILES 100

  5     MAXINSTANCES 8

  6     MAXLOGHISTORY 292

  7 LOGFILE

  8   GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9   GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

10   GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

11  --STANDBY LOGFILE

12 DATAFILE

13   '/u01/app/oracle/oradata/orcl/SYSTEM01.DBF',

14   '/u01/app/oracle/oradata/orcl/UNDOTBS01.DBF',

15    '/u01/app/oracle/oradata/orcl/SYSAUX01.DBF',

16   '/u01/app/oracle/oradata/orcl/USERS01.DBF'

17 CHARACTER SET ZHS16GBK

18  ;

CREATECONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

*

ERROR atline 1:

ORA-01503:CREATE CONTROLFILE failed

ORA-01159:file is not from same database as previous files - wrong database id

ORA-01517:log member: '/u01/app/oracle/oradata/orcl/redo01.log'

异常原因：

createcontrolfile中指定的文件并非来自同一数据库.此场景中redo文件并没有从windows拷贝到linux，控制文件创建语句中的redo文件为此前linux上数据库的redo文件，所以造成此异常.

解决方案：

1、删除目标端redo文件

[root@liujunorcl]# ll

total1541164

drwxr-xr-x  2 root  root           163 Oct 29 21:51bak

-rw-r-----.1 oracle oinstall  52429312 Oct 29 10:34redo01.log

-rw-r-----.1 oracle oinstall  52429312 Oct 29 18:22redo02.log

-rw-r-----.1 oracle oinstall  52429312 Oct 29 18:05redo03.log

-rw-r--r--  1 oracle oinstall 251666432 Oct 29 18:34SYSAUX01.DBF

-rw-r--r--  1 oracle oinstall 524296192 Oct 29 18:34SYSTEM01.DBF

-rw-r--r--  1 oracle oinstall 639639552 Oct 29 18:34UNDOTBS01.DBF

-rw-r--r--  1 oracle oinstall   5251072 Oct 29 18:34 USERS01.DBF

[root@liujunorcl]# mv *.log bak

2、将源端windowsredo文件拷贝到目标端

sftp>lpwd

E:\

sftp>cd /u01/app/oracle/oradata/orcl

sftp>put *.log

UploadingREDO01.LOG to /u01/app/oracle/oradata/orcl/REDO01.LOG

  100% 51200KB 51200KB/s 00:00:00     

E:\REDO01.LOG:52429312 bytes transferred in 0 seconds (51200 KB/s)

UploadingREDO02.LOG to /u01/app/oracle/oradata/orcl/REDO02.LOG

  100% 51200KB 51200KB/s 00:00:00     

E:\REDO02.LOG:52429312 bytes transferred in 0 seconds (51200 KB/s)

UploadingREDO03.LOG to /u01/app/oracle/oradata/orcl/REDO03.LOG

  100% 51200KB 51200KB/s 00:00:00     

E:\REDO03.LOG:52429312 bytes transferred in 0 seconds (51200 KB/s)

[root@liujunorcl]# pwd

/u01/app/oracle/oradata/orcl

[root@liujunorcl]# chown oracle:oinstall *.LOG

[root@liujunorcl]# ll

total1541164

drwxr-xr-x2 root   root           217 Oct 29 21:52 bak

-rw-r--r--1 oracle oinstall  52429312 Oct 29 18:34REDO01.LOG

-rw-r--r--1 oracle oinstall  52429312 Oct 29 18:34REDO02.LOG

-rw-r--r--1 oracle oinstall  52429312 Oct 29 18:34REDO03.LOG

-rw-r--r--1 oracle oinstall 251666432 Oct 29 18:34 SYSAUX01.DBF

-rw-r--r--1 oracle oinstall 524296192 Oct 29 18:34 SYSTEM01.DBF

-rw-r--r--1 oracle oinstall 639639552 Oct 29 18:34 UNDOTBS01.DBF

-rw-r--r--1 oracle oinstall   5251072 Oct 29 18:34USERS01.DBF

重新创建控制文件.

SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

  2     MAXLOGFILES 16

  3     MAXLOGMEMBERS 3

  4     MAXDATAFILES 100

  5     MAXINSTANCES 8

  6     MAXLOGHISTORY 292

  7 LOGFILE

  8   GROUP 1 '/u01/app/oracle/oradata/orcl/REDO01.LOG' SIZE 50M BLOCKSIZE512,

  9   GROUP 2 '/u01/app/oracle/oradata/orcl/REDO02.LOG' SIZE 50M BLOCKSIZE512,

10   GROUP 3 '/u01/app/oracle/oradata/orcl/REDO03.LOG' SIZE 50M BLOCKSIZE 512

11  --STANDBY LOGFILE

12 DATAFILE

13   '/u01/app/oracle/oradata/orcl/SYSTEM01.DBF',

14   '/u01/app/oracle/oradata/orcl/UNDOTBS01.DBF',

15   '/u01/app/oracle/oradata/orcl/SYSAUX01.DBF',

16   '/u01/app/oracle/oradata/orcl/USERS01.DBF'

17 CHARACTER SET ZHS16GBK

18  ;

Controlfile created.

结论：如上所示，控制文件成功创建.