Oracle RMAN 清除归档日志

Oracle RMAN 清除归档日志
在开发环境及UAT环境经常碰到需要清除归档日志的情形，对于这个问题方法有很多。可以直接使用rm方式清除归档日志，也可以使用find命令来查找符合条件的记录来清除归档日志，或者直接写个shell脚本来搞定。这样在DEV或者UAT还可以，但是在Prod环境还是建议使用RMAN提供的命令来搞定比较妥当。因为rm，find方式删除了实际的归档日志也释放了空间，但对应的存储在控制文件中的归档信息并没有彻底清除。依旧占用着一些空间未能及时清除而需要控制文件通过age out方式来释放空间。本文描述了使用RMAN方式来清除归档日志，同时也可以将其部署到shell脚本中使用。

1、清除归档日志的方式

a、手动删除
      使用rm 或者find方式来删除，通过该方式删除之后，在RMAN下可以通过 crosscheck archivelog all 校验归档是否失效，如下面的操作:
      rm -rf arch_816906485_1_10.arc
    find /u02/database/GOBO1/archive/ -ctime +0 -delete
   RMAN> crosscheck archivelog all

b、使用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;                              --->同上一命令

2、演练使用RMAN清除归档日志
[python] view plain copy

print ?
1. robin@SZDB:~> export ORACLE_SID=GOBO1
2. robin@SZDB:~> rman target /
4. Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jul 11 17:07:00 2013
6. Copyright (c) 1982, 2005, Oracle. All rights reserved.
8. connected to target database: GOBO1 (DBID=733951103)
10. RMAN> host;
12. robin@SZDB:~> cd /u02/database/GOBO1/archive/
13. robin@SZDB:/u02/database/GOBO1/archive> ls
14. arch_816906485_1_10.arc arch_816906485_1_12.arc
15. arch_816906485_1_11.arc arch_816906485_1_13.arc
16. ............
18. robin@SZDB:/u02/database/GOBO1/archive> rm -rf arch_816906485_1_10.arc arch_816906485_1_11.arc arch_816906485_1_12.arc
19. robin@SZDB:/u02/database/GOBO1/archive> exit;
20. exit
21. host command complete
23. RMAN> crosscheck archivelog all;
24. released channel: ORA_DISK_1
25. allocated channel: ORA_DISK_1
26. channel ORA_DISK_1: sid=1075 devtype=DISK
27. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_9.arc recid=2085 stamp=817211151
28. validation failed for archived log
29. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_10.arc recid=2086 stamp=817250793
30. ..............
31. validation succeeded for archived log
32. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_90.arc recid=2166 stamp=820458049
33. Crosschecked 83 objects
35. RMAN> list expired archivelog all;
37. List of Archived Log Copies
38. Key Thrd Seq S Low Time Name
39. ------- ---- ------- - ----------------- ----
40. 2086 1 10 X 20130604 11:05:51 /u02/database/GOBO1/archive/arch_816906485_1_10.arc
41. 2087 1 11 X 20130604 22:06:17 /u02/database/GOBO1/archive/arch_816906485_1_11.arc
42. 2088 1 12 X 20130605 19:30:53 /u02/database/GOBO1/archive/arch_816906485_1_12.arc
44. RMAN> delete archivelog until sequence 16;
46. released channel: ORA_DISK_1
47. allocated channel: ORA_DISK_1
48. channel ORA_DISK_1: sid=1075 devtype=DISK
50. List of Archived Log Copies
51. Key Thrd Seq S Low Time Name
52. ------- ---- ------- - ----------------- ----
53. 2084 1 8 A 20130604 09:53:17 /u02/database/GOBO1/archive/arch_816906485_1_8.arc
54. .................
55. 2092 1 16 A 20130607 22:03:23 /u02/database/GOBO1/archive/arch_816906485_1_16.arc
57. Do you really want to delete the above objects (enter YES or NO)? yes
58. ...............
59. deleted archive log
60. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_16.arc recid=2092 stamp=817516861
61. Deleted 9 objects
63. RMAN> delete archivelog all completed before 'sysdate-7';
65. released channel: ORA_DISK_1
66. allocated channel: ORA_DISK_1
67. channel ORA_DISK_1: sid=1075 devtype=DISK
69. List of Archived Log Copies
70. Key Thrd Seq S Low Time Name
71. ------- ---- ------- - ----------------- ----
72. 2093 1 17 A 20130608 00:01:00 /u02/database/GOBO1/archive/arch_816906485_1_17.arc
73. 2094 1 18 A 20130608 18:00:17 /u02/database/GOBO1/archive/arch_816906485_1_18.arc
74. ...........
75. deleted archive log
76. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_72.arc recid=2148 stamp=819847035
77. Deleted 56 objects
79. RMAN> list copy of database archivelog all;
82. List of Archived Log Copies
83. Key Thrd Seq S Low Time Name
84. ------- ---- ------- - ----------------- ----
85. 2149 1 73 A 20130703 23:17:13 /u02/database/GOBO1/archive/arch_816906485_1_73.arc
86. 2150 1 74 A 20130704 22:00:19 /u02/database/GOBO1/archive/arch_816906485_1_74.arc
87. 2151 1 75 A 20130704 22:04:40 /u02/database/GOBO1/archive/arch_816906485_1_75.arc
88. ...............
89. 2164 1 88 A 20130709 23:19:34 /u02/database/GOBO1/archive/arch_816906485_1_88.arc
90. 2165 1 89 A 20130710 13:00:34 /u02/database/GOBO1/archive/arch_816906485_1_89.arc
91. 2166 1 90 A 20130710 22:02:44 /u02/database/GOBO1/archive/arch_816906485_1_90.arc
93. RMAN> delete archivelog from time 'sysdate-1';
95. released channel: ORA_DISK_1
96. allocated channel: ORA_DISK_1
97. channel ORA_DISK_1: sid=1075 devtype=DISK
99. List of Archived Log Copies
100. Key Thrd Seq S Low Time Name
101. ------- ---- ------- - ----------------- ----
102. 2165 1 89 A 20130710 13:00:34 /u02/database/GOBO1/archive/arch_816906485_1_89.arc
103. 2166 1 90 A 20130710 22:02:44 /u02/database/GOBO1/archive/arch_816906485_1_90.arc
105. Do you really want to delete the above objects (enter YES or NO)? yes
106. deleted archive log
107. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_89.arc recid=2165 stamp=820447373
108. deleted archive log
109. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_90.arc recid=2166 stamp=820458049
110. Deleted 2 objects
112. RMAN> delete archivelog all completed before 'sysdate - 1';
114. released channel: ORA_DISK_1
115. allocated channel: ORA_DISK_1
116. channel ORA_DISK_1: sid=1075 devtype=DISK
118. List of Archived Log Copies
119. Key Thrd Seq S Low Time Name
120. ------- ---- ------- - ----------------- ----
121. 2149 1 73 A 20130703 23:17:13 /u02/database/GOBO1/archive/arch_816906485_1_73.arc
122. .......................
123. 2164 1 88 A 20130709 23:19:34 /u02/database/GOBO1/archive/arch_816906485_1_88.arc
125. Do you really want to delete the above objects (enter YES or NO)? yes
126. ................
127. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_88.arc recid=2164 stamp=820414835
128. Deleted 16 objects
130. RMAN> sql " alter system archive log current";
132. sql statement: alter system archive log current
134. RMAN> list copy of archivelog all;
137. List of Archived Log Copies
138. Key Thrd Seq S Low Time Name
139. ------- ---- ------- - ----------------- ----
140. 2167 1 91 A 20130711 01:00:48 /u02/database/GOBO1/archive/arch_816906485_1_91.arc
142. RMAN> delete noprompt archivelog all completed before 'sysdate';
144. released channel: ORA_DISK_1
145. allocated channel: ORA_DISK_1
146. channel ORA_DISK_1: sid=1075 devtype=DISK
148. List of Archived Log Copies
149. Key Thrd Seq S Low Time Name
150. ------- ---- ------- - ----------------- ----
151. 2167 1 91 A 20130711 01:00:48 /u02/database/GOBO1/archive/arch_816906485_1_91.arc
152. deleted archive log
153. archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_91.arc recid=2167 stamp=820517964
154. Deleted 1 objects
3、清除归档日志简单的shell脚本
[python] view plain copy

