一、转载自 http://blog.sina.com.cn/s/blog_7282b74b0100tu3d.html
ORACLE打开时提示联机日志无法归档(2011-07-18 11:48:06)
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from all_users;
select * from all_users
*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询
SQL> startup nomount;
ORA-32004: 指定了废弃/过时的参数
ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它
SQL> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 226492416 bytes
Fixed Size 1248212 bytes
Variable Size 180356140 bytes
Database Buffers 37748736 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-16038: 日志 3 序列号 382 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 3 线程 1: 'E:\ORADATA\NYY\REDO03.LOG'
检查flash recovery area的使用情况:
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 101.65 0 61
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
已选择6行。
计算flash recovery area已经占用的空间:
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
3.0495
查看db_recovery_file_dest_size参数的值
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G
SQL>
原因:oracle10g在默认情况下,归档日志是保存在闪回恢复区的,如果建库的时候用的默认设置,闪回恢复区是2G,空间被占满了以后就无法再归档了
解决办法
1、通过设置db_recovery_file_dest_size参数,增大了flash recovery area来解决这个问题。
通过设置db_recovery_file_dest_size参数,增大了flash recovery area来解决这个问题。
SQL> alter system set db_recovery_file_dest_size=4G scope=both;
2、在rman中通过删除flash recovery area中不必要的备份来释放flash recovery area空间
RMAN> delete expired archivelog all;
释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=152 devtype=DISK
说明与恢复目录中的任何存档日志均不匹配
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=152 devtype=DISK
RMAN> alter database open;
数据库已打开
----------------
文2:转载自http://hi.baidu.com/liuchun0614/blog/item/517e471f554498f8e1fe0b68.html
解决过程记录如下。
检查了下alter日志:
检查了下dump文件:
-------------------------------------------------------------------------------------------------------------
Dump file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_arc1_5392.trc
Fri Apr 02 09:56:09 2010
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1146M/2042M, Ph+PgF:2150M/3413M, VA:1276M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 17
Windows thread id: 5392, image: ORACLE.EXE (ARC1)
*** SERVICE NAME:() 2010-04-02 09:56:09.203
*** SESSION ID:(155.1) 2010-04-02 09:56:09.203
kcrrwkx: work to do 0x1 (start)
*** 2010-04-02 09:56:09.281 20146 kcrr.c
ORA-16014: log 1 sequence# 93 not archived, no available destinations
ORA-00312: online log 1 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
*** 2010-04-02 09:57:09.312
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 09:58:09.390
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 09:58:09.453 20146 kcrr.c
*** 2010-04-02 09:59:09.468
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:00:09.546
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:00:09.593 20146 kcrr.c
*** 2010-04-02 10:01:09.578
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:02:09.656
kcrrwkx: work to do 0x1 (end)
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
*** 2010-04-02 10:02:09.812
************************************************************************
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.
************************************************************************
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47716352 bytes disk space from 2147483648 limit
*** 2010-04-02 10:02:09.828 60680 kcrr.c
ARC1: Error 19809 Creating archive log file to 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_04_02\O1_MF_1_89_%U_.ARC'
*** 2010-04-02 10:02:09.828 58942 kcrr.c
kcrrfail: dest:10 err:19809 force:0 blast:1
*** 2010-04-02 10:02:10.000 20146 kcrr.c
ORA-16038: log 2 sequence# 89 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'
*** 2010-04-02 10:03:09.750
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:04:09.828
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:04:09.890 20146 kcrr.c
*** 2010-04-02 10:04:58.687
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:04:58.984 20146 kcrr.c
*** 2010-04-02 10:05:11.500
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:06:11.500
tkcrrxmp: Stopping ARC2 to reduce ARCH processes from 3 to 2
kcrrwkx: work to do 0x1 (end)
*** 2010-04-02 10:06:16.640 20146 kcrr.c
-------------------------------------------------------------------------------------------------------------
出现这个问题的原因,在没看相关的日志和dump文件之前猜测是archive造成,同事的本子只是简单的搭了个环境,没必要使用归档,因此使用如下操作过程解决了该问题,记录下。
--------------------------------got it process----------------------------
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
ORA-16038: 日志 2 序列号 89 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 89
下一个存档日志序列 89
当前日志序列 94
SQL> archive log stop;
已处理的语句
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
SQL> show parameter arch;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string ARC%S_%R.%T
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
remote_archive_enable string true
standby_archive_dest string %ORACLE_HOME%\RDBMS
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 89
下一个存档日志序列 89
当前日志序列 94
SQL> alter system switch logfile;
alter system switch logfile
*
第 1 行出现错误:
ORA-01109: 数据库未打开
SQL> clear
SQL> clear scr;
C:\Documents and Settings\lenovo>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 2 09:55:25 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-16038: 日志 2 序列号 89 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'
SQL> select * from log;
select * from log
*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询
SQL> select * from logfile;
select * from logfile
*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询
SQL> archive log next;
ORA-16014: 日志 2 的序列号 89 未归档, 没有可用的目的地
ORA-00312: 联机日志 2 线程 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'
SQL> alter database drop logfile member 'D:\oracle\product\10.2.0\oradata\orcl\
EDO02.LOG';
alter database drop logfile member 'D:\oracle\product\10.2.0\oradata\orcl\REDO0
.LOG'
*
第 1 行出现错误:
ORA-00361: 无法删除最后一个日志成员
D:\oracle\product\10.2.0\oradata\orcl\REDO02.LOG (组 2)
SQL> alter database clear unarchived logfile 'D:\oracle\product\10.2.0\oradata\
rcl\REDO02.LOG';
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
数据库已经打开。
SQL>
----------------------------------------------------------------------------------------------------
由于是同事的本子的实例,解决过程中并没有考虑其他因素,时候检阅其他文档发现可以如下处理:
(1).flash recovery area的使用情况:
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0/flash
_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> select * from v$flash_recovery_area_usage;
(2).计算flash recovery area已经占用的空间:
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.1231
可以看到,这里已经有2.1231G使用了,这说明我们刚开始设置的db_recovery_file_dest_size=2G不足,导致 online redo log无法归档;
通过设置db_recovery_file_dest_size参数,增大了flash recovery area来解决这个问题。
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
系统已更改。
(3).也可以通过删除flash recovery area中不必要的备份来释放flash recovery area空间来解决这个问题:
(1). delete obsolete;
(2). crosscheck backupset;
delete expired backupset;
最后打开数据库;
SQL> alter database open;
-The End-
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29119536/viewspace-1420997/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29119536/viewspace-1420997/