Oracle11g調整redo日誌大小、組數和每組成員數
調整redo日誌大小和組數
-
[oracle@scdb1 ~]$ export ORACLE_SID=cams
-
[oracle@scdb1 ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:35:42 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
-
-
SQL> alter database add logfile group 4 '/data/cams/redo04.log' size 1g;
-
-
Database altered.
-
-
SQL> alter database add logfile group 5'/data/cams/redo05.log' size 1g;
-
-
Database altered.
-
-
SQL> alter database add logfile group 6'/data/cams/redo06.log' size 1g;
-
-
Database altered.
-
-
SQL> set linesize 300;
-
SQL> col member for a30;
-
SQL> select * from v$logfile;
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- ------------------------------ ---
-
3 ONLINE /data/cams/redo03.log NO
-
2 ONLINE /data/cams/redo02.log NO
-
1 ONLINE /data/cams/redo01.log NO
-
4 ONLINE /data/cams/redo04.log NO
-
5 ONLINE /data/cams/redo05.log NO
-
6 ONLINE /data/cams/redo06.log NO
-
-
6 rows selected.
-
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
1 52428800 1 INACTIVE
-
2 52428800 1 INACTIVE
-
3 52428800 1 CURRENT
-
4 1073741824 1 UNUSED
-
5 1073741824 1 UNUSED
-
6 1073741824 1 UNUSED
-
-
6 rows selected.
-
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
1 52428800 1 INACTIVE
-
2 52428800 1 INACTIVE
-
3 52428800 1 ACTIVE
-
4 1073741824 1 CURRENT
-
5 1073741824 1 UNUSED
-
6 1073741824 1 UNUSED
-
-
6 rows selected.
-
-
SQL> alter system checkpoint;
-
-
System altered.
-
-
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
1 52428800 1 INACTIVE
-
2 52428800 1 INACTIVE
-
3 52428800 1 INACTIVE
-
4 1073741824 1 CURRENT
-
5 1073741824 1 UNUSED
-
6 1073741824 1 UNUSED
-
-
6 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> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
4 1073741824 1 CURRENT
-
5 1073741824 1 UNUSED
-
6 1073741824 1 UNUSED
-
-
SQL> !mv /data/cams/redo01.log /data/cams/redo01.log_bak20160811
-
-
SQL> !mv /data/cams/redo02.log /data/cams/redo02.log_bak20160811
-
-
SQL> !mv /data/cams/redo03.log /data/cams/redo03.log_bak20160811
-
-
SQL> alter database add logfile GROUP 1 ('/data/cams/redo01.log') size 1g;
-
-
Database altered.
-
-
SQL> alter database add logfile GROUP 2 ('/data/cams/redo02.log') size 1g;
-
-
Database altered.
-
-
SQL> alter database add logfile GROUP 3 ('/data/cams/redo03.log') size 1g;
-
-
Database altered.
-
-
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
1 1073741824 1 UNUSED
-
2 1073741824 1 UNUSED
-
3 1073741824 1 UNUSED
-
4 1073741824 1 CURRENT
-
5 1073741824 1 UNUSED
-
6 1073741824 1 UNUSED
-
-
6 rows selected.
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
1 1073741824 1 ACTIVE
-
2 1073741824 1 ACTIVE
-
3 1073741824 1 ACTIVE
-
4 1073741824 1 ACTIVE
-
5 1073741824 1 ACTIVE
-
6 1073741824 1 CURRENT
-
-
6 rows selected.
-
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
-
-
GROUP# BYTES MEMBERS STATUS
-
---------- ---------- ---------- ----------------
-
1 1073741824 1 INACTIVE
-
2 1073741824 1 INACTIVE
-
3 1073741824 1 INACTIVE
-
4 1073741824 1 CURRENT
-
5 1073741824 1 INACTIVE
-
6 1073741824 1 INACTIVE
-
-
SQL> exit
- Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
增加redo日誌組成員數
-
SQL> col member for a30;
-
SQL> /
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- ------------------------------ ---
-
1 ONLINE /data/cams/redo01.log NO
-
2 ONLINE /data/cams/redo02.log NO
-
3 ONLINE /data/cams/redo03.log NO
-
4 ONLINE /data/cams/redo04.log NO
-
5 ONLINE /data/cams/redo05.log NO
-
6 ONLINE /data/cams/redo06.log NO
-
修改儲存許可權
-
chown -R oracle:oinstall /file
-
chown -R oracle:oinstall /backup
-
-
新增組成員
-
SQL> !mkdir -p /file/cams/redo
-
SQL> !mkdir -p /backup/cams/redo
-
SQL> alter database add logfile member '/file/cams/redo/redo01.log' to group 1;
-
SQL> alter database add logfile member '/backup/cams/redo/redo01.log' to group 1;
-
SQL> alter database add logfile member '/file/cams/redo/redo02.log' to group 2;
-
SQL> alter database add logfile member '/backup/cams/redo/redo02.log' to group 2;
-
SQL> alter database add logfile member '/file/cams/redo/redo03.log' to group 3;
-
SQL> alter database add logfile member '/backup/cams/redo/redo03.log' to group 3;
-
SQL> alter database add logfile member '/file/cams/redo/redo04.log' to group 4;
-
SQL> alter database add logfile member '/backup/cams/redo/redo04.log' to group 4;
-
SQL> alter database add logfile member '/file/cams/redo/redo05.log' to group 5;
-
SQL> alter database add logfile member '/backup/cams/redo/redo05.log' to group 5;
-
SQL> alter database add logfile member '/file/cams/redo/redo06.log' to group 6;
-
SQL> alter database add logfile member '/backup/cams/redo/redo06.log' to group 6;
-
-
SQL> select * from v$logfile;
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- ------------------------------ ---
-
1 ONLINE /data/cams/redo01.log NO
-
2 ONLINE /data/cams/redo02.log NO
-
3 ONLINE /data/cams/redo03.log NO
-
4 ONLINE /data/cams/redo04.log NO
-
5 ONLINE /data/cams/redo05.log NO
-
6 ONLINE /data/cams/redo06.log NO
-
1 INVALID ONLINE /file/cams/redo/redo01.log NO
-
1 INVALID ONLINE /backup/cams/redo/redo01.log NO
-
2 INVALID ONLINE /file/cams/redo/redo02.log NO
-
2 INVALID ONLINE /backup/cams/redo/redo02.log NO
-
3 INVALID ONLINE /file/cams/redo/redo03.log NO
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- ------------------------------ ---
-
3 INVALID ONLINE /backup/cams/redo/redo03.log NO
-
4 INVALID ONLINE /file/cams/redo/redo04.log NO
-
4 INVALID ONLINE /backup/cams/redo/redo04.log NO
-
5 INVALID ONLINE /file/cams/redo/redo05.log NO
-
5 INVALID ONLINE /backup/cams/redo/redo05.log NO
-
6 INVALID ONLINE /file/cams/redo/redo06.log NO
-
6 INVALID ONLINE /backup/cams/redo/redo06.log NO
-
-
18 rows selected.
-
剛加入到redolog group的日誌檔案在被使用之前也是INVALID狀態
-
SQL> alter system switch logfile;
-
-
System altered.
-
-
SQL> select * from v$logfile;
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- ------------------------------ ---
-
1 ONLINE /data/cams/redo01.log NO
-
2 ONLINE /data/cams/redo02.log NO
-
3 ONLINE /data/cams/redo03.log NO
-
4 ONLINE /data/cams/redo04.log NO
-
5 ONLINE /data/cams/redo05.log NO
-
6 ONLINE /data/cams/redo06.log NO
-
1 INVALID ONLINE /file/cams/redo/redo01.log NO
-
1 INVALID ONLINE /backup/cams/redo/redo01.log NO
-
2 INVALID ONLINE /file/cams/redo/redo02.log NO
-
2 INVALID ONLINE /backup/cams/redo/redo02.log NO
-
3 ONLINE /file/cams/redo/redo03.log NO
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- ------------------------------ ---
-
3 ONLINE /backup/cams/redo/redo03.log NO
-
4 INVALID ONLINE /file/cams/redo/redo04.log NO
-
4 INVALID ONLINE /backup/cams/redo/redo04.log NO
-
5 INVALID ONLINE /file/cams/redo/redo05.log NO
-
5 INVALID ONLINE /backup/cams/redo/redo05.log NO
-
6 INVALID ONLINE /file/cams/redo/redo06.log NO
-
6 INVALID ONLINE /backup/cams/redo/redo06.log NO
-
- 18 rows selected.
如果覺得每組成員太多，可能會影響效率，可以刪除一組冗餘，提高效率：
-
SQL> alter database drop logfile member '/backup/cams/redo/redo01.log';
-
SQL> alter database drop logfile member '/backup/cams/redo/redo02.log';
-
SQL> alter database drop logfile member '/backup/cams/redo/redo03.log';
-
SQL> alter database drop logfile member '/backup/cams/redo/redo04.log';
-
SQL> alter database drop logfile member '/backup/cams/redo/redo05.log';
- SQL> alter database drop logfile member '/backup/cams/redo/redo06.log';
這裡建議將redo日誌的其他成員存放在不同的磁碟中，以避免磁碟故障造成資料損失。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-2142612/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle DataGuard環境主備庫日誌組數和大小調整Oracle
- Oracle調整redo log日誌大小Oracle
- 10G DATAGUARD增加REDO日誌組、刪除日誌組、刪除日誌成員實驗
- 對日誌檔案組和日誌檔案組成員的管理
- 刪除日誌組成員
- 備份恢復之redo日誌組member成員丟失
- MySQL 5.6修改REDO日誌的大小和個數MySql
- 線上修改REDO LOG的大小及增加新的日誌組
- redo日誌組資訊查詢
- 線上重建redo log日誌組
- 【REDO】刪除聯機重做日誌檔案組成員的注意事項
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 刪除日誌檔案組或成員
- 【rac】實驗三：增加日誌組成員
- 為oracle新增重做日誌組及重做日誌成員Oracle
- 刪除日誌檔案組與日誌檔案成員
- 在ASM下增加一個日誌組成員ASM
- 新增日誌檔案組與日誌檔案成員
- 新增日誌組以及調整日誌的大小
- 【備份恢復】 恢復重做日誌組成員
- RAC環境下的redo日誌組重建
- oracle rac+adg調整redo日誌組導致adg備庫ogg抽取程式abendOracle
- Oracle日誌組新增冗餘檔案和日誌組Oracle
- oracle 修改日誌大小及增加日誌成員Oracle
- dg庫日誌應用慢引數調整
- ORACLE RAC+DG調整redo大小Oracle
- oracle丟失的是所有的redo日誌組Oracle
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- 檢視oracle的redo日誌組切換頻率Oracle
- Linux 系統日誌的組成Linux
- jQuery滑鼠拖動調整數字大小jQuery
- Oracle產生redo日誌量大小統計Oracle
- 修改online redo日誌檔案大小
- 【kingsql分享】RAC擴大online日誌組大小SQL
- 【REDO】刪除聯機重做日誌檔案組的注意事項
- 【力扣】組合總數（另一種整數溢位）力扣
- 【數學】組合數學 - 排列組合
- oracle線上調整重做日誌Oracle