当前位置: 首页 > DG > 正文

11G ADG环境归档日志自动删除策略

本次测试来至于跟同事的一次聊天

 

环境介绍:关于ADG环境中,主备库归档日志怎么删除的问题。其实这在11G之前还是比较麻烦的,但是在11G,这一切都变得很简单了。

 

欢迎大家加入ORACLE超级群:17115662 免费解决各种ORACLE问题

 

1,数据库版本

www.htz.pw > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

2,创建测试数据与相关参数的配置

这里主要是创建了一张测试表,表上面创建了索引。

www.htz.pw > create table scott.htz as select * from dba_objects;

 

Table created.

 

www.htz.pw > create index scott.ind_htz_object_id on scott.htz(object_id);

 

Index created.

 

www.htz.pw > c/object_id/owner/g

  1* create index scott.ind_htz_owner on scott.htz(object_id)

www.htz.pw > c/object_id/owner

  1* create index scott.ind_htz_owner on scott.htz(owner)

www.htz.pw > /

 

Index created.

 

www.htz.pw > c/owner/object_name

  1* create index scott.ind_htz_object_name on scott.htz(owner)

www.htz.pw > c/owner/object_name

  1* create index scott.ind_htz_object_name on scott.htz(object_name)

www.htz.pw > /

 

Index created.

修改备库的删除策略

RMAN> configure archivelog deletion policy to applied on standby;

 

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters are successfully stored

这里没有出现警告

3,主库产生大量日志

这里写了一个for的循环

www.htz.pw > begin

  2  for i in 1..1000000 loop

  3  update scott.htz set owner=’HTZ’;

  4  commit;

  5  end loop;

  6  end;

  7  /

在备库可以见到如下的信息

www.htz.pw > @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED                 RECLAIMABLE               FILES

—————————— ——————– ——————– ——————– ———-

+ARCH_S1                       1024M                904M                 143M                         15

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                       1.17                         0          1

REDO LOG                          59.38                         0          8

ARCHIVED LOG                      32.32                     13.96          7

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                         0                         0          0

FOREIGN ARCHIVED LOG                  0                         0          0

 

 

Deleted Oracle managed file +ARCH_S1/asm11gs1/archivelog/2014_05_06/thread_1_seq_28.661.846888275

Deleted Oracle managed file +ARCH_S1/asm11gs1/archivelog/2014_05_06/thread_1_seq_27.660.846888279

Deleted Oracle managed file +ARCH_S1/asm11gs1/archivelog/2014_05_06/thread_1_seq_29.327.846888281

Deleted Oracle managed file +ARCH_S1/asm11gs1/archivelog/2014_05_06/thread_1_seq_30.671.846889485

RFS[2]: Opened log for thread 1 sequence 38 dbid 99090287 branch 841538336

Archived Log entry 146 added for thread 1 sequence 38 rlc 841538336 ID 0x5fdc3cf dest 2:

Tue May 06 23:10:40 2014

Media Recovery Log +ARCH_S1/asm11gs1/archivelog/2014_05_06/thread_1_seq_38.671.846889831

Tue May 06 23:10:44 2014

RFS[2]: Selected log 4 for thread 1 sequence 39 dbid 99090287 branch 841538336

Tue May 06 23:10:54 2014

Media Recovery Waiting for thread 1 sequence 39 (in transit)

Recovery of Online Redo Log: Thread 1 Group 4 Seq 39 Reading mem 0

  Mem# 0: +DATA_S1/asm11gs1/onlinelog/group_4.267.841392453

  Mem# 1: +ARCH_S1/asm11gs1/onlinelog/group_4.640.841392455

Tue May 06 23:11:08 2014

这里我们看到备库已经开始删除了,此时FRA的使用目录达到了80%,默认开始删除是从80%开始的

现在我们把db_recovery_file_dest_size增加。

www.htz.pw > alter system set db_recovery_file_dest_size=2500M;

 

System altered.

增加后,在alert中没有发现delete操作了。

www.htz.pw > @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED%                 USED% RECLAIMABLE               FILES

—————————— ——————– ——————– —— ——————– ———-

+ARCH_S1                       2500M                1966M                    79 423M                         38

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                        .48                         0          1

REDO LOG                          24.32                         0          8

ARCHIVED LOG                       53.8                     16.92         29

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                         0                         0          0

FOREIGN ARCHIVED LOG                  0                         0          0

 

