【rac】實驗三：增加日誌組成員
1. 準備工作
1.1. 檢視日誌組基本情況
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 19:21:59 2016
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
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 INACTIVE 50
3 2 1 INACTIVE 50
4 2 1 INACTIVE 50
5 1 1 CURRENT 50
6 1 1 INACTIVE 50
7 2 1 CURRENT 50
8 2 1 INACTIVE 50
8 rows selected.
SYS@RACDB1>col member for a45;
SYS@RACDB1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
8 +DATA/racdb/onlinelog/group_8.271.926535323
8 rows selected.
2. 增加日誌組成員
——檢視日誌位置：
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCR/
ASMCMD> cd data
ASMCMD> ls
RACDB/
ASMCMD> cd racdb
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileRACDB.ora
ASMCMD> cd onlinelog
ASMCMD> ls
group_1.267.926536571
group_2.262.926536585
group_3.266.926536603
group_4.261.926536613
group_5.272.926535655
group_6.269.926535221
group_7.270.926535251
group_8.271.926535323
ASMCMD>
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo01_a.log' to group 1;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo02_a.log' to group 2;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo03_a.log' to group 3;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo04_a.log' to group 4;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo05_a.log' to group 5;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo06_a.log' to group 6;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo07_a.log' to group 7;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo08_a.log' to group 8;
Database altered.
SYS@RACDB1>alter database add logfile member '+fra/racdb/onlinelog/redo01_b.log' to group 1;
alter database add logfile member '+fra/racdb/onlinelog/redo02_b.log' to group 2;
alter database add logfile member '+fra/racdb/onlinelog/redo03_b.log' to group 3;
alter database add logfile member '+fra/racdb/onlinelog/redo04_b.log' to group 4;
alter database add logfile member '+fra/racdb/onlinelog/redo05_b.log' to group 5;
alter database add logfile member '+fra/racdb/onlinelog/redo06_b.log' to group 6;
alter database add logfile member '+fra/racdb/onlinelog/redo07_b.log' to group 7;
alter database add logfile member '+fra/racdb/onlinelog/redo08_b.log' to group 8;
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
——檢視日誌組成員：
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 3 INACTIVE 50
2 1 3 INACTIVE 50
3 2 3 INACTIVE 50
4 2 3 INACTIVE 50
5 1 3 CURRENT 50
6 1 3 INACTIVE 50
7 2 3 CURRENT 50
8 2 3 INACTIVE 50
8 rows selected.
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
1 +DATA/racdb/onlinelog/redo01_a.log INVALID
2 +DATA/racdb/onlinelog/redo02_a.log INVALID
3 +DATA/racdb/onlinelog/redo03_a.log INVALID
4 +DATA/racdb/onlinelog/redo04_a.log INVALID
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
8 +DATA/racdb/onlinelog/group_8.271.926535323
5 +DATA/racdb/onlinelog/redo05_a.log INVALID
6 +DATA/racdb/onlinelog/redo06_a.log INVALID
7 +DATA/racdb/onlinelog/redo07_a.log INVALID
8 +DATA/racdb/onlinelog/redo08_a.log INVALID
1 +FRA/racdb/onlinelog/redo01_b.log INVALID
2 +FRA/racdb/onlinelog/redo02_b.log INVALID
3 +FRA/racdb/onlinelog/redo03_b.log INVALID
4 +FRA/racdb/onlinelog/redo04_b.log INVALID
5 +FRA/racdb/onlinelog/redo05_b.log INVALID
6 +FRA/racdb/onlinelog/redo06_b.log INVALID
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
7 +FRA/racdb/onlinelog/redo07_b.log INVALID
8 +FRA/racdb/onlinelog/redo08_b.log INVALID
24 rows selected.
3. 刪除日誌組成員
3.1. 什麼情況無法刪除日誌成員
有以下兩種情況無法刪除：
1) 狀態為 CURRENT 的日誌組成員；
2) 日誌組成員的狀態為 INVALID 的。
3.2. 檢視日誌組成員狀態
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
8 +DATA/racdb/onlinelog/group_8.271.926535323
1 +DATA/racdb/onlinelog/redo01_a.log INVALID
2 +DATA/racdb/onlinelog/redo02_a.log INVALID
3 +DATA/racdb/onlinelog/redo03_a.log INVALID
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
4 +DATA/racdb/onlinelog/redo04_a.log INVALID
5 +DATA/racdb/onlinelog/redo05_a.log INVALID
6 +DATA/racdb/onlinelog/redo06_a.log INVALID
7 +DATA/racdb/onlinelog/redo07_a.log INVALID
8 +DATA/racdb/onlinelog/redo08_a.log INVALID
1 +FRA/racdb/onlinelog/redo01_b.log INVALID
2 +FRA/racdb/onlinelog/redo02_b.log INVALID
3 +FRA/racdb/onlinelog/redo03_b.log INVALID
4 +FRA/racdb/onlinelog/redo04_b.log INVALID
5 +FRA/racdb/onlinelog/redo05_b.log INVALID
6 +FRA/racdb/onlinelog/redo06_b.log INVALID
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
7 +FRA/racdb/onlinelog/redo07_b.log INVALID
8 +FRA/racdb/onlinelog/redo08_b.log INVALID
24 rows selected.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 3 INACTIVE 50
2 1 3 INACTIVE 50
3 2 3 INACTIVE 50
4 2 3 INACTIVE 50
5 1 3 CURRENT 50
6 1 3 INACTIVE 50
7 2 3 CURRENT 50
8 2 3 INACTIVE 50
8 rows selected.
3.3. 切換日誌組
透過切換日誌組使日誌成員的狀態為空（也就是有效），每個節點有幾組日誌， 就切換幾次即可
1) 一節點切換 4 次
SYS@RACDB1>alter system switch logfile;
System altered.
SYS@RACDB1>/
System altered.
SYS@RACDB1>/
System altered.
SYS@RACDB1>/
System altered.
2) 二節點切換 4 次
SYS@RACDB2>alter system switch logfile;
System altered.
SYS@RACDB2>/
System altered.
SYS@RACDB2>/
System altered.
SYS@RACDB2>/
System altered.
3) 檢視切換後日志組成員的狀態
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
8 +DATA/racdb/onlinelog/group_8.271.926535323
1 +DATA/racdb/onlinelog/redo01_a.log
2 +DATA/racdb/onlinelog/redo02_a.log
3 +DATA/racdb/onlinelog/redo03_a.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
4 +DATA/racdb/onlinelog/redo04_a.log
5 +DATA/racdb/onlinelog/redo05_a.log
6 +DATA/racdb/onlinelog/redo06_a.log
7 +DATA/racdb/onlinelog/redo07_a.log
8 +DATA/racdb/onlinelog/redo08_a.log
1 +FRA/racdb/onlinelog/redo01_b.log
2 +FRA/racdb/onlinelog/redo02_b.log
3 +FRA/racdb/onlinelog/redo03_b.log
4 +FRA/racdb/onlinelog/redo04_b.log
5 +FRA/racdb/onlinelog/redo05_b.log
6 +FRA/racdb/onlinelog/redo06_b.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
7 +FRA/racdb/onlinelog/redo07_b.log
8 +FRA/racdb/onlinelog/redo08_b.log
24 rows selected.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 3 INACTIVE 50
2 1 3 INACTIVE 50
3 2 3 INACTIVE 50
4 2 3 INACTIVE 50
5 1 3 CURRENT 50
6 1 3 INACTIVE 50
7 2 3 CURRENT 50
8 2 3 INACTIVE 50
8 rows selected.
3.4. 刪除非 CURRENT 狀態的日誌組成員
1) 透過前面的查詢我們知道，日誌組 5 和 7 的狀態為 CURRENT
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_1.267.926536571';
alter database drop logfile member '+DATA/racdb/onlinelog/group_2.262.926536585';
alter database drop logfile member '+DATA/racdb/onlinelog/group_3.266.926536603';
Database altered.
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_4.261.926536613';
alter database drop logfile member '+DATA/racdb/onlinelog/group_6.269.926535221';
Database altered.
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_8.271.926535323';
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
——檢視日誌狀態：
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
5 +DATA/racdb/onlinelog/group_5.272.926535655
7 +DATA/racdb/onlinelog/group_7.270.926535251
1 +DATA/racdb/onlinelog/redo01_a.log
2 +DATA/racdb/onlinelog/redo02_a.log
3 +DATA/racdb/onlinelog/redo03_a.log
4 +DATA/racdb/onlinelog/redo04_a.log
5 +DATA/racdb/onlinelog/redo05_a.log
6 +DATA/racdb/onlinelog/redo06_a.log
7 +DATA/racdb/onlinelog/redo07_a.log
8 +DATA/racdb/onlinelog/redo08_a.log
1 +FRA/racdb/onlinelog/redo01_b.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
2 +FRA/racdb/onlinelog/redo02_b.log
3 +FRA/racdb/onlinelog/redo03_b.log
4 +FRA/racdb/onlinelog/redo04_b.log
5 +FRA/racdb/onlinelog/redo05_b.log
6 +FRA/racdb/onlinelog/redo06_b.log
7 +FRA/racdb/onlinelog/redo07_b.log
8 +FRA/racdb/onlinelog/redo08_b.log
18 rows selected.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 3 CURRENT 50
6 1 2 INACTIVE 50
7 2 3 CURRENT 50
8 2 2 INACTIVE 50
8 rows selected.
2) 切換日誌組
——節點一切換 1 次
SYS@RACDB1>alter system switch logfile;
System altered.
——節點二切換 1 次
SYS@RACDB2>alter system switch logfile;
System altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 3 ACTIVE 50
6 1 2 CURRENT 50
7 2 3 INACTIVE 50
8 2 2 CURRENT 50
8 rows selected.
——節點一進行歸檔：
SYS@RACDB1>alter system checkpoint;
System altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 3 INACTIVE 50
6 1 2 CURRENT 50
7 2 3 INACTIVE 50
8 2 2 CURRENT 50
8 rows selected.
3）最後刪除 5號日誌組和7號日誌組的成員
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_5.272.926535655';
alter database drop logfile member '+DATA/racdb/onlinelog/group_7.270.926535251';
Database altered.
SYS@RACDB1>
Database altered.
4) 驗證
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 2 INACTIVE 50
6 1 2 CURRENT 50
7 2 2 INACTIVE 50
8 2 2 CURRENT 50
8 rows selected.
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/redo01_a.log
2 +DATA/racdb/onlinelog/redo02_a.log
3 +DATA/racdb/onlinelog/redo03_a.log
4 +DATA/racdb/onlinelog/redo04_a.log
5 +DATA/racdb/onlinelog/redo05_a.log
6 +DATA/racdb/onlinelog/redo06_a.log
7 +DATA/racdb/onlinelog/redo07_a.log
8 +DATA/racdb/onlinelog/redo08_a.log
1 +FRA/racdb/onlinelog/redo01_b.log
2 +FRA/racdb/onlinelog/redo02_b.log
3 +FRA/racdb/onlinelog/redo03_b.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
4 +FRA/racdb/onlinelog/redo04_b.log
5 +FRA/racdb/onlinelog/redo05_b.log
6 +FRA/racdb/onlinelog/redo06_b.log
7 +FRA/racdb/onlinelog/redo07_b.log
8 +FRA/racdb/onlinelog/redo08_b.log
16 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2128193/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【rac】實驗四：增加日誌組
- 10G DATAGUARD增加REDO日誌組、刪除日誌組、刪除日誌成員實驗
- 在ASM下增加一個日誌組成員ASM
- 刪除日誌組成員
- oracle 修改日誌大小及增加日誌成員Oracle
- ORACLE 11G RAC 增加日誌組及增大日誌檔案Oracle
- 對日誌檔案組和日誌檔案組成員的管理
- 為oracle新增重做日誌組及重做日誌成員Oracle
- 刪除日誌檔案組與日誌檔案成員
- 刪除日誌檔案組或成員
- 新增日誌檔案組與日誌檔案成員
- 【備份恢復】 恢復重做日誌組成員
- 備份恢復之redo日誌組member成員丟失
- Oracle11g調整redo日誌大小、組數和每組成員數Oracle
- 【rac】實驗二：增加控制檔案
- RAC環境下的redo日誌組重建
- Linux 系統日誌的組成Linux
- 【REDO】刪除聯機重做日誌檔案組成員的注意事項
- 7.增加日誌
- 線上修改REDO LOG的大小及增加新的日誌組
- 不能刪除聯機日誌組中的成員ORA-00362，解決方法。
- rac日誌收集方法
- 【kingsql分享】RAC擴大online日誌組大小SQL
- Oracle日誌組新增冗餘檔案和日誌組Oracle
- 【VMWARE】vCenter Server 日誌檔案的位置及組成Server
- 11月26實驗室日誌
- 11g rac 日誌
- 日誌追蹤：log增加traceId
- Oracle 11g RAC檢視ASM日誌、grid日誌和DB日誌OracleASM
- RAC 開啟歸檔日誌
- RAC 11g的日誌
- ORACLE RAC 日誌結構解析Oracle
- RAC的告警日誌檔案
- 網格RAC基礎日誌
- Nginx實戰（三） 日誌配置與切割Nginx
- oracle中清空日誌組Oracle
- 增加日誌檔案，修改當前日誌檔案大小
- 小組成員的github地址Github