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
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
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*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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-1800592/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle日誌組新增冗餘檔案和日誌組Oracle
- 【rac】實驗四：增加日誌組
- oracle 11g rac 新增重做日誌檔案Oracle
- 【Oracle日誌】- 日誌檔案重建Oracle
- Oracle 11g RAC檢視ASM日誌、grid日誌和DB日誌OracleASM
- 對日誌檔案組和日誌檔案組成員的管理
- 增加日誌檔案，修改當前日誌檔案大小
- 【rac】實驗三：增加日誌組成員
- 刪除日誌檔案組與日誌檔案成員
- oracle 修改日誌大小及增加日誌成員Oracle
- 11g rac 日誌
- RAC的告警日誌檔案
- Oracle 11g alter日誌檔案位置Oracle
- 為oracle新增重做日誌組及重做日誌成員Oracle
- oracle 日誌檔案管理Oracle
- 新增日誌檔案組與日誌檔案成員
- RAC 11g的日誌
- ORACLE 11G RAC--日誌路徑Oracle
- 10G DATAGUARD增加REDO日誌組、刪除日誌組、刪除日誌成員實驗
- 16、重做日誌檔案的狀態及重做日誌組的狀態說明
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- Oracle日誌檔案常用操作Oracle
- oracle 關於-日誌檔案Oracle
- 日誌檔案
- 【VMWARE】vCenter Server 日誌檔案的位置及組成Server
- 【RAC】Oracle RAC叢集環境下日誌檔案結構Oracle
- 日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
- oracle日誌分析從列表中移去一個日誌檔案Oracle
- SQL Server日誌檔案總結及日誌滿的處理SQLServer
- oracle中清空日誌組Oracle
- Oracle RAC中使用RMAN管理歸檔日誌Oracle
- oracle歸檔日誌Oracle
- Oracle 歸檔日誌Oracle
- RAC 開啟歸檔日誌
- oracle12告警日誌檔案?Oracle
- Oracle重做日誌檔案基礎Oracle
- oracle 聯機重做日誌檔案Oracle
- 轉移Oracle日誌檔案位置Oracle