단다니의 집

출처 : http://blog.bagesoft.com/693

오라클 현재 접속(세션)상태 확인

select * from v$resource_limit

결과에서 processes 가 프로세스 갯수(백그라운드+Dedicate Process)
session이 동시접속 세션 수 입니다.
current_utilization 이 현재 접속 카운트이고... (1명 접속할 때 마다 1씩 증가)
max_utilization  이 오라클을 시작한 이래로 최대로 접속했을 때 피크 수치입니다. processes 파라미터도 이 값을 근거로 어느 정도 여유를 두고 설정하면 됩니다.

initial_allocation 은...  init.ora 파라미터에서 설정한 수치입니다.
만약 max_utilization 이 limit_value에 근접한다면 processes 를 늘려주어야 하겠지요.



오라클 접속 가능수(세션수) 변경

alter system set processes = 200 scope=spfile;

출처 : http://articles.techrepublic.com.com/5100-10878_11-5259821.html

CREATE OR REPLACE function join
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end join;

create or replace type split_tbl as table of varchar2(32767);

CREATE OR REPLACE function split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;

SELECT   count(*)

       FROM v$session s

       WHERE s.username IS NOT NULL

          AND NVL (s.osuser, 'x') <> 'SYSTEM'

          AND s.TYPE <> 'BACKGROUND'; 

 ◈ 세션에 필요한 정보를 보는 SQL 

SELECT   /*+ rule */

         s.status "Status", s.serial# "Serial#", s.TYPE "Type",

         s.username "DB User", s.osuser "Client User", s.server "Server",

         s.machine "Machine", s.module "Module", s.terminal "Terminal",

         s.program "Program", p.program "O.S. Program",

         s.logon_time "Connect Time", lockwait "Lock Wait",

         si.physical_reads "Physical Reads", si.block_gets "Block Gets",

         si.consistent_gets "Consistent Gets",

         si.block_changes "Block Changes",

         si.consistent_changes "Consistent Changes", s.process "Process",

         p.spid, p.pid, s.serial#, si.sid, s.sql_address "Address",

         s.sql_hash_value "Sql Hash", s.action

FROM v$session s, v$process p, sys.v_$sess_io si

WHERE s.paddr = p.addr(+)

     AND si.sid(+) = s.sid

     AND s.username IS NOT NULL

     AND NVL (s.osuser, 'x') <> 'SYSTEM'

     AND s.TYPE <> 'BACKGROUND'

ORDER BY 3 

출처 : http://javaiyagi.tistory.com/entry/oracle-TABLESPACE-%EC%83%9D%EC%84%B1

▣ 테이블스페이스의 생성

   

     

▣ 테이블스페이스의 자동 확장


▣ 생성한 데이터파일이 다 채워졌을 경우 자동으로 데이터 파일을 확장하는 방법 입니다.

▣ AUTOEXTEND 옵션을 사용 하시면 됩니다.

▣ AUTOEXTEND 옵션 사용법
   - ON 아니면 OFF
   - CREATE DATABASE, CREATE TABLESPACE, ALTER TABLESPACE에서
     사용 할 수 있습니다.

▣ AUTOEXTEND 사용 예제

SQL> ALTER TABLESPACE storm
          ADD DATAFILE 'C:\ORACLE\ORADATA\app_data02.dbf' SIZE 200M
          AUTOEXTEND ON NEXT 10M
          MAXSIZE 500M;

storm 테이블스페이스에 200M 데이터 파일을 추가 했는데요, 
이 데이터 파일의 크기인 200M를 전부 사용하게 되면 500M이 될 때까지 자동으로  10M 증가합니다.



▣ AUTOEXTEND 의 OFF

 SQL>  ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\app_data02.dbf'
                 AUTOEXTEND OFF;

App_data02.dbf파일의 자동확장을 중지 시킵니다.    

출처 : http://yoonow.tistory.com/10

yum? 보통 레드헷 리눅스에 기본적으로 들어 있는 패키지 관리 프로그램이라 보면 될것이다

=================== yum 기본 사용법 ===================
1. 업데이트 목록 확인

# yum check-update
=> 현재 인스톨된 프로그램 중에 업데이트 되어야 할것을 알려준다!

# yum list
=> 서버에 있는 프로그램 목록 싸그리다나온다! (grep을 이용해서 쓰고픈 프로그램을 찾는다 ex: yum list | grep mysql)

# yum list installed
=> 컴에 설치되어 있는 프로그램이 나온다!


2. 업데이트

# yum update [package1] [package2]
=> package1,2를 안쓰면 필요한거 싹다 업데이트 해 버린다

# yum install package1 [package2]
=> 말 그대로 인스톨!


3. 패키지 제거

# yum remove package1 [package2]
=> 말 그대로 제거

4. 패키지 정보

# yum info package1
# yum info updates
=> 업데이트 서버에 있는 패키지 정보

=================== yum group 사용법 ===================

오늘 하면서 알아낸 group 의 사용법이다.
귀차니즘이 많은 분에게 필수! 아주 편하다 많은 패키지가 동시에 설치 되지만
귀차니즘을 제거해주는 -_-;;

# yum groupinstall mysql
=> 이런식으로 하면 mysql관련된 라이브러리, 서버, 클라이언트 다 깔린다 -_-;
꽤나 많이 깔리기때문에, 의존성에 짜증나면 이걸로 한번에 처리하는것도 괜찮은듯

