180328 FLASHBACK 7가지 중 FLASHBACK DB를 제외한 나머지 6개 실습
FLASHBACK.txtSYS@orcl>alter database add supplemental log data;
Database altered.
SYS@orcl>ed
Wrote file afiedt.buf
1 alter database add supplemental log data
2* (primary key) columns
SYS@orcl>/
Database altered.
SYS@orcl>grant execute on dbms_flashback to hr;
Grant succeeded.
SYS@orcl>grant select any transaction to hr;
Grant succeeded.
SYS@orcl>conn hr/hr
Connected.
HR@orcl>select salary from employees
2 where employee_id = 101;
SALARY
----------
23460
HR@orcl>update employees
2 set salary = salary * 1.5
3 where employee_id = 101;
1 row updated.
HR@orcl>commit;
Commit complete.
HR@orcl>select salary from employees
2 where employee_id = 101;
SALARY
----------
35190
<flashback query 1>
HR@orcl>select employee_id, salary from employees
2 as of timestamp (systimestamp - 5/(24*60))
3 where employee_id = 101;
EMPLOYEE_ID SALARY
----------- ----------
101 23460
<flashback query 1-2>
HR@orcl>ed
Wrote file afiedt.buf
1 update employees
2 set salary =
3 (select salary from employees
4 as of timestamp (systimestamp -7/(24*60))
5 where employee_id = 101)
6* where employee_id = 101
HR@orcl>/
1 row updated.
HR@orcl>commit
2 ;
Commit complete.
HR@orcl>select salary from employees
2 where employee_id = 101;
SALARY
----------
23460
HR@orcl>
Flashback에는 7가지 방법이 있다.
<1.VERSIONS QUERY>
HR@orcl>col versions_startime for a25
HR@orcl>col versions_endtime for a25
HR@orcl>select versions_starttime, versions_endtime,
2 versions_xid, salary
3 from employees
4 versions between timestamp minvalue and maxvalue
5 where employee_id = 101;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID
--------------------------------------------------------------------------- ------------------------- ----------------
SALARY
----------
28-MAR-18 04.54.32 PM 0500070031020000
24633
28-MAR-18 04.53.18 PM 28-MAR-18 04.54.32 PM 0200070042020000
23460
28-MAR-18 04.48.06 PM 28-MAR-18 04.53.18 PM 0200050042020000
35190
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID
--------------------------------------------------------------------------- ------------------------- ----------------
SALARY
----------
28-MAR-18 04.48.06 PM
23460
<2.Transaction Query>
HR@orcl>desc flashback_transaction_query
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
HR@orcl>select undo_sql, xid from flashback_transaction_query
2 where table_name = 'employees';
no rows selected
HR@orcl>ed
Wrote file afiedt.buf
1 select undo_sql, xid from flashback_transaction_query
2* where table_name = 'EMPLOYEES'
HR@orcl>/
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
update "HR"."EMPLOYEES" set "SALARY" = '35190' where ROWID = 'AAAEQVAAEAAAADNAAB';
0200070042020000
update "HR"."EMPLOYEES" set "SALARY" = '23460' where ROWID = 'AAAEQVAAEAAAADNAAB';
0200050042020000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
update "HR"."EMPLOYEES" set "SALARY" = '23460' where ROWID = 'AAAEQVAAEAAAADNAAB';
0500070031020000
18 rows selected.
<<<<<<<<<<<<<<<참고 : 스풀을 이용하는 방법도 있다 스풀에 저장된 일련번호를 입력하여 그 시점으로 돌리는 원리>>>>>>>>>>
HR@orcl>spool flashbacktransactionquery.txt
HR@orcl>/
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
update "HR"."EMPLOYEES" set "SALARY" = '35190' where ROWID = 'AAAEQVAAEAAAADNAAB';
0200070042020000
update "HR"."EMPLOYEES" set "SALARY" = '23460' where ROWID = 'AAAEQVAAEAAAADNAAB';
0200050042020000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
0400210095010000
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
XID
----------------
0400210095010000
0400210095010000
update "HR"."EMPLOYEES" set "SALARY" = '23460' where ROWID = 'AAAEQVAAEAAAADNAAB';
0500070031020000
18 rows selected.
HR@orcl>spool off
HR@orcl>!
[oracle@localhost admin]$ ls
afiedt.buf flashbacktransactionquery.txt glogin.sql glogin.sql~ help libsqlplus.def plustrce.sql pupbld.sql
[oracle@localhost admin]$ get flashbacktransactionquery.txt
<3. FLASHBACK TABLE>
HR@orcl>update employees
2 set salary = salary * 1.5;
20 rows updated.
HR@orcl>commit;
Commit complete.
HR@orcl>select avg(salary) from employees;
AVG(SALARY)
-----------
16605.9
HR@orcl>alter table employees enable row movement; <<
Table altered.
HR@orcl>flashback table employees to timestamp (sysdate - interval '5' minute);
Flashback complete.
HR@orcl>alter table employees disable row movement; << 행 이동을 잠근다.
Table altered.
2 select salary from employees
3 versions between timestamp minvalue and maxvalue
4 where employee_id = 101;
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME SALARY
--------------------------------------------------------------------------- ----------
28-MAR-18 05.09.57 PM
24633
28-MAR-18 05.09.57 PM
36949.5
28-MAR-18 05.07.21 PM
28-MAR-18 05.09.57 PM 36949.5
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME SALARY
--------------------------------------------------------------------------- ----------
28-MAR-18 04.54.32 PM
28-MAR-18 05.07.21 PM 24633
28-MAR-18 04.53.18 PM
28-MAR-18 04.54.32 PM 23460
28-MAR-18 04.53.18 PM 35190
6 rows selected.
HR@orcl>save q_version
Created file q_version.sql
<FLASHBACK UNDROP>
HR@orcl>drop table dept;
Table dropped.
HR@orcl>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIGEMP TABLE
BIN$aHSpzABlqAXgUAB/AQAVQw==$0 TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
DEPARTMENTS2 TABLE
DP_EMP TABLE
EMPLOYEES TABLE
EMPXT TABLE
EMPXT2 TABLE
JOBS TABLE
JOB_GRADES TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
SYS_TEMP_FBT TABLE
TEST_EMP TABLE
16 rows selected.
HR@orcl>select * from "BIN$aHSpzABlqAXgUAB/AQAVQw==$0";
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87
AD_ASST 5280 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96
MK_MAN 15600 100 20
202 Pat Fay PFAY 603.123.6666 17-AUG-97
MK_REP 7200 201 20
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94
AC_MGR 14400 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-94
AC_ACCOUNT 9960 205 110
100 Steven King SKING 515.123.4567 17-JUN-87
AD_PRES 28800 90
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89
AD_VP 24633 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93
AD_VP 26979 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-90
IT_PROG 10800 102 60
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
104 Bruce Ernst BERNST 590.423.4568 21-MAY-91
IT_PROG 7200 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-99
IT_PROG 5040 103 60
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-99
ST_MAN 6960 100 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-95
ST_CLERK 4200 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-97
ST_CLERK 3720 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-98
ST_CLERK 3120 124 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-98
ST_CLERK 3000 124 50
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-00
SA_MAN 12600 .2 100 80
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-96
SA_REP 13200 .3 149 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-98
SA_REP 10320 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-99
SA_REP 8400 .15 149
20 rows selected.
HR@orcl>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
DEPT BIN$aHSpzABlqAXgUAB/AQAVQw==$0 TABLE 2018-03-28:17:19:18
HR@orcl>flashback table dept to before drop;
Flashback complete.
<FLASHBACK DATA ARCHIVE>
- 불완전 복구랑 비슷
SYS@orcl>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@orcl>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 109
Next log sequence to archive 111
Current log sequence 111
SYS@orcl>create tablespace fda_tbs
2 datafile '/u01/app/oracle/oradata/orcl/fda_tbs01.dbf'
3 size 20m autoextend on;
Tablespace created.
SYS@orcl>create user archive_admin
2 identified by oracle << 암호 oracle
3 default tablespace fda_tbs;
User created.
SYS@orcl>grant connect, resource to archive_admin;
Grant succeeded.
SYS@orcl>grant flashback archive administer to archive_admin; << flashback 할 수 있는 관리자 role 주기
Grant succeeded.
ARCHIVE_ADMIN@orcl>create flashback archive fla1 // 테이블 스페이스 저 영역에 1년 간 undo 데이터 보관하도록 지정
2 tablespace fda_tbs
3 retention 1 year;
Flashback archive created.
ARCHIVE_ADMIN@orcl>grant flashback archive on fla1 to hr; << 그 영역을 hr이 쓸 수 있도록 권한 주기
Grant succeeded.
ARCHIVE_ADMIN@orcl>conn hr/hr
Connected.
HR@orcl>create table emp1
2 as
3 select * from employees;
Table created.
HR@orcl>create table emp2
2 as
3 select * from employees;
Table created.
HR@orcl>alter table emp1 flashback archive fla1; << fla1을 emp1테이블에 담을거다 1년 간 보관된다. emp2는 따로 지정 안했으니 보관 안된당
Table altered.