[归档]no primary destinations and failed to archive-CSDN博客

问题现象：

SQL> select count(*) from test1;

COUNT(*)

----------

1835008

SQL> insert into test1 select * from test1; 光标停滞

SQL> select status from v$instance；

STATUS

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

OPEN

SQL> conn test/test 连接失败

ERROR:

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

警告: 您不再连接到 ORACLE。

SQL> conn / as sysdba 可连接

已连接。

SQL>

alert_orcl.log：

————————————

Wed Jul 01 10:16:50 2009

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orc1_arc0_1748.trc:

ORA-16014: log 3 sequence# 12 not archived, no available destinations

ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'

Wed Jul 01 10:16:51 2009

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orc1_arc1_2712.trc:

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

解决方法：

SQL> show parameter recovery_file

NAME TYPE VALUE

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

db_recovery_file_dest string C:\oracle\product\10.2.0\flash_recovery_area

db_recovery_file_dest_size big integer 2G

os中删除db_recovery_file_dest下不用的归档文件，然后进入rman：

rman>crosscheck archivelog all; 注意:此处绝非crosscheck backup of archivelog all;

rman>delete expired archivelog all; 注意:此处绝非delete expired backup of archivelog all;

rman>delete obsolete;

小记：

习惯使然，我犯了注意中的错误，导致问题始终解决不了，alert_orcl.log中不停出现如下错误：

Wed Jul 01 10:01:27 2009

ARC0: Archiving not possible: No primary destinations

ARC0: Failed to archive thread 1 sequence 12 (4)

Wed Jul 01 10:01:28 2009

ARC1: Archiving not possible: No primary destinations

ARC1: Failed to archive thread 1 sequence 12 (4)

后来仔细检查，才发现根本没有从rman中删掉归档记录！

成功验证：

SQL> conn test/test

已连接。

SQL> insert into test1 select * from test1;

已创建1835008行。

SQL> select count(*) from test1;

COUNT(*)

----------

3670016

附：最早错误出现过程

————————————————————————————

之所以最后才解决，原因有二：

1、没有第一时间看错误日志；

2、os归档目录下删除后空间已剩余90%以上，rman中也已delete，以为成功，没有意识到自己删除的是备份而非归档crosscheck backup;delete expired backup;

错误现象：

SQL> alter database open;

alter database open

第 1 行出现错误:

ORA-16014: 日志 1 的序列号 2 未归档, 没有可用的目的地

ORA-00312: 联机日志 1 线程 1:

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> recover database until cancel

完成介质恢复。

SQL> alter database open resetlogs;

数据库已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> / 停滞……

^C 强行终止

SQL> startup force

ORACLE 例程已经启动。

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 146803588 bytes

Database Buffers 457179136 bytes

Redo Buffers 7135232 bytes

数据库装载完毕。

ORA-16038: 日志 1 序列号 2 无法归档

ORA-19809: 超出了恢复文件数的限制

ORA-00312: 联机日志 1 线程 1:

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> archive log list;

数据库日志模式存档模式

自动存档启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 4

下一个存档日志序列 6

当前日志序列 6

错误日志：

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orc1_arc0_3960.trc:

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

Wed Jul 01 09:09:19 2009

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

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.

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

Wed Jul 01 09:09:19 2009

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orc1_arc0_3960.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 50360832 bytes disk space from 2147483648 limit

ARC0: Error 19809 Creating archive log file to 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_07_01\O1_MF_1_2_U_.ARC'

ARC0: Failed to archive thread 1 sequence 2 (19809)

ARCH: Archival stopped, error occurred. Will continue retrying

Wed Jul 01 09:09:20 2009

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orc1_arc0_3960.trc:

ORA-16038: log 1 sequence# 2 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

Wed Jul 01 09:09:23 2009

ARC1: Archiving not possible: No primary destinations

ARC1: Failed to archive thread 1 sequence 2 (4)

ARCH: Archival stopped, error occurred. Will continue retrying

Wed Jul 01 09:09:23 2009

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orc1_arc1_260.trc:

ORA-16014: log 1 sequence# 2 not archived, no available destinations

ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16426127/viewspace-607990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16426127/viewspace-607990/