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

苏承栈

2026-10-28 • 👁 0人浏览过

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

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

在备库上开启日志应用：

在主库上进行日志切换：

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

备库上删除：

相关文章