oracle調(diào)整redo日志組步驟

本文格式為Word版，下載可任意編輯——oracle調(diào)整redo日志組步驟本文檔說明普通架構(gòu)及DG架構(gòu)下，調(diào)整redo日志組大小的方法，也可作為調(diào)整redo文件位置的方法。

普通架構(gòu)

1、創(chuàng)立2個新的日志組4/5

SQL>alterdatabaseaddlogfilegroup4('/oradata0/redo4a.log','/oradata1/redo4b.log')size128MB;

SQL>alterdatabaseaddlogfilegroup5('/oradata1/redo5a.log','/oradata2/redo5b.log')size128MB;

2、確認redo日志組狀態(tài)，觸發(fā)redolog歸檔，令group1/2/3狀態(tài)為非currentselectGROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,statusfromv$log;altersystemcheckpointlocal;altersystemswitchlogfile;(屢屢)

selectGROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,statusfromv$log;3、確認group1/2/3狀態(tài)為非current后，刪除group1/2/3SQL>alterdatabasedroplogfilegroup1;SQL>alterdatabasedroplogfilegroup2;SQL>alterdatabasedroplogfilegroup3;

4、重命名redolog文件（慎用rm刪除命令）selectGROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,statusfromv$log;mv/oradata0/redo1a.log/oradata0/redo1a.log.bakmv/oradata1/redo1b.log/oradata1/redo1b.log.bakmv/oradata1/redo2a.log/oradata1/redo2a.log.bakmv/oradata2/redo2b.log/oradata2/redo2b.log.bakmv/oradata2/redo3a.log/oradata2/redo3a.log.bakmv/oradata0/redo3b.log/oradata0/redo3b.log.bak

5、重新創(chuàng)立3個新的日志組group1/2/3

SQL>alterdatabaseaddlogfilegroup1('/oradata1/redo1a.log','/oradata2/redo1b.log')size128MB;

SQL>alterdatabaseaddlogfilegroup2('/oradata2/redo2a.log','/oradata3/redo2b.log')size128MB;

SQL>alterdatabaseaddlogfilegroup3('/oradata3/redo3a.log','/oradata1/redo3b.log')size128MB;

6、確認redo日志組狀態(tài)，觸發(fā)redolog歸檔，令group4/5狀態(tài)為非current

selectGROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,statusfromv$log;altersystemswitchlogfile;(屢屢)

selectGROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,statusfromv$log;7、確認group4/5狀態(tài)為非current后，刪除group4/5SQL>alterdatabasedroplogfilegroup4;SQL>alterdatabasedroplogfilegroup5;8、操作系統(tǒng)物理刪除redolog文件

rm/oradata0/redo4a.logrm/oradata1/redo4b.logrm/oradata1/redo5a.logrm/oradata2/redo5b.log

DG架構(gòu)

對于搭建了DG架構(gòu)的數(shù)據(jù)庫，如需調(diào)整redo日志組，需要主備數(shù)據(jù)庫都同時調(diào)整，涉及中止日志自動應(yīng)用、對standby數(shù)據(jù)庫的redo日志組進行clear操作等。

1、standby備機中止日志應(yīng)用

SQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASECANCEL;SQL〉selectNAME,DATABASE_ROLE,SWITCHOVER_STATUSfromv$database;將STANDBY_FILE_MANAGEMENT設(shè)置為MANUALSQL〉altersystemsetstandby_file_management=manual;SQL〉showparameterSTANDBY_FILE_MANAGENT

2、primary主機將STANDBY_FILE_MANAGEMENT設(shè)置為

MANUAL

SQL>altersystemsetstandby_file_management=manual;SQL〉showparameterSTANDBY_FILE_MANAGENT

3、primary主機檢查當前日志組

SQL>selectgroup#,type,memberfromv$logfile;

GROUP#TYPEMEMBER

1ONLINE/redolog1/redo1a.log1ONLINE/redolog2/redo1b.log2ONLINE/redolog2/redo2a.log2ONLINE/redolog3/redo2b.log3ONLINE/redolog3/redo3a.log3ONLINE/redolog1/redo3b.log4STANDBY/redolog1/redo4.log5STANDBY/redolog2/redo5.log6STANDBY/redolog3/redo6.log7STANDBY/redolog1/redo7.log

10rowsselected.

刪除standbyredolog

alterdatabasedroplogfilegroup4;alterdatabasedroplogfilegroup5;alterdatabasedroplogfilegroup6;alterdatabasedroplogfilegroup7;

selectgroup#,type,memberfromv$logfile;

物理刪除或重命名4、5、6、7standbyredo日志組相關(guān)的文件。mv/redolog1/redo4.log/redolog1/redo4.log.bakstbmv/redolog2/redo5.log/redolog2/redo5.log.bakstbmv/redolog3/redo6.log/redolog3/redo6.log.bakstbmv/redolog1/redo7.log/redolog1/redo7.log.bakstb

