如何删除彻底归档日志文件
--测试环境：
--虚拟机下 Redhat5.4(bit)
--Oracle 11G R2(64bit)
--注意：语句只能在实验环境下执行，切勿在生产环境下执行！
ps:
--这个符号是解释
有颜色字体标示是需要注意的地方
--以下是我的操作步骤，大家可以一步一步跟练习，由于每个命令都有解析，所以就算没有什么oracle基础也行
--由于本人水平有限，也是处于学习充电阶段，大家有什么问题欢迎提出哦，咱们一起解决，嘿嘿
[root@orcl ~]# su – oracle
--设置一下ORACLE_SID
[oracle@orcl ~]$ export ORACLE_SID=denver
--查看一下denver实例是否启动
[oracle@orcl ~]$ ps -ef|grep oracle
root 27264 6887 0 04:23 tty1 00:00:00 su - oracle
oracle 27265 27264 0 04:23 tty1 00:00:00 -bash
root 29670 29641 0 09:43 pts/1 00:00:00 su - oracle
oracle 29671 29670 0 09:43 pts/1 00:00:00 -bash
oracle 29754 1 0 09:50 ? 00:00:00/u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
root 29868 29807 0 10:00 pts/3 00:00:00 su - oracle
oracle 29869 29868 0 10:00 pts/3 00:00:00 -bash
oracle 30135 29869 0 10:30 pts/3 00:00:00 rlwrap sqlplus / as sysdba
oracle 30136 30135 0 10:30 pts/2 00:00:00 sqlplus as sysdba
oracle 30198 1 0 10:33 ? 00:00:00 ora_pmon_denver
oracle 30200 1 0 10:33 ? 00:00:02 ora_vktm_denver
oracle 30204 1 0 10:33 ? 00:00:00 ora_gen0_denver
oracle 30206 1 0 10:33 ? 00:00:00 ora_diag_denver
oracle 30208 1 0 10:33 ? 00:00:00 ora_dbrm_denver
oracle 30210 1 0 10:33 ? 00:00:00 ora_psp0_denver
oracle 30212 1 0 10:33 ? 00:00:01 ora_dia0_denver
oracle 30214 1 0 10:33 ? 00:00:02 ora_mman_denver
oracle 30216 1 0 10:33 ? 00:00:00 ora_dbw0_denver
oracle 30218 1 0 10:33 ? 00:00:00 ora_lgwr_denver
oracle 30220 1 0 10:33 ? 00:00:01 ora_ckpt_denver
oracle 30222 1 0 10:33 ? 00:00:01 ora_smon_denver
oracle 30224 1 0 10:33 ? 00:00:00 ora_reco_denver
oracle 30226 1 0 10:33 ? 00:00:01 ora_mmon_denver
oracle 30229 1 0 10:33 ? 00:00:00 ora_mmnl_denver
oracle 30231 1 0 10:33 ? 00:00:00 ora_d000_denver
oracle 30233 1 0 10:33 ? 00:00:00 ora_s000_denver
oracle 30279 30136 0 10:33 ? 00:00:02 oracledenver (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 30284 1 0 10:34 ? 00:00:00 ora_arc0_denver
oracle 30286 1 0 10:34 ? 00:00:00 ora_arc1_denver
oracle 30288 1 0 10:34 ? 00:00:00 ora_arc2_denver
oracle 30290 1 010:34 ? 00:00:00 ora_arc3_denver
oracle 30292 1 0 10:34 ? 00:00:00 ora_qmnc_denver
oracle 30311 1 0 10:34 ? 00:00:00 ora_cjq0_denver
oracle 30313 1 0 10:34 ? 00:00:00 ora_q000_denver
oracle 30315 1 010:34 ? 00:00:00 ora_q001_denver
oracle 30352 1 0 10:39 ? 00:00:00 ora_smco_denver
oracle 30354 1 0 10:39 ? 00:00:00 ora_w000_denver
oracle 30381 29671 0 10:43 pts/1 00:00:00 rlwrap rman target /
oracle 30382 30381 0 10:43 pts/0 00:00:04 rman target /
oracle 30387 30382 0 10:43 ? 00:00:03 oracledenver(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 30390 30382 0 10:43 ? 00:00:00 oracledenver(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 30391 30382 0 10:43 ? 00:00:00 oracledenver(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 30435 30406 0 10:45 pts/4 00:00:00 su - oracle
oracle 30436 30435 0 10:45 pts/4 00:00:00 -bash
root 30661 30625 0 11:05 pts/5 00:00:00 su - oracle
oracle 30662 30661 0 11:05 pts/5 00:00:00 -bash
oracle 30696 30662 0 11:05 pts/5 00:00:00 ps -ef
oracle 30697 30662 0 11:05 pts/5 00:00:00 grep oracle
[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production onFri Aug 8 11:06:06 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
--查看当前的归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
--下面的步骤是把归档模式更改为非归档模式，
--首先关了数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--将数据库启动到mount的模式下，这种模式下只打开控制文件，不打开数据文件
SQL> startup mount;
ORACLE instance started.
Total System Global Area 556584960 bytes
Fixed Size 1337860 bytes
Variable Size 373294588 bytes
Database Buffers 176160768 bytes
Redo Buffers 5791744 bytes
Database mounted.
--把数据库切换为非归档模式
SQL> alter database noarchivelog;
Database altered.
--把数据库打开，此时打开数据文件
SQL> alter database open;
Database altered.
--查看归档的模式，此时就从归档模式à非归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
--下面的步骤是从非归档模式à归档模式,方法和上面的类似
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 556584960 bytes
Fixed Size 1337860 bytes
Variable Size 373294588 bytes
Database Buffers 176160768 bytes
Redo Buffers 5791744 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
--此时就是归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
--看一下数据库是否位于archivelog模式中，并且归档过程正在进行
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select archiver from v$instance;
ARCHIVE
-------
STARTED
--查看归档日志的位置，由于是第一次切换到归档，所以没有文件
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
--日志切换，这里是手动切换日志，会产生一个归档日志
SQL> alter system switch logfile;
System altered.
--上一步由于产生了一个归档日志，所以在这里能查到，并且能看到具体的位置
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5
wgj_.arc
--下面多产生几个归档日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
--这里可以看一下归档日志的位置
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5
wgj_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_9_9y8j6
o3g_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_10_9y8j
6t3q_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_11_9y8j
NAME
--------------------------------------------------------------------------------
6yhd_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_12_9y8j
706o_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_13_9y8j
7166_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_14_9y8j
7335_.arc
8 rows selected.
SQL> !
--切换到那个目录，查看一下归档文件
[oracle@orcl ~]$ cd/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/
[oracle@orcl 2014_08_08]$ ls
o1_mf_1_10_9y8j6t3q_.arc o1_mf_1_13_9y8j7166_.arc o1_mf_1_9_9y8j6o3g_.arc
o1_mf_1_11_9y8j6yhd_.arc o1_mf_1_14_9y8j7335_.arc
o1_mf_1_12_9y8j706o_.arc o1_mf_1_8_9y8j5wgj_.arc
鎬昏 2608
-rw-r----- 1 oracle oinstall 2048 08-08 11:15 o1_mf_1_10_9y8j6t3q_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_11_9y8j6yhd_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_12_9y8j706o_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_13_9y8j7166_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_14_9y8j7335_.arc
-rw-r----- 1 oracle oinstall 2638848 08-0811:15 o1_mf_1_8_9y8j5wgj_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_9_9y8j6o3g_.arc
--通过rman对归档日志进行管理，下面是测试crosscheck archivelog all和delete expired --archivelog all这两个命令
--连接rman
[oracle@orcl 2014_08_08]$ rman target /
Recovery Manager: Release 11.2.0.1.0 -Production on Fri Aug 8 11:18:08 2014
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: DENVER(DBID=4143344931)
--检查无效的归档日志，succeeded表示成功，文件都有效
RMAN> crosscheck archivelog all;
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5wgj_.arcRECID=2 STAMP=855054908
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_9_9y8j6o3g_.arcRECID=3 STAMP=855054933
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_10_9y8j6t3q_.arcRECID=4 STAMP=855054938
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_11_9y8j6yhd_.arcRECID=5 STAMP=855054942
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_12_9y8j706o_.arcRECID=6 STAMP=855054944
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_13_9y8j7166_.arcRECID=7 STAMP=855054945
validation succeeded for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_14_9y8j7335_.arcRECID=8 STAMP=855054947
Crosschecked 7 objects
--这个命令是删除无效的归档日志，由于上面检查的没有无效的，所以这里什么也没有删除
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
specification does not match any archivedlog in the repository
RMAN> exit
Recovery Manager complete.
[oracle@orcl 2014_08_08]$ ls
o1_mf_1_10_9y8j6t3q_.arc o1_mf_1_13_9y8j7166_.arc o1_mf_1_9_9y8j6o3g_.arc
o1_mf_1_11_9y8j6yhd_.arc o1_mf_1_14_9y8j7335_.arc
o1_mf_1_12_9y8j706o_.arc o1_mf_1_8_9y8j5wgj_.arc
[oracle@orcl 2014_08_08]$ ll
鎬昏 2608
-rw-r----- 1 oracle oinstall 2048 08-08 11:15 o1_mf_1_10_9y8j6t3q_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_11_9y8j6yhd_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_12_9y8j706o_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_13_9y8j7166_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_14_9y8j7335_.arc
-rw-r----- 1 oracle oinstall 2638848 08-0811:15 o1_mf_1_8_9y8j5wgj_.arc
-rw-r----- 1 oracle oinstall 1024 08-08 11:15 o1_mf_1_9_9y8j6o3g_.arc
--把归档日志的物理文件都删除
[oracle@orcl 2014_08_08]$ rm *.arc
鎬昏 0
[oracle@orcl 2014_08_08]$ exit
exit
--在删除归档文件的物理文件之后，在oracle里面可以查到
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5
wgj_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_9_9y8j6
o3g_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_10_9y8j
6t3q_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_11_9y8j
NAME
--------------------------------------------------------------------------------
6yhd_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_12_9y8j
706o_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_13_9y8j
7166_.arc
/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_14_9y8j
7335_.arc
8 rows selected.
SQL> !
[oracle@orcl ~]$ cd/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/
--此时归档日志的物理文件已经删除完成，查看不到了
[oracle@orcl 2014_08_08]$ ll
鎬昏 0
--连接到rman
[oracle@orcl 2014_08_08]$ rman target /
Recovery Manager: Release 11.2.0.1.0 -Production on Fri Aug 8 11:20:35 2014
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: DENVER(DBID=4143344931)
--现在检查无效的归档日志文件，failed表示就是无效的
RMAN> crosscheck archivelog all;
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5wgj_.arcRECID=2 STAMP=855054908
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_9_9y8j6o3g_.arcRECID=3 STAMP=855054933
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_10_9y8j6t3q_.arcRECID=4 STAMP=855054938
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_11_9y8j6yhd_.arcRECID=5 STAMP=855054942
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_12_9y8j706o_.arcRECID=6 STAMP=855054944
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_13_9y8j7166_.arcRECID=7 STAMP=855054945
validation failed for archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_14_9y8j7335_.arcRECID=8 STAMP=855054947
Crosschecked 7 objects
--把上一步检查到了无效的归档日志都删除，要删除输入 yes
--此处删除的其实是数据库控制文件里面对归档日志的记录，这里删除之后，在oracle里面就查不到归--档日志的信息了
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
List of Archived Log Copies for databasewith db_unique_name DENVER
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 8 X 08-AUG-14
Name: /u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5wgj_.arc
3 1 9 X 08-AUG-14
Name:/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_9_9y8j6o3g_.arc
4 1 10 X 08-AUG-14
Name:/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_10_9y8j6t3q_.arc
5 1 11 X 08-AUG-14
Name:/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_11_9y8j6yhd_.arc
6 1 12 X 08-AUG-14
Name:/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_12_9y8j706o_.arc
7 1 13 X 08-AUG-14
Name: /u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_13_9y8j7166_.arc
8 1 14 X 08-AUG-14
Name:/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_14_9y8j7335_.arc
Do you really want to delete the aboveobjects (enter YES or NO)? yes
deleted archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_8_9y8j5wgj_.arcRECID=2 STAMP=855054908
deleted archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_9_9y8j6o3g_.arcRECID=3 STAMP=855054933
deleted archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_10_9y8j6t3q_.arcRECID=4 STAMP=855054938
deleted archived log
archived log file name=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_11_9y8j6yhd_.arcRECID=5 STAMP=855054942
deleted archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_12_9y8j706o_.arcRECID=6 STAMP=855054944
deleted archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_13_9y8j7166_.arcRECID=7 STAMP=855054945
deleted archived log
archived log filename=/u01/app/oracle/flash_recovery_area/DENVER/archivelog/2014_08_08/o1_mf_1_14_9y8j7335_.arcRECID=8 STAMP=855054947
Deleted 7 EXPIRED objects
RMAN> exit
Recovery Manager complete.
[oracle@orcl 2014_08_08]$ ll
鎬昏 0
[oracle@orcl 2014_08_08]$ exit
exit
--此时再查看的话oracle里面就看不到归档日志的信息了
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
8 rows selected.
SQL>
转载于:https://blog.51cto.com/wangjingshuai/1537362
扫一扫
1731
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
