本次测试来至于跟同事的一次聊天
环境介绍：关于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%左右，这里仍然可以看到在自动删除， |
11G ADG环境归档日志自动删除策略：等您坐沙发呢！