오라클 falshback

# flashback table : 휴지통에서 테이블 복구

[hirasvr1:/SYSTEM/oraswno_datafile/DEVDB_DATA]$sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 29 15:25:04 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> grant dba to hr ;

SQL> conn hr/hr
Connected.
SQL> create table emp1 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp1 values ('aaa', 1) ;

1 row created.

SQL> insert into emp1 values ('bbb', 2) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> drop table emp1 ;

Table dropped.

SQL> show recyclebin <- 문제1) 무슨 명령어?
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$XymNUHIAK3vgRAAAAAAAAA==$0 TABLE 2008-12-29:15:26:16

SQL> purge recyclebin; <- 문제2) 무슨 명령어?

Recyclebin purged.

SQL>
SQL> show recyclebin <-- 휴지통에 아무것도 없으니깐 아무것도 안나옴 ^^
SQL>

SQL> create table emp1 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp1 values ('aaa', 1) ;

1 row created.

SQL> insert into emp1 values ('bbb', 2) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> drop table emp1 ;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$XymNUHIBK3vgRAAAAAAAAA==$0 TABLE 2008-12-29:15:33:13

SQL> flashback table emp1 to before drop;

Flashback complete.

SQL> select * from emp1 ;

ENAME ID
---------- ----------
aaa 1
bbb 2

SQL>
SQL> purge recyclebin;

Recyclebin purged.

SQL>
SQL> create table emp2 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp2 values ('aaa', 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> drop table emp2 purge ; <- 문제3) 무슨 명령어?

Table dropped.

SQL> show recyclebin
SQL>

SQL> create table emp3 (
2 ename varchar2(10),
3 id number(10)) ;

Table created.

SQL> insert into emp3 values ('aaa', 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> create index index_emp3 on emp3(ename) ;

Index created.

SQL> select index_name from user_indexes where table_name = 'EMP3' ;

INDEX_NAME
------------------------------
INDEX_EMP3

SQL> drop table emp3 ;

Table dropped.

SQL> show recyclebin ;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP3 BIN$XymNUHIEK3vgRAAAAAAAAA==$0 TABLE 2008-12-29:15:55:10
SQL>
SQL> flashback table emp3 to before drop;

Flashback complete.

SQL> select * from emp3 ;

ENAME ID
---------- ----------
aaa 1

SQL> select index_name from user_indexes where table_name = 'EMP3' ;

INDEX_NAME
------------------------------
BIN$XymNUHIDK3vgRAAAAAAAAA==$0 <- 문제4) 이것은 무엇인가?

SQL> alter index "BIN$XymNUHIDK3vgRAAAAAAAAA==$0" rename to index_emp3 ; <- 문제5) 무슨 명령어?

Index altered.

SQL> select index_name from user_indexes where table_name = 'EMP3' ;

INDEX_NAME
------------------------------
INDEX_EMP3

SQL>

===========================================================================================
# flashback query : 과거 특정 시점의 데이터를 확인하고 그때로 돌아가자 ^^

-- 현제 시간과 DB 시간이 다를수 있으니, DB시간을 확인하고 DB시간에 맞춰 작업하세요 ^^

SQL> select to_char(sysdate, 'YYYY/MM/DD HH24:MI') from dual ;

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
29-DEC-08 04.28.16.798523 PM +09:00

SQL> create table emp4 (
2 ename varchar2(10),
3 salary number(10)) ;

Table created.

SQL> insert into emp4 values ('namwon', 100) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
29-DEC-08 04.31.08.784948 PM +09:00

SQL> select * from emp4 ;

ENAME SALARY
---------- ----------
namwon 100

SQL> update emp4
2 set salary=500
3 where ename='namwon' ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from emp4 ; <- 헉, 정남원을 급여가 100만원을 줘야 하는데 실수로 500만원을 줬다. 큰일큰일 ^^

ENAME SALARY
---------- ----------
namwon 500

SQL> select * from emp4 as of timestamp to_timestamp('2008-12-29 16:31','YYYY/MM/DD HH24:MI') ;

ENAME SALARY
---------- ----------
namwon 100

SQL> select * from emp4 as of timestamp to_timestamp('2008-12-29 16:35','YYYY/MM/DD HH24:MI') ;

ENAME SALARY
---------- ----------
namwon 100

SQL> select * from emp4 as of timestamp to_timestamp('2008-12-29 16:38','YYYY/MM/DD HH24:MI') ; <- 이 시간에 실수로 update 했구나.

ENAME SALARY
---------- ----------
namwon 500

SQL> update emp4 set salary = ( select salary from emp4
2 as of timestamp to_timestamp('2008-12-29 16:31','YYYY/MM/DD HH24:MI')
3 where ename='namwon' )
4 where ename='namwon'; <- 16:31 으로 빨리 돌아가자 ^^ 남원 급여는 100만원이야.ㅋㅋ

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from emp4 ;

ENAME SALARY
---------- ----------
namwon 100 <-- 정상적으로 100만원으로 돌아갔음 ^^

SQL>

=================================================================================================

# flashback version query : 과거 특정 기간(시간, SCN)의 이력 데이터 확인 및 그시점으로 flashback

SQL> conn hr/hr
Connected.
SQL> create table emp5 (
2 ename varchar2(10),
3 salary number(10)) ;

Table created.

SQL> insert into emp5 values ('NAMWON', 100);

1 row created.

SQL> insert into emp5 values ('KING', 1000) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 100
KING 1000

