解决 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;
   # 开启数据库
   alter database open;