ORA-01152 时，recover database 到底起什么作用？

武汉-SSH

ORA-01152 时，控制文件里datafile scn 与 datafile_header scn 相同时，recover database 到底起什么作用？

数据库是归档模式
RMAN> backup database;
。。。。。。。。。。。。。
RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    535.20M    DISK        00:00:51     17-3月 -12
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20120317T215850
        Piece Name: /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/UTF8/backupset/2012_03_17/o1_mf_nnndf_TAG20120317T215850_7p964w5p_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  1       Full 621676     17-3月 -12 /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/system01.dbf
  2       Full 621676     17-3月 -12 /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/undotbs01.dbf
  3       Full 621676     17-3月 -12 /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/sysaux01.dbf
  4       Full 621676     17-3月 -12 /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    6.80M      DISK        00:00:00     17-3月 -12
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20120317T215850
        Piece Name: /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/UTF8/backupset/2012_03_17/o1_mf_ncsnf_TAG20120317T215850_7p966myv_.bkp
  Control File Included: Ckp SCN: 621695       Ckp time: 17-3月 -12
  SPFILE Included: Modification time: 17-3月 -12


RMAN> shutdown abort

Oracle instance shut down

删除全库
[root@testdb utf8]# rm control0* redo0* sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2020160 bytes
Variable Size                 88083648 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6365184 bytes

RMAN> restore controlfile from '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/UTF8/backupset/2012_03_17/o1_mf_ncsnf_TAG20120317T215850_7p966myv_.bkp';

Starting restore at 17-3月 -12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/control01.ctl
output filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/control02.ctl
output filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/control03.ctl
Finished restore at 17-3月 -12

RMAN> mount database;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 17-3月 -12
Starting implicit crosscheck backup at 17-3月 -12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 17-3月 -12

Starting implicit crosscheck copy at 17-3月 -12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-3月 -12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/UTF8/backupset/2012_03_17/o1_mf_ncsnf_TAG20120317T215850_7p966myv_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/db_1/oradata/utf8/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/UTF8/backupset/2012_03_17/o1_mf_nnndf_TAG20120317T215850_7p964w5p_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/UTF8/backupset/2012_03_17/o1_mf_nnndf_TAG20120317T215850_7p964w5p_.bkp tag=TAG20120317T215850
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-3月 -12


[root@testdb utf8]# cat check.sql
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$database;
select checkpoint_change# from v$datafile_header;

SQL> @check

CHECKPOINT_CHANGE#
------------------
            621676
            621676
            621676
            621676


CHECKPOINT_CHANGE#
------------------
            619047


CHECKPOINT_CHANGE#
------------------
            621676
            621676
            621676
            621676

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;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/ut                                                                                  f8/system01.dbf'

RMAN> recover database;

Starting recover at 17-3月 -12
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/17/2012 22:16:56
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 621676

SQL> alter database open RESETLOGS;

Database altered.

[ 本帖最后由 武汉-SSH 于 2012-3-17 23:33 编辑 ]

xifenfei

数据库的last scn不一致，你热备的时候，应该是null，所以需要恢复

Maclean Liu(刘相兵

[oracle@vrh8 ~]$ oerr ora 1152
01152, 00000, "file %s was not restored from a sufficiently old backup "
// *Cause:  An incomplete recovery session was started, but an insufficient
//         number of logs were applied to make the database consistent. This
//         file is still in the future of the last log applied. The most
//         likely cause of this error is forgetting to restore the file
//         from a backup before doing incomplete recovery.
// *Action: Either apply more logs until the database is consistent or
//         restore the database file from an older backup and repeat recovery.


The database was not shutdown when you created a copy of the control file.

If the database and/or OracleService<sid> service (<SID> is the SID (ORCL is
the default SID) of the database) is not shutdown before making a
copy of the control file then they will be out of synchronization and cause the
ORA-1152 on startup.  By shutting down the database and services you can be
assured that Oracle is not accessing the original control file and that the
copy will be an exact synchronized replica.  If the you forget to reference
the additional control file in the INIT<sid>.ORA file before starting the
database then the duplicate control file must be deleted and the run through
all the steps above again.

This error occurs when a file is ahead in time (SCN) than the stopping point of recovery.

武汉-SSH

dump controlf、file_hdrs 中control seq代表什么
Control Seq

orafans

刘老大，
学习了。

xteitxu

这个案例怎么去保证一致性呢？是不是在abort之前，多切几次归档，可以保证一致啊

武汉-SSH

恩，想了一下，这个案例，我原本想问的的是，恢复到什么程度才能满足 OPEN RESETLOGS 去打开数据库。

是不是就是v$datafile.checkpoint_change# = v$datafile_header.checkpoint_change# 呢？

如果是的话，RESTORE 完成以后就满足这个条件啊，为什么必须 RECOVER，而且 RECOVER 感觉什么也没做啊？

所以，我才问 RECOVER 在这里到底起什么作用？

aigo_h

RECOVER 应用redo log.

ORA-01152: file 1 was not restored from a sufficiently old backup

lingzhiluoye

RECOVER 应用redo log.