业务不能连接，检查集群状态，发现节点二处于“stuck archive”状态：

[grid@rac1 /home/grid]$crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE  ONLINE       rac1
ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
ONLINE  ONLINE       rac1
ONLINE  ONLINE       rac2
ora.VOTE.dg
ONLINE  ONLINE       rac1
ONLINE  ONLINE       rac2
ora.asm
ONLINE  ONLINE       rac1                     Started
ONLINE  ONLINE       rac2                     Started
ora.gsd
ONLINE  OFFLINE      rac1
ONLINE  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.oc4j
1        ONLINE  ONLINE       rac2
ora.orcl.db
1        ONLINE  ONLINE       rac1                     Open
2        ONLINE  ONLINE       rac2                     stuck archive
ora.rac1.vip
1        ONLINE  ONLINE       rac1
ora.rac2.vip
1        ONLINE  ONLINE       rac2
ora.scan1.vip
1        ONLINE  ONLINE       rac2

首先想到归档空间满了，检查磁盘组空间，有足够的剩余空间：

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   5730304  4304320                0         4304320              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     15360    14957                0           14957              0             Y  VOTE/
ASMCMD> exit

检查，实例状态正常，

SQL> select instance_name,status from gv$instance;
 
INSTANCE_NAME    STATUS
 
----------------------------------------------------
 
orcl2            OPEN
orcl1            OPEN

检查数据库alert日志，报不能创建归档：

************************************************************************
ARC1: Error 19809 Creating archive log file to '+DATA'Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_arc3_29884666.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 629145600000 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
5. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,then consider changing RMAN ARCHIVELOG DELETION POLICY.
6. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
7. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
8. 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.
************************************************************************

检查归档配置，发现配置到“db_recovery_file_dest_size”闪回空间里了，由于近期归档增量较多，导致空间达到500G，超过db_recovery_file_dest_size设置的值。

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     80859
Next log sequence to archive   80859
Current log sequence           80861
 
INSTANCE_NAME    STATUS
 
---
 
orcl2            OPEN
orcl1            OPEN
 
SQL> show parameter db_r
 
NAME                                 TYPE        VALUE
 
---
 
db_recovery_file_dest                string      +DATA
db_recovery_file_dest_size           big integer 500000M
db_recycle_cache_size                big integer 0
dbwr_io_slaves                       integer     0
SQL> exit
 
SQL> alter system set db_recovery_file_dest_size=600000M
 
SQL> select flashback_on from v$database;
 
-------------------- 
FLASHBACK_ON
 
YES
 
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
 
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .01                         0               1
REDO LOG                            .51                         0               6
ARCHIVED LOG                      83.74                         0            1383
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> alter system set log_archive_dest_1='location=+DATA';
 
System altered.

调整db_recovery_file_dest_size=600000M后业务恢复，最后为了防止后续再次发生空间满，把归档路径设置成'+DATA'。

ITPub博客

rac使用默认闪回区归档空间满

最新文章