먼저 SYS 계정에 아래의 패키지 생성

CREATE OR REPLACE PACKAGE SYS.DBMS_CRYPTO AS

    ---------------------------------------------------------------------------
    --
    -- PACKAGE NOTES
    --
    -- DBMS_CRYPTO contains basic cryptographic functions and
    -- procedures.  To use correctly and securely, a general level of
    -- security expertise is assumed.
    --
    -- VARCHAR2 datatype is not supported.  Cryptographic operations
    -- on this type should be prefaced with conversions to a uniform
    -- character set (AL32UTF8) and conversion to RAW type.
    --
    -- Prior to encryption, hashing or keyed hashing, CLOB datatype is
    -- converted to AL32UTF8.  This allows cryptographic data to be
    -- transferred and understood between databases with different
    -- character sets, across character set changes and between
    -- separate processes (for example, Java programs).
    --
    ---------------------------------------------------------------------------


    -------------------------- ALGORITHM CONSTANTS ----------------------------
    -- The following constants refer to various types of cryptographic
    -- functions available from this package.  Some of the constants
    -- represent modifiers to these algorithms.
    ---------------------------------------------------------------------------

    -- Hash Functions
    HASH_MD4           CONSTANT PLS_INTEGER            :=     1;
    HASH_MD5           CONSTANT PLS_INTEGER            :=     2;
    HASH_SH1           CONSTANT PLS_INTEGER            :=     3;

    -- MAC Functions
    HMAC_MD5           CONSTANT PLS_INTEGER            :=     1;
    HMAC_SH1           CONSTANT PLS_INTEGER            :=     2;

    -- Block Cipher Algorithms
    ENCRYPT_DES        CONSTANT PLS_INTEGER            :=     1;  -- 0x0001
    ENCRYPT_3DES_2KEY  CONSTANT PLS_INTEGER            :=     2;  -- 0x0002
    ENCRYPT_3DES       CONSTANT PLS_INTEGER            :=     3;  -- 0x0003
    ENCRYPT_AES        CONSTANT PLS_INTEGER            :=     4;  -- 0x0004
    ENCRYPT_PBE_MD5DES CONSTANT PLS_INTEGER            :=     5;  -- 0x0005
    ENCRYPT_AES128     CONSTANT PLS_INTEGER            :=     6;  -- 0x0006
    ENCRYPT_AES192     CONSTANT PLS_INTEGER            :=     7;  -- 0x0007
    ENCRYPT_AES256     CONSTANT PLS_INTEGER            :=     8;  -- 0x0008

    -- Block Cipher Chaining Modifiers
    CHAIN_CBC          CONSTANT PLS_INTEGER            :=   256;  -- 0x0100
    CHAIN_CFB          CONSTANT PLS_INTEGER            :=   512;  -- 0x0200
    CHAIN_ECB          CONSTANT PLS_INTEGER            :=   768;  -- 0x0300
    CHAIN_OFB          CONSTANT PLS_INTEGER            :=  1024;  -- 0x0400

    -- Block Cipher Padding Modifiers
    PAD_PKCS5          CONSTANT PLS_INTEGER            :=  4096;  -- 0x1000
    PAD_NONE           CONSTANT PLS_INTEGER            :=  8192;  -- 0x2000
    PAD_ZERO           CONSTANT PLS_INTEGER            := 12288;  -- 0x3000
    PAD_ORCL           CONSTANT PLS_INTEGER            := 16384;  -- 0x4000

    -- Stream Cipher Algorithms
    ENCRYPT_RC4        CONSTANT PLS_INTEGER            :=   129;  -- 0x0081


    -- Convenience Constants for Block Ciphers
    DES_CBC_PKCS5      CONSTANT PLS_INTEGER            := ENCRYPT_DES
                                                          + CHAIN_CBC
                                                          + PAD_PKCS5;

    DES3_CBC_PKCS5     CONSTANT PLS_INTEGER            := ENCRYPT_3DES
                                                          + CHAIN_CBC
                                                          + PAD_PKCS5;

    AES_CBC_PKCS5      CONSTANT PLS_INTEGER            := ENCRYPT_AES
                                                          + CHAIN_CBC
                                                          + PAD_PKCS5;


    ----------------------------- EXCEPTIONS ----------------------------------
    -- Invalid Cipher Suite
    CipherSuiteInvalid EXCEPTION;
    PRAGMA EXCEPTION_INIT(CipherSuiteInvalid, -28827);

    -- Null Cipher Suite
    CipherSuiteNull EXCEPTION;
    PRAGMA EXCEPTION_INIT(CipherSuiteNull,    -28829);

    -- Key Null
    KeyNull EXCEPTION;
    PRAGMA EXCEPTION_INIT(KeyNull,            -28239);

    -- Key Bad Size
    KeyBadSize EXCEPTION;
    PRAGMA EXCEPTION_INIT(KeyBadSize,         -28234);

    -- Double Encryption
    DoubleEncryption EXCEPTION;
    PRAGMA EXCEPTION_INIT(DoubleEncryption,   -28233);


    ---------------------- FUNCTIONS AND PROCEDURES ------------------------

    ------------------------------------------------------------------------
    --
    -- NAME:  Encrypt
    --
    -- DESCRIPTION:
    --
    --   Encrypt plain text data using stream or block cipher with user
    --   supplied key and optional iv.
    --
    -- PARAMETERS
    --
    --   plaintext   - Plaintext data to be encrypted
    --   crypto_type - Stream or block cipher type plus modifiers
    --   key         - Key to be used for encryption
    --   iv          - Optional IV for block ciphers.  Default all zeros.
    --
    -- USAGE NOTES:
    --
    --   Block ciphers may be modified with chaining type (CBC most
    --   common) and padding type (PKCS5 recommended).  Of the four
    --   common data formats, three have been provided: RAW, BLOB,
    --   CLOB. For VARCHAR2 encryption, callers should first convert
    --   to AL32UTF8 character set and then encrypt.
    --
    --     Encrypt(UTL_RAW.CAST_TO_RAW(CONVERT(src,'AL32UTF8')),typ,key);
    --
    --   As return type for encrypt is RAW, callers should consider
    --   encoding it with RAWTOHEX or UTL_ENCODE.BASE64_ENCODE to make
    --   it suitable for VARCHAR2 storage.  These functions expand
    --   data size by 2 and 4/3, respectively.
    --
    --   To improve readability, callers should define their own
    --   package level constants to represent the ciphersuites used
    --   for encryption and decryption.
    --
    --   For example:
    --
    --   DES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
    --                                       + DBMS_CRYPTO.CHAIN_CBC
    --                                       + DBMS_CRYPTO.PAD_PKCS5;
    --
    --
    -- STREAM CIPHERS (RC4) ARE NOT RECOMMENDED FOR STORED DATA ENCRYPTION.
    --
    --
    ------------------------------------------------------------------------

    FUNCTION  Encrypt (src IN            RAW,
                       typ IN            PLS_INTEGER,
                       key IN            RAW,
                       iv  IN            RAW          DEFAULT NULL)
      RETURN RAW;

    PROCEDURE Encrypt (dst IN OUT NOCOPY BLOB,
                       src IN            BLOB,
                       typ IN            PLS_INTEGER,
                       key IN            RAW,
                       iv  IN            RAW          DEFAULT NULL);

    PROCEDURE Encrypt (dst IN OUT NOCOPY BLOB,
                       src IN            CLOB         CHARACTER SET ANY_CS,
                       typ IN            PLS_INTEGER,
                       key IN            RAW,
                       iv  IN            RAW          DEFAULT NULL);


    ------------------------------------------------------------------------
    --
    -- NAME:  Decrypt
    --
    -- DESCRIPTION:
    --
    --   Decrypt crypt text data using stream or block cipher with user
    --   supplied key and optional iv.
    --
    -- PARAMETERS
    --
    --   cryptext    - Crypt text data to be decrypted
    --   crypto_type - Stream or block cipher type plus modifiers
    --   key         - Key to be used for encryption
    --   iv          - Optional IV for block ciphers.  Default all zeros.
    --
    -- USAGE NOTES:
    --   To retrieve original plain text data, Decrypt must be called
    --   with the same cipher, modifiers, key and iv used for
    --   encryption.  If crypt text data was converted to hex or
    --   base64 prior to storage, it must be decoded using HEXTORAW or
    --   UTL_ENCODE.BASE64_DECODE prior to decryption.
    --
    ------------------------------------------------------------------------

    FUNCTION  Decrypt (src IN            RAW,
                       typ IN            PLS_INTEGER,
                       key IN            RAW,
                       iv  IN            RAW          DEFAULT NULL)
       RETURN RAW;

    PROCEDURE Decrypt (dst IN OUT NOCOPY BLOB,
                       src IN            BLOB,
                       typ IN            PLS_INTEGER,
                       key IN            RAW,
                       iv  IN            RAW          DEFAULT NULL);

    PROCEDURE Decrypt (dst IN OUT NOCOPY CLOB         CHARACTER SET ANY_CS,
                       src IN            BLOB,
                       typ IN            PLS_INTEGER,
                       key IN            RAW,
                       iv  IN            RAW          DEFAULT NULL);


    ------------------------------------------------------------------------
    --
    -- NAME:  Hash
    --
    -- DESCRIPTION:
    --
    --   Hash source data by cryptographic hash type.
    --
    -- PARAMETERS
    --
    --   source    - Source data to be hashed
    --   hash_type - Hash algorithm to be used
    --
    -- USAGE NOTES:
    --   SHA-1 (HASH_SH1) is recommended.  Consider encoding returned
    --   raw value to hex or base64 prior to storage.
    --
    ------------------------------------------------------------------------

    FUNCTION Hash (src IN RAW,
                   typ IN PLS_INTEGER)
      RETURN RAW DETERMINISTIC;

    FUNCTION Hash (src IN BLOB,
                   typ IN PLS_INTEGER)
      RETURN RAW DETERMINISTIC;

    FUNCTION Hash (src IN CLOB        CHARACTER SET ANY_CS,
                   typ IN PLS_INTEGER)
      RETURN RAW DETERMINISTIC;


    ------------------------------------------------------------------------
    --
    -- NAME:  Mac
    --
    -- DESCRIPTION:
    --
    --   Message Authentication Code algorithms provide keyed message
    --   protection.
    --
    -- PARAMETERS
    --
    --   source   - Source data to be mac-ed
    --   mac_type - Mac algorithm to be used
    --   key      - Key to be used for mac
    --
    -- USAGE NOTES:
    --   Callers should consider encoding returned raw value to hex or
    --   base64 prior to storage.
    --
    ------------------------------------------------------------------------
    FUNCTION Mac (src IN RAW,
                  typ IN PLS_INTEGER,
                  key IN RAW)
      RETURN RAW;

    FUNCTION Mac (src IN BLOB,
                  typ IN PLS_INTEGER,
                  key IN RAW)
      RETURN RAW;

    FUNCTION Mac (src IN CLOB         CHARACTER SET ANY_CS,
                  typ IN PLS_INTEGER,
                  key IN RAW)
      RETURN RAW;


    ------------------------------------------------------------------------
    --
    -- NAME:  RandomBytes
    --
    -- DESCRIPTION:
    --
    --   Returns a raw value containing a pseudo-random sequence of
    --   bytes.
    --
    -- PARAMETERS
    --
    --   number_bytes - Number of pseudo-random bytes to be generated.
    --
    -- USAGE NOTES:
    --   number_bytes should not exceed maximum RAW length.
    --
    ------------------------------------------------------------------------
    FUNCTION RandomBytes (number_bytes IN PLS_INTEGER)
      RETURN RAW;


    ------------------------------------------------------------------------
    --
    -- NAME:  RandomNumber
    --
    -- DESCRIPTION:
    --
    --   Returns a random Oracle Number.
    --
    -- PARAMETERS
    --
    --  None.
    --
    ------------------------------------------------------------------------
    FUNCTION RandomNumber
      RETURN NUMBER;


    ------------------------------------------------------------------------
    --
    -- NAME:  RandomInteger
    --
    -- DESCRIPTION:
    --
    --   Returns a random BINARY_INTEGER.
    --
    -- PARAMETERS
    --
    --  None.
    --
    ------------------------------------------------------------------------
    FUNCTION RandomInteger
      RETURN BINARY_INTEGER;


    PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, RNDS, WNPS, RNPS);

