웹 개발과 관련된 모든 것들

RAW 파일 한쪽만 권한을 바꾸어 주고 마운트시 정상적으로 마운트 되나

아래와 같은 오류 발생함...

ORA-01157: 데이터 28 파일을 식별 또는 잠금 할 수 없습니다- DBWR 추적 파일을 보십시오

ORA-01110: 28 데이터 파일: '/dev/rnvms06'

 

If you connect to sqlplus AS SYSDBA you could try

ALTER SYSTEM CHECK DATAFILES ; 
ALTER SYSTEM CHECKPOINT;

데이터 파일 복구 하도록 하고

양쪽 시스템 동기화 맞추기 ~

RAC 시스템 모두에서 위와 같은 명령을 턱 ~

원래는 에러를 발생하지 않게 하는게 정상임...

실행법

sqlldr id/password control=KB51_COL.CTL

 

[db1:/orabackup]$vi KB51_COL.CTL
"KB51_COL.CTL" 26 lines, 1165 characters
LOAD DATA
INFILE 'KB51_COL.LST'
BADFILE 'KB51_COL.BAD'
DISCARDFILE 'KB51_COL.DIS'
APPEND                  -- 추가로 삽입만 한다.
        INTO TABLE KB51_20090226
        fields terminated by ","
       (
                                    TELG_COM    CHAR(51),
                                    JOIN_NO    CHAR(15),
                                    JOIN_BSN_NO    CHAR(10),
                                    VCHR_CARD_ID    CHAR(16),
                                    APROV_NO    CHAR(8),
                                    APROV_DT    CHAR(8),
                                    SALE_DT    CHAR(8),
                                    PAY_DT    CHAR(8),
                                    SETTLE_AMT    CHAR(13),
                                    AGREE_AMT    CHAR(13),
                                    GOV_PAY_AMT    CHAR(13),
                                    INCM_TAX    CHAR(13),
                                    SOCI_TAX    CHAR(13),
                                    FEE    CHAR(13),
                                    CNCL_TP    CHAR(1),
                                    TX_GB    CHAR(1),
                                    VCHR_BLNC    CHAR(6) nullif VCHR_BLNC = blanks  -- 공백일경우 NULL로
        )

External Table

- Oracle 9i 부터 제공하는 기능으로, Table이 Database 내부의 Tablespace가 아닌 Database 외부의 OS 파일로 존재하게 하는 기능이다.

- 흔히 DW(Data Warehousing) 환경에서 기본적인 ETL(Extraction, Transformation, Loading) 작업에 사용된다.

- External table 의 데이터엔 읽기전용으로만 접근이 가능하고, 저장되는 형식은 일정한 포맷이 정해진 것이 아니라, 규칙적인 포맷으로만 저장이 되어 있으면 읽어올때 그 포맷을 지정하여 불러올 수 있다. 이렇게 일정한 규칙으로 생성되어 있기 때문에 다른 Database 에서도 이 데이터를 사용할 수가 있는 장점이 있다. → 플랫폼에 독립적이다.

- External Table의 데이터에 대해 select, join, sort 를 할 수 있고, view, synonym 도 생성할 수 있다. 그러나 데이터는 읽기전용이기 때문에 Update, Insert, Delete는 불가능하다. index 생성도 불가능하다.

※ Possible - select, join, sort, view, synonym

※ Impossible - update, insert, delete, index

 

- External Table 에 대한 metadata 정의는 아래 형식으로 한다.

SQL> CREATE TABLE table_name ORGANIZATION EXTERNAL ...

- External Table에 데이터를 처음 Unload 하게 되면, 자동으로 Select 문장의 데이터타입을 기준으로 metadata가 생성된다.

 

- External Table로 접근하는 방법

(1) ORACLE_LOADER : external 파일의 데이터를 읽어오는 기능. SQL*Loader 의 문법을 따른다.

(2) ORACLE_DATAPUMP : external 파일에 데이터를 쓰는 기능을 하고, 이를 다시 Database에 다시 reload 한다.

                                      Database로부터 데이터를 읽고 External Table에 insert 한다. 한번에 여러개 파일 가능.

 

- External Table의 장점

(1) 병렬 SQL 수행가능

(2) Table로 Loading 불필요

(3) Storage 절약

(4) Virtual Read-only Table

(5) External regular table 간 조인가능

 

- External Table의 단점

(1) index 생성 불가 (파일처리가 더 빠름)

(2) Join 이나 Filter 수행이 어려움

 

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

CREATE EXTERNAL TABLE

 

