重庆思庄Oracle、Redhat认证学习论坛

标题: 备库未应用的归档日志在主库端的delete测试 [打印本页]

作者: denglj 时间: 2022-9-29 10:13
标题: 备库未应用的归档日志在主库端的delete测试
本帖最后由 denglj 于 2022-9-29 10:15 编辑

文档课题：备库未应用的归档日志在主库端的delete测试.

主库：CentOS7.9 64位+ oracle 11.2.0.4 64位

备库：CentOS7.9 64位+ oracle 11.2.0.4 64位

1、场景模拟

1.1、主库数据

主库最近归档日志.

>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 13','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

21 2022-09-26 11:57:26 0/u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf

20 2022-09-26 11:57:22 0/u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf

19 2022-08-28 23:27:25 0/u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf

18 2022-08-28 23:27:22 0 /u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf

17 2022-08-28 23:07:19 0/u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf

16 2022-08-28 23:06:07 0/u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf

15 2022-08-28 23:05:52 0/u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf

14 2022-08-28 23:00:19 0/u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf

8 rowsselected.

1.2、停止应用

备库停止实时应用.

>select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLYWITH APPLY

>alter database recover managed standby database cancel;

1.3、产生归档

主库建表insert数据，产生新归档日志.

>create table t1 as select * from all_objects;

>insert into t1 select * from t1;

84395rows created.

……(省略若干insert)

>select count(*) from t1;

COUNT(*)

----------

2700640

>commit;

1.4、归档确认

1.4.1、主库查看

>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

30 2022-09-26 13:14:39 39/u01/app/oracle/fast_recovery_area/archivelog/1_30_1101942938.dbf

29 2022-09-26 13:14:35 39 /u01/app/oracle/fast_recovery_area/archivelog/1_29_1101942938.dbf

28 2022-09-26 13:14:32 39/u01/app/oracle/fast_recovery_area/archivelog/1_28_1101942938.dbf

27 2022-09-26 13:12:41 39/u01/app/oracle/fast_recovery_area/archivelog/1_27_1101942938.dbf

26 2022-09-26 13:12:40 33/u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbf

25 2022-09-26 13:09:32 39/u01/app/oracle/fast_recovery_area/archivelog/1_25_1101942938.dbf

24 2022-09-26 13:07:10 19/u01/app/oracle/fast_recovery_area/archivelog/1_24_1101942938.dbf

23 2022-09-26 13:07:08 39/u01/app/oracle/fast_recovery_area/archivelog/1_23_1101942938.dbf

22 2022-09-26 11:57:44 31/u01/app/oracle/fast_recovery_area/archivelog/1_22_1101942938.dbf

21 2022-09-26 11:57:26 0/u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf

20 2022-09-26 11:57:22 0/u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

19 2022-08-28 23:27:25 0 /u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf

18 2022-08-28 23:27:22 0/u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf

17 2022-08-28 23:07:19 0/u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf

16 2022-08-28 23:06:07 0/u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf

15 2022-08-28 23:05:52 0/u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf

14 2022-08-28 23:00:19 0 /u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf

17 rowsselected.

说明：sequence#从22至30均是新产生的归档日志.

1.4.2、备库查看

>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

30 2022-09-26 13:14:39 39/u01/app/oracle/fast_recovery_area/archivelog1_30_1101942938.dbf

29 2022-09-26 13:14:35 39 /u01/app/oracle/fast_recovery_area/archivelog1_29_1101942938.dbf

28 2022-09-26 13:14:32 39/u01/app/oracle/fast_recovery_area/archivelog1_28_1101942938.dbf

27 2022-09-26 13:12:41 39/u01/app/oracle/fast_recovery_area/archivelog1_27_1101942938.dbf

26 2022-09-26 13:12:40 33/u01/app/oracle/fast_recovery_area/archivelog1_26_1101942938.dbf

25 2022-09-26 13:09:32 39 /u01/app/oracle/fast_recovery_area/archivelog1_25_1101942938.dbf

