欢迎投稿

今日深度:

在dataguard备库上找回在主库上被错误的Drop/Trunc

在dataguard备库上找回在主库上被错误的Drop/Truncate/Delete掉的Table


前提:
- 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 sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jul 31 22:08:19 2015

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
---------
31-JUL-15

SQL> show parameter format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf
nls_date_format                      string
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
star_transformation_enabled          string      TRUE
SQL> 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:00

SQL> select count(*) from scott.test_tab_1 ;

  COUNT(*)
----------
      2566

SQL> truncate scott.test_tab_1 ;
truncate scott.test_tab_1
              *
ERROR at line 1:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword


SQL> truncate table scott.test_tab_1 ;

Table truncated.

SQL> 

 






备库:

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440  ------->默认的设置,1440分钟,也就是一天。
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 356M
memory_target                        big integer 356M
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> shutdown immediate; 
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  372449280 bytes
Fixed Size                  1313484 bytes
Variable Size             322962740 bytes
Database Buffers           41943040 bytes
Redo Buffers                6230016 bytes
Database 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 active


SQL> 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(*)
----------
      2566

SQL> 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_1

Export: Release 11.1.0.7.0 - Production on Fri Jul 31 22:20:03 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                     TEST_TAB_1       2566 rows exported
Export terminated successfully without warnings.
[oracle@hostb SBDB1]$ 



备库:

[oracle@hostb SBDB1]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jul 31 22:26:46 2015

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount 
ORACLE instance started.

Total System Global Area  372449280 bytes
Fixed Size                  1313484 bytes
Variable Size             322962740 bytes
Database Buffers           41943040 bytes
Redo Buffers                6230016 bytes
Database mounted.
SQL> recover standby database;
ORA-00279: change 1156098 generated at 07/31/2015 22:10:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_100_884907736.dbf
ORA-00280: change 1156098 for thread 1 is in sequence #100


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1156325 generated at 07/31/2015 22:14:44 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_101_884907736.dbf
ORA-00280: change 1156325 for thread 1 is in sequence #101
ORA-00278: log file '/home/oracle/archive/SBDB1/1_100_884907736.dbf' no longer
needed for this recovery


ORA-00279: change 1156336 generated at 07/31/2015 22:15:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_102_884907736.dbf
ORA-00280: change 1156336 for thread 1 is in sequence #102
ORA-00278: log file '/home/oracle/archive/SBDB1/1_101_884907736.dbf' no longer
needed for this recovery


ORA-00279: change 1156346 generated at 07/31/2015 22:15:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive/SBDB1/1_103_884907736.dbf
ORA-00280: change 1156346 for thread 1 is in sequence #103
ORA-00278: log file '/home/oracle/archive/SBDB1/1_102_884907736.dbf' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/home/oracle/archive/SBDB1/1_103_884907736.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> 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 active


SQL> 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>

www.htsjk.Com true http://www.htsjk.com/oracle/23643.html NewsArticle 在dataguard备库上找回在主库上被错误的Drop/Truncate/Delete掉的Table 前提: - Standby Database Must be in Flashback database mode. - Time at which Drop/Truncate/Delete Table happened should be within the db_flashback_retent...
评论暂时关闭