DB 정리 - 1

▣ BACKUPSET 정리

   

SQL> @fra2

   

PL/SQL procedure successfully completed.

   

   

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 10.9 223.232 8

BACKUPPIECE 36.17 740.7616 12 => 필요없는 부분 수동으로 삭제 -> crosscheck -> delete expired backupset

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 58.72 1202.5856

   

7 rows selected.

   

SQL> r

1 select RECID,DELETED from V$BACKUP_PIECE_DETAILS

2* order by RECID

   

RECID DEL

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

7 NO

8 NO

9 NO

10 NO

11 NO

12 NO

13 NO

14 NO

15 NO

16 NO

17 NO

   

RECID DEL

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

18 NO

19 NO

20 NO

▶ 19, 20 : 현재 사용중인 backupset

14 rows selected.

   

   

[oracle@edrsr4p1 ~]$ rman target /

   

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 27 10:48:51 2009

   

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

   

connected to target database: ORCL (DBID=1173970302)

   

RMAN> backup as compressed backupset format '/home/oracle/orcl%d_%s.bus' database;

   

Starting backup at 27-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_3trzfl2y_.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_3trzfl4m_.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_3trzjjxo_.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_3trzflk7_.dbf

channel ORA_DISK_1: starting piece 1 at 27-AUG-09

channel ORA_DISK_1: finished piece 1 at 27-AUG-09

piece handle=/home/oracle/orclORCL_7.bus tag=TAG20090827T104916 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 27-AUG-09

channel ORA_DISK_1: finished piece 1 at 27-AUG-09

piece handle=/home/oracle/orclORCL_8.bus tag=TAG20090827T104916 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 27-AUG-09

   

RMAN> delete obsolete;

   

using target database control file instead of recovery catalog

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 devtype=DISK

Deleting the following obsolete backups and copies:

Type Key Completion Time Filename/Handle

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

Backup Set 7 27-AUG-09

Backup Piece 7 27-AUG-09 /home/oracle/orclORCL_7.bus

Backup Set 8 27-AUG-09

Backup Piece 8 27-AUG-09 /home/oracle/orclORCL_8.bus

Backup Set 9 27-AUG-09

Backup Piece 9 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105654_59csz6vl_.bkp

Backup Set 10 27-AUG-09

Backup Piece 10 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105654_59ct1y3w_.bkp

Backup Set 11 27-AUG-09

Backup Piece 11 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105845_59ct2osq_.bkp

Backup Set 12 27-AUG-09

Backup Piece 12 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105845_59ct5qxt_.bkp

Backup Set 13 27-AUG-09

Backup Piece 13 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T110333_59ctcpc9_.bkp

   

Do you really want to delete the above objects (enter YES or NO)? yes

deleted backup piece

backup piece handle=/home/oracle/orclORCL_7.bus recid=7 stamp=695990956

deleted backup piece

backup piece handle=/home/oracle/orclORCL_8.bus recid=8 stamp=695991054

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105654_59csz6vl_.bkp recid=9 stamp=695991414

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105654_59ct1y3w_.bkp recid=10 stamp=695991502

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105845_59ct2osq_.bkp recid=11 stamp=695991525

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105845_59ct5qxt_.bkp recid=12 stamp=695991623

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T110333_59ctcpc9_.bkp recid=13 stamp=695991814

deleted backup piece

   

SQL> @fra2

   

PL/SQL procedure successfully completed.

   

   

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 1.78 36.4544 2

BACKUPPIECE 6.03 123.4944 2

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 19.46 398.5408

   

7 rows selected.

   

   

   

▣ 아카이브 정리

   

SQL> @fra2

   

PL/SQL procedure successfully completed.

   

   

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 10.9 223.232 8

BACKUPPIECE 36.17 740.7616 12

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 58.72 1202.5856

   

7 rows selected.

   

SQL> alter system switch logfile;

   

System altered.

   

[oracle@edrsr4p1 archivelog]$ pwd

/u01/app/oracle/flash_recovery_area/ORCL/archivelog

[oracle@edrsr4p1 archivelog]$ ll

total 16

drwxr-x--- 2 oracle oinstall 4096 Aug 26 22:46 2009_08_26

drwxr-x--- 2 oracle oinstall 4096 Aug 27 11:20 2009_08_27

[oracle@edrsr4p1 archivelog]$ mv 2009_08_27 xx

[oracle@edrsr4p1 archivelog]$ rm -rf 2009_08_26 => 오늘 아카이브만 남기고 모두 삭제

