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: {<RET>=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: {<RET>=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.