[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的情况.