print ?
1. #对于RAC环境或者ASM需要清除archive，使用shell脚本调用RMAN是比较妥当的方式
2. #其次，如果你的archive位于闪回区，制定合理的保留策略，也可以让Oracle自动老化无用的归档日志
3. robin@SZDB:~/dba_scripts/custom/bin> more clean_arch.sh
4. # +-------------------------------------------------------+
5. # + Clean archived log as specified time |
6. # + Author : Robinson |
7. # + Blog : http://blog.csdn.net/robinson_0612 |
8. # + Usage : |
9. # + clean_arch.sh $ORACLE_SID |
10. # +-------------------------------------------------------+
11. #
12. #!/bin/bash
13. # --------------------
14. # Define variable
15. # --------------------
17. if [ -f ~/.bash_profile ]; then
18. . ~/.bash_profile
19. fi
21. if [ -z "${1}" ];then
22. echo "Usage: "
23. echo " `basename $0` ORACLE_SID"
24. exit 1
25. fi
27. ORACLE_SID=$1; export ORACLE_SID
28. $ORACLE_HOME/bin/rman log=/users/robin/log/rman.log <<EOF
29. connect target /
30. run{
31. crosscheck archivelog all;
32. delete noprompt expired archivelog all;
33. delete noprompt archivelog all completed before 'sysdate - 1';
34. }
35. exit;
36. EOF
37. exit
4、小结
a、归档日志清除的方法最好是在RMAN方式下完成，这样子是最彻底的清除方式
b、对于生产环境应考虑在RMAN备份的时候清除归档日志，如backup archivelog all时使用delete input与delete all input清除归档日志
c、如果备份期间不清除归档日志则arch会很大，造成归档磁盘满而导致归档失败。建议还是删除或考虑存放到闪回区
d、如果清除大部分又想保留最近的，则使用delete noprompt archivelog all completed before 'sysdate - n'方式
e、详细的清除归档日志语法: http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta008.htm#RCMRF106
相关阅读:
java实现趣味拼算式
 windows下安装docker
Docker_入门？只要这篇就够了！（纯干货适合0基础小白）
网关支付、银联代扣通道、快捷支付、银行卡支付分别是怎么样进行支付的？
【深度解析】第三方支付的分类、接口与支付流程
 去外包公司的伙伴们小心了！——亲身经历，数数外包公司的坑
 一个tomcat下部署多个项目或一个服务器部署多个tomcat
tomcat部署web应用的4种方法以及部署多个应用
 datatables增删改查的实现
 基于SpringMVC+Bootstrap+DataTables实现表格服务端分页、模糊查询
原文地址：https://www.cnblogs.com/fiberhome/p/7698254.html