- CREATE TABLE ... ORGANIZATION EXTERNAL 구문을 사용하여 생성하기는 하지만, 실제로 Table이 생성되는 것은 아니다. external table은 어떤 extents 와도 관련이 되어 있지 않다.

- External Data에 접근하려면 Data Dictionary 에 metadata를 생성해야 한다.

 

 

[예제] Creating External Tables

- 여기서 사용하는 employees table은 hr.employees table 구조를 그대로 사용하였다.

- 필요한 권한: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE

- Directory 오브젝트의 권한 : READ, WRITE

 

 

1. 아래처럼 External Table을 사용할 디렉토리를 생성하고 oracle 관리자가 관련파일에 접근할 수 있도록 디렉토리에 대한 권한을 변경한다.

 

% cd /export/home/kangyw/oracle_test

% mkdir data log bad

% chmod -R 777 data log bad

 

 

 

 

2. data 디렉토리에 External Table의 데이터 파일 2개를 생성한다. (empxt1.dat, empxt2.dat)

 

% cd data

% vi empxt1.dat

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

 

% vi empxt2.dat

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

 

 

3. External Table의 디렉토리를 지정하고, kangyw user에게 해당 디렉토리에 대한 권한을 준다.

   

 

Data Dictionary 에 해당 디렉토리 정보가 등록되고, 이 디렉토리 접근은 kangyw 만 가능하다.

 

 

 

4. External Table에서 DB내부 Table로 Unload를 하기 위해 필요한 employees table은

hr user의 employees table 구조를 사용해야하므로, kangyw user 에게 hr.employees table과 동일한 구조로 employees table을  생성해준다. (subquery as 구문 사용)

 

SQL> CREATE TABLE kangyw.employees AS SELECT * FROM hr.employees WHERE EMAIL = ' ';

 

 

 

5. External Table admin_ext_employees을 생성한다.

dat 파일이 생성시 지정되기 때문에 지금 정의하는 External Table 을 Select 하게 되면, OS상의 dat 파일을 읽어들이게 된다.

 

CREATE TABLE admin_ext_employees (
       employee_id     NUMBER(4),
       first_name      VARCHAR2(20),
       last_name       VARCHAR2(25),
       job_id          VARCHAR2(10),
       manager_id      NUMBER(4),
       hire_date       DATE,
       salary          NUMBER(8,2),
       commission_pct  NUMBER(2,2),
       department_id   NUMBER(4),
       email           VARCHAR2(25))

      

       ORGANIZATION EXTERNAL (
              TYPE ORACLE_LOADER      ☞ 접근방식 지정 (ORACLE_LOADER / ORACLE_DATAPUMP)

                                                           default : ORACLE_LOADER

              DEFAULT DIRECTORY admin_dat_dir  
              ACCESS PARAMETERS)  ☞ ㅂ

 

       records delimited by newline
       badfile admin_bad_dir:'empxt%a_%p.bad'
       logfile admin_log_dir:'empxt%a_%p.log'
       fields terminated by ','                                       ☞ 필드구분자는 쉼표(,)로 함.

                missing field values are null (
                        employee_id, first_name, last_name, job_id, manager_id,
                        hire_date char date_format date mask "dd-mon-yyyy",
                        salary, commission_pct, department_id, email)
                )

                Location('empxt1.dat', 'empxt2.dat')         ☞ 데이터파일 위치 지정
        )

        PARALLEL       ☞ data 소스에서 parallel 쿼리가 가능하게 함. 많은 양의 데이터를 다룰때 사용.
        REJECT LIMIT UNLIMITED;     ☞ External Table 조회 시 발생하는 에러 수에 제한이 없음.

 

 

생성 후 바로 조회하면 데이터 확인이 가능하다.

 

 

 

6. External Table admin_ext_employees 의 내용(OS상 dat 파일)을 DB 내의 employees table에 Insert 한다.

 

 

 

employees table을 조회하면 데이터가 들어가있는 것을 확인할 수 있다.

 

 

 

7. 데이터가 정상적으로 Loading 되면 로그를 확인한다.

 

 

 

 

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

ALTER EXTERNAL TABLE

 

SQL> ALTER TABLE admin_ext_employees REJECT LIMIT 100;

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN REFERNCED;

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

SQL> ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

SQL> ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');

SQL> ALTER TABLE admin_ext_employees Location('empxt3.txt', 'empxt4.txt');

- PARALLEL, ADD COLUMN, MODIFY COLUMN, DROP COLUMN, RENAME TO 는 일반 테이블변경방식과 동일함.

 

--실수로 지운 데이터 살리기..

