mysql 不完全恢复_【不完全恢复】

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 proc【本文来自鸿网互联 (http://www.68idc.cn)】edure 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;

本文原创发布php中文网，转载请注明出处，感谢您的尊重！