Oracle:Enable Flashback
首先,查看下当前的时间与SCN信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@oracle12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 7 15:14:24 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2015-09-07 15:14:32 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1771741 SQL> |
当前数据库中,闪回是否开启?:
1 2 3 4 5 6 7 |
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> |
当前是没有打开闪回的。
查看闪回恢复区的设置:
1 2 3 4 5 6 7 8 |
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 4560M SQL> |
查看一下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@oracle12c flashback]$ cd /u01/app/oracle/fast_recovery_area/ [oracle@oracle12c fast_recovery_area]$ ls orcl ORCL [oracle@oracle12c fast_recovery_area]$ tree * orcl `-- control02.ctl ORCL |-- archivelog | |-- 2015_09_06 | `-- 2015_09_07 `-- onlinelog 4 directories, 1 file [oracle@oracle12c fast_recovery_area]$ |
如果上面的“db_recovery_file_dest”没有设置,则你需要创建闪回恢复区。
创建闪回恢复目录:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@oracle12c ~]# su - oracle [oracle@oracle12c ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 53G 15G 35G 31% / /dev/sda1 99M 13M 81M 14% /boot tmpfs 2.0G 946M 1.1G 48% /dev/shm [oracle@oracle12c ~]$ cd /u01 [oracle@oracle12c u01]$ ls app [oracle@oracle12c u01]$ mkdir flashback [oracle@oracle12c u01]$ cd flashback/ [oracle@oracle12c flashback]$ pwd /u01/flashback [oracle@oracle12c flashback]$ |
然后执行“alter system set …”对“db_recovery_file_dest”做出更改:
1 2 |
alter system set db_recovery_file_dest='/u01/flashback' scope=both; alter system set db_recovery_file_dest_size=30G scope=both; |
当前数据库的归档情况:
1 2 3 4 5 6 7 |
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 15 Current log sequence 17 SQL> |
当前数据库没有打开归档。
要打开数据库的闪回功能,需要先决打开归档模式,然后才能开启闪回。
它需要在数据库mount阶段执行,具体如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 1660944384 bytes Fixed Size 2925072 bytes Variable Size 1056968176 bytes Database Buffers 587202560 bytes Redo Buffers 13848576 bytes Database mounted. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED SQL> SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 15 Current log sequence 17 SQL> SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled. SQL> SQL> alter database archivelog; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 15 Next log sequence to archive 17 Current log sequence 17 SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> SQL> alter database flashback on; Database altered. SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED SQL> alter database open; Database altered. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> |
要对数据表做闪回操作,前提是开启:行迁移(Row Movement)。
当前表是否开启了行迁移呢?:
1 2 3 4 5 6 7 8 9 10 11 |
SQL> col owner for a8 SQL> col table_name for a13 SQL> col tablespace_name for a18 SQL> set linesize 900 SQL> select owner,table_name,tablespace_name,ROW_MOVEMENT from dba_tables where table_name='SEQUENCE_TAB'; OWNER TABLE_NAME TABLESPACE_NAME ROW_MOVE -------- ------------- ------------------ -------- SYS SEQUENCE_TAB SYSTEM DISABLED SQL> |
很明显,没有开启。
对数据表开启行迁移:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2015-09-07 15:36:34 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1773490 SQL> SQL> alter table sequence_tab enable row movement; Table altered. SQL> SQL> select owner,table_name,tablespace_name,ROW_MOVEMENT from dba_tables where table_name='SEQUENCE_TAB'; OWNER TABLE_NAME TABLESPACE_NAME ROW_MOVE -------- ------------- ------------------ -------- SYS SEQUENCE_TAB SYSTEM ENABLED SQL> select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2015-09-07 15:37:34 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1773518 SQL> |
已成功激活“行迁移”。
破坏数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
SQL> select * from sequence_tab; ID DATE_STR SEQUENCE_STR ---------- --------- ------------ 1 02-APR-16 1 2 22-AUG-12 2 3 22-AUG-12 4 4 02-APR-16 3 5 02-APR-16 4 6 25-FEB-15 5 7 25-FEB-15 9 8 25-FEB-15 10 9 25-FEB-15 23 9 rows selected. SQL> SQL> select current_scn from v$database; CURRENT_SCN ----------- 1773610 SQL> select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2015-09-07 15:40:23 SQL> update sequence_tab set sequence_str='1'; 9 rows updated. SQL> select* from sequence_tab; ID DATE_STR SEQUENCE_STR ---------- --------- ------------ 1 02-APR-16 1 2 22-AUG-12 1 3 22-AUG-12 1 4 02-APR-16 1 5 02-APR-16 1 6 25-FEB-15 1 7 25-FEB-15 1 8 25-FEB-15 1 9 25-FEB-15 1 9 rows selected. SQL> commit; Commit complete. SQL> |
执行闪回:
1 2 3 4 5 |
根据时间戳闪回: flashback table sequence_tab to timestamp to_timestamp('2015-09-07 15:40:23','YYYY-MM-DD HH24:mi:ss'); 根据SCN闪回: flashback table sequence_tab to scn 1773610 |
以上的时间戳与SCN信息都是在破坏数据(update操作)之前收集的。
对于数据表的另一种闪回恢复是:闪回删除。
闪回恢复到对表执行的删除操作之前:
1 2 3 4 5 |
通常的做法: flashback table sequence_tab to before drop 你也可以借由闪回删除,对表重命名: flashback table sequence_tab to before drop rename to <new_tab_name> |
不过需要注意的是,SYS用户不对闪回操作提供支持:
1 2 3 4 5 6 7 8 |
SQL> flashback table sequence_tab to timestamp to_timestamp('2015-09-07 15:40:23','YYYY-MM-DD HH24:mi:ss'); flashback table sequence_tab to timestamp to_timestamp('2015-09-07 15:40:23','YYYY-MM-DD HH24:mi:ss') * ERROR at line 1: ORA-08185: Flashback not supported for user SYS SQL> |
————————————————
Done。