END DBMS_CRYPTO;
/


그리고

 오라클 암호화 DBMS_CRYPTO (10g 이상)
------------------------------------------------------------------------------------------
▶ 권한부여
SQL> grant execute on DBMS_CRYPTO to [계정명];
▶ Create Function (일반계정용)
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION KORAS.ENCRY(strValue VARCHAR2, strEncKey VARCHAR2)
    RETURN RAW
    IS
        input_raw RAW(1024);
        key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(strEncKey);
        v_out_raw RAW(1024);
        AES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
    BEGIN
        input_raw := UTL_I18N.STRING_TO_RAW(strValue, 'AL32UTF8');
        v_out_raw := DBMS_CRYPTO.ENCRYPT(
                        src => input_raw,
                        typ => AES_CBC_PKCS5,
                        key => key_raw);
        RETURN v_out_raw;
END ENCRY;
/
CREATE OR REPLACE FUNCTION KORAS.DECRY(v_in_raw RAW, strEncKey VARCHAR2)
    RETURN VARCHAR2
    IS
        key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(strEncKey);
        output_raw RAW(1024);
        v_out_string VARCHAR2(1024);
        AES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
    BEGIN
        output_raw := DBMS_CRYPTO.DECRYPT(
                        src => v_in_raw,
                        typ => AES_CBC_PKCS5,
                        key => key_raw);
        v_out_string := UTL_I18N.RAW_TO_CHAR(output_raw, 'AL32UTF8');
        RETURN v_out_string;
