oracle 歸檔日誌開啟，關閉

小白今天登陸資料庫，報錯：No space left on Device，檢視磁碟空間：

oracle@linux101:~>df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hda1              30G   28G  2.7G  92% /
udev                   12G   68K   12G   1% /dev
/dev/hda2              52G   18G   31G  37% /opt
/dev/hda3              28G   28G     0 100% /home
shm                    16G  1.7G   15G  11% /dev/shm
shmfs                  16G  1.7G   15G  11% /dev/shm

 

於是小白在/home目錄下的各個目錄級別下檢視個檔案的大小，最後鎖定在$ORACLE_HOME/dbs，發現有8.2G，檢視檔案，找到N多的dbf檔案

-rw-r----- 1 oracle dba 41974272 Dec 25 00:51 arch1_67_719519639.dbf
-rw-r----- 1 oracle dba 41977856 Dec 25 00:52 arch1_68_719519639.dbf
-rw-r----- 1 oracle dba 41981440 Dec 25 00:54 arch1_69_719519639.dbf
-rw-r----- 1 oracle dba 41974784 Dec 25 00:55 arch1_70_719519639.dbf
-rw-r----- 1 oracle dba 41989632 Dec 25 00:57 arch1_71_719519639.dbf
-rw-r----- 1 oracle dba 41976320 Dec 25 01:12 arch1_81_719519639.dbf
-rw-r----- 1 oracle dba 18632192 Dec 20 19:48 arch1_9_719519639.dbf
-rw-rw---- 1 oracle dba     1544 Dec 25 10:32 hc_ora11g.dat
-rw-r--r-- 1 oracle dba     2774 Sep 11  2007 init.ora
-rw-r--r-- 1 oracle dba    12920 May  3  2001 initdw.ora
-rw-r--r-- 1 oracle dba     1152 Dec 19 20:25 initora11g.ora
-rw-r----- 1 oracle dba     1919 Dec 19 13:47 initora11g.ora.bak
-rw-r----- 1 oracle dba       24 May 20  2010 lkORA11G
-rw-r----- 1 oracle dba     1536 May 20  2010 orapwora11g
-rw-r----- 1 oracle dba     4608 Dec 25 10:32 spfileora11g.ora
-rw-r----- 1 oracle dba     3584 Dec 19 18:43 spfileora11g.ora.bak
linux101:/home/oracle/app/product/11/db/dbs # date
Wed Dec 25 10:39:12 CST 2013

如此看來，每個arch*.dbf大小為41M，若200個,8G就被吞掉了，小白決定馬上刪掉歸檔檔案~

方法一：

oracle@linux101:~>rman target/      （進入資料庫的備份工具，這裡的/不能省略，否則會報錯）

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Dec 25 13:53:11 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORA11G (DBID=4115719763)

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE';  

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1051 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
290     1    293     A 25-DEC-13
        Name: /home/oracle/app/product/11/db/dbs/arch1_293_719519639.dbf

291     1    294     A 25-DEC-13
        Name: /home/oracle/app/product/11/db/dbs/arch1_294_719519639.dbf

292     1    295     A 25-DEC-13
        Name: /home/oracle/app/product/11/db/dbs/arch1_295_719519639.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/product/11/db/dbs/arch1_293_719519639.dbf RECID=290 STAMP=835094799
deleted archived log
archived log file name=/home/oracle/app/product/11/db/dbs/arch1_294_719519639.dbf RECID=291 STAMP=835094917
deleted archived log
archived log file name=/home/oracle/app/product/11/db/dbs/arch1_295_719519639.dbf RECID=292 STAMP=835095052
...
...
...
Deleted 287 objects

RMAN>

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE';中

若是SYSDATA-7，表明當前的系統時間7天前，before關鍵字表示在7天前的歸檔日誌，如果使用了閃回功能，也會刪除閃回的資料。在這裡是SYSDATE,表示需要刪除當天的歸檔日誌。

 

方法二：

1. 以oracle使用者刪掉歸檔檔案在磁碟上的儲存：

find $ORACLE_HOME/dbs -xdev -mtime +7 -name "*.dbf" -exec rm -f {} ;

2. 刪除在RMAN裡留下未管理的歸檔檔案

oracle@linux101:~>rman target/                   # 進入資料庫備份工具RMAN

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Dec 25 13:53:11 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORA11G (DBID=4115719763)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1052 device type=DISK
specification does not match any archived log in the recovery catalog

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1052 device type=DISK
specification does not match any archived log in the recovery catalog

RMAN>

 

因為是不是生產環境，不需要歸檔模式，現在講歸檔模式關閉掉，

SQL> archive log list;         # 檢視是否是歸檔模式
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/prod
Oldest online log sequence     292
Next log sequence to archive   294
Current log sequence           294
SQL> alter system set log_archive_start=false scope=spfile;       #禁用自動歸檔模式

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;                      # 開啟控制檔案，不開啟資料檔案
ORACLE instance started.

Total System Global Area 2956300288 bytes
Fixed Size                  2163360 bytes
Variable Size            1778388320 bytes
Database Buffers         1157627904 bytes
Redo Buffers               18120704 bytes
Database mounted.
SQL> alter database noarchivelog;   # 將資料庫切換為非歸檔模式

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/app/product/11/db/dbs/arch
Oldest online log sequence     295
Current log sequence           297

 

若要開啟資料庫的歸檔模式,跟關閉差不多，如下：

sql> alter system set log_archive_start=true scope=spfile; #啟用主動歸檔
sql> shutdown immediate;
sql> startup mount;    #開啟控制檔案，不開啟資料檔案
sql> alter database archivelog; #將資料庫切換為歸檔模式
sql> alter database open;   #將資料檔案開啟
sql> archive log list;   #檢視此時是否處於歸檔模式