24 2022-09-26 13:07:10 19/u01/app/oracle/fast_recovery_area/archivelog1_24_1101942938.dbf

23 2022-09-26 13:07:08 39/u01/app/oracle/fast_recovery_area/archivelog1_23_1101942938.dbf

22 2022-09-26 11:57:44 31/u01/app/oracle/fast_recovery_area/archivelog1_22_1101942938.dbf

21 2022-09-26 11:57:26 0/u01/app/oracle/fast_recovery_area/archivelog1_21_1101942938.dbf

19 2022-08-28 23:27:25 0/u01/app/oracle/fast_recovery_area/archivelog1_19_1101942938.dbf

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

18 2022-08-28 23:27:22 0/u01/app/oracle/fast_recovery_area/archivelog1_18_1101942938.dbf

17 2022-08-28 23:07:19 0/u01/app/oracle/fast_recovery_area/archivelog1_17_1101942938.dbf

16 2022-08-28 23:06:07 0 /u01/app/oracle/fast_recovery_area/archivelog1_16_1101942938.dbf

14 rowsselected.

1.5、应用确认

备库查看最新归档应用情况.

>select thread#,sequence#,to_char(first_time,'yyyy-mm-ddhh24:mi:ss'),to_char(next_time,'yyyy-mm-ddhh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied fromv$archived_log order by 1,2;

THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_APPLIED

-------------------- ------------------- ------------------- ----------------------------

1 14 2022-08-28 23:00:19 2022-08-2823:05:52 2022-08-28 23:07:19 YES

1 15 2022-08-28 23:05:52 2022-08-2823:06:07 2022-08-28 23:07:50 YES

1 16 2022-08-28 23:06:07 2022-08-2823:07:19 2022-08-28 23:07:19 YES

1 17 2022-08-28 23:07:19 2022-08-2823:27:22 2022-08-28 23:27:24 YES

1 18 2022-08-28 23:27:22 2022-08-2823:27:25 2022-08-28 23:27:25 YES

1 19 2022-08-28 23:27:25 2022-09-2611:57:22 2022-09-26 11:57:43 YES

1 20 2022-09-26 11:57:22 2022-09-2611:57:26 2022-09-26 11:57:43 YES

1 21 2022-09-26 11:57:26 2022-09-2611:57:44 2022-09-26 11:57:44 YES

1 22 2022-09-26 11:57:44 2022-09-2613:07:08 2022-09-26 13:07:08 NO

1 23 2022-09-26 13:07:08 2022-09-2613:07:10 2022-09-26 13:07:11 NO

1 24 2022-09-26 13:07:10 2022-09-2613:09:32 2022-09-26 13:09:32 NO

THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED

-------------------- ------------------- ------------------- ----------------------------

1 25 2022-09-26 13:09:32 2022-09-2613:12:40 2022-09-26 13:12:41 NO

1 26 2022-09-26 13:12:40 2022-09-2613:12:41 2022-09-26 13:12:42 NO

1 27 2022-09-26 13:12:41 2022-09-2613:14:32 2022-09-26 13:14:33 NO

1 28 2022-09-26 13:14:32 2022-09-2613:14:35 2022-09-26 13:14:36 NO

1 29 2022-09-26 13:14:35 2022-09-2613:14:39 2022-09-26 13:14:39 NO

1 30 2022-09-26 13:14:39 2022-09-2613:14:42 2022-09-26 13:14:42 NO

17 rowsselected.

说明：sequence#从22至30均未应用,场景成功模拟出来.

2、delete测试

主库测试delete操作.

2.1、deleteall input2.1.1、备份语句

deleteall input包含在备份语句中.

RMAN>backup as compressed backupset full database format'/home/oracle/rmanbak/2dbf_%d_%u_%p_%s' plus archivelog format'/home/oracle/rmanbak/3arc_%d_%u_%p_%s' delete all input;

Startingbackup at 26-SEP-22

currentlog archived

usingchannel ORA_DISK_1

channelORA_DISK_1: starting compressed archived log backup set

channelORA_DISK_1: specifying archived log(s) in backup set