[oracle@edrsr4p1 archivelog]$ mv xx 2009_08_27

   

RMAN> crosscheck archivelog all;

   

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=140 devtype=DISK

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_7_59bdzfwg_.arc recid=1 stamp=695945343

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_8_59bdzlkm_.arc recid=2 stamp=695945346

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_9_59bdzoq3_.arc recid=3 stamp=695945349

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_10_59bdzpny_.arc recid=4 stamp=695945350

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_11_59bfjk89_.arc recid=5 stamp=695945889

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_12_59bh3ssd_.arc recid=6 stamp=695947541

validation failed for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_13_59bh5t9v_.arc recid=7 stamp=695947605

validation succeeded for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_27/o1_mf_1_14_59cs8ops_.arc recid=8 stamp=695990695

validation succeeded for archived log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_27/o1_mf_1_15_59cvbqk9_.arc recid=9 stamp=695992807

Crosschecked 9 objects

   

   

RMAN> delete expired archivelog all;

   

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=140 devtype=DISK

   

List of Archived Log Copies

Key Thrd Seq S Low Time Name

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

1 1 7 X 25-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_7_59bdzfwg_.arc

2 1 8 X 26-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_8_59bdzlkm_.arc

3 1 9 X 26-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_9_59bdzoq3_.arc

4 1 10 X 26-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_10_59bdzpny_.arc

5 1 11 X 26-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_11_59bfjk89_.arc

6 1 12 X 26-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_12_59bh3ssd_.arc

7 1 13 X 26-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_13_59bh5t9v_.arc

   

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_7_59bdzfwg_.arc recid=1 stamp=695945343

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_8_59bdzlkm_.arc recid=2 stamp=695945346

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_9_59bdzoq3_.arc recid=3 stamp=695945349

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_10_59bdzpny_.arc recid=4 stamp=695945350

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_11_59bfjk89_.arc recid=5 stamp=695945889

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_12_59bh3ssd_.arc recid=6 stamp=695947541

deleted archive log

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_26/o1_mf_1_13_59bh5t9v_.arc recid=7 stamp=695947605

Deleted 7 EXPIRED objects

   

SQL> @fra2

   

PL/SQL procedure successfully completed.

   

   

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 1.78 36.4544 2

BACKUPPIECE 6.03 123.4944 2

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 19.46 398.5408

   

7 rows selected.

   

▣ 필요없는 테이블 정리

built-in table : emp, dept,bonus,salgrade

   

S SCOTT> select 'drop table "'|| tname || '" cascade constraint;'

  2  from tab where tname not in

  3  ('EMP','DEPT','BONUS','SALGRADE');

'DROPTABLE"'||TNAME||'"CASCADECONSTRAINT;'

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

drop table "T1" cascade constraint;

drop table "T2" cascade constraint;

drop table "T3" cascade constraint;

drop table "A" cascade constraint;

drop table "b" cascade constraint;

drop table "DEPT2" cascade constraint;

drop table "DEPT3" cascade constraint;

drop table "T5" cascade constraint;

drop table "T6" cascade constraint;

drop table "T7" cascade constraint;

drop table "T8" cascade constraint;

drop table "T9" cascade constraint;

drop table "A1" cascade constraint;

drop table "A2" cascade constraint;

drop table "A11" cascade constraint;

drop table "A3" cascade constraint;

drop table "A4" cascade constraint;

drop table "A5" cascade constraint;

drop table "ORDERS" cascade constraint;

drop table "MENU" cascade constraint;

drop table "E4" cascade constraint;

drop table "E5" cascade constraint;

26 개의 행이 선택되었습니다.

   

복사해서 실행한 후 purge 이용

Using Globalization Support

• 적용방식 : 환경변수, 설치 시, session, function
• 주의 : parameter 무시할 것 (과거버전 호환성 때문에 존재)
  • Alter session set ~~ : 가능
  • Alter system set ~~ : 불가능
• 확인 : v$nls_parameters
• 넣을 수 있는 값 : v$nls_valid_values

   

▣ Character Set

▶ Single-byte character sets

• 7-bit => 2^7 => 128 => 영어까지 해결
• 8-bit => 2^8 => 256 => 유럽어까지 해결

▶ Multibyte character sets, including Unicode

• 2byte => 2^16 => 64K 개 => 상용 한글 수
  • Ex) ko15mswin949
• 4byte => 2^32 => 4G 개 => 현존 모든 글자수
  • Ex) al32utf8

   

▣ Windows CharacterSet 변경

   

