Ora-38760:this database instance failed to turn on flashback database

Learning Oracle last night with HR users: CREATE TABLE reg_copy as select * from regions; There is no response to a blank, do not want to forcibly quit, and then use the Oracle user to terminate the session of the way to deal with, successively used three ways:

The first type: Select sid,serial# from v$session where username= ' HR ';

Then use:ALTER SYSTEM KILL SESSION ' sid,serial# ' IMMEDIATE.

Second type:ALTER SYSTEM DISCONNECT SESSION ' sid,serial# ' IMMEDIATE

Third: Use the following command to find the system-level process number and then kill it.

SET Linesize 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

S.sid,

s.serial#,

P.spid,

S.username,

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 ';

As a result, starting the database today, you will get the following hints:

Sql> Startuporacle instance started.  Total System Global area 1536602112 bytesfixed size 2213616 bytesvariable size 956303632 bytesdatabase Buffers 570425344 Bytesredo buffers 7659520 bytesdatabase mounted. Ora-38760:this database instance failed to turn on flashback database

I don't know if it has anything to do with yesterday, just record it. Later for further validation. The steps to handle this failure are documented first.

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

Refer to Daniel's article, said to be related to the restore point.
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 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
------------------ ------------
NOARCHIVELOG

Sql>ALTER DATABASE flashback on;

Database altered.
Sql> select Flashback_on,log_mode from V$database;

flashback_on Log_mode
------------------ ------------
YESARCHIVELOG

Conclusion:

should be due to database non-conformance shutdown caused by 38760 errors, if the restart Flashback error 38714, the deletion of the restore point, and then restart the database, you can start normally. After the normal shutdown insurance.

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

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

Ora-38760:this database instance failed to turn on flashback database