Oracle为什么使用备份的控制文件恢复后一定要resetlogs

SQL> alter database open RESETLOGS;

因为，控制文件不是最新的，打开到mount状态后，这时可以查寻select * from v$log，但是v$log.status和v$log.SEQUENCE#不一定是准确的,控制文件中当前在线日志序列号还是陈旧的,是当初备份时的，而控制文件备份后online redo log可能进行了多次切换， 因为此时数据库还是mount状态，就算recover日志后，无法把recover的改动信息写入控制文件，这样控制文件中当前在线日志序列号还是当初备份时刻的，若按常规方式打开，会报错。所以只要是控制文件是恢复或重建过来的，oracle一律采用RESETLOGS重设日志功能，日志序列号从1重新开始，虽然使用resetlogs，但是recover命令成功执行已经提交的事务事务是不会丢失。

SQL> startup

ORACLE instance started.

Total System Global Area 1272213504 bytes

Fixed Size 1344680 bytes

Variable Size 838863704 bytes

Database Buffers 419430400 bytes

Redo Buffers 12574720 bytes

Database mounted.

Database opened.

SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;

GROUP#    THREAD#  SEQUENCE#    STATUS            

---

     1          1         31   INACTIVE              

     2          1         32   CURRENT             

     3          1         30   INACTIVE 

SQL> alter database backup controlfile to ‘/home/oracle/control’;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> ! ls /u01/app/oracle/oradata/OCP/control*

/u01/app/oracle/oradata/OCP/control01.ctl

/u01/app/oracle/oradata/OCP/control02.ctl

SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;

GROUP#    THREAD#  SEQUENCE#   STATUS   

---

     1          1         31   INACTIVE              

     2          1         32   ACTIVE                

     3          1         33  CURRENT 

SQL> ! rm -f /u01/app/oracle/oradata/OCP/control*

SQL> ! ls /u01/app/oracle/oradata/OCP/control*

ls: /u01/app/oracle/oradata/orcl/control*: No such file or directory

SQL> startup force

ORACLE instance started.

Total System Global Area 1272213504 bytes

Fixed Size 1344680 bytes

Variable Size 838863704 bytes

Database Buffers 419430400 bytes

Redo Buffers 12574720 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL> ! cp /home/oracle/control /u01/app/oracle/oradata/OCP/control01.ctl

SQL> ! cp /home/oracle/control /u01/app/oracle/oradata/OCP/control02.ctl

SQL> alter database mount;

Database altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;

GROUP#    THREAD#  SEQUENCE#   STATUS     

---

     1          1         31  INACTIVE               

     3          1         30  INACTIVE

     2          1         32  CURRENT 

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: ‘/u01/app/oracle/oradata/OCP/system01.dbf’

SQL> recover database using backup controlfile;

ORA-00279: change 1260345 generated at 05/12/2018 05:12:54 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/1_32_970255476.dbf

ORA-00280: change 1260345 for thread 1 is in sequence #32

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

/u01/app/oracle/oradata/OCP/redo02.log

ORA-00279: change 1261322 generated at 05/12/2018 05:13:46 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/1_33_970255476.dbf

ORA-00280: change 1261322 for thread 1 is in sequence #33

ORA-00278: log file ‘/u01/app/oracle/oradata/OCP/redo02.log’ no longer needed

for this recovery

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

/u01/app/oracle/oradata/orcl/redo03.log

Log applied.

Media recovery complete.

SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;

GROUP#    THREAD#  SEQUENCE# STATUS

---

     1          1         31 INACTIVE

     3          1         30 INACTIVE

     2          1         32 CURRENT

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;

Database altered.