▶ 파라미터에서 확인하면 value가 비어있지만 실제 view로 확인하면 비어있지 않다는 것을 알 수 있다.

   

S SYS> desc v$nls_valid_values

Name Null? Type

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

PARAMETER VARCHAR2(64)

VALUE VARCHAR2(64)

ISDEPRECATED VARCHAR2(5)

   

S SYS> desc v$nls_valid_values

Name Null? Type

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

PARAMETER VARCHAR2(64)

VALUE VARCHAR2(64)

ISDEPRECATED VARCHAR2(5)

   

S SYS> select count(*) from v$nls_valid_values;

   

COUNT(*)

----------

494

   

S SYS> select count(distinct PARAMETER) from v$nls_valid_values;

   

COUNT(DISTINCTPARAMETER)

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

4

   

S SYS> select distinct PARAMETER from v$nls_valid_values;

   

PARAMETER

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

CHARACTERSET

SORT

TERRITORY

LANGUAGE

   

S SYS> select distinct parameter,count(*) from v$nls_valid_values

2 group by parameter;

   

PARAMETER COUNT(*)

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

CHARACTERSET 247

SORT 87

TERRITORY 96

LANGUAGE 64

   

   

   

▣ Unicode

• AL32UTF8 : 32는 늘어날 수 있는 최대값을 뜻함
• AL16UTF16 
  
                              <AL32UTF8>                                         <AL16UTF16>   

  

  ※ Encoding : 숫자와 문자 변환

   

▣ Client와 Server간 캐릭터 셋이 맞지 않는다면?

• 맞추면 되나 재설치 필요
• 차선 : nchar, nvarchar, isqlplus(브라우저에서 자동변환)

   

▣ Database Character sets and National Character Sets

Database Character Sets	National Character Sets
Defined at creation time	Defined at creation time
Cannot be changed without re-creation, few exception	Can be exchanged
Store data columns of type CHAR,VARCHAR2,CLOB,LONG => nls_characterset	Store data columns of type NCHAR,NVARCHAR2,NCLOB => nls_nchar_characters
Can store varying-width character sets	Can store Unicode using either AL16UTF16 or UTF8

   

▣ Specifying Language-Dependent Behavior

   

   

▣ Language-and Territory-Dependent Parameters

Parameter	Default values
NLS_NANGUAGE NLS_DATE_LANGUAGE NLS_SORT	AMERICAN AMERICAN BINARY
NLS_TERRITORY NLS_CURRENCY NLS_DUAL_CURRENCY NLS_ISO_CURRENCY NLS_DATE_FORMAT NLS_NUMERIC_CHARACTERS NLS_TIMESTAMP_FORMAT NLS_TIMESTAMP_TZ_FORMAT	AMERICA $ $ AMERICA DD-MM-RR ., DD-MON-RRHH.MI.SSXFF AM DD-MON-RRHH.MI.SSXFF AM TZR

▶ 확인 : V$NLS_VALID_VALUES

▶ 설정 : EX) ALTER SESSION SET "PARAMETER" = "DEFAULT VALUES"

 

▣ Searching and Sorting

Binary sorting : Sorted according to the binary values of the encoded characters

S SYS> ALTER SESSION SET NLS_SORT=BINARY;

   

Session altered.

   

S SYS> SELECT fr_word FROM words

2 ORDER BY fr_word;

FR_WORD

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

gelex

gele

gelee

gele

   

Multilingual linguistic sorting

• Based on the ISO standard (ISO 14651), and the Unicode 3.2 Standard for multilingual collation
• Ordered by the number of strokes, PinYin, or radicals for Chinese characters

     

  S SYS> SELECT fr_word

  2 FROM words

  3 ORDER BY NLSSORT(fr_word,'NLS_SORT=FRENCH_M');

  FR_WORD

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

  gele

  gele

  gelee

  gelez

   

▣ Case- and Accent-Insensitive Search and Sort : 대소문자, 악센트 무시

Specify the linguistic name

NLS_SORT=<NLS_sort_name> [_AI | _CI]

Examples

NLS_SORT=FRENCH_M_AI => M : Multi language

NLS_SORT = XGERMAN_CI

   

▣ Implicit Conversion Between CLOB and NCLOB : 자동 ( 이유 : 명시적 변환이 되지 않기 때문에 )

   

▣ Globaliztion support Features

• Language support
• Territory support
• Character set support
• Linguistic sorting
• Message support
• Date and time formats
• Numeric formats
• Monetary formats

