[20160712]logminer 与rman 删除archivelog

[20160712]logminer 与rman 删除archivelog.txt

--昨天晚上使用logminer遇到的问题，主要后台的rman脚本正好删除需要的archivelog。
--再测试环境下模拟看看：

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2016-07-12 11:19:12

2.logminer：

SYS@book> alter system archive log current ;
System altered.

$ cd /u01/app/oracle/archivelog/book
$ ls -l
total 248056
-rw-r----- 1 oracle oinstall 38988288 2016-07-10 13:10:21 1_343_907434361.dbf
-rw-r----- 1 oracle oinstall 46585344 2016-07-10 22:04:44 1_344_907434361.dbf
-rw-r----- 1 oracle oinstall 46585344 2016-07-11 08:24:54 1_345_907434361.dbf
-rw-r----- 1 oracle oinstall  3506688 2016-07-11 08:40:09 1_346_907434361.dbf
-rw-r----- 1 oracle oinstall     2048 2016-07-11 08:41:56 1_347_907434361.dbf
-rw-r----- 1 oracle oinstall 43033600 2016-07-11 22:00:12 1_348_907434361.dbf
-rw-r----- 1 oracle oinstall 42801664 2016-07-12 08:26:40 1_349_907434361.dbf
-rw-r----- 1 oracle oinstall 32196608 2016-07-12 11:15:47 1_350_907434361.dbf

BEGIN
   DBMS_LOGMNR.START_LOGMNR
   (
      STARTTIME   => '2016-07-10 10:00:00'
     ,ENDTIME     => '2016-07-11 11:00:00'
     ,OPTIONS     =>   DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
                     + DBMS_LOGMNR.CONTINUOUS_MINE
                     + DBMS_LOGMNR.COMMITTED_DATA_ONLY
   );
END;
/

SCOTT@book> @ &r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        68        477 52355       30        198 alter system kill session '68,477' immediate;

select * from V$LOGMNR_CONTENTS where rownum<=100000;
...

# lsof  | grep archivelog
oracle    52355  oracle  258u      REG              104,6   38988288  306348091 /u01/app/oracle/archivelog/book/1_343_907434361.dbf

--对上进程号!!打开rman删除sequence 343的archivelog。

3.使用rman删除对应archivelog：

RMAN> delete archivelog sequence 343;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=145 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=156 device type=DISK
List of Archived Log Copies for database with db_unique_name BOOK
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
204     1    343     A 2016-07-10 08:24:16
        Name: /u01/app/oracle/archivelog/book/1_343_907434361.dbf

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/archivelog/book/1_343_907434361.dbf RECID=204 STAMP=916837821
Deleted 1 objects

# lsof  | grep archivelog
oracle    52355  oracle  258u      REG              104,6   38988288  306348091 /u01/app/oracle/archivelog/book/1_343_907434361.dbf (deleted)

--中断会话，再次执行：

SCOTT@book> select * from V$LOGMNR_CONTENTS where rownum<=100000;
select * from V$LOGMNR_CONTENTS where rownum<=100000
*
ERROR at line 1:
ORA-01291: missing logfile
ORA-16241: Waiting for gap log file (thread# 1, sequence# 1)

--alert.log记录如下：
LOGMINER: summary for session# = 2147484417
LOGMINER: StartScn: 0 (0x0000.00000000)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag: 0x0
LOGMINER: Read buffers: 8
LOGMINER: Memory LWM: limit 10M, LWM 8M, 80%
LOGMINER: Memory Release Limit: 0M
LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 343, /u01/app/oracle/archivelog/book/1_343_907434361.dbf
Tue Jul 12 11:26:21 2016
LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 343, /u01/app/oracle/archivelog/book/1_343_907434361.dbf

--重新来：

BEGIN
   DBMS_LOGMNR.START_LOGMNR
   (
      STARTTIME   => '2016-07-10 10:00:00'
     ,ENDTIME     => '2016-07-11 11:00:00'
     ,OPTIONS     =>   DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
                     + DBMS_LOGMNR.CONTINUOUS_MINE
                     + DBMS_LOGMNR.COMMITTED_DATA_ONLY
   );
END;
/

SCOTT@book> select * from V$LOGMNR_CONTENTS where rownum<=100000;
select * from V$LOGMNR_CONTENTS where rownum<=100000
*
ERROR at line 1:
ORA-01291: missing logfile
ORA-16241: Waiting for gap log file (thread# 1, sequence# 1)

--看来最佳的方式执行是马上执行:
create table tt tablespace xxxx as select * from V$LOGMNR_CONTENTS ;

--实际上如果是使用asm，根本无法删除，而是在rman中报ORA-15028错误。

$ oerr ora 15028
15028, 00000, "ASM file '%s' not dropped; currently being accessed"
// *Cause:  An attempt was made to drop an ASM file, but the file was
//          being accessed by one or more clients and therefore could
//          not be dropped.
// *Action: Stop all clients that are using this file and retry the drop
//          command. Query the V$ASM_CLIENT fixed view in an ASM instance
//          or use ASMCMD LSOF to list active clients.
//

不过asmcmd lsof有点鸡肋，无法定位会话。