logminer

参考视频:

[bbk1260]

[bbk1261]

objectives

  • analyze redo logfile
  • incomplete recover database

steps

  • backup database
  • modify spfile
  • restart database
  • manipulate database,switch logfile
  • use logmnr
  • analyze redo logfile
  • inmplete recover datavase
  • verify recover

modify spfile

  • mkdir /logmnr
  • chown -R oracle:dba /logmnr
  • alter system set utl_file_dir='/logmnr' scope='spfile' side='*';
  • shutdown databae;
  • startup;

模拟误操作

  • insert
  • drop
  • insert
  • switch logfile

use logmnr

  • create dictionary
  • add logfile
  • start analyze
  • create table as select * from v$logmnr_contents
  • end logmnr
  • find exact drop time
[root@11203ora /]# mkdir logmnr
[root@11203ora /]# ll
total 15240
dr-xr-xr-x.   2 root   root         4096 Jul 12 21:30 bin
dr-xr-xr-x.   4 root   root         4096 Jul  9 21:01 boot
drwxr-xr-x.   2 root   root         4096 Jun 25 23:04 cgroup
drwxr-xr-x   16 root   root         3860 Jul 26 12:38 dev
drwxr-xr-x. 104 root   root        12288 Jul 26 12:38 etc
drwxr-xr-x.   3 root   root         4096 Jul  9 22:04 home
-rwxr-xr-x    1 root   root     15485212 Jul 26 08:21 j2re-1_3_1_19-linux-i586.bin
dr-xr-xr-x.  13 root   root         4096 Jul 12 21:30 lib
dr-xr-xr-x.   9 root   root        12288 Jul 12 21:30 lib64
drwxr-xr-x    2 root   root         4096 Jul 29 12:31 logmnr
drwx------.   2 root   root        16384 Jul  9 19:01 lost+found
drwxr-xr-x.   2 root   root         4096 Nov  1  2011 media
drwxr-xr-x    2 root   root            0 Jul 26 12:38 misc
drwxr-xr-x.   2 root   root         4096 Nov  1  2011 mnt
drwxr-xr-x    2 root   root            0 Jul 26 12:38 net
drwxr-xr-x.   4 root   root         4096 Jul  9 22:27 opt
dr-xr-xr-x  125 root   root            0 Jul 26 12:38 proc
dr-xr-x---.   5 root   root         4096 Jul 26 08:57 root
dr-xr-xr-x.   2 root   root        12288 Jul 12 21:30 sbin
drwxr-xr-x.   2 root   root         4096 Jul  9 19:02 selinux
drwxr-xr-x.   2 root   root         4096 Nov  1  2011 srv
drwxr-xr-x   13 root   root            0 Jul 26 12:38 sys
drwxrwxrwt.   4 root   root         4096 Jul 29 03:39 tmp
drwxrwxr-x    3 oracle oinstall     4096 Jul  9 22:05 u01
drwxr-xr-x.  13 root   root         4096 Jul  9 19:03 usr
drwxr-xr-x.  21 root   root         4096 Jul  9 19:12 var
[root@11203ora /]# chown -R oracle:dba /logmnr/
[root@11203ora /]# ll
total 15240
dr-xr-xr-x.   2 root   root         4096 Jul 12 21:30 bin
dr-xr-xr-x.   4 root   root         4096 Jul  9 21:01 boot
drwxr-xr-x.   2 root   root         4096 Jun 25 23:04 cgroup
drwxr-xr-x   16 root   root         3860 Jul 26 12:38 dev
drwxr-xr-x. 104 root   root        12288 Jul 26 12:38 etc
drwxr-xr-x.   3 root   root         4096 Jul  9 22:04 home
-rwxr-xr-x    1 root   root     15485212 Jul 26 08:21 j2re-1_3_1_19-linux-i586.bin
dr-xr-xr-x.  13 root   root         4096 Jul 12 21:30 lib
dr-xr-xr-x.   9 root   root        12288 Jul 12 21:30 lib64
drwxr-xr-x    2 oracle dba          4096 Jul 29 12:31 logmnr
drwx------.   2 root   root        16384 Jul  9 19:01 lost+found
drwxr-xr-x.   2 root   root         4096 Nov  1  2011 media
drwxr-xr-x    2 root   root            0 Jul 26 12:38 misc
drwxr-xr-x.   2 root   root         4096 Nov  1  2011 mnt
drwxr-xr-x    2 root   root            0 Jul 26 12:38 net
drwxr-xr-x.   4 root   root         4096 Jul  9 22:27 opt
dr-xr-xr-x  127 root   root            0 Jul 26 12:38 proc
dr-xr-x---.   5 root   root         4096 Jul 26 08:57 root
dr-xr-xr-x.   2 root   root        12288 Jul 12 21:30 sbin
drwxr-xr-x.   2 root   root         4096 Jul  9 19:02 selinux
drwxr-xr-x.   2 root   root         4096 Nov  1  2011 srv
drwxr-xr-x   13 root   root            0 Jul 26 12:38 sys
drwxrwxrwt.   4 root   root         4096 Jul 29 03:39 tmp
drwxrwxr-x    3 oracle oinstall     4096 Jul  9 22:05 u01
drwxr-xr-x.  13 root   root         4096 Jul  9 19:03 usr
drwxr-xr-x.  21 root   root         4096 Jul  9 19:12 var
[root@11203ora /]#
创建logmnr文件目录 
SQL> alter system set utl_file_dir='/logmnr' scope=spfile;