inputarchived log thread=1 sequence=3 RECID=1 STAMP=1101981177

inputarchived log thread=1 sequence=4 RECID=2 STAMP=1102023824

inputarchived log thread=1 sequence=5 RECID=3 STAMP=1102024493

inputarchived log thread=1 sequence=6 RECID=4 STAMP=1102024496

inputarchived log thread=1 sequence=7 RECID=5 STAMP=1102024586

inputarchived log thread=1 sequence=8 RECID=6 STAMP=1102024590

inputarchived log thread=1 sequence=9 RECID=7 STAMP=1102024819

inputarchived log thread=1 sequence=10 RECID=8 STAMP=1102026657

inputarchived log thread=1 sequence=11 RECID=9 STAMP=1102026729

inputarchived log thread=1 sequence=12 RECID=12 STAMP=1102026774

inputarchived log thread=1 sequence=13 RECID=14 STAMP=1113951619

inputarchived log thread=1 sequence=14 RECID=16 STAMP=1113951952

inputarchived log thread=1 sequence=15 RECID=17 STAMP=1113951967

inputarchived log thread=1 sequence=16 RECID=19 STAMP=1113952039

input archivedlog thread=1 sequence=17 RECID=22 STAMP=1113953243

inputarchived log thread=1 sequence=18 RECID=24 STAMP=1113953245

inputarchived log thread=1 sequence=19 RECID=26 STAMP=1116417443

inputarchived log thread=1 sequence=20 RECID=27 STAMP=1116417446

inputarchived log thread=1 sequence=21 RECID=30 STAMP=1116417464

inputarchived log thread=1 sequence=22 RECID=33 STAMP=1116421628

inputarchived log thread=1 sequence=23 RECID=35 STAMP=1116421630

inputarchived log thread=1 sequence=24 RECID=37 STAMP=1116421772

inputarchived log thread=1 sequence=25 RECID=39 STAMP=1116421960

inputarchived log thread=1 sequence=26 RECID=41 STAMP=1116421961

inputarchived log thread=1 sequence=27 RECID=43 STAMP=1116422073

inputarchived log thread=1 sequence=28 RECID=45 STAMP=1116422076

inputarchived log thread=1 sequence=29 RECID=47 STAMP=1116422079

inputarchived log thread=1 sequence=30 RECID=49 STAMP=1116422082

inputarchived log thread=1 sequence=31 RECID=51 STAMP=1116423749

inputarchived log thread=1 sequence=32 RECID=52 STAMP=1116423749

inputarchived log thread=1 sequence=33 RECID=53 STAMP=1116423750

inputarchived log thread=1 sequence=34 RECID=54 STAMP=1116423753

inputarchived log thread=1 sequence=35 RECID=55 STAMP=1116423757

inputarchived log thread=1 sequence=36 RECID=56 STAMP=1116423760

inputarchived log thread=1 sequence=37 RECID=57 STAMP=1116423763

inputarchived log thread=1 sequence=38 RECID=58 STAMP=1116423766

inputarchived log thread=1 sequence=39 RECID=59 STAMP=1116424094

inputarchived log thread=1 sequence=40 RECID=69 STAMP=1116425177

channelORA_DISK_1: starting piece 1 at 26-SEP-22

channelORA_DISK_1: finished piece 1 at 26-SEP-22

piecehandle=/home/oracle/rmanbak/3arc_ORCL150_0b18miup_1_11 tag=TAG20220926T140617comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:25

channelORA_DISK_1: deleting archived log(s)

archivedlog filename=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_3_k5gzms5f_.arcRECID=1 STAMP=1101981177

archivedlog file name=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_4_k5j98jlm_.arcRECID=2 STAMP=1102023824

archivedlog filename=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_5_k5j9xfwx_.arcRECID=3 STAMP=1102024493

