Shulou(Shulou.com)06/01 Report--

Last night, I learned that ORACLE was executed by HR users: create table reg_copy as select * from regions; was completely blank and didn't want to quit by force, so it was handled by ORACLE users to terminate the session in three ways:

The first kind: select sid,serial# from v$session where username='HR'

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

The second kind: 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 100COLUMN spid FORMAT A10COLUMN username FORMAT A10COLUMN program FORMAT A45

SELECT s.inst_id, s.sid, s.serializations, p.spid, s.username, s.programFROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_idWHERE s.type! = 'BACKGROUND'

As a result, when you get up and start the database today, you will have the following tips:

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 what happened yesterday, write it down first. The implementation will be further verified later. First record the steps to deal with this failure.

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 and say it has something to do with the restoration 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 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,log_mode from v$database

FLASHBACK_ON LOG_MODE

--

YES ARCHIVELOG

Conclusion:

It should be due to the 38760 error caused by the inconsistent shutdown of the database. If the restart flashback all reported an error of 38714, delete the restore point and restart the database to start normally. In the future, it will be safe to turn off the phone normally.

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.