在dataguard备库上找回在主库上被错误的Drop/Truncate/Delete 掉的Table
分类：Oracle DB
前提：- Standby Database Must be in Flashback database mode. – Time at which Drop/Truncate/Delete Table happened should be within the db_flashback_retention_target and all the flashback and archive logs should be available 在dataguard备库上找回在主库上被错误的Drop/Truncate/Delete 掉的Table参考文章：How To Recover From A Drop/Truncate/Delete Table Done On Primary Using Flashback On A Standby Database (文档 ID 958557.1)主库： [oracle@hosta ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.1.0.7.0 – Production on Fri Jul 31 22:08:19 2015Copyright (c) 1982, 2008, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select sysdate from dual;SYSDATE———31-JUL-15SQL> show parameter formatNAMETYPEVALUE———————————— ———– ——————————log_archive_formatstring%t_%s_%r.dbfnls_date_formatstringnls_time_formatstringnls_time_tz_formatstringnls_timestamp_formatstringnls_timestamp_tz_formatstringstar_transformation_enabledstringTRUESQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';Session altered.SQL> select sysdate from dual;SYSDATE——————-2015-07-31 22:10:00SQL> select count(*) from scott.test_tab_1 ; COUNT(*)———-2566SQL> truncate scott.test_tab_1 ;truncate scott.test_tab_1*ERROR at line 1:ORA-03290: Invalid truncate command – missing CLUSTER or TABLE keywordSQL> truncate table scott.test_tab_1 ;Table truncated.SQL>备库：SQL> show parameter targetNAMETYPEVALUE———————————— ———– ——————————archive_lag_targetinteger0db_flashback_retention_targetinteger1440 ——->默认的设置，1440分钟，也就是一天。fast_start_io_targetinteger0fast_start_mttr_targetinteger0memory_max_targetbig integer 356Mmemory_targetbig integer 356Mpga_aggregate_targetbig integer 0sga_targetbig integer 0SQL> recover managed standby database cancel;Media recovery complete.SQL> shutdown immediate; ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 372449280 bytesFixed Size1313484 bytesVariable Size322962740 bytesDatabase Buffers41943040 bytesRedo Buffers6230016 bytesDatabase mounted.SQL> flashback database to timestamp to_date('2015-07-31 22:10:00','YYYY-MM-DD HH24:MI:SS');flashback database to timestamp to_date('2015-07-31 22:10:00','YYYY-MM-DD HH24:MI:SS')*ERROR at line 1:ORA-01153: an incompatible media recovery is activeSQL> alter database recover managed standby database cancel;Database altered.SQL> flashback database to timestamp to_date('2015-07-31 22:10:00','YYYY-MM-DD HH24:MI:SS');Flashback complete.SQL> alter database open read only;Database altered.SQL> select count(*) from scott.test_tab_1 ; COUNT(*)———-2566SQL> exit[oracle@hostb SBDB1]$ export NLS_LANG=american_america.AL32UTF8[oracle@hostb SBDB1]$ exp system/oracle file=/home/oracle/test_tab_exp_0730.dmp tables=scott.test_tab_1Export: Release 11.1.0.7.0 – Production on Fri Jul 31 22:20:03 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in AL32UTF8 character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path …Current user changed to SCOTT. . exporting tableTEST_TAB_12566 rows exportedExport terminated successfully without warnings.[oracle@hostb SBDB1]$
备库：
[oracle@hostb SBDB1]$ sqlplus / as sysdbaSQL*Plus: Release 11.1.0.7.0 – Production on Fri Jul 31 22:26:46 2015Copyright (c) 1982, 2008, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> shutdown immediate Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount ORACLE instance started.Total System Global Area 372449280 bytesFixed Size1313484 bytesVariable Size322962740 bytesDatabase Buffers41943040 bytesRedo Buffers6230016 bytesDatabase mounted.SQL> recover standby database;ORA-00279: change 1156098 generated at 07/31/2015 22:10:03 needed for thread 1ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_100_884907736.dbfORA-00280: change 1156098 for thread 1 is in sequence #100Specify log: {<RET>=suggested | filename | AUTO | CANCEL}AUTOORA-00279: change 1156325 generated at 07/31/2015 22:14:44 needed for thread 1ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_101_884907736.dbfORA-00280: change 1156325 for thread 1 is in sequence #101ORA-00278: log file '/home/oracle/archive/SBDB1/1_100_884907736.dbf' no longerneeded for this recoveryORA-00279: change 1156336 generated at 07/31/2015 22:15:03 needed for thread 1ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_102_884907736.dbfORA-00280: change 1156336 for thread 1 is in sequence #102ORA-00278: log file '/home/oracle/archive/SBDB1/1_101_884907736.dbf' no longerneeded for this recoveryORA-00279: change 1156346 generated at 07/31/2015 22:15:13 needed for thread 1ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_103_884907736.dbfORA-00280: change 1156346 for thread 1 is in sequence #103ORA-00278: log file '/home/oracle/archive/SBDB1/1_102_884907736.dbf' no longerneeded for this recoveryORA-00308: cannot open archived log'/home/oracle/archive/SBDB1/1_103_884907736.dbf'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3SQL> alter database recover managed standby database disconnect from session;alter database recover managed standby database disconnect from session*ERROR at line 1:ORA-01153: an incompatible media recovery is activeSQL> alter database recover managed standby database cancel;Database altered.SQL> alter database recover managed standby database using current logfile disconnect from session;Database altered.SQL>
上一篇Windows 7下，，一块硬盘突然掉盘时的windows操作系统日志
顶0踩0
一直觉得人应该去旅行，在年轻的时候，趁着有脾气装潇洒，
