ORA-38760: This database instance failed to turn on flashbac

昨晚学习ORACLE用HR用户执行：create table reg_copy as select * from regions;居然一片空白没有任何反应，不想强行退出，于是用ORACLE用户来终止会话的方式来处理，先后用了三种方式：

第一种：select sid,serial# from v$session where username='HR';

???????? 然后用：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE。

第二种：ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE

第三种：用以下命令查到系统级别的进程号，然后杀掉。

SET?LINESIZE?100

COLUMN?spid?FORMAT?A10

COLUMN?username?FORMAT?A10

COLUMN?program?FORMAT?A45

?

SELECT?s.inst_id,

???????s.sid,monospace;width:100%;border-bottom-style:none;color:#000000;padding-bottom:0px;text-align:left;padding-top:0px;border-right-style:none;padding-left:0px;margin:0em;border-left-style:none;line-height:12pt;padding-right:0px;background-color:#f4f4f4;">???????s.serial#,monospace;width:100%;border-bottom-style:none;color:#000000;padding-bottom:0px;text-align:left;padding-top:0px;border-right-style:none;padding-left:0px;margin:0em;border-left-style:none;line-height:12pt;padding-right:0px;background-color:#FFFFFF;">???????p.spid,monospace;width:100%;border-bottom-style:none;color:#000000;padding-bottom:0px;text-align:left;padding-top:0px;border-right-style:none;padding-left:0px;margin:0em;border-left-style:none;line-height:12pt;padding-right:0px;background-color:#f4f4f4;">???????s.username,monospace;width:100%;border-bottom-style:none;color:#000000;padding-bottom:0px;text-align:left;padding-top:0px;border-right-style:none;padding-left:0px;margin:0em;border-left-style:none;line-height:12pt;padding-right:0px;background-color:#FFFFFF;">???????s.program

FROM???gv$session?s

???????JOIN?gv$process?p?ON?p.addr?=?s.paddr?AND?p.inst_id?=?s.inst_id

WHERE??s.type?!=?'BACKGROUND';

结果今天起来起动数据库就有如下提示了：

SQL>?startup
ORACLE?instance?started.
Total?System?Global?Area?1536602112?bytes
Fixed?Size????????????2213616?bytes
Variable?Size??????????956303632?bytes
Database?Buffers??????570425344?bytes
Redo?Buffers????????????7659520?bytes
Database?mounted.
ORA-38760:?This?database?instance?failed?to?turn?on?flashback?database

不知跟昨天的事有没有关系，先记录下来。晚些做实现进一步验证。先记录处理此故障的步骤。

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1536602112 bytes
Fixed Size?? ??? ???? 2213616 bytes
Variable Size?? ??? ?? 956303632 bytes
Database Buffers?? ?? 570425344 bytes
Redo Buffers?? ??? ???? 7659520 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38713: Flashback Database logging is already turned on.


SQL> alter database flashback off;

Database altered.

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.


SQL> alter database flashback off;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1536602112 bytes
Fixed Size?? ??? ???? 2213616 bytes
Variable Size?? ??? ?? 956303632 bytes
Database Buffers?? ?? 570425344 bytes
Redo Buffers?? ??? ???? 7659520 bytes
Database 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.

SQL>? select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL> alter database force logging;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1536602112 bytes
Fixed Size?? ??? ???? 2213616 bytes
Variable Size?? ??? ?? 956303632 bytes
Database Buffers?? ?? 570425344 bytes
Redo Buffers?? ??? ???? 7659520 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


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.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

参考大牛的文章，说是跟还原点有关系。
SQL> col name for a30
SQL> /

NAME?? ??? ??? ??????? TO_CHAR(TIME,'YYYY/ GUA
------------------------------ ------------------- ---
B1?? ??? ??? ??????? 2016/06/06 23:03:58 YES

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL> drop restore point b1;

Restore point dropped.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

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.


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.


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1536602112 bytes
Fixed Size?? ??? ???? 2213616 bytes
Variable Size?? ??? ?? 956303632 bytes
Database Buffers?? ?? 570425344 bytes
Redo Buffers?? ??? ???? 7659520 bytes
Database 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.


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> alter database open;

Database altered.

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

Total System Global Area 1536602112 bytes
Fixed Size?? ??? ???? 2213616 bytes
Variable Size?? ??? ?? 956303632 bytes
Database Buffers?? ?? 570425344 bytes
Redo Buffers?? ??? ???? 7659520 bytes
Database mounted.
Database opened.
SQL> select flashback_on,log_mode from v$database;

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

SQL> alter database flashback on;

Database altered.
SQL> select flashback_on,0);">YES?? ??? ??? ARCHIVELOG

结论：

应该是由于数据库非一致性关闭导致的38760错误，如果重启flashback都报错38714，就删除还原点后，再重启数据库，可以正常启动了。以后还是正常关机保险。

参考：http://www.voidcn.com/article/p-vcqxmrtd-bp.html

http://blog.chinaunix.net/uid-22948773-id-2821820.html