4G的分区,包括ORACLE程序文件,各种数据库文件。最初安装的时候本打算把数据库文件与程序文件分开来,放在另外的分区的,但是不知哪里没注意,与程序放到同一个地方了。
运行了一段时间后终于把空间填满了(其实主要还是在启动ARCHIVELOG模式后造成的,因为真正的数据很少,可以忽略不计)。
[oracle@localhost db_1]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda1 7.8G 3.6G 3.9G 49% /
tmpfs 506M 0 506M 0% /dev/shm
/dev/hdb1 3.8G 3.6G 248K 100% /u01
/dev/hdb2 4.2G 1.1G 2.9G 27% /u02
[oracle@localhost db_1]$
/u01即是数据库所在分区。
其实昨天就看到警告日志中就有很多Archival Error的报告,这里摘录一些:
ORACLE Instance orcl - Archival Error
Thu Oct 21 18:00:26 2010
ORA-16038: log 2 sequence# 36 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02_2.log'
Thu Oct 21 18:00:26 2010
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc0_2604.trc:
ORA-16038: log 2 sequence# 36 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02_2.log'
Thu Oct 21 18:01:06 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 36 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
以上是最早出现的Archival Error。
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_10: '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc' (error 19502)
(orcl)
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc
Thu Oct 21 18:06:06 2010
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc0_2604.trc:
ORA-19502: write error on file "/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc", blockno 4097 (blocksize=512)
ORA-27072: File I/O error
Linux Error: 2: No such file or directory
Additional information: 4
Additional information: 4097
Additional information: 781824
ORA-19502: write error on file "/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc", blockno 4097 (blocksize=512)
ARC0: I/O error 19502 archiving log 2 to '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc'
ARC0: Failed to archive thread 1 sequence 36 (19502)
ARCH: Archival stopped, error occurred. Will continue retrying
另一段典型的错误信息,跟删除一个以前的归档日志有关。
虽然有错误信息,但并不影响操作,所以继续观察,没有处理。
到今天晚上,发现sys已经无法登录了。
[oracle@localhost db_1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 10月 22 18:55:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux Error: 28: No space left on device
ORA-01075: you are currently logged on
Enter user-name: /
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
[oracle@localhost db_1]$
但普通用户可以登录,虽然没什么作用。
[oracle@localhost db_1]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 10月 22 19:24:00 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
hr$orcl@localhost.localdomain SQL>
其实记录这个问题,对于真正的数据库似乎没有什么借鉴意义,分析原因有几点:
1.对于数据库将来可能达到的空间占用情况在设计数据库时就有预期。
2.对于由于归档日志造成空间占用,可以通过备份归档日志或自动删除过期归档的方式解决。
3.如果确实发现空间不足的迹象,应该提早解决而不会等到真正没有空间的情况出现(否则这个DBA早被开了吧)
所以说,这篇日志没啥实际意义,但是就算看看正确做法的另一面吧。
对于解决方法,目前想到的有:
1.转移归档日志的存放位置。
2.增加分区所在空间。
为了学习ORACLE的目的,采用第一种。
删除部分trc文件,腾出空间
[oracle@localhost bdump]$ find -ctime +3 -delete
登录数据库,查看数据库状态:
[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 10月 22 22:55:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys$orcl@localhost.localdomain SQL> select status from v$instance;
STATUS
------------------------------------
OPEN
sys$orcl@localhost.localdomain SQL>
本以为数据库会down掉的或是什么,没想到这么坚强:)
显示当前的闪存恢复区位置:
sys$orcl@localhost.localdomain SQL> select name,value
2 from v$system_parameter
3 where name like '%recovery%'
4 /
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
db_recovery_file_dest
/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size
2147483648
recovery_parallelism
0
修改到新的位置:
sys$orcl@localhost.localdomain SQL> alter system set db_recovery_file_dest='/u02/flash_recovery_area' scope=both;
System altered.
过一会儿,就可以看到新的位置下生成了归档日志,而之前生成的归档日志仍保存在原来位置。
sys$orcl@localhost.localdomain SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_14/o1_mf_1_21_6cg38gxn_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_15/o1_mf_1_22_6cjmf829_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_23_6ckwlzct_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_24_6cmc9f25_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_25_6cmcqpos_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_26_6cmdkk06_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_17/o1_mf_1_27_6cp2v3xs_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_18/o1_mf_1_28_6cqf8r6w_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_18/o1_mf_1_29_6cqfvox3_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_18/o1_mf_1_30_6cqgcg1f_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_31_6cxcwqs7_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_32_6cxdm44z_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_33_6cxrnky5_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_34_6cxtb97v_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_35_6cxwpgg0_.arc
/u02/flash_recovery_area/ORCL/archivelog/2010_10_22/o1_mf_1_36_6d3b96mn_.arc
/u02/flash_recovery_area/ORCL/archivelog/2010_10_22/o1_mf_1_37_6d3b9crg_.arc
17 rows selected.
系统目录下也可以确认:
[oracle@localhost 2010_10_22]$ pwd
/u02/flash_recovery_area/ORCL/archivelog/2010_10_22
[oracle@localhost 2010_10_22]$ ls -lt
total 100760
-rw-r----- 1 oracle oinstall 51229184 Oct 22 23:09 o1_mf_1_37_6d3b9crg_.arc
-rw-r----- 1 oracle oinstall 51821056 Oct 22 23:09 o1_mf_1_36_6d3b96mn_.arc
[oracle@localhost 2010_10_22]$
运行了一段时间后终于把空间填满了(其实主要还是在启动ARCHIVELOG模式后造成的,因为真正的数据很少,可以忽略不计)。
[oracle@localhost db_1]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda1 7.8G 3.6G 3.9G 49% /
tmpfs 506M 0 506M 0% /dev/shm
/dev/hdb1 3.8G 3.6G 248K 100% /u01
/dev/hdb2 4.2G 1.1G 2.9G 27% /u02
[oracle@localhost db_1]$
/u01即是数据库所在分区。
其实昨天就看到警告日志中就有很多Archival Error的报告,这里摘录一些:
ORACLE Instance orcl - Archival Error
Thu Oct 21 18:00:26 2010
ORA-16038: log 2 sequence# 36 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02_2.log'
Thu Oct 21 18:00:26 2010
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc0_2604.trc:
ORA-16038: log 2 sequence# 36 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02_2.log'
Thu Oct 21 18:01:06 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 36 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
以上是最早出现的Archival Error。
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_10: '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc' (error 19502)
(orcl)
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc
Thu Oct 21 18:06:06 2010
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc0_2604.trc:
ORA-19502: write error on file "/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc", blockno 4097 (blocksize=512)
ORA-27072: File I/O error
Linux Error: 2: No such file or directory
Additional information: 4
Additional information: 4097
Additional information: 781824
ORA-19502: write error on file "/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc", blockno 4097 (blocksize=512)
ARC0: I/O error 19502 archiving log 2 to '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_21/o1_mf_1_36_6d044gwr_.arc'
ARC0: Failed to archive thread 1 sequence 36 (19502)
ARCH: Archival stopped, error occurred. Will continue retrying
另一段典型的错误信息,跟删除一个以前的归档日志有关。
虽然有错误信息,但并不影响操作,所以继续观察,没有处理。
到今天晚上,发现sys已经无法登录了。
[oracle@localhost db_1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 10月 22 18:55:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux Error: 28: No space left on device
ORA-01075: you are currently logged on
Enter user-name: /
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
[oracle@localhost db_1]$
但普通用户可以登录,虽然没什么作用。
[oracle@localhost db_1]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 10月 22 19:24:00 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
hr$orcl@localhost.localdomain SQL>
其实记录这个问题,对于真正的数据库似乎没有什么借鉴意义,分析原因有几点:
1.对于数据库将来可能达到的空间占用情况在设计数据库时就有预期。
2.对于由于归档日志造成空间占用,可以通过备份归档日志或自动删除过期归档的方式解决。
3.如果确实发现空间不足的迹象,应该提早解决而不会等到真正没有空间的情况出现(否则这个DBA早被开了吧)
所以说,这篇日志没啥实际意义,但是就算看看正确做法的另一面吧。
对于解决方法,目前想到的有:
1.转移归档日志的存放位置。
2.增加分区所在空间。
为了学习ORACLE的目的,采用第一种。
删除部分trc文件,腾出空间
[oracle@localhost bdump]$ find -ctime +3 -delete
登录数据库,查看数据库状态:
[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 10月 22 22:55:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys$orcl@localhost.localdomain SQL> select status from v$instance;
STATUS
------------------------------------
OPEN
sys$orcl@localhost.localdomain SQL>
本以为数据库会down掉的或是什么,没想到这么坚强:)
显示当前的闪存恢复区位置:
sys$orcl@localhost.localdomain SQL> select name,value
2 from v$system_parameter
3 where name like '%recovery%'
4 /
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
db_recovery_file_dest
/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size
2147483648
recovery_parallelism
0
修改到新的位置:
sys$orcl@localhost.localdomain SQL> alter system set db_recovery_file_dest='/u02/flash_recovery_area' scope=both;
System altered.
过一会儿,就可以看到新的位置下生成了归档日志,而之前生成的归档日志仍保存在原来位置。
sys$orcl@localhost.localdomain SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_14/o1_mf_1_21_6cg38gxn_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_15/o1_mf_1_22_6cjmf829_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_23_6ckwlzct_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_24_6cmc9f25_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_25_6cmcqpos_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_16/o1_mf_1_26_6cmdkk06_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_17/o1_mf_1_27_6cp2v3xs_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_18/o1_mf_1_28_6cqf8r6w_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_18/o1_mf_1_29_6cqfvox3_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_18/o1_mf_1_30_6cqgcg1f_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_31_6cxcwqs7_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_32_6cxdm44z_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_33_6cxrnky5_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_34_6cxtb97v_.arc
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_10_20/o1_mf_1_35_6cxwpgg0_.arc
/u02/flash_recovery_area/ORCL/archivelog/2010_10_22/o1_mf_1_36_6d3b96mn_.arc
/u02/flash_recovery_area/ORCL/archivelog/2010_10_22/o1_mf_1_37_6d3b9crg_.arc
17 rows selected.
系统目录下也可以确认:
[oracle@localhost 2010_10_22]$ pwd
/u02/flash_recovery_area/ORCL/archivelog/2010_10_22
[oracle@localhost 2010_10_22]$ ls -lt
total 100760
-rw-r----- 1 oracle oinstall 51229184 Oct 22 23:09 o1_mf_1_37_6d3b9crg_.arc
-rw-r----- 1 oracle oinstall 51821056 Oct 22 23:09 o1_mf_1_36_6d3b96mn_.arc
[oracle@localhost 2010_10_22]$
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24131851/viewspace-676590/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24131851/viewspace-676590/