oracle00279,ORACLE數據恢復

alter Log文件：

Mon Feb 28 13:25:31 2005

ORACLE V8.1.6.0.0 - Production vsnsta=0

vsnsql=e vsnxtr=3

Windows 2000 Version 5.0 Service Pack 4, CPU type 586   ----------------------某交警系統后臺數據庫、2005年上線，版本ORACLE8i ,OS Windows 2000

Starting up ORACLE RDBMS Version: 8.1.6.0.0.

System parameters with non-default values:

processes                = 59

shared_pool_size         = 52428800

large_pool_size          = 614400

java_pool_size           = 20971520

control_files            = D:\Oracle\oradata\ORCL\control01.ctl, D:\Oracle\oradata\ORCL\control02.ctl, D:\Oracle\oradata\ORCL\control03.ctl

db_block_buffers         = 19200

db_block_size            = 8192

compatible               = 8.1.0

log_buffer               = 32768

log_checkpoint_interval  = 10000

log_checkpoint_timeout   = 1800

db_files                 = 1024

db_file_multiblock_read_count= 8

max_enabled_roles        = 30

remote_login_passwordfile= EXCLUSIVE

global_names             = TRUE

distributed_transactions = 500

instance_name            = ORCL

service_names            = ORCL

mts_dispatchers          = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)

open_links               = 4

sort_area_size           = 65536

sort_area_retained_size  = 65536

db_name                  = ORCL

open_cursors             = 100

ifile                    = D:\Oracle\admin\ORCL\pfile\init.ora

os_authent_prefix        =

job_queue_processes      = 4

job_queue_interval       = 10

parallel_max_servers     = 5

background_dump_dest     = D:\Oracle\admin\ORCL\bdump

user_dump_dest           = D:\Oracle\admin\ORCL\udump

max_dump_file_size       = 10240

oracle_trace_collection_name=

Tue Feb 21 11:18:44 2012

Thread 1 advanced to log sequence 456658

Current log# 1 seq# 456658 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG

Tue Feb 21 11:19:20 2012

Errors in file D:\Oracle\admin\ORCL\bdump\orclCKPT.TRC:

ORA-01110: data file 27: 'D:\ORACLE\ORADATA\ORCL\EXAM_IMAGE2009_1.ORA'

ORA-01114: IO error writing block to file 27 (block # 1)

ORA-27072: skgfdisp: I/O error

OSD-04008: WriteFile()

O/S-Error: (OS 5)

Tue Feb 21 11:20:14 2012

Errors in file D:\Oracle\admin\ORCL\udump\ORA46664.TRC:

ORA-01242: êy¾YÎļt3öÏÖýìå′íÎó: êy¾Y¿a′|óú NOARCHIVELOG Ä￡ê½

信息綜合：

1、數據庫無backup；

2、數據庫處于NOARCHIVELOG模式

3、datafile offline drop

4、手動切換archivelog>>redolog group

從數據庫層面：

數據基本死了。

從此事件中，DBA有犯低級錯誤；

(1)、直接沒有備份意識；

(2)、表空間滿后、直接datafile offline drop;

(3)、手動切換日志。

關于datafile offline Recover相關操作：

Database altered.

SQL> recover datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf';

Media recovery complete.

SQL> alter database datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf' online;

Database altered.

SQL> alter database datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf' offline;

Database altered.

SQL> alter database create datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf' as '/data1/stream/ygzhou01/datafile/ygzhou02.dbf';

Database altered.

SQL> recover datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf';

ORA-00279: change 12639985 generated at 02/29/2012 14:19:40 needed for thread 1

ORA-00289: suggestion : /data1/stream/ygzhou01/archivelog/1_704_772473274.dbf

ORA-00280: change 12639985 for thread 1 is in sequence #704

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf' online;

alter database datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf' online

*

ERROR at line 1:

ORA-01113: file 7 needs media recovery

ORA-01110: data file 7: '/data1/stream/ygzhou01/datafile/ygzhou02.dbf'

SQL> recover datafile '/data1/stream/ygzhou01/datafile/ygzhou02.dbf';

ORA-00279: change 12639985 generated at 02/29/2012 14:19:40 needed for thread 1

ORA-00289: suggestion : /data1/stream/ygzhou01/archivelog/1_704_772473274.dbf

ORA-00280: change 12639985 for thread 1 is in sequence #704

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

.

.

.

.