ORA-00257: archiver error. Connect internal only, until freed 解決方法

C:\Users\Administrator>sqlplus h2/hydeesoft@orcl

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 9月 30 10:57:46 2014

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

--系統使用者登入，檢視歸檔已用空間大小
C:\Users\Administrator>sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 9月 30 10:58:10 2014

連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Administrator\flash_rec
                                                 overy_area
db_recovery_file_dest_size           big integer 3912M
SQL> set linesize 200;
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILE
S
-------------------- ------------------ ------------------------- --------------
-
CONTROL FILE                          0                         0
0
REDO LOG                              0                         0
0
ARCHIVED LOG                       99.1                         0              9
0
BACKUP PIECE                          0                         0
0
IMAGE COPY                            0                         0
0
FLASHBACK LOG                         0                         0
0
FOREIGN ARCHIVED LOG                  0                         0
0

已選擇7行。

方法一：擴大歸檔日誌存放空間大小

SQL> alter system set db_recovery_file_dest_size=10g;

系統已更改。

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILE
S
-------------------- ------------------ ------------------------- --------------
-
CONTROL FILE                          0                         0
0
REDO LOG                              0                         0
0
ARCHIVED LOG                      37.86                         0              9
0
BACKUP PIECE                          0                         0
0
IMAGE COPY                            0                         0
0
FLASHBACK LOG                         0                         0
0
FOREIGN ARCHIVED LOG                  0                         0
0

已選擇7行。
SQL> conn h2/hydeesoft
已連線。
SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Administrator\flash_rec
                                                 overy_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
SQL> exit;
從 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷
開

方法二：清除過期歸檔日誌（或強制刪除指定的歸檔日誌）
C:\Users\Administrator>rman target /

恢復管理器: Release 11.2.0.1.0 - Production on 星期二 9月 30 11:38:07 2014

連線到目標資料庫: ORCL (DBID=1383696692)

RMAN> crosscheck archivelog all;
……
對歸檔日誌的驗證成功
歸檔日誌檔名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_414_B2N9PX60_.ARC RECID=161 STAMP=859635808
對歸檔日誌的驗證成功
歸檔日誌檔名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_415_B2N9Q8WD_.ARC RECID=162 STAMP=859635820
……
RMAN> delete expired archivelog all;

釋放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=138 裝置型別=DISK
說明與資料檔案庫中的任何歸檔日誌都不匹配

RMAN> delete archivelog until time 'sysdate’;-- 刪除截止到今天的所有archivelog
……
159 1 412 A 30-9月 -14
名稱: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_412_B2N9P5NV_.ARC

160 1 413 A 30-9月 -14
名稱: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_413_B2N9PJRP_.ARC

161 1 414 A 30-9月 -14
名稱: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_414_B2N9PX60_.ARC

162 1 415 A 30-9月 -14
名稱: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_415_B2N9Q8WD_.ARC

163 1 416 A 30-9月 -14
名稱: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_416_B2N9QR5N_.ARC

是否確定要刪除以上物件 (輸入 YES 或 NO)?yes
……
已刪除的歸檔日誌
歸檔日誌檔名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_415_B2N9Q8WD_.ARC RECID=162 STAMP=859635820
已刪除的歸檔日誌
歸檔日誌檔名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_416_B2N9QR5N_.ARC RECID=163 STAMP=859635835
163 物件已刪除

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE                          0                         0
              0

REDO LOG 0 0
0

ARCHIVED LOG 0 0
0

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
BACKUP PIECE                          0                         0
              0

IMAGE COPY 0 0
0

FLASHBACK LOG 0 0
0

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
FOREIGN ARCHIVED LOG                  0                         0
              0

已選擇7行。

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1285874/，如需轉載，請註明出處，否則將追究法律責任。

ORA-00257: archiver error. Connect internal only, until freed 解決方法

相關文章