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

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

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

Copyright (c) 1982, 2013, Oracle. All rights reserved.

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;

GROUP# STATUS TYPE MEMBER IS_

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

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

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

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

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

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

5 ONLINE +DATA/stone/onlinelog/group_5.270.890496635 NO

5 ONLINE +FRA/stone/onlinelog/group_5.333.890496637 YES

6 ONLINE +DATA/stone/onlinelog/group_6.269.890496649 NO

GROUP# STATUS TYPE MEMBER IS_

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

6 ONLINE +FRA/stone/onlinelog/group_6.332.890496651 YES

7 ONLINE +DATA/stone/onlinelog/group_7.271.890496663 NO

7 ONLINE +FRA/stone/onlinelog/group_7.334.890496665 YES

8 ONLINE +DATA/stone/onlinelog/group_8.272.890496679 NO

8 ONLINE +FRA/stone/onlinelog/group_8.335.890496683 YES

9 ONLINE +DATA/stone/onlinelog/group_9.273.890496703 NO

9 ONLINE +FRA/stone/onlinelog/group_9.336.890496705 YES

10 ONLINE +DATA/stone/onlinelog/group_10.274.890496717 NO

10 ONLINE +FRA/stone/onlinelog/group_10.337.890496719 YES

20 rows selected.

（3）刪除原有日誌組

切換日誌組，使原有日誌組處於INACTIVE狀態。

SQL> alter system switch logfile;

System 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 YES INACTIVE 2

2 1 50 YES INACTIVE 2

3 2 50 YES INACTIVE 2

4 2 50 YES INACTIVE 2

5 1 100 YES INACTIVE 2

6 1 100 NO CURRENT 2

7 1 100 YES INACTIVE 2

8 2 100 YES INACTIVE 2

9 2 100 YES INACTIVE 2

10 2 100 NO CURRENT 2

10 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

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

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

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

5 1 100 YES INACTIVE 2

6 1 100 NO CURRENT 2

7 1 100 YES INACTIVE 2

8 2 100 YES INACTIVE 2

9 2 100 YES INACTIVE 2

10 2 100 NO CURRENT 2

6 rows selected.

（4）重新增加日誌組

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

Database altered.

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

Database altered.

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

Database altered.

SQL> alter database add logfile thread 2 group 4 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 100 YES UNUSED 2

2 1 100 YES UNUSED 2

3 2 100 YES UNUSED 2

4 2 100 YES UNUSED 2

5 1 100 YES INACTIVE 2

6 1 100 NO CURRENT 2

7 1 100 YES INACTIVE 2

8 2 100 YES INACTIVE 2

9 2 100 YES INACTIVE 2

10 2 100 NO CURRENT 2

10 rows selected.

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

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE +DATA/stone/onlinelog/group_1.267.890498177 NO

1 ONLINE +FRA/stone/onlinelog/group_1.260.890498179 YES

2 ONLINE +DATA/stone/onlinelog/group_2.266.890498187 NO

2 ONLINE +FRA/stone/onlinelog/group_2.259.890498191 YES

3 ONLINE +DATA/stone/onlinelog/group_3.262.890498205 NO

3 ONLINE +FRA/stone/onlinelog/group_3.258.890498207 YES

4 ONLINE +DATA/stone/onlinelog/group_4.261.890498225 NO

4 ONLINE +FRA/stone/onlinelog/group_4.257.890498229 YES

5 ONLINE +DATA/stone/onlinelog/group_5.270.890496635 NO

5 ONLINE +FRA/stone/onlinelog/group_5.333.890496637 YES

6 ONLINE +DATA/stone/onlinelog/group_6.269.890496649 NO

GROUP# STATUS TYPE MEMBER IS_

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

6 ONLINE +FRA/stone/onlinelog/group_6.332.890496651 YES

7 ONLINE +DATA/stone/onlinelog/group_7.271.890496663 NO

7 ONLINE +FRA/stone/onlinelog/group_7.334.890496665 YES

8 ONLINE +DATA/stone/onlinelog/group_8.272.890496679 NO

8 ONLINE +FRA/stone/onlinelog/group_8.335.890496683 YES

9 ONLINE +DATA/stone/onlinelog/group_9.273.890496703 NO

9 ONLINE +FRA/stone/onlinelog/group_9.336.890496705 YES

10 ONLINE +DATA/stone/onlinelog/group_10.274.890496717 NO

10 ONLINE +FRA/stone/onlinelog/group_10.337.890496719 YES

20 rows selected.

SQL> alter system switch logfile;

System altered.

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

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

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

1 1 100 YES ACTIVE 2

2 1 100 NO CURRENT 2

3 2 100 NO CURRENT 2

4 2 100 YES UNUSED 2

5 1 100 YES INACTIVE 2

6 1 100 YES ACTIVE 2

7 1 100 YES INACTIVE 2

8 2 100 YES INACTIVE 2

9 2 100 YES INACTIVE 2

10 2 100 YES ACTIVE 2

10 rows selected.

ORACLE 11G RAC 增加日誌組及增大日誌檔案

相關文章