手工清除归档处理归档空间满

最新推荐文章于 2023-06-09 17:16:36 发布

cuixuba8515

最新推荐文章于 2023-06-09 17:16:36 发布

阅读量183

点赞数

文章版权所有Jusin Hao（luckyfriends），支持原创，转载请注明。

1.1. 数据库归档空间满的维护操作

服务器信息

[root@ora-83 ~]# ifconfig -a

eth0 Link encap:Ethernet HWaddr 84:2B:2B:73:8D:21

inet addr:10.168.4.83 Bcast:10.168.4.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:1877375280 errors:1 dropped:0 overruns:0 frame:1

TX packets:2350154974 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:296632242621 (276.2 GiB) TX bytes:559544325176 (521.1 GiB)

Interrupt:82 Memory:da000000-da012800

eth1 Link encap:Ethernet HWaddr 84:2B:2B:73:8D:22

inet addr:10.168.4.80 Bcast:10.168.4.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:1166599051 errors:576 dropped:0 overruns:0 frame:576

TX packets:504077 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:251487510476 (234.2 GiB) TX bytes:32268178 (30.7 MiB)

Interrupt:90 Memory:dc000000-dc012800

lo Link encap:Local Loopback

inet addr:127.0.0.1 Mask:255.0.0.0

UP LOOPBACK RUNNING MTU:16436 Metric:1

RX packets:368818074 errors:0 dropped:0 overruns:0 frame:0

TX packets:368818074 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:53158698831 (49.5 GiB) TX bytes:53158698831 (49.5 GiB)

[root@ora-83 ~]#

SQL> SELECT * FROM V$DATABASE;

DATABASE_NAME

--------------------------------------------------------------------------------

LSDB

检查归档路径位置

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 26154

Next log sequence to archive 26154

Current log sequence 26156

检查闪回区的大小

SQL> show parameter recover

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /u1/app/oracle/flash_recovery_

area

db_recovery_file_dest_size big integer 100G

recovery_parallelism integer 0

SQL>

SQL> show parameter arch

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

log_archive_config string

log_archive_dest string

log_archive_dest_1 string

log_archive_dest_10 string

log_archive_dest_2 string

log_archive_dest_3 string

log_archive_dest_4 string

log_archive_dest_5 string

log_archive_dest_6 string

log_archive_dest_7 string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_8 string

log_archive_dest_9 string

log_archive_dest_state_1 string enable

log_archive_dest_state_10 string enable

log_archive_dest_state_2 string enable

log_archive_dest_state_3 string enable

log_archive_dest_state_4 string enable

log_archive_dest_state_5 string enable

log_archive_dest_state_6 string enable

log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_9 string enable

log_archive_duplex_dest string

log_archive_format string %t_%s_%r.dbf

log_archive_local_first boolean TRUE

log_archive_max_processes integer 2

log_archive_min_succeed_dest integer 1

log_archive_start boolean FALSE

log_archive_trace integer 0

remote_archive_enable string true

standby_archive_dest string ?/dbs/arch

SQL>

SQL> set linesize 1500

SQL> set pagesize 5000

SQL> col member format a50

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------

1 1 26155 52428800 1 YES INACTIVE 5.9722E+12 21-OCT-13

2 1 26156 52428800 1 YES ACTIVE 5.9722E+12 21-OCT-13

3 1 26157 52428800 1 NO CURRENT 5.9722E+12 21-OCT-13

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ------- ------- -------------------------------------------------- ---

3 ONLINE /u1/app/oracle/oradata/lsdb/redo03.log NO

2 ONLINE /u1/app/oracle/oradata/lsdb/redo02.log NO

1 ONLINE /u1/app/oracle/oradata/lsdb/redo01.log NO

连接数据库时报归档空间满

SQL> conn system/oracle@lsdb

ERROR:

ORA-00257: archiver error. Connect internal only, until freed.

查看闪回区的空间使用状态发现已经达到99.9%

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

------------ ------------------ ------------------------- ---------------

CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG 99.97 0 2463

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

6 rows selected.

但是到OS操作系统的文件系统目录下查看实际文件占用为48G，这说明之前物理删除过归档文件，但是没有通过rman将这些已注册担过期的归档日志文件删除。

[oracle@ora-83 archivelog]$ du -sm *

[oracle@ora-83 archivelog]$ du -sm

48691 .

[oracle@ora-83 archivelog]$ ls

2013_09_01 2013_09_06 2013_09_11 2013_09_16 2013_09_21 2013_09_26 2013_10_01

…………

………….

[oracle@ora-83 archivelog]$

[oracle@ora-83 archivelog]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Oct 21 10:16:04 2013

connected to target database: LSDB (DBID=834252102)

RMAN> crosscheck archivelog all;

……………

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26149_9681yyqw_.arc recid=26148 stamp=829356127

validation succeeded for archived log

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26150_9682cxdo_.arc recid=26149 stamp=829356541

validation succeeded for archived log

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26151_9682sb64_.arc recid=26150 stamp=829356970

validation succeeded for archived log

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26152_968338ov_.arc recid=26151 stamp=829357289

validation succeeded for archived log

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26153_9683c4pn_.arc recid=26152 stamp=829357541

Crosschecked 2463 objects

确认删除过期的归档日志文件

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=215 devtype=DISK

List of Archived Log Copies

Key Thrd Seq S Low Time Name

------- ---- ------- - --------- ----

23690 1 23691 X 08-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_08/o1_mf_1_23691_9065fo53_.arc

24977 1 24978 X 31-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24978_9221zz6c_.arc

24978 1 24979 X 31-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24979_9232bnxf_.arc

24979 1 24980 X 31-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24980_9240jkko_.arc

Do you really want to delete the above objects (enter YES or NO)?

………………………

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24979_9232bnxf_.arc recid=24978 stamp=824911957

deleted archive log

archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24980_9240jkko_.arc recid=24979 stamp=824942865

Deleted 1290 EXPIRED objects

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

------------ ------------------ ------------------------- ---------------

CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG 47.49 0 1173

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

6 rows selected.

SELECT substr(name, 1, 30) name,

space_limit AS quota,

space_used AS used,

space_reclaimable AS reclaimable,

number_of_files AS files

FROM v$recovery_file_dest;

NAME QUOTA USED RECLAIMABLE FILES

------------------------------------------------------------ ---------- ------

/u02/flash_recovery_area 1073741824 3576443392 0 82

修改FLASH_RECOVERY_AREA的空间修改为指定大小

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=6g;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14710393/viewspace-1083435/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14710393/viewspace-1083435/

cuixuba8515

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