SQL> update emp5
2 set salary=200
3 where ename='NAMWON';

1 row updated.

SQL> update emp5
2 set salary=2000
3 where ename='KING' ;

1 row updated.

SQL> commit ;
Commit complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 200
KING 2000

SQL> delete from emp5 ;

2 rows deleted.

SQL> commit ;

Commit complete.

SQL> select versions_startscn, versions_endscn, versions_operation, salary, ename
2 from emp5 versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN V SALARY ENAME
----------------- --------------- - ---------- ----------
968877 D 2000 KING
968877 D 200 NAMWON
967905 968877 U 2000 KING
967905 968877 U 200 NAMWON
967846 967905 I 1000 KING
967846 967905 I 100 NAMWON

6 rows selected.

SQL> alter table emp5 enable row movement ; <- rowid를 보전하지 않기 때문에 row movement 기능 활성화 되어 있어야함.

Table altered.

SQL> flashback table emp5 to scn 967846 ; <- 돌아가고 싶은 VERSIONS_STARTSCN 을 적으면 된다

Flashback complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 100
KING 1000

SQL> flashback table emp5 to scn 967905 ;

Flashback complete.

SQL> select * from emp5 ;

ENAME SALARY
---------- ----------
NAMWON 200
KING 2000

SQL> flashback table emp5 to scn 968877 ;

Flashback complete.

SQL> select * from emp5 ;

no rows selected

SQL>

# flashback transaction query : 과거의 어떤 시간이나 SCN 사이의 존재 했던 행들의 값을 질의
트랜잭션에 의해 변화한 행들의 이력을 보여줌

SQL> conn hr/hr
Connected.

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
972858

SQL> create table emp6(
2 ename varchar2(10),
3 salary number(10));

Table created.

SQL> insert into emp6 values ('NAMWON', 100) ;

1 row created.

SQL> insert into emp6 values ('KING', 1000);

1 row created.

SQL> insert into emp6 values ('ALICE', 300) ;

1 row created.

SQL> COMMIT ;

Commit complete.

SQL> update emp6
2 set salary=300
3 where ename='NAMWON' ;

1 row updated.

SQL> update emp6
2 set salary=3000
3 where ename='KING' ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> delete from emp6 ;

3 rows deleted.

SQL> commit ;

Commit complete.

SQL> select versions_startscn, versions_endscn, versions_operation, salary, ename
2 from emp6 versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN V SALARY ENAME
----------------- --------------- - ---------- ----------
973006 D 300 ALICE
973006 D 3000 KING
973006 D 300 NAMWON
972999 973006 U 3000 KING
972999 973006 U 300 NAMWON
972964 973006 I 300 ALICE
972964 972999 I 1000 KING
972964 972999 I 100 NAMWON

8 rows selected.

SQL> select undo_sql from flashback_transaction_query
2 where table_name='EMP6'
3 and commit_scn between 972999 and 973006
4 order by start_timestamp desc;

UNDO_SQL
--------------------------------------------------------------------------------
insert into "HR"."EMP6"("ENAME","SALARY") values ('ALICE','300');
insert into "HR"."EMP6"("ENAME","SALARY") values ('KING','3000');
insert into "HR"."EMP6"("ENAME","SALARY") values ('NAMWON','300');
update "HR"."EMP6" set "SALARY" = '100' where ROWID = 'AAAM4tAAEAAABFEAAA';
update "HR"."EMP6" set "SALARY" = '1000' where ROWID = 'AAAM4tAAEAAABFEAAB';

SQL>

=================================================================================================

# flashback database : 전체 데이터베이스를 과거의 특정 시점으로 돌림

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> shutdown immediate
ORA-01031: insufficient privileges

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@oracle ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 29 23:19:13 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database flashback on ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter system switch logfile ;

System altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
974236

SQL> drop user hr cascade ; <- 제가 젤로 아끼는 hr 유저 drop

User dropped.

SQL> conn hr/hr <- hr 유저가 drop 되서 접속 안됨 ㅠㅠ
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> exit
[oracle@oracle ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 29 23:23:13 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup monut
SP2-0714: invalid combination of STARTUP options
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 974236 ; <- hr 유저 drop전 SCN번호 입력 (각자 환경마다 틀림 ^^)

Flashback complete.

SQL> alter database open resetlogs ;

Database altered.

SQL> conn hr/hr <- hr 유저 복원 ^^
Connected.
SQL>

====================================================================================

해답

SQL> show recyclebin <- 문제1) 무슨 명령어?
답) 휴지통 내용보기

SQL> purge recyclebin; <- 문제2) 무슨 명령어?
답) 휴지통 비우기(휴지통 삭제)

SQL> drop table emp2 purge ; <- 문제3) 무슨 명령어?
답) 테이블 drop 하면서 휴지통에도 동시에 삭제 (flashback 으로 복구 불가 ^^)

INDEX_NAME
------------------------------
BIN$XymNUHIDK3vgRAAAAAAAAA==$0 <- 문제4) 이것은 무엇인가?
답) 테이블이 삭제된후 휴지통으로 들어가면 인덱스명이 바뀐다. 그래서 원래 이름으로 rename 해야 한다 ^^

SQL> alter index "BIN$XymNUHIDK3vgRAAAAAAAAA==$0" rename to index_emp3 ; <- 문제5) 무슨 명령어?
답) 인덱스 이름 바꾸는 명령어