JNB
rss

skin by 이글루스

오라클

로그인시 ORA-00257 발생 (ORA-19815: WARNING: db_recovery_file_dest_size) 14.05.27 20:05
11g에서 발생한 현상입니다.




#######################################################

ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 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.

************************************************************************

ARC2: Error 19809 Creating archive log file to 'D:\ORACLE\FLASH_RECOVERY_AREA\DBCS11G\ARCHIVELOG\2012_09_19\O1_MF_1_410_%U_.ARC'

Wed Sep 19 09:56:42 2012

Errors in file D:\ORACLE\diag\rdbms\dbcs11g\dbcs11g\trace\dbcs11g_arc3_3412.trc:




#######################################################

alert.log의 내용은 위와 같다.




db_recovery_file_dest_size의 경로를 삭제 했는데도 로그인할 때 아래와 같은 에러가 발생한다.

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




지우긴 지웠는데 컨트롤 파일에 갱신이 되지 않은것 같다.




c:\> rman

RMAN> connect target /;

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;




일단은 위와 같이 처리하면 된다.

일단은....




------------추가내용

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 100359680 bytes disk space from 8589934592 limit
ARC0: Error 19809 Creating archive log file to '/oracle/flash_recovery_area/ORCL/archivelog/2007_10_22/o1_mf_1_286_10_.arc'



************************************************************************

You have following choices to free up space from flash 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.
************************************************************************





1. 아카이브 로그 리스트를 확인한다.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     285
Next log sequence to archive   287
Current log sequence           287



2. flash_recovey_area가 셋팅되어 있는 것을 확인한다.



SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 8G
recovery_parallelism                 integer     0



3. 셋팅이 되어 있다면 지금 어느 정도 차여 있는지를 확인해야 한다.



SQL> select * from v$recovery_file_dest ;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oracle/flash_recovery_area
8589934592 8572021760                 0             261


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                99.79                         0             261
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.



현재 보면 archivelog 가 99.79퍼센트가 차여 있는 것을 볼 수 있다.



4. 방법은 flash 메모리를 사용하지 않게 설정하는 방법인데, 이 부분은 알게 되면 다시 업뎃하겠다.

    지금은 기존의 경로에서 파일을 삭제하고 그것을 동기화 시켜 주는 방법을 기술하겠다.



    기존의 파일을 삭제하기 전에 그냥 rman으로 삭제하면 되는데..

    본인은 실수로 파일 시스템을 삭제를 해서 동기화 시켰다.



   1) rman 접속

OS>RMAN

RMAN> connect target

connected to target database: ORCL (DBID=1157783612)
using target database control file instead of recovery catalog



  2) 아카이브 파일 리스트 보기



RMAN> list archivelog like '%.arc';





  3) 아카이브 파일 삭제

RMAN> delete archivelog like '2007_08%.arc' ; - 8월달 데이타만 지울 경우



5. 만약 파일시스템을 먼저 지워서 동기화가 되지 않은 경우에는 다음과 같이 하면 되면 된다.



RMAN> delete archivelog like '/oracle/flash_recovery_area/ORCL/archivelog/2007_08_10%.arc' ;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=519 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
2       1    19      A 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_19_3cr5ooxy_.arc
4       1    20      A 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_20_3cr5opqf_.arc
6       1    21      A 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_21_3cr5osw4_.arc
8       1    22      A 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_22_3cr7p0rf_.arc
10      1    23      A 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_23_3cr7p1lb_.arc
12      1    24      A 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_24_3cr7pc1h_.arc

Do you really want to delete the above objects (enter YES or NO)? YES

RMAN-06207: WARNING: 6 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog      /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_19_3cr5ooxy_.arc
RMAN-06214: Archivelog      /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_20_3cr5opqf_.arc
RMAN-06214: Archivelog      /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_21_3cr5osw4_.arc
RMAN-06214: Archivelog      /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_22_3cr7p0rf_.arc
RMAN-06214: Archivelog      /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_23_3cr7p1lb_.arc
RMAN-06214: Archivelog      /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_24_3cr7pc1h_.arc


지웠는데 이렇게 메세지가 나오면 OS상에 파일은 지워졌지만 실제로 목록에는 지워지지 않아서 DB는 지워지지 않은 걸로 인식한다.

그래서 동기화 시켜줘야 되는데 동기화 시켜주는 것은 다음과 같다.



  1) crosscheck  - (파일이 실제로 있는지 확인하는 명령어 사용방법은 아래와 같다. )



RMAN> crosscheck copy of archivelog all ;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=519 devtype=DISK
validation failed for archived log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_19_3cr5ooxy_.arc recid=2 stamp=630265302
validation failed for archived log

~~

archive log filename=/oradata/archive/arc_1_285_630002876.arc recid=528 stamp=636593416
validation succeeded for archived log
archive log filename=/oradata/archive/arc_1_286_630002876.arc recid=529 stamp=636651765
Crosschecked 528 objects



  2) expire된 파일은 삭제한다. (파일이 실제로 맞지 않는 부분에 대하여는 삭제해 주는 명령어)



RMAN> delete expired copy of archivelog all ;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=519 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
2       1    19      X 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_19_3cr5ooxy_.arc
4       1    20      X 10-AUG-07 /oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_20_3cr5opqf_.arc
3       1    20      X 10-AUG-07 /oradata/archivearc_1_20_630002876.arc

~~

519     1    279     X 19-OCT-07 /oradata/archive/arc_1_279_630002876.arc
521     1    281     X 20-OCT-07 /oradata/archive/arc_1_281_630002876.arc

Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2007_08_10/o1_mf_1_19_3cr5ooxy_.arc recid=2 stamp=630265302
deleted archive log

~~

deleted archive log
archive log filename=/oradata/archive/arc_1_281_630002876.arc recid=521 stamp=636507001
Deleted 441 EXPIRED objects



- DELETE OBSOLETE : 불일치하는 파일을 삭제하는 명령어..(위에 것을 써도 되지만 아랫것도 가능하다)



  3) 실제로 삭제되어서 플래쉬 메모리가 확보되었다.



SQL> select * from v$recovery_file_dest ;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oracle/flash_recovery_area
8589934592 2059579904                 0              81



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                23.98                         0              81
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0




[출처] 로그인시 ORA-00257 발생 (ORA-19815: WARNING: db_recovery_file_dest_size)|작성자 농사


        

    
Copyright 1999-2018 Zeroboard / skin by JY