예 kfm08ot1이라는 테이블의 bnk_cd ='04' 인 데이터를 실수로 삭제를 했다.

commit; 도 완료된 상태라면..

앞이 막막할것이다.

이럴땐 이렇게 데이터를 불러보자..

SELECT * FROM KFM08OT1
 as of timestamp ( systimestamp - interval '10' minute)
where bnk_cd = '04'
조회후  파일을 txt나 엑셀로 저장후..

다시 임포트 해야 합니다.

물론 데이터를 삭제하기 전에는.. 반드시 백업을 받아두고 하는것이.. 제일

중요한 일이지만.. 사람인 이상.. 실수 할수도 있을거라 생각됩니다.

위에 쿼리가 어느정도 한계가 있으니..   지울때는 꼭 데이터 받는 것을 생활화

.. 해야 하지 않을까 합니다.

select * from emp as of timestamp( systimestamp-interval '10' minute) where deptno=20

select * from emp as of timestamp( systimestamp-interval '10' minute) where deptno=20

출처 : http://www.oracleclub.com/article/13624

CREATE OR REPLACE PACKAGE ORHOME.PC_LOG_PROCESSING AS
 PROCEDURE LOG_TRIGGER (LOG_ERROR VARCHAR2);
END PC_LOG_PROCESSING;

CREATE OR REPLACE PACKAGE BODY ORHOME.PC_LOG_PROCESSING AS -- BODY
 PROCEDURE LOG_TRIGGER (LOG_ERROR VARCHAR2) IS
  FILE_HANDLE UTL_FILE.FILE_TYPE;
  BEGIN
   FILE_HANDLE := UTL_FILE.FOPEN('/log','procedure_log','a');
   UTL_FILE.PUT_LINE(FILE_HANDLE,LOG_ERROR);
   UTL_FILE.FCLOSE(FILE_HANDLE);
  EXCEPTION
   WHEN UTL_FILE.INVALID_MODE THEN
   NULL;
   RAISE_APPLICATION_ERROR(-20322,'file invalid mode');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
   NULL;
   RAISE_APPLICATION_ERROR(-20322,'file invalid filehandle');
   WHEN OTHERS THEN
   NULL;
 END LOG_TRIGGER;
END PC_LOG_PROCESSING;

1. 유저명과 권한 을 표시되게 하기

set sqlprompt "_user _privilege> "

만약 sysdba 지정하면

SYS AS SYSDBA>

로 출력된다.

날짜와 같이 프롬프트를 수정하고자 한다면

SQL> set sqlprompt "_user _privilege 'on'  _date >"

SYS AS SYSDBA on 06-JAN-04 >

연결된 인식자까지 표시하고 싶다면

SQL> set sqlprompt "_user 'on' _date 'at' _connect_identifier >"

ANANDA on 06-JAN-04 at SMILEY >

이곳은 SMILEY 라는 SID를 통해 연결 된것입니다.

1. 지워진 테이블 조회

SQL> SELECT * FROM RECYCLEBIN;

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

RECYCLETEST 테이블이 지워진 것을 확인 가능하며,

SQL> FALSHBACK TABLE RECYCLETEST TO BEFORE DROP;

DROP 하기 전의 상태로 RECYCLETEST 테이블을 복원한다.

위와 같이 복구하는 방법은 무지 쉬우나...

단. !!!

PURGE RECYCLEBIN;

이나

DROP TABLE RECYCLETEST PURGE;

한 경우에는  영구적으로 삭제된다 -_-;;;

걍 DROP만하자 ~

2. 테이블 버젼과 플래쉬 백

CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;

 

위와 같이 같은 테이블 명으로 여러번 지울 경우

 

같은 테이블명으로 복구가 불가능 하므로

 

TEST를 TEST1, TEST2로 각각 복구하는 방법은 아래와 같음

 

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1

1. command line 에서 expdp 사용
    결과물을 저장할 디렉토리를 지정한다.
        SQL> create directory data_pump_test as '/home/oracle/data_pump_test/';

    생성한 디렉토리 오브젝트에 대한 권한을 유저에게 부여한다.
        SQL> grant read, write on directory data_pump_test to hr;

    사용자에게는 EXP_FULL_DATABASE, IMP_FULL_DATABASE 롤을 부여한다.
    실제로는 resouce 롤만으로도 기본적인 expdp, impdp 동작을 수행할 수 있다.
   
    expdp 실행
    # expdp hr/hr directory=data_pump_test dumpfile=test_dump.dmp

    - interactive command line
        expdp 진행 중에 컨트롤+C 키를 누르면 Export> 프롬프트가 뜬다.
        여기서 help를 타이핑하면 사용할 수 있는 명령들을 확인할 수 있다.

    data_pump_test 디렉토리에 생성된 export.log 파일의 내용을 확인한다.

    ※ {exclude|include} object_type[: "name_expr"]
            ex)
            - # expdp hr/hr directory=data_pump_test dumpfile=test_dump.dmp exclude=index:"like 'EMP%'"
            - # expdp hr/hr directory=data_pump_test dumpfile=test_dump.dmp include=view
        exclude는 해당 오브젝트를 제외하고 추출/입력
        include는 해당 오브젝트만을 추출/입력
       
        contents={all | metadata_only | data_only}

