ORA-16014: log 1 sequence# 23308 not archived, no available destinations,ORA-00312: online log 1 thread 1:

 

昨天快下班，重启了下server，结果遭遇了数据库打不开的情况，原因是db_recovery_file_dest_size设置太小，满了，导致archivelog无法正常归档。奇怪的是库以pfile启动，导致之前设置的alter system set db_recovery_file_dest_size=20G scope=both;重启后恢复了default。所以需要改成spfile启动。

 

 

 

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

Connected as xxxx-dw

 

SQL>

 

SQL> show parameter log_archive_dest;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest                     string     

log_archive_dest_1                   string     

log_archive_dest_10                  string     

log_archive_dest_2                   string     

log_archive_dest_3                   string     

log_archive_dest_4                   string     

log_archive_dest_5                   string     

log_archive_dest_6                   string     

log_archive_dest_7                   string     

log_archive_dest_8                   string     

log_archive_dest_9                   string     

log_archive_dest_state_1             string      enable

log_archive_dest_state_10            string      enable

log_archive_dest_state_2             string      enable

log_archive_dest_state_3             string      enable

log_archive_dest_state_4             string      enable

log_archive_dest_state_5             string      enable

log_archive_dest_state_6             string      enable

log_archive_dest_state_7             string      enable

log_archive_dest_state_8             string      enable

log_archive_dest_state_9             string      enable

 

SQL> show parameter db_recovery_file_dest_size;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 2G

 

SQL>

SQL> select * from v$recovery_file_dest;

 

NAME                                                                             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

-------------------------------------------------------------------------------- ----------- ---------- ----------------- ---------------

D:/oracle/product/10.2.0/flash_recovery_area                                      2147483648 5261666304                 0             111

SQL> select * from v$flash_recovery_area_usage;

 

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

------------ ------------------ ------------------------- ---------------

CONTROLFILE                   0                         0               0

ONLINELOG                     0                         0               0

ARCHIVELOG               245.02                         0             111

BACKUPPIECE                   0                         0               0

IMAGECOPY                     0                         0               0

FLASHBACKLOG                  0                         0               0

 

6 rows selected

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------

         1          1      23308   52428800          1 NO       INACTIVE            2281986130 2011-3-31 1

         2          1      23309   52428800          1 NO       CURRENT             2282010875 2011-3-31 1

         3          1      23307   52428800          1 NO       INACTIVE            2281951061 2011-3-31 1

 

SQL> alter system set db_recovery_file_dest_size=20G scope=both;

 

alter system set db_recovery_file_dest_size=20G scope=both

 

ORA-32001: 已请求写入 SPFILE, 但是在启动时未指定 SPFILE

 

SQL> alter system set db_recovery_file_dest_size=20G ;

 

alter system set db_recovery_file_dest_size=20G

 

Not logged on

 

SQL> connect xxxx-dw/xxxx-dw@xxxx-dw

Not logged on

 

SQL> show parameter db_recovery_file_dest_size;

Not logged on

 

SQL> connect sys/xxxx-dw@xxxx-dw as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

Connected as SYS

 

SQL> show parameter db_recovery_file_dest_size;

ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询

 

SQL> alter database open;

 

alter database open

 

ORA-16014: 日志 3 sequence# 23307 未归档, 没有可用的目的地

ORA-00312: 联机日志 3 线程 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/XXXX-DW/REDO03.LOG'

 

SQL> connect xxxx-dw/xxxx-dw@xxxx-dw

Not logged on

 

SQL> alter database open;

 

alter database open

 

ORA-16014: 日志 3 sequence# 23307 未归档, 没有可用的目的地

ORA-00312: 联机日志 3 线程 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/XXXX-DW/REDO03.LOG'

 

SQL> show parameter db_recovery_file

ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询

 

SQL> alter system archive log current;

 

alter system archive log current

 

ORA-01109: 数据库未打开

 

SQL> alter system set db_recovery_file_dest_size=20G scope=both;

 

