海量数据迁移之外部表加载(100天)
本地有一个小的环境，今天照例登上sqlplus，突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大，限于本地空间有限。准备改回去。把多余的归档删除了。
sqlplus n1/n1
....
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.这个时候sysdba的权限发挥作用了，它可以照常登录。然后开始做resize操作。
SQL> alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 100M;
但是等了好一会儿，一直没有输出。是不是Hang住了。
看来得删除归档文件了。然后到归档目录下尝试删除，结果这个时候rm似乎不管用了，命令执行了，但是归档文件还在那。我想是不是有系统级的问题了。
决定重启 ，重启以后，Mount状态过后就很不留情的报了一个03113的错误。
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 255852824 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 8220
Session ID: 237 Serial number: 5查看alert日志文件。看到重做日志归档失败。
ARC3 started with pid=23, OS id=8232
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ARCH: Encountered disk I/O error 19502
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_216_837590339.dbf' (error 19502) (TEST01)
ARC2: Encountered disk I/O error 19502
ARC2: Closing local archive destination LOG_ARCHIVE_DEST_1: '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_217_837590339.dbf' (error 19502) (TEST01)
ARC2: I/O error 19502 archiving log 1 to '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_217_837590339.dbf'
ARCH: I/O error 19502 archiving log 3 to '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_216_837590339.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance TEST01 - Archival Error
ORA-16038: log 1 sequence# 217 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 1 thread 1: '/u03/ora11g/oradata/TEST01/redo01.log'
Errors in file /u03/ora11g/diag/rdbms/test01/TEST01/trace/TEST01_ora_8220.trc:
ORA-16038: log 3 sequence# 216 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 3 thread 1: '/u03/ora11g/oradata/TEST01/redo03.log'
USER (ospid: 8220): terminating the instance due to error 16038
Wed May 21 17:48:18 2014
System state dump requested by (instance=1, osid=8220), summary=[abnormal instance termination].
System State dumped to trace file /u03/ora11g/diag/rdbms/test01/TEST01/trace/TEST01_diag_8185.trc
Dumping diagnostic data in directory=[cdmp_20140521174818], requested by (instance=1, osid=8220), summary=[abnormal instance termination].
Instance terminated by USER, pid = 8220因为是测试环境，所以采用了如下的方法，启动到Mount阶段之后
SQL> startup mount
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 255852824 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
SQL> alter database clear unarchived logfile '/u03/ora11g/oradata/TEST01/redo01.log';
Database altered.
SQL> alter database clear unarchived logfile '/u03/ora11g/oradata/TEST01/redo03.log';
Database altered.
SQL> alter database open;
Database altered.查看日志文件情况
SQL> select group#,sequence#,members,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------------- ---
1 219 1 CURRENT NO
2 218 1 INACTIVE NO
3 0 1 UNUSED YES
1* select *from v$logfile
SQL> /
GROUP# STATUS TYPE MEMBER IS_RECOVER
---------- ------- ------- -------------------------------------------------- ----------
1 ONLINE /u03/ora11g/oradata/TEST01/redo01.log NO
2 ONLINE /u03/ora11g/oradata/TEST01/redo02.log NO
3 ONLINE /u03/ora11g/oradata/TEST01/redo03.log NO这个时候重新执行temp文件的resize，就没有问题了。
SQL> alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 100M;
Database altered.本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2014-06-11，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
腾讯云开发者
