运行时错误5解决办法步骤_oracle数据库归档程序错误

运行时错误5解决办法步骤_oracle数据库归档程序错误author: headsen chen date: 2018-04-17 11:12:39 notice:个人原创,转载请注明作者和出处,否则依法追击法律责任。 1,oracle数据库正常使用中,突然报ora-00257的错误,原因是归档日志满了,达到了oracle设置的满值的状态了,所

author: headsen  chen

date: 2018-04-17  11:12:39

notice:个人原创,转载请注明作者和出处,否则依法追击法律责任。

1,oracle数据库正常使用中,突然报ora-00257的错误,原因是归档日志满了,达到了oracle设置的满值的状态了,所以会无法连接了。

运行时错误5解决办法步骤_oracle数据库归档程序错误

 

2,system登录数据库查看使用率和归档文件目录

SQL> show parameter log_archive_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
log_archive_dest_8    string
log_archive_dest_9    string

NAME                            TYPE     VALUE
------------------------------------ ----------- ------------------------------
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
log_archive_dest_state_9    string    enable
SQL>

 

3, archive log list;检查一下归档目录和log sequence ,此时在序号512之前的都可以删除。
SQL> archive log list;

Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     512
Next log sequence to archive   514
Current log sequence	       514

  

4,检查flash recovery area的使用情况,查看archivelog使用率:99% (这里因为已经清理过了,没来得及截图,第一次清理时有99%)

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          43.73            0        215
BACKUPPIECE            0             0        0
IMAGECOPY              0             0        0
FLASHBACKLOG          .3           .22        4

6 rows selected.

上图中,说明归档日志满了,达到43.73%,需要清除过期的归档日志。

 

5,计算flash recovery area已经占用的空间 (已经清理过。没清理之前是2.99)

SQL>  select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
		       1.3209

  

6,找到recovery的目录

SQL> show parameter recover;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /oracle/flash_recovery_area
db_recovery_file_dest_size	     big integer 20G
recovery_parallelism		     integer	 0
SQL> 

 

7,删除过期的归档文件

归档位置用的是默认值,放在flash_recovery_area下(db_recovery_file_dest目录=/oracle/flash_recovery_area)

[oracle@pen EC]$ echo $ORACLE_BASE
/oracle
[oracle@pen EC]$ cd /oracle/flash_recovery_area/EC/archivelog/

[oracle@pen archivelog]$ ls

[oracle@pen archivelog]$ ls
2018_01_01 2018_01_11 2018_01_21 2018_01_31 2018_02_10 2018_02_20 2018_03_02 2018_03_12 2018_03_22 2018_04_01 2018_04_11 2018_01_02 2018_01_12 2018_01_22 2018_02_01 2018_02_11 2018_02_21 2018_03_03 2018_03_13 2018_03_23 2018_04_02 2018_04_12 2018_01_03 2018_01_13 2018_01_23 2018_02_02 2018_02_12 2018_02_22 2018_03_04 2018_03_14 2018_03_24 2018_04_03 2018_04_13 2018_01_04 2018_01_14 2018_01_24 2018_02_03 2018_02_13 2018_02_23 2018_03_05 2018_03_15 2018_03_25 2018_04_04 2018_04_14 2018_01_05 2018_01_15 2018_01_25 2018_02_04 2018_02_14 2018_02_24 2018_03_06 2018_03_16 2018_03_26 2018_04_05 2018_04_15 2018_01_06 2018_01_16 2018_01_26 2018_02_05 2018_02_15 2018_02_25 2018_03_07 2018_03_17 2018_03_27 2018_04_06 2018_04_16 2018_01_07 2018_01_17 2018_01_27 2018_02_06 2018_02_16 2018_02_26 2018_03_08 2018_03_18 2018_03_28 2018_04_07 2018_04_17 2018_01_08 2018_01_18 2018_01_28 2018_02_07 2018_02_17 2018_02_27 2018_03_09 2018_03_19 2018_03_29 2018_04_08 2018_01_09 2018_01_19 2018_01_29 2018_02_08 2018_02_18 2018_02_28 2018_03_10 2018_03_20 2018_03_30 2018_04_09 2018_01_10 2018_01_20 2018_01_30 2018_02_09 2018_02_19 2018_03_01 2018_03_11 2018_03_21 2018_03_31 2018_04_10 

切换到root用户,转移或清除对应的归档日志, 删除一些不用的日期目录的文件,注意保留最后几个文件(比如360以后的)