END DECRY;


돌려 주면 됨
블로그 이미지

요다할아범

,

질문
----------------------------------
select t.corp_cd, t.engineer_nm from mantb_engineer t order by t.corp_cd
//회사코드, 엔지니어 이름 을 회사코드에 맞게 정렬해서 아래와 같은 결과가 나왔습니다.

     코드   엔지니어
1 A05308 박박박
2 A05308 김김김
3 A05308 최최최
4 A05308 민민민
5 A05308 이이이
6 A05308 마마마
7 A05308 차차차
8 A06182 박박박
9 A06182 김김김
10 A06187 최최최
11 A09141 윤윤윤
12 A09141 민민민

이 내용을 코드별로 그룹정렬해서 엔지니어 이름을 붙여서 정렬하고자 합니다.
ex) A05308  박박박 김김김 최최최 민민민 이이이 마마마 차차차
----------------------------------

==================================
답변
----------------------------------
SELECT corp_cd,
       LTRIM(SYS_CONNECT_BY_PATH(engineer_nm,','),',') engineer_nm
  FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY corp_cd ORDER BY engineer_nm ) NUM
       , corp_cd
       , engineer_nm
       , COUNT(*) OVER (PARTITION BY corp_cd) gCnt
    FROM mantb_engineer
)
 WHERE LEVEL = gCnt
 START WITH NUM=1
 CONNECT BY PRIOR corp_cd = corp_cd
     AND PRIOR NUM = NUM-1