archivedlog filename=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_6_k5j9xjw9_.arcRECID=4 STAMP=1102024496

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_7_1101942938.dbfRECID=5 STAMP=1102024586

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_8_1101942938.dbfRECID=6 STAMP=1102024590

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_9_1101942938.dbfRECID=7 STAMP=1102024819

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_10_1101942938.dbfRECID=8 STAMP=1102026657

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_11_1101942938.dbfRECID=9 STAMP=1102026729

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_12_1101942938.dbfRECID=12 STAMP=1102026774

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_13_1101942938.dbfRECID=14 STAMP=1113951619

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbfRECID=16 STAMP=1113951952

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbfRECID=17 STAMP=1113951967

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbfRECID=19 STAMP=1113952039

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbfRECID=22 STAMP=1113953243

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbfRECID=24 STAMP=1113953245

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbfRECID=26 STAMP=1116417443

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbfRECID=27 STAMP=1116417446

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbfRECID=30 STAMP=1116417464

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_22_1101942938.dbfRECID=33 STAMP=1116421628

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_23_1101942938.dbfRECID=35 STAMP=1116421630

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_24_1101942938.dbfRECID=37 STAMP=1116421772

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_25_1101942938.dbfRECID=39 STAMP=1116421960

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbfRECID=41 STAMP=1116421961

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_27_1101942938.dbfRECID=43 STAMP=1116422073

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_28_1101942938.dbfRECID=45 STAMP=1116422076

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_29_1101942938.dbfRECID=47 STAMP=1116422079

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_30_1101942938.dbfRECID=49 STAMP=1116422082

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_31_1101942938.dbfRECID=51 STAMP=1116423749

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_32_1101942938.dbfRECID=52 STAMP=1116423749

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_33_1101942938.dbfRECID=53 STAMP=1116423750

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_34_1101942938.dbfRECID=54 STAMP=1116423753

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_35_1101942938.dbfRECID=55 STAMP=1116423757

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_36_1101942938.dbfRECID=56 STAMP=1116423760

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_37_1101942938.dbfRECID=57 STAMP=1116423763

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_38_1101942938.dbfRECID=58 STAMP=1116423766

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_39_1101942938.dbfRECID=59 STAMP=1116424094

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_40_1101942938.dbfRECID=69 STAMP=1116425177

Finishedbackup at 26-SEP-22

Startingbackup at 26-SEP-22

usingchannel ORA_DISK_1

channelORA_DISK_1: starting compressed full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

inputdatafile file number=00001 name=/u01/app/oracle/oradata/orcl150/system01.dbf

inputdatafile file number=00002 name=/u01/app/oracle/oradata/orcl150/sysaux01.dbf

inputdatafile file number=00003 name=/u01/app/oracle/oradata/orcl150/undotbs01.dbf

inputdatafile file number=00004 name=/u01/app/oracle/oradata/orcl150/users01.dbf

channelORA_DISK_1: starting piece 1 at 26-SEP-22

channelORA_DISK_1: finished piece 1 at 26-SEP-22

piecehandle=/home/oracle/rmanbak/2dbf_ORCL150_0c18mivj_1_12 tag=TAG20220926T140643comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:45

channelORA_DISK_1: starting compressed full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

includingcurrent control file in backup set

includingcurrent SPFILE in backup set

channelORA_DISK_1: starting piece 1 at 26-SEP-22

channelORA_DISK_1: finished piece 1 at 26-SEP-22

piecehandle=/home/oracle/rmanbak/2dbf_ORCL150_0d18mj10_1_13 tag=TAG20220926T140643comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finishedbackup at 26-SEP-22

Startingbackup at 26-SEP-22

currentlog archived

usingchannel ORA_DISK_1

channelORA_DISK_1: starting compressed archived log backup set

channelORA_DISK_1: specifying archived log(s) in backup set

inputarchived log thread=1 sequence=41 RECID=70 STAMP=1116425250

channelORA_DISK_1: starting piece 1 at 26-SEP-22

channelORA_DISK_1: finished piece 1 at 26-SEP-22

piecehandle=/home/oracle/rmanbak/3arc_ORCL150_0e18mj12_1_14 tag=TAG20220926T140730comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01

channelORA_DISK_1: deleting archived log(s)

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_41_1101942938.dbfRECID=70 STAMP=1116425250

