db_recovery_file_dest_size: user-specified limit

db_recovery_file_dest_size: user-specified limit

节点告警信息：

This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup

原因 

每次rman 备份都是备份到archdg 闪回盘 总共20G  上面说的意思是说用了闪回去53%的空间，告警我

做实验 

1 自动rman备份 看 是不是备份到闪回去  猜想 可能是asm的omf 

2 手动自动 +data/数据库名字/arch路径 看看 查看闪回去占用情况

参考

Hi expert,
in my alert log i'm seeing this message. This after I add on 1gb to my recovery file dest.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G scope = both;

this is the message:

db_recovery_file_dest_size of 2048 MB is 49.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

what's it? my istance is in archivelog, but the path /oradata/data07/archive is empty

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/data07/archive
Oldest online log sequence     7120
Next log sequence to archive   7122
Current log sequence           7122
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                49.54                         0              16
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.

SQL> 
SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ----------------------------
db_recovery_file_dest                string                           /oradata/data01/flash_recov
                                                                      ery_area
db_recovery_file_dest_size           big integer                      2G

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_flashback_retention_target        integer                          1440

many thanks for all suggest.

SQL> col name format a30

SQL> set pagesize 6000

SQL> set linesize 500

SQL> col name format a30

SQL> col value format a35

SQL> select name, value from v$parameter where name like 'db_recovery_%';

NAME                           VALUE

------------------------------ -----------------------------------

db_recovery_file_dest          +ARCHDG

db_recovery_file_dest_size      21474836480

select flashback_on from v$database;

 select name,
    floor(space_limit/1024/1024) "Size_MB",
  ceil(space_used/1024/1024) "Used_MB"
   from v$recovery_file_dest
    order by name

  ORA-19527与ORA-00312和db_recovery_file_dest_size of 2048 MB is 0.00% used 

Errors in file d:\oracle\product\10.2.0\admin\billing\bdump\billing_mrp0_2216.trc:

ORA- 19527: 必须重命名物理备用重做日志

ORA- 00312: 联机日志 1 线程 1: 'E:\CZ\BILLING\REDO01.LOG'

Clearing online redo logfile 1 complete

Media Recovery Waiting for thread 1 sequence  13364

Tue Aug 21 13:41:52  2012

Completed: alter database recover managed standby database disconnect from session

Tue Aug 21 13:55:58  2012

db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

这是10g以后，oracle为了加快swtichover的速度，在can become a primary之前就去clear the online logfiles了，而如果没有设置log_file_name_convert，这个时候oracle可能就不认识哪怕是你copy过来的一模一样的logfile了

这个说法也在taobao dba team的blog中得到证实:

http://www.taobaodba.com/html/80_oracle10g_standby_ora_00367_ora_19527.html

解决的办法就是设置log_file_name_convert参数，其实这个standby库建立的时候就是从primary冷备copy过来的文件，路径也完全一样。

alter system set log_file_name_convert='/u02/oradata/felixdb/','/u02/oradata/felixdb/' scope=spfile;

当设置完成，重启standby，apply日志以后，看到后台中果然可以clear了online logfiles，也就没有了问题

db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

就是警告而已，不过如果你用archivelog mode，又没有指定归档路径，就会自动归档到
show parameter db_recovery_file_dest，所以你要监控select * from v$flash_recovery_area_usage

节点告警信息：

This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup

原因 

每次rman 备份都是备份到archdg 闪回盘 总共20G  上面说的意思是说用了闪回去53%的空间，告警我

做实验 

1 自动rman备份 看 是不是备份到闪回去  猜想 可能是asm的omf 

2 手动自动 +data/数据库名字/arch路径 看看 查看闪回去占用情况

参考

Hi expert,
in my alert log i'm seeing this message. This after I add on 1gb to my recovery file dest.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G scope = both;

this is the message:

db_recovery_file_dest_size of 2048 MB is 49.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

what's it? my istance is in archivelog, but the path /oradata/data07/archive is empty

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/data07/archive
Oldest online log sequence     7120
Next log sequence to archive   7122
Current log sequence           7122
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                49.54                         0              16
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.

SQL> 
SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ----------------------------
db_recovery_file_dest                string                           /oradata/data01/flash_recov
                                                                      ery_area
db_recovery_file_dest_size           big integer                      2G

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_flashback_retention_target        integer                          1440

many thanks for all suggest.

SQL> col name format a30

SQL> set pagesize 6000

SQL> set linesize 500

SQL> col name format a30

SQL> col value format a35

SQL> select name, value from v$parameter where name like 'db_recovery_%';

NAME                           VALUE

------------------------------ -----------------------------------

db_recovery_file_dest          +ARCHDG

db_recovery_file_dest_size      21474836480

select flashback_on from v$database;

 select name,
    floor(space_limit/1024/1024) "Size_MB",
  ceil(space_used/1024/1024) "Used_MB"
   from v$recovery_file_dest
    order by name

  ORA-19527与ORA-00312和db_recovery_file_dest_size of 2048 MB is 0.00% used  2012-08-21 14:42:19

分类： Linux

Tue Aug 21 13:41:52  2012

Errors in file d:\oracle\product\10.2.0\admin\billing\bdump\billing_mrp0_2216.trc:

ORA- 19527: 必须重命名物理备用重做日志

ORA- 00312: 联机日志 1 线程 1: 'E:\CZ\BILLING\REDO01.LOG'

Clearing online redo logfile 1 complete

Media Recovery Waiting for thread 1 sequence  13364

Tue Aug 21 13:41:52  2012

Completed: alter database recover managed standby database disconnect from session

Tue Aug 21 13:55:58  2012

db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

这是 10g以后， oracle为了加快swtichover的速度，在can become a primary之前就去clear the online logfiles了，而如果没有设置log_file_name_convert，这个时候oracle可能就不认识哪怕是你copy过来的一模一样的logfile了

   这个说法也在taobao dba team的blog中得到证实:

    http://www.taobaodba.com/html/80_oracle10g_standby_ora_00367_ora_19527.html

   解决的办法就是设置log_file_name_convert参数，其实这个standby库建立的时候就是从primary冷备copy过来的文件，路径也完全一样。

   alter system set log_file_name_convert='/u02/oradata/felixdb/','/u02/oradata/felixdb/' scope=spfile;

   当设置完成，重启standby，apply日志以后，看到后台中果然可以clear了online logfiles，也就没有了问题

db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

就是警告而已，不过如果你用archivelog mode，又没有指定归档路径，就会自动归档到
show parameter db_recovery_file_dest，所以你要监控select * from v$flash_recovery_area_usage