RAC 線上日誌的管理
由於我的測試環境+DATA沒有空間了，現在準備將聯機日誌檔案都放在+FLASH_RECOVERY_AREA
SQL> select name,total_mb,free_mb from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
DATA 2038 34
FLASH_RECOVERY_AREA 2038 1894
我的環境是node1,node2下各有兩組log,每個loggroup下分別有兩個Logfile,分別存放在+DATA和+FLASH_RECOVERY_AREA
下面做的映象。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 56 5242880 2 YES INACTIVE 834944 24-AUG-12
2 1 57 5242880 2 NO CURRENT 845134 24-AUG-12
3 2 17 5242880 2 YES INACTIVE 806616 24-AUG-12
4 2 18 5242880 2 NO CURRENT 833051 24-AUG-12
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE +DATA/orcl/onlinelog/group_2.262.679060343 NO
2 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_2.258.67 YES
9060343
1 ONLINE +DATA/orcl/onlinelog/group_1.261.679060339 NO
1 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.257.67 YES
9060341
3 STALE ONLINE +DATA/orcl/onlinelog/group_3.265.679060525 NO
3 STALE ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_3.259.67 YES
9060525
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/orcl/onlinelog/group_4.266.679060527 NO
4 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_4.260.67 YES
9060527
先建幾組log在+FLASH_RECOVERY_AREA上
SQL> alter database add logfile thread 1 group 5 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_5') size
50m;
Database altered.
SQL> alter database add logfile thread 2 group 6 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_6') size
50m;
Database altered.
SQL> alter database add logfile thread 1 group 7 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_7') size
50m;
Database altered.
SQL> alter database add logfile thread 2 group 8 ('+FLASH_RECOVERY_AREA/orcl/onlinelog/group_8') size
50m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 58 5242880 2 NO CURRENT 857127 24-AUG-12
2 1 57 5242880 2 NO INACTIVE 845134 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
4 2 18 5242880 2 NO INACTIVE 833051 24-AUG-12
5 1 0 52428800 1 YES UNUSED 0
6 2 0 52428800 1 YES UNUSED 0
7 1 0 52428800 1 YES UNUSED 0
8 2 0 52428800 1 YES UNUSED 0
8 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE +DATA/orcl/onlinelog/group_2.262.679060343 NO
2 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_2.258.67 YES
9060343
1 ONLINE +DATA/orcl/onlinelog/group_1.261.679060339 NO
1 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.257.67 YES
9060341
3 ONLINE +DATA/orcl/onlinelog/group_3.265.679060525 NO
3 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_3.259.67 YES
9060525
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/orcl/onlinelog/group_4.266.679060527 NO
4 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_4.260.67 YES
9060527
5 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_5 NO
6 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_6 NO
7 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_7 NO
8 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_8 NO
12 rows selected.
下面開始switch logfile,drop掉inactive的loggroup,需要注意的是要分別到兩個節點上switch logfile.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 58 5242880 2 NO ACTIVE 857127 24-AUG-12
2 1 61 5242880 2 NO CURRENT 859873 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
4 2 18 5242880 2 NO INACTIVE 833051 24-AUG-12
5 1 59 52428800 1 NO ACTIVE 859811 24-AUG-12
6 2 0 52428800 1 YES UNUSED 0
7 1 60 52428800 1 NO ACTIVE 859865 24-AUG-12
8 2 0 52428800 1 YES UNUSED 0
8 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 5242880 2 NO CURRENT 860001 24-AUG-12
2 1 61 5242880 2 NO ACTIVE 859873 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
5 1 59 52428800 1 NO ACTIVE 859811 24-AUG-12
6 2 0 52428800 1 YES UNUSED 0
7 1 60 52428800 1 NO ACTIVE 859865 24-AUG-12
8 2 0 52428800 1 YES UNUSED 0
7 rows selected.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 5242880 2 NO INACTIVE 860001 24-AUG-12
2 1 61 5242880 2 NO INACTIVE 859873 24-AUG-12
3 2 19 5242880 2 NO CURRENT 858328 24-AUG-12
5 1 63 52428800 1 NO INACTIVE 860089 24-AUG-12
6 2 0 52428800 1 YES UNUSED 0
7 1 64 52428800 1 NO CURRENT 860117 24-AUG-12
8 2 0 52428800 1 YES UNUSED 0
7 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
在第二個節點上執行：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
3 2 19 5242880 2 NO INACTIVE 858328 24-AUG-12
5 1 65 52428800 1 NO INACTIVE 860433 24-AUG-12
6 2 20 52428800 1 NO INACTIVE 860499 24-AUG-12
7 1 66 52428800 1 NO CURRENT 860448 24-AUG-12
8 2 21 52428800 1 NO CURRENT 860567 24-AUG-12
SQL> alter database drop logfile group 3;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
5 1 65 52428800 1 NO INACTIVE 860433 24-AUG-12
6 2 20 52428800 1 NO INACTIVE 860499 24-AUG-12
7 1 66 52428800 1 NO CURRENT 860448 24-AUG-12
8 2 21 52428800 1 NO CURRENT 860567 24-AUG-12
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
5 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_5 NO
6 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_6 NO
7 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_7 NO
8 ONLINE +FLASH_RECOVERY_AREA/orcl/onlinelog/group_8 NO
現在+DATA上面沒有log檔案了，都放在了+FLASH_RECOVERY_AREA上面。
SQL> select name,total_mb,free_mb from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
DATA 2038 98
FLASH_RECOVERY_AREA 2038 1702
可以看出DATA增加了98-34=64M的空間，而實際刪除的logfile只應該有4*5M=20M的空間，這是由於ASM的冗餘政策是
NORMAL的原因。
SQL> select group_number,name,total_mb,type from v$asm_diskgroup
2 ;
GROUP_NUMBER NAME TOTAL_MB TYPE
------------ ------------------------------ ---------- ------
1 DATA 2038 NORMAL
2 FLASH_RECOVERY_AREA 2038 EXTERN
SQL> column path format a30
SQL> select name, header_status, state, path, failgroup from v$asm_disk;
NAME HEADER_STATU STATE PATH FAILGROUP
---------- ------------ -------- ------------------------------ --------------------
VOL1 UNKNOWN NORMAL ORCL:VOL1
VOL2 UNKNOWN NORMAL ORCL:VOL2
VOL3 UNKNOWN NORMAL ORCL:VOL3
VOL4 UNKNOWN NORMAL ORCL:VOL4
我的ASM disk組成是VOL1~4每個1G,VOL1和VOL2組成 DATA,NORMAL的Redundancy，VOL3和VOL4組成
FLASH_RECOVERY_AREA,External的Redundancy.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1059257/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 線上日誌管理Oracle
- 線上日誌損壞
- oracle效能調優:管理oracle日誌之調整線上日誌檔案Oracle
- 非線上日誌丟失
- Oracle RAC 環境下的連線管理Oracle
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- 06【線上日誌分析】之KafkaOffsetMonitor監控工具的搭建Kafka
- oracle 線上日誌全部丟失的資料恢復Oracle資料恢復
- 收集、分析線上日誌資料實戰——ELK
- 線上日誌檔案損壞恢復方法
- Oracle RAC中使用RMAN管理歸檔日誌Oracle
- 16【線上日誌分析】之grafana-4.1.1 Install和新建日誌分析的DashBoardGrafana
- 18【線上日誌分析】之Spark on Yarn配置日誌Web UI(HistoryServer服務)SparkYarnWebUIServer
- 節點2線上日誌生成歸檔日誌在節點1上的初步分析
- [20181225]如何清除註冊的線上日誌.txt
- PowerJob 線上日誌飽受好評的祕訣：小但實用的分散式日誌系統分散式
- 日誌分析必備指令集【來自一段線上日誌的檢視的經歷】
- 23【線上日誌分析】之改造CDH的HDFS的NN,DN程式，日誌輸出為json格式JSON
- RAC 11g的日誌
- RAC的告警日誌檔案
- 控制檔案 線上日誌 回滾表空間 筆記筆記
- 12【線上日誌分析】之RedisLive監控工具的詳細安裝Redis
- RAC環境中的儲存部分管理——RAC管理
- 24【線上日誌分析】之Tomcat的支援log4j,日誌輸出為json格式TomcatJSON
- rac日誌收集方法
- 22【線上日誌分析】之專案第二階段概述
- 11【線上日誌分析】之redis-3.2.5 install(單節點)Redis
- 08【線上日誌分析】之Flume Agent(聚合節點) sink to kafka clusterKafka
- 00【線上日誌分析】之專案概述和GitHub專案地址Github
- 管理ORACLE RAC GUARD——RAC GUARD概念和管理Oracle
- 使用OEM管理RAC資料庫——RAC管理資料庫
- 20170315測試線上日誌與備用日誌大小不一樣
- 04【線上日誌分析】之Flume Agent的3臺收集+1臺聚合到hdfs的搭建
- 17【線上日誌分析】之使用IDEA將工程Build成jar包IdeaUIJAR
- 前線上日誌檔案損壞與ora-600 [4000]處理
- 為什麼我的歸檔檔案比我設定的線上日誌的大小要小？
- 11g rac 日誌
- ORACLE的日誌管理Oracle