Finishedbackup at 26-SEP-22

2.1.2、归档确认

含deleteall input的rman备份语句执行后，查看归档情况.

>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

40 2022-09-26 13:48:14 11

39 2022-09-26 13:42:46 14

38 2022-09-26 13:42:43 39

37 2022-09-26 13:42:40 39

36 2022-09-26 13:42:36 39

35 2022-09-26 13:42:33 39

34 2022-09-26 13:42:30 39

33 2022-09-26 13:42:29 39

32 2022-09-26 13:42:28 29

31 2022-09-26 13:14:42 39

30 2022-09-26 13:14:39 39

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

29 2022-09-26 13:14:35 39

28 2022-09-26 13:14:32 39

27 2022-09-26 13:12:41 39

26 2022-09-26 13:12:40 33

25 2022-09-26 13:09:32 39

24 2022-09-26 13:07:10 19

23 2022-09-26 13:07:08 39

22 2022-09-26 11:57:44 31

21 2022-09-26 11:57:26 0

20 2022-09-26 11:57:22 0

19 2022-08-28 23:27:25 0

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

18 2022-08-28 23:27:22 0

17 2022-08-28 23:07:19 0

27 rowsselected.

结论：备库未应用的归档日志被deleteall input语句全部删除.

2.2、delete archivelog all

现测试deletearchivelog all是否能删除备库未应用的归档日志.

2.2.1、新归档

主库再次产生新归档日志.

>insert into t1 select * from t1;

2700640rows created.

> commit;

>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 15','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

57 2022-09-26 14:17:01 39/u01/app/oracle/fast_recovery_area/archivelog/1_57_1101942938.dbf

56 2022-09-26 14:16:57 39/u01/app/oracle/fast_recovery_area/archivelog/1_56_1101942938.dbf

55 2022-09-26 14:16:54 39 /u01/app/oracle/fast_recovery_area/archivelog/1_55_1101942938.dbf

54 2022-09-26 14:16:51 39/u01/app/oracle/fast_recovery_area/archivelog/1_54_1101942938.dbf

53 2022-09-26 14:16:48 39/u01/app/oracle/fast_recovery_area/archivelog/1_53_1101942938.dbf

52 2022-09-26 14:16:45 39/u01/app/oracle/fast_recovery_area/archivelog/1_52_1101942938.dbf

51 2022-09-26 14:16:41 39/u01/app/oracle/fast_recovery_area/archivelog/1_51_1101942938.dbf

50 2022-09-26 14:16:38 39/u01/app/oracle/fast_recovery_area/archivelog/1_50_1101942938.dbf

49 2022-09-26 14:16:35 39/u01/app/oracle/fast_recovery_area/archivelog/1_49_1101942938.dbf

48 2022-09-26 14:16:32 39 /u01/app/oracle/fast_recovery_area/archivelog/1_48_1101942938.dbf

47 2022-09-26 14:16:29 39/u01/app/oracle/fast_recovery_area/archivelog/1_47_1101942938.dbf

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------- -----------------------------------------------------------------

46 2022-09-26 14:16:25 39/u01/app/oracle/fast_recovery_area/archivelog/1_46_1101942938.dbf

45 2022-09-26 14:16:22 39/u01/app/oracle/fast_recovery_area/archivelog/1_45_1101942938.dbf

44 2022-09-26 14:16:17 39/u01/app/oracle/fast_recovery_area/archivelog/1_44_1101942938.dbf

43 2022-09-26 14:16:15 36/u01/app/oracle/fast_recovery_area/archivelog/1_43_1101942938.dbf

42 2022-09-26 14:07:30 39/u01/app/oracle/fast_recovery_area/archivelog/1_42_1101942938.dbf

41 2022-09-26 14:06:17 1

40 2022-09-26 13:48:14 11

39 2022-09-26 13:42:46 14

38 2022-09-26 13:42:43 39

37 2022-09-26 13:42:40 39

36 2022-09-26 13:42:36 39

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

35 2022-09-26 13:42:33 39

