Oracle flashback功能，此次测试环境为Oracle 11.2.0.4 DataGuard环境，主库删除业务表，在备库上闪回数据库至删除前一时间点，捞取数据，再导入至生产库。
1.备库闪回相关参数查询
1.1 查看数据库版本：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1.2 查看数据库闪回是否开启：
SQL> select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY NO
FLASHBACK_ON 为NO 表示数据库闪回未开启
1.3 查看闪回区设置：
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer
闪回区路径，大小均未设置
1.4 查看闪回保留时间：
SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
时间单位为分钟，默认值1440分钟（1天）
2.开启数据库闪回
2.1 配置闪回区大小
SQL> alter system set db_recovery_file_dest_size=10g;
System altered.
2.1.1 配置闪回区路径
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.
2.2配置闪回保留时间（按需）
SQL> alter system set db_flashback_retention_target=10080;
System altered.
2.3 重启数据库至mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
2.4 开启数据库闪回
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY YES
3.检查主备数据库数据同步情况
3.1 主库日志切换
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
4274
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
4276
3.2 备库查询日志应用情况：
SQL> select process,status,thread#,sequence#,block# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 4277 1
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 4274 1
MRP0 APPLYING_LOG 1 4278 77
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 1 4278 77
9 rows selected.
SQL> exec dbms_lock.sleep(3);
select process,status,thread#,sequence#,block# from v$managed_standby;
PL/SQL procedure successfully completed.
SQL>
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 4277 1
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 4274 1
MRP0 APPLYING_LOG 1 4278 87
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 1 4278 87
检查得知，数据库主备数据同步是正常的。
4.模拟主库删除业务表
4.1 查询主库：
[oracle@11gdgpri ~]$ sqlplus ray/ray@orcl
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 12:34:22 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2021-01-28 12:35:33
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11gdgpri ~]$
4.2 查询备库：
[oracle@11gdgpri ~]$ sqlplus ray/ray@orcldg
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 12:35:44 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4.3 主库删除业务表：
[oracle@11gdgpri ~]$ sqlplus ray/ray@orcl
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 12:45:52 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
**2021-01-28 12:46:01** 记住这个时间，数据删除前一时间点，等下备库需要闪回到这个时间点
SQL> drop table scott.dept purge;
Table dropped.
SQL> select * from scott.dept;
select * from scott.dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
此时显示主库已经没有scott.dept这个表了，
4.4 查询备库
[oracle@11gdgpri ~]$ sqlplus ray/ray@orcldg
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 12:49:32 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2021-01-28 12:49:57
SQL> select * from scott.dept;
select * from scott.dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
由于主备库数据同步，备库也没有scott.dept这个表了。
5.备库数据库闪回
前一步骤记录的数据删除前一时间点为2021-01-28 12:46:01，此次闪回我们需要将备库闪回至这一时间点。
5.1 重启备库到mount状态
[oracle@11gdgstd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 12:53:15 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY YES
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> select name,open_mode,database_role,flashback_on from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL MOUNTED PHYSICAL STANDBY YES
5.2 设置日期格式
SQL> ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2021-01-28 12:54:55
5.3 闪回数据库至指定时间点
SQL> flashback database to timestamp to_timestamp('2021-01-28 12:46:01','yyyy-MM-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ ONLY PHYSICAL STANDBY
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可见，数据库闪回至2021-01-28 12:46:01时间点，数据库中scott.dept表已经出现。
6.备库表数据导出
由于备库是open read only状态，expdp工具不支持，因此需要使用exp工具
[oracle@11gdgstd ~]$ expdp "'/ as sysdba'" directory=dp dumpfile=dept.dmp logfile=expdp_dept.log tables=scott.dept
Export: Release 11.2.0.4.0 - Production on Thu Jan 28 13:19:34 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-16000: database open for read-only access
[oracle@11gdgstd ~]$ exp scott/tiger tables=dept file=/home/oracle/dept.dmp log=exp_dept.log buffer=80960000
Export: Release 11.2.0.4.0 - Production on Thu Jan 28 13:31:06 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 ...
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
将dmp文件传输至主库
6.主库表导入
[oracle@11gdgpri ~]$ imp scott/tiger tables=dept file=/home/oracle/dept.dmp log=imp_dept.log buffer=80960000
Import: Release 11.2.0.4.0 - Production on Thu Jan 28 13:35:22 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 4 rows imported
Import terminated successfully without warnings.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
至此，误删除的业务表已经恢复。
7.备库重启，重新开启数据同步
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ ONLY PHYSICAL STANDBY
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select status,instance_name,to_char(startup_time,'yyyy/mm/dd hh24:mi:ss') start_time,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') currt_time from gv$instance;
STATUS INSTANCE_NAME START_TIME CURRT_TIME
------------ ---------------- ------------------- -------------------
OPEN orcldg 2021/01/28 13:40:11 2021/01/28 13:43:59
SQL> select open_mode,name,db_unique_name,force_logging,log_mode,DATABASE_ROLE from v$database;
OPEN_MODE NAME DB_UNIQUE_NAME FOR LOG_MODE
-------------------- --------- ------------------------------ --- ------------
DATABASE_ROLE
----------------
READ ONLY WITH APPLY ORCL orcldg NO ARCHIVELOG
PHYSICAL STANDBY
SQL> select process,status,thread#,SEQUENCE#,BLOCK# from v$managed_standby where process like 'MRP%' or process like 'RFS%' and SEQUENCE#<>0;
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 4298 24
RFS IDLE 1 4298 24
SQL> exec dbms_lock.sleep(3);
select process,status,thread#,SEQUENCE#,BLOCK# from v$managed_standby where process like 'MRP%' or process like 'RFS%' and SEQUENCE#<>0;
PL/SQL procedure successfully completed.
SQL>
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 4298 27
RFS IDLE 1 4298 27
备库已经在开始重新数据同步。
补充：
如果是主库表数据误删除，则采用dblink的方式较为简便，如下：
1.主库删除数据：
[oracle@11gdgpri ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 13:46:44 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter session set nls_date_format='YYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
**2021-01-28 13:47:01**
SQL> delete from scott.dept where deptno<40;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
2.备库数据库闪回至删除数据前一时间点2021-01-28 13:47:01
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL MOUNTED PHYSICAL STANDBY
SQL> ^M
SQL>
SQL>
SQL> flashback database to timestamp to_timestamp('2021-01-28 13:47:01','yyyy-MM-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
备库闪回后，数据已经找到。
3.主库创建到备库的DBLINK
[oracle@11gdgpri ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 13:54:20 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> grant create database link to scott;
Grant succeeded.
SQL> create public database link orcldg connect to scott identified by tiger using '192.168.3.239:1521/orcldg';
Database link created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
SQL> select * from dept@orcldg;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
查询到备库的数据了
4.备库数据捞取插入到主库
SQL> insert into dept select * from dept@orcldg where deptno<40;
3 rows created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from dept@orcldg;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> conn / as sysdba
Connected.
SQL> drop public database link orcldg;
Database link dropped.
5.备库重启，重新开启数据同步
步骤同上，不在赘述。
6505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
