ora-16014;ora-00312;ora-00257 问题解决过程归纳
来源:互联网 发布:python beautifulsoup4 编辑:程序博客网 时间:2024/06/14 04:43
一、删除archivelog方法:
1、配置RMAN自动管理ARCHIVELOG。也可在RMAN中将数据备份到磁带上,然后将过期的ARCHIVELOG删除;
2、可以手工来处理,步骤如下
1)将/oracle下的相关ARCHIVELOG日志文件移到别的文件系统下(保留一段时间的ARCHIVELOG日志即可,其他的可移走,用系统命令mv移走)。然后打包、压缩,备份到介质上,此时可将这些移出的文件删除。注意:别在原来的/oracle打包了,否则空间占满了就有些麻烦了。
2)以oracle用户登录,执行rman target /。如有多个实例此时执行rman target 用户名/密码@实例名,进入rman
3)在rman中执行
RMAN>list archivelog all; /*列出所有的归档日志文件
RMAN>crosscheck archivelog all; /*与物理归档日志文件保持同步,之前移走了一部分文件,因此执行此命令后会在/oracle目录下找不到的归档日志标记为expired
RMAN>list expired archivelog all; /*列出所有expired(过期)的归档日志文件,此时你就可看到移走的归档日志文件均被标记为expired
RMAN>delete expired archivelog all; /*在oracle中删除所有过期的expired文件
RMAN>list archivelog all; /*再列出所有的归档日志文件,就可发现移走的日志文件被删掉了
RMAN>exit /*退出
Rman Crosscheck删除失效归档
当手工删除了归档日志以后,Rman备份会检测到日志缺失,从而无法进一步继续执行。
所以此时需要手工执行crosscheck过程,之后Rman备份可以恢复正常。
1.Crosscheck日志
$ rman target /
Recovery Manager: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: AVATAR2 (DBID=2480694409)
RMAN> crosscheck archivelog all;
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=25 devtype=DISK
validation failed for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2714.dbf recid=2702 stamp=545107659
validation failed for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2715.dbf recid=2703 stamp=545108268
...........
validation failed for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2985.dbf recid=2973 stamp=545399327
validation succeeded for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2986.dbf recid=2974 stamp=545400820
validation succeeded for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2987.dbf recid=2975 stamp=545401757
validation succeeded for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2988.dbf recid=2976 stamp=545402716
validation succeeded for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2989.dbf recid=2977 stamp=545403661
validation succeeded for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2990.dbf recid=2978 stamp=545404946
validation succeeded for archived log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2991.dbf recid=2979 stamp=545406220
Crosschecked 278 objects
RMAN>
2.使用delete expired archivelog all 命令删除所有过期归档日志:
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
376 1 2714 X 23-NOV-04 =/opt/oracle/oradata/avatar2/archive/1_2714.dbf
.....
3.简要介绍一下report obsolete命令
使用report obsolete命令报告过期备份
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 125 01-NOV-04
Backup Piece 125 01-NOV-04 /data1/oracle/orabak/full_1_541045804
Backup Set 131 04-NOV-04
Backup Piece 131 04-NOV-04 /data1/oracle/orabak/full_AVATAR2_20041104_131
....
Backup Set 173 06-DEC-04
Backup Piece 173 06-DEC-04 /data1/oracle/orabak/full_AVATAR2_20041206_173
Backup Set 179 11-DEC-04
Backup Piece 179 11-DEC-04 /data1/oracle/orabak/arch544588206.arc
.....
Backup Piece 189 17-DEC-04 /data1/oracle/orabak/arch545106606.arc
Backup Set 190 17-DEC-04
Backup Piece 190 17-DEC-04 /data1/oracle/orabak/arch545106665.arc
Backup Set 191 20-DEC-04
Backup Piece 191 20-DEC-04 /data1/oracle/orabak/arch_AVATAR2_20041220_194
Archive Log 2973 20-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2985.dbf
Archive Log 2971 20-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2984.dbf
.....
Archive Log 2705 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2717.dbf
Archive Log 2704 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2716.dbf
Archive Log 2703 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2715.dbf
Archive Log 2702 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2714.dbf
4.使用delete obsolete命令删除过期备份:
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 125 01-NOV-04
Backup Piece 125 01-NOV-04 /data1/oracle/orabak/full_1_541045804
....
Archive Log 2704 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2716.dbf
Archive Log 2703 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2715.dbf
Archive Log 2702 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2714.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/data1/oracle/orabak/full_AVATAR2_20041206_173 recid=173 stamp=544156241
.....
deleted archive log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2715.dbf recid=2703 stamp=545108268
deleted archive log
archive log filename=/opt/oracle/oradata/avatar2/archive/1_2714.dbf recid=2702 stamp=545107659
Deleted 286 objects
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=19 devtype=DISK
specification does not match any archive log in the recovery catalog
-The End-
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 = 其他路径
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;
系统已更改。
五、附脚本
前两天备库的归档日志尚未应用,结果被脚本给压缩了,导致备库向主库再次请求归档日志时主库的归档日志也被压缩而找不到相应文件,结果报错.因以前的压缩及删除归档的脚本存在过多弊端,故重新写了一个通用版的压缩并删除归档程序.
程序要求: 主库或备库部署相同的脚本,并在磁盘上尽可能多的保留归档日志,在空间紧张时删除最早的日志,达到空间循环使用的目的.
脚本如下:
####################压缩脚本####################
#!/bin/sh
#fuction gzip archivelog
#created by qds 20100804
. /oracle9/.profile 2>/dev/null;
ORACLE_SID='para';
ARCH_HOME='/oracle9/app/oracle/product/9.2.0/dbs/arch';
ARCH_POSTFIX='arc';
cd /oracle9/maindb/dbshell;
function get_role {
sqlplus -S "/as sysdba" > ./log/get_role.txt <<EOF
set head off
set echo off
select database_role from v/$database;
exit;
EOF
db_role=`sed '/^$/d' ./log/get_role.txt`
if [ "$db_role" == "PRIMARY" ]; then
return 1;
elif [ "$db_role" == "PHYSICAL STANDBY" ]; then
return 2;
else
return 3;
fi;
}
if [ `ps -ef |grep gzip_archive.sh|wc -l` -ge 3 ]; then
echo "the process has been running";
return;
fi;
get_role;
myflag=$?;
if [ $myflag -eq 1 ]; then
echo "this database is primary database";
#find $ARCH_HOME/ -name "*.$ARCH_POSTFIX" -type f -cmin +60 -exec ls -l {} /;
#find $ARCH_HOME/ -name "*.$ARCH_POSTFIX" -type f -cmin +60 -exec gzip {} /;
ls -lt $ARCH_HOME/*.$ARCH_POSTFIX 2>/dev/null|awk '{if(NR>3) print $NF}' >./log/gzip_archive.txt
for i in `cat ./log/gzip_archive.txt`
do
echo `ls -l $i`;
gzip $i;
done;
return;
elif [ $myflag -eq 2 ]; then
echo "this database is standby database";
sqlplus -S "/as sysdba" > ./log/db_archived_log.txt <<EOF
set head off
set echo off
set feedback off
select name from v/$archived_log where applied='YES' and deleted='NO' and first_time>trunc(sysdate-90);
EOF
ls ${ARCH_HOME}/*.$ARCH_POSTFIX 1> ./log/sys_archived_log.txt 2>/dev/null
for i in `cat ./log/db_archived_log.txt`
do
for j in `cat ./log/sys_archived_log.txt`
do
if [ $i == $j ]; then
echo `ls -l $i` ;
gzip $i;
continue;
fi;
done;
done;
return;
else
echo "WARNING:this database is not the monitor type";
return;
fi;
####################删除脚本####################
#!/bin/sh
#fuction gzip and delete the archivelogs
#created by qds 20100803
ARCH_HOME='/oracle9/app/oracle/product/9.2.0/dbs/arch';
OS_TYPE=`uname`;
SPACE_LIMIT=60;
ARCH_POSTFIX='arc.gz';
DEL_ARC_DAYS=60;
DEL_ARC_INTERVAL=2;
SPACE_USED=0;
cd /oracle9/maindb/dbshell;
ARCH_ROOT=`echo "$ARCH_HOME"|awk -F / '{ print "/"$2}'`;
function get_space_used {
if [ $OS_TYPE='AIX' ]; then
SPACE_USED=`df -g |grep $ARCH_ROOT|awk '{print $4}'|sed 's/%//g'`
elif [ $OS_TYPE='Linux' ];then
SPACE_USED=`df -h |grep $ARCH_ROOT|awk '{print $5}'|sed 's/%//g'`
fi;
echo $SPACE_USED
}
if [ `ps -ef |grep del_archive.sh|wc -l` -ge 3 ]; then
echo "the process has been running";
return;
fi;
while [ "`get_space_used`" -ge $SPACE_LIMIT ]
do
#ls -lrt $ARCH_HOME/*.$ARCH_POSTFIX|awk '{if(NR<10) print $NF}'|xargs rm -f;
find $ARCH_HOME -name "*$ARCH_POSTFIX" -type f -ctime +$DEL_ARC_DAYS -exec ls -l {} /;
find $ARCH_HOME -name "*$ARCH_POSTFIX" -type f -ctime +$DEL_ARC_DAYS -exec rm {} /;
DEL_ARC_DAYS=`expr $DEL_ARC_DAYS - $DEL_ARC_INTERVAL`;
if [ $DEL_ARC_DAYS -le 1 ]; then
break;
fi;
done;
注:由于Aix5.2中不支持find的cmin查找,故注释掉,使用了ls的方式.
写该脚本时曾参考meco的http://www.easyora.net/blog/dg_scripts_list.html#comment-307脚本
- ora-16014;ora-00312;ora-00257 问题解决过程归纳
- ORA-16038,ORA-19809,ORA-00312问题解决
- ORA-16014:ORA-00312:
- ORA-16014:ORA-00312
- ORA-00439:DEFERRED_SEGMENT_CREATION 问题解决过程
- ORA-16014 与 ORA-00312
- ORA-29538、ORA-29532、ORA-29913问题解决
- ora-16038 ora-19809 ora-00312 解决过程
- ORA-00119 ORA-00132问题解决
- ora-00604 ora-12705问题解决
- ORA
- ora
- 一次ORA-39083,ORA-01917的问题解决过程
- ORA-12560,ORA-01034,ORA-27101,ORA-01012问题解决
- ORA-00314;ORA-00312
- ORA-00312 ,ORA-00313
- ORA-16038&ORA-00257
- AIX+ORACLE10g RAC ORA-04030 问题解决过程
- 仿ifeng可翻阅上下滚动新闻
- 如何解决SVN错误:Attempted to lock an already-locked dir
- Web开发相关
- ArcGIS Engine 中多线程的使用
- 中断和异常小结
- ora-16014;ora-00312;ora-00257 问题解决过程归纳
- jsp设置主页和加入收藏
- C++初学者指南 第九篇(9)
- mysql定时器
- 实验_用Ukey(CSP)对文件加解密
- template学习笔记
- linux alias 和 unalias 命令
- C++初学者指南 第九篇(10)
- mysql 相关