日志无法归档问题解决

最新推荐文章于 2020-07-15 15:47:33 发布

babymouse1212

最新推荐文章于 2020-07-15 15:47:33 发布

阅读量2.5k

点赞数 1

分类专栏：故障处理

本文链接：https://blog.csdn.net/babymouse1212/article/details/71078312

版权

故障处理专栏收录该内容

103 篇文章 0 订阅

订阅专栏

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-16038: 日志 1 序列号 2554 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 1 线程 1: 'D:\DISK3\REDO01A.LOG'
ORA-00312: 联机日志 1 线程 1: 'D:\DISK6\REDO01B.LOG'
ORA-00312: 联机日志 1 线程 1: 'D:\DISK9\REDO01C.LOG'

进入rman删除过期归档日志

RMAN> delete expired archivelog all;

释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=155 devtype=DISK
说明与恢复目录中的任何存档日志均不匹配

先手工删除D:\oracle\product\10.1.0\flash_recovery_area\MYORACLE\ARCHIVELOG下的早期的归档日志后，

使用

RMAN> delete expired archivelog all;

释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=156 devtype=DISK

已存档的日志副本列表
关键字     Thrd Seq     S 短时间     名称
------- ---- ------- - ---------- ----
172     1    2452    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2452_4YX3QS4F_.ARC
173     1    2453    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2453_4YX7DYZ4_.ARC
174     1    2454    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2454_4YXBHGLY_.ARC
175     1    2455    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2455_4YXDMZG7_.ARC
176     1    2456    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2456_4YXDOZK7_.ARC
177     1    2457    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2457_4YXFCN1M_.ARC
178     1    2458    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2458_4YXFR97K_.ARC
179     1    2459    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2459_4YXG0N40_.ARC
180     1    2460    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2460_4YXGGZOQ_.ARC
181     1    2461    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2461_4YXL2OPK_.ARC
182     1    2462    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2462_4YXOMZCZ_.ARC
183     1    2463    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2463_4YXQ6F6K_.ARC
184     1    2464    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2464_4YXTOFTH_.ARC
185     1    2465    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2465_4YXY6O7M_.ARC
186     1    2466    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2466_4YY1PHV8_.ARC
187     1    2467    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2467_4YY579BV_.ARC
188     1    2468    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2468_4YY8RJRC_.ARC
189     1    2469    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2469_4YYD7XNQ_.ARC
190     1    2470    X 22-4月 -09 D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOV
YORACLE\ARCHIVELOG\2009_04_22\O1_MF_1_2470_4YYFHKNH_.ARC

是否确定要删除以上对象 (输入 YES 或 NO)? yes
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2452_4YX3QS4F_.ARC 记录 ID=172 时间戳 =684847737
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2453_4YX7DYZ4_.ARC 记录 ID=173 时间戳 =684851487
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2454_4YXBHGLY_.ARC 记录 ID=174 时间戳 =684854640
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2455_4YXDMZG7_.ARC 记录 ID=175 时间戳 =684856834
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2456_4YXDOZK7_.ARC 记录 ID=176 时间戳 =684856897
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2457_4YXFCN1M_.ARC 记录 ID=177 时间戳 =684857588
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2458_4YXFR97K_.ARC 记录 ID=178 时间戳 =684857993
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2459_4YXG0N40_.ARC 记录 ID=179 时间戳 =684858260
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2460_4YXGGZOQ_.ARC 记录 ID=180 时间戳 =684858719
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2461_4YXL2OPK_.ARC 记录 ID=181 时间戳 =684862422
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2462_4YXOMZCZ_.ARC 记录 ID=182 时间戳 =684866048
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2463_4YXQ6F6K_.ARC 记录 ID=183 时间戳 =684867662
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2464_4YXTOFTH_.ARC 记录 ID=184 时间戳 =684871215
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2465_4YXY6O7M_.ARC 记录 ID=185 时间戳 =684874838
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2466_4YY1PHV8_.ARC 记录 ID=186 时间戳 =684878417
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2467_4YY579BV_.ARC 记录 ID=187 时间戳 =684882027
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2468_4YY8RJRC_.ARC 记录 ID=188 时间戳 =684885650
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2469_4YYD7XNQ_.ARC 记录 ID=189 时间戳 =684889215
已删除的存档日志
存档日志文件名 =D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\
\2009_04_22\O1_MF_1_2470_4YYFHKNH_.ARC 记录 ID=190 时间戳 =684890483
19 EXPIRED 对象已删除

进入sql plus

Alter database open;

ok 数据库正常启动了

db_recovery_dest_file_size 默认设置为2G，归档日志文件很快会其放满，导致无法归档，可以增加db_recovery_dest_file_size的大小

Alter system set db_recovery_dest_file_size=4G

Alter database open

ok~~~~~~~~~~

警告日志记录如下：

