db_recovery_file_dest_size 空间不足导致无法关闭。

放大 SGA_MAX_SIZE 导致 shutdown immediate ，shutdown abort

无响应。

windows 下杀 ORACLE进程。
然后重新启动 oracle 服务。
SQL> startup nomount;
ORA-00443: background process "PMON" did not start

重启了服务器，还是老样子。
SQL> startup nomount;
ORA-00443: background process "PMON" did not start

观察log；

D:oracleproduct10.2.0adminjdbbdumpalert_jdbs.log

ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 2147483648 字节) 已使用 98.79%, 尚有 26083328 字节可用。

Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.

C:Documents and SettingsAdministrator>cd

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 1 11:08:03 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORA-00443: background process "PMON" did not start
SQL> exit
Disconnected

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 1 11:15:16 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='D:oracleproduct10.2.0adminjdbpfileinit.ora.0252012152121'
2 ;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 67110028 bytes
Database Buffers 134217728 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-16038: log 2 sequence# 74 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'D:ORACLEPRODUCT10.2.0ORADATAJDBREDO02.LOG'

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 2 sequence# 74 not archived, no available destinations
ORA-00312: online log 2 thread 1:
'D:ORACLEPRODUCT10.2.0ORADATAJDBREDO02.LOG'

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 2 sequence# 74 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'D:ORACLEPRODUCT10.2.0ORADATAJDBREDO02.LOG'


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHA
FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ -
------- ------------------
1 1 76 52428800 1 NO CURRENT 189
29-FEB-12
3 1 75 52428800 1 NO INACTIVE 185
29-FEB-12
2 1 74 52428800 1 NO INACTIVE 183
29-FEB-12

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1937701
2 1937701
3 1937701
4 1937701
5 1937701
6 1937701

6 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1937701
2 1937701
3 1937701
4 1937701
5 1937701
6 1937701

6 rows selected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 74
Next log sequence to archive 74
Current log sequence 76
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string d:oracleproduct10.2.0flash
_recovery_area
db_recovery_file_dest_size big integer 2G


SQL> alter system set db_recovery_file_dest_size=5g;

System altered.


SQL> alter database open;

Database altered.


SQL> select status from v$instance
2 ;

STATUS
------------------------------------
OPEN

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14181270/viewspace-1057480/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14181270/viewspace-1057480/