本帖最后由 jiawang 于 2020-5-29 10:53 编辑
数据库:oracle 11.2.0.4.0
OS:Windows 2008 R2
我们在在使用RMAN命令删除归档后,查询v$archived_log视图会发现name列为空了,但其他列的信息还保留,时间长了会留下很多过期的信息,影响维护工作,需要将过期的信息删除。
首先模拟下问题的出现过程: 删除归档日志之前查看v$archived_log视图,情况正常 SQL> col name for a45
SQL> select dest_id,sequence#,name,blocks from v$archived_log
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3787 E:\ARCHIVE\ARC0000003787_0981295486.0001 26267
1 3788 E:\ARCHIVE\ARC0000003788_0981295486.0001 77190
1 3789 E:\ARCHIVE\ARC0000003789_0981295486.0001 61304
1 3790 E:\ARCHIVE\ARC0000003790_0981295486.0001 82720
1 3791 E:\ARCHIVE\ARC0000003791_0981295486.0001 60480
1 3792 E:\ARCHIVE\ARC0000003792_0981295486.0001 47236
1 3793 E:\ARCHIVE\ARC0000003793_0981295486.0001 60955
1 3794 E:\ARCHIVE\ARC0000003794_0981295486.0001 67887
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3795 E:\ARCHIVE\ARC0000003795_0981295486.0001 79003
1 3796 E:\ARCHIVE\ARC0000003796_0981295486.0001 61385
1 3797 E:\ARCHIVE\ARC0000003797_0981295486.0001 59788
1 3798 E:\ARCHIVE\ARC0000003798_0981295486.0001 33872
1 3799 E:\ARCHIVE\ARC0000003799_0981295486.0001 60069
1 3800 E:\ARCHIVE\ARC0000003800_0981295486.0001 62924
1 3801 E:\ARCHIVE\ARC0000003801_0981295486.0001 69551
1 3802 E:\ARCHIVE\ARC0000003802_0981295486.0001 60007
1 3803 E:\ARCHIVE\ARC0000003803_0981295486.0001 59790
1 3804 E:\ARCHIVE\ARC0000003804_0981295486.0001 39796
1 3805 E:\ARCHIVE\ARC0000003805_0981295486.0001 61584
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3806 E:\ARCHIVE\ARC0000003806_0981295486.0001 90796
1 3807 E:\ARCHIVE\ARC0000003807_0981295486.0001 62389
1 3808 E:\ARCHIVE\ARC0000003808_0981295486.0001 14615
1 3809 E:\ARCHIVE\ARC0000003809_0981295486.0001 12579
1 3810 E:\ARCHIVE\ARC0000003810_0981295486.0001 63007
1 3811 E:\ARCHIVE\ARC0000003811_0981295486.0001 59788
1 3812 E:\ARCHIVE\ARC0000003812_0981295486.0001 82726
1 3813 E:\ARCHIVE\ARC0000003813_0981295486.0001 61184
1 3814 E:\ARCHIVE\ARC0000003814_0981295486.0001 27207
1 3815 E:\ARCHIVE\ARC0000003815_0981295486.0001 63358
1 3816 E:\ARCHIVE\ARC0000003816_0981295486.0001 59789
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3817 E:\ARCHIVE\ARC0000003817_0981295486.0001 82720
1 3818 E:\ARCHIVE\ARC0000003818_0981295486.0001 61492
1 3819 E:\ARCHIVE\ARC0000003819_0981295486.0001 26813
1 3820 E:\ARCHIVE\ARC0000003820_0981295486.0001 82720
1 3821 E:\ARCHIVE\ARC0000003821_0981295486.0001 64333
1 3822 E:\ARCHIVE\ARC0000003822_0981295486.0001 59793
1 3823 E:\ARCHIVE\ARC0000003823_0981295486.0001 75450
1 3824 E:\ARCHIVE\ARC0000003824_0981295486.0001 60003
1 3825 E:\ARCHIVE\ARC0000003825_0981295486.0001 26103
1 3826 E:\ARCHIVE\ARC0000003826_0981295486.0001 62833
1 3827 E:\ARCHIVE\ARC0000003827_0981295486.0001 60069
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3828 E:\ARCHIVE\ARC0000003828_0981295486.0001 65839
已选择56行。
查询归档日志
RMAN> crosscheck archivelog all;
继续用rman删除归档日志
RMAN> delete archivelog all;
再次查询视图
SQL> r
1* select dest_id,sequence#,name,blocks from v$archived_log
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3773 64495
1 3774 61107
1 3775 82720
1 3776 59862
1 3777 30223
1 3778 65401
1 3779 60702
1 3780 83477
1 3781 60351
1 3782 26696
1 3783 60344
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3784 60147
1 3785 65908
1 3786 59787
1 3787 26267
1 3788 77190
1 3789 61304
1 3790 82720
1 3791 60480
1 3792 47236
1 3793 60955
1 3794 67887
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3795 79003
1 3796 61385
1 3797 59788
1 3798 33872
1 3799 60069
1 3800 62924
1 3801 69551
1 3802 60007
1 3803 59790
1 3804 39796
1 3805 61584
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3806 90796
1 3807 62389
1 3808 14615
1 3809 12579
1 3810 63007
1 3811 59788
1 3812 82726
1 3813 61184
1 3814 27207
1 3815 63358
1 3816 59789
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3817 82720
1 3818 61492
1 3819 26813
1 3820 82720
1 3821 64333
1 3822 59793
1 3823 75450
1 3824 60003
1 3825 26103
1 3826 62833
1 3827 60069
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 3828 65839
已选择56行。
SQL>
出现这样的现象是因为使用RMAN命令在删除归档日志的时候不能够清除控制文件中的内容,导致v$archived_log留下的过期的不完整信息。
解决办法:下面将归档信息进行清除: 清除控制文件中关于v$archived_log的信息 SQL> execute sys.dbms_backup_restore.resetCfileSection(11); PL/SQL 过程已成功完成。 再次查询v$archived_log,信息已经被清除 SQL> select dest_id,sequence#,name,blocks from v$archived_log; 未选定行 但是这样是把所有的v$archive_log信息都清除了,包括未过期的也会不清除。
|