2. 병렬 처리
    parallel 파라메터는 몇 개의 스레드로 병렬 처리 할 것 인지 지정한다.
        # expdp hr/hr tables=paratest directory=data_pump_test dumpfile=paratest%U.dmp parallel=3 job_name=parr_test;

    paratest01.dmp, paratest02.dmp, paratest03.dmp 세 개의 파일이 생성된다.    

        # impdp hr/hr directory=data_pump_test dumpfile=paratest%U.dmp job_name=parr_test;

실행 예

SQL> create user dpumpuser identified by dpumpuser;
SQL> grant connect, resource, exp_full_database, imp_full_database to dpumpuser;
SQL> create directory data_pump as '/home/oracle/data_pump';
SQL >grant read, write on directory data_pump to dpumpuser;

$ expdp dpumpuser/dpumpuser full=y directory=data_pump dumpfile=20081030.dmp
$ impdp dpumpuser/dpumpuser directory=data_pump dumpfile=20081030.dmp

SQL> revoke read, write on directory data_pump from dpumpuser;
SQL> revoke connect, resource, exp_full_database, imp_full_database from dpumpuser;
SQL> drop directory data_pump;
SQL> drop user dpumpuser;

※ 만약 impdp 가 퍼미션 문제로 실행되지 않는다면 SYSDBA 로 로그인한 후,
SQL> execute dbms_metadata_util.load_stylesheets;
을 실행한 후 재시도.

특정 스키마의 구조와 데이터 추출
$expdp dpumpuser/dpumpuser schema=PORTAL directory=data_pump dumpfile=portal_schema_20081031.dmp

content를 사용해 특정 스키마의 데이터만 추출 (all | metadata_only | data_only)
$expdp dpumpuser/dpumpuser schemas=PORTAL content=data_only directory=data_pump dumpfile=portal_schema_data_20081031.dmp

table_exists_action 옵션 사용, 기존의 데이터를 truncate 하도록 (skip | append | truncate | replace)
    skip - 존재하는 오브젝트에 대해 임포트 스킵
    append - 기존 오브젝트에서 업는 행만을 임포트
    truncate - 기존 테이블 truncate
    replace - drop & recreate
$ impdp dpumpuser/dpumpuser table_exists_action=truncate directory=data_pump dumpfile=portal_schema_data_20081031.dmp

데이터 파일, 테이블 스페이스, 유저는 각각 다음의 옵션으로 변경할 수 있다.
REMAP_DATAFILE=’C:\user01.dbf’:’/usr/data/user01.dbf’
REMAP_TABLESPACE=’users’:’user’
REMAP_SCHEMA=scott:stralth

다음 옵션으로 expdp에 사용되는 공간을 추정할 수 있다.
estimate_only=Y
$ expdp dpumpuser/dpumpuser full=y estimate_only=Y

출처 ; http://devideby0.egloos.com/2097445

오라클 새로운 버젼이 나오면 항샹 봐야한다...

Oracle Database 10g: The Top 20 Features for DBAs

 

오라클 10g의 신기능 특징을 기술한 페이지

 

http://www.oracle.com/technology/pub/articles/10gdba/index.html

 

20주에 걸쳐 작업을 함..

 

Schedule

Week 1—Flashback Versions Query Week 2—Rollback Monitoring Week 3—Tablespace Management Week 4—Oracle Data Pump Week 5—Flashback Table Week 6—Automatic Workload Repository Week 7—SQL*Plus Rel 10.1 Week 8—Automatic Storage Management Week 9—RMAN Week 10—Auditing

Week 11—Wait Interface Week 12—Materialized Views Week 13—Enterprise Manager 10g Week 14—Virtual Private Database Week 15—Automatic Segment Management Week 16—Transportable Tablespaces Week 17—Automatic Shared Memory Management Week 18—ADDM and SQL Tuning Advisor Week 19—Scheduler Week 20—Best of the Rest