失眠网 > Oracle踩坑笔记 - .4.001.imp导入dmp文件失败 archive归档空间已满

Oracle踩坑笔记 - .4.001.imp导入dmp文件失败 archive归档空间已满

时间：2020-03-31 09:58:21

Oracle踩坑笔记 - .4.001.imp导入dmp文件失败，archive归档空间已满

文章目录

Oracle踩坑笔记 - .4.001.imp导入dmp文件失败，archive归档空间已满背景一、原因分析二、解决方式1.查询归档空间使用情况2.清除归档空间历史文件2.1 方法一：(不推荐！！！)直接删除归档空间文件，不确定删除哪些文件，看方法二2.2 方法二：(推荐！！！)rman清除归档日志总结

背景

dmp备份文件50G，imp导入oracle数据库时中途卡主，也未报错。

一、原因分析

网上查了很多文章，最后发现是因为归档空间分配的不够大，归档空间40G < dmp备份文件50G，

imp导入dmp文件时，似乎会将数据都归档备份下来，具体原因不知，请各位有经验的大神指教一下。

二、解决方式

1.查询归档空间使用情况

[oracle@lbs archivelog]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 09:10:38 Copyright (c) 1982, , Oracle. All rights reserved.SQL> conn / as sysdbaConnected.SQL> show parameter recover; #查看归档空间分配存储空间40gNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_deststring/home/u01/app/oracle/recovery_areadb_recovery_file_dest_size big integer 40Grecovery_parallelism integer0SQL> select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE;#查看归档空间适用情况，使用率100.36%，文件数量1009个FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES-------------------- ------------------ ---------------CONTROL FILE00REDO LOG00ARCHIVED LOG 100.36 1009BACKUP PIECE00IMAGE COPY 00FLASHBACK LOG00FOREIGN ARCHIVED LOG 007 rows selected.

2.清除归档空间历史文件

2.1 方法一：(不推荐！！！)直接删除归档空间文件，不确定删除哪些文件，看方法二

[oracle@lbs archivelog]$ pwd #切换到归档空间目录/home/u01/app/oracle/recovery_area/ORCL/archivelog[oracle@lbs archivelog]$ ll总用量 88drwxr-x--- 2 oracle oinstall 4096 3月 29 10:32 _02_10drwxr-x--- 2 oracle oinstall 4096 4月 5 15:40 _03_29drwxr-x--- 2 oracle oinstall 4096 4月 5 15:40 _03_30drwxr-x--- 2 oracle oinstall 4096 4月 5 15:40 _03_31drwxr-x--- 2 oracle oinstall 4096 4月 5 15:40 _04_01drwxr-x--- 2 oracle oinstall 4096 4月 5 15:40 _04_02drwxr-x--- 2 oracle oinstall 4096 4月 5 15:40 _04_03drwxr-x--- 2 oracle oinstall 4096 4月 5 16:11 _04_04drwxr-x--- 2 oracle oinstall 12288 4月 5 19:52 _04_05drwxr-x--- 2 oracle oinstall 40960 4月 6 16:00 _04_06drwxr-x--- 2 oracle oinstall 4096 4月 7 09:10 _04_07[oracle@lbs archivelog]$

因为我是4月6日导入的，所有删除4月6日之前的文件

[oracle@lbs archivelog]$ ll总用量 44drwxr-x--- 2 oracle oinstall 40960 4月 6 16:00 _04_06drwxr-x--- 2 oracle oinstall 4096 4月 7 09:10 _04_07[oracle@lbs archivelog]$

2.2 方法二：(推荐！！！)rman清除归档日志

RMAN清除方式会自动清除磁盘上的归档日志文件，同时会释放控制文件中对应的归档日志的归档信息。可以基于不同的条件来清除归档日志，如基于SCN，基于SEQUENCE，基于TIME等方式。对于上述的三种方式又可以配合from, until, between .. and .. 等等子句来限定范围，方式灵活多变。下面的命令用于校验归档日志的有效性，列出无效的归档日志，以及以何种方式清除归档日志，列出几种常用的: crosscheck archivelog all; --->校验日志的可用性list expired archivelog all; --->列出所有失效的归档日志delete archivelog until sequence 16; --->删除log sequence为16及16之前的所有归档日志delete archivelog all completed before 'sysdate-7'; --->删除系统时间7天以前的归档日志，不会删除闪回区有效的归档日志delete archivelog all completed before 'sysdate - 1'; --->同上，1天以前的delete archivelog from time 'sysdate-1';--->注意这个命令，删除系统时间1天以内到现在的归档日志delete noprompt archivelog all completed before 'sysdate'; --->该命令清除所有的归档日志delete noprompt archivelog all;--->同上一命令##### 本人一般会删除7天以内的delete archivelog all completed before 'sysdate-7'; --->删除系统时间7天以前的归档日志，不会删除闪回区有效的归档日志

