oracle实验中的错误,【案例】Oracle报错ORA-01190的产生原因和2种官方解决办法

【案例】Oracle报错ORA-01190的产生原因和2种官方解决办法

时间:2016-10-21 19:34   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

运维DBA反映生产环境数据库在宕机后数据文件的SCN不一致并报错ORA-01190.总结出两种方法来修复数据文件头SCN不一致的方法。

ORA-01190这个错误比较常见.特别在没有正规DBA运维的环境中比较常见.

先看看ORACLE官方是怎么解决这个报错的

SQL> !oerr ora 01190

01190, 00000, “control file or data file %s is from before the last RESETLOGS”

// *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.

1.数据库的版本

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 – Production

NLSRTL Version 11.2.0.4.0 – Production

2.现象模拟

这里我们通过使用online一个数据文件后.使用open resetlogs方式来打开数据库.这样offline的数据文件就成功resetlogs之间的数据文件.实验环境要求数据库运行在归档模式.其它非归档模式也是一样的.只是需要再侯scn的值

SQL> drop tablespace htz including contents and datafiles;

Tablespace dropped.

SQL> select name from v$dbfile where rownum=1;

NAME

——————————————————————————–

/oracle/app/oracle/oradata/orcl1124/users01.dbf

SQL> create tablespace htz datafile ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’ size 10m autoextend on maxsize 10G;

Tablespace created.

SQL> create table scott.htz tablespace htz as select * from dba_objects;

Table created.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 379965440 bytes

Fixed Size 2253464 bytes

Variable Size 171969896 bytes

Database Buffers 201326592 bytes

Redo Buffers 4415488 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> !rm /tmp/control.txt

SQL> alter database backup controlfile to trace as ‘/tmp/control.txt’;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 379965440 bytes

Fixed Size 2253464 bytes

Variable Size 171969896 bytes

Database Buffers 201326592 bytes

Redo Buffers 4415488 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE “ORCL1124” RESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 ‘/oracle/app/oracle/oradata/orcl1124/redo01.log’ SIZE 50M BLOCKSIZE 512,

9 GROUP 2 ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ SIZE 50M BLOCKSIZE 512,

10 GROUP 3 ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ SIZE 50M BLOCKSIZE 512

11 — STANDBY LOGFILE

12 DATAFILE

13 ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’,

14 ‘/oracle/app/oracle/oradata/orcl1124/sysaux01.dbf’,

15 ‘/oracle/app/oracle/oradata/orcl1124/undotbs01.dbf’,

16 ‘/oracle/app/oracle/oradata/orcl1124/users01.dbf’,

17 ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’

18 CHARACTER SET ZHS16GBK

19 ;

Control file created.

SQL> set lines 200

SQL> col name for a60

SQL> select * from v$dbfile;

FILE# NAME

———- ————————————————————

5 /oracle/app/oracle/oradata/orcl1124/htz01.dbf

4 /oracle/app/oracle/oradata/orcl1124/users01.dbf

3 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf

2 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf

1 /oracle/app/oracle/oradata/orcl1124/system01.dbf

SQL> alter database datafile 5 offline;

Database altered.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1277201 generated at 04/22/2014 18:06:17 needed for thread 1

ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_90_%u_.arc

ORA-00280: change 1277201 for thread 1 is in sequence #90

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Databas