oracle rac alter日志,ORACLE 11G RAC 增加日志組及增大日志文件

1、查看目前日志組和日志文件情況

SQL> select * from v$logfile order by 1;

GROUP# STATUS ?TYPE ? ?MEMBER ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? IS_

---------- ------- ------- -------------------------------------------------- ---

1 ? ? ? ? ONLINE ?+FRA/stone/onlinelog/group_1.257.884819615 ? ? ? ? YES

1 ? ? ? ? ONLINE ?+DATA/stone/onlinelog/group_1.261.884819613 ? ? ? ?NO

2 ? ? ? ? ONLINE ?+FRA/stone/onlinelog/group_2.258.884819623 ? ? ? ? YES

2 ? ? ? ? ONLINE ?+DATA/stone/onlinelog/group_2.262.884819619 ? ? ? ?NO

3 ? ? ? ? ONLINE ?+DATA/stone/onlinelog/group_3.266.884820745 ? ? ? ?NO

3 ? ? ? ? ONLINE ?+FRA/stone/onlinelog/group_3.259.884820749 ? ? ? ? YES

4 ? ? ? ? ONLINE ?+DATA/stone/onlinelog/group_4.267.884820755 ? ? ? ?NO

4 ? ? ? ? ONLINE ?+FRA/stone/onlinelog/group_4.260.884820759 ? ? ? ? YES

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP# ? ?THREAD# BYTES/1024/1024 ARC STATUS ? ? ? ? ? ? ?MEMBERS

---------- ---------- --------------- --- ---------------- ----------

1 ? ? ? ? ?1 ? ? ? ? ? ? ?50 NO ?CURRENT ? ? ? ? ? ? ? ? ? 2

2 ? ? ? ? ?1 ? ? ? ? ? ? ?50 YES INACTIVE ? ? ? ? ? ? ? ? ?2

3 ? ? ? ? ?2 ? ? ? ? ? ? ?50 YES INACTIVE ? ? ? ? ? ? ? ? ?2

4 ? ? ? ? ?2 ? ? ? ? ? ? ?50 NO ?CURRENT ? ? ? ? ? ? ? ? ? 2

可以看到目前是2個節點，每個節點2組日志，每組日志2個日志文件，大小均為50M，分別位于+DATA和+FRA磁盤組。

2、查看日志組切換頻率和歸檔日志生成量

SQL> select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) from v$log_history a, v$log_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;

SEQUENCE# FIRST_TIM ?SEQUENCE# FIRST_TIM ROUND(((A.FIRST_TIME-B.FIRST_TIME)*24)*60,2)

---------- --------- ---------- --------- --------------------------------------------

41 15-SEP-15 ? ? ? ? 42 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?.43

40 15-SEP-15 ? ? ? ? 41 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?.42

39 15-SEP-15 ? ? ? ? 40 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?.53

38 15-SEP-15 ? ? ? ? 39 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?.22

37 15-SEP-15 ? ? ? ? 38 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?.18

36 15-SEP-15 ? ? ? ? 37 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?29.93

35 22-JUL-15 ? ? ? ? 36 15-SEP-15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 79165.62

SQL> select trunc(completion_time),sum(mb)/1024 day_gb from(select name,completion_time,blocks*block_size /1024 /1024 mb from v$archived_log) group by trunc(completion_time) order by (trunc(completion_time));

