[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有点鸡肋，无法定位会话。