发现有删除操作

 

Tue May 06 23:25:32 2014

Deleted Oracle managed file +ARCH_S1/asm11gs1/archivelog/2014_05_06/thread_1_seq_35.264.846889759

直到fra区使用达到80%,开始新的删除操作

通过上面的实验,我们可以说明,当RMAN中配置archivelog的删除策略为applied on standby时,备库FRA空间使用达到80%,就可以自动的删除归档日志。

 

 

4,主库自动删除归档测试

下面来看看主库自动删除归档测试

www.htz.pw > @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED                  USED% RECLAIMABLE               FILES

—————————— ——————– ——————– —— ——————–+ARCH_P                        3072M                2984M                    97 0M                          173

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                        .39                         0          1

REDO LOG                          11.62                         0          7

ARCHIVED LOG                      76.79                         0        160

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                       8.3                         0          5

FOREIGN ARCHIVED LOG                  0                         0          0

主备的已经使用达到了98%,但是并没有见删除日志操作

 

 

Tue May 06 23:26:44 2014

Archived Log entry 225 added for thread 1 sequence 66 ID 0x5fdc3cf dest 1:

Tue May 06 23:27:00 2014

Thread 1 advanced to log sequence 68 (LGWR switch)

  Current log# 2 seq# 68 mem# 0: +DATA_P/asm11gp/onlinelog/group_2.289.841532841

  Current log# 2 seq# 68 mem# 1: +ARCH_P/asm11gp/onlinelog/group_2.620.841532843

Tue May 06 23:27:06 2014

LNS: Standby redo logfile selected for thread 1 sequence 68 for destination LOG_ARCHIVE_DEST_2

Tue May 06 23:27:12 2014

Archived Log entry 227 added for thread 1 sequence 67 ID 0x5fdc3cf dest 1:

Tue May 06 23:27:32 2014

Thread 1 advanced to log sequence 69 (LGWR switch)

  Current log# 3 seq# 69 mem# 0: +DATA_P/asm11gp/onlinelog/group_3.290.841532845

  Current log# 3 seq# 69 mem# 1: +ARCH_P/asm11gp/onlinelog/group_3.619.841532847

Tue May 06 23:27:39 2014

LNS: Standby redo logfile selected for thread 1 sequence 69 for destination LOG_ARCHIVE_DEST_2

Tue May 06 23:27:48 2014

Archived Log entry 229 added for thread 1 sequence 68 ID 0x5fdc3cf dest 1:

Tue May 06 23:28:04 2014

Thread 1 advanced to log sequence 70 (LGWR switch)

  Current log# 1 seq# 70 mem# 0: +DATA_P/asm11gp/onlinelog/group_1.288.841532839

  Current log# 1 seq# 70 mem# 1: +ARCH_P/asm11gp/onlinelog/group_1.621.841532841

Tue May 06 23:28:08 2014

LNS: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2

Tue May 06 23:28:20 2014

Archived Log entry 231 added for thread 1 sequence 69 ID 0x5fdc3cf dest 1:

这里看到主库FRA区使用达到了97%,但是仍不见自动归档删除操作

下面修改rman中的归档删除策略

www.htz.pw > !rman target /

 

Recovery Manager: Release 11.2.0.3.0 – Production on Tue May 6 23:34:30 2014

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ASM11G (DBID=99090287)

 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

 

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy

这里我们将db_recovery_file_dest_size增加后,再观察一下

www.htz.pw > alter system set db_recovery_file_dest_size=4G;

 

System altered.

 

www.htz.pw > @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED                  USED% RECLAIMABLE               FILES

—————————— ——————– ——————– —— ——————– ———-

+ARCH_P                        4096M                3407M                    83 0M                          182

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                        .29                         0          1

REDO LOG                           8.72                         0          7

ARCHIVED LOG                      67.92                         0        169

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                      6.23                         0          5

FOREIGN ARCHIVED LOG                  0                         0          0

 

看到日志使用已经达到83%,但是还是不见自动删除的操作,看到rman归档的自动删除归档日志,没有生效

Tue May 06 23:44:15 2014

LNS: Standby redo logfile selected for thread 1 sequence 77 for destination LOG_ARCHIVE_DEST_2

Tue May 06 23:44:32 2014

Archived Log entry 245 added for thread 1 sequence 76 ID 0x5fdc3cf dest 1:

Tue May 06 23:44:42 2014

