之前写过一篇关于Oraclelogminer的推文，链接如下：
Logminer：oracle人为误操作之恢复神器
今天再转一篇老刘的文章，正文如下：
LogMiner可以用来分析在线/redo日志，也可以用来分析离线日志文件/归档日志，是由一组PL/SQL包和一些动态视图组成，而且是免费的。
参考了老杨的《Logminer：oracle人为误操作之恢复神器》，在11g尝试一次。但是请注意12c以上，具体操作过程，应该有改动，有机会得研究下。
如果没安装LogMiner所需的两个包dbms_logmnr和dbms_logmnr_d，则可执行这两个脚本，dbmslmd.sql和dbmslm.sql，路径是
?/rdbms/admin。dbmslmd.sql的内容，
RemRemHeader:dbmslmd.sql13-feb-.15:15:25ajadamsExpRemRemdbmslmd.sqlRemRemCopyright(c),,Oracle.Allrightsreserved.RemRemNAMERemdbmslmd.sql-DBMSLogminerDictionarypackagespecificationRemforDBMS_LOGMNR_DRemRemDESCRIPTIONRemThisfilecontainsthelogminerpackagespecificationforDBMS_LOGMNR_DRemRemNOTESRemRemMODIFIED(MM/DD/YY)Remajadams02/13/06-createsynonymRemabrown09/13/05-bug:unwinddictionaryRemjnesheiw02/17/05-BugRelocatedchangehistoryandlogmnrRemmetadacreationtoprvtlmd.sqlRemdoshaugh04/14/98-CreatedRemRemRemPUBLICPROCEDURESRemRemBUILD(FileName,FileLocation,Options)RemRemSET_TABLESPACE(NewTablespace);RemRemPUBLICCONSTANTSRemRemSTORE_IN_FLAT_FILERemRemSTORE_IN_REDO_LOGSRemRemMARK_SAFE_MINE_POINTRemRemRem------------------------------------------------------------------CREATEorREPLACEPACKAGEdbms_logmnr_dAS----PACKAGENAME--dbms_logmnr_d----DESCRIPTION--ThispackagecontainsLogminerDictionaryrelatedprocedures.--"build"isusedtogatherthelogminerdictionary.----"set_tablespace"isusedtoalterthedefaulttablespaceof--Logminertables.----BUILD--Thelogminerdictionarycanbegathered--intoaflatfile(LogminerV1behavior)oritcanbegathered--intotheredologstream.----WhencreatingaFlatFiledictionarytheprocedurequeriesthe--dictionarytablesofthecurrentdatabaseandcreatesatextbased--filecontainingtheircontents.Eachtableisrepresentedby--"pseudo"SQLstatements.Adescriptionofthecolumnsina--tableiscreatedbya"CREATE_TABLE"line(onestatementfor--table).Itcontainsthename,datatypeandlengthforeach--column.A"INSERT_INTO"statementiscreatedforeachrowina--selectedtable.Itcontainsthevaluesforeachrow.Thefile--iscreatedinpreparationoffutureanalysisofdatabases--logfilesusingthelogminertool.----Whengatheringthesystemdictionaryintothelogstreamtheprocedure--queriesthedictionarytablesinsertingtheresultsintoaspecial--setofLogminerGathertables(SYS.LOGMNRG_*).Asideeffectof--eachqueryisthattheresultantinsertscauseredotobegenerated.--Downstreamprocessingcanminethisredotodeterminethecontents--ofthissystemssystemdictionaryatthetimethisprocedurewas--executed.----NOTE:Databasemustbein"ArchivelogMode"andsupplementallogging--mustbeenabledforthisproceduretorun----BUILDINPUTS--dictionary_filename-nameofthedictionaryfile--dictionary_location-pathtofiledirectory--options-Toexplicitlyindicateflatfileorlogstreamdestination.----BUILDEXAMPLE1--Creatingadictionaryfileas:--/usr/ora/dict.ora--Completesyntax,typedallononeline:----SQLexecutedbms_logmnr_d.build(dict.ora,--/usr/ora,--DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);----BUILDEXAMPLE2--Creatingadictionaryfileas:--/usr/ora/dict.ora--LogminerV1syntax.----SQLexecutedbms_logmnr_d.build(dict.ora,/usr/ora);----BUILDEXAMPLE3--Gatheringadictionaryintothelogstream--Completesyntax,typedallononeline:----SQLexecutedbms_logmnr_d.build(,,--DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);----BUILDNOTES--Thedictionarygathershouldbedoneafteralldictionary--changestoadatabaseandpriortothecreationofanylog--filesthataretobeanalyzed.------SET_TABLESPACE--BydefaultallLogminertablesarecreatedtousetheSYSAUX--tablespace.AlluserswillfinditdesirabletoalterLogminer--tablestoemployanalternatetablespace.Usethisroutineto--recreateallLogminertablesinanalternatetablespace.----SET_TABLESPACEINPUTS--new_tablespace-astringnamingapreexistanttablespace.--STORE_IN_FLAT_FILECONSTANTINTEGER:=1;STORE_IN_REDO_LOGSCONSTANTINTEGER:=2;MARK_SAFE_MINE_POINTCONSTANTINTEGER:=8;PROCEDUREbuild(dictionary_filenameINVARCHAR2DEFAULT,dictionary_locationINVARCHAR2DEFAULT,optionsINNUMBERDEFAULT0);----PROCEDUREset_tablespace(new_tablespaceINVARCHAR2);----ENDdbms_logmnr_d;--EndDefinitionofpackage/CREATEORREPLACEPUBLICSYNONYMdbms_logmnr_dFORsys.dbms_logmnr_d;
dbmslm.sql的内容
createorreplacePACKAGEdbms_logmnrIS----------------------OVERVIEW----ThispackagecontainstheproceduresusedbyLogMinerad-hocquery--interfacethatallowsforredologstreamanalysis.--Therearethreeproceduresandtwofunctionsavailabletotheuser:--dbms_logmnr.add_logfile():toregisterlogfilestobeanalyzed--dbms_logmnr.remove_logfile():toremovelogfilesfrombeinganalyzed--dbms_logmnr.start_logmnr():toprovidewindowofanalysisand--meta-datainformation--dbms_logmnr.end_logmnr():toendtheanalysissession--dbms_logmnr.column_present():whetheraparticularcolumnvalue--ispresnetinaredorecord--dbms_logmnr.mine_value():extractdatavaluefromaredorecord-------------------------------PROCEDUREINFORMATION:--#1dbms_logmnr.add_logfile():--DESCRIPTION:--RegistersaredologfilewithLogMiner.Multipleredologscanbe--registeredbycallingtheprocedurerepeatedly.Theredologs--donotneedtoberegisteredinanyparticularorder.--Botharchivedandonlineredologscanbemined.Ifasuccessful--calltotheprocedureismadeacalltostart_logmnr()mustbe--madebeforeselectingfromvlogmnr_contents.----CONSTANTS:--dbms_logmnr.NEW:Startsanewlist.Anyfileregisteredpriorto--thiscallisdiscarded.--dbms_logmnr.ADDFILE:Addstheredologtotheexisting--list.LogMinercheckstomakesurethattheredologisfrom--thesamedatabase(DB_IDandRESETLOGS_SCN)incarnationasthe--onespreviouslyadded.----EXCEPTIONS:--ORA:Redologfilespecifiedcannotbeopened.--ORA:Errorreadingtheheaderoftheredologfile--ORA:Redologfilespecifiedisfromadatabasewitha--differentDB_ID--ORA:Redologfilespecifiedisfromadatabasewith--differentincarnation--ORA:Redologfilespecifiedisaduplicateofapreviously--specifiedredolog.LogMinermatchesredologsbythe--logsequencenumber.Thustworedologswithdifferent--namesbutwiththesamelogsequence#(forinstance--theonlinecounterpartofanarchivedredologhas--adifferentname,butattemptingtoregisteritwith--LogMinerafterregisteringthearchivedcounterpart--willreturnthisexception).----#2dbms_logmnr.remove_logfile()--DESCRIPTION:--UnregistersaredologfilefromLogMiner.Multipleredologscanbe--unregisteredbycallingtheprocedurerepeatedly.Theredologs--donotneedtobeunregisteredinanyparticularorder.Ifa--successfulcalltotheprocedureismadeacalltostart_logmnr()--mustbemadebeforeselectingfromvlogmnr_contents.----EXCEPTIONS:--ORA:Attemptwasmadetoremovearedologthathasnotbeen--registeredwithLogMiner----#3dbms_logmnr.start_logmnr()--DESCRIPTION:--LoadsthedatadictionaryusedbyLogMinertotranslateinternal--schemaobjectidentifierstonames.Theredostreamdoesnot--containnamesofschemaobjectsandcolumns.Thedatadictionary--extractcanbeprovidedinthreeways:--(i)useOraclesonlinecatalog.Thisisonlyvalidwhenthe--miningofredologsisdoneinthesamesystemthatgenerated--them.--(ii)usedatadictionaryextracttakentoaflatfile.--(Seedescriptionofdbms_logmnr_d.build())--(iii)usedatadictionaryextractedintheredostream.This--optioncanONLYbeusedwhenLogMinerisbeingrunonanopen--database,andthesourceandtheminingdatabaseinstancesare--Oracle9iorhigher.--TheusercanalsorestricttheanalysisinsideanSCNrangeora--timerange.IfbothSCNrangeandtimerangearespecified,only--theSCNrangeisused.--Theuserneedstobemindfulofthefactthatuseoftimerange--canbeimprecise.Ifastart_timeorstart_scnisspecified,it--mustbecontainedinaredologaddedbyapreviouscallto--dbms_logmnr.add_logfile().Ifastart_timeandstart_scnisnot--specified,LogMinerwillsetitbasedontheearliestaddedredo--log.Ifaend_timeorend_scnisspecifiedanditisbeyondthe--latestaddedredolog,LogMinerwilloverwritetheend_timeand--andend_scnwithinformationfromthelatestaddedredolog.When--theCONTINOUS_MINEoptionisinusethesemanticsof--startandendtime/scnrangesmaybedifferent.--Seeadditionaldocumentationbelow.----CONSTANTS(usedinoptionsparameter)--dbms_logmnr.NO_DICT_RESET_ONSELECT:(willbedeprecatedsoon)--dbms_logmnr.COMMITED_DATA_ONLY:GroupsDMLsbelongingtothe--sametransaction.Transactionsareshownintheir
