其他恢复技术总结 —— Logminer, DBPITR, TSPITR(3)

//==============================================================
//= Example 3: Using Continuous Mining to Include Future Values in a Query
//==============================================================

//This examples assumes that you want to monitor all changes made to the table hr.employees from now until 5 hours from now, and that you are using the dictionary in the online catalog.
 
 Step 1 Start LogMiner.
 
 EXECUTE DBMS_LOGMNR.START_LOGMNR(-
 STARTTIME => SYSDATE, -
 ENDTIME => SYSDATE + 5/24, -
 OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE + -
 DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
 
 Step 2 Query the V$LOGMNR_CONTENTS view.
 
 //This example specifies the SET ARRAYSIZE statement so that rows are displayed as they are entered in the redo log file. If you do not specify the SET ARRAYSIZE statement, rows are not returned until the SQL internal buffer is full.
 
 SET ARRAYSIZE 1;
 SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS
 WHERE SEG_OWNER = 'JOHN' AND TABLE_NAME = 'NAMELIST';
 
 
 In another session:
 
 SQL> select * from namelist;

 NAME
 --------------------
 DictInLog
 test
 test
 Jeff
 John
 Jack
 IhateU
 IhateU
 LogLog
 
 9 rows selected.
 
 SQL> delete from namelist where name='LogLog';
 
 1 row deleted.
 
 SQL> commit;
 
 Commit complete.
 
 SQL> update namelist set name='IloveU' where name='IhateU';
 
 2 rows updated.
 
 SQL> commit;
 
 Commit complete.
 
 Results in Query sesion:
 
 SQL>    SET ARRAYSIZE 1;
 SQL>    SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS
   2     WHERE SEG_OWNER = 'JOHN' AND TABLE_NAME = 'NAMELIST';
 
 USR                  SQL_REDO
 -------------------- --------------------------------------------------
 JOHN                 delete from "JOHN"."NAMELIST" where "NAME" = 'LogL
                      og' and ROWID = 'AAAC3sAAEAAAAAQAAA';
 
 JOHN                 update "JOHN"."NAMELIST" set "NAME" = 'IloveU' whe
                      re "NAME" = 'IhateU' and ROWID = 'AAAC3sAAEAAAAANA
                      AA';
 
 JOHN                 update "JOHN"."NAMELIST" set "NAME" = 'IloveU' whe
                      re "NAME" = 'IhateU' and ROWID = 'AAAC3sAAEAAAAANA
                      AB';

 //So, that's what I want:)
 
 Step 3 End the LogMiner session.
 EXECUTE DBMS_LOGMNR.END_LOGMNR();

 
//********************************************************************************************
//* Example Scenarios
//********************************************************************************************

//==============================================================
//= Scenario 1: Using LogMiner to Track Changes Made by a Specific User
//  This example shows how to see all changes made to the database in a specific time range by a single user: john.
//==============================================================
 
 1. Create the LogMiner dictionary file.
 
 Using online catalogs....
 
 
 2. Add redo log files.
 
  EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
 LOGFILENAME => 'D:OPTFLASH_RECOVERY_AREAORA10GBRARCHIVELOG2008_04_28O1_MF_1_88_41CG8YOR_.ARC', -
 OPTIONS => DBMS_LOGMNR.NEW);
 
 3. Start LogMiner and limit the search to the specified time range:
 
 EXECUTE DBMS_LOGMNR.START_LOGMNR( -
 STARTTIME => TO_DATE('2008-04-28 19:31:00','YYYY-MM-DD HH24:MI:SS'), -
 ENDTIME => TO_DATE('2008-04-28 19:46:38', 'YYYY-MM-DD HH24:MI:SS'), -
 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
 DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
 DBMS_LOGMNR.PRINT_PRETTY_SQL + -
 DBMS_LOGMNR.CONTINUOUS_MINE);
 
 4. Query the V$LOGMNR_CONTENTS view.
 
 SQL> SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE USERNAME = 'JOHN' AND SEG_NAME = 'NAMELIST';

 SQL_REDO                                           SQL_UNDO
 -------------------------------------------------- --------------------------------------------------
 delete from "JOHN"."NAMELIST"                      insert into "JOHN"."NAMELIST"
  where                                              values
     "NAME" = 'DictInLog' and                           "NAME" = 'DictInLog';
     ROWID = 'AAAC3sAAEAAAAAMAAA';
 
 insert into "JOHN"."NAMELIST"                      delete from "JOHN"."NAMELIST"
  values                                             where
     "NAME" = 'haha';                                   "NAME" = 'haha' and
                                                        ROWID = 'AAAC3sAAEAAAAAMAAB';
                                                       
  5. End the LogMiner session.
 
  EXECUTE DBMS_LOGMNR.END_LOGMNR( );
                                                 

//==============================================================
//= Scenario 2: Using LogMiner to Calculate Table Access Statistics
//  In this example, assume you manage a direct marketing database and want to
  determine how productive the customer contacts have been in generating revenue for
  a 2-week period in January. Assume that you have already created the LogMiner
  dictionary and added the redo log files that you want to search (as demonstrated in
  the previous example).
//==============================================================

 1. Start LogMiner and specify a range of times:
 
 
 EXECUTE DBMS_LOGMNR.START_LOGMNR( -
 STARTTIME => TO_DATE('2008-04-28 19:31:00','YYYY-MM-DD HH24:MI:SS'), -
 ENDTIME => TO_DATE('2008-04-28 19:46:38', 'YYYY-MM-DD HH24:MI:SS'), -
 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
 DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
 DBMS_LOGMNR.PRINT_PRETTY_SQL + -
 DBMS_LOGMNR.CONTINUOUS_MINE);

 2. Query the V$LOGMNR_CONTENTS view to determine which tables were modified  in the time range you specified, as shown in the following example. (This query filters out system tables that traditionally have a $ in their name.)

  SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM
  V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY
  SEG_OWNER, SEG_NAME ORDER BY Hits DESC;
  
    SEG_OWNER                      SEG_NAME                                                                                           HITS
  ------------------------------ ---------------------------------------------------------------------------------------------------- ----------
  SYSMAN                         MGMT_SYSTEM_PERFORMANCE_LOG                                                                          30
  SYSMAN                         MGMT_CURRENT_METRICS,MGMT_CURRENT_METRICS_PK                                                         28
  SYSMAN                         MGMT_METRICS_RAW,MGMT_METRICS_RAW_PK                                                                 28
  SYSMAN                         MGMT_METRIC_THRESHOLDS                                                                               14
  SYSMAN                         MGMT_METRIC_COLLECTIONS                                                                              14
  SYS                            SMON_SCN_TIME                                                                                         5
  SYSMAN                         MGMT_TARGETS                                                                                          2
  SYSMAN                         MGMT_STRING_METRIC_HISTORY                                                                            2
  JOHN                           NAMELIST                                                                                              2
  SYS                            WRI$_SCH_CONTROL                                                                                      1
  
  10 rows selected.
  
 4. End the LogMiner session.
 
 SQL> execute dbms_logmnr.end_logmnr();
 
 //Oh my God! The end finnally!!!

[The End!!!]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-259953/，如需转载，请注明出处，否则将追究法律责任。