oracle 错误01190,ORA-01189及 ORA-01190错误的模拟及解决-白红宇的个人博客

oracle 错误01190,ORA-01189及 ORA-01190错误的模拟及解决

发布日期：2021-06-24 14:05:37 浏览次数：2 分类：技术文章

本文共 8907 字，大约阅读时间需要 29 分钟。

ORA-01189及 ORA-01190错误的模拟及解决

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host mv /data/oracle/oradata/test/test_reset01.dbf

/data/oracle/oradata/test/test_reset01.dbf.bak

---模拟数据文件被删除。

SQL> host ls -l /data/oracle/oradata/test/

total 805196

drwxr-sr-x 2 oracle

dba 4096 Sep 8 09:46 arch

-rw-r----- 1 oracle

dba 1875968

Sep 8 14:15 control01.ctl

-rw-r----- 1 oracle

dba 1875968

Sep 8 14:15 control02.ctl

-rw-r----- 1 oracle

dba 1875968

Sep 8 14:15 control03.ctl

-rw-r----- 1 oracle dba 104858112

Sep 8 14:15 redo01.log

-rw-r----- 1 oracle dba 104858112

Sep 8 09:46 redo02.log

-rw-r----- 1 oracle dba 104858112

Sep 8 09:46 redo03.log

-rw-r----- 1 oracle dba 283123712

Sep 8 14:15 system01.dbf

-rw-r----- 1 oracle dba 41951232 Sep 7 14:30 temp01.dbf

-rw-r----- 1 oracle dba 10493952 Sep 8 14:15 test_reset01.dbf.bak

-rw-r----- 1 oracle dba 209723392

Sep 8 14:15 undotbs01.dbf

SQL> STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST"

NORESETLOGS ARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES

MAXLOGMEMBERS 3

MAXDATAFILES

100

MAXINSTANCES

MAXLOGHISTORY 226

LOGFILE

GROUP 1

'/data/oracle/oradata/test/redo01.log' SIZE

100M,

GROUP 2

'/data/oracle/oradata/test/redo02.log' SIZE

100M,

GROUP 3

'/data/oracle/oradata/test/redo03.log' SIZE

100M

-- STANDBY LOGFILE

DATAFILE

'/data/oracle/oradata/test/system01.dbf',

'/data/oracle/oradata/test/undotbs01.dbf'

CHARACTER SET WE8ISO8859P1

;ORACLE instance started.

----重建控制文件，但里面不存在被删除数据文件的信息。

----模拟控制文件是数据文件创建之前备份的。

Total System Global Area 353441008 bytes

Fixed

Size 451824 bytes

Variable

Size 134217728 bytes

Database

Buffers 218103808 bytes

Redo

Buffers 667648 bytes

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Control file created.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/data/oracle/oradata/test/system01.dbf

/data/oracle/oradata/test/undotbs01.dbf

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database

recovery

---模拟以resetlogs打开数据库。

SQL> recover database using backup controlfile;

ORA-00279: change 141094 generated at 09/08/2006 14:15:16 needed

for thread 1

ORA-00289: suggestion :

/data/oracle/oradata/test/arch/test_10.arc

ORA-00280: change 141094 for thread 1 is in sequence #10

---模拟以resetlogs打开数据库。

Specify log: {=suggested | filename | AUTO |

CANCEL}

/data/oracle/oradata/test/redo01.log ---输入online redo log。

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

----OK，resetlogs打开数据库成功。

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/data/oracle/oradata/test/system01.dbf

/data/oracle/oradata/test/undotbs01.dbf

/data/oracle/product/9.2.0/dbs/MISSING00003

----看到没，多出来了missing file。

SQL> alter database rename file

'/data/oracle/product/9.2.0/dbs/MISSING00003' to

'/data/oracle/oradata/test/test_reset01.dbf.bak';

Database altered.

---注意：这里必须是正确的OS上存在的数据文件，因为在rename的时候Oracle会去读数据文件头。

---假如不存在此文件，则rename会报错。

SQL> recover datafile 3;

ORA-00283: recovery session canceled due to errors

ORA-01190: controlfile or data file 3 is from before the last

RESETLOGS

ORA-01110: data file 3:

'/data/oracle/oradata/test/test_reset01.dbf.bak'

----哈哈，终于看到了ORA-01190

SQL>

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

----模拟open数据库后，又进行了若干操作。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST"

NORESETLOGS ARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES

MAXLOGMEMBERS 3

MAXDATAFILES

100

MAXINSTANCES

MAXLOGHISTORY 226

LOGFILE

GROUP 1

'/data/oracle/oradata/test/redo01.log' SIZE

100M,

GROUP 2

'/data/oracle/oradata/test/redo02.log' SIZE

100M,

GROUP 3

'/data/oracle/oradata/test/redo03.log' SIZE

100M

-- STANDBY LOGFILE

DATAFILE

'/data/oracle/oradata/test/system01.dbf',

'/data/oracle/oradata/test/undotbs01.dbf',

'/data/oracle/oradata/test/test_reset01.dbf'

---文件名错误。

CHARACTER SET WE8ISO8859P1

;

ORACLE instance started.

Total System Global Area 353441008

bytes

Fixed

Size 451824 bytes

Variable

Size 134217728 bytes

Database

