oracle 笔记7（Fundamentals II）

Logminer

1、Backup database

2、Modify spfile

SQL>alter system set utl_file_dir='/u01/lin' 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/lin');
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/LIN/archivelog/2011_08_16/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/LIN/archivelog/2011_08_16/o1_mf_1_8_74llp4qm_.arc
16-AUG-11 16-AUG-11 2243770666 LIN               1 15-JUL-11          1          8     171839     172311 NO  NO  ARCHIVE512     391168
         0

SQL>execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/lin/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

SQL> 

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/lin/system01.dbf
restoring datafile 00002 to /home/oracle/oradata/lin/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oradata/lin/sysaux01.dbf
channel d1: reading from backup piece /u01/lin/inc0_LIN_09mk3h5d_1_1
channel d1: restored backup piece 1
piece handle=/u01/lin/inc0_LIN_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


RMAN> 

SQL> alter database open resetlogs;


Database altered.


SQL> 

8、Verify recover