168大数据

标题: 记一次RAC归档满及DB_RECOVERY_FILE_DEST_SIZE理解 [打印本页]

作者: dbadoudou 时间: 2014-9-1 15:02
标题: 记一次RAC归档满及DB_RECOVERY_FILE_DEST_SIZE理解

记一次RAC归档满及DB_RECOVERY_FILE_DEST_SIZE理解

一、RAC归档满及处理
[root@rac1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME          TARGET  STATE       SERVER                STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
            ONLINE  ONLINE    rac1
            ONLINE  ONLINE    rac2
ora.DATA.dg
            ONLINE  ONLINE    rac1
            ONLINE  ONLINE    rac2
ora.LISTENER.lsnr
            ONLINE  ONLINE    rac1
            ONLINE  ONLINE    rac2
ora.asm
            ONLINE  ONLINE    rac1                   Started
            ONLINE  ONLINE    rac2                   Started
ora.gsd
            OFFLINE OFFLINE    rac1
            OFFLINE OFFLINE    rac2
ora.net1.network
            ONLINE  ONLINE    rac1
            ONLINE  ONLINE    rac2
ora.ons
            ONLINE  ONLINE    rac1
            ONLINE  ONLINE    rac2
ora.registry.acfs
            ONLINE  ONLINE    rac1
            ONLINE  ONLINE    rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
   1       ONLINE  ONLINE    rac2
ora.cvu
   1       ONLINE  ONLINE    rac2
ora.doudou.db
   1       ONLINE  INTERMEDIATE rac1                   Stuck Archiver
   2       ONLINE  INTERMEDIATE rac2                   Stuck Archiver
ora.oc4j
   1       ONLINE  ONLINE    rac2
ora.rac1.vip
   1       ONLINE  ONLINE    rac1
ora.rac2.vip
   1       ONLINE  ONLINE    rac2
ora.scan1.vip
   1       ONLINE  ONLINE    rac2
--初步推断，归档满了

查看alert.log
ARC2: Error 19809 Creating archive log file to '+DATA'
Errors in file /oracle/app/oracle/diag/rdbms/doudou/doudou1/trace/doudou1_arc3_7838.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 8388608000 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.
--从alert.log来看，确实归档满了，而且还给出了解决办法。

解决办法：（测试库，所以我就暴力处理了）
[oracle@rac1 ~]$ rman target /
RMAN> delete archivelog all;

二、DB_RECOVERY_FILE_DEST_SIZE理解
SYS@doudou1> archive log list;
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    279
Next log sequence to archive 279
Current log sequence          280

SYS@doudou2> archive log list;
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    188
Next log sequence to archive 188
Current log sequence          189

SYS@doudou1> show parameter DB_RECOVERY_FILE_DEST

NAME                               TYPE                VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest             string                +DATA
db_recovery_file_dest_size          big integer          8000M

ASMCMD> du +data/doudou/archivelog
Used_MB    Mirror_used_MB
6816             6816
--为什么我们的归档目录没有使用到8000M，但是不能使用了呢？是不是有什么错误呢，其实这是正确的，只是自己理解错误而已。
下面是官档对DB_RECOVERY_FILE_DEST_SIZE的解释
The DB_RECOVERY_FILE_DEST_SIZE setting does not include the following kinds of disk overhead:
Block 0 or the operating system block header of each Oracle Database file is not included.
Allow an extra 10% for this data when computing the actual disk usage required for the fast recovery area.
DB_RECOVERY_FILE_DEST_SIZE does not indicate the real size occupied on disk when the underlying file system is mirrored, compressed, or affected by overhead not known to Oracle Database.
For example, if the recovery area is on a two-way mirrored ASM disk group, each file of x bytes occupies 2x bytes on the ASM disk group. In this case, set DB_RECOVERY_FILE_DEST_SIZE to no more than half the size of the disks for the ASM disk group. Likewise, when using a three-way mirrored ASM disk group, DB_RECOVERY_FILE_DEST_SIZE must be no greater than one third the size of the disks in the disk group, and so on.

总结：
1.DB_RECOVERY_FILE_DEST_SIZE这个参数不是OS限制参数，所以不要以OS目录的思想来限制DB_RECOVERY_FILE_DEST_SIZE的大小，另一点就是DB_RECOVERY_FILE_DEST_SIZE参数会留有10%用于fast recovery area。
2.RAC归档量的使用是跟节点数有关的，例如有2个节点的RAC，那归档量将是2个节点的和，也就是每个节点只能使用DB_RECOVERY_FILE_DEST_SIZE的一半，如果是多个节点则是N/A（N DB_RECOVERY_FILE_DEST_SIZE可用值，A节点数）。

作者: dbdream 时间: 2014-9-4 17:57
RAC归档量的使用是跟节点数有关的，例如有2个节点的RAC，那归档量将是2个节点的和，也就是每个节点只能使用DB_RECOVERY_FILE_DEST_SIZE的一半，
不是这样的吧，比如业务都跑一个节点，这样归档量两个节点是不一样的，难道业务节点只能用一半的空间？

作者: dbdream 时间: 2014-9-4 18:01
闪回恢复区的使用率需要在数据库中查询的，比如你在OS上收到rm掉归档日志，数据库是不知道的，并不会立即回收闪回恢复区的空间。可以通过V$RECOVERY_AREA_USAGE和V$FLASH_RECOVERY_AREA_USAGE视图查看闪回区的使用情况。

作者: dbadoudou 时间: 2014-9-9 08:41

dbdream 发表于 2014-9-4 17:57
RAC归档量的使用是跟节点数有关的，例如有2个节点的RAC，那归档量将是2个节点的和，也就是每个节点只能使用 ...

只是提示你要考虑，共享目录有多个节点在使用归档。并不是完全的。这段简述，是官档说的。不要理解偏了

欢迎光临 168大数据 (http://www.bi168.cn/)