System altered.

SQL>
修改spfile
[root@11203ora /]# su - oracle
11203ora-> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 29 12:35:19 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size                  2229064 bytes
Variable Size             335547576 bytes
Database Buffers           83886080 bytes
Redo Buffers                8413184 bytes
Database mounted.
Database opened.
SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /logmnr
SQL>
重启数据库，使spfile文件生效 
SQL> execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/logmnr');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
使用Logmnr
  1  begin
  2  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/TESTDB/archivelog/2013_07_29/o1_mf_1_139_8zcx06o8_.arc',options=>dbms_logmnr.new);
  3* end;
SQL> /

PL/SQL procedure successfully completed.

SQL>
View Code 
SQL> select * from v$logmnr_logs;

    LOG_ID FILENAME                                           LOW_TIME            HIGH_TIME                DB_ID DB_NAME   RESET_SCN RESET_SCN_TIME      COMPATIBLE         THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE    BLOCKSIZE   FILESIZE INFO                                 STATUS
---------- -------------------------------------------------- ------------------- ------------------- ---------- -------- ---------- ------------------- ----------------- ---------- ---------- ---------- ---------- --- --- ------- ---------- ---------- -------------------------------- ----------
       139 /u01/app/oracle/fast_recovery_area/TESTDB/archivel 2013/07/29 12:22:43 2013/07/29 12:48:06 2603690985 TESTDB       995548 2013/07/09 22:34:19 11.2.0.0.0                 1        139    2688940    2690749 NO  NO  ARCHIVE       512    1073152                                           0
           og/2013_07_29/o1_mf_1_139_8zcx06o8_.arc


SQL>
查看添加成功的日志
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/logmnr/dictionary.ora');

PL/SQL procedure successfully completed.

SQL>
分析数据字典信息
SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
      1627

SQL>
count record 
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size                  2229064 bytes
Variable Size             335547576 bytes
Database Buffers           83886080 bytes
Redo Buffers                8413184 bytes
Database mounted.
SQL>
关闭数据库，重新启动到mount状态.
11203ora-> rman nocatalog target sys/oracle@testdb

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 29 15:10:53 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2603690985, not open)
using target database control file instead of recovery catalog

RMAN> run{}

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "}": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure, convert, copy, crosscheck, delete, duplicate, execute, flashback, host, mount, open, recover, release, repair, report, restore, resync, send, set, show, shutdown, sql, startup, switch, transport, validate, "
RMAN-01007: at line 1 column 5 file: standard input

RMAN> 

RMAN> run{
2> allocate channel d1 device type disk;
3> set until scn 2690511;
4> restore database;
5> recover database;
6> }

allocated channel: d1
channel d1: SID=10 device type=DISK

executing command: SET until clause

