ORA-00257故障解决过程
上一篇 / 下一篇 2009-05-08 10:58:45 / 个人分类:quest
概述:
Oracle数据库是目前业界最常用的大型数据库系统,我在实际项目中遇到出现ORA-00257错误(空间不足错误),通过查找资料,绝大部分说这是由于归档日志太多,占用了全部的硬盘剩余空间导致的,通过简单删除日志或加大存储空间就能够解决。但是我在Oracle 10g上发现,存储空间还有很大,却也报这个错误。原来是Oracle 10g中新的特性,对Flash Recovery的管理导致的。
1、软硬件环境
服务器HP Proliant DL580G4(Intel Xeon 3.16GHz/4GB/ 72.8*4/RAID4)
操作系统Red Flag DC Server release 5.0 (Trinity) for x86-64 Linux
数据库Oracle 10.2.0.1.0
2、问题现象
数据库系统已经试运行了半个多月,在7月24日晚上连接数据库后做数据更新时出现ORA-00257错误,如下图。
提示归档错误,通过查找ORACLE错误代码,解释为硬盘空间不足,需要删除归档日志增加空间,但是服务器可用空间200GB,目前只用了10GB左右,这是为什么呢?
3、诊断过程:
1)查看ORACLE数据库归档日志情况
说明在出现问题之前数据库归档处理一直是正常的。
2)查看数据库REDOLOG情况
发现ARC状态为NO,表示系统没法自动做归档。
3)手工切换日志
ORA-01013: 用户请求取消当前的操作
在等待长时间没反应后,中断操作,手工切换日志没有成功。
4)查看Oracle数据库后台归档服务进程
5)查看FLASH_RECOVERY_AREA空间使用情况
6)查看FLASH_RECOVERY_AREA空间中各部分使用情况
这时再查看日志的状态,发现REDO LOG处于正常的归档状态。
5、小结
造成本次故障的原因由两方面同时发生所造成的:
·其一是Flash_Recovery_Area空间缺省安装时比较小,只有2GB,容易用完;
·其二是由于采用归档方式通过Veritas备份,由于备份软件没有运行,造成归档日志没有及时删除。
从本次故障解决处理中,我们可以得出经验教训:
·Oracle 10g数据库物理空间管理方式与以前Oracle发生了变化,对归档日志所在的Flash_Recovery_Area空间进行了另外限制;
·对数据库系统管理员要对Oracle数据库归档日志、备份软件运行状况定期检查,提前发现、处理可能发生的故障。
Oracle数据库是目前业界最常用的大型数据库系统,我在实际项目中遇到出现ORA-00257错误(空间不足错误),通过查找资料,绝大部分说这是由于归档日志太多,占用了全部的硬盘剩余空间导致的,通过简单删除日志或加大存储空间就能够解决。但是我在Oracle 10g上发现,存储空间还有很大,却也报这个错误。原来是Oracle 10g中新的特性,对Flash Recovery的管理导致的。
1、软硬件环境
服务器HP Proliant DL580G4(Intel Xeon 3.16GHz/4GB/ 72.8*4/RAID4)
操作系统Red Flag DC Server release 5.0 (Trinity) for x86-64 Linux
数据库Oracle 10.2.0.1.0
2、问题现象
数据库系统已经试运行了半个多月,在7月24日晚上连接数据库后做数据更新时出现ORA-00257错误,如下图。
提示归档错误,通过查找ORACLE错误代码,解释为硬盘空间不足,需要删除归档日志增加空间,但是服务器可用空间200GB,目前只用了10GB左右,这是为什么呢?
3、诊断过程:
1)查看ORACLE数据库归档日志情况
[root@hrmsdb /]# cd /oracle/flash_recovery_area/HKCHR/archivelog [root@hrmsdb archivelog]# ls 2006_07_04 2006_07_13 2006_07_17 2006_07_20 2006_07_23 2006_07_11 2006_07_14 2006_07_18 2006_07_21 2006_07_24 2006_07_12 2006_07_15 2006_07_19 2006_07_22 2006_07_25 [root@hrmsdb archivelog]# cd 2006_07_25 [root@hrmsdb 2006_07_25]# ls [root@hrmsdb 2006_07_25]# cd ../2006_07_24 [root@hrmsdb 2006_07_24]# ls o1_mf_1_92_2d933vgb_.arc o1_mf_1_96_2d954ns7_.arc o1_mf_1_98_2d969d5h_.arc o1_mf_1_95_2d9537cs_.arc o1_mf_1_97_2d956km0_.arc |
说明在出现问题之前数据库归档处理一直是正常的。
2)查看数据库REDOLOG情况
[oracle@hrmsdb ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 7月 25 10:44:18 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect / as sysdba 已连接。 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- --------------------------------------- -------------- 1 1 101 52428800 1 NO CURRENT 3621973 24-7月 -06 2 1 99 52428800 1 NO INACTIVE 3600145 24-7月 -06 3 1 100 52428800 1 NO INACTIVE 3611932 24-7月 -06 |
发现ARC状态为NO,表示系统没法自动做归档。
3)手工切换日志
SQL> alter system switch logfile; alter system switch logfile * 第 1 行出现错误: |
ORA-01013: 用户请求取消当前的操作
在等待长时间没反应后,中断操作,手工切换日志没有成功。
4)查看Oracle数据库后台归档服务进程
[oracle@hrmsdb ~]$ ps -ef|grep oracle oracle 4601 1 0 Jul11 ? 00:00:04 /oracle/product/10.2.0/db_1/bin/ tnslsnr LISTENER -inherit oracle 5025 1 0 Jul11 ? 00:00:00 /usr/bin/ssh-agent -s oracle 20923 1 0 Jul24 ? 00:00:01 ora_pmon_hkchr oracle 20925 1 0 Jul24 ? 00:00:00 ora_psp0_hkchr oracle 20927 1 0 Jul24 ? 00:00:00 ora_mman_hkchr oracle 20929 1 0 Jul24 ? 00:00:01 ora_dbw0_hkchr oracle 20931 1 0 Jul24 ? 00:01:07 ora_lgwr_hkchr oracle 20933 1 0 Jul24 ? 00:00:05 ora_ckpt_hkchr oracle 20935 1 0 Jul24 ? 00:00:01 ora_smon_hkchr oracle 20937 1 0 Jul24 ? 00:00:00 ora_reco_hkchr oracle 20939 1 0 Jul24 ? 00:00:00 ora_cjq0_hkchr oracle 20941 1 0 Jul24 ? 00:00:01 ora_mmon_hkchr oracle 20943 1 0 Jul24 ? 00:00:05 ora_mmnl_hkchr oracle 20945 1 0 Jul24 ? 00:00:00 ora_d000_hkchr oracle 20947 1 0 Jul24 ? 00:00:00 ora_s000_hkchr oracle 20953 1 0 Jul24 ? 00:09:41 ora_arc0_hkchr oracle 20955 1 1 Jul24 ? 00:10:29 ora_arc1_hkchr oracle 20959 1 0 Jul24 ? 00:00:00 ora_qmnc_hkchr oracle 20967 1 0 Jul24 ? 00:00:00 ora_q000_hkchr oracle 20969 1 0 Jul24 ? 00:00:00 ora_q001_hkchr oracle 21715 1 0 Jul24 ? 00:00:19 oraclehkchr (LOCAL=NO) oracle 21765 1 0 Jul24 ? 00:00:00 ora_j000_hkchr oracle 21816 1 0 Jul24 ? 00:00:00 ora_j001_hkchr oracle 21832 1 0 Jul24 ? 00:00:00 ora_j002_hkchr oracle 21839 1 0 Jul24 ? 00:00:00 ora_j003_hkchr oracle 21859 1 0 Jul24 ? 00:00:00 ora_j004_hkchr oracle 21861 1 0 Jul24 ? 00:00:00 ora_j005_hkchr oracle 21886 1 0 Jul24 ? 00:00:00 ora_j006_hkchr oracle 21888 1 0 Jul24 ? 00:00:00 ora_j007_hkchr root 23187 23186 0 10:39 ? 00:00:00 login -- oracle oracle 23188 23187 0 10:39 pts/0 00:00:00 -bash oracle 23216 23188 0 10:39 pts/0 00:00:00 sqlplus oracle 23217 23216 0 10:39 ? 00:00:00 oraclehkchr (DESCRIPTION=(LOCAL= YES)(ADDRESS=(PROTOCOL=beq))) root 23224 23223 0 10:40 ? 00:00:00 login -- oracle oracle 23225 23224 0 10:40 pts/1 00:00:00 -bash oracle 23310 23225 0 10:46 pts/1 00:00:00 ps -ef oracle 23311 23225 0 10:46 pts/1 00:00:00 grep oracle [oracle@hrmsdb ~]$ 后台进程都正常运行。 |
5)查看FLASH_RECOVERY_AREA空间使用情况
[root@hrmsdb /]# cd /oracle [root@hrmsdb oracle]# ls admin flash_recovery_area oraInventory product [root@hrmsdb oracle]# du -a -k flash_recovery_area 4 flash_recovery_area/HKCHR/onlinelog 42456 flash_recovery_area/HKCHR/archivelog/2006_07_15/o1_mf_1_74_2cj1h1jz_.arc ………………. 42448 flash_recovery_area/HKCHR/archivelog/2006_07_14/o1_mf_1_68_2cfzwwvt_.arc 512560 flash_recovery_area/HKCHR/archivelog/2006_07_14 1469224 flash_recovery_area/HKCHR/archivelog 6988 flash_recovery_area/HKCHR/backupset/2006_07_04/o1_mf_ncsnf_TAG20060704T1 74229_2bng1o0b_.bkp 876916 flash_recovery_area/HKCHR/backupset/2006_07_04/o1_mf_nnndf_TAG20060704T1 74229_2bng0cx4_.bkp 883908 flash_recovery_area/HKCHR/backupset/2006_07_04 883912 flash_recovery_area/HKCHR/backupset 2353144 flash_recovery_area/HKCHR 2353148 flash_recovery_area [root@hrmsdb oracle]# FLASH_RECOVERY_AREA空间使用了2.35GB |
6)查看FLASH_RECOVERY_AREA空间中各部分使用情况
SQL> select * from v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ------------------------------------------------------------------------------------------------------------------ /oracle/flash_recovery_area 2147483648 2134212608 0 35 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 69.97 0 40 BACKUPPIECE 30.01 0 2 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0 已选择6行。 |
发现ARCHIVELOG占近70%,BACKUPPIRCR占了30%,这样FLASH_RECOVERY_AREA空间的空间已经被完全占据了。
4、解决过程
根据数据库目前可用存储空间为200GB、FLASH_RECOVERY_AREA空间为2GB的实际情况,把FLASH_RECOVERY_AREA的空间修改为20GB。
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20g; 系统已更改。 SQL> select * from v$recovery_file_dest; ------------------------------------------------------- ---------- ----------------------------------- NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- ------------- -------------- ---------- ---------- ------------ /oracle/flash_recovery_area 2.1475E+10 2264587776 0 38 |
这时再查看日志的状态,发现REDO LOG处于正常的归档状态。
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- -------------------------------------------- -------------- 1 1 101 52428800 1 YES ACTIVE 3621973 24-7月 -06 2 1 102 52428800 1 NO CURRENT 3650399 25-7月 -06 3 1 100 52428800 1 YES INACTIVE 3611932 24-7月 -06 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 7.6 0 43 BACKUPPIECE 4.21 0 2 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0 已选择6行。 SQL> |
5、小结
造成本次故障的原因由两方面同时发生所造成的:
·其一是Flash_Recovery_Area空间缺省安装时比较小,只有2GB,容易用完;
·其二是由于采用归档方式通过Veritas备份,由于备份软件没有运行,造成归档日志没有及时删除。
从本次故障解决处理中,我们可以得出经验教训:
·Oracle 10g数据库物理空间管理方式与以前Oracle发生了变化,对归档日志所在的Flash_Recovery_Area空间进行了另外限制;
·对数据库系统管理员要对Oracle数据库归档日志、备份软件运行状况定期检查,提前发现、处理可能发生的故障。
正好以前研究过
SQL> create table test.test as select * from all_objects ;
Table created.
SQL> insert into test.test select * from test.test ;
49840 rows created.
SQL> /
99680 rows created.
SQL> /
199360 rows created.
SQL> commit;
Commit complete.
SQL> drop table test.test ;
Table dropped.
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 .1 0 1
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL> alter system switch logfile ;
System altered.
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 2.3 0 2
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL> select owner,object_name,ts_name from dba_recyclebin order by droptime ;
OWNER OBJECT_NAME
------------------------------ ------------------------------
TS_NAME
------------------------------
TEST BIN$MJyuu8o5ZxvgQAB/AQAOxA==$0
TEST
TEST BIN$MJyuu8o6ZxvgQAB/AQAOxA==$0
TEST
TEST BIN$MJyuu8pAZxvgQAB/AQAOxA==$0
TEST
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@test02 u02]$
[oracle@test02 u02]$ rman target sys/oracle@db002
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 17 05:48:31 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DB002 (DBID=908667212)
RMAN> backup database ;
Starting backup at 17-MAY-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/db002/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/db002/sysaux01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/db002/undotbs01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/db002/example01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/db002/test01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/db002/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-MAY-07
channel ORA_DISK_1: finished piece 1 at 17-MAY-07
piece handle=/u02/recovery/DB002/backupset/2007_05_17/o1_mf_nnndf_TAG20070517T054843_34pz1wmp_.bkp tag=TAG20070517T054843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:48
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-MAY-07
channel ORA_DISK_1: finished piece 1 at 17-MAY-07
piece handle=/u02/recovery/DB002/backupset/2007_05_17/o1_mf_ncsnf_TAG20070517T054843_34pz5cbx_.bkp tag=TAG20070517T054843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17-MAY-07
RMAN> exit
Recovery Manager complete.
[oracle@test02 u02]$
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 2.3 2.3 2
BACKUPPIECE 43 0 2
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
backup use rman again ;
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 2.3 2.3 2
BACKUPPIECE 86 43 4
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
SQL> 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 ;
2 3
NAME QUOTA USED RECLAIMABLE FILES
------------------------------ ---------- ---------- ----------- ----------
/u02/recovery 2147483648 1896140288 972746240 6
SQL>
[oracle@test02 u02]$ rman target sys/oracle@db002
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 17 06:06:45 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DB002 (DBID=908667212)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
..............................................
archive log filename=/u02/recovery/DB002/archivelog/2007_05_17/o1_mf_1_27_34px7p7g_.arc recid=26 stamp=622790264
Crosschecked 26 objects
RMAN>
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
specification does not match any archive log in the recovery catalog
RMAN> delete noprompt obsolete ;
delete expired backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN> exit
Recovery Manager complete.
[oracle@test02 u02]$
SQL> 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 ; 2 3
NAME QUOTA USED RECLAIMABLE FILES
------------------------------ ---------- ---------- ----------- ----------
/u02/recovery 2147483648 923394048 0 2
SQL>
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 0 0 0
BACKUPPIECE 43 0 2
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
SQL> create table test.test as select * from all_objects ;
Table created.
SQL> insert into test.test select * from test.test ;
49840 rows created.
SQL> /
99680 rows created.
SQL> /
199360 rows created.
SQL> commit;
Commit complete.
SQL> drop table test.test ;
Table dropped.
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 .1 0 1
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL> alter system switch logfile ;
System altered.
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 2.3 0 2
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL> select owner,object_name,ts_name from dba_recyclebin order by droptime ;
OWNER OBJECT_NAME
------------------------------ ------------------------------
TS_NAME
------------------------------
TEST BIN$MJyuu8o5ZxvgQAB/AQAOxA==$0
TEST
TEST BIN$MJyuu8o6ZxvgQAB/AQAOxA==$0
TEST
TEST BIN$MJyuu8pAZxvgQAB/AQAOxA==$0
TEST
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@test02 u02]$
[oracle@test02 u02]$ rman target sys/oracle@db002
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 17 05:48:31 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DB002 (DBID=908667212)
RMAN> backup database ;
Starting backup at 17-MAY-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/db002/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/db002/sysaux01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/db002/undotbs01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/db002/example01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/db002/test01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/db002/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-MAY-07
channel ORA_DISK_1: finished piece 1 at 17-MAY-07
piece handle=/u02/recovery/DB002/backupset/2007_05_17/o1_mf_nnndf_TAG20070517T054843_34pz1wmp_.bkp tag=TAG20070517T054843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:48
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-MAY-07
channel ORA_DISK_1: finished piece 1 at 17-MAY-07
piece handle=/u02/recovery/DB002/backupset/2007_05_17/o1_mf_ncsnf_TAG20070517T054843_34pz5cbx_.bkp tag=TAG20070517T054843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17-MAY-07
RMAN> exit
Recovery Manager complete.
[oracle@test02 u02]$
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 2.3 2.3 2
BACKUPPIECE 43 0 2
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
backup use rman again ;
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 2.3 2.3 2
BACKUPPIECE 86 43 4
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
SQL> 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 ;
2 3
NAME QUOTA USED RECLAIMABLE FILES
------------------------------ ---------- ---------- ----------- ----------
/u02/recovery 2147483648 1896140288 972746240 6
SQL>
[oracle@test02 u02]$ rman target sys/oracle@db002
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 17 06:06:45 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DB002 (DBID=908667212)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
..............................................
archive log filename=/u02/recovery/DB002/archivelog/2007_05_17/o1_mf_1_27_34px7p7g_.arc recid=26 stamp=622790264
Crosschecked 26 objects
RMAN>
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
specification does not match any archive log in the recovery catalog
RMAN> delete noprompt obsolete ;
delete expired backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN> exit
Recovery Manager complete.
[oracle@test02 u02]$
SQL> 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 ; 2 3
NAME QUOTA USED RECLAIMABLE FILES
------------------------------ ---------- ---------- ----------- ----------
/u02/recovery 2147483648 923394048 0 2
SQL>
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 0 0 0
BACKUPPIECE 43 0 2
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
相关阅读:
- Oracle常用命令集 (january, 2009-3-10)
- ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务_焦头烂额一整天 (january, 2009-3-24)
- ORA-28547的解决之道 (january, 2009-3-25)
- 如何关闭Windows Vista系统中的UAC (january, 2009-4-03)
- install oracle 10g2 client under Vista environment (january, 2009-4-03)
- 删除Oracle数据库10g垃圾表的最新方法 (january, 2009-4-21)
- Oracle的同义词(synonyms)总结 (january, 2009-4-22)
- Oracle 学习笔记 (january, 2009-4-23)
- Ora-12638解决之道 (january, 2009-4-27)
- EMAIL (january, 2009-5-08)
TAG: quest
标题搜索
日历
|
|||||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
1 | 2 | 3 | 4 | ||||||
5 | 6 | 7 | 8 | 9 | 10 | 11 | |||
12 | 13 | 14 | 15 | 16 | 17 | 18 | |||
19 | 20 | 21 | 22 | 23 | 24 | 25 | |||
26 | 27 | 28 | 29 | 30 | 31 |
我的存档
数据统计
- 访问量: 34491
- 日志数: 52
- 图片数: 4
- 建立时间: 2007-09-19
- 更新时间: 2010-06-08