10G DATAGUARD增加REDO日誌組、刪除日誌組、刪除日誌成員實驗

還不算暈發表於2014-04-20

參考文件：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是沒影響的。

刪除日誌組成員
2014-04-24
刪除日誌檔案組與日誌檔案成員
2012-03-12
刪除日誌檔案組或成員
2016-10-19
【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
2011-10-20
【rac】實驗三：增加日誌組成員
2016-11-09
【REDO】刪除聯機重做日誌檔案組成員的注意事項
2011-07-13
Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）
2020-12-18
Oracle
【REDO】刪除聯機重做日誌檔案組的注意事項
2011-07-12
elasticsearch日誌刪除命令
2018-11-15
Elasticsearch
日誌刪除指令碼
2017-09-22
指令碼
oracle redo 日誌刪除後的恢復
2008-03-30
Oracle Redo
【rac】實驗四：增加日誌組
2016-11-09
oracle dataguard 自動刪除歸檔日誌
2011-07-28
Oracle
新增、刪除日誌檔案
2014-03-07
oracle刪除歸檔日誌
2013-12-24
Oracle
mysql bin 日誌的刪除
2014-10-02
MySql
歸檔日誌的刪除
2012-06-14
mysql binlog日誌刪除
2013-05-22
MySql
Oracle歸檔日誌刪除
2013-04-01
Oracle
如何在DATAGUARD中新增刪除聯機日誌
2019-07-21
對日誌檔案組和日誌檔案組成員的管理
2010-01-12
在ASM下增加一個日誌組成員
2015-01-26
ASM
為oracle新增重做日誌組及重做日誌成員
2014-09-09
Oracle
不能刪除聯機日誌組中的成員ORA-00362，解決方法。
2008-03-08
DATAGUARD_standby刪除歸檔日誌的指令碼
2017-05-31
指令碼
DataGuard備庫刪除已經應用的日誌
2014-08-23
DataGuard已應用歸檔日誌刪除指令碼
2012-09-27
指令碼
刪除data guard歸檔日誌
2013-11-19
刪除歸檔日誌檔案
2011-05-27
手動刪除歸檔日誌
2011-04-07
如何刪除SAP 系統日誌
2012-08-14
作用RMAN 刪除歸檔日誌
2009-10-09
redo日誌組資訊查詢
2016-12-23
線上重建redo log日誌組
2013-12-08
狀態為active的日誌組也是不允許刪除的
2010-07-02
刪除oracle 11g的警告日誌和監聽日誌
2015-02-16
Oracle
SQL Server 2005 日誌刪除和日誌檔案限制
2009-02-19
SQLServer
刪除redo所有日誌,資料庫無法啟動
2014-01-14
資料庫