ORACLE資料恢復

ygzhou518發表於2012-03-02
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章