# yum groupremove mysql
=> 이런식으로 싹다 지울수도 있어서 편하다 ㅋ

http://werty4082.springnote.com/pages/1565126

1.flashback query :  특정시점의 모든 데이터를 query하여 현재 변경된 값 이전의 row 데이터를 가져온다.

2.flashback version query : 특정한 두 시점 사이에 발생한 스키마 objects및
row 데이터의 변경 이력을 보여줌으로써 rows를 변경한 트랙잭션을 알 수 있다.

3.flashback trasaction query : 특정 트랜잭션에 의해 변경된 모든 내역을 보여준다.

Flashback Query

이 기능은 9i에서 소개된 flashback 최초의 개념이다. 특정 시점의 지정한 데이터 전체를 query하는 기능이다.
특정 시점으로 돌아갈수는 있지만 DML을 할수 없는등 여러가지 불편한 점이 많았다.
이러한 불편한점을 10g에서 새로나온 아래에서 소개할 다른 타입의 query를 통해 해소할 수 있다.

Flashback Version Query

특정 시간(time) 또는 시점(scn)사이의 모든 row값을 조회함으로서 변경 히스토리를 알 수 있게 해준다.
이 기능은 commit된 데이터만 추출함으로 불필요한 rollback transaction을 제거할 수 있다.

external table, temporary table, fixed table, cluster 일부, view를 대상으로는 사용할 수 없다.
DDL로 데이블 구조가 바뀐 경우에는 DDL수행 이전의 데이터는 version query를 사용할 수 없다.
view를 통해 "create view view_name as select version query"는 가능하다

Flashback Transaction Query

위의 flashback version query를 통하여 히스토리를 파악할 수 있고 그 정보를 바탕으로 10g가 제공하는

system view인 flashback_transaction_query를 이용하면 트랜잭션 단위별로 더 자세한 정보를 쉽게 알수 있다.

Version Query Example

case1

SQL> conn scott/tiger
Connected.
SQL> create table pse_version as select * from dept;

Table created.

SQL> select * from pse_version;

DEPTNO     DNAME         LOC
---------- -------------- -------------
10            ACCOUNTING   NEW YORK
20            RESEARCH      DALLAS
30            SALES             CHICAGO
40            OPERATIONS    BOSTON


SQL> update pse_version set dname='PARK' where deptno='10';

1 row updated.

SQL> delete from pse_version where deptno='20';

1 row deleted.

SQL> select to_char(sysdate, 'HH24:MI:SS') from dual;

TO_CHAR(
--------
07:49:35

SQL> commit;

Commit complete.


SQL>
select versions_startscn st_scn, versions_endscn endscn, versions_xid txid, versions_operation opt
  , dname
from pse_version
  versions between scn minvalue and maxvalue
where deptno='10' or deptno='20';

ST_SCN      ENDSCN    TXID                     O  DNAME
---------- ---------- ----------------      - --------------
521809                        05002C0030010000    D  RESEARCH
521809                        05002C0030010000    U  PARK
                 521809                                       ACCOUNTING
                 521809                                       RESEARCH

case2

 SQL> select * from pse_version;

DEPTNO      DNAME           LOC
---------- -------------- -------------
10             PARK              NEW YORK
30             SALES            CHICAGO
40            OPERATIONS    BOSTON

SQL> update pse_version set dname='LEE'
2 where deptno='40';

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from pse_version where deptno='40';

1 row deleted.

SQL> commit;

Commit complete.


SQL> select versions_startscn st_scn, versions_endscn endscn,
2 versions_xid txid, versions_operation opt, dname
3 from pse_version
4 versions between scn minvalue and maxvalue
5 where deptno='40';

ST_SCN     ENDSCN    TXID                  O   DNAME
---------- ---------- ---------------- -   --------------
522486                       0600120019010000  D  LEE
522472        522486      01002D00E2000000 U  LEE
                 522472                                    OPERATIONS

위의 결과를 설명하면 OPERATIONS의 이름이 SCN 522472까지만 유효하다는 것이고

522472시점에 LEE란 이름으로 업데이트 되었고 522486까지 LEE가 유효하다는 것이고

522486시점에 LEE가 Delete되었다는 정보를 알 수 있다.

SQL> select scn_to_timestamp(522486) from dual;

SCN_TO_TIMESTAMP(522486)
---------------------------------------------------------------------------
30-OCT-07 08.06.27.000000000 AM



SQL> select current_scn, timestamp_to_scn(systimestamp) from v$database;

CURRENT_SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP)
----------- ------------------------------
523251 523250

위와같이 conversion Function이 추가됨으로 인해.. SCN과 Timestamp 간의 변환으로 자유롭게 질의를 수행할 수 있다.

Flashback_Transaction_Query

이 view를 query하기 위해서는 system권한인 "select any transaction" 권한이 필요하다.
또한 flashback transaction query를 통해서 더 자세한 정보를 보기 위해서는
(chained rows 혹은 cluster 테이블등의) 데이터베이스 상태가 "supplemental log" 를 enable한 상태이어야 한다.

SQL> alter database add supplemental log data;

Transaction Query Limit
1. DDL은 dictionary update의 형식으로 나타난다.
2. IOT의 key컬럼 update는 insert, delete처럼 나타난다.
3. drop된 테이블은 object number로 표시된다.
4. drop된 계정은 username이 아니라 user ID로 표시된다.

Flashback Transaction Query Example

 시나리오 상황 사용자가 실수를 하여 데이터를 원래 상태로 되돌려 달라고 요청한 상태이다.