[oracle@pen archivelog]$ rm -rf 2018_01*
[oracle@pen archivelog]$ rm -rf 2018_02*

[oracle@pen archivelog]$ ls
2018_03_01 2018_03_06 2018_03_11 2018_03_16 2018_03_21 2018_03_26 2018_03_31 2018_04_05 2018_04_10 2018_04_15 2018_03_02 2018_03_07 2018_03_12 2018_03_17 2018_03_22 2018_03_27 2018_04_01 2018_04_06 2018_04_11 2018_04_16 2018_03_03 2018_03_08 2018_03_13 2018_03_18 2018_03_23 2018_03_28 2018_04_02 2018_04_07 2018_04_12 2018_04_17 2018_03_04 2018_03_09 2018_03_14 2018_03_19 2018_03_24 2018_03_29 2018_04_03 2018_04_08 2018_04_13 2018_03_05 2018_03_10 2018_03_15 2018_03_20 2018_03_25 2018_03_30 2018_04_04 2018_04_09 2018_04_14 

 

8,在删除归档日志后,必须用RMAN维护控制文件,否则空间显示仍然不释放。(rman使用system用户登录,或者自创的chen用户登录,必须要有dba的权限) 

[oracle@pen archivelog]$ rman target chen/chen

[oracle@pen archivelog]$ rman target chen/chen

Recovery Manager: Release
10.2.0.1.0 - Production on Tue Apr 17 11:30:03 2018 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: EC (DBID=1558596032) RMAN>

 

9. 检查一些无用的archivelog
RMAN> crosscheck archivelog all;

archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_13/o1_mf_1_506_ff13y3dg_.arc recid=464 stamp=973364932
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_14/o1_mf_1_507_ff2lbs2k_.arc recid=465 stamp=973412441
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_14/o1_mf_1_508_ff3n9454_.arc recid=466 stamp=973447204
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_15/o1_mf_1_509_ff4fvh4q_.arc recid=467 stamp=973473391
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_15/o1_mf_1_510_ff6dv19y_.arc recid=468 stamp=973537890
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_17/o1_mf_1_511_ffbs1s2f_.arc recid=469 stamp=973681465
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_17/o1_mf_1_512_ffbs1s9l_.arc recid=470 stamp=973681466
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_04_17/o1_mf_1_513_ffbs1tb9_.arc recid=471 stamp=973681466
...
Crosschecked 215 objects

 

10, 删除过期的归档
RMAN> delete expired archivelog all;  ——> 输入“YES” 确认。

......
archive log filename=/oracle/flash_recovery_area/EC/archivelog/2018_02_28/o1_mf_1_411_f9dx438b_.arc recid=375 stamp=969297667 Deleted 119 EXPIRED objects

下面这一条命令视情况而定,可以不执行。

RMAN>delete archivelog until time ‘sysdate-1’ ; 删除截止到前一天的所有archivelog 

RMAN>exit  (退出)

 

11,再次查询,发现使用率正常,已经降到19.57

SQL> sqlplus / as sysdba

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

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 19.57 0 96
BACKUPPIECE            0             0        0
IMAGECOPY              0             0        0
FLASHBACKLOG          .3            .22       4

6 rows selected.

 

这样,oracle的归档日志满的问题就解决了,可以登录和访问了。

 

补充:如果archive log模式下不能正常startup,则先恢复成noarchive log,startup成功后,再shutdown;

操作命令如下:

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
shutdown immediate;
再次startup以archive log模式
startup mount;
show parameter log_archive_dest;
alter database archivelog;
archive log list;               
alter database open;
如果还不行,则删除一些archlog log
SQL> select group#,sequence# from v$log;
    GROUP# SEQUENCE#
———- ———-
         1         62
         3         64
         2         63
原来是日志组一的一个日志不能归档
SQL> alter database clear unarchived logfile group 1;
alter database open;
最后,也可以指定位置Arch Log, 请按照如下配置
select name from v$datafile;
alter system set log_archive_dest=’/opt/app/oracle/oradata/usagedb/arch’ scope=spfile
或者修改大小(我的改成了20G,不能太小了)
SQL> alter system set db_recovery_file_dest_size=3G scope=both;

 

今天的文章运行时错误5解决办法步骤_oracle数据库归档程序错误分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/45631.html

(0)
编程小号编程小号
上一篇 2023-09-06 18:11
下一篇 2023-09-06 18:30

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注