※ 기타 셋팅 : lbuilder 사용해서 값 얻음. dbca나 alter session 문 사용

   

S SYS> r            한국어 지원하는 캐릭터 셋

1  select parameter,value from v$nls_valid_values

2* where parameter='CHARACTERSET' and (value like '%KO%' or value like '%UTF%')

PARAMETER            VALUE

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

CHARACTERSET         CL8KOI8U

CHARACTERSET         CL8KOI8R

CHARACTERSET         KO16KSC5601    =>     old 한 unix 계열에서 사용, nlssort 함수 사용

CHARACTERSET         KO16DBCS

CHARACTERSET         KO16KSCCS

CHARACTERSET         KO16MSWIN949     =>     windows 전용, binary sorting 사용

CHARACTERSET         AL24UTFFSS

CHARACTERSET         UTF8

CHARACTERSET         UTFE

CHARACTERSET         AL32UTF8      =>      unicode 계열의 characterset, binary sorting 사용

CHARACTERSET         KO16KSC5601FIXED

PARAMETER            VALUE

CHARACTERSET         KO16DBCSFIXED

CHARACTERSET         AL16UTF16      =>      unicode 계열의 characterset, binary sorting 사용

   

=>  설치전 nls_lang 설정시 사용한다.

binary sorting 사용하는 이유 : binary정렬, 문자 언어 정렬이 같음(nlssort 함수 필요 없음) 반대로 KO16KSC5601은 같지 않음

   

▶ 참고 NLS_LANG : 영어, 독어 알파벳 순서 같지만 영어, 러시아어 알파벳 순서 다름 

nls 관련 셋팅값은 방대함

  ↓↓↓↓↓

S SYS> select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')

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

thursday

S SYS> select * from v$nls_parameters where parameter='NLS_DATE_LANGUAGE';

PARAMETER            VALUE

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

NLS_DATE_LANGUAGE    AMERICAN

S SYS>

S SYS> alter session set NLS_DATE_LANGUAGE='KOREAN';

Session altered.

S SYS> select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')

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

???

S SYS> alter session set NLS_DATE_LANGUAGE='FRENCH';

Session altered.

S SYS> select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')

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

Jeudi

S SYS> select to_char('123456789','9,999,999,999,999,999L') from dual;  =>  현재 프랑스로 셋팅되어 있지만 $로 표시 => 지역 셋팅과 통화는 따로 설정해야 한다.