SQL> conn sys/oracle as sysdba
Connected.
SQL> grant select any transaction to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create table tx_query as select * from pse_version;

Table created.

SQL> select * from tx_query;

DEPTNO DNAME LOC
---------- -------------- -------------
10 PARK NEW YORK
30 SALES CHICAGO


SQL> update tx_query set dname='JANG'
2 where deptno='10';

1 row updated.

SQL> commit;

Commit complete.

SQL> update tx_query set loc='WEST'
2 where deptno='10';

1 row updated.

SQL> commit;

Commit complete.

실상황에서는 매우 복잡할것 이므로.. 먼저 아래와 같이 versions query를 질의하여 트랜잭션 이력을 알아본다.

그후에 그 이력을 통하여 transaction query를 이용하여 복구하도록 한다

SQL> select versions_startscn st_scn, versions_endscn endscn,
2 versions_xid txid, versions_operation opt, dname, loc
3 from tx_query
4 versions between scn minvalue and maxvalue
5 where deptno='10';

ST_SCN      ENDSCN    TXID                   O    DNAME          LOC
---------- ---------- ----------------   - -------------- -------------
523757                        0500090032010000  U    JANG            WEST
523675         523757      07000200F6000000  U    JANG            NEW YORK
                  523675                                      PARK           NEW YORK

위의 결과를 통해 트랙잭션 이력을 확인 하였다. 이를 바탕으로 transaction query 를 실행하자

SQL> select undo_sql from flashback_transaction_query
2 where table_name = 'TX_QUERY'
3 and commit_scn between 523675 and 523757
4 order by start_timestamp desc;

UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."TX_QUERY" set "LOC" = 'NEW YORK' where ROWID = 'AAAM0iAAEAAAAHUAAA';

update "SCOTT"."TX_QUERY" set "DNAME" = 'PARK' where ROWID = 'AAAM0iAAEAAAAHUAAA';

위와같이 복구할 수 있는 SQL문이 출력되었다.

desc로 출력하였으므로 위에서부터 아래로 차례대로 적용하면 복구된다.

SQL> update "SCOTT"."TX_QUERY" set "LOC" = 'NEW YORK' where ROWID = 'AAAM0iAAEAAAAHUAAA';

1 row updated.

SQL> update "SCOTT"."TX_QUERY" set "DNAME" = 'PARK' where ROWID = 'AAAM0iAAEAAAAHUAAA';
1 row updated.

SQL> select * from tx_query;

DEPTNO         DNAME         LOC
---------- -------------- -------------
10                  PARK           NEW YORK
30                  SALES         CHICAGO

SQL> commit;

Commit complete.

 정상적으로 복구 되었음을 확인할 수 있다.

 Flashback Drop

SQL> flashback table table_name to before drop [rename to new_name];

flashback drop이 되면 대상 테이블에 종속된 "dependent objects"도 동시에 같이 복구되며
이때 object 이름은 오라클이 자동생성한 이름을 사용하게 되므로 미리 그 이름을 확인하여
복구가 끝난후 적절한 이름으로 바꾸어줄 필요가 있다.
(단 dependent objects가 dictionary management tablespace에 속해 있는 것은 상관없다
이들은 모두 recycle의 보호 대상이다.)

다음과 같은 dependent objects는 flashback drop과 동시에 같이 복구되지 않는다.
1.bitmap-join indexes
2.materialized view logs
3.referential integrity constraints
4.table이 drop되기 이전에 먼저 drop된 indexes

굳이 recycle bin 기능을 사용하고 싶지 않다면
히든파라메터인 "_recyclebin=false"를 설정하면 된다.

Flashback Drop Example

SQL> create user pse identified by pse;

User created.

SQL> grant connect,resource to pse;

Grant succeeded.


SQL> conn pse/pse
Connected.
SQL> create table test01 (col1 number);

Table created.

SQL> create table test02 (rcol1 number);

Table created.

SQL> insert into test01 values (1);

1 row created.

SQL> insert into test02 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test01;

Table dropped.

SQL> drop table test02;

Table dropped.

SQL> create table test01 (col2 number);

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$PZ0L3qzABNfgQAB/AQB0vw==$0 TABLE
BIN$PZ0L3qy/BNfgQAB/AQB0vw==$0 TABLE
TEST01 TABLE


SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST01 BIN$PZ0L3qy/BNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:45:30
TEST02 BIN$PZ0L3qzABNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:45:35
SQL> select original_name, ts_name, can_undrop
2 from user_recyclebin;

ORIGINAL_NAME TS_NAME CAN
-------------------------------- ------------------------------ ---
TEST01 USERS YES
TEST02 USERS YES


SQL> insert into test01
2 select * from "BIN$PZ0L3qy/BNfgQAB/AQB0vw==$0";

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test01;

Table dropped.

SQL> create table test01 (col3 number);

Table created.

SQL> select original_name, ts_name, can_undrop
2 from user_recyclebin;

ORIGINAL_NAME TS_NAME CAN
-------------------------------- ------------------------------ ---
TEST01 USERS YES
TEST02 USERS YES
TEST01 USERS YES


SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST01 BIN$PZ0L3qzCBNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:56:01
TEST01 BIN$PZ0L3qy/BNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:45:30
TEST02 BIN$PZ0L3qzABNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:45:35
SQL> flashback table test01 to before drop rename to testxxx01;

