Oracle數(shù)據(jù)庫管理分析過程

1、第四章 管理重做日志重做日志簡介日志切換和檢查點(diǎn)增加和刪除重做日志顯示重做日志信息分析重做日志1wanghailiang重做日志志簡介Group 2Group 3Group 1Disk 1Disk 2MemberMemberMemberMemberMemberMember2wanghailiang重做日志志工作原原理數(shù)據(jù)庫實(shí)例SGACKPT LGWR DBWR數(shù)據(jù)高速緩存控制文件數(shù)據(jù)文件重做日志重做日志緩沖區(qū)ARCH參數(shù)文件口令文件歸檔日志3wanghailiang日志切換換和檢查查點(diǎn)強(qiáng)制日志志切換ALTERSYSTEMSWITCH LOGFILE;檢查點(diǎn)參參數(shù)LOG_CHECKPOINT_I

2、NTERVALLOG_CHECKPOINT_TIMEOUT手工檢查查點(diǎn)ALTERSYSTEMCHECKPOINT4wanghailiang增加重做做日志組組log3a.rdo日志組1日志組2日志組3ALTERDATABASEADDLOGFILE(g:testtest3a.log,g:testtest3b.log)SIZE 1M;log3b.loglog1a.rdoLog1a.loglog2a.rlog5wanghailiang增加重做做日志成成員日志組2日志組1ALTER DATABASE ADD LOGFILE MEMBER g:testtest1b.log TO GROUP 1,g:tes

3、ttest2b.log TO GROUP 2;log2b.loglog2a.loglog1b.loglog1a.log6wanghailiang改變重做做日志名名稱或位位置復(fù)制重做做日志到到新位置置：copy修改重做做日志名名稱ALTERDATABASERENAMEFILEg:testtest3a.logTOg:testtest3c.log;7wanghailiang刪除日志志組日志組1日志組2日志組3ALTERDATABASEDROP LOGFILEGROUP3;log1a.rdolog2a.rdolog3b.rdolog3a.rdolog1b.rdolog2b.rdo8wanghailia

4、ng刪除日志志成員日志組2日志組1ALTERDATABASEDROP LOGFILEMEMBERg:testtest2b.log;log1a.rdolog2b.rdolog1b.rdolog2a.rdo9wanghailiang清除重做做日志ALTERDATABASECLEARLOGFILEGROUP1;10wanghailiang顯示重做做日志信信息V$LOGV$LOGFILE11wanghailiang分析重做做日志跟蹤數(shù)據(jù)據(jù)庫、表表和用戶戶改變映像數(shù)據(jù)據(jù)訪問取消數(shù)據(jù)據(jù)庫變化化12wanghailiang使用LogMiner建立字典典文件指定重做做日志分析重做做日志跟蹤改變變結(jié)束LogMi

5、ner13wanghailiang建立字典典文件指定I/O目錄：UTL_FILE_DIR=g:test重新啟動動數(shù)據(jù)庫庫：STARTUP FORCE建立字典典文件：execute dbms_logmnr_d.build(dict.ora,g:test);14wanghailiang指定重做做日志關(guān)閉數(shù)據(jù)據(jù)庫：SHUTDOWN安裝數(shù)據(jù)據(jù)庫：STARTUP MOUNT建立重做做日志列列表execute dbms_logmnr.add_logfile(g:testtest1a.log,DBMS_LOGMNR.NEW);增加其它它重做日日志execute dbms_logmnr.add_logfile

6、(g:testtest2a.log,DBMS_LOGMNR.ADDFILE);15wanghailiang分析重做做日志execute dbms_logmnr.start_logmnr(dictfilename=g:testdict.ora);16wanghailiang跟蹤改變變SELECTtimestamp,username,sql_redo,sql_undofrom v$logmnr_contentswhereseg_name=EMP;17wanghailiang結(jié)束LogMinerexecute dbms_logmnr.end_logmnr;18wanghailiang獲得日志志分析信信息V$LOGMNR_DICTIONARYV$LOGMNR_PARAMETERSV$LOGMNR_CONTENTS19wang