【實驗】通過v$archived_log檢視獲得歸檔日誌檔案的詳細資訊

secooler發表於2009-07-28

1.查詢方法，SQL語句，這不是重點，重點是在2的status欄位解釋，在不看第二點的說明前，看看聰明的你能不能推斷出字母A,D和X等的含義
sys@ora10g> set lines 200
sys@ora10g> col name for a100
sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
sys@ora10g> select name,completion_time,status from v$archived_log;

NAME                                                    COMPLETION_TIME     S
------------------------------------------------------- ------------------- -
/archivelog/2009_07_06/o1_mf_1_12_553rlmlp_.arc         2009-07-06 19:39:00 X
/archivelog/2009_07_07/o1_mf_1_13_554lgm7w_.arc         2009-07-07 03:00:35 A
/archivelog/2009_07_07/o1_mf_1_14_555l2t7w_.arc         2009-07-07 12:00:26 A
/archivelog/2009_07_07/o1_mf_1_15_555mvzbo_.arc         2009-07-07 12:30:56 A
/archivelog/2009_07_07/o1_mf_1_16_555mw6v0_.arc         2009-07-07 12:31:03 A
/archivelog/2009_07_07/o1_mf_1_17_555mwvrq_.arc         2009-07-07 12:31:24 A
/archivelog/2009_07_07/o1_mf_1_18_555mxk1k_.arc         2009-07-07 12:31:45 A
/archivelog/2009_07_07/o1_mf_1_19_555my406_.arc         2009-07-07 12:32:04 A
/archivelog/2009_07_07/o1_mf_1_20_555mypvr_.arc         2009-07-07 12:32:23 A
/archivelog/2009_07_07/o1_mf_1_21_555mz9hy_.arc         2009-07-07 12:32:42 A
/archivelog/2009_07_07/o1_mf_1_22_555mzzlh_.arc         2009-07-07 12:33:04 A
/archivelog/2009_07_07/o1_mf_1_23_555n0o84_.arc         2009-07-07 12:33:25 A
/archivelog/2009_07_07/o1_mf_1_24_555n1c9s_.arc         2009-07-07 12:33:48 A
/archivelog/2009_07_07/o1_mf_1_25_555n1qdj_.arc         2009-07-07 12:34:00 A
/archivelog/2009_07_07/o1_mf_1_26_555n3lpc_.arc         2009-07-07 12:34:59 A
/archivelog/2009_07_07/o1_mf_1_27_555n43dp_.arc         2009-07-07 12:35:16 A
/archivelog/2009_07_07/o1_mf_1_28_555n49rw_.arc         2009-07-07 12:35:22 A
/archivelog/2009_07_07/o1_mf_1_29_555n4h03_.arc         2009-07-07 12:35:27 A
/archivelog/2009_07_07/o1_mf_1_30_555n4p7m_.arc         2009-07-07 12:35:34 A
/archivelog/2009_07_07/o1_mf_1_31_555n53dd_.arc         2009-07-07 12:35:48 A
/archivelog/2009_07_07/o1_mf_1_32_555n5ph9_.arc         2009-07-07 12:36:07 A
/archivelog/2009_07_07/o1_mf_1_33_555n5zms_.arc         2009-07-07 12:36:16 A
/archivelog/2009_07_07/o1_mf_1_34_555n680k_.arc         2009-07-07 12:36:24 A
/archivelog/2009_07_07/o1_mf_1_35_555n6gh4_.arc         2009-07-07 12:36:31 A
/archivelog/2009_07_07/o1_mf_1_36_555n6mz4_.arc         2009-07-07 12:36:36 A
/archivelog/2009_07_07/o1_mf_1_37_555n6son_.arc         2009-07-07 12:36:42 A
/archivelog/2009_07_07/o1_mf_1_38_555n7l2b_.arc         2009-07-07 12:37:06 A
/archivelog/2009_07_07/o1_mf_1_39_555n7w5w_.arc         2009-07-07 12:37:16 A
/archivelog/2009_07_07/o1_mf_1_40_555n862s_.arc         2009-07-07 12:37:26 A
/archivelog/2009_07_07/o1_mf_1_41_555n8ot7_.arc         2009-07-07 12:37:42 A
/archivelog/2009_07_07/o1_mf_1_42_555n8zft_.arc         2009-07-07 12:37:52 A
/archivelog/2009_07_07/o1_mf_1_43_555n977o_.arc         2009-07-07 12:37:59 A
/archivelog/2009_07_07/o1_mf_1_44_555n9pp5_.arc         2009-07-07 12:38:15 A
/archivelog/2009_07_07/o1_mf_1_45_555nb0rx_.arc         2009-07-07 12:38:25 A
/archivelog/2009_07_07/o1_mf_1_46_555nbcgd_.arc         2009-07-07 12:38:36 A
/archivelog/2009_07_07/o1_mf_1_47_555nblhp_.arc         2009-07-07 12:38:43 A
/archivelog/2009_07_07/o1_mf_1_48_555nbsz4_.arc         2009-07-07 12:38:50 A
/archivelog/2009_07_07/o1_mf_1_49_555nc0pb_.arc         2009-07-07 12:38:57 A
/archivelog/2009_07_07/o1_mf_1_50_555nc7sg_.arc         2009-07-07 12:39:04 A
/archivelog/2009_07_07/o1_mf_1_51_555ncl1t_.arc         2009-07-07 12:39:14 A
/archivelog/2009_07_07/o1_mf_1_52_555nd5ft_.arc         2009-07-07 12:39:34 A
/archivelog/2009_07_07/o1_mf_1_53_555ndy78_.arc         2009-07-07 12:39:59 A
/archivelog/2009_07_07/o1_mf_1_54_555p2711_.arc         2009-07-07 13:08:24 A
/archivelog/2009_07_07/o1_mf_1_55_556gc237_.arc         2009-07-07 20:02:43 A
/archivelog/2009_07_08/o1_mf_1_56_556zrjkv_.arc         2009-07-08 01:00:01 A
/archivelog/2009_07_09/o1_mf_1_57_55bh76n0_.arc         2009-07-09 08:42:16 A
/archivelog/2009_07_09/o1_mf_1_58_55bh76qd_.arc         2009-07-09 08:42:17 A
/archivelog/2009_07_09/o1_mf_1_59_55bh7b6g_.arc         2009-07-09 08:42:19 A
/archivelog/2009_07_09/o1_mf_1_60_55bh7nwd_.arc         2009-07-09 08:42:29 A
/archivelog/2009_07_10/o1_mf_1_66_55g0bzj5_.arc         2009-07-10 16:52:48 A
/archivelog/2009_07_10/o1_mf_1_67_55gldwk2_.arc         2009-07-10 22:01:01 A
/archivelog/2009_07_11/o1_mf_1_68_55hgh5c7_.arc         2009-07-11 06:00:06 A
/archivelog/2009_07_11/o1_mf_1_69_55j02b44_.arc         2009-07-11 11:00:26 A
/archivelog/2009_07_11/o1_mf_1_70_55jzv2f6_.arc         2009-07-11 20:02:43 A
/archivelog/2009_07_12/o1_mf_1_71_55kzd9no_.arc         2009-07-12 05:00:59 A
/archivelog/2009_07_12/o1_mf_1_72_55lshvjh_.arc         2009-07-12 12:26:36 A
/archivelog/2009_07_12/o1_mf_1_73_55mtof2s_.arc         2009-07-12 21:52:45 A
/archivelog/2009_07_13/o1_mf_1_74_55ns4m3b_.arc         2009-07-13 06:32:51 A
/archivelog/2009_07_13/o1_mf_1_75_55omj805_.arc         2009-07-13 14:02:48 A
/archivelog/2009_07_13/o1_mf_1_76_55phhlv0_.arc         2009-07-13 22:00:19 A
/archivelog/2009_07_14/o1_mf_1_77_55pxlgof_.arc         2009-07-14 02:00:47 A
/archivelog/2009_07_14/o1_mf_1_78_55qwtbko_.arc         2009-07-14 10:54:03 A
/archivelog/2009_07_14/o1_mf_1_79_55rscc79_.arc         2009-07-14 19:00:59 A
/archivelog/2009_07_14/o1_mf_1_80_55s6fcgz_.arc         2009-07-14 22:43:56 A
/archivelog/2009_07_15/o1_mf_1_81_55t0hqcc_.arc         2009-07-15 06:08:56 A
/archivelog/2009_07_15/o1_mf_1_82_55trnf2s_.arc         2009-07-15 13:01:01 A
/archivelog/2009_07_15/o1_mf_1_83_55vr74t8_.arc         2009-07-15 22:00:05 A
/archivelog/2009_07_15/o1_mf_1_84_55vy0cm2_.arc         2009-07-15 23:38:52 A
/archivelog/2009_07_16/o1_mf_1_85_55wxy2vz_.arc         2009-07-16 08:43:47 A
/archivelog/2009_07_16/o1_mf_1_86_55xqjp6n_.arc         2009-07-16 16:00:22 A
/archivelog/2009_07_16/o1_mf_1_87_55ydmr1r_.arc         2009-07-16 22:00:24 A
/archivelog/2009_07_17/o1_mf_1_88_55z030v2_.arc         2009-07-17 03:32:49 A
/archivelog/2009_07_17/o1_mf_1_89_55ztb95d_.arc         2009-07-17 11:00:25 A
/archivelog/2009_07_17/o1_mf_1_90_560pgk7z_.arc         2009-07-17 19:00:34 A
/archivelog/2009_07_17/o1_mf_1_91_56110mcm_.arc         2009-07-17 22:00:52 A
/archivelog/2009_07_18/o1_mf_1_92_561onj3t_.arc         2009-07-18 03:52:48 A
/archivelog/2009_07_18/o1_mf_1_93_56245m86_.arc         2009-07-18 08:00:52 A
/archivelog/2009_07_18/o1_mf_1_94_562wxc6w_.arc         2009-07-18 15:03:07 A
/archivelog/2009_07_18/o1_mf_1_95_563srrj1_.arc         2009-07-18 23:15:37 A
/archivelog/2009_07_19/o1_mf_1_96_564osl49_.arc         2009-07-19 07:13:55 A
/archivelog/2009_07_19/o1_mf_1_97_565j353w_.arc         2009-07-19 14:42:45 A
/archivelog/2009_07_19/o1_mf_1_98_566g5cln_.arc         2009-07-19 23:15:56 A
/archivelog/2009_07_20/o1_mf_1_99_5677rbt3_.arc         2009-07-20 06:32:43 A
/archivelog/2009_07_20/o1_mf_1_100_567tm3wn_.arc        2009-07-20 11:54:12 A
/archivelog/2009_07_20/o1_mf_1_101_568mkx2w_.arc        2009-07-20 19:00:14 A
/archivelog/2009_07_20/o1_mf_1_102_568y4zmo_.arc        2009-07-20 22:01:04 A
/archivelog/2009_07_21/o1_mf_1_103_569nf63k_.arc        2009-07-21 04:20:54 A
/archivelog/2009_07_21/o1_mf_1_104_56bhbphq_.arc        2009-07-21 12:00:23 A
/archivelog/2009_07_21/o1_mf_1_105_56bpdz2f_.arc        2009-07-21 14:01:03 A
/archivelog/2009_07_21/o1_mf_1_106_56bq6bkl_.arc        2009-07-21 14:14:34 A
/archivelog/2009_07_21/o1_mf_1_107_56bqgg4p_.arc        2009-07-21 14:18:54 A
/archivelog/2009_07_21/o1_mf_1_108_56cgytqf_.arc        2009-07-21 21:00:11 A
/archivelog/2009_07_21/o1_mf_1_109_56cpn46m_.arc        2009-07-21 23:11:00 A
/archivelog/2009_07_22/o1_mf_1_110_56dj12mj_.arc        2009-07-22 06:24:35 A
/archivelog/2009_07_22/o1_mf_1_111_56fjv3vx_.arc        2009-07-22 15:44:36 A
/archivelog/2009_07_22/o1_mf_1_112_56g6vbrk_.arc        2009-07-22 22:00:11 A
/archivelog/2009_07_23/o1_mf_1_113_56gfxz16_.arc        2009-07-23 00:01:03 A
/archivelog/2009_07_23/o1_mf_1_114_56hfrzmd_.arc        2009-07-23 09:04:32 A
/archivelog/2009_07_23/o1_mf_1_115_56hq9grl_.arc        2009-07-23 11:46:55 A
/archivelog/2009_07_23/o1_mf_1_116_56j8shjn_.arc        2009-07-23 16:45:36 A
/archivelog/2009_07_23/o1_mf_1_117_56jv7yb3_.arc        2009-07-23 22:00:31 A
/archivelog/2009_07_24/o1_mf_1_118_56kc5f8m_.arc        2009-07-24 02:32:13 A
/archivelog/2009_07_24/o1_mf_1_119_56lcjpo1_.arc        2009-07-24 11:44:23 A
/archivelog/2009_07_24/o1_mf_1_120_56lhsf9m_.arc        2009-07-24 12:57:17 A
/archivelog/2009_07_24/o1_mf_1_121_56lhvhxq_.arc        2009-07-24 12:58:24 A
/archivelog/2009_07_24/o1_mf_1_122_56lhxg5f_.arc        2009-07-24 12:59:26 A
/archivelog/2009_07_24/o1_mf_1_123_56lhz868_.arc        2009-07-24 13:00:24 A
/archivelog/2009_07_24/o1_mf_1_124_56lj12sz_.arc        2009-07-24 13:01:22 A
/archivelog/2009_07_24/o1_mf_1_125_56lj1vs4_.arc        2009-07-24 13:01:47 A
/archivelog/2009_07_24/o1_mf_1_126_56lj4tmr_.arc        2009-07-24 13:03:22 A
/archivelog/2009_07_24/o1_mf_1_127_56m1vos0_.arc        2009-07-24 18:05:42 A
/archivelog/2009_07_24/o1_mf_1_128_56mhnd1n_.arc        2009-07-24 22:00:44 A
/archivelog/2009_07_25/o1_mf_1_129_56n4qy8n_.arc        2009-07-25 04:01:02 A
/archivelog/2009_07_25/o1_mf_1_130_56nlyzvf_.arc        2009-07-25 08:03:44 A
/archivelog/2009_07_25/o1_mf_1_131_56od9156_.arc        2009-07-25 15:15:45 A
/archivelog/2009_07_25/o1_mf_1_132_56p7pv0x_.arc        2009-07-25 23:03:55 A
/archivelog/2009_07_26/o1_mf_1_133_56q6v4mp_.arc        2009-07-26 07:55:17 A
/archivelog/2009_07_26/o1_mf_1_134_56qzr5bn_.arc        2009-07-26 15:00:22 A
/archivelog/2009_07_26/o1_mf_1_135_56rw3jw9_.arc        2009-07-26 23:04:17 A
/archivelog/2009_07_27/o1_mf_1_136_56srb8h5_.arc        2009-07-27 07:05:45 A
                                                        2009-07-27 15:00:18 D