Flashback complete.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST01 BIN$PZ0L3qy/BNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:45:30
TEST02 BIN$PZ0L3qzABNfgQAB/AQB0vw==$0 TABLE 2007-10-29:15:45:35

위에서 LIFO 즉, 가장 마지막에 드롭된 테이블부터 복구되는것을 확인할 수 있다


SQL> flashback table test01 to before drop rename to testxxx02;

Flashback complete.

SQL> purge table test02;

Table purged.


SQL> select original_name, ts_name, can_undrop
2 from user_recyclebin;

no rows selected


rename절을 사용했기때문에 복구하면서 중복에러는 발생하지 않았다.


제대로 복구가 되었는지 확인해보고 dependent object의 복구 테스트를 해보자

SQL> desc testxxx01;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL3 NUMBER

SQL> desc testxxx02;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NUMBER




SQL> create index test_idx on testxxx01 (col3);

Index created.

SQL> drop table testxxx01;

Table dropped.

SQL> col org for a10
SQL> col type for a10
SQL> select original_name org, object_name obj,
2 type, can_undrop from user_recyclebin;

ORG OBJ TYPE CAN
---------- ------------------------------ ---------- ---
TEST_IDX BIN$PZ0L3qzDBNfgQAB/AQB0vw==$0 INDEX NO
TESTXXX01 BIN$PZ0L3qzEBNfgQAB/AQB0vw==$0 TABLE YES


SQL> flashback table testxxx01 to before drop;

Flashback complete.

SQL> select original_name org, object_name obj,
2 type, can_undrop from user_recyclebin;

no rows selected

SQL> select index_name, status from user_indexes
2 where table_name='TESTXXX01';

INDEX_NAME STATUS
------------------------------ --------
BIN$PZ0L3qzDBNfgQAB/AQB0vw==$0 VALID

SQL> alter index "BIN$PZ0L3qzDBNfgQAB/AQB0vw==$0" rename to test_idx;

Index altered.


SQL> select index_name, status from user_indexes
2 where table_name='TESTXXX01';

INDEX_NAME STATUS
------------------------------ --------
TEST_IDX VALID


dependent object는 recycle bin에 있는 오라클이 자동생성한 이름으로 복구되므로 그 이름을 바꾸어주는 작업이 필요하다.

Flashback Table

flashback 명령 자체가 하나의 트랜잭션으로 처리되기 때문에 모두 flashback이 되던지 아니면 모두 실패하게 된다.
flashback version query와 trasaction query를 이용하여 flashback table의 시점을 알맞게 결정할 수 있다.

command syntax

SQL> flashback table table_name [,table lists]
               to {SCN|TIMESTAMP} value
               [enable|disable triggers];

위 syntax에서 triggers의 기본값은 disable이다.
triggers를 enable로 지정하면 flashback을 진행하는 동안 지정된 테이블의 trigger는
그대로 유지디지만 disable을 지정하면 모든 trigger를 disable시키고 flashback을 진행한다음
flashback이 끝나면 다시 trugger를 자동으로 enable시킨다.

Flashback Table의 유의사항

1. 단일 트랜잭션으로 처리됨으로 지정된 테이블에 대한 flashback은 모두 성공하던지
아니면 모두 실패하며 flashback table 명령 자체를 롤백할 수 없다.

2.flashback이 진행되는 동안 exclusive DML lock을 획득한다.

3.통계정보는 flashback되지 않는다.

4.flashback이 진행되는 동안 dependent indexes는 모두 그대로 유지되지만 drop된 index가 다시 만들어지지는 않는다.

5.지정한 table에 대하여 "on-commit materialized view"가 있어도 자동으로 유지된다.

6.flashback table 명령어가 수행되면 alert.log에 기록된다.

7.대상 테이블의 constraints가 flashback이 진행되는 동안 위반이되면(violation을 일으키면)
모든 작업은 중단되고 작업 전 상태로 원상복구 된다.

8.대상 테이블에 대한 DDL 작업이 있었다면 이 시점보다 더 이전의 시점으로 flashback을 하는것은
불가능하다. 단, 그 DDL이 스토리지 속성을 변경하는 것이었다면 상관없다.

9.flashback table을 통해 데이터를 복구하기 위해서는 대상 테이블의 "row movement"속성을 enable 해주어야 한다.

10.flashback table 명령은 다음과 같은 object를 대상으로 할 수 없다.
cluster, materialized view, AQ table, static data dictionary talbes(fixed tables), system tables, remote tables

Flashback Table Example

SQL> conn sys/oracle as sysdba
Connected.
SQL> grant select on v_$database to pse;

Grant succeeded.

SQL> conn pse/pse
Connected.
SQL> create table fbtest (col1 number);

Table created.

SQL> insert into fbtest values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
509310


SQL> insert into fbtest values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
509354

SQL> delete from fbtest;

2 rows deleted.

SQL> commit;

Commit complete.


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
509396

SQL> select * from fbtest;

no rows selected

가끔씩 사용자가 실수로 where절이 없는 delete를 사용하는 경우가 있다. 이경우처럼
이 상황에서 사용자로부터 데이터가 1만 있었던 시점으로 돌아가고 싶다는 요청을 받았다.


SQL> flashback table fbtest to scn 509310;
flashback table fbtest to scn 509310
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table fbtest enable row movement;

Table altered.

SQL> flashback table fbtest to scn 509310;

Flashback complete.

SQL> select * from fbtest;

