场景:测试数据库的hang住问题。
长期解决的方法:定时做rman备份,删除归档日志
1.把数据库启动至mount状态
SQL> startup mount
ORACLE instance started.
2.启动数据库报错
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
3.查看alert日志位置
SQL> show parameter background_dump;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/diag/rdbms/arpdb/arpd
b/trace
4.分析alert日志
Errors in file /u01/app/diag/rdbms/arpdb/arpdb/trace/arpdb_ora_2420.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 43851448320 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
5.进入rman删除archive日志,(由于是测试环境,所以删除日志,保证数据库的正常运行)
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
***************************命令解释**************************************************************************
SYSDATA-7,表明当前的系统时间7天前,before关键字表示在7天前的归档日志,如果使用了闪回功能,也会删除闪回的数据。
同样道理,也可以删除从7天前到现在的全部日志,不过这个命令要考虑清楚,做完这个删除,最好马上进行全备份数据库
*************************************************************************************************************
6.查看当前闪回日志
SQL> set line 110
SQL> set pagesize 2000
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 2.76 0 27
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/fast_recovery_area
db_recovery_file_dest_size big integer 41820M
阅读(4958) | 评论(0) | 转发(0) |