ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

今天在研究flashback的時候碰到了個小問題，無法開啟flashback功能，報錯：ORA-38706和ORA-38714

步驟如下：

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            use_recovery_file_dest
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

如果沒有開啟歸檔，必須在mount狀態下執行：

SQL> alter database archivelog;

否則是無法開啟flashback的，因為閃回需要歸檔

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

在網上google了一下，沒找到什麼有用的資訊，別人出現這個情況是因為歸檔沒有開啟，但是我已經開啟歸檔了

查看alert.log檔案，也沒什麼資訊，只報了一條：

Wed Aug 21 00:15:20 2013
alter database flashback on
ORA-38706 signalled during: alter database flashback on...

根據提示，理解為需要recover database

SQL> recover database;
Media recovery complete.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

仍然不行，於是從錯誤碼編號著手，看看有什麼有價值的資訊

[oracle@RHEL5U3 ~]$ oerr ora 38706
38706, 00000, "Cannot turn on FLASHBACK DATABASE logging."
// *Cause:  An ALTER DATABASE FLASHBACK ON command failed.
//          Other messages in the alert log describe the problem.
// *Action: Fix the problem and retry.

這個等於沒說，alert.log裡沒有什麼內容

[oracle@RHEL5U3 ~]$ oerr ora 38714
38714, 00000, "Instance recovery required."
// *Cause:  An ALTER DATABASE FLASHBACK ON command failed
because the 
//          database either crashed or was shutdown with the ABORT
//          option.
// *Action: Open the database and then enter the SHUTDOWN command with the
//          NORMAL or IMMEDIATE option.

哇，亮點來了，看到沒有，cause裡寫得很清楚，由於資料庫crash或shutdown abort

action也寫明瞭解決方法，只要open以後，再正常關閉資料庫就行了，多簡單

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              67110480 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

flashback開啟成功，驗證一下：

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

總結：出現錯誤要根據錯誤提示去一步步解決問題，首先當然是查看alert.log，看是否存在有價值的提示，如果沒有，那麼就要從ORA-xxxxx 錯誤本身來尋找問題了，如果google不到好的解決方案也不要氣餒，我們還有oracle提供的oerr命令，非常之好用，有時候使用它會給你帶來意外驚喜哦

-------------------------------------------------------------------------------------------------------
By aaron8219 Chinaunix Blog：http://blog.chinaunix.net/uid/24612962.html

原創內容，轉載請註明連結，謝謝！

http://blog.csdn.net/aaron8219/article/details/10129503