重庆思庄Oracle、Redhat认证学习论坛

标题: 重建控制文件出现ORA-01159、ORA-01517告警 [打印本页]

作者: denglj 时间: 2022-11-2 10:39
标题: 重建控制文件出现ORA-01159、ORA-01517告警

问题描述：将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.

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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)