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相關操作：

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

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

.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-717749/，如需轉載，請註明出處，否則將追究法律責任。

ORACLE資料恢復

相關文章