아래와 같이 정렬됩니다..
한 컬럼에 모든것을 이으실려면 컬럼간에 문자열 결합인자를 사용하시면 됩니다.
corp_cd || LTRIM(SYS_CONNECT_BY_PATH(engineer_nm,','),',')
응용해서 원하는 결과를 얻으시기를~~~

     코드               엔지니어
1  A05308  박박박, 김김김, 최최최, 민민민, 이이이, 마마마, 차차차
2  A06182  박박박, 김김김
3  A06187  최최최
4  A09141  윤윤윤, 민민민

ORDER BY 구문이 없으면 ERROR가 나네요 추가해서 수정했습니다.


 ------------------------------

2012년 10월 29일 추가

SELECT 한 데이터를 세로로 아닌 가로로 표현해야 될 때가 있다. 

그런 경우에 사용하는 쿼리이다.

     WITH TABLE1 AS

    (
      SELECT '01' AS COLUMN1 FROM dual
      UNION ALL 
      SELECT '03' AS COLUMN1 FROM dual
      UNION ALL 
      SELECT '05' AS COLUMN1 FROM dual
      UNION ALL 
      SELECT '08' AS COLUMN1 FROM dual
    )
     
    SELECT SUBSTR(REPLACE(XMLAGG(XMLELEMENT("nm", ', ' || COLUMN1)).EXTRACT('//text()').GetStringVal(), ', , ', NULL), 3)
      FROM TABLE1;

보면 데이터를 XMLELMENT로 바꾼 다음에 그걸 XMLAGG로 싸준 방식이다.

이런 식으로 하면 데이터는 아래와 같이 나온다.

01, 03, 05, 08

그런데 오라클이 10인지 11인지가 되면서 함수가 하나 생겼다.

바로 WM_CONCAT인데 아주 강력하다.

 SELECT SUBSTR(REPLACE(XMLAGG(XMLELEMENT("nm", ', ' || COLUMN1)).EXTRACT('//text()').GetStringVal(), ', , ', NULL), 3)
      FROM TABLE1;

이 부분을

SELECT WM_CONCAT(COLUMN1) FROM TABLE1;

