[归档]no primary destinations and failed to archive

 

问题现象：

SQL> select count(*) from test1;

  COUNT(*)

----------

   1835008

 

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

^C

 

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/