重新添加standbyredolog

ALTERDATABASEADDSTANDBYLOGFILEGROUP7('/redolog1/stbredo7.log')SIZE300M;

ALTERDATABASEADDSTANDBYLOGFILEGROUP8('/redolog2/stbredo8.log')SIZE300M;

ALTERDATABASEADDSTANDBYLOGFILEGROUP9('/redolog3/stbredo9.log')SIZE300M;

ALTERDATABASEADDSTANDBYLOGFILEGROUP10('/redolog1/stbredo10.log')SIZE300M;

selectgroup#,type,memberfromv$logfile;

4、primary主機調(diào)整在線redo日志組

添加4/5/6onlineredolog

alterdatabaseaddlogfilegroup4('/redolog1/redo4a.log','/redolog2/redo4b.log')size300m;alterdatabaseaddlogfilegroup5('/redolog2/redo5a.log','/redolog3/redo5b.log')size300m;alterdatabaseaddlogfilegroup6('/redolog3/redo6a.log','/redolog1/redo6b.log')size300m;selectgroup#,type,memberfromv$logfile;

刪除1/2/3onlineredologaltersystemcheckpointlocal;altersystemswitchlogfile;

selectGROUP#,MEMBERS,BYTES,STATUSfromv$log;可屢屢執(zhí)行，直至1、2、3組全為inactive

刪除1、2、3重做日志組

alterdatabasedroplogfilegroup1;alterdatabasedroplogfilegroup2;alterdatabasedroplogfilegroup3;

selectgroup#,type,memberfromv$logfile;

重命名舊的onlineredologfile

mv/redolog1/redo1a.log/redolog1/redo1a.log.bakoldmv/redolog2/redo1b.log/redolog2/redo1b.log.bakoldmv/redolog2/redo2a.log/redolog2/redo2a.log.bakoldmv/redolog3/redo2b.log/redolog3/redo2b.log.bakoldmv/redolog3/redo3a.log/redolog3/redo3a.log.bakoldmv/redolog1/redo3b.log/redolog1/redo3b.log.bakold

重新添加1/2/3onlineredolog

alterdatabaseaddlogfilegroup1('/redolog1/redo1a.log','/redolog2/redo1b.log')size300m;alterdatabaseaddlogfilegroup2('/redolog2/redo2a.log','/redolog3/redo2b.log')size300m;alterdatabaseaddlogfilegroup3('/redolog3/redo3a.log','/redolog1/redo3b.log')size300m;selectgroup#,type,memberfromv$logfile;

進行日志切換測試

altersystemswitchlogfile;

selectGROUP#,MEMBERS,BYTES,STATUSfromv$log;

將STANDBY_FILE_MANAGEMENT設(shè)置為AUTO

SQL〉A(chǔ)LTERSYSTEMSETSTANDBY_FILE_MANAGEMENT='AUTO';

5、standby備機檢查當前日志組

SQL>selectgroup#,type,memberfromv$logfile;

GROUP#TYPEMEMBER

1ONLINE/redolog1/redo1a.log1ONLINE/redolog2/redo1b.log2ONLINE/redolog2/redo2a.log2ONLINE/redolog3/redo2b.log3ONLINE/redolog3/redo3a.log3ONLINE/redolog1/redo3b.log4STANDBY/redolog1/redo4.log5STANDBY/redolog2/redo5.log6STANDBY/redolog3/redo6.log7STANDBY/redolog1/redo7.log

10rowsselected.

刪除standbyredolog

alterdatabasedroplogfilegroup4;alterdatabasedroplogfilegroup5;alterdatabasedroplogfilegroup6;alterdatabasedroplogfilegroup7;

selectgroup#,type,memberfromv$logfile;

物理重命名standbyredolog

mv/redolog1/redo4.log/redolog1/redo4.log.bakstbmv/redolog2/redo5.log/redolog2/redo5.log.bakstbmv/redolog3/redo6.log/redolog3/redo6.log.bakstbmv/redolog1/redo7.log/redolog1/redo7.log.bakstb

重新添加standbyredolog

ALTERDATABASEADDSTANDBYLOGFILEGROUP7('/redolog1/stbredo7.log')SIZE300M;

ALTERDATABASEADDSTANDBYLOGFILEGROUP8('/redolog2/stbredo8.log')SIZE300M;

ALTERDATABASEADDSTANDBYLOGFILEGROUP9('/redolog3/stbredo9.log')SIZE300M;

ALTERDATABASEADDSTANDBYLOGFILEGROUP10('/redolog1/stbredo10.log')SIZE300M;

selectgroup#,type,memberfromv$logfile;

添加4/5/6onlineredolog

alterdatabaseaddlogfilegroup4('/redol