1.Oracle Flashback Query --闪回查询
ORACLE根据undo信息，利用undo数据，类似一致性读取方法，可以把表置于一个删除前的时间点(或SCN)，从而将数据找回。
Flashback query(闪回查询)前提：
sys@CLONEPDB_PLUGPDB> show parameter undo
NAME TYPE VALUE
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
其中undo_management = auto，设置自动管理
Undo_retention = n(秒),设置决定undo最多的保存时间，其值越大，就需要越多的undo表空间的支持。修改undo_retention的命令如下：
sys@CLONEPDB_PLUGPDB> alter system set undo_retention = 3600;
System altered.
Elapsed: 00:00:00.06
sys@CLONEPDB_PLUGPDB> show parameter undo
NAME TYPE VALUE
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
获取数据删除前的一个时间点或scn，如下：
sys@CLONEPDB_PLUGPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-17 14:16:00 5409876
Elapsed: 00:00:00.11
sys@CLONEPDB_PLUGPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@CLONEPDB_PLUGPDB> select count(*) from emp;
COUNT(*)
12
Elapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> delete from emp;
12 rows deleted.
Elapsed: 00:00:00.04
scott@CLONEPDB_PLUGPDB> commit;
Commit complete.
Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual
*
ERROR at line 1:
ORA-00904: DBMS_FLASHBACK: invalid identifier
Elapsed: 00:00:00.02
查询该时间点(或scn)的数据，如下：
scott@CLONEPDB_PLUGPDB> select count(*) from emp;
COUNT(*)
0
Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select from emp as of timestamp to_timestamp('2018-01-17 14:16:00', 'yyyy-mm-dd hh24:mi:ss');--或select from emp as of scn 5409876;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
12 rows selected.
Elapsed: 00:00:00.15
恢复
scott@CLONEPDB_PLUGPDB> insert into emp (select * from emp as of scn 5409876);--也可以用时间
12 rows created.
Elapsed: 00:00:00.04
scott@CLONEPDB_PLUGPDB> commit;
Commit complete.
Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select count(*) from emp;
COUNT(*)
12
局限：
| 不能Falshback到5天以前的数据。
| 闪回查询无法恢复到表结构改变之前，因为闪回查询使用的是当前的数据字典。
| 受到undo_retention参数的影响，对于undo_retention之前的数据，Flashback不保证能Flashback成功。
| 对drop,truncate等不记录回滚的操作，不能恢复。
| 普通用户使用dbms_flashback包，必须通过管理员授权。
2.Oracle Flashback Drop Table 闪回Drop掉表
scott@clonepdb_plugPDB> show recyclebin
scott@clonepdb_plugPDB> select * from test;
no rows selected
Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> drop table test;
Table dropped.
Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> create table test as select * from emp where rownum<2;
Table created.
Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
1
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop table test;
Table dropped.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST BIN$Kqeo3hZ/RA+w3PdIGKlt6Q==$0 TABLE 2018-01-18:15:27:11
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table test to before drop;
Flashback complete.
Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
1
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> drop table test;
Table dropped.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST BIN$WZphkGyLQjqqgTNlaFN6jA==$0 TABLE 2018-01-18:15:29:52
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table "BIN$WZphkGyLQjqqgTNlaFN6jA==$0" to before drop;
Flashback complete.
Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
1
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> drop table test;
Table dropped.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table "BIN$lHb2N8coS86p8/1o8xr29A==$0" to before drop;
Flashback complete.
Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
0
scott@clonepdb_plugPDB> flashback table "BIN$Avlh8rB/Q22J0WciRhx58g==$0" to before drop rename to test_new;
Flashback complete.
Table dropped.
Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;
Table created.
Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> alter table test enable row movement;
Table altered.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-18 16:08:56 5535328
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> drop table test purge
2 ;
Table dropped.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> flashback table test to scn 5535328;
flashback table test to scn 5535328
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;
Table created.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test enable row movement;
Table altered.
Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select * from test;
EMPNO ENAME SAL
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1500
7900 JAMES 950
7902 FORD 3000
EMPNO ENAME SAL
7934 MILLER 1300
12 rows selected.
Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-18 16:13:45 5536324
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;
2 rows deleted.
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit
2 ;
Commit complete.
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-18 16:14:57 5536483
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;
2 rows deleted.
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-18 16:15:14 5536518
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test;
8 rows deleted.
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;
Commit complete.
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536518;
Flashback complete.
Elapsed: 00:00:00.55
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
8
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536324;
Flashback complete.
Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
12
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536483;
Flashback complete.
Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
10
Elapsed: 00:00:00.01
purge表不能闪回
闪回不分先后顺序
需要启动行移动alter table test enable row movement;
system表不能闪回
4.FLASHBACK ARCHIVE 闪回归档
sys@clonepdb_plugPDB> create tablespace fda datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' size 5m;
Tablespace created.
Elapsed: 00:00:00.56
sys@clonepdb_plugPDB> select name from v$datafile;
NAME
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\FDA01.DBF
Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> create flashback archive fla1 tablespace fda retention 3 year;
Flashback archive created.
Elapsed: 00:00:00.19
sys@clonepdb_plugPDB> grant flashback archive on fla1 to scott;
Grant succeeded.
scott@clonepdb_plugPDB> select * from test;
EMPNO ENAME SAL
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1500
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
10 rows selected.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test flashback archive fla1;
Table altered.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test drop column SAL;
Table altered.
Elapsed: 00:00:06.97
scott@clonepdb_plugPDB> select * from test;
EMPNO ENAME
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER
7900 JAMES
7902 FORD
7934 MILLER
10 rows selected.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> desc test
Name Null? Type
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
scott@clonepdb_plugPDB> truncate table test;
Table truncated.
Elapsed: 00:00:08.37
scott@clonepdb_plugPDB> drop table test;
drop table test
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> insert into test select empno,ename from emp;
12 rows created.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> commit
2 ;
Commit complete.
Elapsed: 00:00:00.23
scott@clonepdb_plugPDB> select count(*) from test;
COUNT(*)
12
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp('2018-01-19 07:00:00','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
0
scott@clonepdb_plugPDB> delete from test;
12 rows deleted.
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;
Commit complete.
Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp('2018-01-19 09:40:00','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
12
scott@clonepdb_plugPDB> insert into test select empno,ename from test as of timestamp to_timestamp('2018-01-19 09:40:00','YYYY-MM-DD HH24:MI:SS');
12 rows created.
Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> commit;
Commit complete.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> update test set empno=7969 where empno=7369;
update test set empno=7969 where empno=7369
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLA1" runs out of space and tracking on "TEST" is suspended
SYS扩空间
SQL> alter database datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' autoextend on next 32M maxsize 2048M;
Database altered.
scott@clonepdb_plugPDB> update test set ename=7969 where empno=7369;
1 row updated.
Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> commit;
Commit complete.
Elapsed: 00:00:00.14
scott@clonepdb_plugPDB> update test set ENAME =(select ename from test as of timestamp to_timestamp('2018-01-19 10:40:00','YYYY-MM-DD HH24:MI:SS') where empno=7369) where empno=7369;
1 row updated.
Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> commit;
Commit complete.
Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> select * from test where empno=7369;
EMPNO ENAME
7369 SMITH
Elapsed: 00:00:00.01
sys@newtestCDB> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 98.54 0 5 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
db_recovery_file_dest_size big integer 300M
sys@newtestCDB> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;
System altered.
Elapsed: 00:00:00.06
SQL> ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; --更改保留时间
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE
2 TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' day);
从闪回数据归档FLA1中清除了一天前的所有历史记录数据。通常会在保留时间到期后的第一天执行自动清除。也可以覆盖此设置以进行临时清除。
Flashback archive altered.
scott@clonepdb_plugPDB> col table_name format A10
scott@clonepdb_plugPDB> col owner_name format A10
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME STATUS
TEST SCOTT ENABLED
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test no flashback archive;
alter table test no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL> grant flashback archive administer to scott;
Grant succeeded.
scott@clonepdb_plugPDB> alter table test no flashback archive;
Table altered.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME STATUS
TEST SCOTT DISABLED
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-19 14:16:51 5757544
Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> truncate table test;
Table truncated.
Elapsed: 00:00:08.32
scott@clonepdb_plugPDB> select * from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss');
EMPNO ENAME
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER
7900 JAMES
7902 FORD
EMPNO ENAME
7934 MILLER
12 rows selected.
Elapsed: 00:00:00.12
scott@clonepdb_plugPDB> insert into table test (select from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss'));
insert into table test (select from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss'))
*
ERROR at line 1:
ORA-00903: invalid table name
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> insert into test (select * from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss'));
12 rows created.
Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-19 14:23:58 5759385
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test rename to test_01;
Table altered.
Elapsed: 00:00:08.36
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME STATUS
TEST_01 SCOTT ENABLED
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop flashback archive fla1;
Flashback archive dropped.
Elapsed: 00:00:00.04
truncate 表 表能恢复
flashback archive administer //授予用户创建，修改或删除闪回回档 flashback archive //授予用户对表进行归档。
可以删除列，truncate 表，rename 表名 与11g R1不同
不能drop表
插入不能闪回，删除，更新可以
ORA-55617 直接resize不行 建议用autoextend on next 32M maxsize 2048M;
5.Oracle Flashback Version Query 闪回版本查询
Flashback Version Query引入了一些数据表“伪列”，可以提供对数据版本的操作和检索。
scott@CLONEPDB_PLUGPDB> create table test as select empno, ename, sal from emp where rownum<3;
Table created.
Elapsed: 00:00:00.17
scott@CLONEPDB_PLUGPDB> select * from test;
EMPNO ENAME SAL
7369 SMITH 800
7499 ALLEN 1600
Elapsed: 00:00:00.07
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
7369
7499
Elapsed: 00:00:00.08
scott@CLONEPDB_PLUGPDB> update test set sal=200 where empno=7369;
1 row updated.
Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
7369
7499
Elapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> commit;
Commit complete.
Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
07001E000D070000 5415641 U 7369
5415641 7369
7499
Elapsed: 00:00:00.02
U表示数据修改后的版本数据。如果删除数据，如下操作：
scott@CLONEPDB_PLUGPDB> delete test where empno=7499;
1 row deleted.
Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
09000800EE060000 5415674 D 7499
07001E000D070000 5415641 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> delete test;
1 rows deleted.
scott@clonepdb_plugPDB> commit;
scott@clonepdb_plugPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
01001C005D070000 5415969 D 7369
09000800EE060000 5415674 D 7499
07001E000D070000 5415641 5415969 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('07001E000D070000')
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant select on flashback_transaction_query to scott;
Grant succeeded.
Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('07001E000D070000')
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant execute on dbms_flashback to scott;
Grant succeeded.
Elapsed: 00:00:00.05
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> exec dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('01001C005D070000'));
BEGIN dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('01001C005D070000')); END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 1
也失败
在cdb建c##scott用户
c##scott@newtestCDB> select count() from emp;
COUNT(*)
12
Elapsed: 00:00:00.02
c##scott@newtestCDB> create table test as select empno, ename, sal from emp where rownum<3;
Table created.
Elapsed: 00:00:00.25
c##scott@newtestCDB> select * from test;
EMPNO ENAME SAL
7369 SMITH 800
7499 ALLEN 1600
Elapsed: 00:00:00.05
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
7369
7499
Elapsed: 00:00:00.01
c##scott@newtestCDB> update test set sal=200 where empno=7369;
1 row updated.
Elapsed: 00:00:00.02
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
7369
7499
Elapsed: 00:00:00.01
c##scott@newtestCDB> commit;
Commit complete.
Elapsed: 00:00:00.01
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;
XID START_SCN ENDSCN O EMPNO
080010009B0E0000 5502537 U 7369
5502537 7369
7499
c##scott@newtestCDB> select * from test;
EMPNO ENAME SAL
7369 SMITH 200
7499 ALLEN 1600
6.闪回数据库
flashback log 快照
+
归档日志(或当前日志)
逻辑恢复
a.配置归档方式
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\app\Administrator\virtual\archivelog
Oldest online log sequence 125
Next log sequence to archive 127
Current log sequence 127
b.配置闪回恢复区
sys@newtestCDB> show parameter db_recovery
NAME TYPE VALUE
db_recovery_file_dest string C:\app\Administrator\virtual\F
lashRecovery
db_recovery_file_dest_size big integer 2G
c.配置闪回保留时间
sys@newtestCDB> show parameter db_flashback_retention_target
NAME TYPE VALUE
db_flashback_retention_target integer 1440
1440 单位分钟
d.查询是否启用
sys@newtestCDB> select flashback_on from v$database;
FLASHBACK_ON
YES
Elapsed: 00:00:00.02
selectfrom v$process where pname='RVWR';
或select from v$bgprocess where name=upper('rvwr');
例子：
增加一个表空间，然后闪回
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
1 SYSAUX YES NO YES 1
0 SYSTEM YES NO YES 1
2 UNDOTBS1 YES NO YES 1
4 USERS YES NO YES 1
3 TEMP NO NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 2
2 UNDOTBS1 YES NO YES 2
3 TEMP NO NO YES 2
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
TS# NAME INC BIG FLA ENC CON_ID
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
5 USERS YES NO YES 3
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
sys@newtest[CDB](10.8.5.204-12.2)> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-22 10:07:05 6730082
sys@newtestCDB> create tablespace abce datafile 'C:\app\Administrator\virtual\oradata\newtest\abcd01.dbf' size 50m;
Tablespace created.
Elapsed: 00:00:01.06
sys@newtestCDB> select * from v$tablespace order by con_id;
TS# NAME INC BIG FLA ENC CON_ID
4 USERS YES NO YES 1
2 UNDOTBS1 YES NO YES 1
0 SYSTEM YES NO YES 1
5 ABCE YES NO YES 1
1 SYSAUX YES NO YES 1
3 TEMP NO NO YES 1
1 SYSAUX YES NO YES 2
2 UNDOTBS1 YES NO YES 2
3 TEMP NO NO YES 2
0 SYSTEM YES NO YES 2
5 USERS YES NO YES 3
TS# NAME INC BIG FLA ENC CON_ID
3 TEMP NO NO YES 3
2 UNDOTBS1 YES NO YES 3
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
0 SYSTEM YES NO YES 4
21 rows selected.
Elapsed: 00:00:00.05
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup mount
ORACLE 例程已经启动。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
sys@newtestCDB> flashback database to scn 6730082;
闪回完成。
已用时间: 00: 00: 13.49
sys@newtestCDB> alter database open read only;
数据库已更改。
已用时间: 00: 00: 08.89
sys@newtestCDB> select * from v$tablespace order by con_id;
TS# NAME INC BIG FLA ENC CON_ID
1 SYSAUX YES NO YES 1
0 SYSTEM YES NO YES 1
2 UNDOTBS1 YES NO YES 1
4 USERS YES NO YES 1
3 TEMP NO NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 2
2 UNDOTBS1 YES NO YES 2
3 TEMP NO NO YES 2
0 SYSTEM YES NO YES 3
5 USERS YES NO YES 3
TS# NAME INC BIG FLA ENC CON_ID
3 TEMP NO NO YES 3
2 UNDOTBS1 YES NO YES 3
1 SYSAUX YES NO YES 3
0 SYSTEM YES NO YES 4
5 USERS YES NO YES 4
3 TEMP NO NO YES 4
2 UNDOTBS1 YES NO YES 4
1 SYSAUX YES NO YES 4
6 FDA YES NO YES 4
20 rows selected.
Elapsed: 00:00:00.05
SQL> select file#,checkpoint_change#,con_id from v$datafile order by con_id;
FILE# CHECKPOINT_CHANGE# CON_ID
1 6730083 1
3 6730083 1
5 6730083 1
7 6730083 1
2 1525489 2
4 1525489 2
6 1525489 2
8 6730083 3
11 6730083 3
10 6730083 3
9 6730083 3
40 6730083 4
43 6730083 4
42 6730083 4
41 6730083 4
45 6730083 4
16 rows selected
sys@newtestCDB> startup force mount
ORACLE 例程已经启动。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
sys@newtestCDB> alter database open resetlogs;
数据库已更改。
已用时间: 00: 02: 15.47
PDB级闪回
PDB闪回有几个基本的前提条件：
a.enable local undo
b.enable archivelog mode
c.enable flashback database;
sys@newtestCDB> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDBTEST MOUNTED
4 CLONEPDB_PLUG MOUNTED
sys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;
Pluggable database altered.
Elapsed: 00:00:06.46
sys@newtestCDB> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDBTEST MOUNTED
4 CLONEPDB_PLUG READ WRITE NO
sys@newtestCDB> alter database local undo on;
alter database local undo on
*
ERROR at line 1:
ORA-65192: database must be in UPGRADE mode for this operation
Elapsed: 00:00:00.03
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup UPGRADE
ORACLE 例程已经启动。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
数据库已经打开。
sys@newtestCDB> alter database local undo on;
数据库已更改。
已用时间: 00: 00: 00.34
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup
ORACLE 例程已经启动。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
数据库已经打开。
sys@newtestCDB> col PROPERTY_NAME for a25;
sys@newtestCDB> col PROPERTY_VALUE for a25;
sys@newtestCDB> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
LOCAL_UNDO_ENABLED TRUE
Elapsed: 00:00:00.02
select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME
from cdb_tablespaces a,CDB_DATA_FILES b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';
CON_ID TABLESPACE_NAME
FILE_NAME
1 UNDOTBS1
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01.DBF
sys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;
Pluggable database altered.
Elapsed: 00:00:12.05
sys@newtestCDB> alter session set container=CLONEPDB_PLUG;
Session altered.
Elapsed: 00:00:00.12
sys@newtestCDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-22 11:02:17 6736984
Elapsed: 00:00:00.38
sys@newtestCDB> create restore point CLONEPDB_PLUG_20180122 GUARANTEE FLASHBACK DATABASE;
Restore point created.
Elapsed: 00:00:00.12
sys@newtestCDB> create tablespace abce datafile 'C:\app\Administrator\virtual\oradata\newtest\CLONEPDB_PLUG\abcd01.dbf' size 50m;
Tablespace created.
Elapsed: 00:00:01.39
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
7 ABCE YES NO YES 4
7 rows selected.
Elapsed: 00:00:00.05
sys@newtestCDB> col name for a30
sys@newtestCDB> select SCN,to_char(time,'mm-dd hh24:mi') time,NAME,CLEAN_PDB_RESTORE_POINT IS_CLEAN,CON_ID from v$restore_point;
SCN TIME NAME IS_ CON_ID
6737077 01-22 11:03 CLONEPDB_PLUG_20180122 NO 4
Elapsed: 00:00:00.01
sys@newtestCDB> flashback pluggable database clonepdb_plug to scn 6736984;
Flashback complete.
Elapsed: 00:00:03.18
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;
Pluggable database altered.
Elapsed: 00:00:04.06
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
7 ABCE YES NO YES 4
7 rows selected.
Elapsed: 00:00:00.07
sys@newtestCDB> alter pluggable database CLONEPDB_PLUG close;
Pluggable database altered.
Elapsed: 00:00:00.16
sys@newtestCDB> flashback pluggable database clonepdb_plug TO RESTORE POINT CLONEPDB_PLUG_20180122;
Flashback complete.
Elapsed: 00:00:01.41
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;
Pluggable database altered.
Elapsed: 00:00:04.02
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
7 ABCE YES NO YES 4
7 rows selected.
Elapsed: 00:00:00.06
sys@newtestCDB> alter pluggable database clonepdb_plug close;
Pluggable database altered.
Elapsed: 00:00:00.18
sys@newtestCDB> alter pluggable database clonepdb_plug open resetlogs;
Pluggable database altered.
Elapsed: 00:00:11.50
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
6 rows selected.
Elapsed: 00:00:00.08
看来pdb不能用readonly 来检查
sys@newtestCDB> select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;
INCARNATION# RESETLOGS_TIME
1 2017-03-08 15:57:31
2 2017-12-13 17:22:26
3 2018-01-22 10:24:24
Elapsed: 00:00:00.06
sys@newtestCDB> select DB_INCARNATION#,PDB_INCARNATION#,INCARNATION_TIME,con_id from v$pdb_incarnation order by 3;
DB_INCARNATION# PDB_INCARNATION# INCARNATION_TIME CON_ID
2 0 2017-12-13 17:22:26 4
3 0 2018-01-22 10:24:24 4
3 1 2018-01-22 11:03:02 4
Elapsed: 00:00:00.04
PDB级别的闪回，并没有改变整个数据库的INCARNATION， 从新增加的v$pdb_incarnation视图可以确认刚才的闪回操作只是在PDB(con_id is 3)增加了对应的2条记录。
C:\app\Administrator\virtual\oradata\newtest\clonepdb_plug>rman target sys/zncg3
008_ZNCG@clonepdb_plug
恢复管理器: Release 12.2.0.1.0 - Production on 星期一 1月 22 12:33:42 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: NEWTEST:CLONEPDB_PLUG (DBID=50957894, 未打开)
RMAN> flashback pluggable database CLONEPDB_PLUG to scn 6749827;
从位于 22-1月 -18 的 flashback 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=28 设备类型=DISK
正在开始介质的恢复
介质恢复完成, 用时: 00:00:03
在 22-1月 -18 完成了 flashback
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;
Pluggable database altered.
Elapsed: 00:00:04.33
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
7 ABCE YES NO YES 4
7 rows selected.
Elapsed: 00:00:00.06
sys@newtestCDB> alter pluggable database clonepdb_plug close;
Pluggable database altered.
sys@newtestCDB> alter pluggable database clonepdb_plug open resetlogs;
Pluggable database altered.
Elapsed: 00:00:10.53
sys@newtestCDB> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
5 USERS YES NO YES 4
6 FDA YES NO YES 4
6 rows selected.
Elapsed: 00:00:00.09
flashback backup在12.2中有rman和SQL两种方式闪回， 当使用shared undo里需要使用rman，前提需要在pdb close immediate后创建clean resotre point, 过程中会自动创建辅助实例CDB和PDB PITR； 使用local undo时,就可以使用SQL命令更佳快速，而且只是pdb 数据文件原位置闪回，并应用undo，在做之前创建任意一种restore point都可以，也不需要辅助实例。
扫一扫
1015
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
