1. 准备工作
1.1. 查看日志组基本情况
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 18:46:28 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>
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 CURRENT 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 CURRENT 50
1.2. 查看日志成员路径
SYS@RACDB1>col member for a50
SYS@RACDB1>select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 +FRA/racdb/onlinelog/group_1.257.926529029
1 +DATA/racdb/onlinelog/group_1.261.926529029
2 +FRA/racdb/onlinelog/group_2.258.926529029
2 +DATA/racdb/onlinelog/group_2.262.926529029
3 +DATA/racdb/onlinelog/group_3.266.926529301
3 +FRA/racdb/onlinelog/group_3.259.926529301
4 +DATA/racdb/onlinelog/group_4.267.926529301
4 +FRA/racdb/onlinelog/group_4.260.926529301
8 rows selected.
2. 增加日志组
SYS@RACDB1>alter database add logfile thread 1 group 5 '+DATA' size 50m;
Database altered.
SYS@RACDB1>alter database add logfile thread 1 group 6 '+DATA' size 50m;
Database altered.
SYS@RACDB1>alter database add logfile thread 2 group 7 '+DATA' size 50m;
Database altered.
SYS@RACDB1>alter database add logfile thread 2 group 8 '+DATA' size 50m;
Database altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log order by 1,2;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 CURRENT 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 CURRENT 50
5 1 1 UNUSED 50
6 1 1 UNUSED 50
7 2 1 UNUSED 50
8 2 1 UNUSED 50
8 rows selected.
SYS@RACDB1>select group#,member from v$logfile order by 1 ;
GROUP# MEMBER
---------- --------------------------------------------------
1 +DATA/racdb/onlinelog/group_1.261.926529029
1 +FRA/racdb/onlinelog/group_1.257.926529029
2 +DATA/racdb/onlinelog/group_2.262.926529029
2 +FRA/racdb/onlinelog/group_2.258.926529029
3 +DATA/racdb/onlinelog/group_3.266.926529301
3 +FRA/racdb/onlinelog/group_3.259.926529301
4 +DATA/racdb/onlinelog/group_4.267.926529301
4 +FRA/racdb/onlinelog/group_4.260.926529301
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
---------- --------------------------------------------------
8 +DATA/racdb/onlinelog/group_8.271.926535323
12 rows selected.
3. 删除日志组
删除一节点的 1 号日志组， 删除二节点的 3 号日志组
1) 查看日志组的状态，
发现 1 号日志组的状态为 CURRENT
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log order by 1,2;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 CURRENT 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 CURRENT 50
5 1 1 UNUSED 50
6 1 1 UNUSED 50
7 2 1 UNUSED 50
8 2 1 UNUSED 50
8 rows selected.
2) 手工切换一下日志组， 查看日志组状态， 状态为 ACTIVE
SYS@RACDB1>alter system switch logfile;
System altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log order by 1,2;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 ACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 CURRENT 50
5 1 1 CURRENT 50
6 1 1 UNUSED 50
7 2 1 UNUSED 50
8 2 1 UNUSED 50
8 rows selected.
3) 日志组的状态为 ACTIVE 时， 说明在实例恢复时需要这个日志组， 所以不能删除， 手工执行检查点事件， 使其日志组状态变为 INACTIVE
SYS@RACDB1>alter system checkpoint;
System altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log order by 1,2;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 CURRENT 50
5 1 1 CURRENT 50
6 1 1 UNUSED 50
7 2 1 UNUSED 50
8 2 1 UNUSED 50
8 rows selected.
4) 删除 1 号日志组
SYS@RACDB1>alter database drop logfile group 1;
Database altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log order by 1,2;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 CURRENT 50
5 1 1 CURRENT 50
6 1 1 UNUSED 50
7 2 1 UNUSED 50
8 2 1 UNUSED 50
7 rows selected.
5) 删除 3 号日志组
SYS@RACDB1>alter database drop logfile group 3;
Database altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log order by 1,2;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
2 1 2 INACTIVE 50
4 2 2 CURRENT 50
5 1 1 CURRENT 50
6 1 1 UNUSED 50
7 2 1 UNUSED 50
8 2 1 UNUSED 50
6 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2128194/ ，如需转载，请注明出处，否则将追究法律责任。
