ORA-01190

ORA-01190: control file or data file string is from before the last RESE

Cause: Attempting to use a data file when the log reset information in the file does not match the control file. Either the data file or the control file is a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS.

Action: Restore file from a more recent backup.

 

备份和恢复ORA-01190错误处理

Author: Rainny
Date: 2005-03-11

ORA-01190是指还原回去的控制文件和数据文件不一致(SCN对不上).我们先来归纳一下当控制文件和数据
文件不一致时,ORACLE所要进行的工作:

1. 首先,需要介质恢复.也就是说需要应用重做日志的更改到数据文件,直到控制文件和数据文件同步;
2. 如果重做日志丢失,我们可以试图用RESETLOGS方式来打开数据库,如果这个失败,则会报ORA-01190.

解决ORA-01190的方法就是修改隐含参数_allow_resetlogs_corruption,将其值设为TRUE,这就意味着可以
允许控制文件和数据文件不一致,强制用RESETLOGS方式打开数据库:
(1)首先创建PFILE
create pfile='f:\initrainny.ora' from spfile;
(2)在pfile中增加隐含参数: _allow_resetlogs_corruption='TURE'
___________________________________________________________________________
*.aq_tm_processes=1
*.background_dump_dest='D:\oracle_home\adminrainnybdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle_home\oradatarainnyCONTROL01.CTL','D:\oracle_home\oradatarainny\CONTROL02.CTL','D:\oracle_home\oradatarainny\CONTROL03.CTL'
*.core_dump_dest='D:\oracle_home\adminrainnycdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='rainny'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rainnyXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='rainny'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_start=FALSE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='D:\oracle_home\adminrainnyudump'
=================================
*._allow_resetlogs_corruption='TURE'
=================================
----------------------------------------------------------------------------------

SQL>shutdown immediate;

(3)以PFILE启动数据库

SQL>startup pfile='f:\initrainny.ora';

(4)以resetlogs方式打开数据库

SQL>alter database open resetlogs;
SQL> show parameter _allow_resetlogs

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE

注意,如果以resetlogs方式打后数据库后没有备份数据库,而数据库在RESETLOGS打开之后又发生数据文件丢失,则此时,我们只有使用RESETLOGS之前的数据库备份来恢复数据库.

1. 我们首先要确定拥有的备份是在哪个INCARNATION之后备份的:

_________________________________________________
RMAN> LIST BACKUP BY FILE;

List of Datafile Backups

========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag
---- ------- - -- - ---------- --------- ------- ------- ---
1 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
3 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
4 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
5 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
6 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
7 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
8 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
9 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
10 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
11 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
12 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
13 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308
14 2646 B F A 35602270 17-JAN-07 1 1 TAG20070117T094308

List of Controlfile Backups
===========================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag
---------- --------- ------- - ------- ------- ---
35602696 17-JAN-07 2781 A 1 1
35602474 17-JAN-07 2732 A 1 1

List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Tag
----------------- ------- - ------- ------- ---
17-JAN-07 2732 A 1 1
____________________________________________________

通过LIST BACKUP,我们看到数据库备份的CHECKPOINT SCN 是: 35602270,
而控制文件备份中没有一个的checkpoin scn是: 35602270,也就是不存在控制文件和数据文件完全一致的
完整备份.
我们再来看数据库的incarnation:

______________________________________
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 17 RAINNY 327571156 NO 190578 04-JUL-06
1 18 RAINNY 327571156 NO 31958084 18-DEC-06
1 19 RAINNY 327571156 NO 32226461 21-DEC-06
1 2 RAINNY 327571156 NO 32268855 21-DEC-06
1 74 RAINNY 327571156 NO 32323102 21-DEC-06
1 247 RAINNY 327571156 NO 32493939 25-DEC-06
1 298 RAINNY 327571156 NO 32516218 25-DEC-06
1 358 RAINNY 327571156 NO 32517288 25-DEC-06
1 400 RAINNY 327571156 NO 32530668 25-DEC-06
1 454 RAINNY 327571156 NO 32531405 25-DEC-06
1 546 RAINNY 327571156 NO 32536878 25-DEC-06
1 637 RAINNY 327571156 NO 32567229 26-DEC-06
1 776 RAINNY 327571156 NO 32637405 26-DEC-06
1 1362 RAINNY 327571156 NO 33906024 03-JAN-07
1 1958 RAINNY 327571156 NO 35014299 12-JAN-07
1 2330 RAINNY 327571156 NO 35563685 16-JAN-07
1 2556 RAINNY 327571156 NO 35567345 16-JAN-07
1 2623 RAINNY 327571156 NO 35600812 17-JAN-07
1 2679 RAINNY 327571156 NO 35602271 17-JAN-07
1 2717 RAINNY 327571156 NO 35602272 17-JAN-07
1 2751 RAINNY 327571156 NO 35602272 17-JAN-07
1 2766 RAINNY 327571156 YES 35602495 17-JAN-07
_________________________________________________________________________

通过对比,我们可以看出数据文件的备份的CHECKPOINT SCN 35602270是介于两个INCARNATION的
CHECKPOINT SCN: 35600812和35602271之间,也就是说数据文件是在INCARNATION 2623之后, 2679之前备
份的,所以我们要将数据库重置到incarnation 2623:

RMAN> reset database to incarnation 2623;
database reset to incarnation 2623 in recovery catalog
RMAN>

然后关闭数据库,启动到NOMOUNT,开始还原控制文件(本例中由于不存在与数据文件备份完全一致的控制文
件备份,所以我们还原一个与数据文件备份最接近的一个控制文件备来还原):

RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
RMAN> restore controlfile from 'F:zq_backupctl_autobackupCTL_RAINNY_20070117_
C-327571156-20070117-02.BAK';
Starting restore at 17-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:ORACLE_HOMEORADATARAINNYCONTROL01.CTL
output filename=D:ORACLE_HOMEORADATARAINNYCONTROL02.CTL
output filename=D:ORACLE_HOMEORADATARAINNYCONTROL03.CTL
Finished restore at 17-JAN-07
RMAN>

还原控制文件后,MOUNT数据库,然后RESTORE数据库,RESTORE完成后,由于控制文件和数据文件不一致,所以
会报ORA-01190,按前面我所描述的方法来处理这个ORA-01190.

从中,我们发现,用RESETLOGS打开数据库后(还原控制文件或不一致RECOVER后,都要求用RESETLOGS方式打
开数据库),一定要记得备份数据库.