alter system set db_recovery_file_dest_size=20G scope=both

 

ORA-32001: 已请求写入 SPFILE, 但是在启动时未指定 SPFILE

 

SQL> alter database open;

 

alter database open

 

ORA-16014: 日志 3 sequence# 23307 未归档, 没有可用的目的地

ORA-00312: 联机日志 3 线程 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/XXXX-DW/REDO03.LOG'

 

SQL> shutdown immediate;

 

shutdown immediate

 

ORA-00900: 无效 SQL 语句

 

SQL> shutdown immediate;

 

shutdown immediate

 

ORA-00900: 无效 SQL 语句

 

----------------------------------------------从plsql dev 转到sqlplus

 

Microsoft Windows XP [版本 5.1.2600]

(C) 版权所有 1985-2001 Microsoft Corp.

 

C:/Documents and Settings/felix>sqlplus xxxx-dw/xxxx-dw@xxxx-dw

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 31 19:53:09 2011

 

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

 

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

 

请输入用户名:

ERROR:

ORA-01017: 用户名/口令无效; 登录被拒绝

 

SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus

 

C:/Documents and Settings/felix>

C:/Documents and Settings/felix>sqlplus sys/xxxx-dw@xxxx-dw as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 31 19:53:21 2011

 

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

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> shutdown immediate

ORA-01109: 数据库未打开

 

 

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

 

Total System Global Area 1317011456 bytes

Fixed Size                  1305016 bytes

Variable Size             243271240 bytes

Database Buffers         1065353216 bytes

Redo Buffers                7081984 bytes

数据库装载完毕。

数据库已经打开。

SQL> alter database open;

alter database open

*

第 1 行出现错误:

ORA-01531: 实例已打开数据库

 

 

SQL> show parameter db_recory;

SQL> alter system set db_recovery_file_dest_size=20G scope=both;

 

系统已更改。

 

SQL> alter database open;

alter database open

*

第 1 行出现错误:

ORA-01531: 实例已打开数据库

 

 

SQL>

 

 

 

SQL> show parameter db_recovery_file

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      D:/oracle/product/10.2.0/flash_recovery_area

db_recovery_file_dest_size           big integer 20G

 

SQL> show parameter db_recovery_file_dest_size;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 20G

 

SQL>

 

 

 

 

 

 

--------其实告警日志下面已经给出了解决方案：

//192.168.10.59/d$/oracle/product/10.2.0/admin/xxxx-dw/bdump/alert_xxxx-dw.log

 

Thu Mar 31 19:47:34  2011

ARC0: Error 19809 Creating archive log file to 'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/XXXX-DW/ARCHIVELOG/2011_03_31/O1_MF_1_23308_U_.ARC'

ARCH: Archival stopped, error occurred. Will continue retrying

Thu Mar 31 19:47:34  2011

Errors in file d:/oracle/product/10.2.0/admin/xxxx-dw/bdump/xxxx-dw_arc0_1592.trc:

ORA-16038: log 1 sequence# 23308 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/XXXX-DW/REDO01.LOG'

 

ARC1 started with pid=17, OS id=2512

Thu Mar 31 19:47:35  2011

ARC1: Becoming the heartbeat ARCH

ARCH: Archival stopped, error occurred. Will continue retrying

Thu Mar 31 19:47:35  2011

Errors in file d:/oracle/product/10.2.0/admin/xxxx-dw/bdump/xxxx-dw_arc1_2512.trc:

ORA-16014: log 1 sequence# 23308 not archived, no available destinations

ORA-00312: online log 1 thread 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/XXXX-DW/REDO01.LOG'

 

You have following choices to free up space from flash 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.

************************************************************************

Thu Mar 31 19:47:35  2011

Errors in file d:/oracle/product/10.2.0/admin/xxxx-dw/udump/xxxx-dw_ora_4700.trc:

ORA-19809: ???????????

ORA-19804: ???? 15230464 ?????? (? 2147483648 ???)