COL1
----------
1

그런데 변덕스런 고객이 갑자기 delete 된 상태로 다시 돌아가고 싶다고 한다면


SQL> flashback table fbtest to scn 509396;

Flashback complete.

SQL> select * from fbtest;

no rows selected


실제 상황에서는 SCN번호를 알고 있는 경우가 적으므로 timestamp를 이용하는 경우가
더 많을것이다.

timestamp를 이용하는 경우 interval type을 이용하면 좀 더 쉽게 활용할수 있다.

예를들어 사용자가 실수를 해서 실수했다고 전화가 왔다. 그 사람에게 언제 그랬냐고
그러면 대충 5~6분정도 지난거 같다고 할것이다. 이런 경우 아래 방법으로 여유있게
8분정도의 이전 시점으로 돌아가는것도 괜찮다.


SQL> flashback table fbtest to timestamp
2 (systimestamp - interval '8' minute);

Flashback complete.


SQL> select * from fbtest;

COL1
----------
1

Undo Retention

이러한 flashback 기법들은 undo segment로부터 데이터를 추출하는 기능이다.
때문에 "automatic undo management"방식을 선택하고 undo tablespace의 크기,
undo retention time의 적절한 설정이 있어야만 flashback 기능을 제대로 활용할수 있다.

undo 데이타를 보존하는 시간인 undo_retention 값은 최소 보존 예측시간이지 보장시간이 아니다.
때문에 언제든지 undo 공간이 모자르면 undo retention time은 지켜지지않고 덮어쓰기 되어버린다.
이 말은 항상 이 시간만큼 flashback이 유효하다는 보장이 없다는 것이다.

10g에서는 이러한 문제점을 방지하기 위해 새로운 undo tablespace속성이 나왔다.
"retention guarantee"를 설정하면 undo 공간이 부족하더라도 undo retention time을 보장하는 설정을 지원한다.

 SQL> select tablespace_name,retention
2 from dba_tablespaces;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
FRA_TBS NOT APPLY


SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name, retention
2 from dba_tablespaces;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 GUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
FRA_TBS NOT APPLY

7 rows selected.

 위의 결과를 통해 오로지 undo tablespace에만 한해서 retention guarantee 옵션이 사용됨을 알 수 있다.

하지만 역시.. 모든 새로운 기능을 설정할때는 부작용을 고려해 보아야 한다.

"retention guarantee"를 잘못 설정하면 다른 DML들이 undo 공간에 값을 못남기는 최악의 상황이 일어날수도 있다.

기간이 지난넘들은 자연히 dbwr에 의해 내려 써져서 공간을 반납하므로 순환되야 하는데..

"retention guarantee"값으로 인해 고정이 되버리니 말이다.

처음 적용시 가장 좋은 방법은 undo tablespace 공간을 확보한후 automatic undo management를 채택하고 여러분이 원하는

최대한의 시간만큼 undo retention time을 계산한 후 undo tablespace를 "retention guarantee"로 만드는 것이다.

물론 처음에는 적절하지 않은 설정일것이다. 이것은 반복적인 운영을 통해 undo space 에러 시점과 undo space 양을

계속 살펴보면 최적의 적절한 양이 보일것이다!!!!

Flashback Database

논리적 오류가 아닌 물리적으로 파일에 문제가 생긴 경우에는 Flashback Database를 할 수 없고 전통적인 복구를 해야한다.

10g에서 이 기능을 이용하기 위해서는 새롭게 제공되는 log인 flashback database log가 있어야 한다.
이 log는 주기적으로 data block의 before image를 저장하는데 나중에 원하는 시점으로 datafile을 back out할때
재 사용이 가능한 형태로 기록된다. flashback database log는 따로 관리 해 줄 필요가 없고
이를 저장하는 영역인flashback recovery area의 공간만 고려 대상이다.
즉 flashback database log는 flashback recovery area 안에서 자동으로 생성,삭제,관리된다.^^

정리하면 flashback log를 담을 수 있는 SGA 내의 flashback buffer와 flashback 가능 시간을 지정하는 패라메터를 설정하고
flashback을 enable 시켜야 한다. 일단, flashback이 enable되면 새로운 background 프로세스인 recovery writer(RVWR)가 활성화 되고
이 프로세스가 SGA로부터 undo 블록을 담고 있는 flashback buffer의 내용을 flashback log에 내려쓴다.
이것은 buffer cache로부터 모든 변경 사항을 담고있는 redo log buffer를 LGWR가 redo log에 내려쓰는것과 거의 똑같다.

RVWR 백그라운드 프로세스

플래시백 데이터베이스

- 이 기능은 Oracle 10g에 새로 도입된 기능이다.
- 데이터베이스를 flashback ㅣ킨다는 것은, 데이터베이스를 이전 시점의 상태로 되돌리는 것을 의미한다.
- 플래시백 데이터베이스 기능은, 오라클 데이터베이스 전체를, 과거 특정 시점으로 되돌리는 신속한 방법을 제공해 준다.
- 이것은 이전의 point in time recovery와는 다른 것이다.
- 이것을 위해 background process Recovery Writer (RVWR) 프로세스가 추가되었는데 이것은, 데이터블럭의 pre-image를
저장하는, flashback log를 기록하는 역할을 수행한다.
- Flashback Database를 사요아여 다음과 같은 작업을 수행할 수 있다 :
- 논리적인 데이터 corruption의 추적
- 사용자의 실수로 인한 작업의 회복
- 이 기능은, media failure 발생시 데이터베이스를 복구하는데는 사용할 수 없다.
- 데이터베이스를 과거 특정 시점으로 flahback 시키는데 걸리는 시간은, 변경이 된 건수에
직접적으로 영향을 받으며, 데이터베이스의 크기와는 무관하다.

