startup数据库报错ORA-16038

使用startup启动数据库，报错如下：

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
ORA-16038: log 3 sequence# 21 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/var/oracledata/orcl/redo03.log'

奇怪！很久没启动这数据库了，怎么上来就报错。

从 limit exceeded for recovery files 可以看出，recovery files超出了限制。

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

数据库现在是mount状态，没有open；

SQL> show parameter db_recovery_file

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /oracle/flash_recovery_area
db_recovery_file_dest_size           big integer                       2G
由上面的参数 db_recovery_file_dest_size 可以看到，这里设置的闪回区只有2G，那看看已使用的有多少。

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        98.78                         0              25
BACKUPPIECE                                         .34                       .34               1
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                          0                         0               0

6 rows selected.

由上面的查询结果可以看出，闪回区的99％以上的空间被被占用了，看来是这个闪回区大小的问题了，如是，先加大它。

SQL> alter system set db_recovery_file_dest_size=3G scope=both;          

System altered.

更改为3G后，再open试试

SQL> alter database open;

Database altered.

SQL>

数据库终于正常启动了。

然后，我把刚才设置为3G的参数又改为2G，看看会不会报什么错，结果很意外。

SQL> alter system set db_recovery_file_dest_size=2G scope=both;          

System altered.

SQL> show parameter recovery

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer
2G
recovery_parallelism                 integer
0
SQL>

奇怪，居然没影响。下班了，关机走人。

 

第二天，带着疑问，继续看看这个问题。

SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------------------------------------------
NO

SQL>

怀疑会不会是oracle自动把flashback功能关闭了，昨天刚启动时明明报那错误的。既然现在停了flashback功能，那就手动启动试试看。

SQL> alter database flashback on;
alter database flashback on;
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

 

靠，启动这都报错，可能是因为我这数据库是从其他机器迁移过来的缘故，按照遇到相同问题人士的解决办法。

首先，使Oracle无论什么操作都进行redo的写入。

SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-12920: database is already in force logging mode

再关闭flashbakc功能。

SQL> alter database flashback off;

Database altered.

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

mount模式重启数据库。
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
SQL> alter database flashback on ;

Database altered.

SQL> alter system set db_recovery_file_dest_size=2G scope=both;   

System altered.

SQL>select * from v$flash_recovery_area_usage;

FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        134.2                         0              31
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                        .74                         0               1

6 rows selected.

奇怪，怎么闪回区的使用率超过了100％。会不会因为上面的那句把原来的3G改为现在的2G引起的，重启试试，看是否会恢复或报错。

SQL> alter database open;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

可以确定，现在已经开启了flashback功能。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        134.2                         0              31
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                        .74                         0               1

6 rows selected.

SQL> show parameter db_recovery

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /oracle//flash_recovery_area
db_recovery_file_dest_size           big integer                       2G
SQL>

重启后，闪回区还是2G，而它的使用率超过了100％，数据库还能正常使用，不明白怎么回事了，继续观察一段时间再说吧。

 

创建个测试表，写段代码，循环100万次，往表里面插入数据，执行到后来数据库hold住了，强行停止并重启机器，再检查那个参数是否真的没影响了。

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
ORA-16038: log 3 sequence# 27 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/var/oracledata/kms/redo03.log'

 

问题终于还是出来了，出现最开始的不能打开问题，只不过这次在意料之中。

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

SQL> set linesize 200                                                              
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        134.2                         0              31
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                        .74                         0               1

6 rows selected.

SQL> show parameter db_recovery

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /oracle/flash_recovery_area
db_recovery_file_dest_size           big integer                       2G
通过以上查询结果，查明数据库当前的flashback功能启动了，而闪回区的使用已经超过了100％。

尝试关闭flashback功能，在打开数据库试试，看是否还受闪回区不足的限制。

SQL> alter database flashback off;

Database altered.

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 27 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/var/oracledata/kms/redo03.log'

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------------------------------------------
NO

 

以上证明，即使关闭了flashback，数据库还是打不开。尝试重启数据库再试，还是一样的报错，原来是这样的。

通过

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     27
Next log sequence to archive   27
Current log sequence           29

和

SQL> show parameter archive

发现 log_archive_dest_n都没设置，归档日志在db_recovery_file_dest 路径下。

