首页 > 代码库 > 实验案例：删除文件系统日志组，保留ASM日志组
实验案例：删除文件系统日志组，保留ASM日志组
实验案例：删除文件系统日志组，保留ASM日志组
说明：
oracle文件系统迁移至ASM，需要删除文件系统的日志组，只保留ASM日志组。若两者均保留，会出现读写日志性能不平衡状态。向ASM中读写日志要远快于向文件系统中读写日志。
操作步骤：
SQL> select * from v$log;
--查看到当前日志组为2，第1、3日志组处于INACTIVE状态，脏块已经写完，可以将这两个日志组删除
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 2 YES INACTIVE
864542 29-MAY-14
2 1 7 52428800 2 NO CURRENT
864576 29-MAY-14
3 1 3 52428800 2 YES INACTIVE
864552 29-MAY-14
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
4 1 4 52428800 1 YES INACTIVE
864565 29-MAY-14
5 1 5 52428800 1 YES INACTIVE
864568 29-MAY-14
6 1 6 52428800 1 YES INACTIVE
864571 29-MAY-14
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
2 1 7 52428800 2 NO CURRENT
864576 29-MAY-14
3 1 3 52428800 2 YES INACTIVE
864552 29-MAY-14
4 1 4 52428800 1 YES INACTIVE
864565 29-MAY-14
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
5 1 5 52428800 1 YES INACTIVE
864568 29-MAY-14
6 1 6 52428800 1 YES INACTIVE
864571 29-MAY-14
SQL> col name for a40
SQL> set pagesize 200
SQL> r
1* select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#
GROUP# SEQUENCE# NAME STATUS
---------- ---------- ---------------------------------------- ----------------
ARC
---
3 3 /u01/app/oracle/oradata/metro/redo03.log INACTIVE
YES
2 7 /u01/app/oracle/oradata/metro/redo02.log CURRENT
NO
4 4 +DATA/metro/onlinelog/group_4.274.848866 INACTIVE
125
YES
2 7 /disk1/metro/redofile/redo02a.log CURRENT
NO
3 3 /disk1/metro/redofile/redo03a.log INACTIVE
YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE
171
YES
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE
215
YES
7 rows selected.
SQL> set linesize 200;
SQL> r
1* select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
3 3 /u01/app/oracle/oradata/metro/redo03.log INACTIVE YES
2 7 /u01/app/oracle/oradata/metro/redo02.log CURRENT NO
4 4 +DATA/metro/onlinelog/group_4.274.848866 INACTIVE YES
125
2 7 /disk1/metro/redofile/redo02a.log CURRENT NO
3 3 /disk1/metro/redofile/redo03a.log INACTIVE YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
7 rows selected.
SQL> alter database drop logfile group 3;
--删除日志组3
Database altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
2 7 /u01/app/oracle/oradata/metro/redo02.log CURRENT NO
4 4 +DATA/metro/onlinelog/group_4.274.848866 INACTIVE YES
125
2 7 /disk1/metro/redofile/redo02a.log CURRENT NO
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> alter system switch logfile;
--切归档，改变当前日志组
System altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
--日志组处于active状态，存在脏块没有写完，暂不可删除日志组2
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
2 7 /u01/app/oracle/oradata/metro/redo02.log ACTIVE YES
4 8 +DATA/metro/onlinelog/group_4.274.848866 CURRENT NO
125
2 7 /disk1/metro/redofile/redo02a.log ACTIVE YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> r
1* select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#
--几分钟后，再次查看日志状态，日志组2已经将脏块写完，此时可以删除该日志组了
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
2 7 /u01/app/oracle/oradata/metro/redo02.log INACTIVE YES
4 8 +DATA/metro/onlinelog/group_4.274.848866 CURRENT NO
125
2 7 /disk1/metro/redofile/redo02a.log INACTIVE YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> alter database drop logfile group 2;
--删除日志组2
Database altered.
说明：
oracle文件系统迁移至ASM，需要删除文件系统的日志组，只保留ASM日志组。若两者均保留，会出现读写日志性能不平衡状态。向ASM中读写日志要远快于向文件系统中读写日志。
操作步骤：
SQL> select * from v$log;
--查看到当前日志组为2，第1、3日志组处于INACTIVE状态，脏块已经写完，可以将这两个日志组删除
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 2 YES INACTIVE
864542 29-MAY-14
2 1 7 52428800 2 NO CURRENT
864576 29-MAY-14
3 1 3 52428800 2 YES INACTIVE
864552 29-MAY-14
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
4 1 4 52428800 1 YES INACTIVE
864565 29-MAY-14
5 1 5 52428800 1 YES INACTIVE
864568 29-MAY-14
6 1 6 52428800 1 YES INACTIVE
864571 29-MAY-14
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
2 1 7 52428800 2 NO CURRENT
864576 29-MAY-14
3 1 3 52428800 2 YES INACTIVE
864552 29-MAY-14
4 1 4 52428800 1 YES INACTIVE
864565 29-MAY-14
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
5 1 5 52428800 1 YES INACTIVE
864568 29-MAY-14
6 1 6 52428800 1 YES INACTIVE
864571 29-MAY-14
SQL> col name for a40
SQL> set pagesize 200
SQL> r
1* select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#
GROUP# SEQUENCE# NAME STATUS
---------- ---------- ---------------------------------------- ----------------
ARC
---
3 3 /u01/app/oracle/oradata/metro/redo03.log INACTIVE
YES
2 7 /u01/app/oracle/oradata/metro/redo02.log CURRENT
NO
4 4 +DATA/metro/onlinelog/group_4.274.848866 INACTIVE
125
YES
2 7 /disk1/metro/redofile/redo02a.log CURRENT
NO
3 3 /disk1/metro/redofile/redo03a.log INACTIVE
YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE
171
YES
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE
215
YES
7 rows selected.
SQL> set linesize 200;
SQL> r
1* select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
3 3 /u01/app/oracle/oradata/metro/redo03.log INACTIVE YES
2 7 /u01/app/oracle/oradata/metro/redo02.log CURRENT NO
4 4 +DATA/metro/onlinelog/group_4.274.848866 INACTIVE YES
125
2 7 /disk1/metro/redofile/redo02a.log CURRENT NO
3 3 /disk1/metro/redofile/redo03a.log INACTIVE YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
7 rows selected.
SQL> alter database drop logfile group 3;
--删除日志组3
Database altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
2 7 /u01/app/oracle/oradata/metro/redo02.log CURRENT NO
4 4 +DATA/metro/onlinelog/group_4.274.848866 INACTIVE YES
125
2 7 /disk1/metro/redofile/redo02a.log CURRENT NO
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> alter system switch logfile;
--切归档，改变当前日志组
System altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
--日志组处于active状态，存在脏块没有写完，暂不可删除日志组2
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
2 7 /u01/app/oracle/oradata/metro/redo02.log ACTIVE YES
4 8 +DATA/metro/onlinelog/group_4.274.848866 CURRENT NO
125
2 7 /disk1/metro/redofile/redo02a.log ACTIVE YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> r
1* select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#
--几分钟后，再次查看日志状态，日志组2已经将脏块写完，此时可以删除该日志组了
GROUP# SEQUENCE# NAME STATUS ARC
---------- ---------- ---------------------------------------- ---------------- ---
2 7 /u01/app/oracle/oradata/metro/redo02.log INACTIVE YES
4 8 +DATA/metro/onlinelog/group_4.274.848866 CURRENT NO
125
2 7 /disk1/metro/redofile/redo02a.log INACTIVE YES
5 5 +DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171
6 6 +DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> alter database drop logfile group 2;
--删除日志组2
Database altered.
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。