Exadata数据库DG环境的Redolog日志文件瘦身提高传送效率
局域网内的Exadata一体机数据库DG也放在同一机房内的虚拟机上，项目时把PRD的redolog大小设置为4G，传输到DG备机上也的archivelog归档日志过大，网络传送花时间长，因此需要重新设置redolog大小
查询日志情况
col group# format 99
col member format a60
col status format a7
col TYPE format a7
col IS_RECOVERY_DEST_FILE format a3
--REDO ONLINE LOG STANDBY LOG
select group#,member,type from v$logfile where type='STANDBY';
09:05:50 SQL> select group#,member,type from v$logfile where type='STANDBY';
GROUP# MEMBER TYPE
15 /oracle/PRD/sapdata1/STDBY/onlinelog/group_15.2333.869677413 STANDBY
16 /oracle/PRD/sapdata1/STDBY/onlinelog/group_16.2332.869677415 STANDBY
17 /oracle/PRD/sapdata1/STDBY/onlinelog/group_17.2331.869677417 STANDBY
18 /oracle/PRD/sapdata1/STDBY/onlinelog/group_18.2330.869677419 STANDBY
19 /oracle/PRD/sapdata1/STDBY/onlinelog/group_19.2329.869677421 STANDBY
25 /oracle/PRD/sapdata1/STDBY/onlinelog/group_25.1862.869677441 STANDBY
26 /oracle/PRD/sapdata1/STDBY/onlinelog/group_26.1861.869677445 STANDBY
27 /oracle/PRD/sapdata1/STDBY/onlinelog/group_27.1860.869677447 STANDBY
28 /oracle/PRD/sapdata1/STDBY/onlinelog/group_28.1859.869677449 STANDBY
29 /oracle/PRD/sapdata1/STDBY/onlinelog/group_29.1857.869677451 STANDBY
10 rows selected.
09:06:59 SQL>
--select * from v$logfile where type='STANDBY';
--＃查询为standby log 10组
select group#,member,type from v$logfile where type<>'STANDBY';
09:07:59 SQL> select group#,member,type from v$logfile where type<>'STANDBY';
GROUP# MEMBER TYPE
11 /oracle/PRD/sapdata1/STDBY/onlinelog/group_11.2478.867924049 ONLINE
12 /oracle/PRD/sapdata1/STDBY/onlinelog/group_12.2477.867924053 ONLINE
13 /oracle/PRD/sapdata1/STDBY/onlinelog/group_13.2476.867924055 ONLINE
14 /oracle/PRD/sapdata1/STDBY/onlinelog/group_14.2410.867924057 ONLINE
21 /oracle/PRD/sapdata1/STDBY/onlinelog/group_21.2416.867924605 ONLINE
22 /oracle/PRD/sapdata1/STDBY/onlinelog/group_22.2415.867924607 ONLINE
23 /oracle/PRD/sapdata1/STDBY/onlinelog/group_23.2413.867924611 ONLINE
24 /oracle/PRD/sapdata1/STDBY/onlinelog/group_24.2412.867924613 ONLINE
8 rows selected.
09:08:00 SQL>
--select * from v$logfile where type<>'STANDBY';
--#查询ONLINE LOG 为8组
PRD
09:12:12 SQL> select group#,member,type from v$logfile where type='STANDBY';
GROUP# MEMBER TYPE
15 +DATA/prd/onlinelog/group_15.2333.869677413 STANDBY
16 +DATA/prd/onlinelog/group_16.2332.869677415 STANDBY
17 +DATA/prd/onlinelog/group_17.2331.869677417 STANDBY
18 +DATA/prd/onlinelog/group_18.2330.869677419 STANDBY
19 +DATA/prd/onlinelog/group_19.2329.869677421 STANDBY
25 +DATA/prd/onlinelog/group_25.1862.869677441 STANDBY
26 +DATA/prd/onlinelog/group_26.1861.869677445 STANDBY
27 +DATA/prd/onlinelog/group_27.1860.869677447 STANDBY
28 +DATA/prd/onlinelog/group_28.1859.869677449 STANDBY
29 +DATA/prd/onlinelog/group_29.1857.869677451 STANDBY
10 rows selected.
09:12:37 SQL>
09:12:37 SQL> select group#,member,type from v$logfile where type<>'STANDBY';
GROUP# MEMBER TYPE
11 +DATA/prd/onlinelog/group_11.2478.867924049 ONLINE
12 +DATA/prd/onlinelog/group_12.2477.867924053 ONLINE
13 +DATA/prd/onlinelog/group_13.2476.867924055 ONLINE
14 +DATA/prd/onlinelog/group_14.2410.867924057 ONLINE
21 +DATA/prd/onlinelog/group_21.2416.867924605 ONLINE
22 +DATA/prd/onlinelog/group_22.2415.867924607 ONLINE
23 +DATA/prd/onlinelog/group_23.2413.867924611 ONLINE
24 +DATA/prd/onlinelog/group_24.2412.867924613 ONLINE
8 rows selected.
09:13:14 SQL>
1.在备库添加新的10组 standby log
alter database recover managed standby database cancel;
--#先取消APPLIED
alter database add standby logfile group 31 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_31_redo.log') size 500M;
alter database add standby logfile group 32 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_32_redo.log') size 500M;
alter database add standby logfile group 33 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_33_redo.log') size 500M;
alter database add standby logfile group 34 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_34_redo.log') size 500M;
alter database add standby logfile group 35 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_35_redo.log') size 500M;
alter database add standby logfile group 36 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_36_redo.log') size 500M;
alter database add standby logfile group 37 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_37_redo.log') size 500M;
alter database add standby logfile group 38 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_38_redo.log') size 500M;
alter database add standby logfile group 39 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_39_redo.log') size 500M;
alter database add standby logfile group 40 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_40_redo.log') size 500M;
09:28:16 SQL> select group#,member,type from v$logfile where type='STANDBY';
GROUP# MEMBER TYPE
15 /oracle/PRD/sapdata1/STDBY/onlinelog/group_15.2333.869677413 STANDBY
16 /oracle/PRD/sapdata1/STDBY/onlinelog/group_16.2332.869677415 STANDBY
17 /oracle/PRD/sapdata1/STDBY/onlinelog/group_17.2331.869677417 STANDBY
18 /oracle/PRD/sapdata1/STDBY/onlinelog/group_18.2330.869677419 STANDBY
19 /oracle/PRD/sapdata1/STDBY/onlinelog/group_19.2329.869677421 STANDBY
25 /oracle/PRD/sapdata1/STDBY/onlinelog/group_25.1862.869677441 STANDBY
26 /oracle/PRD/sapdata1/STDBY/onlinelog/group_26.1861.869677445 STANDBY
27 /oracle/PRD/sapdata1/STDBY/onlinelog/group_27.1860.869677447 STANDBY
28 /oracle/PRD/sapdata1/STDBY/onlinelog/group_28.1859.869677449 STANDBY
29 /oracle/PRD/sapdata1/STDBY/onlinelog/group_29.1857.869677451 STANDBY
31 /oracle/PRD/sapdata1/STDBY/onlinelog/group_31_redo.log STANDBY
GROUP# MEMBER TYPE
32 /oracle/PRD/sapdata1/STDBY/onlinelog/group_32_redo.log STANDBY
33 /oracle/PRD/sapdata1/STDBY/onlinelog/group_33_redo.log STANDBY
34 /oracle/PRD/sapdata1/STDBY/onlinelog/group_34_redo.log STANDBY
35 /oracle/PRD/sapdata1/STDBY/onlinelog/group_35_redo.log STANDBY
36 /oracle/PRD/sapdata1/STDBY/onlinelog/group_36_redo.log STANDBY
37 /oracle/PRD/sapdata1/STDBY/onlinelog/group_37_redo.log STANDBY
38 /oracle/PRD/sapdata1/STDBY/onlinelog/group_38_redo.log STANDBY
39 /oracle/PRD/sapdata1/STDBY/onlinelog/group_39_redo.log STANDBY
40 /oracle/PRD/sapdata1/STDBY/onlinelog/group_40_redo.log STANDBY
20 rows selected.
09:28:17 SQL>
2.在备库删除备库standby log 15-19,25-29
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
如果出现以下错误，在主库上切换一下日志
alter system switch logfile; --(切换)
--ALTER SYSTEM ARCHIVE LOG CURRENT;(无效)
09:29:39 SQL> alter database drop logfile group 16;
alter database drop logfile group 16
*
ERROR at line 1:
ORA-00261: log 16 of thread 1 is being archived or modified
ORA-00312: online log 16 thread 1:
'/oracle/PRD/sapdata1/STDBY/onlinelog/group_16.2332.869677415'
09:30:50 SQL>
09:44:58 SQL> select group#,member,type from v$logfile where type='STANDBY';
GROUP# MEMBER TYPE
31 /oracle/PRD/sapdata1/STDBY/onlinelog/group_31_redo.log STANDBY
32 /oracle/PRD/sapdata1/STDBY/onlinelog/group_32_redo.log STANDBY
33 /oracle/PRD/sapdata1/STDBY/onlinelog/group_33_redo.log STANDBY
34 /oracle/PRD/sapdata1/STDBY/onlinelog/group_34_redo.log STANDBY
35 /oracle/PRD/sapdata1/STDBY/onlinelog/group_35_redo.log STANDBY
36 /oracle/PRD/sapdata1/STDBY/onlinelog/group_36_redo.log STANDBY
37 /oracle/PRD/sapdata1/STDBY/onlinelog/group_37_redo.log STANDBY
38 /oracle/PRD/sapdata1/STDBY/onlinelog/group_38_redo.log STANDBY
39 /oracle/PRD/sapdata1/STDBY/onlinelog/group_39_redo.log STANDBY
40 /oracle/PRD/sapdata1/STDBY/onlinelog/group_40_redo.log STANDBY
10 rows selected.
09:44:59 SQL>
3.主库standbylog添加
alter database add standby logfile group 31 ('+DATA/prd/onlinelog/group_31_redo.log') size 500M;
alter database add standby logfile group 32 ('+DATA/prd/onlinelog/group_32_redo.log') size 500M;
alter database add standby logfile group 33 ('+DATA/prd/onlinelog/group_33_redo.log') size 500M;
alter database add standby logfile group 34 ('+DATA/prd/onlinelog/group_34_redo.log') size 500M;
alter database add standby logfile group 35 ('+DATA/prd/onlinelog/group_35_redo.log') size 500M;
alter database add standby logfile group 36 ('+DATA/prd/onlinelog/group_36_redo.log') size 500M;
alter database add standby logfile group 37 ('+DATA/prd/onlinelog/group_37_redo.log') size 500M;
alter database add standby logfile group 38 ('+DATA/prd/onlinelog/group_38_redo.log') size 500M;
alter database add standby logfile group 39 ('+DATA/prd/onlinelog/group_39_redo.log') size 500M;
alter database add standby logfile group 40 ('+DATA/prd/onlinelog/group_40_redo.log') size 500M;
09:52:24 SQL> select group#,member,type from v$logfile where type='STANDBY';
GROUP# MEMBER TYPE
15 +DATA/prd/onlinelog/group_15.2333.869677413 STANDBY
16 +DATA/prd/onlinelog/group_16.2332.869677415 STANDBY
17 +DATA/prd/onlinelog/group_17.2331.869677417 STANDBY
18 +DATA/prd/onlinelog/group_18.2330.869677419 STANDBY
19 +DATA/prd/onlinelog/group_19.2329.869677421 STANDBY
25 +DATA/prd/onlinelog/group_25.1862.869677441 STANDBY
26 +DATA/prd/onlinelog/group_26.1861.869677445 STANDBY
27 +DATA/prd/onlinelog/group_27.1860.869677447 STANDBY
28 +DATA/prd/onlinelog/group_28.1859.869677449 STANDBY
29 +DATA/prd/onlinelog/group_29.1857.869677451 STANDBY
31 +DATA/prd/onlinelog/group_31_redo.log STANDBY
32 +DATA/prd/onlinelog/group_32_redo.log STANDBY
33 +DATA/prd/onlinelog/group_33_redo.log STANDBY
34 +DATA/prd/onlinelog/group_34_redo.log STANDBY
35 +DATA/prd/onlinelog/group_35_redo.log STANDBY
36 +DATA/prd/onlinelog/group_36_redo.log STANDBY
37 +DATA/prd/onlinelog/group_37_redo.log STANDBY
38 +DATA/prd/onlinelog/group_38_redo.log STANDBY
39 +DATA/prd/onlinelog/group_39_redo.log STANDBY
40 +DATA/prd/onlinelog/group_40_redo.log STANDBY
20 rows selected.
09:52:30 SQL>
4.在主库上删除主库旧的standby log 15-19,25-29
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
09:53:55 SQL> alter database drop logfile group 15;
Database altered.
09:53:58 SQL> alter database drop logfile group 16;
Database altered.
09:54:08 SQL> alter database drop logfile group 17
09:54:11 2 ;
Database altered.
09:54:13 SQL> alter database drop logfile group 18;
Database altered.
09:54:16 SQL> alter database drop logfile group 19;
Database altered.
09:54:20 SQL> alter database drop logfile group 25;
Database altered.
09:54:26 SQL> alter database drop logfile group 26;
Database altered.
09:54:30 SQL> alter database drop logfile group 27;
Database altered.
09:54:34 SQL> alter database drop logfile group 28;
Database altered.
09:54:38 SQL> alter database drop logfile group 29;
Database altered.
09:54:41 SQL>
09:54:41 SQL> select group#,member,type from v$logfile where type='STANDBY';
GROUP# MEMBER TYPE
31 +DATA/prd/onlinelog/group_31_redo.log STANDBY
32 +DATA/prd/onlinelog/group_32_redo.log STANDBY
33 +DATA/prd/onlinelog/group_33_redo.log STANDBY
34 +DATA/prd/onlinelog/group_34_redo.log STANDBY
35 +DATA/prd/onlinelog/group_35_redo.log STANDBY
36 +DATA/prd/onlinelog/group_36_redo.log STANDBY
37 +DATA/prd/onlinelog/group_37_redo.log STANDBY
38 +DATA/prd/onlinelog/group_38_redo.log STANDBY
39 +DATA/prd/onlinelog/group_39_redo.log STANDBY
40 +DATA/prd/onlinelog/group_40_redo.log STANDBY
10 rows selected.
09:55:04 SQL>
09:55:05 SQL>
5．在主库添加新的ONLINE REDO LOG
ALTER DATABASE ADD LOGFILE THREAD 1 group 41 ('+DATA/prd/onlinelog/group_41_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 42 ('+DATA/prd/onlinelog/group_42_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 43 ('+DATA/prd/onlinelog/group_43_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 44 ('+DATA/prd/onlinelog/group_44_redo.log') SIZE 500M;
--ALTER DATABASE ADD LOGFILE THREAD 1 group 45 ('+DATA/prd/onlinelog/group_45_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 51 ('+DATA/prd/onlinelog/group_51_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 52 ('+DATA/prd/onlinelog/group_52_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 53 ('+DATA/prd/onlinelog/group_53_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 54 ('+DATA/prd/onlinelog/group_54_redo.log') SIZE 500M;
--ALTER DATABASE ADD LOGFILE THREAD 2 group 55 ('+DATA/prd/onlinelog/group_55_redo.log') SIZE 500M;
10:07:45 SQL> select group#,member,type from v$logfile where type<>'STANDBY';
GROUP# MEMBER TYPE
11 +DATA/prd/onlinelog/group_11.2478.867924049 ONLINE
12 +DATA/prd/onlinelog/group_12.2477.867924053 ONLINE
13 +DATA/prd/onlinelog/group_13.2476.867924055 ONLINE
14 +DATA/prd/onlinelog/group_14.2410.867924057 ONLINE
21 +DATA/prd/onlinelog/group_21.2416.867924605 ONLINE
22 +DATA/prd/onlinelog/group_22.2415.867924607 ONLINE
23 +DATA/prd/onlinelog/group_23.2413.867924611 ONLINE
24 +DATA/prd/onlinelog/group_24.2412.867924613 ONLINE
41 +DATA/prd/onlinelog/group_41_redo.log ONLINE
42 +DATA/prd/onlinelog/group_42_redo.log ONLINE
43 +DATA/prd/onlinelog/group_43_redo.log ONLINE
44 +DATA/prd/onlinelog/group_44_redo.log ONLINE
51 +DATA/prd/onlinelog/group_51_redo.log ONLINE
52 +DATA/prd/onlinelog/group_52_redo.log ONLINE
53 +DATA/prd/onlinelog/group_53_redo.log ONLINE
54 +DATA/prd/onlinelog/group_54_redo.log ONLINE
16 rows selected.
10:07:50 SQL>
6.在主库删除旧的ONLINE REDOLOG
删除之前切文档，首先查看是不是ACTIVE或者是INACTIVE
不是INACTIVE 多切几次归档
select * from v$log;
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
10:19:12 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
14 1 1783 4294967296 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
21 2 1638 4294967296 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
22 2 1639 4294967296 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
23 2 1640 4294967296 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
24 2 1637 4294967296 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
41 1 1787 524288000 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
42 1 1788 524288000 512 1 YES INACTIVE 1.6112E+11 11-APR-15 1.6112E+11 11-APR-15
43 1 1789 524288000 512 1 NO CURRENT 1.6112E+11 11-APR-15 2.8147E+14
44 1 0 524288000 512 1 YES UNUSED 0 0
51 2 1641 524288000 512 1 NO CURRENT 1.6112E+11 11-APR-15 2.8147E+14
52 2 0 524288000 512 1 YES UNUSED 0 0
53 2 0 524288000 512 1 YES UNUSED 0 0
54 2 0 524288000 512 1 YES UNUSED 0 0
13 rows selected.
10:19:19 SQL> alter database drop logfile group 14;
Database altered.
10:19:40 SQL> alter database drop logfile group 21;
Database altered.
10:19:45 SQL> alter database drop logfile group 22;
Database altered.
10:19:51 SQL> alter database drop logfile group 23;
Database altered.
10:19:59 SQL> alter database drop logfile group 24;
Database altered.
10:19:59 SQL>
7.在备库添加新的ONLINE REDO LOG
alter system set standby_file_management='MANUAL';
--必须修改为手工处理
10:22:32 SQL> show parameter standby_file_management;
NAME TYPE VALUE
standby_file_management string AUTO
10:23:35 SQL> alter system set standby_file_management='MANUAL';
System altered.
10:23:54 SQL> show parameter standby_file_management;
NAME TYPE VALUE
standby_file_management string MANUAL
10:23:59 SQL>
select group#,thread#,sequence#,members,status from v$log;
ALTER DATABASE ADD LOGFILE THREAD 1 group 41 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_41_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 42 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_42_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 43 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_43_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 44 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_44_redo.log') SIZE 500M;
--ALTER DATABASE ADD LOGFILE THREAD 1 group 45 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_45_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 51 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_51_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 52 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_52_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 53 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_53_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2 group 54 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_54_redo.log') SIZE 500M;
--ALTER DATABASE ADD LOGFILE THREAD 2 group 55 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_55_redo.log') SIZE 500M;
10:18:14 SQL> select group#,thread#,sequence#,members,status from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
11 1 1784 1 CLEARIN
G
12 1 1785 1 CLEARIN
G
13 1 1786 1 CLEARIN
G
14 1 1783 1 CLEARIN
G
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
21 2 1638 1 CLEARIN
G
22 2 1639 1 CLEARIN
G
23 2 1640 1 CLEARIN
G
24 2 1637 1 CLEARIN
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
G
8 rows selected.
10:19:47 SQL>
10:24:20 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 41 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_41_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 42 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_42_redo.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 43 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_43_redo.log') SIZE 500M;
Database altered.
10:24:24 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 44 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_44_redo.log') SIZE 500M;
Database altered.
10:24:27 SQL>
Database altered.
10:24:30 SQL>
Database altered.
10:24:32 SQL> 10:24:32 SQL> 10:24:32 SQL>
10:24:33 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 51 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_51_redo.log') SIZE 500M;
Database altered.
10:24:43 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 52 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_52_redo.log') SIZE 500M;
Database altered.
10:24:49 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 53 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_53_redo.log') SIZE 500M;
Database altered.
10:24:54 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 54 ('/oracle/PRD/sapdata1/STDBY/onlinelog/group_54_redo.log') SIZE 500M;
Database altered.
10:24:59 SQL>
10:25:01 SQL>
8.在删除备库旧的ONLINE REDO LOG
ALTER DATABASE CLEAR LOGFILE GROUP 11;
alter database drop logfile group 11;
ALTER DATABASE CLEAR LOGFILE GROUP 12;
alter database drop logfile group 12;
ALTER DATABASE CLEAR LOGFILE GROUP 13;
alter database drop logfile group 13;
ALTER DATABASE CLEAR LOGFILE GROUP 14;
alter database drop logfile group 14;
ALTER DATABASE CLEAR LOGFILE GROUP 21;
alter database drop logfile group 21;
ALTER DATABASE CLEAR LOGFILE GROUP 22;
alter database drop logfile group 22;
ALTER DATABASE CLEAR LOGFILE GROUP 23;
alter database drop logfile group 23;
ALTER DATABASE CLEAR LOGFILE GROUP 24;
alter database drop logfile group 24;
10:29:57 SQL> select group#,thread#,sequence#,members,status from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
13 1 1786 1 CLEARIN
G
12 1 1785 1 CLEARIN
G
11 1 0 1 UNUSED
44 1 1790 1 CLEARIN
G
43 1 0 1 UNUSED
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
42 1 0 1 UNUSED
41 1 1791 1 CURRENT
14 1 1783 1 CLEARIN
G
53 2 0 1 UNUSED
52 2 0 1 UNUSED
51 2 1641 1 CURRENT
54 2 0 1 UNUSED
23 2 1640 1 CLEARIN
G
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
22 2 1639 1 CLEARIN
G
21 2 1638 1 CLEARIN
G
24 2 1637 1 CLEARIN
G
16 rows selected.
alter system set standby_file_management='AUTO' scope=both;
alter database recover managed standby database disconnect from session;
