oracle 恢複學習 案例1 一個資料檔案丢失 完全恢複資料庫

案例1 資料檔案user01丢失

恢複背景：

資料庫打開處于歸檔模式，首先備份資料檔案user01.dbf，然後删除user01.dbf

關閉資料庫

啟動資料庫

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size      1218316 bytes

Variable Size    100665588 bytes

Database Buffers    62914560 bytes

Redo Buffers      2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/opt/app/oracle/oradata/ora10/users01.dbf'

把資料檔案user01.dbf恢複到原來位置，啟動資料庫

ORA-01113: file 4 needs media recovery

檢視錯誤資訊

SQL> select file#,error,change#  from v$recover_file;

     FILE# ERROR           CHANGE#

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

  4             493464

恢複資料庫（媒體恢複）

SQL> recover database;

Media recovery complete.

no rows selected

歸檔曆史序号查詢

select sequence#,first_change#,next_change# from v$log_history;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

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

  1   446075       451169

  2   451169       469056

  3   469056       493368

  4   493368       493385

  5   493385       493423

  6   493423       493464

  7   493464       493478

  8   493478       493527

8 rows selected.

目前歸檔序号查詢

SQL> select sequence#,first_change#,archived from v$log;

 SEQUENCE# FIRST_CHANGE# ARC

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

  8   493478 YES

  7   493464 YES

  9   493527 NO

警告日志檔案路徑

$ORACLE_BASE/admin/ora10/bdump

alert_SID.log

可以檢視使用那個重做日志進行恢複的

[oracle@oraDBServer bdump]$ tail -100 alert_ora10.log 

Shutting down archive processes

Archiving is disabled

Archive process shutdown avoided: 0 active

Tue Mar 17 17:07:32 2009

Starting ORACLE instance (normal)

Cannot determine all dependent dynamic libraries for /proc/self/exe

Unable to find dynamic library libocr10.so in search paths

RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:

LD_LIBRARY_PATH is not set!

The default library directories are /lib and /usr/lib

Unable to find dynamic library libocrb10.so in search paths

Unable to find dynamic library libocrutl10.so in search paths

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on. 

IMODE=BR

ILAT =18

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version: 10.2.0.1.0.

System parameters with non-default values:

  processes                = 150

  __shared_pool_size       = 92274688

  __large_pool_size        = 4194304

  __java_pool_size         = 4194304

  __streams_pool_size      = 0

  sga_target               = 167772160

  control_files            = /opt/app/oracle/oradata/ora10/control01.ctl, /opt/app/oracle/oradata/ora10/control02.ctl, /opt/app/oracle/oradata/ora10/control03.ctl

  db_block_size            = 8192

  __db_cache_size          = 62914560

  compatible               = 10.2.0.1.0

  db_file_multiblock_read_count= 16

  db_recovery_file_dest    = /opt/app/oracle/flash_recovery_area

  db_recovery_file_dest_size= 2147483648

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  remote_login_passwordfile= EXCLUSIVE

  db_domain                = 

  dispatchers              = (PROTOCOL=TCP) (SERVICE=ora10XDB)

  job_queue_processes      = 10

  background_dump_dest     = /opt/app/oracle/admin/ora10/bdump

  user_dump_dest           = /opt/app/oracle/admin/ora10/udump

  core_dump_dest           = /opt/app/oracle/admin/ora10/cdump

  audit_file_dest          = /opt/app/oracle/admin/ora10/adump

  db_name                  = ora10

  open_cursors             = 300

  pga_aggregate_target     = 16777216

PMON started with pid=2, OS id=4728

PSP0 started with pid=3, OS id=4730

MMAN started with pid=4, OS id=4732

LGWR started with pid=6, OS id=4736

DBW0 started with pid=5, OS id=4734

SMON started with pid=8, OS id=4740

RECO started with pid=9, OS id=4742

CKPT started with pid=7, OS id=4738

MMON started with pid=11, OS id=4746

MMNL started with pid=12, OS id=4748

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

CJQ0 started with pid=10, OS id=4744

Tue Mar 17 17:07:33 2009

ALTER DATABASE   MOUNT

Tue Mar 17 17:07:37 2009

Setting recovery target incarnation to 2

Successful mount of redo thread 1, with mount id 664338261

Database mounted in Exclusive Mode

Completed: ALTER DATABASE   MOUNT

ALTER DATABASE OPEN

ORA-1113 signalled during: ALTER DATABASE OPEN...

Tue Mar 17 17:08:12 2009

ALTER DATABASE RECOVER  database  

Media Recovery Start

WARNING! Recovering data file 4 from a fuzzy backup. It might be an online

backup taken without entering the begin backup command.

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

  Mem# 0 errs 0: /opt/app/oracle/oradata/ora10/redo03.log

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

  Mem# 0 errs 0: /opt/app/oracle/oradata/ora10/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 9 Reading mem 0

  Mem# 0 errs 0: /opt/app/oracle/oradata/ora10/redo02.log

Media Recovery Complete (ora10)

Completed: ALTER DATABASE RECOVER  database  

Tue Mar 17 17:22:36 2009

db_recovery_file_dest_size of 2048 MB is 1.78% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

[oracle@oraDBServer bdump]$

\

本文轉自 pgmia 51CTO部落格，原文連結:http://blog.51cto.com/heyiyi/139700