请教一个resetlog的问题，请高手不吝赐教！

最新推荐文章于 2024-10-12 22:35:37 发布

congtaijian0536

最新推荐文章于 2024-10-12 22:35:37 发布

阅读量171

点赞数

文章标签：数据库

今天测试表空间offline immediate后用resetlogs的方式重建控制文件的时候发现一个问题，表空间offline immediate后，由于没有执行检查点，所以对应数据文件是需要恢复的，reset方式重建控制文件打开数据库后该数据文件不能恢复。报ora-19909错误。下面是详细过程

数据文件如下：

-rw-r----- 1 oracle oinstall 262152192 7月 9 17:17 sysaux01.dbf

-rw-r----- 1 oracle oinstall 524296192 7月 9 17:17 system01.dbf

-rw-r----- 1 oracle oinstall 125837312 7月 8 21:02 temp01.dbf

-rw-r----- 1 oracle oinstall 346038272 7月 9 17:17 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 7月 9 17:17 users01.dbf

sys@ORCL> create tablespace tbs01 datafile '/oracle/app/oradata/orcl/tbs01.dbf' size 1m;

Tablespace created.

sys@ORCL> alter tablespace tbs01 offline immediate;

Tablespace altered.

sys@ORCL> col name format a40

sys@ORCL> select file#, name, checkpoint_change#, last_change#, offline_change#, creation_change#,status, enabled from v$datafile;

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS ENABLED

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

1 /oracle/app/oradata/orcl/system01.dbf 2091192 2091190 9 SYSTEM READ WRITE

2 /oracle/app/oradata/orcl/undotbs01.dbf 2091192 2091190 444079 ONLINE READ WRITE

3 /oracle/app/oradata/orcl/sysaux01.dbf 2091192 2091190 6609 ONLINE READ WRITE

4 /oracle/app/oradata/orcl/users01.dbf 2091192 2091190 10566 ONLINE DISABLED

5 /oracle/app/oradata/orcl/example01.dbf 2091192 2091190 467467 ONLINE READ WRITE

6 /oracle/app/oradata/orcl/tbs01.dbf 2091572 2091606 0 2091571 RECOVER DISABLED

6 rows selected.

--这里last_change#为2091606。

sys@ORCL> alter system switch logfile;

System altered.

sys@ORCL> /

System altered.

sys@ORCL> /

System altered.

sys@ORCL> select file#, name, checkpoint_change#, last_change#, offline_change#, creation_change#,status, enabled from v$datafile;

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS ENABLED

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

1 /oracle/app/oradata/orcl/system01.dbf 2091633 2091190 9 SYSTEM READ WRITE

2 /oracle/app/oradata/orcl/undotbs01.dbf 2091633 2091190 444079 ONLINE READ WRITE

3 /oracle/app/oradata/orcl/sysaux01.dbf 2091633 2091190 6609 ONLINE READ WRITE

4 /oracle/app/oradata/orcl/users01.dbf 2091633 2091190 10566 ONLINE DISABLED

5 /oracle/app/oradata/orcl/example01.dbf 2091633 2091190 467467 ONLINE READ WRITE

6 /oracle/app/oradata/orcl/tbs01.dbf 2091572 2091606 0 2091571 RECOVER DISABLED

6 rows selected.

sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORCL> startup nomount

ORACLE instance started.

Total System Global Area 83886080 bytes

Fixed Size 1265912 bytes

Variable Size 62918408 bytes

Database Buffers 16777216 bytes

Redo Buffers 2924544 bytes

sys@ORCL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 4 '/oracle/app/oradata/orcl/redo04.log' SIZE 10M,

9 GROUP 5 '/oracle/app/oradata/orcl/reco05.log' SIZE 10M

10 -- STANDBY LOGFILE

11 DATAFILE

12 '/oracle/app/oradata/orcl/system01.dbf',

13 '/oracle/app/oradata/orcl/undotbs01.dbf',

14 '/oracle/app/oradata/orcl/sysaux01.dbf',

15 '/oracle/app/oradata/orcl/users01.dbf',

16 '/oracle/app/oradata/orcl/example01.dbf'

17 CHARACTER SET ZHS16GBK

18 ;

Control file created.

sys@ORCL> alter database open resetlogs;

Database altered.

sys@ORCL> select file#, name, checkpoint_change#, last_change#, offline_change#, creation_change#,status, enabled from v$datafile;

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS ENABLED

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

1 /oracle/app/oradata/orcl/system01.dbf 2091942 2091940 9 SYSTEM READ WRITE

2 /oracle/app/oradata/orcl/undotbs01.dbf 2091942 2091940 444079 ONLINE READ WRITE

3 /oracle/app/oradata/orcl/sysaux01.dbf 2091942 2091940 6609 ONLINE READ WRITE

4 /oracle/app/oradata/orcl/users01.dbf 2091942 2091940 10566 ONLINE DISABLED

5 /oracle/app/oradata/orcl/example01.dbf 2091942 2091940 467467 ONLINE READ WRITE

6 /oracle/app/product/10.2.0/db_1/dbs/MISS 0 2091981 0 2091571 RECOVER UNKNOWN

ING00006

6 rows selected.

sys@ORCL> alter database rename file 'MISSING00006' to '/oracle/app/oradata/orcl/tbs01.dbf';