ORA-16038 ORA-19502 ORA-00312

[root@db-gwyx-4 archivelog]# pwd
/oradata/archivelog

 [oracle@db-gwyx-4 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 16 11:17:58 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

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

ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 1 thread 1: 'xxx'

SQL>alter system archive log all to '/oradata/archivelog';

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 3 sequence# 1203 cannot be archived
ORA-19502: write error on file "", blockno  (blocksize=)
ORA-00312: online log 3 thread 1: '/oradata/qhznjl/redo03.log'

SQL> alter system archive log all to '/oradata/archivelog/qhznjl'; 

System altered.

SQL> alter database open;

Database altered.

SQL> alter system set log_archive_dest_1='/oradata/archivelog/qhznjl' scope =spfile  ;
alter system set log_archive_dest_1='/oradata/archivelog/qhznjl' scope =spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

SQL> alter system set log_archive_dest_1='location=/oradata/archivelog/qhznjl' scope =both;

System altered.

 

---end------

reference：

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5 - Release: 10.1 to 10.2
Information in this document applies to any platform.

Symptoms

An 'alter database open;' fails with errors as below:

$ strings spfiledb.ora
...
*.db_recovery_file_dest='/backup/10g/db/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Mon May 31 19:50:20 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> startup mount
ORACLE instance started.
Total System Global Area 620756992 bytes
Fixed Size 780104 bytes
Variable Size 316938424 bytes
Database Buffers 301989888 bytes
Redo Buffers 1048576 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 230 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/oradata/10g/db/instant/redo01.log'
SQL>

Cause

All the information about what is placed in the flash recovery area is registered in the rman repository or the controlfile.
If it is determined that there is not sufficient space in the recovery file destination as set by db_recovery_file_dest_size, then this fails.
Just deleting the old backups/archive logs from disk is not sufficient as it is the rman repository/controlfile that holds the used space information.

Solution

There are a couple of possible options.

Increase the parameter db_recovery_file_dest_size

Stop using the db_recovery_file_dest by unsetting the parameter.
(This assumes you never really wanted to use this option)

Remove the Entries from the rman repository/controlfile
The removal is described in the RMAN documentation but the below is a quick and dirty way if you don't have a rman repository.

NOTE:
Deleting still required archived log files endangers your ability to recover - so be careful.

Delete unwanted archived log files from disk (rm, del commands)

Connect to rman ( RMAN> connect target / )

RMAN> crosscheck archivelog all; - marks the controlfile that the archives have been deleted

RMAN> delete expired archivelog all; - deletes the log entries identified above.
You should then find archiving resumes correctly.

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

转载于:http://blog.itpub.net/13750068/viewspace-735597/