[20210722]ORA-38760與flashback database.txt

[20210722]ORA-38760與flashback database.txt

--//有時候在資料庫升級時設定保證儲存點,如果這些flash資訊刪除會導致資料庫無法open,透過測試說明問題.

1.環境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SYS@book> create restore point 20210722 guarantee flashback database;
create restore point 20210722 guarantee flashback database
                     *
ERROR at line 1:
ORA-00904: : invalid identifier
--//開始不能使用數字.

SYS@book> create restore point a20210722 guarantee flashback database;
Restore point created.

SYS@book> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY

SYS@book> show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 60G

$ ll /u01/app/oracle/fast_recovery_area/BOOK/flashback
total 102528
-rw-r----- 1 oracle oinstall 52436992 2021-07-22 10:25:35 o1_mf_jhkop8d2_.flb
-rw-r----- 1 oracle oinstall 52436992 2021-07-22 10:24:13 o1_mf_jhkopcj8_.flb

2.測試:
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--//模擬刪除flashback目錄中的檔案.
$ mv /u01/app/oracle/fast_recovery_area/BOOK/flashback /u01/app/oracle/fast_recovery_area/BOOK/flashback.xxx
`/u01/app/oracle/fast_recovery_area/BOOK/flashback' -> `/u01/app/oracle/fast_recovery_area/BOOK/flashback.xxx'

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database

SYS@book> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE
------------------ --------------------
RESTORE POINT ONLY MOUNTED

SYS@book> alter database flashback off;
Database altered.

SYS@book> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE
------------------ --------------------
RESTORE POINT ONLY MOUNTED

$ mkdir /u01/app/oracle/fast_recovery_area/BOOK/flashback

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

SYS@book> select * from v$restore_point ;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1: "/u01/app/oracle/fast_recovery_area/BOOK/flashback/o1_mf_jhkop8d2_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--//噢,檔案已經不在了.

$ touch /u01/app/oracle/fast_recovery_area/BOOK/flashback/o1_mf_jhkop8d2_.flb

SYS@book> select * from v$restore_point ;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1: "/u01/app/oracle/fast_recovery_area/BOOK/flashback/o1_mf_jhkop8d2_.flb"
ORA-27047: unable to read the header block of file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1

SYS@book> drop  restore point a20210722 ;
Restore point dropped.

SYS@book> alter database open ;
Database altered.

--//OK,問題是我可能根本不不知道保證儲存點的名字,如何操作呢?

3.重複測試:
SYS@book> create restore point b20210722 guarantee flashback database;
Restore point created.

SYS@book> select * from v$restore_point
  2  @ prxx
==============================
SCN                           : 13288778422
DATABASE_INCARNATION#         : 1
GUARANTEE_FLASHBACK_DATABASE  : YES
STORAGE_SIZE                  : 52428800
TIME                          : 2021-07-22 10:38:42.000000000
RESTORE_POINT_TIME            :
PRESERVED                     : YES
NAME                          : B20210722

PL/SQL procedure successfully completed.

$ ll /u01/app/oracle/fast_recovery_area/BOOK/flashback
total 102528
-rw-r----- 1 oracle oinstall 52436992 2021-07-22 10:38:45 o1_mf_jhkpklfz_.flb
-rw-r----- 1 oracle oinstall 52436992 2021-07-22 10:38:45 o1_mf_jhkpkmpd_.flb

$ rm -f /u01/app/oracle/fast_recovery_area/BOOK/flashback/*.flb
*/

SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
--//問題再現.

SYS@book> select * from v$restore_point ;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1: "/u01/app/oracle/fast_recovery_area/BOOK/flashback/o1_mf_jhkpklfz_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--//提供兩種方法獲取保證儲存點,使用rman或者轉儲控制檔案.

RMAN> list restore point all;
using target database control file instead of recovery catalog
SCN              RSP Time            Type       Time                Name
---------------- ------------------- ---------- ------------------- ----
13288778422                          GUARANTEED 2021-07-22 10:38:42 B20210722

--//13288778422 = scn_wrap,scn_base(10): 3,403876534 = scn_wrap,scn_base(16): 0x3,0x1812aab6

SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump controlf 12
Statement processed.
SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_19279.trc

--//開啟/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_19279.trc,可以發現如下資訊.
***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
 (size = 212, compat size = 212, section max = 2048, section in-use = 1,
  last-recid= 3, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 174, numrecs = 2048)
RESTORE POINT #1:
 restore point name: B20210722 guarantee flag: 1 incarnation: 1next record 0
 restore point scn: 0x0003.1812aab6 07/22/2021 10:38:42
--//0x0003.1812aab6 = scn(10): 13288778422 = scn(16): 0x31812aab6

RMAN> drop restore point  B20210722;
--//昏沒提示的嗎.

RMAN> alter database open ;
database opened

SYS@book> select * from v$restore_point ;
no rows selected

SYS@book> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE
------------------ --------------------
NO                 READ WRITE

--//OK問題解決.這種情況最常見於資料庫升級,建立保證儲存點要保留一段時間,最後忘記又不小心清除了flashback log的情況.