ORA-16038 ORA-19502 ORA-00312

最新推荐文章于 2025-05-22 17:43:58 发布

转载最新推荐文章于 2025-05-22 17:43:58 发布 · 168 阅读

文章标签：

#数据库

本文记录了在Oracle数据库中遇到的归档日志错误ORA-16038及ORA-19502，并详细展示了如何通过调整归档路径和参数设置来解决这些问题。同时介绍了当启动数据库时遇到ORA-16038错误时的可能原因及解决方案。

[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

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/