Starting restore at 2013/07/29 15:14:41

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/testdb/system01.dbf
channel d1: restoring datafile 00002 to /u01/app/oracle/oradata/testdb/sysaux01.dbf
channel d1: restoring datafile 00003 to /u01/app/oracle/oradata/testdb/undotbs01.dbf
channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/testdb/users01.dbf
channel d1: restoring datafile 00005 to /u01/app/oracle/oradata/testdb/example01.dbf
channel d1: restoring datafile 00006 to /u01/app/oracle/oradata/testdb/DATACENTER01.dbf
channel d1: restoring datafile 00007 to /u01/app/oracle/oradata/testdb/rman_ts01.dbf
channel d1: restoring datafile 00008 to /u01/app/oracle/oradata/testdb/myundotbs01.dbf
channel d1: restoring datafile 00009 to /u01/app/oracle/oradata/testdb/testtbs01.dbf
channel d1: restoring datafile 00010 to /u01/app/oracle/oradata/testdb/testtbs02.dbf
channel d1: restoring datafile 00011 to /u01/app/oracle/oradata/testdb/ts_perf_01.dbf
channel d1: reading from backup piece /home/oracle/logmnr/rmanback/inc0_TESTDB_6eofv34u_1_1
channel d1: piece handle=/home/oracle/logmnr/rmanback/inc0_TESTDB_6eofv34u_1_1 tag=INC0
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:01:45
Finished restore at 2013/07/29 15:16:27

Starting recover at 2013/07/29 15:16:27

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 2013/07/29 15:16:30
released channel: d1

RMAN>
连接到rman，进行incomplete recovery
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL>
启动数据库，以resetlog方式打开.
SQL> conn ARCER/ARCER
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COILS_ASSETS_DEPRECIATION      TABLE
COILS_CODE_ASSESS              TABLE
COILS_COMPANY_LICENSE          TABLE
COILS_EMP                      TABLE
COILS_LOG_ASSESS               TABLE
COILS_STORES                   TABLE
COMPANY_ANNOUNCEMENT           TABLE
CRM_CUSTOMER                   TABLE
FGPS_EMPLOYEE                  TABLE
FGPS_FINANCEUNIT               TABLE
FGPS_GROUP                     TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
FGPS_GROUP_VS_MENU             TABLE
FGPS_MENU                      TABLE
FGPS_MYRECEIVER                TABLE
FGPS_PEOPLE                    TABLE
FGPS_SYSTEMUSER                TABLE
FGPS_USER                      TABLE
FGPS_USERGRANTACCOUNT          TABLE
FGPS_VOUCHER                   TABLE
FRX_DEV_SUGGEST                TABLE
FRX_OA_ATTENDANCE              TABLE
FRX_OA_DICT                    TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
FRX_OA_LOG_TEMPLATE            TABLE
FRX_OA_WORKLOG                 TABLE
INVESTMENT_INCOME_STATEMENT    TABLE
NETSTORE                       TABLE
NETSTORE_INCOMEEXPENDITURELIST TABLE
RECHARGE_SUM_VW                VIEW
SYSTEM_GROUP                   TABLE
SYSTEM_MENU                    TABLE
SYS_CATEGORY                   TABLE
SYS_GROUP_MENU_CORRESPONDENCE  TABLE
TEST3                          TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
WAITTINGHANDLER                TABLE
WAITTING_HANDLER_DETAIL        TABLE

35 rows selected.

SQL>
以试验用户ARCER连接,发现被删除的表已经被恢复回来.
SQL> select * from test3;

COL1
---------------
first row
second row

SQL>
drop netstore表之后，新插入的数据也不存在； 
select scn,username,seg_name,seg_owner,seg_type_name,timestamp,operation,sql_redo,sql_undo from hr.tb_logmnr where operation = 'DDL';
tb_logmnr

Summary

  • V$LOGMNR_CONTENTS是一张非常重要的表，在分析日志信息是所要使用到的. 这张表中使用到几个重要的字段
    • scn
    • username
    • seg_name
    • seg_owner
    • seg_type_name
    • tiemstamp
    • operation
  • incomplete recover database缺点:丢失时间点之后的改动.
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/arcer/p/3222704.html