TO_CHAR('123456789','9,999,999,9                                              

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

                    123,456,789$

   

S SYS> alter session set nls_currency='EURO';

Session altered.

S SYS> select to_char('123456789','9,999,999,999,999,999L') from dual;

TO_CHAR('123456789','9,999,999,9

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

                 123,456,789EURO

   

양이 방대하기 때문에 검색하기 어렵기 때문에 lbuilder 사용한다.(GUI에서 사용한다)

▶ lbuilder : 새로운 나라, 언어가 생겼을 때 이를 적용하기 위함

        -> 기존값 보여줌(완전히 새로운 것은 없음. 즉 수정해서 만들어라)

         -> 모르는 나라와 언어의 적용을 위한 nls 값 확인

   

   

   

   

   

Oracle Transparent Data Encryption (TDE)

• Need for secure information
• Automatic encryption of sensitive information : ex) 급여, 카드번호, 비밀번호
  • Embedded in the Oracle database
  • No need to change application logic
  • Encrypts data and index values
• Using an encryption key
  • Master key for the entire database
  • Stored in Oracle Wallet

  ※ master key : 개인키 + 대칭형키 로 암호화 한것 (oracle 비밀번호 바꾸면 대칭형 키가 바뀜)

     

TDE : 투명하다 - dml 작업 시 다른 작업은 없지만

암호화했다 - datafile은 보호 (데이터 추출 X) 한다.

   

▣ Implementing Transparent Data Encryption

▶ Wallet 생성

   

▶ [oracle@edrsr4p1 ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

   

▶ Master Key Setting

SQL> alter system set encryption key identified by "welcome1";

   

▶ Create Table ( 암호화된 컬럼이 포함되며 sys 객체는 불가능하다 )

▶ 데이터 삽입

▶ Wallet 닫힌 상태에서 확인

   

▣ Backup the wallet

[oracle@edrsr4p1 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

   

#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

   

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=FILE)

(METHOD_DATA=

(DIRECTORY="/u01/app/oracle/product/10.2.0/db_1/")

)

)

   

[oracle@edrsr4p1 ~]$ ls /u01/app/oracle/product/10.2.0/db_1/ewallet.p12

/u01/app/oracle/product/10.2.0/db_1/ewallet.p12

[oracle@edrsr4p1 ~]$ cp /u01/app/oracle/product/10.2.0/db_1/ewallet.p12 ./

[oracle@edrsr4p1 ~]$

[oracle@edrsr4p1 ~]$ rman target /

   

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 23 15:56:10 2009

   

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

   

connected to target database: ORCL (DBID=1173970302)

   

RMAN> backup datafile 4;

   

Starting backup at 23-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=136 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

channel ORA_DISK_1: starting piece 1 at 23-AUG-09

channel ORA_DISK_1: finished piece 1 at 23-AUG-09

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_23/o1_mf_nnndf_TAG20090823T155611_591t0d36_.bkp tag=TAG20090823T155611 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 23-AUG-09

   

RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;

   

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters are successfully stored

   

RMAN> backup datafile 4; => Wallet 이 깨졌기 때문에 에러 발생

   

Starting backup at 23-AUG-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

channel ORA_DISK_1: starting piece 1 at 23-AUG-09

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/23/2009 15:56:28

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

   

   

S SYS> alter system set encryption key identified by "welcome1"; => Wallet 복구

   

System altered.

   

S SYS> !

[oracle@edrsr4p1 ~]$ rman target /

   

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 23 15:56:59 2009

   

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

   

connected to target database: ORCL (DBID=1173970302)

   

RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;

   

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters are successfully stored

   

RMAN> backup datafile 4; => wallet 복구했기 때문에 가능

   

Starting backup at 23-AUG-09

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=135 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

channel ORA_DISK_1: starting piece 1 at 23-AUG-09

channel ORA_DISK_1: finished piece 1 at 23-AUG-09

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_23/o1_mf_nnndf_TAG20090823T155702_591t1yvl_.bkp tag=TAG20090823T155702 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 23-AUG-09

   

▣ Existing Tables and TDE

• Add encrypted columns
  • ALTER TABLE emp ADD (ssn VARCHAR2(11) ENCYPT); 컬럼 추가 시 암호화
• Encrupt unencrypted columns
  • ALTER TABLE emp MODIFY (first_name ENCRYPT); 기존 컬럼 암호화
• Disable column encryption
  • ALTER TABLE emp MODIFY (first_name DECRYPT); 암호화된 컬럼 암호해제
• Add or remove salt
  • ALTER TABLE emp MODIFY (first_name ENCRYPT [NO] SALT); 성능향상 위해 암호화
• Change keys and the encryption algorithm
  • ALTER TABLE emp REKEY USING '3DES168';

암호화 방법

Window

▣ Window : 의미상 일을 하도록 허용하는 시간

▣ Windown 예)

   

   

Chain

▣ Chain에 사용할 프로그램 추가 생성

   

▣ Chain 생성

   

▶ Show SQL

   

▣ 생성한 Chain을 Job에 등록

   

▣ 실행 확인

   

▣ 관련 뷰

   

▶ 참고 : Job Class

   

Scheduler

▣ Scheduler 생성 (5초 간격)

   

▣ 생성한 Scheduler를 Job에 등록

   

▶ Show SQL

   

▣ 실제로 테이블에 insert 되는지 확인

   

▶ 참고 (Job Enable & Disable)

   

▣ 관련 뷰

   

▣ Time-Based Schedule

▶ SQl

BEGIN

DBMS_SCHEDULER.CREATE_JOB(

job_name=>'HR.DO_BACKUP',

job_type =>'EXECUTABLE',

job_action => '/home/usr/rman/nightly_incr.sh',

start_date=>SYSDATE,

repeat_interval=>'FREQ=DAILY;BYHOUR=23',

/* next night at 11:00 PM */

comments=> 'Nightly incremental backups');

End;

/

   

▣ Event-Based Schedule

• A queue specification (where your application enqueues messages to start a job)
• An event condition (same syntax as an Oracle Streams AQ(Access Queue) that if TRUE starts th job

   

▶ Event raised by Scheduler Jobs

• JOB_STARTED
• JOB_SUCCEEDED
• JOB_FAILED
• JOB_BROKEN
• JOB_COMPLETED
• JOB_STOPPED
• JOB_SCH_LIM_REACHED
• JOB_DISABLED
• JOB_CHAIN_STALLED
• JOB_ALL_EVENTS
• JOB_RUN_COMPLETED
• JOB_OVER_MAX_DUR