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.




一切正常。