Flashback Database의 일반적인 사항

1.전통적으로 대용량 데이터베이스에서 log buffer를 최소 8MB로 설정하는 것이 일반적이다.
이 경우 flashback database를 구성하면 flashback buffer는 이의 두 배인 16MB로 설정된다.

=================================== 참 고 =======================================
아 래 글은 내가 참여한 Oracle Forum의 Thread 중 하나로 Redo Buffer에서의 경합에 대해서 다루고 있다. 영어로 외국인들과 이런 문제를 논의한다는게 쉽지 않지만, 일부러 시간을 내서 참여하곤 한다. 한국의 위상(?)을 알리고자 하는 것도 있고, Oracle의 본토인 미국에서는 주로 어떤 문제를 가지고 싸움들을 하는지 알고 싶은 마음도 있다.

최 초의 질문은 "ADDM 리포트에 보니까 Redo Buffer 크기를 32M로 늘리라고 되어 있는데, 이게 맞는 말인가?"에서 시작한다. 그 후 log file sync, log file switch completion, log buffer space와 같은 대기 현상에 대해서 각자의 생각을 이야기하고, 그 와중에서 약간의 싸움도 있다.

이 런 싸움이 생기는 결정적인 이유는 Redo Buffer 크기가 어느 정도가 적합한가에 대해 통일된 가이드가 없기 때문이다. Redo Buffer의 Redo Record는 1M 이상 할당되면 LGWR에 의해 자동 Write가 된다. 따라서 1M를 넘는 Redo Buffer 크기는 무의미하다는 의견이 있고, 많은 문서에서 이런 사실을 밝히고 있다.

하 지만, 동시에 많은 프로세스가 Redo Buffer를 사용하게 되면 Buffer 공간이 모자란 현상이 생길 수 있다. 이 때는 log buffer space 이벤트가 발생한다. 이런 경우에는 Redo Buffer를 늘려주어야 한다. 아마 ADDM이 Redo Buffer 크기를 키우라는 가이드를 했을 때는 이런 사실에 근거를 두었을 것이다.

또 한가지 재미있는 사실은 10gR2 부터는 log_buffer 파라미터로 지정한 것보다 실제 Redo Buffer의 크기가 크게 할당된다는 것이다. 10gR2부터는 fixed SGA area와 Redo Buffer가 통합되었고, 이로 인해 fixed SGA 영역에서 쓰고 남는 프리 영역이 Redo Buffer로 할당되기 때문이다. SGA의 메모리 단위는 Granule인데(4~ 16M 사이), 만일 16M 크기의 Granule에서 2M를 쓰고 14M가 남았다면 이 공간을 Redo Buffer가 쓰게 된다.

아래에 실 시스템에서의 예가 있다.

log_buffer=50M
Total System Global Area 734003200 bytes
Fixed Size 2075656 bytes
Variable Size 184550392 bytes
Database Buffers 490733568 bytes
Redo Buffers 56643584 bytes

log_buffer를 50M로 지정했지만, 실제 Redo Buffer 크기는 54M인것을 확인할 수 있다.

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

위의 내용은 OWI저자인 욱짜님 블로그 내용을 참고한 것이다.

2.SGA에서 undo 블록을 flashback buffer로 옮기는 작업을 대략 10분마다 이루어진다.

3.flashback database를 구성하려면 해당 데이타베이스는 archive log mode여야 한다.

4.flashback database 명령을 사용하여 특정 시점으로 데이타베이스를 되돌릴 때에는 반드시 mount exclusive 상태에서 작업을 진행해야 한다.
이것도 recovery이기 때문에 다른 사용자가 데이터베이스에서 어떤 작업도 진행하면 안되기 때문이다.

5.flashback database 명령은 RMAN에서도 가능하다.

6.flashback database log는 query 부분은 다루지 않음으로 데이타베이스의 쓰기 작업을 하는 애플리케이션이
얼마나 많은가 만이 이 log의 overhead에 영향을 준다.

flashback database parameter

db_recovery_file_dest=/app/oracle/recovery_area
db_recovery_file_dest_size=20G
db_flashback_retention_target=1440  #1day

Open Database with Flashback Mode

위의 파라메터를 설정한 다음에 archivelog mode인 상태에서

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

Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 88082580 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.


SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

flashback mode를 해제할 때에는 "alter database flashback off" 명령을 사용하면 된다.

이 명령을 치면 현재 존재하는 flashback database log는 모두 자동으로 삭제되므로 신중해야 한다!! ^^

flashback log가 제대로 활성화 되었는지 확인해본다.

 SQL> select flashback_on from v$database;

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

Flashback Database 수행

SQL> conn sys/oracle as sysdba
Connected.
SQL> create user sungeun identified by sungeun;

User created.

SQL> grant connect,resource to sungeun;

Grant succeeded.

SQL> conn sungeun/sungeun
Connected.
SQL> create table flashtest (col number);

Table created.

SQL> insert into flashtest values (1);

1 row created.

SQL> commit;

Commit complete.


SQL> conn sys/oracle as sysdba
Connected.
SQL> select username from dba_users
2 where username = 'SUNGEUN';