上述描述摘自：CSDN博客，可用于参考

[oracle@lbs archivelog]$ sqlplus /nolog #进入sqlplus 查看归档空间使用率和文件数量SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 09:54:12 Copyright (c) 1982, , Oracle. All rights reserved.SQL> conn / as sysdbaConnected.SQL> alter system set db_recovery_file_dest_size=100G scope=both;System altered.SQL> show parameter recover;NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_deststring/home/u01/app/oracle/recovery_areadb_recovery_file_dest_size big integer 100Grecovery_parallelism integer0SQL> select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES-------------------- ------------------ ---------------CONTROL FILE00REDO LOG00ARCHIVED LOG 40.15 1009BACKUP PIECE00IMAGE COPY 00FLASHBACK LOG00FOREIGN ARCHIVED LOG 007 rows selected.SQL>exit;[oracle@lbs ~]$ rman target / #进入rmanRecovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 7 10:23:59 Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1368157747)RMAN> crosscheck archivelog all;#校验日志的可用性using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=243 device type=DISKvalidation failed for archived logarchived log file name=/home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86515_k4qp0zqs_.arc RECID=58 STAMP=1101217701validation failed for archived logarchived log file name=/home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86516_k4qp2nq3_.arc RECID=59 STAMP=1101217759validation failed for archived logarchived log file name=/home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86517_k4qp3h3m_.arc RECID=60 STAMP=1101217777......archived log file name=/home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_07/o1_mf_1_87552_k4wls01d_.arc RECID=1095 STAMP=1101378215Crosschecked 1038 objectsRMAN> list expired archivelog all; #列出所有失效的归档日志List of Archived Log Copies for database with db_unique_name ORCL=====================================================================KeyThrd SeqS Low Time ------- ---- ------- - ---------581 86515 X 05-APR-22Name: /home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86515_k4qp0zqs_.arc591 86516 X 05-APR-22Name: /home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86516_k4qp2nq3_.arc......2501 86707 X 05-APR-22Name: /home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86707_k4rccl97_.arc2511 86708 X 05-APR-22Name: /home/u01/app/oracle/recovery_area/ORCL/archivelog/_04_05/o1_mf_1_86708_k4rcdsy1_.arcRMAN> delete archivelog until sequence 86708; #删除log sequence为86708及86708之前的所有归档日志,此处最大的seq是86708RMAN> exitRecovery Manager complete.[oracle@lbs ~]$ sqlplus /nolog #回到sqlplus查看归档空间利用率和归档文件数量SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 10:44:03 Copyright (c) 1982, , Oracle. All rights reserved.SQL> conn / as sysdbaConnected.SQL> show parameter recover;NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_deststring/home/u01/app/oracle/recovery_areadb_recovery_file_dest_size big integer 100Grecovery_parallelism integer0SQL> select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES-------------------- ------------------ ---------------CONTROL FILE00REDO LOG00ARCHIVED LOG 34.39 875BACKUP PIECE00IMAGE COPY 00FLASHBACK LOG00FOREIGN ARCHIVED LOG 007 rows selected.

日志清除成功！

FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES-------------------- ------------------ ---------------ARCHIVED LOG 40.15 1009 --->ARCHIVED LOG 34.39 875## 3.修改归档空间大小具体根据你的服务器容量大小而定，我的还剩3.2T，所以设置成100G完全没问题```shell[oracle@lbs archivelog]$ df -hl文件系统容量已用可用已用%% 挂载点/dev/mapper/vg_lbs-lv_root50G 13G 35G 26% /tmpfs 16G 2.1G 14G 14% /dev/shm/dev/sda1 485M 38M 422M 9% /boot/dev/mapper/vg_lbs-lv_home5.4T 2.0T 3.2T 39% /home[oracle@lbs archivelog]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 09:54:12 Copyright (c) 1982, , Oracle. All rights reserved.SQL> conn / as sysdbaConnected.SQL> alter system set db_recovery_file_dest_size=100G scope=both;System altered.SQL> show parameter recover;NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_deststring/home/u01/app/oracle/recovery_areadb_recovery_file_dest_size big integer 100Grecovery_parallelism integer0SQL> select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES-------------------- ------------------ ---------------CONTROL FILE00REDO LOG00ARCHIVED LOG 40.15 1009BACKUP PIECE00IMAGE COPY 00FLASHBACK LOG00FOREIGN ARCHIVED LOG 007 rows selected.