10G DATAGUARD增加REDO日志组、删除日志组、删除日志成员实验

参考文档：http://www.eygle.com/digest/2010/10/oracle_dataguard_redo_changing.html
Metalink 473442.1 号文档
近期客户的DATAGUARD环境中主库出现有 checkpoint not complete警告，当前系统只有3组REDO大小各100M，需要增加REDO日志组。

1.在主库和备库设置standby_file_management 为manual

在主库的设置
SQL> show parameter standby_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
alter system set standby_file_management='manual';
SQL> show parameter standby_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      manual
在备库上的停止恢复应用并设置参数：
SQL>  alter database recover managed standby database cancel;
Database altered.
SQL> show parameter standby_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> alter system set standby_file_management='manual';
System altered.
SQL> show parameter standby_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
#############################################################################

2.检查当前REDO日志组号及大小、位置--主备都检查。

SQL> select a.group#,a.bytes/1024/1024 mb,b.member from v$log a,v$logfile b where a.group#=b.group#;
    GROUP#         MB MEMBER
---------- ---------- ----------------------------------------
         1        100 /u01/app/oracle/prod/disk1/redo01.log
         2        100 /u01/app/oracle/prod/disk1/redo02.log
         3        100 /u01/app/oracle/prod/disk1/redo03.log
SQL>  select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
7 rows selected.
################################################################

3.增加REDO组并检查-参考上步查出的REDO日志位置、大小、成员数。

在主库上增加REDO组及STANDBY REDOLOG（根据系统目前是否创建STANDBY REDOLOG决定）

SQL> alter database add logfile group 11 '/u01/app/oracle/prod/disk1/redo11.log' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog11.log' size 100m;
Database altered.
SQL>  select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
        11 /u01/app/oracle/prod/disk1/redo11.log
         8 /u01/app/oracle/prod/disk1/standbylog11.log
############################################################
在备库上增加REDO组及STANDBY REDOLOG（根据系统目前是否创建STANDBY REDOLOG决定）
SQL> alter database add logfile group 11 '/u01/app/oracle/prod/disk1/redo11.log' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog11.log' size 100m;
Database altered.
SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
        11 /u01/app/oracle/prod/disk1/redo11.log
         8 /u01/app/oracle/prod/disk1/standbylog11.log
9 rows selected.
SQL> select a.group#,a.bytes/1024/1024 mb,b.member from v$log a,v$logfile b where a.group#=b.group#;
    GROUP#         MB MEMBER
---------- ---------- --------------------------------------------------
         1        100 /u01/app/oracle/prod/disk1/redo01.log
        11        100 /u01/app/oracle/prod/disk1/redo11.log
         3        100 /u01/app/oracle/prod/disk1/redo03.log
         2        100 /u01/app/oracle/prod/disk1/redo02.log
######################################

4.检查主备库REDO日志组一致后，在主、备库恢复参数standby_file_management并查询

SQL> alter system set standby_file_management='auto';
System altered.
SQL> show parameter standby_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto

5.测试DATAGUARD是否可以正常应用--结合 主备库alert日志

在备库上开启日志应用：

SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            69
在主库上进行日志切换：
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            70
SQL>
在备库查询日志是否可以正常应用 ：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            70
可以在主库多切换几次日志，观察备库的日志使用状态：
select group#,status from v$log;
select group#,status from v$standby_log;

删除日志组就是以上步骤，把增加处换成删除

--注意主库CURRENT状态的日志组不能删，可以先手动切换，再删除；

--在备库上CLEARING_CURRENT状态的日志了不能删除，需要通过开启、关闭恢复应用来切换当前日志组，不要手动切换。

删除日志组成员实验如下：

主库上删除日志组成员
SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
         1 /u01/app/oracle/prod/disk2/log1b.log
         2 /u01/app/oracle/prod/disk2/log2b.log
         3 /u01/app/oracle/prod/disk2/log3b.log
10 rows selected.
SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log2b.log';
alter database drop logfile member '/u01/app/oracle/prod/disk2/log2b.log'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/app/oracle/prod/disk1/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/prod/disk2/log2b.log'
SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log3b.log';
Database altered.
SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log1b.log';
Database altered.
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
         2 /u01/app/oracle/prod/disk2/log2b.log
8 rows selected.

SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log2b.log';
Database altered.
SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
7 rows selected.
SQL> show parameter standby
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system switch logfile;
System altered.
##########################################

备库上删除：

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING_CURRENT
         3 CLEARING
         2 CLEARING
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select group#,member,status from v$logfile;
   GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
         1 /u01/app/oracle/prod/disk2/log1b.log
         2 /u01/app/oracle/prod/disk2/log2b.log
         3 /u01/app/oracle/prod/disk2/log3b.log
10 rows selected.
SQL>  select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CLEARING_CURRENT
         3 CLEARING
         2 CLEARING

SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log2b.log';
alter database drop logfile member '/u01/app/oracle/prod/disk2/log2b.log'
*
ERROR at line 1:
ORA-01275: Operation DROP LOG MEMBER is not allowed if standby file management
is automatic.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system set standby_file_management='manual';
System altered.

SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log2b.log';
Database altered.

SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log1b.log';
alter database drop logfile member '/u01/app/oracle/prod/disk2/log1b.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/u01/app/oracle/prod/disk1/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/prod/disk2/log1b.log'

SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log3b.log';
Database altered.

SQL> alter database recover managed standby database disconnect from session;   --通过开启恢复应用来切换当前日志组，不要手动切换。
Database altered.

SQL>  select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CLEARING_CURRENT
         3 CLEARING
         2 CLEARING

SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CLEARING
         3 CLEARING_CURRENT
         2 CLEARING

SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log1b.log';
alter database drop logfile member '/u01/app/oracle/prod/disk2/log1b.log'
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files  ---取消恢复应用
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database drop logfile member '/u01/app/oracle/prod/disk2/log1b.log';
Database altered.
SQL>  select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CLEARING
         3 CLEARING_CURRENT
         2 CLEARING
SQL>
SQL>  select group#,member,status from v$logfile;
    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/prod/disk1/redo01.log
         2 /u01/app/oracle/prod/disk1/redo02.log
         3 /u01/app/oracle/prod/disk1/redo03.log
         4 /u01/app/oracle/prod/disk1/standbylog1.log
         5 /u01/app/oracle/prod/disk1/standbylog2.log
         6 /u01/app/oracle/prod/disk1/standbylog3.log
         7 /u01/app/oracle/prod/disk1/standbylog4.log
7 rows selected.
########
删除完成可以在主库上切换下日志看是否应用到备库，一般来说删除一个REDO日志MEMBER是没影响的。