【不完全恢复】
来源:互联网 发布:之二虫又何知的虫 编辑:程序博客网 时间:2024/04/30 00:22
不完全恢复
set until scn <scn编号>
set until time|timestamp ... 最常用的
set until logseq <>
不完全恢复示例:
SQL> conn scott/tiger
Connected.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-11-06 18:32:14
SQL> drop table emp purge;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/jadl10g/redo03.log
/u01/oracle/oradata/jadl10g/redo02.log
/u01/oracle/oradata/jadl10g/redo01.log
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/jadl10g/redo03.log');
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') time,sql_redo from v$logmnr_contents where table_name='EMP';
SCN TIME
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------
483041 2014-11-06 18:32:29
drop table emp purge;
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 19:37:08 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JADL10G (DBID=2011530396)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 163580992 bytes
Database Buffers 427819008 bytes
Redo Buffers 6287360 bytes
RMAN> run{
2> set until time "to_date('2014-11-06 18:32:28','yyyy-mm-dd hh24:mi:ss')"; --- set until scn 122222
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }
executing command: SET until clause
Starting restore at 06-NOV-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/jadl10g/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/jadl10g/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/jadl10g/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/jadl10g/users01.dbf
restoring datafile 00005 to /u01/oracle/oradata/jadl10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T160359_b5pbvjts_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T160359_b5pbvjts_.bkp tag=TAG20141106T160359
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 06-NOV-14
Starting recover at 06-NOV-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 06-NOV-14
database opened
此时执行如下操作发现该表是存在的:
select * from scott.emp;
set until scn <scn编号>
set until time|timestamp ... 最常用的
set until logseq <>
不完全恢复示例:
SQL> conn scott/tiger
Connected.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-11-06 18:32:14
SQL> drop table emp purge;
Table dropped.
删除该表后做不完全恢复才能使该表执行查询,否则不行的噢噢
获取恢复时间点可以采用日志挖掘的方式:SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/jadl10g/redo03.log
/u01/oracle/oradata/jadl10g/redo02.log
/u01/oracle/oradata/jadl10g/redo01.log
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/jadl10g/redo03.log');
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') time,sql_redo from v$logmnr_contents where table_name='EMP';
SCN TIME
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------
483041 2014-11-06 18:32:29
drop table emp purge;
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 19:37:08 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JADL10G (DBID=2011530396)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 163580992 bytes
Database Buffers 427819008 bytes
Redo Buffers 6287360 bytes
RMAN> run{
2> set until time "to_date('2014-11-06 18:32:28','yyyy-mm-dd hh24:mi:ss')"; --- set until scn 122222
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }
executing command: SET until clause
Starting restore at 06-NOV-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/jadl10g/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/jadl10g/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/jadl10g/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/jadl10g/users01.dbf
restoring datafile 00005 to /u01/oracle/oradata/jadl10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T160359_b5pbvjts_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T160359_b5pbvjts_.bkp tag=TAG20141106T160359
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 06-NOV-14
Starting recover at 06-NOV-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 06-NOV-14
database opened
此时执行如下操作发现该表是存在的:
select * from scott.emp;
0 0
- 【不完全恢复】
- rman恢复-不完全恢复
- 理解不完全恢复
- RMAN的不完全恢复
- 冷备份---不完全恢复
- RMAN不完全恢复
- Oracle不完全恢复
- Oracle 不完全恢复
- 不完全数据恢复
- 关于RESETLOGS不完全恢复
- 闪回不完全恢复
- 不完全恢复-redo损坏
- 不完全恢复-ctl-create_script
- 不完全恢复-ctl-bak
- rman实现不完全恢复
- Incarnation不完全恢复
- ORACLE手工不完全恢复
- 模拟执行不完全恢复
- Device Mapper 代码分析
- 测试用例规范v2.0
- 玩微信这么久,你知道蓝色星球下站着的人是谁吗?
- 飞速整理:方法和数组
- 详解RMQ & LCA
- 【不完全恢复】
- 为什么内部类使用的局部变量必须是final而全局变量可以是no-final
- 222服务器edx-platform搭建及其汉化
- html5常用标签 body部分 包括外观、结构体、分隔。
- IOS UI控件之Picker View
- 精辟的句子收集
- 2015_12_27微软校园招聘笔试题目
- 第九周任务--编写课题组网站
- C++ Primer复习和学习笔记 第一章 快速入门