이렇게만 변경하면 바로 원하는 결과 값이 나온다.

---------------------------------------------------------------------------------------------
오라클 클럽에서 아주 제대로 된 예제를 봐서 추가로 올린다.

WITH t(type, name,code) AS
(
SELECT 'A', '사과', '0' FROM dual
UNION ALL SELECT 'A', '레몬', '1' FROM dual
UNION ALL SELECT 'A', '포도', '2' FROM dual
UNION ALL SELECT 'A', '참외', '3' FROM dual
)
SELECT type
     , SUBSTR(XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()'), 2) name_9i
     , SUBSTR(XMLAgg(XMLElement(x, ',', code) ORDER BY code).Extract('//text()'), 2) code_9i
     , wm_concat(name) name_10g
     , wm_concat(code) code_10g
     , ListAgg(name, ',') WITHIN GROUP(ORDER BY code) name_11g
     , ListAgg(code, ',') WITHIN GROUP(ORDER BY code) code_11g
  FROM t
 GROUP BY type
;

블로그 이미지

요다할아범

,

사용자가 DML을 실행후
commit 등을 안하게 되면 락이 걸리게 되는데 이때 해결 방법
물론 오라클 인스턴스를 재시작해도 되지만
이 방법이 훨씬 간단함

select a.sid, a.serial#, b.type, c.object_name
from V$session a, v$lock b, dba_objects c
where a.sid = b.sid and b.id1 = c.object_id
and b.type = 'TM' and c.object_name = 'FSFE1_STRACK_INFO'

위의 쿼리를 실행 후 나오는 결과를 아래의 쿼리에 대입하여 실행함

alter system kill session '150,101';

블로그 이미지

요다할아범

,

오라클 백업에는 크게 피지컬 백업(Physical Backup) 방식과 로지컬 백업(Logical Backup) 방식이 있다.

피지컬 백업 방식이란 데이타 파일 자체를 카피하는 방식을 말하는데, 이것은 다시 데이타베이스를 셧다운하고 백업하는 방식과 오픈된 상태에서 백업하는 방식으로 나뉜다. 반면, 로지컬 백업 방식이란 오라클에서 제공하는 유틸리티 중의 하나인 Export/Import를 이용하여 백업하는 형식으로, 이 백업 파일을 이용하여 리커버리를 실행하면 Create Object 문장을 이용해 테이블스페이스와 유저, 테이블을 생성하고, Insert Statement를 이용해 특정 테이블에 Insert 작업이 이루어지기 때문에 이처럼 일컫는 것이다.

먼저 이들에 대해 각각 알아보고, 데이타 복구(Recovery)를 위한 기본 개념들을 다루고자 한다. 그리고 리커버리에 대한 더 구체적인 설명은 다음 호에서 다룰 예정이다.


Cold Backup 받는 방법

콜드 백업(Cold Backup)은 데이타베이스가 정상적으로 셧다운된 상태에서 데이타 파일, 로그 파일, 콘트롤 파일을 모두 백업 받는 것을 말한다. 셧다운하지 않고 오픈된 상태에서 백업을 받으면 백업 받은 내용을 나중에 사용할 수가 없으므로 유의해야 한다.
콜드 백업을 위해서 데이타베이스를 셧다운할 때에는 Normal 또는 Immediate 옵션을 사용해야 하며, Abort를 사용해서는 안 된다.
상황이 여의치 않아 Abort를 사용한 경우에는 셧다운 후에 다시 스타트업하고 Normal로 셧다운한 후 백업을 받도록 한다.

우선 이들 파일을 백업하기 위해 콘트롤 파일과 데이타 파일 및 로그 파일의 위치를 확인하여 이들을 tar, cpio, dd 등의 명령을 이용하여 백업 받도록 한다. NT에서는 Copy 명령이나 탐색기를 이용해서 백업을 받으면 된다.

 

■ 콘트롤 파일 확인 방법
sqlplus에 system/manager로 접속 후
SQL>select value from v$controlfile;

■ 데이타 파일 확인 방법
SQL>select name from v$datafile;

■ 로그 파일 확인 방법
SQL>select member from v$logfile;

 

이처럼 각각의 파일 위치를 확인하여 위에서 확인된 3종류의 파일들을 백업 받으면 된다. 이때 오라클은 반드시 셧다운된 상태이어야 한다. 항상은 아니지만 필요에 따라서는 패러미터 파일까지 받아두는 것이 좋다. 패러미터 파일은 $ORACLE_HOME/dbs/init.ora에 있는데, 이 초기화 패러미터 파일에 기술되어진 패러미터는 시스템의 성능에 주요한 역할을 하므로 이 파일이 없어진 경우에 예전의 성능을 유지하려면 이 파일을 필수적으로 백업해야 한다.

 

NT에서는 C:\ORANT\DATABASE\init.ora 파일이다.

일반적으로 NT의 SID가 처음 만들어질 때 ORCL이므로 이 초기 패러미터 파일 역시 initORCL.ora라고 되어 있다.

여기서 는 오라클 인스턴스 이름으로 환경변수 ORACLE_SID로 지정되어 있다. 이 ORACLE_SID는 Unix의 경우 $env, NT의 경우는 Registry에 지정되어 있으므로 직접 확인해 볼 수 있다.


Hot Backup 받는 방법


핫 백업(Hot Backup)이란 데이타베이스를 셧다운하지 않고 백업하는 방식으로, 일반적인 경우는 적용되지 않고 자신의 데이타베이스를 Archive Mode로 운영하는 경우에만 가능하다.
콜드 백업을 이용하여 데이타를 복구하는 경우에는 콜드 백업을 받은 시점까지만 복구가 가능한 데 반해, 이 Archive 방식으로 데이타베이스를 운영하는 경우에는 사용자가 원하는 시점까지 데이타를 복구할 수 있다. 즉, 원하는 시간을 주어서, SCN 번호를 주어서, 혹은 어느 데이타 파일 한 개만의 복구도 가능하다.

데이타 복구만 생각한다면 모든 데이타베이스를 Archive 방식으로 운영하는 것이 좋겠지만, 이 방식을 사용하는 경우 데이타베이스 관리자가 백업/리커버리에 관하여 많은 지식과 경험을 갖춰야 하며, 또 Archive Log를 계속 유지관리하는 데는 스페이스와 성능의 부하가 따르기 때문에 사용자의 적절한 판단에 의하여 이용하도록 한다.

그러면 Archive Log Mode의 운영 방법에 대해 알아보자.


Archive Log Mode 운영 방법

오라클에서 데이타베이스가 셧다운되지 않은 상태에서 백업을 받거나 문제가 생긴 시점까지의 완벽한 리커버리 작업을 수행하기 위해서는 데이타베이스를 아카이브 로그 모드로 운영하여야 한다.

아카이브 로그 모드로 운영하기 위해서는 다음의 절차에 따라 변경하여야 한다.

 

1. initSID.ora 파일과 configSID.ora에 다음의 패러미터가 이미 설정되어 있는지 확인한 후에 없을 경우 initSID.ora에 설정한다.

(1) LOG_ARCHIVE_START = TRUE

이 패러미터에 의해 데이타베이스가 처음 구동시 ARCH 프로세스가 기동.

Log Switch 발생시 Automatic Archive를 수행한다.

만약 이 패러미터가 False이면 Manual Archive를 수행한다.
(2)LOG_ARCHIVE_DEST =/home/oracle8/dbs/archive_file/arc
아카이브 파일이 생성되는 위치인 디렉토리와 확장자를 포함하지 않는 파일명을 지정.

여기에서 archive_file까지는 디렉토리이며 마지막에 있는 arc는 아카이브 로그 파일 이름의 첫 부분이다.
이 아카이브 로그 모드를 설정하면 로그 파일의 스위치가 일어날 때마다 지속적으로 이 Log_Archive_Dest에 지정한 위치에 로그 파일이 쌓이게 되는데, 만일 부주의에 의해 이 아카이브 로그 파일이 깨지거나 분실되는 경우는 그 이후의 데이타 복구가 불가능하므로 주의하여야 한다.

이러한 문제를 줄이기 위해 Oracle8부터는 아카이브 로그 파일을 Mirroring하기 위하여 여러 군데에 보관할 수 있는 방안도 도입되었다. 이는 log_archive_duplex_dest를 설정함으로써 가능하다.

또한 V8i부터는 최대 5군데에 이 로그 파일을 저장할 수 있도록 하여

LOG_ARCHIVE_DEST_1=”LOCATION=/oracle8/arch_1 MANDATORY”
LOG_ARCHIVE_DEST_2=”LOCATION=/oracle8/arch_2 OPTIONAL?

처럼 지정이 가능하다

(3) LOG_ARCHIVE_FORMAT = %s.log
아카이브 파일의 확장자와 로그 시퀀스 번호의 형식을 지정.

이는 (2)에서 정의된 아카이브 로그 파일의 첫 부분 이름과 함께 나타난다.
arc123.log, arc124.log (123과 124는 로그 시퀀스 번호)
와 같은 형태의 파일이 지정한 위치에 생성된다.

 

2. 다음과 같이 작업하여 아카이브 로그 모드로 변환한다.

$ svrmgrl

SVRMGR> connect internal
SVRMGR> startup mount - ■
SVRMGR> alter database archivelog; - □
SVRMGR> archive log list - ●
Database log mode ARCHIVELOG - ◎
Automatic archival ENABLED - ◇
Archive destination ?/dbs_ar/offline_log/offline - ◆
Oldest online log sequence 123 - △
Next log sequence to archive 125 - ▲
Current log sequence 125 - ▷
SVRMGR> alter database open; - ▶

■ DB를 Startup Mount까지만 한다.
□ 이 커맨드를 이용하여 아카이브 모드로 데이타베이스를 변경한다.
● 아카이브 모드로 변경되었는지를 확인한다.
◎ 데이타베이스가 아카이브 모드임을 나타낸다. 만약 NOARCHIVELOG로 되어 있으면 변경되지 않은 것을 의미한다.
◇ initSID.ora 파일에서 LOG_ARCHIVE_START 패러미터를 TRUE로 정의하였음을 나타내며 False인 경우에는 DISABLED로 나타난다.
◆ initSID.ora 파일의 LOG_ARCHIVE_DEST 패러미터에서 정의한 아카이브할 장소이다.
△ 3개의 Redo Log 중 가장 오래된 리두 로그의 시퀀스가 123임을 의미한다.
▲ 다음에 아카이브 받을 파일의 로그 시퀀스 번호를 나타낸다.
▷ 현재 사용중인 리두 로그의 시퀀스가 125임을 의미한다. 만약 이전부터 아카이브 로그 모드로 운영중이었다면 여기에서 아카이브 로그 파일은 로그 시퀀스 124까지 아카이브되어 있다는 것을 의미한다.
▶ 아카이브 모드로 변경 후 DB를 오픈한다.


No Archive Log Mode로 전환하는 방법
반대로, Archivelog Mode에서 No Archivelog Mode로 전환하는 방법은 다음과 같다.

먼저, 위에서 세팅했던 initSID.ora 파일과 configSID.ora에 있는 다음 패러미터 앞에 #을 넣고 저장한다.
#LOG_ARCHIVE_START = TRUE
#LOG_ARCHIVE_DEST = /home/oracle8/dbs/archive_file/arc
#LOG_ARCHIVE_FORMAT = %s.log

$ svrmgrl
SVRMGR> connect internal;           
SVRMGR> shutdown immediate
SVRMGR> startup mount           
ORACLE instance started.           
Database mounted.           
SVRMGR> alter database noarchivelog;     
Statement processed.           
SVRMGR> alter database open;           
Statement processed.


Hot Backup 하는 방법

데이타베이스를 셧다운하지 않고 데이타 파일을 백업하는 방법이다.
이 방법은 콜드 백업보다 더 복잡하지만 데이타베이스가 오픈되어 있는 도중에 할 수 있고 또한 테이블스페이스 별로 백업할 수 있다는 장점이 있다.
핫 백업은 항상 데이타베이스를 아카이브 로그 모드 상태로 두고 실시한다.

| 방법 | 테이블스페이스 단위로 백업을 실시한다.
svrmgrl > CONNECT INTERNAL

svrmgrl > ALTER TABLESPACE SYSTEM BEGIN BACKUP;

시스템 테이블스페이스의 모든 데이타파일에 대해서 OS Backup 한다.

$ tar cvf /dev/rmt0 /usr/oracle8/dbs/syst1ORA8.dbf

$ tar cvf /dev/rmt0 /usr/oracle8/dbs/syst2ORA8.dbf

svrmgrl > ALTER TABLESPACE SYSTEM END BACKUP;

svrmgrl > ALTER TABLESPACE USERS BEGIN BACKUP;

$ tar cvf /dev/rmt0 /usr/oracle8/dbs/user1ORA8.dbf

svrmgrl > ALTER TABLESPACE USERS END BACKUP;

다른 테이블스페이스에 대해서도 같은 방법으로 한다.


주의할 점은 BEGIN과 END 사이에는 해당 테이블스페이스의 데이타파일 헤더 정보가 변경되지 않는다는 것이다. 따라서 OS 백업이 종료됨과 동시에 ‘ALTER TABLESPACE ... END BAKCUP’ 커맨드를 실행하여 데이타파일의 헤더가 변경되도록 한다.
이 아카이브 백업 파일을 이용한 복구 방법은 다음 기회에 다루도록 한다.


Export Backup 하는 방법

오라클에서 제공되는 Export 유틸리티는 데이타베이스에 저장된 데이타를 바이너리 형태의 OS 파일로 만들고, 필요시 Import 유틸리티를 이용하여 데이타베이스로 다시 올리는 방식이다 .
이 유틸리티는 각 오브젝트 단위로 처리가 가능하기 때문에 테이블 몇 개만을 복구한다든가 특정 사용자의 테이블들을 다른 테이블스페이스로 옮긴다든가 또는 전체 데이타베이스의 자료를 서로 다른 OS로 옮기는 경우 등에 특히 유리하다.


Export의 종류

이 Export 방안은 시스템과 서로 메시지를 주고받으면서 백업을 할 수 있는 Interactive Mode가 있고, 한 라인의 명령어로 Export시 필요한 옵션을 길게 열거해주는 Command 방식이 있다.
Command 방식은 순간 순간 필요한 옵션을 적어주기만 하면 되기 때문에 여기서는 Interactive 방식을 다루어 보기로 한다


Export의 단위

 

Full 단위 : 전체 데이타베이스를 Export 한다.
User 단위 : 특정 유저 전체 오브젝트를 Export 한다.
Table 단위 : 특정 테이블을 Export 한다.
Partition 단위 : 특정 테이블의 파티션을 Export 한다.

Export의 실제 예제
| 예 1 | 전체 데이타베이스의 Export (Interactive Method)

$ exp system/manager
Connected to: ORACLE8 Server Release 8.0.5 - Production

With the procedural and distributed options
PL/SQL Release 2.2 - Production
Enter array fetch buffer size : 4096 > 100000 (RETURN)
Export file : expdat.dmp >
(1) E(ntire database), (2) U(sers), (3) T(ables) : U > e
Export grants (Y/N) : Y > y
Export table data (Y/N) : Y > y
Compress extents (Y/N) : Y > y
About to export the entire database....

. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting role
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting sequence numbers
. exporting cluster definitions
. exporting stored procedures
. about to export SYSTEM’s tables ...
. about to export SCOTT’s tables ...
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting triggers

Export terminated successfully without warnings.
| 예 2 | 전체 데이타베이스의 EXPORT(Command Line Method)

$ exp userid=system/manager full=y file=fullbackup.dmp buffer=100000
| 예 3 | 전체 데이타베이스의 EXPORT(Dynamic Method)
Export 패러미터를 다음과 같은 파일(tusc.par) 형태로 만든다.

system/manager
full=y
file=fullbackup.dmp
buffer=100000
$ exp parfile=tusc.par

| 예 4 | User 단위의 Export

$ exp system/manager
Connected to: ORACLE8 Server Release 8.0.5 - Production

With the procedural and distributed options
PL/SQL Release 2.2 - Production
Enter array fetch buffer size : 4096 > 100000 (RETURN)
Export file : expdat.dmp >
(1) E(ntire database), (2) U(sers), (3) T(ables) : U > u
Export grants (Y/N) : Y > y
Export table data (Y/N) : Y > y
Compress extents (Y/N) : Y > y

About to export specified users
User to be exported: (RETURN to quit) > scott
. exporting snapshots
. exporting snapshot log
. exporting database links
. exporting sequence numbers
. exporting sequence numbers
. exporting cluster definitions
. exporting stored procedures
. about to export SCOTT’s tables ...
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.

| 예 5 | User 단위의 Export (Command Line Method)

$ exp system/manager owner=scott file=scott.dmp buffer=100000

또는

$ exp scott/tiger file=scott.dmp buffer=100000

| 예 6 | User 단위의 Export (Dynamic Method )
Export 패러미터를 다음과 같은 파일(tusc.par) 형태로 만든다.

$vi tusc.par

scott/tiger
file=scott.dmp
buffer=1000000
$ exp parfile=tusc.par

| 예 7 | Table 단위의 Export

$ exp scott/tiger file=table.dmp tables=emp,dept buffer=100000

블로그 이미지

요다할아범

,