ORA-38760: 此数据库实例无法启用闪回数据库：guarantee restore point 导致

来源：互联网发布：sql sever check 约束编辑：程序博客网时间：2024/06/11 17:49

一大早起来打开sqlplus的时候，发现数据库启动不了，并且出现下面的错误：

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 17 09:04:48 2013Copyright (c) 1982, 2010, Oracle.  All rights reserved.Enter user-name: shEnter password:ERROR:ORA-01033: ORACLE initialization or shutdown in progressProcess ID: 0Session ID: 0 Serial number: 0

这个错误的描述不太精确，需要进一步确认错误产生的原因。为此我通过CMD启动sqlplus通过SYS用户登录数据库。

C:\Users\asus>sqlplus/nologSQL*Plus: Release 11.2.0.1.0 Production on 星期六 8月 17 08:55:12 2013Copyright (c) 1982, 2010, Oracle.  All rights reserved.SQL> conn sys/123456@test as sysdba;已连接。SQL> shutdown immediate;ORA-01109: 数据库未打开已经卸载数据库。ORACLE 例程已经关闭。

也就是说，数据库果然是由于某些原因而无法打开。为此，试着打开数据库，看看是什么错误造成的。

SQL> startupORACLE 例程已经启动。Total System Global Area  644468736 bytesFixed Size                  1376520 bytesVariable Size             268439288 bytesDatabase Buffers          369098752 bytesRedo Buffers                5554176 bytes数据库装载完毕。ORA-38760: 此数据库实例无法启用闪回数据库

那又是什么原因让数据库实例无法启动闪回数据库呢？这是，我想到了数据库启动或关闭的所有错误都会记录在后台报警日志文件中。为此，下一步，应该去查看后台报警日志文件，查看详细的错误信息。后台警报日志文件就是background_dump_test路径下的alert_SID.log文件.把文件拉到后面就可以看到最近的警报记录。

SQL> show parameter BACKGROUND_DUMP_DEST;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------background_dump_dest                 string      d:\app\asus\diag\rdbms\test\te                                                 st\trace

下面是后台警报日志文件对于本次启动报错的描述：

Starting up:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options.Using parameter settings in server-side spfile D:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILETEST.ORASystem parameters with non-default values:  processes                = 150  nls_language             = "AMERICAN"  nls_territory            = "CHINA"  nls_date_format          = "yyyy-mm-dd hh24:mi:ss"  memory_target            = 1G  control_files            = "D:\APP\ASUS\ORADATA\TEST\CONTROL01.CTL"  control_files            = "D:\APP\ASUS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL"  db_block_size            = 8192  compatible               = "11.2.0.0.0"  log_archive_format       = "ARC%S_%R.%T"  db_recovery_file_dest    = "D:\app\asus\flash_recovery_area"  db_recovery_file_dest_size= 3852M  db_flashback_retention_target= 1440  undo_tablespace          = "UNDOTBS1"  undo_retention           = 900  remote_login_passwordfile= "EXCLUSIVE"  db_domain                = ""  dispatchers              = "(PROTOCOL=TCP) (SERVICE=TESTXDB)"  audit_file_dest          = "D:\APP\ASUS\ADMIN\TEST\ADUMP"  audit_trail              = "DB"  db_name                  = "TEST"  open_cursors             = 300  diagnostic_dest          = "D:\APP\ASUS"Sat Aug 17 08:56:14 2013PMON started with pid=2, OS id=5952 Sat Aug 17 08:56:14 2013VKTM started with pid=3, OS id=4924 at elevated prioritySat Aug 17 08:56:14 2013GEN0 started with pid=4, OS id=3128 VKTM running at (10)millisec precision with DBRM quantum (100)msSat Aug 17 08:56:14 2013DIAG started with pid=5, OS id=4304 Sat Aug 17 08:56:14 2013DBRM started with pid=6, OS id=4048 Sat Aug 17 08:56:14 2013PSP0 started with pid=7, OS id=3544 Sat Aug 17 08:56:14 2013DIA0 started with pid=8, OS id=5484 Sat Aug 17 08:56:14 2013MMAN started with pid=9, OS id=6036 Sat Aug 17 08:56:14 2013DBW0 started with pid=10, OS id=4192 Sat Aug 17 08:56:14 2013LGWR started with pid=11, OS id=5880 Sat Aug 17 08:56:14 2013CKPT started with pid=12, OS id=5804 Sat Aug 17 08:56:15 2013SMON started with pid=13, OS id=1964 Sat Aug 17 08:56:15 2013RECO started with pid=14, OS id=1900 Sat Aug 17 08:56:15 2013MMON started with pid=15, OS id=2072 Sat Aug 17 08:56:15 2013MMNL started with pid=16, OS id=4412 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...starting up 1 shared server(s) ...ORACLE_BASE from environment = D:\app\asusSat Aug 17 08:56:15 2013ALTER DATABASE   MOUNTSuccessful mount of redo thread 1, with mount id 2121853871Allocated 3981204 bytes in shared pool for flashback generation bufferStarting background process RVWRSat Aug 17 08:56:20 2013RVWR started with pid=20, OS id=3680 Can not open flashback thread because there is no more space in flash recovery areaDatabase mounted in Exclusive ModeLost write protection disabledCompleted: ALTER DATABASE   MOUNTSat Aug 17 08:56:22 2013ALTER DATABASE OPENErrors in file d:\app\asus\diag\rdbms\test\test\trace\test_ora_5552.trc:ORA-38760: 此数据库实例无法启用闪回数据库ORA-38760 signalled during: ALTER DATABASE OPEN...Sat Aug 17 09:11:18 2013Errors in file d:\app\asus\diag\rdbms\test\test\trace\test_m000_5136.trc:ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 99.92% used, and has 3282432 remaining bytes available.************************************************************************You have following choices to free up space from recovery area:1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,   then consider changing RMAN ARCHIVELOG DELETION POLICY.2. Back up files to tertiary device such as tape using RMAN   BACKUP RECOVERY AREA command.3. Add disk space and increase db_recovery_file_dest_size parameter to   reflect the new space.4. Delete unnecessary files using RMAN DELETE command. If an operating   system command was used to delete files, then use RMAN CROSSCHECK and   DELETE EXPIRED commands.************************************************************************

