業務不能連線，檢查叢集狀態，發現節點二處於“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'。

rac使用預設閃回區歸檔空間滿

相關文章