oracle 报错归档日志文件,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:51arch1_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;   #查看此时是否处于归档模式