Oracle數(shù)據(jù)庫如何刪除歸檔日志文件
前言
Oracle啟用歸檔日志的狀態(tài)下,會在指定的歸檔日志目錄生成大量的日志文件,而且這些日志文件默認(rèn)是不會定期清理。長時間運行下,會占用大量的存儲空間,最終可能導(dǎo)致Oracle沒有足夠的空間維持啟動狀態(tài)。
在Oracle的控制文件中記錄著每一個歸檔日志文件得相關(guān)信息。當(dāng)手動刪除了歸檔日志的物理文件后,控制文件中依然記錄著已刪除的歸檔日志信息。所以當(dāng)我們刪除歸檔日志文件時,不僅需要刪除物理文件,還要刪除控制文件中對應(yīng)的信息。
一、切換Oracle用戶
[root@ea9446c43596 /]# su - oracle
二、查看歸檔日志路徑
歸檔日志路徑不一定相同,如果不清楚歸檔日志在哪,可以通過命令find / -name *.dbf
查找
[oracle@ea9446c43596 ~]$ cd /home/oracle/app/oracle/oradata/archlogs/ [oracle@ea9446c43596 archlogs]$ ls 1_10_900206934.dbf 1_12_900206934.dbf 1_14_900206934.dbf 1_5_900206934.dbf 1_7_900206934.dbf 1_9_900206934.dbf 1_11_900206934.dbf 1_13_900206934.dbf 1_4_900206934.dbf 1_6_900206934.dbf 1_8_900206934.dbf
三、方法一:刪除歸檔日志物理文件
如果之前手動刪除過物理文件,可以查看這個方法,將控制文件中歸檔日志記錄刪除
1 刪除物理文件
刪除物理文件:1_4_900206934.dbf
[oracle@ea9446c43596 archlogs]$ rm -f 1_4_900206934.dbf
2 查看控制文件
通過如下命令查看Oracle控制文件記錄歸檔日志信息,發(fā)現(xiàn)"1_4_900206934.dbf"還存在
rman target /
rman登錄命令
list archivelog all;
查看所有的歸檔日志
[oracle@ea9446c43596 archlogs]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 12 10:01:18 2022 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: HELOWIN (DBID=1384114315) RMAN> list archivelog all; using target database control file instead of recovery catalog List of Archived Log Copies for database with db_unique_name HELOWIN ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 3 1 4 A 07-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf 4 1 5 A 07-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf 5 1 6 A 08-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf 6 1 7 A 09-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf 7 1 8 A 09-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf 8 1 9 A 10-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf 9 1 10 A 10-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf 10 1 11 A 10-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf 11 1 12 A 11-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf 12 1 13 A 11-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf 13 1 14 A 11-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf
3 對比檢查歸檔日志物理文件和控制文件一致性
如果你之前已經(jīng)刪除過物理文件,并沒有刪除控制文件中的信息,請看如下操作:
crosscheck archivelog all;
優(yōu)先使用這個命令,核驗比對物理文件和歸檔日志記錄信息一致性
list expired archivelog all;
crosscheck
后,可以使用這個命令,查看有哪些過期的日志文件
RMAN> crosscheck archivelog all; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=772 device type=DISK validation failed for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf RECID=3 STAMP=1122847256 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf RECID=4 STAMP=1122894018 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf RECID=5 STAMP=1122948041 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf RECID=6 STAMP=1122980424 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf RECID=7 STAMP=1123029988 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf RECID=8 STAMP=1123066818 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf RECID=9 STAMP=1123095615 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf RECID=10 STAMP=1123124827 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf RECID=11 STAMP=1123153640 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf RECID=12 STAMP=1123184937 validation succeeded for archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf RECID=13 STAMP=1123211251 Crosschecked 11 objects RMAN> list expired archivelog all; List of Archived Log Copies for database with db_unique_name HELOWIN ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 3 1 4 X 07-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf
4 刪除過期文件
delete expired archivelog all;
刪除過期日志文件,在執(zhí)行命令后,會提示你將要刪除哪些過期的日志文件
RMAN> delete expired archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=772 device type=DISK List of Archived Log Copies for database with db_unique_name HELOWIN ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 3 1 4 X 07-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf Do you really want to delete the above objects (enter YES or NO)? yes deleted archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf RECID=3 STAMP=1122847256 Deleted 1 EXPIRED objects
四、方法二:rman刪除歸檔日志
這種方法,可以將控制文件中記錄信息和物理文件同步刪除,更為方便。
1 查看控制文件記錄
rman target /
登錄rman
list archivelog all;
查看所有歸檔日志信息
[oracle@ea9446c43596 archlogs]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 12 10:14:25 2022 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: HELOWIN (DBID=1384114315) RMAN> list archivelog all; using target database control file instead of recovery catalog List of Archived Log Copies for database with db_unique_name HELOWIN ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 4 1 5 A 07-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf 5 1 6 A 08-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf 6 1 7 A 09-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf 7 1 8 A 09-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf 8 1 9 A 10-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf 9 1 10 A 10-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf 10 1 11 A 10-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf 11 1 12 A 11-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf 12 1 13 A 11-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf 13 1 14 A 11-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf
2 刪除指定日期前日志
delete archivelog all completed before 'SYSDATE - 3';
刪除3天前的歸檔日志,在刪除控制文件記錄的同時,也會將物理文件刪除。
RMAN> delete archivelog all completed before 'SYSDATE - 3'; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=772 device type=DISK List of Archived Log Copies for database with db_unique_name HELOWIN ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 4 1 5 A 07-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf 5 1 6 A 08-DEC-22 Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf Do you really want to delete the above objects (enter YES or NO)? yes deleted archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf RECID=4 STAMP=1122894018 deleted archived log archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf RECID=5 STAMP=1122948041 Deleted 2 objects
3 查看物理文件
在上一步中刪除了1_5_900206934.dbf
和1_6_900206934.dbf
文件,觀察物理文件列表后,可以看到物理文件也會被同步刪除。
[oracle@ea9446c43596 archlogs]$ ls 1_10_900206934.dbf 1_12_900206934.dbf 1_14_900206934.dbf 1_8_900206934.dbf 1_11_900206934.dbf 1_13_900206934.dbf 1_7_900206934.dbf 1_9_900206934.dbf
五、使用到的命令集合
登錄rman
rman target /
查看所有歸檔日志
list archivelog all;
對比控制文件、物理文件
crosscheck archivelog all;
查看過期歸檔日志
list expired archivelog all;
刪除過期歸檔日志
delete expired archivelog all;
刪除指定日期歸檔日志
delete archivelog all completed before 'SYSDATE - n';
退出rman
exit;
總結(jié)
到此這篇關(guān)于Oracle數(shù)據(jù)庫如何刪除歸檔日志文件的文章就介紹到這了,更多相關(guān)Oracle刪除歸檔日志文件內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Zabbix監(jiān)控Oracle歸檔日志空間的全過程
本文將介紹Zabbix監(jiān)控Oracle歸檔日志空間的全過程,Zabbix是一個開源的監(jiān)控系統(tǒng),它可以監(jiān)控各種不同類型的服務(wù)器和服務(wù),如果您想要監(jiān)控Oracle數(shù)據(jù)庫,文中是一些簡單的步驟,需要的朋友可以參考下2024-04-04windows系統(tǒng)下Oracle?11g完整安裝教程
Oracle?11g?是Orcale公司推出的一款數(shù)據(jù)庫管理系統(tǒng),受廣泛應(yīng)用于企業(yè)級應(yīng)用程序,下面這篇文章主要給大家介紹了關(guān)于windows系統(tǒng)下Oracle?11g完整安裝的相關(guān)資料,需要的朋友可以參考下2024-04-04關(guān)于sql腳本導(dǎo)入Oracle時重復(fù)生成check約束的問題解決
這篇文章主要給大家介紹了關(guān)于sql腳本導(dǎo)入Oracle時重復(fù)生成check約束的問題解決方法,文中給出了詳細(xì)的檢查步驟,對大家理解和解決這個問題具有很好的幫助,需要的朋友們下面來一起看看吧。2017-05-05Oracle中sys和system的區(qū)別小結(jié)
SYS用戶具有DBA權(quán)限,并且擁有SYS模式,只能通過SYSDBA登陸數(shù)據(jù)庫。是Oracle數(shù)據(jù)庫中權(quán)限最高的帳號 SYSTEM具有DBA權(quán)限。但沒有SYSDBA權(quán)限。平常一般用該帳號管理數(shù)據(jù)庫就可以了。2009-11-11