本文共 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
5
MAXLOGMEMBERS 3
MAXDATAFILES
100
MAXINSTANCES
1
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>
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
5
MAXLOGMEMBERS 3
MAXDATAFILES
100
MAXINSTANCES
1
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
5
MAXLOGMEMBERS 3
MAXDATAFILES
100
MAXINSTANCES
1
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(*)
----------
5
----到此恢复成功。
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 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!