빅데이터과정/WORKSHOP 1 썸네일형 리스트형 #25_140717_WSHOP_DATABASE LINK # DATABASE LINK 마지막문제. 프로시저를 아래와 같이 window 쪽 DB에서 수행하면 리눅스의 DB에 있는 테이블이window 쪽에도 만들어지게 하시오 CREATE OR REPLACE PROCEDURE pro150(p_input VARCHAR2)IS begin EXECUTE IMMEDIATE 'CREATE TABLE '||p_input||' AS SELECT * FROM employees@dbdblink';END;/ 점심문제. 리눅스 DB의 SCOTT 계정의 테이블들을 윈도우 DB쪽의 SCOTT 계정의 테이블로 생성했다. 윈도우쪽에 scott 계정의 테이블과 리눅스 쪽에 SCOTT 계정의 테이블의 차이가 몇 개인지 숫자로 출력되게 하는 SQL을 작성하시오 리눅스 – 윈도우 = 2윈도우 – 리.. 더보기 #25_140717_WSHOP_EXTERNAL TABLES # EXTERNAL TABLES emp1.txt n EXTERNAL TABLES(scott) # ext1.txt 파일을 /oracle/home/ 위치에 저장해야 한다SQL> create directory emp_dir2 as '/home/oracle/';SQL> drop table ext_emp;SQL> create table ext_emp (emp_id number(3),emp_name varchar2(10),hiredate date)organization external(type oracle_loaderdefault directory emp_dir2access parameters(records delimited by newlinefields terminated by ","(emp_name char,e.. 더보기 #25_140717_WSHOP_DATA PUMP # DATA PUMP l data pump 를 사용하는 이유?- 특정 테이블을 물리적 파일로 생성해서 백업을 하고 싶을 때- truncate 같은 경우는 rollback, flashback 둘다 불가능한데 이럴 때 import를 이용한다 l Data pump의 모드 4가지?1. Table mode2. schema mode- SCOTT 유저가 가지고 있는 모든 테이블들을 전부 export 받고 싶을 때3. tablespace mode4. database mode - export pump : DB내의 테이블을 물리적 덤프 파일로 생성- import pump : 물리적 덤프파일을 DB에 테이블로 생성 l export와 import : 9i 버전까지 사용l export pump 와 import pump : 10.. 더보기 #25_140717_WSHOP_DIRECT PATH LOAD # DIRECT PATH LOAD DIRECT PATH INSERT l HWM 위로 데이터를 입력하는 방법1. serial 하게 데이터를 입력 : 하나의 프로세서 데이터를 입력힌트 : /*+append*/2. parallel 하게 데이터를 입력 : 여러 개의 프로세서가 데이터를 입력힌트 : /*+parallel*/ n append insert 문제153. emp테이블과 똑같은 emp800 이라는 테이블을 생성하고(emp테이블의 구조만 가져와서 생성). emp테이블의 data를 emp800에 입력하는데 HWM 위로 입력하시오 create table emp800 asselect * from emp where 1=2; insert /*+ append */ into emp800select * from emp;c.. 더보기 #24_140716_WSHOP_DIRECT LOAD INSERT # DIRECT LOAD INSERT l 17장. 오라클의 데이터 이행방법 3가지 1. direct load insert : 서브쿼리를 사용한 insert 인데 high water mark 위쪽으로 insert 2. direct path insert (SQL*Loader) : 외부의 text file을 DB의 테이블에 입력 3. data pump : 데이터베이스와 데이터베이스간의 데이터 이상 - 논리적인 테이블을 물리적 덤프 파일로 생성해서 데이터를 이동(주루 백업할 때 사용) 문제148. scott 으로 접속해서 emp 테이블과 똑 같은 구조를 갖는 emp700 테이블을 생성하고high water mark 위쪽에 데이터를 입력하시오 SQL> insert /*+ append */ into emp700se.. 더보기 #24_140716_WSHOP_복구 어드바이져 # 복구 어드바이져 l 복구 어드바이져란- 오라클 장애상황을 감지하여 복구 스크립트를 생성해주는 기능 l 복구 어디바이져가 안되는 경우1. RAC 환경2. 너무 복잡한 장애상황일 때 l 명령어 3개 수행1. RMAN> list failure;-- 현재 장애상황을 알려줌2. RMAN> advise failure;- 현재 장애상황에 대한 스크립트를 생성하는 단계3. RMAN> repair failure;- 복구 스크립트를 자동으로 수행 n 그밖의 명령어- SELECT * FROM v$ir_failure;: 복구 내용 출력- SELECT * FROM v$ir_manual_checklist;: 오류 내용- SELECT * FROM v$ir_repail;: 조치 내역 출력 SQL> select file_name .. 더보기 #24_140716_WSHOP_백업(RMAN) # 백업(RMAN) l RMAN 을 사용해서 백업과 복구를 했을 때 장점(P15-8)1. 간단한 명령어로 백업과 복구를 쉽게 할 수 있다2. database에 있는 모든 파일들을 전부 백업3. 백업가능 파일- datafile- controlfile- archive log file- parameter file4. 백업불가능한 파일- redo log file : 사용자 관리 백업- password file : 깨지면 다시 생성하면 된자5. disk 나 테이프에 백업을 할 수 있다 l RMAN 으로 백업했을 떄 백업되는 유형 2가지 (p15-7)1. backup set : 하나의 상자 안에 여러 개의 data file 들을 넣어주는 백업셋2. image copy : 원본하고 똑같이 copy3. 두 개의 차이점.. 더보기 #24_140716_WSHOP_백업(사용자관리) # 백업(사용자관리) l 백업 1. 사용자 관리 백업 : OS 명령어로(CP 명령어) 백업- cold backup (shutdown 상태)- hot backup (=online backup, open 상태) 2. rman을 사용한 백업 : rman 명령어로 백업- cold backup (mount 상태)- hot backup (open 상태) l 사용자 관리 백업 - hot backup (=online backup): DB의 data file 들만 백업: data file 들의 동기화정보를 갱신하지 않도록 해놓고 OS 명령어로 백업 - 동기화정보 : DB에 있는 파일들이 모두 같은 시점의 파일들임을 증명하는 정보 SQL> select file#, checkpoint_change#from v$datafil.. 더보기 #23_140715_WSHOP_백업/복구 # 백업/복구 l 장애의 6가지 유형1. 문장에러- 코드의 오류, 권한 부족 : 코드수정, 권한부여로 해결 2. 유저프로세서 에러- 클라이언트 PC 또는 응용프로그램이 비정상적으로 종료 : 다시 시작시켜 해결한다- pmon 이 알아서 commit 안한 data는 rollback 해준다 3. 네트웍 에러- 클라이언트에서 서버로 오라클 접속이 안된다- 원인1) 서버의 리스너가 내려간 경우 – lsnrctl start(리스너를 올린다)2) 물리적 네트웍 구성이 잘못된 경우(랜카드에 이상이 있거나 랜선이 빠지거나) – 백업네트웍을 구성해야 한다 4. 유저의 실수 : flashback 기술로 복구한다- 실수로 테이블 drop- 실수로 data를 다 지우고 commit- 주의할 사항 : truncate table .. 더보기 #22_140714_WSHOP_자동화된 유지관리 # 자동화된 유지관리 l 악성SQL 관리를 위해서 할일l 개발자 : 악성 SQL을 작성해서는 안된다l DBA : 옵티마이저 테이블 통계정보를 잘 관리해야 한다- parsing : 실행계획 생성 – full table scan, index scan 할지 결정- execute : 검색- fetch 1. 매일 22시에 3가지가 수행된다- 테이블들에 대해서 통계정보 자동 수집- 하루동안 발생했던 악성 SQL들 중에 가장 문제되는 SQL들을 자동으로 튜닝- 공간 어드바이저가 공간 튜닝을 함(조각모음)- 확인 : select client_name, status- 상태 disablebegindbms_auto_task_admin.disable(client_name =>'auto optimizer stats collec.. 더보기 #22_140714_WSHOP_테이블 통계정보 # 테이블 통계정보 1. 오라클에서 사용하는 통계의 종류- 시스템 통계 : 한시간에 한번씩 자동으로 수집되는 메모리 성능 정보- 테이블 통계 : 테이블에 대한 분석정보(예 : 테이블 건수, 테이블 크기 등등)select table_name, num_rows, last_analyzedfrom user_tables; 2. 테이블 통계정보를 수집하는 명령어- exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’); 3. 테이블 통계정보의 사용용도는 옵티마이저로 더 좋은 실행계획을 생성할 수 있도록 한다- 옵티마이저의 역할은 실행계획을 생성하는 오라클 프로세서인데 테이블 통계정보를 이용해서 효율적인 실행계획을 세운다- 이를 위해서는 많은 통계정보가 필요하다 4. 테이블 통계정보.. 더보기 #22_140714_WSHOP_SNAPSHOT # SNAPSHOT 1. snapshot 을 수동으로 하는 명령어- exec dbms_workload_repository.create_snapshot;- @?/rdbms/admin/awrrpt.sql 2. 악성 SQL 관리를 위해서 해야할 일- 개발자 : 악성 SQL을 작성해서는 안된다- DBA : 옵티마이저 테이블 통계정보를 잘 관리해야 한다 3. snapshot을 자동으로 하는 방법과 수동으로 하는 방법- 수동 : exec dbms_workload_repository.create_snapshot;- 자동(p12-10) : statistics_level = typical 또는 all 로 지정 4. statistics_level 파라미터 확인- SQL> show parameter statistics_le.. 더보기 #22_140714_WSHOP_AUDIT_FINE-GRAINED AUDITION # FINE-GRAINED AUDITION l fined grained auditing- 특정 조건에 해당하는 select 문과 DML문을 통째로 감사되도록 하는 기능 1. audit_sys_operations를 true 로 설정하면 sysdba 권한을 가진 유저도 감사 대상이 되도록 할 수 있다 2. 상세 감사 기능을 활성화 시키기전에 system tablepspace full 이 발생하지 않도록 주의 해야한다. 3. DB 보안을 위해서 평상시 DBA가 신경써야할 것- sys 패스워드는 자주 변경해야한다 - 오라클 otn 사이트에 수시로 접속해서 보안에 관한 패치를 적용해야 한다(otn.oracle.com) 문제101. soctt 으로 접속해서 월급이 3000 이상인 사원들을 삭제하고 그 delete .. 더보기 #21_140711_WSHOP_AUDIT # AUDIT 1. 감사기능을 활성화- audit_trail1) false : 감사 모니터링 안하겠다2) db : 감사 로그를 database에 저장하겠다3) os : 감사 로그를 OS에 저장하겠다4) XM : 감사 로그를 xml 형태로 os에 저장하겠다. 2. scott 계정 audit 조회 show parameter audit; - audit 을 킨다 SELECT username, action_name, TIMESTAMP, terminal FROM DBA_AUDIT_TRAILWHERE username='SCOTT'; - audit 을 조회 SQL> show parameter audit; NAME TYPE VALUE------------------------------------ ----------- .. 더보기 #21_140711_WSHOP_최소 권한의 원칙 적용 # 최소 권한의 원칙 적용 문제97. sys 유저에서 adams 에게 database에 있는 어느 테이블이든 다 조회할 수 있는 권한을 주고 데이터 딕셔너리 조회가 가능한지 확인하시오 - select any table 권한을 받았다 하더라도 데이터 딕셔너리(dba_xxxx_의 접근을 제한 하기 위한 파라미터를 설정해줘야 한다 SQL> grant select any table to adams;SQL> connect adams/tigerSQL> select table_name from dba_tables;- O7_DICTIONARY_ACCESSIBILITY 가 false 로 되어있기 때문에 조회 안됨ERROR at line 1:ORA-00942: table or view does not exist SQL> .. 더보기 #21_140711_WSHOP_UNDO DATA # UNDO DATA undo data가 지원하는 내용 1. rollback 2. flashback 기능 - flashback query : 특정 테이블의 과거 data를 쿼리- flashback table 3. undo 데이터를 위해 DBA가 관리해야할 파라미터 # undo_management : auto로 관리 # undo_retention : commit을 했어도 데이터를 복구할 수 있는 시간 # undo_tablespace : - undo segment 를 저장한다. - undo_tablespace는 retention 시간을 보장하지 않기 때문에 retention 크기를 늘렸으 면 tablespace 공간도 늘려줘야 한다. - DML 너무작은 undo tablespace는 DML 작업을 수행하지 .. 더보기 #21_140711_WSHOP_ DEAD LOCK #DEAD LOCK l DEAD LOCK(P9-31)- update를 하게되면 행(row)에 lock을 건다- 행에 락을 걸기 떄문에 해당하는 행의 모든 데이터는 접근 불가능하다- 2개의 SCOTT 세션으로 접속하고 아래의 순서로 수행하면 5번째 A 세션에서 오류가 발생한다 SCOTT (A) SCOOT (B)1SQL> update emp 2 set sal = 0 3 where ename='KING'; 2SQL> update emp 2 set sal=9000 3 where ename='ALLEN';3SQL> update emp 2 set deptno=40 3 where ename='ALLEN'; 4SQL> update emp 2 set deptno =40 3 where ename='KING';5ERROR .. 더보기 #21_140711_WSHOP_LOCK충돌 # LOCK 충돌 접속된 세션의 정보를 확인하고 오라클에서 kill 수행 SELECT sid, serial#, usernameFROM v$sessionWHERE sid IN (SELECT blocking_session FROM v$session); SID SERIAL# USERNAME---------- ---------- ------------------------------ 34 37 SCOTT alter system kill session '34,37' immediate; l 락이 걸린 해당 세션을 kill 시키고 락이 풀리는 것을 확인할 수 있다 접속된 세션의 정보를 확인하고 OS에서 kill 수행 - spid : 해당 세션의 OS의 프로세서 IDSET LINESIZE 100COLUMN spid F.. 더보기 #21_140711_WSHOP_임시테이블 # 임시테이블 l 임시테이블- 잘못지울 수 있기 떄문에 데이터를 저장하고 분석한후 나중에 지워야 하는 부담감이 줄어든다.- delete from emp; (20분 걸림, 롤백떄문에 오래걸림)- truncate from emp; (0.0000001초. 롤백 없음) 1. 내 세션이 끝나기 전까지 data를 유지- on commit preserve rowsCREATE GLOBAL TEMPORARY table emp708(empno number(10),ename varchar2(10),sal number(10))on commit delete rows; INSERT INTO emp708 VALUES(1111,'lee',1111);INSERT INTO emp708 VALUES(2222,'lee',2222); SEL.. 더보기 #20_140710_WSHOP_유저관리 # 유저관리 문제63. king 이라는 유저를 아래와 같이 생성하시오 create user kingIDENTIFIED BY tigerDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempACCOUNT UNLOCKPASSWORD EXPIRE;GRANT CREATE SESSION TO king;grant unlimited tablespace to king; l DEFAULT TABLESPACE users - king 유저가 사용할 기본 테이블 스패이스l TEMPORARY TABLESPACE temp - select 구문 정렬작업하기 위한 disk 공간l PASSWORD EXPIRE; tieger 패스워드를 바로 소멸시키겠다.king으로 접속할 때 새롭게 패스워드를 설정해야 .. 더보기 #20_140710_WSHOP_네트워크 구성 # 네트워크 구성 클라이언트리스너서버tnsnames.ora listener.ora서버의 IP 주소HOST=edydr1p0.us.oracle.com) 서버의IP주소서버의 포트PORT=1521 서버의 포트프로토콜PROTOCOL=tcp 프로토콜서비스 이름(pmon이 알려줌)Service "dbdb" 서비스이름 - alter system register : pmon이 리스터에게 서비스 이름을 알려주도록 하는 명령어. 서비스가 올라오지 않을 때 수행 LISTENER 문제56. listener.ora 파일이 있는 위치로 이동하시오 cd $ORACLE_HOME/network/adminlsl 해당 위치로 가면 listerner.ora 파일이 없으면 netca 명령어로 생성해준다 n 리스너 내용 # listener.or.. 더보기 #19_140709_WSHOP_SCRIPT 확인 # SCRIPT 확인 문제46. 현재 DB에 있는 테이블스패이스의 생성 스크립트를 출력하시오 set long 50000select dbms_metadata.get_ddl('TABLESPACE', tablespace_name)FROM dba_tablespaces;l dbms_metadata : 테이블이나 인덱스나 tablespace 같은 DB 오브젝트의 생성 스크립트를 추출할 수 있게 하는 패키지l 다른 서버에 테이블 스패이스를 이식할 떄 한번에 돌릴 수 있다 문제47. scott 유저로 접속해서 scott 유저가 가지고 있는 테이블의 생성 스크립트를 출력하시오 SELECT dbms_metadata.get_ddl('TABLE', table_name, 'SCOTT') FROM user_tables; 더보기 #19_140709_WSHOP_TABLESPACE # TABLESPACE 문제19. data01 이라는 테이블 스패이스를 생성하고 data01 테이블 스패이스에 emp02 테이블을 생성하시오 CREATE TABLESPACE data01DATAFILE 'c:\data01.dbdf' SIZE 50m;l data01 테이블 스패이스를 생성 CREATE TABLE emp302(empno NUMBER(10),ename VARCHAR2(10),sal NUMBER(10))TABLESPACE data01; SELECT table_name, tablespace_nameFROM user_tables WHERE table_name='EMP302'; 문제22. data01 테이블 스패이스의 50MB 공간을 더 늘리시오(data file 을 data01에 추가한다) ALTER.. 더보기 #19_140709_WSHOP_DYNAMIC PERFORMANCE # DYNAMIC PERFORMANCE - v$fixed_table – v$로 시작하는 dynamic performance view의 사전- dictionary – data dictionary(user_tables, all_tables, dba_tables) 의 사전: user_tables : 내가 생성한 테이블: all_tables : 내가 생성한 테이블 + 다른 유져가 권한준 테이블: dba_tables : DB에 있는 모든 테이블- SQL> select * from dictionary n Dynamic Performance 조회SQL> select * from v$fixed_table;SQL> select * from v$fixed_tablewhere name like '%CONTROL%'; 문제3.. 더보기 #19_140709_WSHOP_깔끔하게 출력하는법 create or replace procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER is l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(4000); l_status integer; l_descTbl dbms_sql.desc_tab; l_colCnt number; begin execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '; dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); dbms_sql.describe_colu.. 더보기 #19_140709_WSHOP_장애복구 # 장애복구 DB에 문제가 생겼을 때 문제를 진단하기 위해서 열어봐야할 파일은 다음과 같다 1. alert log file: DB에 장애가 발생했을 떄 에러번호와 메세지: DB를 startup, shutdown 한 정보: 데이터베이스의 구조를 변경했을 때 수행한 명령어2. trace file: 실제로 장애를 일으킨 상세한 정보를 보고자 할 때는 trace file을 열어봐야 한다 alert log file 과 trace file 외에 진단을 하기 위한 정보가 들어있는 뷰는 v$로 시작하는 dynamic performance view n 메모리에 올라온 SQL에 대한 정보를 보는 뷰 select sql_textfrom v$sql; n 악성 SQL 조회(cpu_time : 마이크로초) select sql_t.. 더보기 #19_140709_WSHOP_SHUTDOWN 옵션 # SHUTDOWN 옵션 SHUTDOWN 옵션 normaltransactionalimmediateabort새로운 유저의 접속을 허용NONONONO기존접속 유저들의 접속YESNONONO기존 진행되고 있던 DML 작업YESYESNONOCheckpoint Event(Memory->Disk)YESYESYESNO l 불가피하게 shutdown aobrt 를 수행해서 DB를 내렸다면 세션을 내리고 shutdown immediate로 내렸다가 다시 올리면 빠르게 올라온다 l startup force – shutdown abort + startup 더보기 #17_140708_WSHOP_INSTANCE # INSTANCE 4장. Instance 관리(1) 오라클 DB와 instance 를 관리하는 툴 소개(2) 오라클 인스턴스의 구조정보를 담고 있는 파일인 파라미터 파일 관리방법(3) DB의 startup 옵션(4) DB의 shutdown 옵션(5) altert log file : DB에 문제가 생겼을 때 그 문제를 해결하기 위한 정보가 들어있는 파일(6) 정보A. 에러번호와 에러 메시지 B. DB를 올렸다 내렸다 할 때 생기는 정보 ALERT LOG FILE l alert log file 의 위치확인 SQL> show parameter dump NAME TYPE VALUE------------------------------------ ----------- ------------------------.. 더보기 #16_140707_WSHOP_데이터베이스 구조2 # 데이터베이스 구조 1. DB BUFFER CACHE cache 속성으로 만들어야할 테이블은 무엇인가? - 자주 엑세스하는 크기가 작은 테이블- 테이블을 그냥 생성하면 디폴트가 noCache 이다 테이블의 속성이 Cache 인지 noCache 인지 확인하는 방법 SELECT table_name, CACHE FROM user_tables; emp 테이블을 cache 속성으로 변경하는 방법 alter table emp cache; emp 테이블을 엑세스할 때 메모리의 MRU로 무조건 올리고 싶을데 alter table 권한이 없을경우 힌트를 쓴다 SELECT /*+ cache */ ename, sal FROM EMP WHERE ename='SCOTT'; l 이 SQL로 읽은 데이터는 full table s.. 더보기 #16_140704_WSHOP_데이터베이스 구조 # WORKSHOP l 오라클 DB의 구조1. database + instance (memory)- 오라클 database 내의 3가지 파일 : data file, control file, redo log file2. data file : data가 저장되어 있는 파일- select file_name FROM dba_data_files;3. control file : db를 control 하기 위한 파일(DB의 구조정보)- 파일들의 위치와 이름, 상태정보- SELECT NAME FROM v$controlfile;4. redo log file : DB의 변경사항을 적어놓는 파일.아래의 명령어가 log file로 들어감장애 발생이 복구하기 위한 파일- DML문 : insert, update, delete, .. 더보기 이전 1 다음