ORA-01190、ORA-01110 alter tablespace USERS online _allow_resetlogs_corruption

提示错误如下：

 SQL> alter tablespace USERS online;

alter tablespace USERS online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the las
ORA-01110: data file 4: 'D:\ORACLEDB\SEINEEBS\SEINEEBSDATA_DATA01.DBF'

ORA-01190:控制文件或数据文件5来自于最后一个 RESETLOGS 之前

 原因：

由于V$DATAFILE中的CHECKPOINT_CHANGE#仍然大于V$RECOVER_FILE中的CHANGE#，决定用ADJUST_SCN来调整SCN.

 

 

解决方法：

1.首先需要设置_allow_resetlogs_corruption参数，否则不能成功
SQLPLUS>ALTER SYSTEM SET "_allow_resetlogs_corruption"=TRUE SCOPE=SPFILE;

2.SQLPLUS>SHUTDOWN IMMEDIATE

3.SQLPLUS>STARTUP MOUNT;

4.SQLPLUS>ALTER DATABASE OPEN;

5.SQLPLUS>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ADJUST_SCN LEVEL 1';
会话已更改。

6.SQLPLUS>SHUTDOWN IMMEDIATE

7.SQLPLUS>STARTUP MOUNT;
RACLE 例程已经启动。

 

 

 

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.5788E+10 bytes
Fixed Size 2267264 bytes
Variable Size 1.2348E+10 bytes
Database Buffers 1.3422E+10 bytes
Redo Buffers 16293888 bytes
Database mounted.
SQL> SELECT TS#,FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE;

TS# FILE# NAME STATUS CHECKPOINT_CHANGE#
------- ------------------
0 1 D:\ORACLEDB\SEINEEBS\SYSTEM01.DBF SYSTEM 9995685537

1 2 D:\ORACLEDB\SEINEEBS\SYSAUX01.DBF OFFLINE 0

2 3 D:\ORACLEDB\SEINEEBS\UNDOTBS01.DBF ONLINE 9995685537

4 4 D:\ORACLEDB\SEINEEBS\USERS01.DBF OFFLINE 0

6 5 D:\ORACLEDB\SEINEEBS\SEINEEBSDATA_DATA01.DBF OFFLINE 0

7 6 D:\ORACLEDB\SEINEEBS\SEINEEBSBASEDATA_DATA01.DBF OFFLINE 0

6 rows selected.

SQL> SELECT * FROM V$RECOVER_FILE;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
2 OFFLINE OFFLINE UNKNOWN ERROR 9995500935 13-SEP-15
4 OFFLINE OFFLINE UNKNOWN ERROR 9995500935 13-SEP-15
5 OFFLINE OFFLINE UNKNOWN ERROR 9995500935 13-SEP-15
6 OFFLINE OFFLINE UNKNOWN ERROR 9995500935 13-SEP-15

 

SQL> RECOVER UNTIL CANCEL
Media recovery complete.完成介质恢复。
虽然不做这个操作也能打开数据库，但是是要用RESETLOGS来打开数据库，否则仍然将其它数据文件联机的时候
仍然会报ORA-01189.然后将数据文件状态联机。

SQL> ALTER DATABASE DATAFILE 2,4,5,6 ONLINE;
Database altered.数据库已更改

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.打开数据库。

可执行以下语句查看，文件状态现在已经显示是ONLINEB
SELECT TS#,FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE;
至此，数据库已经恢复了，

临时表空间文件找回
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\oracledb\seineebs\TEMP02.DBF' REUSE;

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\oracledb\seineebs\TEMP02.DBF' REUSE
*
ERROR at line 1:
ORA-01537: cannot add file 'D:\oracledb\seineebs\TEMP02.DBF' - file already
part of database

将UNDO管理方式改成自动
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 2.5788E+10 bytes
Fixed Size 2267264 bytes
Variable Size 1.2348E+10 bytes
Database Buffers 1.3422E+10 bytes
Redo Buffers 16293888 bytes
Database mounted.
Database opened.