数据库启动（RMAN、归档模式）--BUG--ORA-16038、19809、00312

（1）：

    bug-----------：测试 oracle启动的时候报：

 

SQL> startup 
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1218532 bytes
Variable Size              83888156 bytes
Database Buffers          117440512 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-16038: log 1 sequence# 53 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/mediadb/redo01.log'

 

（2）：

 初步查找的原因应该是：

 

Oracle 10g默认的归档日志存放在flash_recovery_area，而默认安装的时候该区间选择了1G大小
当归档日志超过1G的时候，空间不足，于是无法归档，当时手工删除归档日志文件，Oracle是无法识别空间是否释放。导致数据库无法启动

 

（3）：

解决方法通常包括：

1. 提高参数db_recovery_file_dest_size

• 使用sqlplus /nolog
• 登录，SQL> connect /as sysdba;
• SQL> startup mount;
• SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH;
• SQL> alter database open;

2. 关闭闪回功能（如果你确实不再需要该功能）

• 使用sqlplus /nolog登录，connect /as sysdba;
• SQL> alter system archive log stop;

3. 删除无用的归档文件并更新rman记录

• 查看对应initxxx.ora，找不到的话可以在$ORACLE_HOME下搜索，找对应SID的即可
• 在initxxx.ora中找到# File Configuration部分，可以看到db_recovery_file_dest=XXXXX，即为数据闪回区的日志文件记录位置。
• 进入闪回区目录，查看对应SID下的archivelog文件夹内内容，删除旧的日志归档文件
• 使用RMAN命令，RMAN> connect target /
• 使用命令RMAN> crosscheck archivelog all，该操作将标明哪些归档文件已被删除
• 使用命令RMAN> delete expired archivelog all，该命令将删除rman controlfile中相应的记录

 

 

自己解决方法具体为：

SQL> select   * from   v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS     ARCHIV           STATUS                           FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------

         1              1             53           52428800          1       NO                 INACTIVE                               2677591 29-MAR-09

         2              1             56           52428800          1       NO                 CURRENT                                2852855 04-MAY-09

         3              1             55           52428800          1       NO                 INACTIVE                               2776096 30-MAR-09

 

日志文件组的状态：

current: 当前正在使用的工作组
inactive: 非活动组
active  : 归档还没有完成
unused  : 还没有使用,一般新建的工组组都是这个状态

在active,current状态下的工作组都不能删除,要日志切换后才可以

 

 

 

sql> alter system set  db_recovery_file_dest_size=4g scope=spfile;
参数db_recovery_file_dest_size的值根据磁盘剩余空间大小和实际需要来设置，没有定论 

SQL> alter  database  clear  logfile  group  2;
alter  database  clear  logfile  group  2
*
ERROR at line 1:
ORA-00350: log 2 of instance mediadb (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/mediadb/redo02.log'

SQL> alter  database  clear  unarchived  logfile  group  2;

Database altered.

SQL> alter  database  open;

Database altered.

 

重新启动数据库OK

 

 

SQL> shutdown  immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup  mount;
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1218532 bytes
Variable Size              79693852 bytes
Database Buffers          121634816 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter  database  archivelog;

Database altered.

SQL> alter  database  open;

Database altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     59
Next log sequence to archive   61
Current log sequence           61

 

 

 

最后打开oracle NTS 监听器以及EM控制台：

 

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 

 

[oracle@localhost ~]$ lsnrctl  start 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 04-MAY-2009 15:35:24

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain.viewtoo)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                04-MAY-2009 15:38:35
Uptime                    0 days 0 hr. 3 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain.viewtoo)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 

 

[oracle@localhost ~]$ emctl  start  dbconsole
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control .................... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/localhost.localdomain_mediadb/sysman/log

 

 

（4）：

RMAN用法删除无用的 归档日志：

[oracle@localhost ~]$ rman   target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 4 15:19:33 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MEDIADB (DBID=954190712)

RMAN> crosscheck  archivelog  all;

 

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
validation failed for archived log...

.....

Crosschecked 45 objects

RMAN> delete  expired archivelog  all;

.....

Do you really want to delete the above objects (enter YES or NO)? yes

.......

（5）：

反思：

 

检查flash recovery area的使用情况：

 

SQL> select   sum(percent_space_used)*3/100 from  v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
                       2.9331

SQL> alter  system set  db_recovery_file_dest_size=4g  scope=spfile;

System altered.

SQL> select  sum(percent_space_used)*3/100  from  v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
                       2.9331

可以看到，这里已经有 2.9331G使用了，这说明我们刚开始设置的db_recovery_file_dest_size=2G不足，导致online redo log无法归档，在这里，我们通过设置db_recovery_file_dest_size参数，增大了flash recovery area来解决这个问题。

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

转载于:http://blog.itpub.net/15713890/viewspace-612582/