错误码: ORA-19815

author:skate
time：2009/08/20

---

 

错误码: ORA-19815

 

解决方法：

1.增加db_recovery_file_dest_size空间的大小，默认值是2G
2.如果里面放有archivelog的话,可以用rman删除,步骤如下：

 

$ rman target /
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> exit;

 

知识扩展：

 

1.Flashback database 和flash recovery area的关系？

 

Flashback Database uses its own logging mechanism, creating flashback
logs which are stored in the flash recovery area. You can only use
Flashback Database if flashback logs are available. Therefore, you
must set up your database in advance to create flashback logs if you
want to take advantage of this feature.To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the
past you want to be able to restore your database with Flashback
Database.From that time on, at regular intervals, the database copies images
of each altered block in every datafile into the flashback logs.
These block images can later be reused to reconstruct the datafile
contents as of any moment at which logs were captured.When a database is restored to its state at some past target time using Flashback Database, each block changed since that time is
restored from the copy of the block in the flashback logs most
immediately prior to the desired target time. The redo log is then
used to re-apply changes since the time that block was copied to the
flashback logs.

 

 

2.  如何启用flashback database？

 

 1.确认当前模式

SQL>select flashback_on from v$database;
FLA
---
NO

2.检查/修改恢复区设置

SQL>show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data5/flash_recovery_area
db_recovery_file_dest_size           big integer 10G

3.检查/修改闪回时间设置

注意：db_flashback_retention_target参数值的单位是分钟(1天=24*60分钟=1440分钟)

SQL >show parameter db_flashback_retention_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     60

SQL>alter system set db_flashback_retention_target=1440;

System altered.

4.重新启动数据库到Mount状态，启动flashback database选项。

  shutdown immediate;
 startup mount ;
 或者
shutdown immediate;
startup mount exclusive;

NOTE：也可以启动到mount状态后，修改以上的参数，例如：

alter system set db_flashback_retention_target=1440;
alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
alter system set db_recovery_size=15G;

 

 

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

SQL>startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SQL>alter database flashback on;
Database altered.

 

SQL >alter database open;

Database altered.

 

5. flashback database:

(1) 关闭数据库并启动到mount状态

shutdown immediate;
startup mount ;

(2) flashback database

flashback database to timestamp/scn...;

(3) open resetlogs

alter database open resetlogs;

 

6、监控flashback log area:

(1) 监控flashback log空间占用情况

select retention_target, flashback_size, estimated_flashback_size
  from v$flashback_database_log;

(2) 监控过去24小时内每小时flashback日志、redo日志及data block读写量

select to_char(end_time, 'yyyy-mm-dd hh:miAM') end_timestamp,
       flashback_data,
       db_data,
       redo_data
  from v$flashback_database_stat;

(3) 监控flash recovery area情况

select name,
       space_limit max_size,
       space_used used,
       space_reclaimable obsolete,
       number_of_files num_files
  from v$recovery_file_dest;

 

 

 3.如何设置flash recovery area,涉及到那些参数？

(1).db_recovery_file_dest：指定闪回恢复区的位置

(2).db_recovery_file_dest_size：指定闪回恢复区的可用空间大小

(3).db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟，也就是一天。当然，
    实际上可回退的时间还决定于闪回恢复区的大小，因为里面保存了回退所需要的flash log。所以这个参数要和    db_recovery_file_dest_size配合修改。

alter system set db_flashback_retention_target=1440;
alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
alter system set db_recovery_file_dest_size=15G;

 

 

---end---