SQL> host tree /oracle//flash_recovery_area
/oracle//flash_recovery_area
`-- ORCL
    |-- archivelog
    |   |-- 2011_03_11
    |   |-- 2011_03_14
    |   |   |-- o1_mf_1_1_6qvk8fc9_.arc
    |   |   |-- o1_mf_1_1_6qvt1jd9_.arc
    |   |   |-- o1_mf_1_1_6qvt78l7_.arc
    |   |   |-- o1_mf_1_2_6qvkhbhk_.arc
    |   |   |-- o1_mf_1_2_6qvt1j9z_.arc
    |   |   |-- o1_mf_1_2_6qvt8fqo_.arc
    |   |   |-- o1_mf_1_3_6qvt1jcb_.arc
    |   |   `-- o1_mf_1_3_6qvtb51m_.arc
    |   |-- 2011_03_22
    |   |   `-- o1_mf_1_4_6rhpz7km_.arc
    |   |-- 2011_03_23
    |   |   |-- o1_mf_1_5_6rkkw2rr_.arc
    |   |   `-- o1_mf_1_6_6rmzjlmd_.arc
    |   |-- 2011_03_24
    |   |   `-- o1_mf_1_7_6rpmmk7f_.arc
    |   |-- 2011_03_25
    |   |   `-- o1_mf_1_8_6rrhcz8s_.arc
    |   |-- 2011_03_26
    |   |   `-- o1_mf_1_9_6rt8jjmf_.arc
    |   |-- 2011_03_27
    |   |   `-- o1_mf_1_10_6rwfy45k_.arc
    |   |-- 2011_03_28
    |   |   `-- o1_mf_1_11_6rz9cyg5_.arc
    |   |-- 2011_03_29
    |   |   |-- o1_mf_1_12_6s1d42vn_.arc
    |   |   `-- o1_mf_1_13_6s3shj86_.arc
    |   |-- 2011_03_30
    |   |   `-- o1_mf_1_14_6s6fv9f5_.arc
    |   |-- 2011_03_31
    |   |   `-- o1_mf_1_15_6s9274sz_.arc
    |   |-- 2011_04_01
    |   |   `-- o1_mf_1_16_6sc1k1bf_.arc
    |   |-- 2011_04_02
    |   |   `-- o1_mf_1_17_6sdq6l0g_.arc
    |   |-- 2011_04_03
    |   |   `-- o1_mf_1_18_6shmvv52_.arc
    |   |-- 2011_04_04
    |   |   `-- o1_mf_1_19_6smmq5p5_.arc
    |   |-- 2011_04_05
    |   |   `-- o1_mf_1_20_6soplgym_.arc
    |   |-- 2011_04_06
    |   |-- 2011_04_07
    |   |-- 2011_04_08
    |   |-- 2011_04_09
    |   |-- 2011_04_10
    |   |-- 2011_04_11
    |   |-- 2011_11_18
    |   |   |-- o1_mf_1_21_7dbm3nfr_.arc
    |   |   |-- o1_mf_1_22_7dbm3rfo_.arc
    |   |   |-- o1_mf_1_23_7dbm3s3f_.arc
    |   |   |-- o1_mf_1_24_7ddrynsr_.arc
    |   |   |-- o1_mf_1_25_7ddrynt9_.arc
    |   |   `-- o1_mf_1_26_7ddrytld_.arc
    |   `-- 2011_11_19
    |       |-- o1_mf_1_27_7df18xbp_.arc
    |       `-- o1_mf_1_28_7df1919h_.arc
    |-- flashback
    `-- onlinelog

29 directories, 33 files

SQL>

这里，我通过rman来删除归档日志试试，因为这里只是测试环境，归档日志没什么用。

[oracle@oraclesvr ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on 星期六 11月 19 00:32:51 2011

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

connected to target database: ORCL (DBID=1986166942)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvt1jd9_.arc recid=23 stamp=745783472
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvk8fc9_.arc recid=15 stamp=745783368
validation succeeded for archived log

。。。

Crosschecked 33 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN> delete archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
------- ---- ------- - ---------- ----
23      1    1       A 14-3月 -11 /oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvt1jd9_.arc
15      1    1       A 14-3月 -11 /oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvk8fc9_.arc

。。。

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvt1jd9_.arc recid=23 stamp=745783472
deleted archive log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvk8fc9_.arc recid=15 stamp=745783368

。。。

 

Deleted 33 objects

RMAN> exit

SQL> conn /as sysdba
Connected.
SQL> set linesize 200
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                         3.87                         0               1
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                          0                         0               0

6 rows selected.

SQL>

闪回区变小了。

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

转载于:http://blog.itpub.net/9399028/viewspace-711577/