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

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

源端：windows 2003 32位 + oracle 10.2.0.4 32位

目标端：centos 7.9 64位 + 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  ;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

*

ERROR at line 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'

 

异常原因：

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

解决方案：

1、删除目标端redo文件

[root@liujun orcl]# ll

total 1541164

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

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

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

-rw-r-----. 1 oracle oinstall  52429312 Oct 29 18:05 redo03.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:34 USERS01.DBF

[root@liujun orcl]# mv *.log bak

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

sftp> lpwd

E:\

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

sftp> put *.log

Uploading REDO01.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)

Uploading REDO02.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)

Uploading REDO03.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@liujun orcl]# pwd

/u01/app/oracle/oradata/orcl

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

[root@liujun orcl]# ll

total 1541164

drwxr-xr-x 2 root   root           217 Oct 29 21:52 bak

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

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

-rw-r--r-- 1 oracle oinstall  52429312 Oct 29 18:34 REDO03.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:34 USERS01.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 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  ;

Control file created.

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