TRUNC(COM ? ? DAY_GB

--------- ----------

11-JUL-15 .048826694

12-JUL-15 .170285702

13-JUL-15 .141009331

14-JUL-15 .135242462

15-JUL-15 .153475761

16-JUL-15 ?.09009409

17-JUL-15 .031218529

18-JUL-15 .084494114

20-JUL-15 .088860512

21-JUL-15 .011713982

22-JUL-15 .106176376

TRUNC(COM ? ? DAY_GB

--------- ----------

15-SEP-15 ?.37887764

12 rows selected.

根據以上結果進行分析，系統需要的重做日志組數量和重做日志文件大小。也可以簡單通過一天的歸檔日志量平均到每小時，如每小時是400M的歸檔日志量，如果15分鐘切換一次的話，那么就設置日志文件大小為100M。

可以通過告警日志查看是否出現LGWR等待，如果有，則需要增加重做日志組。

3、增加日志組及增大日志文件

(1)備份參數文件和控制文件

[oracle@rac1~]$?rman?target?/

Recovery?Manager:?Release?11.2.0.4.0?-?Production?on?Tue?Sep?15?15:44:54?2015

connected?to?target?database:?STONE?(DBID=2995045972)

RMAN>?backup?current?controlfile;

Starting?backup?at?15-SEP-15

using?target?database?control?file?instead?of?recovery?catalog

allocated?channel:?ORA_DISK_1

channel?ORA_DISK_1:?SID=32?instance=stone1?device?type=DISK

channel?ORA_DISK_1:?starting?full?datafile?backup?set

channel?ORA_DISK_1:?specifying?datafile(s)?in?backup?set

including?current?control?file?in?backup?set

channel?ORA_DISK_1:?starting?piece?1?at?15-SEP-15

channel?ORA_DISK_1:?finished?piece?1?at?15-SEP-15

piece?handle=+FRA/stone/backupset/2015_09_15/ncnnf0_tag20150915t154735_0.330.890495259?tag=TAG20150915T154735?comment=NONE

channel?ORA_DISK_1:?backup?set?complete,?elapsed?time:?00:00:03

Finished?backup?at?15-SEP-15

RMAN>?backup?spfile;

Starting?backup?at?15-SEP-15

using?channel?ORA_DISK_1

channel?ORA_DISK_1:?starting?full?datafile?backup?set

channel?ORA_DISK_1:?specifying?datafile(s)?in?backup?set

including?current?SPFILE?in?backup?set

channel?ORA_DISK_1:?starting?piece?1?at?15-SEP-15

channel?ORA_DISK_1:?finished?piece?1?at?15-SEP-15

piece?handle=+FRA/stone/backupset/2015_09_15/nnsnf0_tag20150915t154800_0.331.890495281?tag=TAG20150915T154800?comment=NONE

channel?ORA_DISK_1:?backup?set?complete,?elapsed?time:?00:00:01

Finished?backup?at?15-SEP-15

或者：

[oracle@rac1~]$?sqlplus?/?as?sysdba

SQL*Plus:?Release?11.2.0.4.0?Production?on?Tue?Sep?15?15:48:48?2015

Connected?to:

Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.4.0?-?64bit?Production

With?the?Partitioning,?Real?Application?Clusters,?Automatic?Storage?Management,?OLAP,

Data?Mining?and?Real?Application?Testing?options

SQL>?alter?database?backup?controlfile?to?'/home/oracle/control.ctl';

Database?altered.

SQL> alter database backup controlfile to trace as '/home/oracle/control.trc';

Database altered.

SQL>?create?pfile='/home/oracle/pfile.ora'?from?spfile;

File?created.

(2)增加日志組和日志文件

SQL> ?alter database add logfile thread 1 group 5 size 100M;

Database altered.

SQL> ?alter database add logfile thread 1 group 6 size 100M;

Database altered.

SQL> ?alter database add logfile thread 1 group 7 size 100M;

Database altered.

SQL> ?alter database add logfile thread 2 group 8 size 100M;

Database altered.

SQL> ?alter database add logfile thread 2 group 9 size 100M;

Database altered.

SQL> ?alter database add logfile thread 2 group 10 size 100M;

Database altered.

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP# ? ?THREAD# BYTES/1024/1024 ARC STATUS ? ? ? ? ? ? ?MEMBERS

---------- ---------- --------------- --- ---------------- ----------

1 ? ? ? ? ?1 ? ? ? ? ? ? ?50 NO ?CURRENT ? ? ? ? ? ? ? ? ? 2

2 ? ? ? ? ?1 ? ? ? ? ? ? ?50 YES INACTIVE ? ? ? ? ? ? ? ? ?2

3 ? ? ? ? ?2 ? ? ? ? ? ? ?50 YES INACTIVE ? ? ? ? ? ? ? ? ?2

4 ? ? ? ? ?2 ? ? ? ? ? ? ?50 NO ?CURRENT ? ? ? ? ? ? ? ? ? 2

5 ? ? ? ? ?1 ? ? ? ? ? ? 100 YES UNUSED ? ? ? ? ? ? ? ? ? ?2

6 ? ? ? ? ?1 ? ? ? ? ? ? 100 YES UNUSED ? ? ? ? ? ? ? ? ? ?2

7 ? ? ? ? ?1 ? ? ? ? ? ? 100 YES UNUSED ? ? ? ? ? ? ? ? ? ?2

8 ? ? ? ? ?2 ? ? ? ? ? ? 100 YES UNUSED ? ? ? ? ? ? ? ? ? ?2

9 ? ? ? ? ?2 ? ? ? ? ? ? 100 YES UNUSED ? ? ? ? ? ? ? ? ? ?2

10 ? ? ? ? ?2 ? ? ? ? ? ? 100 YES UNUSED ? ? ? ? ? ? ? ? ? ?2

10 rows selected.

SQL> ?select * from v$logfile order by 1;