/archivelog/2009_07_27/o1_mf_1_138_56vdqh0f_.arc        2009-07-27 22:00:15 A
/archivelog/2009_07_28/o1_mf_1_139_56vov462_.arc        2009-07-28 00:35:48 A
/archivelog/2009_07_28/o1_mf_1_140_56wonlh8_.arc        2009-07-28 09:38:27 A
                                                        2009-07-28 09:40:29 D
/archivelog/2009_07_27/o1_mf_1_137_56tn41rl_.arc        2009-07-28 09:40:43 A
/archivelog/2009_07_28/o1_mf_1_141_56wsj62m_.arc        2009-07-28 10:44:22 A

127 rows selected.

sys@ora10g>

2.分別解釋status引數值的含義
STATUS=A Available，表示歸檔日誌檔案有效
STATUS=U Unavailable，表示歸檔日誌檔案無效
STATUS=D Deleted，表示歸檔日誌已經被刪除
STATUS=X eXpired，表示歸檔日誌被使用者使用作業系統的命令刪除了

OK，到此，對上面列出來的資訊有一點感覺了吧。

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-610655/，如需轉載，請註明出處，否則將追究法律責任。

清除Oracle控制檔案中的歸檔資訊v$archived_log
2020-09-01
OracleHive
檢視歸檔日誌空間
2014-05-31
oracle檢視昨天產生歸檔日誌檔案總量
2017-04-12
Oracle
【實驗】【檢視】使用v$bgprocess檢視獲得所有後臺程式資訊
2009-08-08
控制檔案/歸檔日誌
2009-03-12
ORACLE 歸檔日誌資訊sql
2014-02-24
OracleSQL
日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
2014-08-04
備份歸檔日誌檔案
2016-11-11
日誌檔案和歸檔管理
2013-12-11
刪除歸檔日誌檔案
2011-05-27
通過Xtrabackup日誌來恢復檢查點檔案
2017-12-18
RAC中歸檔日誌得命名格式
2011-01-18
用RMAN備份歸檔日誌時檢查歸檔日誌是否存在
2015-01-28
通過web url獲取檔案資訊
2019-05-11
Web
logmnr挖掘歸檔日誌檔案
2016-12-13
改變歸檔日誌檔案大小
2013-02-28
恢復歸檔日誌檔案的常用方法
2011-04-23
歸檔日誌
2016-11-14
【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘
2018-12-18
Oracle
oracle歸檔日誌過滿清理
2017-06-14
Oracle
歸檔日誌檔案無故地增長
2017-02-07
【11g】v$diag_info檢視獲得控制檔案轉儲檔名及位置
2009-03-03
JAVA通過URL連結獲取視訊檔案資訊（無需下載檔案）
2019-07-22
Java
通過 alter system dump logfile語句dump REDO及歸檔日誌資訊示例
2013-11-17
非歸檔丟失日誌檔案的恢復
2014-07-08
【實驗】【Archived Log】歸檔日誌格式和歸檔路徑之change趣談
2009-09-20
Hive
oracle 刪除過期的歸檔日誌
2019-01-03
Oracle
RMAN備份資料檔案+控制檔案+歸檔日誌
2015-01-28
oracle歸檔日誌
2021-04-02
Oracle
Oracle 歸檔日誌
2013-12-05
Oracle
歸檔日誌挖掘
2024-02-29
通過nfs共享目錄實現oracle 歸檔日誌遠端備份
2012-10-15
NFSOracle
恢復控制檔案後，沒有最後一個歸檔日誌的備份，也沒新增歸檔日誌資訊，怎麼恢復？
2017-09-18
mysql的日誌檔案詳解
2018-04-28
MySql
歸檔日誌多歸檔路徑 duplex
2013-12-02
如何正確刪除ORACLE歸檔日誌檔案
2017-03-11
Oracle
歸檔日誌的刪除
2012-06-14
日誌檔案過大清理
2019-04-18

【實驗】通過v$archived_log檢視獲得歸檔日誌檔案的詳細資訊

相關文章