USERNAME
------------------------------
SUNGEUN

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
517219

SQL> drop user sungeun cascade;

User dropped.

SQL> select username from dba_users
2 where username = 'SUNGEUN';

no rows selected


SQL> flashback database to scn 517219;
flashback database to scn 517219
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.


SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL>
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 92276884 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.


SQL> flashback database to scn 517219;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.


SQL> conn sungeun/sungeun
Connected.
SQL> select * from flashtest;

COL
----------
1

이것은 테스트라 바로 resetlogs 오픈했지만.. 실제 상황이라면

"read only mode"로 오픈하여 flashback database가 제대로 되었는지.. 내가 원하는 시점인지 확인을 먼저 해야 한다.

꼭 확인후에 resetlogs로 오픈하는 섬세함(?)이 필요하다^^

Flashback Database 제한사항

다음의 경우에는 flashback database 명령을 사용할 수 없으며 경우에 따라 flashback database 명령과 전통적인 복구 방법을 혼용해도 좋다.

1. controlfile이 restore 되거나 재생성 된 경우  <--- restore란 다른 정보를 가진 controlfile의 restore를 말하는거 같다.

2. tablespace가 Drop된 경우

3. datafile이 Drop된 경우

4. datafile이 shrunk된 경우. (datafile resize가 datafile속성인 autoextend로 인해 늘어난것이면 상관없다.)

5. 이미 resetlogs로 오픈한 시점보다 이전 시점으로의 flashback

물리적인 손상인 경우는 flashback database가 안되므로.. 문제가 생긴 datafile만 offline시킨후 flashback database를 이용하여

과거로 돌아간후 해당 datafile만 전통적인 방식으로 복구하면 좀 더 빠른 복구가 가능할것이다. 

Running the FLASHBACK DATABASE Command from RMAN

1. Query the target database to determine the range of possible Flashback Database SCNs. The following queries show you the the latest and earliest SCN in the flashback window: <PRE class=CE1>SQL> SELECT CURRENT_SCN FROM V$DATABASE;SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;</PRE>
2. Start RMAN and connect to the target database. For example: <PRE class=CE1>rman TARGET /</PRE>
3. Run the FLASHBACK DATABASE command to return the database to a prior TIME, SCN, or archived log SEQUENCE number. If you configured sbt channels, RMAN automatically restores archived logs from tape as needed during the Flashback Database operation. For example: <PRE class=CE1>RMAN> FLASHBACK DATABASE TO SCN 46963;RMAN> FLASHBACK DATABASE TO SEQUENCE 5304;RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-1/24);RMAN> FLASHBACK DATABASE TO TIME timestamp('2002-11-05 14:00:00');RMAN> FLASHBACK DATABASE TO TIME to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');</PRE>

When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.

<PRE class=CE>RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';</PRE>

At this point you have several options:

• If you are content with the results, you can make the database available by performing an ALTER DATABASE OPEN RESETLOGS. <PRE class=CE1>RMAN> ALTER DATABASE OPEN RESETLOGS</PRE>
• If you discover that you have chosen the wrong target time for your Flashback Database operation, you can use RECOVER DATABASE UNTIL to bring the database forward, or perform FLASHBACK DATABASE again with an SCN further in the past. You can completely undo the effects of your flashback operation by performing complete recovery of the database: <PRE class=CE1>RMAN> RECOVER DATABASE;</PRE>
• If you only want to retrieve some lost data from the past time, you can open the database read-only, then perform a logical export of the data using an Oracle export utility (Data Pump Export or Original Export), then run RECOVER DATABASE to return the database to the present time and re-import the data using the Oracle import utility that corresponds to the export utility you used.

Note that, as with point-in-time recovery, you lose all updates to the database after the target SCN for the Flashback Database operation.

Monitoring Flashback Database

SQL> select oldest_flashback_scn oldscn, oldest_flashback_time oldtime,
2 retention_target rt_min, flashback_size cursize,
3 estimated_flashback_size estsize
4 from v$flashback_database_log;

OLDSCN 　OLDTIME 　　　　　RT_MIN 　CURSIZE 　ESTSIZE
---------- ----------------- ---------- ---------- ----------
514781 　　20071029 18:38:04 　　1440 　　16384000 　　　0


SQL> desc v$flashback_database_log
Name 　　　　　　　　　　　　　　　　　　Null? 　　Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN 　　　　　　　　　　  　　NUMBER        <--- flashback data에서 가장 낮은(빠른) SCN
OLDEST_FLASHBACK_TIME　　　　　　　　　　　 　 DATE             <--- 앞의 SCN을 시간으로 표시
RETENTION_TARGET　　　　　　　　　　　　　　　   NUMBER        <--- 분으로 표시되는 기대하는 retention time
FLASHBACK_SIZE 　　　　　　　　　　　　　　　　　NUMBER        <--- 현재 flashback 데이터의 크기(bytes)
ESTIMATED_FLASHBACK_SIZE 　　　　　　　　　　　NUMBER        <--- 현재 기준으로 현재 retention time을 충족할 수 있는 flashback 공간의 추정크기

