dataguard 下主备 online redo 与 standby redo log resize 重建

环境说明：本实验环境是一个节点的rac + 单节点 asm dg database 与 grid 版本是 11.2.0.4 。
提别提醒如果是多节点集群，操作时需要特别注意 thread 。

一. 主库操作

1.1 查看redo 信息
SQL> col member for a60
SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER
———- ——- ——————————————————–
3 ONLINE +DATA/devdb/onlinelog/group_3.263.936769395
3 ONLINE +FLASH/devdb/onlinelog/group_3.259.936769401
2 ONLINE +DATA/devdb/onlinelog/group_2.262.936769383
2 ONLINE +FLASH/devdb/onlinelog/group_2.258.936769389
1 ONLINE +DATA/devdb/onlinelog/group_1.261.936769373
1 ONLINE +FLASH/devdb/onlinelog/group_1.257.936769379
4 STANDBY +DATA/devdb/onlinelog/group_4.267.938494955
4 STANDBY +FLASH/devdb/onlinelog/group_4.278.938494963
5 STANDBY +DATA/devdb/onlinelog/group_5.268.938494969
5 STANDBY +FLASH/devdb/onlinelog/group_5.279.938494975
6 STANDBY +DATA/devdb/onlinelog/group_6.269.938494981
6 STANDBY +FLASH/devdb/onlinelog/group_6.280.938494985
7 STANDBY +DATA/devdb/onlinelog/group_7.270.938495005
7 STANDBY +FLASH/devdb/onlinelog/group_7.281.938495013

14 rows selected.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES INACTIVE 50
2 1 YES INACTIVE 50
3 1 NO CURRENT 50

1.2 修改standby redo

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER
———- ——- ————————————————
3 ONLINE +DATA/devdb/onlinelog/group_3.263.936769395
3 ONLINE +FLASH/devdb/onlinelog/group_3.259.936769401
2 ONLINE +DATA/devdb/onlinelog/group_2.262.936769383
2 ONLINE +FLASH/devdb/onlinelog/group_2.258.936769389
1 ONLINE +DATA/devdb/onlinelog/group_1.261.936769373
1 ONLINE +FLASH/devdb/onlinelog/group_1.257.936769379

6 rows selected.

添加standby redo

SQL> alter database add standby logfile thread 1 group 4 size 60m,group 5 size 60m,group 6 size 60m,group 7 size 60m;

Database altered.

SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER
———- ——- ————————————————–
3 ONLINE +DATA/devdb/onlinelog/group_3.263.936769395
3 ONLINE +FLASH/devdb/onlinelog/group_3.259.936769401
2 ONLINE +DATA/devdb/onlinelog/group_2.262.936769383
2 ONLINE +FLASH/devdb/onlinelog/group_2.258.936769389
1 ONLINE +DATA/devdb/onlinelog/group_1.261.936769373
1 ONLINE +FLASH/devdb/onlinelog/group_1.257.936769379
4 STANDBY +DATA/devdb/onlinelog/group_4.267.938747301
4 STANDBY +FLASH/devdb/onlinelog/group_4.278.938747305
5 STANDBY +DATA/devdb/onlinelog/group_5.268.938747309
5 STANDBY +FLASH/devdb/onlinelog/group_5.279.938747313
6 STANDBY +DATA/devdb/onlinelog/group_6.269.938747317
6 STANDBY +FLASH/devdb/onlinelog/group_6.280.938747321
7 STANDBY +DATA/devdb/onlinelog/group_7.270.938747325
7 STANDBY +FLASH/devdb/onlinelog/group_7.281.938747327

14 rows selected.

1.3 修改Online redo

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES INACTIVE 50
2 1 YES INACTIVE 50
3 1 NO CURRENT 50

先处理inactive，它表示已经完成规定的，可以删除。

SQL> alter database drop logfile group 1;

Database altered.

注意：至少要2个redo组

SQL> alter database add logfile thread 1 group 1 size 60m;

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES UNUSED 60
2 1 YES INACTIVE 50
3 1 NO CURRENT 50

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile thread 1 group 2 size 60m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 NO CURRENT 60
2 1 YES UNUSED 60
3 1 YES ACTIVE 50

说明：ACTIVE 表示正在归档