从上面的语句可以看出是闪回恢复区的空间快用完了，所以数据库启动不了。为此，首要任务应该是扩大闪回恢复区的空间或删除、移出部分闪回恢复区中的文件以让闪回恢复区有足够的空间使用。

查看v$flashback_database_log果然用于闪回的空间已经用完了，另外从v$flash_recovery_area_usage来看，闪回恢复区的空间的确已经达到了警戒值。

SQL> col file_type format A20;SQL> set linesize 120 pagesize 100;SQL> select * from v$flash_recovery_area_usage;FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES-------------------- ------------------ ------------------------- ---------------CONTROL FILE                          0                         0               0REDO LOG                              0                         0               0ARCHIVED LOG                      48.91                         0              66BACKUP PIECE                       30.4                         0               3IMAGE COPY                            0                         0               0FLASHBACK LOG                     20.61                         0             208FOREIGN ARCHIVED LOG                  0                         0               0已选择7行。SQL> select * from v$flashback_database_log ;OLDEST_FLASHBACK_SCN OLDEST_FLASHBA RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE-------------------- -------------- ---------------- -------------- ------------------------                   0                            1440      832438272                        0

这时，我忽然想起几天前似乎建立了一个guarantee restore point，可能就是它占用了大量的空间，retention_target为1天，该guarantee restore point也就没什么意义了。

SQL> col time format A20;SQL> col restore_point_time format A20;SQL> col name format A10;SQL> set linesize 150;SQL> select * from v$restore_point;       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                 RESTORE_POINT_TIME   PRE NAME---------- --------------------- --- ------------ -------------------- -------------------- --- ----------   2808629                     2 YES    832438272 11-8月 -13 03.43.20.                      YES NOW_GUARRA                                                  000000000 下午                                NTEE   2809035                     2 NO             0 11-8月 -13 03.51.48.                      YES NOW_PRESER                                                  000000000 下午                                VE   2592127                     2 NO             0 08-8月 -13 04.56.57.                      NO  RESP                                                  000000000 下午

果然，存在，而且从storage_size来看，基本上占据了所有的用于flashback log的空间。至此，症结已经找到。是由于guarantee restore point 导致的空间不足。

SQL> alter database flashback off;数据库已更改。SQL> alter database open;alter database open*第 1 行出现错误:ORA-38760: 此数据库实例无法启用闪回数据库SQL> drop restore point now_guarrantee;还原点已删除。SQL> alter database flashback on;alter database flashback on*第 1 行出现错误:ORA-38706: 无法启用 FLASHBACK DATABASE 事件记录。ORA-38714: 要求进行实例恢复。SQL> alter database open;数据库已更改。SQL> select * from v$restore_point;       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                 RESTORE_POINT_TIME   PRE NAME---------- --------------------- --- ------------ -------------------- -------------------- --- ----------   2809035                     2 NO             0 11-8月 -13 03.51.48.                      YES NOW_PRESER                                                  000000000 下午                                VE   2592127                     2 NO             0 08-8月 -13 04.56.57.                      NO  RESP                                                  000000000 下午

SQL> shutdown immediate;数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup mount;ORACLE 例程已经启动。Total System Global Area  644468736 bytesFixed Size                  1376520 bytesVariable Size             268439288 bytesDatabase Buffers          369098752 bytesRedo Buffers                5554176 bytes数据库装载完毕。SQL> alter database flashback on;数据库已更改。SQL> alter database open;数据库已更改。