SQL> desc v$flashback_database_stat
Name　　　　　　　　　　　　　　　　　　　 Null? 　　　　Type
----------------------------------------- -------- ----------------------------
BEGIN_TIME 　　　　　　　　　　　　　　　　　　　　DATE       <--- 시작시간
END_TIME 　　　　　　　　　　　　　　　　　　　　　DATE       <--- 끝 시간
FLASHBACK_DATA 　　　　　　　　　　　　　　　　　NUMBER   <--- interval동안 write된 flashback data
DB_DATA 　　　　　　　　　　　　　　　　　　　　　  NUMBER  <--- interval동안 read,write한 datablock size
REDO_DATA 　　　　　　　　　　　　　　　　　　　 　NUMBER  <--- interval동안 write된 redo data(bytes)
ESTIMATED_FLASHBACK_SIZE 　　　　　　　　　　 　NUMBER  <--- interval 시간에 필요로 했던 flashback space 추정크기

                                                                                                      (v$flashback_database_log의 동일 column과 같은값)

위의  view는대략 1시간 간격으로 데이터를 유지하며 최근 24시간 동안의 falshback log에 대한 I/O 오버헤드를 판단하는 유용한 정보를 제공한다^^

아래 스크립트는 interval이 1시간 단위로 저장될때 시간당 flashback log의 편균 write를 구하는 SQL이다.

SQL> select round(avg(flashback_data)/1024/1024,2)
2 from v$flashback_database_stat;

ROUND(AVG(FLASHBACK_DATA)/1024/1024,2)
--------------------------------------
2.78

No Falshback Tablespace

SQL> select name,flashback_on from v$tablespace;

NAME 　　　　　　　　　　　　FLA
------------------------------ ---
SYSTEM 　　　　　　　　　　　YES
UNDOTBS1 　　　　　　　　　　YES
SYSAUX 　　　　　　　　　　　YES
USERS 　　　　　　　　　　　　YES
TEMP 　　　　　　　　　　　　　YES
EXAMPLE 　　　　　　　　　　　YES
FRA_TBS 　　　　　　　　　　　YES

7 rows selected.

위와 같이 10g에서는 새로운 컬럼이 추가되었다. 위의 결과는 현재 모든 tablespace 가 flashback data에 적용 대상이라는 것이다.

OFF시키려면 " alter tablespace tablespace_name flashback off " 하면 해당 tablespace는 flashback OFF 된다.

주의할점은 flashback off된 tablespace는 Database Flashback이 필요할때 해당 tablespace는 모두 offline으로 변경한후

Flashback Database를 진행해야 한다. 진행후.. offline된 tablespace를 전통적인 방식으로 복구하거나

Drop해야만 정상적인 Database로 Open할수 있다.

 참조 : (장경상저)오라클10g구축과활용, 10gR1메뉴얼, 기술지원게시판

출처 : Tong - 둥근둥글님의 Mobile통

출처 : http://articles.techrepublic.com.com/5100-10878_11-5259821.html


A common task when selecting data from a database is to take a set of values a query returns and format it as a comma delimited list. Another task that's almost as common is the need to do the reverse: Take a comma delimited list of values in a single string and use it as a table of values.

Many scripting languages, such as Perl and Python, provide functions that do this with their own language-specific list of values; so it's surprising that, as of yet, this functionality isn't a standard part of SQL functions. I've seen some pretty ugly looking code that involved complex declarations with MAX and DECODE, but that solution usually only returns a limited set of values. With some of the new Oracle9i and above features, it's possible to do this yourself.

I'd like to use a "join" functionality to specify a query that returns a single column and a delimiter, and then receive a simple string that contains a list of those values separated by my delimiter. The query part can be passed to the function as a REF CURSOR using the new SQL CURSOR function. The delimiter should default to a comma, since that is the most commonly used delimiter. So, the syntax should be:

The following code will perform this function:

The PL/SQL User's Guide says you always have to declare a package that defines a ref cursor; however, the database already defines this as SYS_REFCURSOR in the STANDARD package. The PL/SQL code should be fairly straightforward. There is a limit of 32,767 characters on the output string and the input column.

Since all datatypes can be automatically converted to character strings, you can use any datatype in the cursor--as long as it's one column. For example:

There's another extra benefit. Since the cursor is part of the SQL statement, you can easily join the query inside the join with the outer query. Here is a query that returns each table and a list of the columns that make up its primary key:

View the output in Table A.

You can also use this "join" function to compare two sets of ordered data. For example, the following query will check that an index has been created on a foreign key (which helps prevent locking the table and aids master-detail queries):

This query works by executing two subqueries: one that queries foreign keys and another that queries indexes. The join between these two queries is on the table name and the list of columns used in creating the foreign key and the index, taken as an ordered list of values.

We'd also like the reverse functionality: to have the ability to take a single comma-delimited value and treat it as if it were a column in a table. We can take advantage of the TABLE SQL function and PL/SQL function tables to do this quite easily, but first, we must define the result type to be a TABLE type of the largest possible string.

With this function, I can run a query like this:

The PL/SQL procedure will parse its argument and return each part through a PIPELINE; the TABLE function allows it to be used in the FROM statement, so it appears to SQL as if it is a table with one column and three rows. (Remember that the column being returned is named COLUMN_VALUE if you want to use the value elsewhere.)

Here's an example query, which shows a dynamic IN condition in a query. The split function generates a table of values, which can be used on a row-by-row basis.

View the output in Table B.

If you want, you can join a column and then split it, too:

And, you can use this method to merge sets of values:

These are just simple example functions. You could extend join to enclose values in quotes and escape quotes inside the values. You could extend split to allow a REF CURSOR parameter instead of a single VARCHAR2, so it could split up sets of columns as well.