ORA-16038、ORA-19809、ORA-00312问题的学习总结

最新推荐文章于 2014-05-24 12:29:49 发布

daimin1983

最新推荐文章于 2014-05-24 12:29:49 发布

阅读量1k

点赞数

分类专栏： ORA 文章标签： archive database sql 数据库 system file

本文链接：https://blog.csdn.net/daimin1983/article/details/3261806

版权

ORA 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

今天在启动自己windowns下的oracle时，遇到如下的情况：
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:/Documents and Settings/Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 9 20:59:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba
已连接。
步骤1：
SQL> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 1593835520 bytes
Fixed Size                  1251508 bytes
Variable Size             536872780 bytes
Database Buffers         1048576000 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。

步骤2：
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-16038: 日志 1 序列号 74 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 1 线程 1:
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG'

查了一下错误信息
ORA-19809: limit exceeded for recovery files
Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.
Action: The error is accompanied by 19804. See message 19804 for further details.

解决方法：
方法1、
1）将归档设置到其他目录
修改alter system set log_archive_dest _n= 其他路径
2）转移或者删除闪回恢复区里的归档日志。

方法2、也可以使用增大闪回恢复区的方法来解决
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest_size big integer 2G

下面是我的解决该问题的全部过程：(使用方法1来解决的)

首先试图只使用CLEAR LOGFILE命令来试图转移或者删除闪回恢复区里的归档日志，文档描述如下：
Use the CLEAR LOGFILE clause to reinitialize an online redo log, optionally without archiving the redo log.
CLEAR LOGFILE is similar to adding and dropping a redo log, except that the statement may be issued even if there
are only two logs for the thread and may be issued for the current redo log of a closed thread.
You must specify UNARCHIVED if you want to reuse a redo log that was not archived.

首先，执行下面的语句看看group1是不是current日志组
SQL> select group#,sequence#,archived,status from v$log;
    GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1         74 NO INACTIVE
         3         75 NO INACTIVE
         2         76 NO CURRENT

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
第 1 行出现错误:
ORA-00350: 日志 1 (实例 orcl 的日志, 线程 1) 需要归档
ORA-00312: 联机日志 1 线程 1:
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG'

总结: 日志文件组的状态
current: 当前正在使用的工作组
inactive: 非活动组
active : 归档还没有完成
unused : 还没有使用,一般新建的工组组都是这个状态

视图使用 clear unarchived logfile，使用UNARCHIVED会在重做日志不再需要归档的情况下可以定义UNARCHIVED。
SQL> alter database clear unarchived logfile group 1;
数据库已更改。

SQL> select group#,sequence#,archived,status from v$log;

    GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          0 YES UNUSED
         3         75 NO INACTIVE
         2         76 NO CURRENT

SQL> alter database open;
数据库已更改。

但是，在验证以普通用户重新连接数据库时，报如下错误：
ORA-00257: archiver error. Connect internal only, until freed.

查询资料说如下：
Oracle docs note this about ORA-00257:
ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions.
The most likely cause of this message is the destination device is out of space to store the redo log file.
Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is
set up properly for archiving.
ORA-00257 is a common error in Oracle 10g. You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery
are, or db_recovery_file_dest_size .

ORA-00257错误是由于回闪区内的空间不够造成的，下面还是需要扩大回闪区的大小

MetaLink offers a wealth of information concerning the resolution of ORA-00257 in Oracle 10g. First, make sure your automatic archiving is enabled. To check the archive
lo made, try:
SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     75
下一个存档日志序列   75
当前日志序列           77

Now, note thatyou can find archive destinations if you are using a destination of USE_DB_RECOVERY_FILE_DEST by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:/oracle/product/10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use:

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------------------------------------------------------------ ---------- ---------------
C:/oracle/product/10.2.0/flash_recovery_area 2147483648 2147201024 0 78

You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination.

You next need to archive the log files by,

SQL> alter system archive log all;
alter system archive log all
*
第 1 行出现错误:
ORA-16038: 日志 3 序列号 75 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 3 线程 1:
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG'

It is important to note that within step five of the ORA-00257 resolution, you may also encounter ORA-16020 in the LOG_ARCHIVE_MIN_SUCCEED_DEST, and you should use the proper
archivelog path and use (keeping in mind that you may need to take extra measures if you are using Flash Recovery Area as you will receive more errors if you attempt to use
LOG_ARCHIVE_DEST):

SQL> alter system set LOG_ARCHIVE_DEST_1 = 'location=C:/oracle/product/10.2.0/archivelogpath reopen';
系统已更改。

The last step in resolving ORA-00257 is to change the logs for verification using:
SQL> alter system switch logfile;
alter system switch logfile
*
第 1 行出现错误:
ORA-01109: 数据库未打开

SQL> alter database open;
数据库已更改。

SQL> alter system switch logfile;
系统已更改。