Mon May 25 14:19:41 2009
ALTER SYSTEM SET db_recovery_file_dest_size='4G' SCOPE=BOTH;
Mon May 25 14:20:05 2009
ARC1: Evaluating archive   log 1 thread 1 sequence 2774
db_recovery_file_dest_size of 4096 MB is 50.09% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Committing creation of archivelog 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2774_51NG4OJM_.ARC'
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2774_51NG4OJM_.ARC
Archiver process freed from errors. No longer stopped
Mon May 25 14:20:06 2009
ARC0: Evaluating archive   log 2 thread 1 sequence 2775
Mon May 25 14:20:07 2009
ARC1: Evaluating archive   log 2 thread 1 sequence 2775
ARC1: Unable to archive log 2 thread 1 sequence 2775
      Log actively being archived by another process
ARC1: Evaluating archive   log 3 thread 1 sequence 2776
Mon May 25 14:20:08 2009
Committing creation of archivelog 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2775_51NG4QO4_.ARC'
Mon May 25 14:20:09 2009
Committing creation of archivelog 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2776_51NG4QPM_.ARC'
Mon May 25 14:20:09 2009
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2775_51NG4QO4_.ARC
Mon May 25 14:20:09 2009
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2776_51NG4QPM_.ARC
Mon May 25 14:20:09 2009
ARC0: Evaluating archive   log 4 thread 1 sequence 2777
Mon May 25 14:20:10 2009
ARC1: Evaluating archive   log 4 thread 1 sequence 2777
ARC1: Unable to archive log 4 thread 1 sequence 2777
      Log actively being archived by another process
Mon May 25 14:20:10 2009
Committing creation of archivelog 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2777_51NG4TC1_.ARC'
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2777_51NG4TC1_.ARC
Mon May 25 14:22:47 2009
Alter database open
Mon May 25 14:22:48 2009
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 2779
Maximum redo generation record size = 120832 bytes
Maximum redo generation change vector size = 116476 bytes
Private_strands 7 at log switch
Thread 1 opened at log sequence 2779
Current log# 1 seq# 2779 mem# 0: D:\DISK3\REDO01A.LOG
Mon May 25 14:22:49 2009
Current log# 1 seq# 2779 mem# 1: D:\DISK6\REDO01B.LOG
Current log# 1 seq# 2779 mem# 2: D:\DISK9\REDO01C.LOG
Successful open of redo thread 1
Mon May 25 14:22:49 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon May 25 14:22:49 2009
SMON: enabling cache recovery
Mon May 25 14:22:50 2009
ARC1: Evaluating archive   log 5 thread 1 sequence 2778
Mon May 25 14:22:51 2009
ARC0: Evaluating archive   log 5 thread 1 sequence 2778
Mon May 25 14:22:51 2009
ARC0: Unable to archive log 5 thread 1 sequence 2778
      Log actively being archived by another process
ARC0: Evaluating archive   log 5 thread 1 sequence 2778
ARC0: Unable to archive log 5 thread 1 sequence 2778
      Log actively being archived by another process
Mon May 25 14:22:52 2009
Committing creation of archivelog 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2778_51NG9V9Q_.ARC'
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2778_51NG9V9Q_.ARC
kccwnc: trying to expand controlfile section 11 for Oracle Managed Files
Mon May 25 14:22:53 2009
Successfully onlined Undo Tablespace 8.
Mon May 25 14:22:53 2009
SMON: enabling tx recovery
Mon May 25 14:22:55 2009
kccrsz: expanded controlfile section 11 from 224 to 448 records
requested to grow by 224 record(s); added 8 block(s) of records
Controlfile has resized from 190 to 206 blocks.
Mon May 25 14:22:56 2009
Database Characterset is ZHS16GBK
Mon May 25 14:22:56 2009
Published database character set on system events channel
Mon May 25 14:22:58 2009
Starting background process QMNC
QMNC started with pid=19, OS id=928
Mon May 25 14:23:01 2009
replication_dependency_tracking turned off (no async multimaster replication found)
Mon May 25 14:23:03 2009
Starting background process MMON
Starting background process MMNL
MMNL started with pid=21, OS id=1668
MMON started with pid=20, OS id=1692
Mon May 25 14:23:05 2009
Completed: Alter database open
Mon May 25 14:24:48 2009
Private_strands 7 at log switch
Thread 1 advanced to log sequence 2780
Current log# 2 seq# 2780 mem# 0: D:\DISK3\REDO02A.LOG
Current log# 2 seq# 2780 mem# 1: D:\DISK6\REDO02B.LOG
Current log# 2 seq# 2780 mem# 2: D:\DISK9\REDO02C.LOG
Mon May 25 14:24:48 2009
ARC1: Evaluating archive   log 1 thread 1 sequence 2779
Committing creation of archivelog 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2779_51NGFKNB_.ARC'
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_25\O1_MF_1_2779_51NGFKNB_.ARC