配置、监控闪回恢复区

来源：互联网发布：ubuntu 共享 usb 网络编辑：程序博客网时间：2024/04/27 17:25

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/19250651

闪回恢复区一般在生产上很少开启，用在测试库比较多。

1、开启闪回恢复区
sys@OCM> alter system set db_recovery_file_dest='/flash';

System altered.

sys@OCM> alter system set db_recovery_file_dest_size=230M;

System altered.

sys@OCM> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@OCM> startup mount;
ORACLE instance started.

Total System Global Area 1006809088 bytes
Fixed Size 2233520 bytes
Variable Size 478153552 bytes
Database Buffers 419430400 bytes
Redo Buffers 106991616 bytes
Database mounted.

sys@OCM> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

sys@OCM> alter database flashback on;

Database altered.

sys@OCM> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

sys@OCM> alter database open;

Database altered.

2、闪回恢复区中的文件分成两类：永久文件和临时文件
（1）永久文件
控制文件
在线联机重做文件
（2）临时文件
归档重做日志文件
闪回日志
控件文件自动备份
数据文件副本
RMAN备份集
RMAN文件

3、监控闪回恢复区
（1）告警日志
ORA-19815: WARNING: db_recovery_file_dest_size of 241172480 bytes is 94.24% used, and has 13891072 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.
************************************************************************

（2）查v$recovery_file_dest视图来了解闪回恢复区的位置和最大的大小

sys@OCM> col name for a20
sys@OCM> select * from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ----------- ---------- ----------------- ---------------
/flash 241172480 227612160 0 132

（3）查v$flash_recovery_area_usage确定闪回恢复区的文件使用明细
sys@OCM> 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 7.28 0 116
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 86.96 0 2
FOREIGN ARCHIVED LOG 0 0 0

（3）查dba_outstanding_alerts了解空间的使用情况和建议
sys@OCM> select * from dba_outstanding_alerts;

SEQUENCE_ID REASON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
----------- ---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ----------------------------------------------------------------
REASON
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TIME_SUGGESTED CREATION_TIME
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
SUGGESTED_ACTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ADVISOR_NAME METRIC_VALUE MESSAGE_TYPE MESSAGE_GROUP MESSAGE_LEVEL HOSTING_CLIENT_ID MODULE_ID PROCESS_ID HOST_ID HOST_NW_ADDR INSTANCE_NAME INSTANCE_NUMBER USER_ID
------------------------------ ------------ ------------ ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- --------------- ------------------------------
EXECUTION_CONTEXT_ID ERROR_INSTANCE_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------
1019 123 RECOVERY AREA RECOVERY AREA
db_recovery_file_dest_size of 220200960 bytes is 97.88% used and has 4659712 remaining bytes available.
15-FEB-14 07.12.19.285810 PM +08:00 15-FEB-14 07.09.21.216429 PM +08:00
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. Backup files to tape using RMAN BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter. 4. Delete unnecessary files using RMAN DELETE command. If OS command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.
97 Warning Space 1 SERVER MANAGEABILITY:kra "ocm"."ocm" mydb 192.168.248.129 ocm 1
1019-0

4、闪回可重用的空间
（1）BACKUP PIECE/IMAGE COPY 结合RMAN的保留策略会被重用。
（2）FLASHBACK LOG 结合参数db_flashback_retention_target（这个参数其实只是其中一个指标而已,本质还是要看你的闪回区是不是足够大）

1 0