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

ORA-00257: archiver error. Connect internal only. until freed, ora-00257freed
1. plsq logon Error

ORA-00257: archiver error. Connect internalonly. until freed

 

2. the alert Log in the background reports the following error:

[Oracle @ powerlong4 ~] $ Tail-fn 200/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/alert_powerdes.log

Tue Apr 28 10:00:56 2015

Errors in file/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc9_30051.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 19327352832 bytes is 100.00% used, and has 0 remaining bytes available.

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

You have following choices to free up spacefrom 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 astape using RMAN

Backup recovery area command.

3. Add disk space and increasedb_recovery_file_dest_size parameter

Reflect the new space.

4. Delete unnecessary files using RMANDELETE command. If an operating

System command was used to delete files, then use rman crosscheck and

Delete expired commands.

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

Errors in file/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc9_30051.trc:

ORA-19809: limit exceeded for recoveryfiles

ORA-19804: cannot reclaim 31602688 bytesdisk space from 19327352832 limit

ARC9: Error 19809 Creating archive log fileto '/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_28/o1_mf_000075 _ % u _. arc'

 

Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.csdn.net/mchdba/article/details/45332505
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------

 

3. View archived logs

RMAN> crosscheck archivelog all; -- first check

...

Validation succeeded for archived log

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_72_bmkbwfrg _. arcRECID = 63633 STAMP = 877796493

Validation succeeded for archived log

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_73_bmkyqplx _. arcRECID = 63634 STAMP = 877816822

Validation succeeded for archived log

Archived log file name =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_74_bmkysysc _. arcRECID = 63635 STAMP = 877816895

Crosschecked 1635 objects

 

We can see that there are a lot of archive logs applied to the sendary library.

 

 

 

4. Here, you can delete redundant archived logs to view expired archived logs.

[Oracle @ powerlong4 ~] $ Rman target/

 

RMAN> delete expired archivelog all;

 

Released channel: ORA_DISK_1

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID = 787 devicetype = DISK

Specification does not match any archivedlog in the repository

 

RMAN>

 

5. Delete expired archive logs

Delete expired archivelog all;

RMAN> delete expired archivelog all;

 

Released channel: ORA_DISK_1

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID = 787 devicetype = DISK

Specification does not match any archivedlog in the repository

 

RMAN>

No expired archived logs are displayed.

 

6. If there is no expiration date, we can delete the specified time to make room.

RMAN> delete archivelog until time "to_date ('2017-04-27 13:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')";

 

Part of the execution process is as follows:

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_70_bmjfsm48 _. arcthread = 1 sequence = 70

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/ow.mf_000071_bmjk1mvp _. arcthread = 1 sequence = 71

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_72_bmkbwfrg _. arcthread = 1 sequence = 72

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_1_73_bmkyqplx _. arcthread = 1 sequence = 73

RMAN-08137: WARNING: archived log notdeleted, needed for standby or upstream capture process

Archived log filename =/oracle/app/oracle/flash_recovery_area/PDUNQ/archivelog/2015_04_23/o1_mf_74_bmkysysc _. arcthread = 1 sequence = 74

 

RMAN>

 

Select * from V $ FLASH_RECOVERY_AREA_USAGE;

 

7. Check the space used.

SQL> select * fromV $ FLASH_RECOVERY_AREA_USAGE;

 

FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLE

---------------------------------------------------------------

NUMBER_OF_FILES

---------------

Control file 0 0

0

 

Redo log 0 0

0

 

Archived log 74.36 0

1635

 

 

FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLE

---------------------------------------------------------------

NUMBER_OF_FILES

---------------

Backup piece 25.5 0

7

 

Image copy 0 0

0

 

Flashback log 0 0

0

 

 

FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLE

---------------------------------------------------------------

NUMBER_OF_FILES

---------------

Foreign archived log 0 0

0

 

 

7 rows selected.

 

SQL>

 

OK. You can see that the archive log space is 74% and there is enough space to log on.

Log on to plsql again and report the same error.

 

8. Restart the oracle service.

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

 

Total System Global Area 3373858816 bytes

Fixed Size 2218032 bytes

Variable Size 1694500816 bytes

Database Buffers 1660944384 bytes

Redo Buffers 16195584 bytes

Database mounted.

Database opened.

SQL>

 

9. Then, use plsql to log on. Now you can log on normally.

 

To sum up, Because oracle's dataguard is interrupted, archiving logs on primary have been accumulated, resulting in full archiving space, so the database cannot log on remotely, the following two common methods can be used at this time: 1) Delete expired archive logs based on the time point; 2) increase the value of db_recovery_file_dest_size.