Saturday, February 7, 2015

ORA-00257: archiver error. Connect internal only, until freed.


DB: 11gR2 RAC 2 NODE on LINUX

SQL> select name from v$database;

NAME
---------
PERFDB

This Error will occur for various reasons, let see what is causing this error in my database

Alert log message:  (Always check Alert log for this error)

ARC3: Error 19504 Creating archive log file to '+FRA'
Fri Oct 10 19:04:18 2014
Errors in file /u01/app/oracle/diag/rdbms/perfdb/PERFDB2/trace/PERFDB2_arc0_15765.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.

From my alert log it is clear that my FRA disk group got exhausted


SQL> show parameter recovery

NAME                                         TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string             +FRA
db_recovery_file_dest_size         big integer   300G
recovery_parallelism                    integer         0

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
----------        ----------
       300       3.42773438       (have enough space)

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim", NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

Type                                  % Used     % Reclaim    # Files
--------------------                ----------     ---------- ----------
CONTROL FILE                           0          0          0
REDO LOG                                  0          0          0
ARCHIVED LOG                      1.13        0         21
BACKUP PIECE                        .01          0          1
IMAGE COPY                              0          0          0
FLASHBACK LOG                       0          0          0
FOREIGN ARCHIVED LOG        0          0          0

7 rows selected.


Database PERFDB is absolutely fine and recovery dest has space but +FRA is full because of archivelogs of other database which are sharing same ASM diskgroup
Here in this cluster 4 database are running and sharing same FRA diskgroup

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   1535979    16725                0           16725              0             N       DATA/
MOUNTED  EXTERN  N  512   4096  1048576   1535979    539       0         539          0         N       FRA/
MOUNTED  EXTERN  N      512   4096  1048576       494      130                0             130              0             N      OCR/
MOUNTED  EXTERN  N      512   4096  1048576       494      131                0             131              0             N       OCRM/
MOUNTED  NORMAL  N      512   4096  1048576      1482     1194              494             120              0             Y       VOTE/



ASMCMD> pwd
+FRA/****DB/ARCHIVELOG
ASMCMD> rm -rf 2012_05*
ASMCMD> rm -rf 2012_06*
...   ……….

(OR)

If you want to delete archive logs older than ‘x’ days, then

RMAN> delete archivelog until time 'SYSDATE-X';       (deleting older than X days)

Freed some space in FRA by removing old archive log files(Take backup if this is critical db), then problem resolved for PERFDB database.

SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER NAME          TOTAL_MB    FREE_MB       USABLE_FILE_MB
------------ -------------------          ---------- -------     ----------      ---------------
           1 DATA                              1535979      16725          16725
           2 FRA                                 1535979     462046         462046
           3 OCRM                                    494          131            131
           4 OCR                                        494          130            130
           5 VOTE                                   1482         1194            120


If your Alert log says

Alert log message:

ARC2: Error 19809 Creating archive log file to '+FRADG'
Errors in file /oratrace/diag/rdbms/DB/DBSID2/trace/DBSID_arc3_29957.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 6005194752 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.
************************************************************************

Then check the below link:


0 comments:

Post a Comment

Auto Scroll Stop Scroll