Thread 1 advanced to log sequence 78 (LGWR switch)

  Current log# 3 seq# 78 mem# 0: +DATA_P/asm11gp/onlinelog/group_3.290.841532845

  Current log# 3 seq# 78 mem# 1: +ARCH_P/asm11gp/onlinelog/group_3.619.841532847

Tue May 06 23:44:48 2014

LNS: Standby redo logfile selected for thread 1 sequence 78 for destination LOG_ARCHIVE_DEST_2

Tue May 06 23:45:02 2014

Archived Log entry 247 added for thread 1 sequence 77 ID 0x5fdc3cf dest 1:

Tue May 06 23:45:11 2014

Thread 1 advanced to log sequence 79 (LGWR switch)

  Current log# 1 seq# 79 mem# 0: +DATA_P/asm11gp/onlinelog/group_1.288.841532839

  Current log# 1 seq# 79 mem# 1: +ARCH_P/asm11gp/onlinelog/group_1.621.841532841

Tue May 06 23:45:19 2014

LNS: Standby redo logfile selected for thread 1 sequence 79 for destination LOG_ARCHIVE_DEST_2

Tue May 06 23:45:29 2014

Archived Log entry 249 added for thread 1 sequence 78 ID 0x5fdc3cf dest 1:

重启数据库

www.htz.pw > alter system set event=’19823 trace name context forever,level 50′ scope=spfile;

 

System altered.

 

www.htz.pw > shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

www.htz.pw > startup

ORACLE instance started.

 

Total System Global Area  283930624 bytes

Fixed Size                  2227664 bytes

Variable Size             167772720 bytes

Database Buffers          109051904 bytes

Redo Buffers                4878336 bytes

Database mounted.

Database opened.

 

见次跑脚本,仍不见自动删除

再次修改参数,重启数据库

www.htz.pw > alter system set “_log_deletion_policy“=ALL scope=spfile;

 

System altered.

 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

 

old RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy

 

 

www.htz.pw > startup force;

ORACLE instance started.

 

Total System Global Area  283930624 bytes

Fixed Size                  2227664 bytes

Variable Size             167772720 bytes

Database Buffers          109051904 bytes

Redo Buffers                4878336 bytes

Database mounted.

sDatabase opened.

 

Starting background process CJQ0

Tue May 06 23:50:34 2014

CJQ0 started with pid=44, OS id=5037

Tue May 06 23:50:38 2014

db_recovery_file_dest_size of 4096 MB is 19.73% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

 

这里突然发现db_recovery_file_dest_size使用只有19.73了,不知道什么时候删除了

alert.log中无任何delete的日志

 

www.htz.pw > @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED                  USED% RECLAIMABLE               FILES

—————————— ——————– ——————– —— ——————– ———-

+ARCH_P                        4096M                3714M                    91 2906M                       191

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                        .29                         0          1

REDO LOG                           8.72                         0          7

ARCHIVED LOG                      72.92                     70.95        176

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                      8.72                         0          7

FOREIGN ARCHIVED LOG                  0                         0          0

 

假的

www.htz.pw > alter system switch logfile;

 

System altered.

 

www.htz.pw > /

 

System altered.

 

这里可以看到已经开始删除了

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_06/thread_1_seq_6.632.841528467

Archived Log entry 263 added for thread 1 sequence 85 ID 0x5fdc3cf dest 1:

Thread 1 cannot allocate new log, sequence 87

Checkpoint not complete

  Current log# 2 seq# 86 mem# 0: +DATA_P/asm11gp/onlinelog/group_2.289.841532841

  Current log# 2 seq# 86 mem# 1: +ARCH_P/asm11gp/onlinelog/group_2.620.841532843

Thread 1 advanced to log sequence 87 (LGWR switch)

  Current log# 3 seq# 87 mem# 0: +DATA_P/asm11gp/onlinelog/group_3.290.841532845

  Current log# 3 seq# 87 mem# 1: +ARCH_P/asm11gp/onlinelog/group_3.619.841532847

Tue May 06 23:53:22 2014

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_06/thread_1_seq_7.631.841530255

Archived Log entry 264 added for thread 1 sequence 86 ID 0x5fdc3cf dest 1:

这里我们还能见到大批量的日志删除操作

