oracle 查询归档日志信息
任务：鼎甲备份里面有一个定时任务，每天需要删除系统日期前十天的归档日志信息，有时候软件会提示删除失败，这时候需要去数据库查询日志是否被成功删除。如程序执行错误，则需要登陆系统手工验证后执行日志删除。
操作如下：
环境 hp unix oracle 11.2.0.4
登陆 rman ，查询前十天的归档信息
RMAN> list archivelog all completed before 'sysdate -10';
List of Archived Log Copies for database with db_unique_name HISDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
218899 1 3865 A 2022-06-17 20:21:41
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3865.1860.1107659933
218901 1 3866 A 2022-06-18 03:18:52
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3866.2056.1107659941
218907 1 3867 A 2022-06-18 03:19:01
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3867.2020.1107680459
218911 1 3868 A 2022-06-18 09:00:58
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3868.2031.1107685707
218915 1 3869 A 2022-06-18 10:28:27
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3869.2063.1107688003
218917 1 3870 A 2022-06-18 11:06:42
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3870.1706.1107688469
218920 1 3871 A 2022-06-18 11:14:28
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3871.1823.1107688607
218923 1 3872 A 2022-06-18 11:16:29
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3872.1812.1107688733
218925 1 3873 A 2022-06-18 11:18:53
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3873.1798.1107688857
218927 1 3874 A 2022-06-18 11:20:57
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3874.1825.1107688981
218931 1 3875 A 2022-06-18 11:23:00
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3875.1852.1107689107
218933 1 3876 A 2022-06-18 11:25:07
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3876.1878.1107689231
218936 1 3877 A 2022-06-18 11:27:10
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3877.1930.1107689355
218939 1 3878 A 2022-06-18 11:29:14
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3878.1807.1107689475
218941 1 3879 A 2022-06-18 11:31:14
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3879.1888.1107689589
218945 1 3880 A 2022-06-18 11:33:09
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3880.1827.1107689709
218947 1 3881 A 2022-06-18 11:35:09
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3881.1894.1107689833
218949 1 3882 A 2022-06-18 11:37:12
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3882.2054.1107689951
218952 1 3883 A 2022-06-18 11:39:10
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3883.2039.1107690063
218955 1 3884 A 2022-06-18 11:41:01
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3884.1933.1107690173
218957 1 3885 A 2022-06-18 11:42:53
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3885.1916.1107690541
218961 1 3886 A 2022-06-18 11:49:00
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3886.1927.1107690665
218963 1 3887 A 2022-06-18 11:51:04
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3887.1899.1107690787
218965 1 3888 A 2022-06-18 11:53:07
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3888.2044.1107690911
218969 1 3889 A 2022-06-18 11:55:10
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3889.1972.1107691041
218971 1 3890 A 2022-06-18 11:57:20
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3890.1876.1107691167
218973 1 3891 A 2022-06-18 11:59:26
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3891.1702.1107691299
218977 1 3892 A 2022-06-18 12:01:39
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3892.1687.1107691419
218979 1 3893 A 2022-06-18 12:03:39
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3893.2036.1107691531
218981 1 3894 A 2022-06-18 12:05:31
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3894.2071.1107691657
218985 1 3895 A 2022-06-18 12:07:37
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3895.1709.1107691773
218987 1 3896 A 2022-06-18 12:09:32
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3896.1808.1107691895
218989 1 3897 A 2022-06-18 12:11:35
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3897.1799.1107692007
218993 1 3898 A 2022-06-18 12:13:27
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_1_seq_3898.1892.1107692119
218895 2 3873 A 2022-06-17 20:20:14
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3873.1962.1107654175
218898 2 3874 A 2022-06-18 01:42:54
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3874.1412.1107659931
218900 2 3875 A 2022-06-18 03:18:49
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3875.1948.1107659941
218905 2 3876 A 2022-06-18 03:19:01
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3876.1728.1107678835
218909 2 3877 A 2022-06-18 08:33:55
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3877.1691.1107684067
218913 2 3878 A 2022-06-18 10:01:06
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3878.2008.1107686755
218921 2 3879 A 2022-06-18 10:45:55
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3879.1819.1107688609
218928 2 3880 A 2022-06-18 11:16:47
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3880.1817.1107688983
218937 2 3881 A 2022-06-18 11:23:03
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3881.1847.1107689357
218944 2 3882 A 2022-06-18 11:29:16
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3882.2076.1107689709
218953 2 3883 A 2022-06-18 11:35:09
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3883.1803.1107690067
218960 2 3884 A 2022-06-18 11:41:04
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3884.1918.1107690665
218968 2 3885 A 2022-06-18 11:51:05
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3885.1957.1107691041
218976 2 3886 A 2022-06-18 11:57:21
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3886.1689.1107691421
218984 2 3887 A 2022-06-18 12:03:40
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3887.1834.1107691773
218992 2 3888 A 2022-06-18 12:09:32
Name: +ARCH/hisdb/archivelog/2022_06_18/thread_2_seq_3888.1917.1107692119
RMAN>
同时使用grid用户登陆 asmcmd 查看归档日志路径信息，切换到 +ARCH下查看
ASMCMD> ls
2021_12_28/
2022_06_18/
2022_06_19/
2022_06_20/
2022_06_21/
2022_06_22/
2022_06_23/
2022_06_24/
2022_06_25/
2022_06_26/
2022_06_27/
2022_06_28/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y 2021_12_28/
Y 2022_06_18/
Y 2022_06_19/
Y 2022_06_20/
Y 2022_06_21/
Y 2022_06_22/
Y 2022_06_23/
Y 2022_06_24/
Y 2022_06_25/
Y 2022_06_26/
Y 2022_06_27/
Y 2022_06_28/
ASMCMD>
ASMCMD> ls -l *18
Type Redund Striped Time Sys Name
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3865.1860.1107659933
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3866.2056.1107659941
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3867.2020.1107680459
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3868.2031.1107685707
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3869.2063.1107688003
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3870.1706.1107688469
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3871.1823.1107688607
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3872.1812.1107688733
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3873.1798.1107688857
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3874.1825.1107688981
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3875.1852.1107689107
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3876.1878.1107689231
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3877.1930.1107689355
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3878.1807.1107689475
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3879.1888.1107689589
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3880.1827.1107689709
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3881.1894.1107689833
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3882.2054.1107689951
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3883.2039.1107690063
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3884.1933.1107690173
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3885.1916.1107690541
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3886.1927.1107690665
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3887.1899.1107690787
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3888.2044.1107690911
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3889.1972.1107691041
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3890.1876.1107691167
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3891.1702.1107691299
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3892.1687.1107691419
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3893.2036.1107691531
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3894.2071.1107691657
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3895.1709.1107691773
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3896.1808.1107691895
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3897.1799.1107692007
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3898.1892.1107692119
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3899.1896.1107703517
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3900.1919.1107703583
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3901.1678.1107711355
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3902.1975.1107721139
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3903.1971.1107729915
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3904.1820.1107730559
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3905.1995.1107731025
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3906.1880.1107732155
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3873.1962.1107654175
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3874.1412.1107659931
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3875.1948.1107659941
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3876.1728.1107678835
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3877.1691.1107684067
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3878.2008.1107686755
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3879.1819.1107688609
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3880.1817.1107688983
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3881.1847.1107689357
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3882.2076.1107689709
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3883.1803.1107690067
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3884.1918.1107690665
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3885.1957.1107691041
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3886.1689.1107691421
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3887.1834.1107691773
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3888.1917.1107692119
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3889.1850.1107703517
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3890.1832.1107703583
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3891.2096.1107710575
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3892.1741.1107724373
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3893.1815.1107729913
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3894.1718.1107729917
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3895.2078.1107730551
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3896.2079.1107730557
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3897.1802.1107730793
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3898.1821.1107730795
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3899.1924.1107731017
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3900.1925.1107731023
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3901.1929.1107731127
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3902.1872.1107731127
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3903.1965.1107732151
ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3904.1969.1107732153
ASMCMD>
数据库备份软件执行删除任务后，显示任务成功执行，软件显示如下
再次执行日志查询显示为空：
RMAN> list archivelog all completed before 'sysdate -10'; RMAN>
系统查看日志文件如下：
ASMCMD> ls -l *18 Type Redund Striped Time Sys Name ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3899.1896.1107703517 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3900.1919.1107703583 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3901.1678.1107711355 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3902.1975.1107721139 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3903.1971.1107729915 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3904.1820.1107730559 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3905.1995.1107731025 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_1_seq_3906.1880.1107732155 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3889.1850.1107703517 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3890.1832.1107703583 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3891.2096.1107710575 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3892.1741.1107724373 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3893.1815.1107729913 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3894.1718.1107729917 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3895.2078.1107730551 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3896.2079.1107730557 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3897.1802.1107730793 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3898.1821.1107730795 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3899.1924.1107731017 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3900.1925.1107731023 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3901.1929.1107731127 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3902.1872.1107731127 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3903.1965.1107732151 ARCHIVELOG UNPROT COARSE JUN 28 12:00:00 Y thread_2_seq_3904.1969.1107732153 ASMCMD>
自此归档成功删除。
如未成功删除可以尝试手工命令删除
delete archivelog all completed before 'sysdate-N'
