问题描述:
好不容易新安了12c rac,测试rman功能时,需要开启归档,开完归档发现数据库性能很差,在recover database时甚至卡在那不会结束,dave的一句‘看看闪回区’提醒了我,进到闪回区,果然15G的闪回区已经将近满了!本来以为测试用,懒得修改归档路径,直接把日志清了,数据库暂时回归正常了;接下来向数据库导入4G的测试数据,arch再次崩溃,会话无法来接,报错如下:
Imp会话被hang住,又去查了一下归档,4.5g的归档,15g的空间,不至于是归档的问题呀,查看一下闪回区大小:
SQL> select file_type,percent_space_used from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED
----------------------- ------------------
CONTROL FILE .4
REDO LOG .4
ARCHIVED LOG 96.32
BACKUP PIECE .4
IMAGE COPY 0
FLASHBACK LOG 0
FOREIGN ARCHIVED LOG 0
AUXILIARY DATAFILE COPY 0
8 rows selected.
原来,这里才是罪魁祸首,查看一下闪回区大小
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 4785M
果然4500/4785将近98%,看来是oracle归档虽然放在足够大的闪回区,闪回区有参数的限制,修改一下归档路径,删除原来闪回区中的归档,一切OK,总结下一些命令:
RMAN删除归档日志:
检查所有archivelog
crosscheck archivelog all;
删除废弃的日志
delete expired archivelog all;
删除7天前日志
delete archivelog all completed before ‘sysdate-7’;
甚至删除2小时前日志
delete archivelog all completed befor ‘sysdate-2/24’;
查看数据库闪回区空间使用情况
SQL> select file_type,percent_space_used from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED
----------------------- ------------------
CONTROL FILE .4
REDO LOG 4.26
ARCHIVED LOG 0
BACKUP PIECE .4
IMAGE COPY 0
FLASHBACK LOG 0
FOREIGN ARCHIVED LOG 0
AUXILIARY DATAFILE COPY 0
8 rows selected.
修改闪回区大小:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter flash;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FLASH
db_recovery_file_dest_size big integer 4785M
SQL> alter system set db_recovery_file_dest_size = 5G;
System altered.
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FLASH
db_recovery_file_dest_size big integer 5G
修改归档路径:
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch';
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.