oracle闪回案例,【案例】Oracle闪回数据库 使用flashback database闪回表空间异常处理...

天萃荷净

运维DBA反映Oracle数据库表空间被删除，然后使用flashback database 无法正常恢复，分析原因为该表空间的数据文件的SCN异常导致

Sat Jul 05 17:10:06 2014

create tablespace oracleplus datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF'

size 50M autoextend on next 50M maxsize 1536M extent management local

Sat Jul 05 17:10:06 2014

ORA-1543 signalled during: create tablespace oracleplus datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF'

size 50M autoextend on next 50M maxsize 1536M extent management local

...

Sat Jul 05 17:10:59 2014

drop tablespace oracleplus

Sat Jul 05 17:10:59 2014

ORA-1549 signalled during: drop tablespace oracleplus

...

Sat Jul 05 17:11:05 2014

drop tablespace oracleplus

ORA-1549 signalled during: drop tablespace oracleplus

...

Sat Jul 05 17:11:24 2014

drop tablespace oracleplus including contents

Sat Jul 05 17:11:36 2014

Thread 1 advanced to log sequence 186895 (LGWR switch)

Current log# 1 seq# 186895 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO01.LOG

Sat Jul 05 17:11:36 2014

ARC3: Warning. Log sequence in archive filename wrapped

to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.

Old log archive with same name might be overwritten.

Sat Jul 05 17:11:43 2014

LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_4

Sat Jul 05 17:11:49 2014

LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_2

Sat Jul 05 17:12:09 2014

Starting control autobackup

Control autobackup written to DISK device

handle 'D:\FULLBACK\C-1342406147-20140705-00'

Completed: drop tablespace oracleplus including contents

通过这里可以发现删除表空间时间点为2014年7月5日17:12:09

1.闪回数据库到删除之前时间点

Sat Jul 05 18:16:54 2014

Database mounted in Exclusive Mode

Completed: ALTER DATABASE MOUNT

Sat Jul 05 18:19:23 2014

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')

Sat Jul 05 18:19:25 2014

Flashback Restore Start

Sat Jul 05 18:20:52 2014

--闪回时的控制文件中无表空间oracleplus信息(因为已经被删除)，

--但是由于闪回的system 数据字典里面有相关文件信息,因此数据库在控制文件里面创建相关文件信息

Flashback: created tablespace #6: 'oracleplus' in the controlfile.

Flashback: created OFFLINE file 'UNNAMED00012' for tablespace #6 in the controlfile.

Filename was:

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\oracleplus4.DBF' when dropped.

File will have to be restored from a backup and recovered.

Flashback: created OFFLINE file 'UNNAMED00010' for tablespace #6 in the controlfile.

Filename was:

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\oracleplus3.DBF' when dropped.

File will have to be restored from a backup and recovered.

Flashback: created OFFLINE file 'UNNAMED00008' for tablespace #6 in the controlfile.

Filename was:

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\oracleplus2.DBF' when dropped.

File will have to be restored from a backup and recovered.

Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6 in the controlfile.

Filename was:

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\oracleplus.DBF' when dropped.

File will have to be restored from a backup and recovered.

Flashback Restore Complete

Flashback Media Recovery Start

parallel recovery started with 15 processes

Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86891_0766797318.001

Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86892_0766797318.001

Sat Jul 05 18:21:40 2014

Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86893_0766797318.001

Sat Jul 05 18:21:47 2014

WARNING: inbound connection timed out (ORA-3136)

Sat Jul 05 18:22:11 2014

Recovery of Online Redo Log: Thread 1 Group 3 Seq 186894 Reading mem 0

Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO03.LOG

Sat Jul 05 18:22:39 2014

Incomplete Recovery applied until change 9078991241

Flashback Media Recovery Complete

ORA-38795 signalled during: FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')...

Sat Jul 05 18:30:11 2014

ALTER DATABASE OPEN RESETLOGS

Sat Jul 05 18:30:11 2014

ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...

--重命名相关UNNAMExxxxx文件名到硬盘上被删除表空间文件

Sat Jul 05 18:39:31 2014

alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus.DBF'

Sat Jul 05 18:39:31 2014

Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus.DBF'

Sat Jul 05 18:39:47 2014

alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus2.DBF'

Sat Jul 05 18:39:47 2014

Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus2.DBF'

Sat Jul 05 18:39:59 2014

alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus3.DBF'

Sat Jul 05 18:39:59 2014

Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus3.DBF'

Sat Jul 05 18:40:12 2014

alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus4.DBF'

Sat Jul 05 18:40:12 2014

Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012'

to 'D:\oracle\product\10.2.0\oradata\gzserver\oracleplus4.DBF'

Sat Jul 05 18:41:25 2014

ALTER DATABASE OPEN RESETLOGS

Sat Jul 05 18:41:25 2014

ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...

到这里,可以看出来,因为数据库整体已经闪回,但是被drop 表空间的四个数据文件未被正常闪回,因此该四个文件的scn可能异常,通过数据库恢复检查脚本(Oracle Database Recovery Check)检查结果如下

这里很明显控制文件中的scn信息混乱不做过多参考,数据文件头信息看到只有ts# 6中的四个文件(就是被删除的表空间文件)scn过大,其他文件scn都处于正常状态(处于干净状态),到这里很明显,数据库闪回成功,但是被drop tablespace的数据文件未被闪回,因此该故障可以通过bbed修改四个文件头信息和其他文件相同即可使得数据库恢复正常

温馨提示:数据库操作需要慎重,备份重于一切

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle闪回数据库 使用flashback database闪回表空间异常处理