等一会后

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 NO CURRENT 60
2 1 YES UNUSED 60
3 1 YES INACTIVE 50

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile thread 1 group 3 size 60m;

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 NO CURRENT 60
2 1 YES UNUSED 60
3 1 YES UNUSED 60
OK,至此，主库完成。

二. 备库操作

2.1 查看信息
SQL> col member for a60
SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER
———- ——- ————————————————–
3 ONLINE +DATA/phydb/onlinelog/group_3.268.938177101
3 ONLINE +FLASH/phydb/onlinelog/group_3.262.938177103
2 ONLINE +DATA/phydb/onlinelog/group_2.267.938177097
2 ONLINE +FLASH/phydb/onlinelog/group_2.261.938177099
1 ONLINE +DATA/phydb/onlinelog/group_1.266.938177093
1 ONLINE +FLASH/phydb/onlinelog/group_1.260.938177095
4 STANDBY +DATA/phydb/onlinelog/group_4.262.938176937
4 STANDBY +FLASH/phydb/onlinelog/group_4.256.938176939
5 STANDBY +DATA/phydb/onlinelog/group_5.263.938176941
5 STANDBY +FLASH/phydb/onlinelog/group_5.257.938176943
6 STANDBY +DATA/phydb/onlinelog/group_6.264.938176945
6 STANDBY +FLASH/phydb/onlinelog/group_6.258.938176945
7 STANDBY +DATA/phydb/onlinelog/group_7.265.938176947
7 STANDBY +FLASH/phydb/onlinelog/group_7.259.938176949

14 rows selected.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES CURRENT 50
2 1 YES CLEARING 50
3 1 YES CLEARING 50

2.2 处理standby redo

对于standby 上redo的处理之前，我们要先停掉redo 的apply：否则报错 ORA-01156

SQL> alter database recover managed standby database cancel;

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database add standby logfile thread 1 group 4 size 60m,group 5 size 60m,group 6 size 60m,group 7 size 60m;

Database altered.

SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER
———- ——- ————————————————-
3 ONLINE +DATA/phydb/onlinelog/group_3.268.938177101
3 ONLINE +FLASH/phydb/onlinelog/group_3.262.938177103
2 ONLINE +DATA/phydb/onlinelog/group_2.267.938177097
2 ONLINE +FLASH/phydb/onlinelog/group_2.261.938177099
1 ONLINE +DATA/phydb/onlinelog/group_1.266.938177093
1 ONLINE +FLASH/phydb/onlinelog/group_1.260.938177095
4 STANDBY +DATA/phydb/onlinelog/group_4.265.938720451
4 STANDBY +FLASH/phydb/onlinelog/group_4.259.938720453
5 STANDBY +DATA/phydb/onlinelog/group_5.264.938720457
5 STANDBY +FLASH/phydb/onlinelog/group_5.258.938720463
6 STANDBY +DATA/phydb/onlinelog/group_6.263.938720465
6 STANDBY +FLASH/phydb/onlinelog/group_6.257.938720469
7 STANDBY +DATA/phydb/onlinelog/group_7.262.938720471
7 STANDBY +FLASH/phydb/onlinelog/group_7.256.938720475

14 rows selected.

2.3 处理online redo

先将standby_file_management设为手动：

SQL> alter system set standby_file_management=manual;
System altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile thread 1 group 2 size 60m;

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES CURRENT 50
2 1 YES UNUSED 60
3 1 YES CLEARING 50

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile thread 1 group 3 size 60m;

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES CURRENT 50
2 1 YES UNUSED 60
3 1 YES UNUSED 60

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’;

System altered.

— 到主库手动切换几次redo

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

–查看备库的redo：

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES CLEARING 50
2 1 YES CLEARING 60
3 1 YES CURRENT 60
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’MANUAL’;

System altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES CLEARING 50
2 1 YES CLEARING 60
3 1 YES CURRENT 60
SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile thread 1 group 1 size 60m;

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP# THREAD# ARC STATUS BYTES/1024/1024
———- ———- — —————- —————
1 1 YES UNUSED 60
2 1 YES CLEARING 60
3 1 YES CURRENT 60

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

主库测试：

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
77

备库查看是否同步：

SQL> select sequence#,applied from v$archived_log;
。。。。。
76 YES
77 IN-MEMORY

69 rows selected.

一切正常。

个人收藏笔记记录

开通VIP