Oracle笔记7（Fundamentals II）

Logminer

1、Backup database

2、Modify spfile

SQL> alter system set utl_file_dir='/u01/kevin' scope =spfile;

3、Restart database

4、Manipulate database,switch logfile

5、Use logmnr

SQL> execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/u01/kevin');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

SQL> select * from v$archived_log;

SQL> begin
  2  dbms_logmnr.add_logfile(logfilename=>'/home/oracle/flash_recovery_area/kevin/archivelog/2011_09_18/o1_mf_1_8_74llp4qm_.arc',options=>dbms_logmnr.new);
  3  end;
  4  /
PL/SQL procedure successfully completed.

SQL> select * from v$logmnr_logs;

    LOG_ID
----------
FILENAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN  THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE   FILESIZE INFO
--------- --------- ---------- -------- ---------- --------- ---------- ---------- ---------- ---------- --- --- ------- ---------- ---------- --------------------------------
    STATUS
----------
         8
/home/oracle/flash_recovery_area/KEVIN/archivelog/2011_08_16/o1_mf_1_8_74llp4qm_.arc
16-AUG-11 16-AUG-11 2243770666 KEVIN               1 15-JUL-11          1          8     171839     172311 NO  NO  ARCHIVE512     391168
         0

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/kevin/dictionary.ora');

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
      1212

SQL> create table test.tb_logmnr as select * from v$logmnr_contents;

Table created.

SQL> conn test/test
Connected.
SQL> select count(*) from tb_logmnr;
  COUNT(*)
----------
      1212

6、Analyze redo logfile

select scn,username,seg_name,seg_owner,seg_type_name,timestamp,operation,sql_redo from tb_logmnr where operation='DDL';

7、Incomplete recover database

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


allocated channel: d1
channel d1: sid=155 devtype=DISK


executing command: SET until clause


Starting restore at 16-AUG-11


channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oradata/kevin/system01.dbf
restoring datafile 00002 to /home/oracle/oradata/kevin/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oradata/kevin/sysaux01.dbf
channel d1: reading from backup piece /u01/kevin/inc0_KEVIN_09mk3h5d_1_1
channel d1: restored backup piece 1
piece handle=/u01/kevin/inc0_KEVIN_09mk3h5d_1_1 tag=INC0
channel d1: restore complete, elapsed time: 00:00:46
Finished restore at 16-AUG-11


Starting recover at 16-AUG-11


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


Finished recover at 16-AUG-11
released channel: d1

SQL> alter database open resetlogs;


Database altered.

8、Verify recover