Thread 1 advanced to log sequence 92 (LGWR switch)

  Current log# 2 seq# 92 mem# 0: +DATA_P/asm11gp/onlinelog/group_2.289.841532841

  Current log# 2 seq# 92 mem# 1: +ARCH_P/asm11gp/onlinelog/group_2.620.841532843

Tue May 06 23:54:59 2014

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_3.644.841390223

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_8.293.841369727

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_9.294.841369731

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_10.298.841370737

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_11.299.841370739

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_12.300.841370743

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_15.303.841370951

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_14.304.841370951

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_13.305.841370951

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_16.306.841370951

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_17.310.841370975

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_19.311.841370975

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_18.312.841370975

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_21.313.841370983

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_20.314.841370983

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_03_05/thread_1_seq_22.315.841370983

其实ORACLE自动删除,也可以大批量的删除的,但是一般情况下,都是在新生成归档前,就删除一个归档日志,不知道什么条件下触发大批量的归档删除操作,但是FRA空间使用一直在80%左右,并没有达到我们之前的配置的50%

 

我们可以通过下面的脚本为不停的切日志

这里空间一直在80%左右,没有降到我们想到的50%

begin

for i in 1..1000000 loop

 EXECUTE IMMEDIATE ‘alter system switch logfile’;

commit;

end loop;

end;

/

 

终于降到50%左右了

这里切换了100次左右

 

www.htz.pw > @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED                  USED% RECLAIMABLE               FILES

—————————— ——————– ——————– —— ——————– ———-

+ARCH_P                        5120M                2496M                    49 1056M                       193

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                        .23                         0          1

REDO LOG                           6.97                         0          7

ARCHIVED LOG                      41.52                     20.63        185

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                         0                         0          0

FOREIGN ARCHIVED LOG                  0                         0          0

 

7 rows selected.

 

下面我们把_log_deletion_policy参数恢复默认值

www.htz.pw > alter system reset “_log_deletion_policy” scope=spfile;

 

System altered.

 

www.htz.pw > startup force;

ORACLE instance started.

 

Total System Global Area  283930624 bytes

Fixed Size                  2227664 bytes

Variable Size             167772720 bytes

Database Buffers          109051904 bytes

Redo Buffers                4878336 bytes

Database mounted.

Database opened.

www.htz.pw > @parameter_hide.sql

 

+————————————————————————+

| display hide parameter value                                       |

+————————————————————————+

 

Enter Search Parameter (i.e. max|all) : _log_deletion_policy

 

PARAMETER                                SESSION_VALUE        INSTANCE_VALUE       DESCRIPTION

—————————————- ——————– ——————– —————–_log_deletion_policy                     mandatory            mandatory            archivelog deletion policy for mandatory/all destination

运行测试SQL

 

www.htz.pw >  @recovery_file_dest.sql

“RECOVERY FILE DEST AND SIZE”

 

NAME                           QUOTA                USED                  USED% RECLAIMABLE               FILES

—————————— ——————– ——————– —— ——————– ———-

+ARCH_P                        5120M                2523M                    49 480M                        314

 

 

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE     number

——————– —————— ————————- ———-

CONTROL FILE                        .23                         0          1

REDO LOG                           6.97                         0          7

ARCHIVED LOG                      41.11                      8.44        305

BACKUP PIECE                          0                         0          0

IMAGE COPY                            0                         0          0

FLASHBACK LOG                         0                         0          0

FOREIGN ARCHIVED LOG                  0                         0          0

 

7 rows selected.

 

Wed May 07 01:16:57 2014

Deleted Oracle managed file +ARCH_P/asm11gp/archivelog/2014_05_07/thread_1_seq_211.541.846895731

Wed May 07 01:17:03 2014

LNS: Standby redo logfile selected for thread 1 sequence 519 for destination LOG_ARCHIVE_DEST_2

Wed May 07 01:17:07 2014

Archived Log entry 1128 added for thread 1 sequence 518 ID 0x5fdc3cf dest 1:

 

这里空间一直在50%左右,这里仍然可以看到在自动删除,

本文固定链接: http://www.htz.pw/2014/05/07/11g-adg%e7%8e%af%e5%a2%83%e5%bd%92%e6%a1%a3%e6%97%a5%e5%bf%97%e8%87%aa%e5%8a%a8%e5%88%a0%e9%99%a4%e7%ad%96%e7%95%a5.html | 认真就输

该日志由 huangtingzhong 于2014年05月07日发表在 DG 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 11G ADG环境归档日志自动删除策略 | 认真就输