34 2022-09-26 13:42:30 39

33 2022-09-26 13:42:29 39

32 2022-09-26 13:42:28 29

31 2022-09-26 13:14:42 39

30 2022-09-26 13:14:39 39

29 2022-09-26 13:14:35 39

28 2022-09-26 13:14:32 39

27 2022-09-26 13:12:41 39

26 2022-09-26 13:12:40 33

25 2022-09-26 13:09:32 39

SEQUENCE# TIME Size(M) NAME

----------------------------- ---------------------------------------------------------------------------

24 2022-09-26 13:07:10 19

23 2022-09-26 13:07:08 39

22 2022-09-26 11:57:44 31

21 2022-09-26 11:57:26 0

20 2022-09-26 11:57:22 0

19 2022-08-28 23:27:25 0

18 2022-08-28 23:27:22 0

17 2022-08-28 23:07:19 0

16 2022-08-28 23:06:07 0

15 2022-08-28 23:05:52 0

14 2022-08-28 23:00:19 0

44 rowsselected.

说明：sequence#42-57备库肯定未应用.

2.2.2、deletearchivelog

主库deletearchivelog all测试.

RMAN>delete archivelog all;

releasedchannel: ORA_DISK_1

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=197 device type=DISK

List ofArchived Log Copies for database with db_unique_name ORCL150

=====================================================================

Key Thrd Seq S Low Time

----------- ------- - ---------

73 1 42 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_42_1101942938.dbf

75 1 43 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_43_1101942938.dbf

77 1 44 A 26-SEP-22

Name: /u01/app/oracle/fast_recovery_area/archivelog/1_44_1101942938.dbf

79 1 45 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_45_1101942938.dbf

81 1 46 A 26-SEP-22

Name: /u01/app/oracle/fast_recovery_area/archivelog/1_46_1101942938.dbf

83 1 47 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_47_1101942938.dbf

85 1 48 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_48_1101942938.dbf

87 1 49 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_49_1101942938.dbf

89 1 50 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_50_1101942938.dbf

91 1 51 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_51_1101942938.dbf

93 1 52 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_52_1101942938.dbf

95 1 53 A 26-SEP-22

Name: /u01/app/oracle/fast_recovery_area/archivelog/1_53_1101942938.dbf

97 1 54 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_54_1101942938.dbf

99 1 55 A 26-SEP-22

Name: /u01/app/oracle/fast_recovery_area/archivelog/1_55_1101942938.dbf

101 1 56 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_56_1101942938.dbf

102 1 57 A 26-SEP-22

Name:/u01/app/oracle/fast_recovery_area/archivelog/1_57_1101942938.dbf

Do youreally want to delete the above objects (enter YES or NO)? yes

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_42_1101942938.dbfRECID=73 STAMP=1116425776

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_43_1101942938.dbfRECID=75 STAMP=1116425778

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_44_1101942938.dbfRECID=77 STAMP=1116425783

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_45_1101942938.dbfRECID=79 STAMP=1116425786

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_46_1101942938.dbfRECID=81 STAMP=1116425790

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_47_1101942938.dbfRECID=83 STAMP=1116425793

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_48_1101942938.dbfRECID=85 STAMP=1116425796

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_49_1101942938.dbfRECID=87 STAMP=1116425799

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_50_1101942938.dbfRECID=89 STAMP=1116425802

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_51_1101942938.dbfRECID=91 STAMP=1116425805

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_52_1101942938.dbfRECID=93 STAMP=1116425808

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_53_1101942938.dbfRECID=95 STAMP=1116425811

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_54_1101942938.dbfRECID=97 STAMP=1116425814

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_55_1101942938.dbfRECID=99 STAMP=1116425817

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_56_1101942938.dbfRECID=101 STAMP=1116425821

deletedarchived log

archivedlog file name=/u01/app/oracle/fast_recovery_area/archivelog/1_57_1101942938.dbfRECID=102 STAMP=1116425825

Deleted16 objects

结论：主库通过deletearchivelog all可以删除备库未应用的归档日志.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)