博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
oracle 恢复学习 案例1 一个数据文件丢失 完全恢复数据库
阅读量:6764 次
发布时间:2019-06-26

本文共 5799 字,大约阅读时间需要 19 分钟。

案例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恢复到原来位置,启动数据库
 
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-01113: file 4 needs media recovery
ORA-01110: data file 4: '/opt/app/oracle/oradata/ora10/users01.dbf'
查看错误信息
SQL> select file#,error,change#  from v$recover_file;
     FILE# ERROR           CHANGE#
---------- ----------------------------------------------------------------- ----------
  4             493464
 
恢复数据库(介质恢复)
 
SQL> recover database;
Media recovery complete.
SQL> select file#,error,change#  from v$recover_file;
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
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
Tue Mar 17 17:07:32 2009
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
Tue Mar 17 17:07:37 2009
Successful mount of redo thread 1, with mount id 664338261
Tue Mar 17 17:07:37 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Mar 17 17:07:37 2009
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...
Tue Mar 17 17:08:12 2009
ALTER DATABASE RECOVER  database  
Tue Mar 17 17:08:12 2009
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.
Tue Mar 17 17:08:12 2009
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
Tue Mar 17 17:08:12 2009
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
Tue Mar 17 17:08:12 2009
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
Tue Mar 17 17:08:12 2009
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

转载地址:http://ibdeo.baihongyu.com/

你可能感兴趣的文章
移动设备、手机浏览器Javascript滑动事件代码
查看>>
Ubuntu 中的VI和vim
查看>>
Libgdx window add alpha action change the background actor alpha
查看>>
(转)过滤器原理
查看>>
JavaScript 触发click事件 兼容FireFox,IE 和 Chrome
查看>>
【英语称谓】软件行业外企称谓简称
查看>>
实现打印级别且带图片的Excel 方案
查看>>
[再寄小读者之数学篇](2015-06-24 Series)
查看>>
【Linux】linux常用基本命令
查看>>
4-python学习——数据操作
查看>>
Oracle函数
查看>>
【redis使用全解析】常见运维操作
查看>>
hdu2377Bus Pass(构建更复杂的图+spfa)
查看>>
2015第29周三
查看>>
CCBValue
查看>>
C#一些知识点:委托和事件的区别
查看>>
Cocos2d-js-v3.2 在 mac 上配置环境以及编译到 Andorid 的注意事项(转)
查看>>
android开源项目学习
查看>>
提升Mac os x 10.10+xcode6.1之后,Cocoapods发生故障的解决方案
查看>>
标准API使用小技巧
查看>>