Buffers 218103808 bytes

Redo

Buffers 667648 bytes

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE

CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01565: error in identifying file

'/data/oracle/oradata/test/test_reset01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST"

NORESETLOGS ARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES

MAXLOGMEMBERS 3

MAXDATAFILES

100

MAXINSTANCES

MAXLOGHISTORY 226

LOGFILE

GROUP 1

'/data/oracle/oradata/test/redo01.log' SIZE

100M,

GROUP 2

'/data/oracle/oradata/test/redo02.log' SIZE

100M,

GROUP 3

'/data/oracle/oradata/test/redo03.log' SIZE

100M

-- STANDBY LOGFILE

DATAFILE

'/data/oracle/oradata/test/system01.dbf',

'/data/oracle/oradata/test/undotbs01.dbf',

'/data/oracle/oradata/test/test_reset01.dbf.bak'

CHARACTER SET WE8ISO8859P1

;ORACLE instance started.

Total System Global Area 353441008

bytes

Fixed

Size 451824 bytes

Variable

Size 134217728 bytes

Database

Buffers 218103808 bytes

Redo

Buffers 667648 bytes

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE CONTROLFILE REUSE DATABASE "TEST"

NORESETLOGS ARCHIVELOG

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01189: file is from a different RESETLOGS than previous

files

ORA-01110: data file 3:

'/data/oracle/oradata/test/test_reset01.dbf.bak'

---哈哈，ORA-01189 错误也出现了。

----下面是解决方法：

1、首先需要设置_allow_resetlogs_corruption参数

2、用ADJUST_SCN来调整SCN

关于“用ADJUST_SCN来调整SCN”，信息如下：

增进SCN有两种常用方法:

1.通过immediate trace name方式(在数据库Open状态下)

alter session set events 'IMMEDIATE trace name ADJUST_SCN level

x';

2.通过10015事件(在数据库无法打开，mount状态下)

alter session set events '10015 trace name adjust_scn level

x';

注:level 1为增进SCN 10亿 (1 billion)

(1024*1024*1024),通常Level

1已经足够。也可以根据实际情况适当调整。

SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount pfile='/tmp/pfile.ora'

---增加 *._allow_resetlogs_corruption='TRUE'

参数，以pfile方式启动。

ORACLE instance started.

Total System Global Area 353441008

bytes

Fixed

Size 451824 bytes

Variable

Size 134217728 bytes

Database

Buffers 218103808 bytes

Redo

Buffers 667648 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME

ADJUST_SCN LEVEL 1';

Session altered.

alert.log中的信息：

Fri Sep 8 14:33:24 2006

Debugging event used to advance scn to 1073741824

---此应该是在重起数据库后生效。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount pfile='/tmp/pfile.ora'

ORACLE instance started.

Total System Global Area 353441008

bytes

Fixed

Size 451824 bytes

Variable

Size 134217728 bytes

Database

Buffers 218103808 bytes

Redo

Buffers 667648 bytes

Database mounted.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_

---------- ------- -------

ERROR CHANGE#

-----------------------------------------------------------------

----------

TIME

---------

3 OFFLINE OFFLINE

UNKNOWN

ERROR 141094

SQL> recover until cancel;

Media recovery complete.

---上面这一步很重要，虽然不做这个操作也能打开数据库，但是我们是要用RESETLOGS来打开数据库，否则仍然将其它数据文件联机的时候仍然会报ORA-01189.

SQL> alter database datafile 3 online;

Database altered.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database

open

SQL> alter database open resetlogs;

Database altered.

SQL> select name,status from v$datafile;

NAME

--------------------------------------------------------------------------------

STATUS

-------

/data/oracle/oradata/test/system01.dbf

SYSTEM

/data/oracle/oradata/test/undotbs01.dbf

ONLINE

/data/oracle/oradata/test/test_reset01.dbf.bak

ONLINE

SQL> select count(*) from test_reset;

COUNT(*)

----------

----到此恢复成功。

SQL> select

PRIOR_RESETLOGS_CHANGE#,RESETLOGS_CHANGE#,RESETLOGS_CHANGE# -

PRIOR_RESETLOGS_CHANGE#

2 from v$database;

PRIOR_RESETLOGS_CHANGE# RESETLOGS_CHANGE#

----------------------- -----------------

RESETLOGS_CHANGE#-PRIOR_RESETLOGS_CHANGE#

-----------------------------------------

141096 1073741832

1073600736

SQL> select CREATION_CHANGE#,CHECKPOINT_CHANGE#

,UNRECOVERABLE_CHANGE#,OFFLINE_CHANGE#

2 from v$datafile;

CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE#

OFFLINE_CHANGE#

---------------- ------------------ ---------------------

---------------

4 1073741833 0 1073741831

5066 1073741833 0 1073741831

113698 1073741833 0 1073741831

----由此可见：

----ADJUST_SCN不仅会增加数据文件的scn，而且连数据库的resetlogs

scn也会增进。

----这也是这里为什么可以恢复成功的原因。

转载地址：https://blog.csdn.net/weixin_33308985/article/details/116515662 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：linux进程树查看,linux：使用pstree和tree查看进程树和目录树

下一篇：oracle block media recovery,Oracle OCP 1Z0 053 Q401(Block Media Recovery)

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章