[20120929]ora-55605错误.txt

[20120929]ora-55605错误.txt

昨天在测试机器查看进程,发现一个进程.

$ ps -ef | grep fbda

503      13583     1  0 09:27 ?        00:00:00 ora_fbda_test

503      14502 14465  0 09:43 pts/2    00:00:00 grep fbda

查看alert.log也能发现这个进程的启动,我查看以前的日志,发现并没有启动.

我记得当时测试时使用的表空间我前几天已经删除.看来给重新建立操作看看.

SQL> create tablespace fratable datafile '/u01/app/oracle11g/oradata/test/fratable01.dbf' size 100M;

--OK建立成功!但是我执行如下命令时报错.

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create flashback archive fra_1year tablespace fratable retention 1 year;

create flashback archive fra_1year tablespace fratable retention 1 year

                         *

ERROR at line 1:

ORA-55605: Incorrect Flashback Archive is specified

SQL> host oerr ora 55605

55605, 00000, "Incorrect Flashback Archive is specified"

// *Cause: An attempt was made to operate on a Flashback Archive that does

//         not exist, or to create a Flashback Archive that already exists.

// *Action: Check the SQL statement.

--很明显,应该是已经存在.但是应该记录在哪里呢?我当时一定仅仅是删除表空间.没有删除flashback archive fla_1year;

--使用10046跟踪看看,发现要访问SYS.SYS_FBA_FA表.

SQL> column faname format a20

SQL> select * from SYS.SYS_FBA_FA;

FANAME                      FA#  RETENTION  CREATESCN   PURGESCN      FLAGS     SPARE1     SPARE2 OWNERNAME

-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------

FRA_1YEAR                     1        365   16006734   16006734          0                       SCOTT

--可以发现确实存在!执行删除操作看看:

SQL> drop flashback archive fra_1year;

Flashback archive dropped.

SQL> select * from SYS.SYS_FBA_FA;

no rows selected

SQL> select * from dba_flashback_archive;

no rows selected

--在重启数据库看看.ora_fbda_test进程消失!

$ ps -ef | grep fbda

503      18480 14465  0 10:52 pts/2    00:00:00 grep fbda