SQL>create user ftest identified by ftest
2 default tablespace users
3 temporary tablespace temp;
User created
SQL>grant connect, resource to ftest;
Grant succeeded
SQL>conn ftest/ftest
Connected
FTEST>create table ftest1(no number);
Table created
FTEST>insert into ftest1 values(1);
1 row created
FTEST>commit
Commit complete
FTEST>select * from tab;
TNAME TABTYPE CLUSTEID
----------------------------------------------------------------
FTEST1 TABLE
FTEST>drop table ftest1;
FTEST>select * from tab
TNAME TABTYPE CLUSTERID
---------------------------------------------------------------------------
BIN$5AVBt+oOMsfgOAB/ AOBnZO==$O TABLE
FTEST>show recyclebin ;
CLUSTERID
ORIGINALNAME RECYClEBINNAME OBJECT1YPE DROPTIME
------------------------------------------------------------------------------------
FTEST1 BIN$5AVBt-teOM때OAB/AOBnZO=$O TABlE 2013-08-16:07:47:20
FTEST>select * from "BIN$5AVBt+oOMsfgOAB/AOBnZO==$0"
NO
---
1
# FTEST1이었던 테이블이 Drop 되면서 이름이 변경
# Show recyclebin을 조회하니 해당 테이블의 원래 이름과 삭제 후 이름,삭제 시간을 알 수 있다
# SELECT 로 내용까지 조회 가능
FTEST>flashback table ftest1 to before drop
Flashback complete
FTEST>select * from tab;
TNAME TABTYPE CLUSTEID
----------------------------------------------------------------
FTEST1 TABLE
FTEST>drop table ftest1 purge;
Table dropped
FTEST>show recyclebin;
FTEST>select * from tab;
no rows selected
* recyclebin의 내용을 비우려면 purge 명령 사용
FTEST>create table ftest1 (no1 number) ;
Table created
FTEST>insert into ftest1 values (1) ,
1 row created
FTEST>create table ftest2 (no2 number)
Table created
FTEST>insert into ftest2 values (2);
1 row created
FTEST>commit
Commit complete
FTEST>select * from tab
TNAME TABTYPE CLUSTERID
----------------------------------
FTEST1 TABLE
FTEST2 TABLE
FTEST>drop table ftest1;
Table dropped
FTEST>drop table ftest2;
Table dropped
FTEST>show recyc1ebin;
ORIGINALNAME RECYCLEBINNAME OBJECTTYPE DROPTIME
-------------------------------------------------------------------------------------
FTEST1 BIN$5ASoEeHVwiPgOAB/ A0AN10==$0 TABLE 2013-08-16 08:15:57
FTEST2 BIN$5ASoEeHWwiPgOAB/ A0AN10==$0 TABLE 2013-08-16 08:16:00
FTEST>purge table ftest1 ,
Table purged
FTEST>show recyc1ebin;
ORIGINALNAME RECYCLEBINNAME OBJECTTYPE DROPTIME
-------------------------------------------------------------------------------------
FTEST2 BIN$5ASoEeHWwiPgOAB/ A0AN10==$0 TABLE 2013-08-16 08:16:00
FTEST>purge recyclebin; <= 휴지통을 전부 비우는 명령
Recyclebin purged
FTEST>show recyclebin;
FTEST>select * from tab <= 휴지통이 모두 비워져서 내역이 없음
no rows selected
* Recyclebin 종류
FTEST> conn sys/oracle as sysdba ;
connected
SYS>!vi recyclebin.sql
set line 200
col name for a30
SELECT SUBSTR(object_name,1,50) as name,object_type,owner
from dba_objects
WHERE object_name LIKE '%RECYCLEBIN%'
SYS>@recyc1ebin
NAME OBJECT_TYPE OWNER
----------------------------------------------
RECYCLEBIN$ TABLE SYS
RECYCLEBIN$_OBJ INDEX SYS
RECYCLEBIN$_TS INDEX SYS
RECYCLEBIN$_OWNER INDEX SYS
USER_RECYCLEBIN VIEW SYS
USER_RECYCLEBIN SYNONYM PUBLIC
RECYCLEBIN SYNONYM PUBLIC
DBA_RECYCLEBIN VIEW SYS
DBA_RECYCLEBIN SYNONYM PUBLIC
9 rows se1ected
* Recycle 기능에 대한 현재 설정 조회
SYS> set line 200
SYS> co1 ksppinm for a30
SYS> co1 ksppstv1 for alO
SYS> co1 ksppstdf for al0
SYS> select a.ksppinm , b.ksppstvl , b.ksppstdf
2 from x$ksppi a , x$ksppcv b
3 where a .indx = b.indx
4 and a.ksppinm like '%recyclebin%'
5 order by a.ksppinm
/
KSPPINM KSPPSTVL KSPPSTDF
--------------------------------
recyclebin on TRUE
* Recycle 기능에 대한 사용 유무 제어
# Oracle 10g R1 : _recyclebin 파라미터
# Oracle 10g R2 : alter session set, alter system set
SYS>select * from v$version ;
BANNER
----------------------------------------------------------------------
Oracle Database l1g Enterprise Edition Release 1L2.0.2.0 - Production
PLlSOL Release 1L2.0,2,O - Production
CORE 1L2.0.2.0 Production
TNS for Linux Version 11.2 .0.2.0 - Production
NLSRTL Version 11.2 .0.2.0 - Production
SYS>col objecLname tor a30
SYS>col originaLname tor a15
SYS>col type for alO
SYS>select objecLname,originaLname,type,droptime trom dba_recyclebin
2 where owner='FTEST'
OBJECT_NAME ORIGINALNAME TYPE DROPTIME
---------------------------------------------------------------------------
BIN$5AaYpOivykbgOAB/AOBzCw==$O FTEST3 TABLE 2013-08-16103448
SYS>alter session set recyclebin=off;
Session altered
SYS>create table ftest.test4 (no4 number);
Table created
SYS>drop table ftest.test4 ;
Table dropped
SYS>select objecLname,originaLname, type,droptime from dba_recyclebin
2 where owner='FTEST'
OBJECLNAME ORIGINALNAME TYPE DROPTIME
------------------------------------------------------------------------------
BIN$5AaYpOivykbgOAB/ AOBzCw==$O FTEST3 TABLE 2013-08-16103448
SYS>conn ftest/ftest
Connected
FTEST>create table ftest5 (no5 number),
Table created
FTEST>drop table ftest5;
Table dropped
FTEST>show recyclebin;
ORIGINALNAME RECYCLEBINNAME OBJECTTYPE DROPTIME
-------------------------------------------------------------------------------------
FTEST3 BIN$5AaYpOivykbgOAB/ AOBzCw==$O TABLE 2013- 08 -1610 3448
FTEST5 BIN$5AaYpOivykbgOAB/ AOBzCw==$O TABLE 2013- 08 -1610 3448
* sys 계정으로 로그인하여 alter system set 으로 off 후 테스트
SYS>a1ter system set recyclebin=off
a1ter system set recyclebin=off
*
ERROR at line 1
ORA-02096 specified initialization parameter is not modifiable with this option
SYS>shutdown immediate
Database c\osed
Database dismounted
ORACLE instance shut down
SYS>exit
Disconnected from Orac\e Database l1g Enterprise Edition Release 112.0.2.0 -*
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
lorac\e@localhost -IS vi /app/orac\e/product/llg/dbs/inittestdbora
*pfile 수정
recyclebin=off
SYS>startup
ORACLE instance started
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted
Database opened
SYS>conn ftes t/ftest
Connected
FTEST>create table ftest6(no6 number);
Table created
FTEST>drop table ftest6;
Table dropped
FTEST>show recyclebin;
ORIGINALNAME RECYCLEBINNAME OBJECTTYPE DROPTIME
-------------------------------------------------------------------------------------
FTEST3 BIN$5AaYpOivykbgOAB/ AOBzCw==$O TABLE 2013- 08 -1610 3448
FTEST5 BIN$5AaYpOivykbgOAB/ AOBzCw==$O TABLE 2013- 08 -1610 3448
SCOTT> create table animal
(no number
,name varchar2(10)
,qty number);
SCOTT> insert into animal values(1,'Lion',5);
SCOTT> insert into animal values(2,'Tiger',10);
SCOTT> insert into animal values(3,'Rabbit',15);
SCOTT> commit;
Commit complete
SCOTT> select * from animal;
NO NAME QTY
---------- -------------------- ----------
1 Lion 5
2 Tiger 10
3 Rabbit 15
SCOTT> create index animal_name_ix on animal(name);
SCOTT> alter table modify name constraint animal_name_nn not null;
SCOTT> create view v_animal
2 as select no, name
3 from animal;
View Created
SCOTT> select object_name, object_type, status
2 from user_objects
3 where object_name like '%ANIMAL%';
OBJECT_NAME OBJECT_T STATUS
---------------------------------------
ANIMAL TABLE VALID
ANIMAL_NAME_IX INDEX VALID
V_ANIMAL VIEW VALID
SCOTT> SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
2 FROM USER_CONS_COLUMNS
3 WHERE TABLE_NAME LIKE '%ANIMAL';
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
--------------------------------------------
ANIMAL ANIMAL_NAME_NN NAME
h4. Step2. animal 테이블을 삭제한 후 관련 objects들의 상태 확인
SCOTT> drop table animal;
Table dropped
SCOTT> show recyclebin
ORIGINAL_NAME REYCLEBIN_NAME OBJECT_TYPE DROP_TIME
---------------------------------------------------------------------------------------
ANIMAL BIN$~ TABLE 2013-08-16 12:07:22
SCOTT> @stat.sql
OBJECT_NAME OBJECT_T STATUS
---------------------------------------
V_ANIMAL VIEW INVALID
SCOTT> @cons.sql
no rows selected
SCOTT> flashback table animal to before drop;
SCOTT> select * from animal;
NO NAME QTY
---------- -------------------- ----------
1 Lion 5
2 Tiger 10
3 Rabbit 15
SCOTT> @stat.sql
OBJECT_NAME OBJECT_T STATUS
---------------------------------------
V_ANIMAL VIEW INVALID
ANIMAL TABLE VALID
SCOTT> select table_name, index_name, column_name
2 from user_ind_columns
3 where table_name = 'ANIMAL';
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------------------------------
ANIMAL BIN$~~ NAME
SCOTT> alter index "BIN$~~ " rename to animal_name_ix;
Index altered
SCOTT> @stats
OBJECT_NAME OBJECT_T STATUS
---------------------------------------
ANIMAL TABLE VALID
ANIMAL_NAME_IX INDEX VALID
V_ANIMAL VIEW INVALID
SCOTT> drop view v_animal
View dropped
SCOTT> create view v_animal
2 as select no, name
3 from anmal;
View created
SCOTT> @stats
OBJECT_NAME OBJECT_T STATUS
---------------------------------------
ANIMAL TABLE VALID
ANIMAL_NAME_IX INDEX VALID
V_ANIMAL VIEW VALID
SCOTT> @cons
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
------------------------------------------------------
ANIMAL BIN$~~ NAME
SCOTT> alter table animal rename constraint "BIN$~~ " to animal_name_nn
Table altered
SCOTT> @cons
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
--------------------------------------------
ANIMAL ANIMAL_NAME_NN NAME
####################################
# File Configuration
####################################
db_recovery_file_dest = /home/oracle/flash_recovery_area <= 10g 버전일 경우 사용
db_recovery_file_dest = /app/oracle/flash_recovery_area <= 11g 버전일 경우 사용
db_recovery_file_dest_size = 2147483648 <= 10g 버전일 경우 사용, 기본값 2G
db_recovery_file_dest_size = 4196401152 <= 10g 버전일 경우 사용, 기본값 4G
db_flashback_retention_target = 30
SYS> shutdown immediate;
SYS> startup mount
SYS> alter database archivelog;
Database altered
SYS> alter database flashback on;
Database altered
SYS> alter database opne;
SYS> select flashback_on from v$database;
FLASHBACK_ON
---------------
YES
h3. Step1. 테스트용 계정과 테스트용 테이블을 생성한다.
SYS> create user tuser identified by abc123
2 default tablespace user
3 temporary tablespace temp;
User created
SYS> grant connect, resource to tuser;
Grant succeeded
SYS> conn tuser/abc123
Connected
TUSER> create table test1 (no number);
Table created
TUSER> create table test2 (no number);
Table created
TUSER> insert into test1 values(1);
1 row created
TUSER> insert into test1 values(2);
1 row created
TUSER> insert into test2 values(3);
1 row created
TUSER> insert into test2 values(4);
1 row created
TUSER> commit;
h3. Step2. 테스트용 계정 tuser를 삭제한 후 데이터를 확인
TUSER> conn sys/oracle as sysdba
Connected
SYS> drop user tuser cascade;
User dropped
SYS> select * from tuser.test1;
select * from tuser.test1
*
ERROR at line 1:
ORA-00942: table or view does not exist
h3. Step3. Flashback 명령어로 복구
SYS> flashback database to timestamp(systimestamp - interval '5' minute);
flashback database to timestamp(systimestamp - interval '5' minute);
*
ERROR at line 1:
ORA-38757 : Database must be mounted and not open to FLASHBACK
SYS> shutdown immediate
SYS> startup mount;
ORACLE instance started
~
SYS> flashback database to timestamp(systimestamp - interval '10' minute);
Flashback complete
SYS> alter database open;
alter database open*
ERROR at line 1:
ORA-01589 : must use RESETLOGS or NORESETLOGS option for database open
SYS> alter database open resetlogs;
Database altered
SYS> select * from tuser.test1;
NO
---
1
2
h1. 10.2 Flashback Data Archive(11g New Feature)
# Row Level Flashback, Table Level Flashback은 undo data를 활용하며 undo segment가 재사용되면 Flashback 기능 사용불가
# undo segment에 있는 commit된 내용을 특정 테이블 스페이스에 Archive하여 영구적으로 저장하는 기능
!fbda.jpg!
# Flashback Data Archive 특징
## FBDA가 Undo segment의 내용을 모두 기록하기 전에는 해당 undo segment는 재활용되지 않는다
## 최대 10개까지의 FBDA 백그라운드 프로세스가 동시에 작업 가능
## FBDA 프로세스는 특정 시간이 되면 자동으로 활성화되어 undo segment의 내용을 저장 ( default : 5분)
## 해당 데이터는 자동으로 파티셔닝되어 저장
## Retention time을 설정하여 데이터를 관리하며 retention time이 지난 데이터는 자동으로 삭제
## Insert 되는 데이터는 이 기능을 사용하지 않음
# Flashback Database Archive 활성화하기
## Flashback history table을 저장할 테이블 스페이스를 생성
## Flashback data archive를 관리할 관리자 계정 생성
## 관리자 계정에 권한 할당
## 관리자 계정으로 로그인 한 후 flashback history table 생성
{code:SQL}
SYS> CREATE TABLESPACE TS_FDA01
2 DATAFILE '/app/oracle/oradata/testdb/ts_fda01.dbf' size 5M;
SYS> create user fbadmin identified by fbpwd
2 default tablespace ts_fda01;
SYS> grant resource, connect to fbadmin;
Grant succeeded
SYS> grant flashback archive administer to fbadmin;
Grant succeeded
* flashback archive administer
1. Flashback Data Archive를 관리하기 위한 특별한 시스템 권한
2. Flashback History Table을 생성하고 관리
3. 테이블 스페이스를 추가하거나 삭제할 수 있다
4. Flashback History Table Retention time을 관리
SYS> conn fbadmin/fbpwd
Connected
FBADMIN> create flashback archive fda01 tablespace ts_fda01
2 retention 30 day;
Flashback archive created
SYS> select owner_name, flashback_archive_name, retention_in_days, status
2 from dba_flashback_archive;
SCOTT> create undo tablespace undo_fda
2 datafile '/app/oracle/oradata/testdb/undo_fda01.dbf size 256k;
Tablespace created
SCOTT> alter system set undo_tablespace = undo_fda;
System altered
SCOTT> alter system set undo_retention = 3; (second)
SCOTT> create table test01(no number, name varchar2(10));
Table created
SCOTT> insert into test01 values(1,'AAA');
1 row created
SCOTT> insert into test01 values(2,'BBB');
1 row created
SCOTT> insert into test01 values(3,'CCC');
1 row created
SCOTT> commit;
Commit complete
SCOTT> select * from test01
NO NAME
------------
1 AAA
2 BBB
3 CCC
SCOTT> update test01 set name = 'DDD'
3 rows updated
SCOTT> conn sys/oracle as sysdba
Connected
* 사용중인 undo segments 정보 확인
SYS> select segment_name, owner, tablespace_name, status
2 from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE STATUS
-------------------- ------------ ---------- --------------------------------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1_2329185666$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2_1745259924$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3_711839592$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4_317910858$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5_3027368052$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6_1174846085$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7_4115546942$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8_1822178776$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9_2800287069$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU10_1114372776 PUBLIC UNDOTBS1 OFFLINE
_SYSSMU13_3939341484$ PUBLIC UNDO_FDA ONLINE
_SYSSMU11_484445380$ PUBLIC UNDO_FDA ONLINE
SYS> select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollback r
3 where s.taddr = t.addr
4 and t.dixusn = r.usn;
SID SERIAL# USERNAME ROLLBACK SEG
-----------------------------------------------------
1 17 SCOTT _SYSSMU13_3939341484$
SYS> commit;
Commit complete
** 다른 사용자(userb) 생성 후 대량의 DML문을 발생시켜 위 undo segment를 재활용하도록 함
SYS> create user userb identified by userb
2 default tablespace example;
User created
SYS> grant connect, resource to userb;
Grant succeeded
SYS> conn userb/userb
Connected
USERB> create table test02(no number, name varchar2(20));
Table created
USERB> begin
2 for i in 1 .. 1000 loop
3 insert into test01 values(i,'Overwrirte');
4 end loop;
5 end; <= Commit 안함
USERB> select count(*) from test02;
COUNT(*)
--------
1000
USERB> conn sys/oracle as sysdba
Connected
SYS> select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollback r
3 where s.taddr = t.addr
4 and t.dixusn = r.usn;
SID SERIAL# USERNAME ROLLBACK SEG
-----------------------------------------------------
1 15 SCOTT _SYSSMU13_3939341484$
* SCOTT 사용자가 사용했던 _SYSSMU13_3939341484$ segment 를 사용중
USERB> begin
2 for i in 1 .. 2000 loop
3 insert into test01 values(i,'Overwrirte');
4 end loop;
5 end;
USERB> conn sys/oracle as sysdba
Connected
SYS> select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollback r
3 where s.taddr = t.addr
4 and t.dixusn = r.usn;
SID SERIAL# USERNAME ROLLBACK SEG
-----------------------------------------------------
1 15 SCOTT _SYSSMU11_484445380$
USERB> conn scott/tiger
Connected
SCOTT> flashback table test01 to timestamp(systimestamp - interval '5' mnute);
flashback table test01 to timestamp(systimestamp - interval '5' mnute)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P000
ORA-01555: snapshot too old: rollback segment number 11 with name "_~
too small
h2. 실습 3-2. Flashback data Archive 사용하여 DML 복구 시도하기
h3. Step 1. 현재 flashback data archive 상태를 확인
SYS> select owner_name, flashback_archive_name, retention_in_days, stats
2 from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME RETETION_IN_DAYS STATUS
----------------------------------------------------------------------
FBADMIN FDA01 30
h3. Step 2. Scott 사용자로 test3 테이블을 생성하며 FDA를 FDA01을 사용하도록 설정
SYS> conn scott/tiger
Connected
SCOTT> create table test3(no number, name varchar2(10)) flashback archive fda01;
create table test3(no number, name varchar2(10)) flashback archive fda01
*
ERROR at line 1:
ORA-55620:No privilege to use Flashback Archive
SCOTT> conn sys/oracle as sysdba
Connected
SYS> grant flashback archive on fda01 to scott;
Grnat succeeded
SYS> conn scott/tiger
Connected
SCOTT> create table test3(no number, name varchar2(10)) flashback archive fda01;
Table created;
SCOTT> insert into test01 values(1,'AAA');
1 row created
SCOTT> insert into test01 values(2,'BBB');
1 row created
SCOTT> insert into test01 values(3,'CCC');
1 row created
SCOTT> commit;
Commit complete
h3. Step 3. Update 장애 발생시킴
SCOTT> update test01 set name = 'DDD'
3 rows updated
h3. Step 4. 다른 터미널에서 scott사용자의 undo segment 사용을 확인
SCOTT> conn sys/oracle as sysdba
Connected
SCOTT> @roll
SID SERIAL# USERNAME ROLLBACK SEG
-----------------------------------------------------
1 23 SCOTT _SYSSMU13_3939341484$
h3. Step 5. Userb 계정으로 test04 생성 후 undo segments 재사용
USERB> create table test04(no number, name varchar2(20));
Table created
USERB> begin
2 for i in 1 .. 1000 loop
3 insert into test04 values(i,'No name');
4 end loop;
5 end; <= Commit 안함
USERB> conn sys/oracle as sysdba
Connected
SCOTT> @roll
SID SERIAL# USERNAME ROLLBACK SEG
-----------------------------------------------------
1 23 SCOTT _SYSSMU13_3939341484$
USERB> begin
2 for i in 1 .. 1000 loop
3 insert into test04 values(i,'No name');
4 end loop;
5 end;
h3. Step 6. Scott 계정에서 fashback table 명령어로 test3 테이블을 복구
SCOTT> alter table test3 enable row movement;
Table altered
SCOTT> flashback table test3 to timestamp(systimestamp - interval '5' minute);
Flashback complete
SCOTT> select * from test01
NO NAME
------------
1 DDD
2 DDD
3 DDD
SCOTT> flashback table test3 to timestamp(systimestamp - interval '7' minute);
Flashback complete
SCOTT> select * from test01
NO NAME
------------
1 AAA
2 BBB
3 CCC
SCOTT> drop table test2;
drop table test3
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SCOTT> alter table test3 no flashback archive;
Table altered
SCOTT> drop table test3;
Table droppd
SYS> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ ARCHIVE_TABLE_NAME STATUS