|
radata/mdnss/redo01.log',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo03.log',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like '%drop%' and seg_name='DAODAO' and seg_owner='SCOTT'
SQL> select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like '%drop%' and seg_name='DAODAO' and seg_owner='SCOTT';
SCN
----------
SQL_REDO
--------------------------------------------------------------------------------
1089685
drop table daodao purge;
说明这个SCN号上面删除了这个对应的表所以得进行还原
SQL> select * from v$log;
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
1079325
alter tablespace USERS begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/users01.dbf /home/oracle/hotbk/
alter tablespace USERS end backup;
alter tablespace UNDOTBS1 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/undotbs01.dbf /home/oracle/hotbk/
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/sysaux01.dbf /home/oracle/hotbk/
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/system01.dbf /home/oracle/hotbk/
alter tablespace SYSTEM end backup;
alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data01.dbf /home/oracle/hotbk/
alter tablespace JF_DATA end backup;
alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data02.dbf /home/oracle/hotbk/
alter tablespace JF_DATA end backup;
alter tablespace T2 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/t2a.dbf /home/oracle/hotbk/
alter tablespace T2 end backup;
alter database backup controlfile to '/home/oracle/hotbk/ctl_after_reset.bk';
alter database backup controlfile to trace as '/home/oracle/hotbk/cre_ctl.sql';
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
1079325
SQL> SELECT COUNT(*) FROM DAODAO;
COUNT(*)
----------
500002
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 9 52428800 512 1 YES
INACTIVE 1089570 31-JUL-13 1089575 31-JUL-13
2 1 10 52428800 512 1 NO
CURRENT 1089575 31-JUL-13 2.8147E+14
3 1 7 52428800 512 1 YES
INACTIVE 1089560 31-JUL-13 1089565 31-JUL-13
4 1 8 52428800 512 1 YES
INACTIVE 1089565 31-JUL-13 1089570 31-JUL-13
select * from v$logfile
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/mdnss/redo03.log
NO
2 ONLINE
/u01/app/oracle/oradata/mdnss/redo02.log
NO
1 ONLINE
/u01/app/oracle/oradata/mdnss/redo01.log
NO
4 ONLINE
/u01/app/oracle/oradata/mdnss/redo09.log
NO
|