Skip to content

解决 Oracle 归档空间满问题

背景说明

公司有服务在使用oracle数据库,由于是遗留系统,且已无专职维护人员,最近服务出现错误,经过排查发现是oracle数据的问题。

问题

经过应用错误日志方发现是DATASET出错,登录oracle发现sysdba可以连接,但是其他用户不能登录;查看内存发现内存已满,检查后台日志发现。
出现如下错误:

ORA-00270: error creating archive log

经过搜索,该错误是备库归档空间满导致归档不能传输。

解决思路

两种方式:
1 增加归档空间
2.删除历史归档释放空间
官方文档内容:

You have following choices to free up space from 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.

解决操作

方式1 删除历史归档释放空间

  1. 检查空间
    show parameter db_recovery_file_dest_size
  2. rman设置
    RMAN> show all;
    RMAN configuration parameters for database with db_unique_name DAVID are:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'g:\oracle\backup\%F';
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'G:\APP\DAVIDD\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDAVID.ORA'; # default
    将备份策略改为基于冗余数量的备份策略
    RMAN> configure retention policy to redundancy 5;
    old RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
    new RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
    new RMAN configuration parameters are successfully stored
    RMAN> show all;
    RMAN configuration parameters for database with db_unique_name DAVID are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'g:\oracle\backup\%F';
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'G:\APP\DAVIDD\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDAVID.ORA';
  3. 手工删除日志
    ## 数字3 根据情况自行修改,单位为天
    delete archivelog all completed before 'sysdate - 3';
  4. asmcmd 删除文件
    su - grid
    export ORACLE_SID=+ASM1
    asmcmd
    ## +DATA 根据你配置修改
    cd +DATA/orcl/archivelog
    ## 根据前缀删除指定文件夹
    rm -rf 2020_02*

    方式2 增加空间

    alter system set db_recovery_file_dest_size=20G;
    show parameter db_recovery_file_dest_size

    重启数据库

    在修改完成相关配置之后,则需要重启数据库。

    sqlplus /as sysdba;
    # 关闭数据库
    shutdown normal;
    # 装载数据库
    startup mount;
    # 开启数据库
    altert database open;
发表评论

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