=================================
Flashback Transaction BACKOUT
=================================
SYS@orcl>alter system set db_recovery_file_dest_size = 7g;
System altered.
SYS@orcl>alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
System altered.
SYS@orcl>alter database add supplemental log data;
Database altered.
SYS@orcl>alter database add supplemental log data
2 (primary key) columns;
Database altered.
SYS@orcl>alter database add supplemental log data
2 (foreign key) columns;
Database altered.
SYS@orcl>grant execute on dbms_flashback to hr;
Grant succeeded.
SYS@orcl>grant select any transaction to hr;
Grant succeeded.
[oracle@lnx04 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 14 09:39:48 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
@>conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl>show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 7G
SYS@orcl>alter system set db_recovery_file_dest_size = 10g;
System altered.
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ARC
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SYS@orcl>select supplemental_log_data_min,
2 supplemental_log_data_pk
3 ,supplemental_log_data_ui
4 ,supplemental_log_data_fk
5 ,supplemental_log_data_all
6 from v$database ;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES NO YES NO
SYS@orcl>alter system switch logfile;
System altered.
SYS@orcl>alter system checkpoint;
System altered.
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
SYS@orcl>startup
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
Database opened.
[oracle@lnx04 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 14 09:44:16 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
@>conn hr/hr@orcl
Connected.
HR@orcl>create table hr.region_ft
2 (region_id number
3 , region_name varchar2(25)
4 ) tablespace users ;
Table created.
HR@orcl>insert into hr.region_ft values(10,'Pole');
1 row created.
HR@orcl>insert into hr.region_ft values(20,'Moon');
1 row created.
HR@orcl>insert into hr.region_ft values (30,'Venus');
1 row created.
HR@orcl>insert into hr.region_ft values (40,'Mars');
1 row created.
HR@orcl>insert into hr.region_ft values (50,'Saturn');
1 row created.
HR@orcl>commit;
Commit complete.
HR@orcl>select * from hr.region_ft;
REGION_ID REGION_NAME
---------- -------------------------
10 Pole
20 Moon
30 Venus
40 Mars
50 Saturn
HR@orcl>update hr.region_ft
2 set region_name='Two Poles'
3 where region_id = 10;
1 row updated.
HR@orcl>update hr.region_ft
2 set region_name='Many Moons'
3 where region_id = 20;
1 row updated.
HR@orcl>commit;
Commit complete.
HR@orcl>select * from hr.region_ft;
REGION_ID REGION_NAME
---------- -------------------------
10 Two Poles
20 Many Moons
30 Venus
40 Mars
50 Saturn
HR@orcl>update hr.region_ft
2 set region_name = 'No star'
3 where region_id = 10;
1 row updated.
HR@orcl>update hr.region_ft
2 set region_name = 'Red star'
3 where region_id = 40;
1 row updated.
HR@orcl>update hr.region_ft
2 set region_name = 'Big star'
3 where region_id = 50;
1 row updated.
HR@orcl>commit;
Commit complete.
HR@orcl>select * from hr.region_ft;
REGION_ID REGION_NAME
---------- -------------------------
10 No star
20 Many Moons
30 Venus
40 Red star
50 Big star
HR@orcl>update hr.region_ft
2 set region_name = 'Still called Venus'
3 where region_id = 30;
1 row updated.
HR@orcl>commit;
Commit complete.
HR@orcl>select * from hr.region_ft;
REGION_ID REGION_NAME
---------- -------------------------
10 No star
20 Many Moons
30 Still called Venus
40 Red star
50 Big star
[oracle@lnx04 ~]$ ec
orcl
[oracle@lnx04 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 14 09:49:54 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
@>conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl>alter system checkpoint;
System altered.
SYS@orcl>alter system switch logfile;
System altered.
SYS@orcl>alter system checkpoint;
System altered.
SYS@orcl>alter system switch logfile;
System altered.
SYS@orcl>alter system checkpoint;
System altered.
select hr.region_ft.*,
versions_startscn ST_SCN,
versions_endscn END_SCN,
versions_XID TX_ID,
versions_operation OPERATION
from hr.region_ft versions between scn minvalue and maxvalue
SYS@orcl>/
REGION_ID REGION_NAME ST_SCN END_SCN TX_ID O
---------- ------------------------- ---------- ---------- ---------------- -
30 Still called Venus 1193889 0A00100069020000 U
50 Big star 1193842 0600200026030000 U
40 Red star 1193842 0600200026030000 U
10 No star 1193842 0600200026030000 U
20 Many Moons 1193792 030018003E030000 U
10 Two Poles 1193792 1193842 030018003E030000 U
50 Saturn 1193658 1193842 050017004B030000 I
40 Mars 1193658 1193842 050017004B030000 I
30 Venus 1193658 1193889 050017004B030000 I
20 Moon 1193658 1193792 050017004B030000 I
10 Pole 1193658 1193792 050017004B030000 I
11 rows selected.
1 declare
2 v_xid xid_array ;
3 begin
4 v_xid := xid_array('030018003E030000');
5 dbms_flashback.transaction_backout(
6 numtxns=>1,
7 xids=>v_xid,
8 options=>dbms_flashback.nocascade);
9* end;
SYS@orcl>/
declare
*
ERROR at line 1:
ORA-55507: Encountered mining error during Flashback Transaction Backout. function:krvxpsr
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
alert log
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 1088521 (0x0000.00109c09)
LOGMINER: EndScn: 1194336 (0x0000.00123960)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
END
declare
v_xid xid_array ;
begin
v_xid := xid_array('030018003E030000');
dbms_flashback.transaction_backout(
numtxns=>1,
xids=>v_xid,
options=>dbms_flashback.cascade);
end;
SYS@orcl>/
declare
*
ERROR at line 1:
ORA-55507: Encountered mining error during Flashback Transaction Backout. function:krvxpsr
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
Wed Oct 14 09:59:06 2015
LOGMINER: summary for session# = 2147484417
LOGMINER: StartScn: 1088521 (0x0000.00109c09)
LOGMINER: EndScn: 1194547 (0x0000.00123a33)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
declare
v_xid xid_array ;
begin
v_xid := xid_array('030018003E030000');
dbms_flashback.transaction_backout(
numtxns=>1,
xids=>v_xid,
options=>dbms_flashback.cascade);
end;
/
=========================
flashback table
=========================
HR@orcl>create table FT_tab as select * from employees;
Table created.
HR@orcl>alter table FT_tab enable row movement;
Table altered.
HR@orcl>!date
Wed Oct 14 10:44:39 KST 2015
HR@orcl>select last_name, salary from FT_tab where employee_id = 200;
LAST_NAME SALARY
------------------------- ----------
Whalen 4400
HR@orcl>update FT_tab set salary = 4800 where employee_id = 200;
1 row updated.
HR@orcl>select last_name,salary from FT_tab where employee_id =200;
LAST_NAME SALARY
------------------------- ----------
Whalen 4800
HR@orcl>commit;
Commit complete.
HR@orcl>flashback table FT_tab to timestamp
2 to_timestamp('2015-10-14:10:44:29','YYYY-MM-DD:HH24:MI:SS');
Flashback complete.
HR@orcl>select last_name, salary from FT_tab where employee_id = 200;
LAST_NAME SALARY
------------------------- ----------
Whalen 4400
'스터디북' 카테고리의 다른 글
| [10/14] 실습 - 3 FBDA (0) | 2015.10.14 |
|---|---|
| [10/14] 실습 - 2 flashback database (0) | 2015.10.14 |
| [10/14] 정리 (0) | 2015.10.14 |
| [10/13] admin 기본 연습 (0) | 2015.10.13 |
| [10/13] 실습 - 4 Flashback query, versions query, transaction query (0) | 2015.10.13 |