[오라클]오라클 강의

### 컨트롤 파일 ####

1. 컨트롤 파일 미러링
   1) spfile의 경우
   shutdown immediate; -- DB서버를 정지
   alter system set controlfiles = '/data/controlfile1.ctl', '/data/controlfile2.ctl' scope=spfile; -- 파일 추가
   cp /data/controlfile1.ctl /data/controlfile2.ctl ## OS에서 파일 복사
   startup
   2) pfile의 경우
   shutdown immediate; -- DB서버 정지
   vi /data/admin/pfileSID.ora 파일에 컨트롤 파일 추가
   cp /data/controlfile1.ctl /data/controlfile2.ctl ## OS에서 파일 복사
   startup

1. 컨트롤 파일 복구
   SQL> select spid, name from v$process, v$bgprocess where paddr(+) = addr; -- 프로세스 확인
   SQL> show parameter user_dump_dest

   NAME TYPE VALUE
   ------------------------------------ ----------- ------------------------------
   user_dump_dest string /data/oracle/app/oracle/admin/emsdb/udump -- 해당 디렉토리에는 컨트롤 파일을 재생성할 수 있는 스크립트가 저장되어 있음.
   SQL > shutdown immediate;
   cd /data/oracle/app/oracle/admin/emsdb/udump/
   grep controlfile *.trc ## 해당 파일 찾은 후
   cp -p SID_ora_PID.trc controlfile.sql ## 파일 변경 후
   sqlplus "/as sysdba"
   SQL > @controlfile.sql -- 실행 하면 컨트롤 파일 및 아카이브 파일이 생성 된다. 일부 에러 발생 가능성 있음.

### 리두로그 파일 ###

1. 리두 로그 파일 미러링
   SQL > select * from v$logfile; -- 로그 파일 확인
   SQL > select * from v$log; -- 현재 사용 중인 로그 파일 확인 및 MEMBERS 의 수가 1만 있을 경우 미러링이 안되어 있는 것임
   SQL > alter database add logfile group 2 '/data/redo/redolog2_1.log' size 500M; -- 그룹 생성
   SQL > alter database add logfile member '/data/redo/redolog2_2.log' to group 2; -- 2그룹에 대한 추가 파일 생성

### 아카이브 모드 확인 ###

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/archivelog
Oldest online log sequence 748
Next log sequence to archive 750
Current log sequence 750

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/archivelog
Oldest online log sequence 748
Next log sequence to archive 750
Current log sequence 750

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 748 YES INACTIVE
2 749 YES INACTIVE
3 750 NO CURRENT
4 595 YES INACTIVE
5 596 YES INACTIVE
6 597 NO CURRENT

6 rows selected.

SQL> select archiver from v$instance;

ARCHIVE
-------
STARTED

### 오프라인 백업(노아카이브 모드) ###

1. 주의 할 점
   오프라인 백업 시에는 정상적인 방법으로 종료(normal, immediate, transactional) 해야 한다. abort 종료의 경우 메모리 상의 할당된 내용도 즉시 종료 하고 데이터 베이스를 종료하기 때문에 나중에 스타트업 시에 복구 작업을 수행하게 된다. 이때 수행하는 프로세스는 SMON이다.
2. 백업 받을 파일은 컨트롤 파일, 데이터 파일, 리두로그 파일, 파라메터 파일 전부를 같은 시점에 백업 받아야 한다.
   한번에 같이 받지 않으면 복구 시점이 틀어지게 된다. 이유는 컨트롤 파일의 SCN 번호 부여가 바뀌기 때문이다.
3. 백업 전 확인 사항
   SQL > select TABLESPACE_NAME, FILE_NAME from dba_data_files; -- 데이터 파일의 구조 분석
   SQL > select GROUP#, MEMBER from v$logfile; -- 리두로그 파일의 구조 분석
   SQL > select * from v$confile; -- 컨트롤 파일의 구조 분석
   SQL > host edit /data/oracle/products/pfile/initSID.ora -- 파라메터 파일 구조 분석

   ## 오라클 DB 정상적인지 확인SQL > host edit moredept.sql
   alter system switch logfile;
   insert into scott.dept values(1,'Personnen','Pusan');
   insert into scott.dept values(2,'Personnen','Pusan');
   insert into scott.dept values(3,'Personnen','Pusan');
   alter system switch logfile;
   insert into scott.dept values(4,'Personnen','Pusan');
   insert into scott.dept values(5,'Personnen','Pusan');
   insert into scott.dept values(6,'Personnen','Pusan');
   alter system switch logfile;
   insert into scott.dept values(7,'Personnen','Pusan');
   insert into scott.dept values(8,'Personnen','Pusan');
   insert into scott.dept values(9,'Personnen','Pusan');
   commit;
   select count(*) from scott.dept
   SQL > @moredept

4. 특정 파일이 삭제되어 데이터 베이스 OPEN이 안될 경우
   startup -- 에러 발생될 경우 해당 파일을 아래의 명령어로 변경 후 open 시도 ## 컨트롤 파일의 파일경로 수정을 해주는 단계임.
   alter database rename file '/data/users01.dbf' to '/data/oradata/users01.dbf';
   alter database open;

### 아카이브 모드 백업-복구 ###

1. 아카이브 모드 변경 작업
   1) initSID.ora 파일에 아래의 내용을 넣는다.
   LOG_ARCHIVE_START = TRUE
   LOG_ARCHIVE_DEST = [archivefile의 경로와 파일명]
   LOG_ARCHIVE_DEST_n = [archivefile의 경로와 파일명] ## DB_RECOVERY_FILE_DEST 파라메터를 정의하면 안된다.(해당 경로는 10g에서 플래시백에서 사용함)
   LOG_ARCHIVE_FORMAT = [format type].[확장자] ## sample [%r_%s.arc]
   ## [%s]아카이브의 일련번호 : 1, 2, 3 ... [%S] 아카이브 파일의 일련번호를 0으로 채워줌 00001, 00002, 00003 ...
   ## [%t]데이터 베이스의 인스턴스를 구분해서 표시해줌 [%T] 001, 002, 003...
   ## [%r]재시작된 후 아카이브 파일의 혼동을 줄이기 위해 RESETLOG ID 값을 구분하기 위한 것임.alter database open resetlogs명령어에 의해..(10g부터 적용됨)
   DB_RECOVERY_FILE_DEST ="
   2) 아카이브 모드 변경
   startup mount
   alter database archivelog;
   alter database open;
2. 아카이브 모드 복구 절차(전체 복구 방법)
   1) FULL 백업 된 파일들의 백업 시점이 일치하는지 확인.
   2) 아카이브 모드로 운영중인지 확인.
   3) 최종 FULL 백업된 이후의 아카이브 로그 파일이 있는지 확인.
   4) 최종 리두로그 파일이 존재하는지 확인.(select * v$log;) -- 마운트 단계까지 들어간다.
   5) 장애가 발생한 데이터 파일 확인.(select * v$recover_file; select file_id, file_name from dba_data_files;) 에러가 발생한 DBF파일을 복사해 놓는다.
   6) 데이터 베이스 STARTUP
   7) recover database;
   8) auto
   9) alter database open
3. 아카이브 모드 복구 절차(테이블 스페이스 완전 복구 방법)
   1) 오픈단계에서 장애발생 테이블스페이스 기준으로 복구한다.
   2) 오프라인 백업 데이터의 생성일자를 확인한다.
   3) 아카이브 모드로 운영중인지의 여부 확인한다.
   4) DB shutdown 후에 해당 테이블 스페이스의 데이터 파일을 복사한다.
   5) DB startup 한다. 기동 시에 해당 데이터 파일의 시점 불일치 에러 메시지가 발생한다.(ORA-01113, ORA-01110)
   6) alter database datafle 'OOOO.DBF' offline ;
   7) ALTER DATABASE OPEN; -- 을 실행하여 정상적으로 OPEN 시도.
   8) ALTER TABLESPACE OOOO OFFLINE IMMEDIATE; -- 해당 테이블 스페이스는 ONLINE상태로 OFFLINE으로 변경해줘야 한다.
   9) RECOVER TABLESPACE OOOO;
4. 아카이브 모드 복구 절차(데이터파일 완전 복구 방법)
   1) 오픈단계에서 장애발생 데이터 파일기준으로 복구한다.
   2) 오프라인 백업 데이터의 생성일자를 확인한다.
   3) 아카이브 모드로 운영중인지의 여부 확인한다.
   4) DB shutdown 후에 해당 데이터 파일을 복사한다.
   5) DB startup 한다. 기동 시에 해당 데이터 파일의 시점 불일치 에러 메시지가 발생한다.(ORA-01113, ORA-01110)
   6) alter database datafle 'OOOO.DBF' offline IMMEDIATE;
   7) ALTER DATABASE OPEN; -- 을 실행하여 정상적으로 OPEN 시도.
   8) RECOVER DATAFILE 'OOOO.DBF';
   9) AUTO
5. 아카이브 모드 복구 절차(read-only 테이블 복구)
   1) 백업 파일 복사
   2) startup mount -- DB를 마운트 단계까지 간다.
   3) recover database
   4) alter database open;
   5) alter tablespace TABLESPACE_NAME read only;
6. 패러럴 복구 절차

   1) initSID.ora 파일 관련 파라메터 수정(Parallel_min_servers=2 / Parallel_max_servers=4 / Recovery_parallel_servers =4)
   2) select name from v$bgprocess; (P000, P001 등 프로세스가 수행됨)
   3) recover database parallel(degree 4)

7. 시간기반 불완전 복구 방법
   1) DROP 된 테이블을 DROP 시키기 전으로 복구
   2) 로그마이너 패키지로 확인 가능
   
   
   
   3) 백업된 모든 데이터 파일 복사
   ** 주의 **
   장애가 발생한 데이터 파일만 복사하여 아카이브 파일들을 순차적으로 적용하게 되면 복구 작업이 완료되었지만, 불완전 복구 방법에서는
   특정 파일만 재 설치해서는 모든 데이터를 과거 특정시점으로 되돌릴 수 없기 때문에 반드시 모든 데이터를 과거 시점으로 복구 해야 함.
   불완전 복구 작업을 수행할 때 가장 중요한 포인트 중에 하나는 마지막 오프라인 백업된 모든 데이터 파일들을 현재
   경로로 복사 해야 한다는 것임.
   이때, 마지막 컨트롤 파일이나 리두로그 파일들은 복사할 필요는 없으며, 데이터 파일이 깨진 것이므로 모든 데이터 파일들만 복사 해야 함.
   4) STARTUP MOUNT
   5) SET AUTORECOVERY ON
   6) RECOVER DATABASE UNTIL TIME '2011-07-12-12:10:10'; ## 시점 복구
   7) ALTER DATABASE OPEN; ## 해당 명령을 실행하면 아래와 같은 에러 메시지 발생.
   ORA- Can not open Database; Please Open Database with resetlog option
   8) ALTER DATABASE OPEN RESETLOGS; ## SCN과 모든 로그 파일이 새로 시작되는 것임
   archivce log list 로 확인 가능
   9) DROP 된 테이블 복구 확인 후 OFF-LINE 백업 꼭 수행해야 함!!!(## RESETLOGS로 로그파일이 초기화 되기 때문에 꼭 백업 해야 함)
8. 취소 기반 불완전 복구
   1) 복구 파일 복사 후 STARTUP MOUNT
   2) SET AUTORECOVERY ON
   3) RECOVER DATABASE UNTIL CANCEL; 과거 특정 시점까지 복구 완료
   ### 리두로그 파일 일부 삭제 시 복구
   
   ### 리두로그 파일 전체 삭제 시 복구
   
   
   
   ### 모든 컨트롤 파일이 삭제된 경우
   
   마지막 Current Redo log 파일을 지정해 주어야 함.
   ### 컨트롤 파일과 특정 datafile 삭제된 경우
   
9. 변경기반 불완전 복구 방법
   1) RECOVER DATAABASE UNTIL CHANGE 80014;

6/2 일 SQL 튜닝 교육 5090912_workshop.zip5090914_labs.zip

1. blocks 할당이 크고 avg_space의 크기가 크면 공간 낭비가 심한것임.
   SQL > select table_name, num_rows, blocks, avg_space, last_analyzed
   from user_tables
   where table_name like '%'
   SQL > save a1 -- 저장됨.
   SQL > get a1 -- 불러오기
   SQL > exec dbms_stats.gather_table_stats('sh','cust_test'); -- 통계정보 갱신
   SQL > exec dbms_stats.delete_table_stats('sh','sales_test'); -- 통계정보 삭제
2. blevel 이 4level 로 떨어질 경우 공간 활용도가 떨어지고 있다는 것임.
   SQL > select index_name, num_rows, distinct_keys, leaf_blocks, blevel
   from user_indexes
   where table_name = 'sales_test'
3. SQL TRACE
   SQL > alter session set tracefile_identifier = 'statement_trace';
   SQL > alter session set sql_trace=true;
   SQL > select max(cust_credit_limit) from customers
   where cust_city = ' Paris';
   SQL > alter session set sql_trace=false;
   #> tkprof orcl_ora_2364_statement_trace.trc run1.txt sys=no ## recursive SQL 문장 제외하라는 옵션(sys=no)
   SQL > create index cust_city_idx on customers(cust_city); -- index 생성 후 재실행해서 확인
4. STATSPACK 생성 방법
   SQL > @%ORACLE_HOME%\rdbms\admin\spcreate
   perfstat_password의 값을 입력하십시오: pertstat
   pertstat

   
   Choose the Default tablespace for the PERFSTAT user
   ---------------------------------------------------
   Below is the list of online tablespaces in this database which can
   store user data. Specifying the SYSTEM tablespace for the user's
   default tablespace will result in the installation FAILING, as
   using SYSTEM for performance data is not supported.

   Choose the PERFSTAT users's default tablespace. This is the tablespac
   in which the STATSPACK tables and indexes will be created.

   TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
   ------------------------------ --------- ----------------------------
   EXAMPLE PERMANENT
   SYSAUX PERMANENT *
   USERS PERMANENT

   Pressing <return> will result in STATSPACK's recommended default
   tablespace (identified by *) being used.

   default_tablespace의 값을 입력하십시오: USERS

   Using tablespace USERS as PERFSTAT default tablespace.

   
   Choose the Temporary tablespace for the PERFSTAT user
   -----------------------------------------------------
   Below is the list of online tablespaces in this database which can
   store temporary data (e.g. for sort workareas). Specifying the SYSTEM
   tablespace for the user's temporary tablespace will result in the
   installation FAILING, as using SYSTEM for workareas is not supported.

   Choose the PERFSTAT user's Temporary tablespace.

   TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
   ------------------------------ --------- --------------------------
   TEMP TEMPORARY *

   Pressing <return> will result in the database's default Temporary
   tablespace (identified by *) being used.

   temporary_tablespace의 값을 입력하십시오: temp
   SQL > show user
   SQL > exec statspack.snap; -- 스냅샷 찍음.

5. SQL Access Advisor TEST
   SQL > exec dbms_stats.gather_table_stats('sh','sales_test');
   SQL> select * from sales_test where cust_id = 100866;
   SQL> select * from sales_test1 where cust_id=100866;
   SQL> select c.cust_id, s.channel_id, sum(s.amount_sold)
   from customers c, sales s
   where c.cust_id = s.cust_id
   group by c.cust_id, s.channel_id
6. Materialized View
   - JOIN하는 테이블들에 Mview 생성이 되면 summary 된 하나의 테이블 형식으로 조회가 되며, 사용자가 기존의 쿼리를 사용하여
   조회를 하더라도 Mview가 자동으로 자체 쿼리를 생성하여 응답속도를 빠르게 조회한다.
7. leaf block 공간 낭비 확인
   SQL> select lf_rows_len, del_lf_rows_len, (del_lf_rows_len/lf_rows_len)*100 "Delete %" from index_stats
   SQL> update cust_test set cust_id=cust_id + 20000000
   where rownum <= 2000; -- 업데이트 후 확인작업
   SQL> analyze index cust_id_idx validate structure; -- 통계 정보 갱신 후 블럭 확인
   SQL> select lf_rows_len, del_lf_rows_len, (del_lf_rows_len/lf_rows_len)*100 "Delete %" from index_stats
   LF_ROWS_LEN DEL_LF_ROWS_LEN Delete %
   ----------- --------------- ----------
   911322 29881 3.27886301 <== %가 20% 이상이면 공간 활용도가 낮음으로 rebuild 필요함.

   1 개의 행이 선택되었습니다.
   SQL> alter index cust_id_idx rebuild;

8. Unused Index 모니터링
   SQL> select 'alter index '||index_name||' monitoring usage;' from user_indexes;
   SQL> select table_name, index_name, monitoring, used from v$object_usage

   SQL> exec dbms_stats.gather_schema_stats('hr'); -- 통계정보 갱신 후 확인하게 되면 index가 사용된 걸로 확인된다.(안좋음ㅡㅡ;)

6/3일 SQL 튜닝 교육

1. function based index
   SQL> create index func_first_name_idx on cust_test(upper(cust_first_name));
   SQL> select index_name, index_type, status from user_indexes
   where table_name = 'CUST_TEST';
   SQL> select index_name, column_name, column_position
   from user_ind_columns
   where table_name = 'CUST_TEST'
   order by 1, 3;
   SQL> select index_name, column_expression
   from user_ind_expressions
   where table_name = 'CUST_TEST';
2. HINT
   - /*+ */ <== 힌트 포맷, 여러개의 힌트 사용시 한 포맷안에서 띄어쓰기로 작성.
3. TRACE
   SQL> set autot traceonly explain
   SQL> select /*+ index(sales_test prod_time_idx) */ *
   from sales_test
   where prod_id=13
   and time_id between to_date('19980510', 'yyyymmdd')
   and to_date('19980522','yyyymmdd');
4. Meterialized view
   SQL> create materialized view cust_sales_mv
   enable query rewrite
   as
   select c.cust_id, s.channel_id, sum(s.amount_sold)
   from customers c, sales s
   where c.cust_id = s.cust_id
   group by c.cust_id, s.channel_id;
   SQL> create materialized view cust_sales_mv1
   enable query rewrite
   as
   select c.cust_id, s.channel_id, sum(s.amount_sold)
   from customers c, sales s
   where c.cust_id = s.cust_id
   group by c.cust_id, s.channel_id;
   SQL> set autot traceonly explain
   SQL> alter materialized view cust_sales_mv disable query rewrite;
5. Using DBMS_MVIEW Package
   declare
   task_nm varchar2(20) := 'emp_tune';
   begin
   dbms_advisor.tune_mview(task_name=>task_nm,
   mv_create_stmt=>'create materialized view emp_mv
   as
   select department_id, avg(salary)
   from employees
   group by department_id');
   end;
   /
6. FLASHBACK
   - 9i 에서는 DML 에 대해 flashback query
   - 10g Database :
   
   . table --> Drop
   --> Table
   --> Version
   --> Transaction
   #### flashback transaction
   SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;
   SQL> delete from dept where deptno=40;
   SQL> select * from dept as of timestamp
   to_timestamp('2011/06/03 15:46:07', 'yyyy/mm/dd hh24:mi:ss') -- 과거 특정 시간대로 돌아갈 경우
   where deptno=40 -- 과거 시간대 40번 부서가 있었는지 확인
   SQL> select * from dept as of timestamp
   systimestamp - interval '10' minute -- 과거 시간대로 돌아갈 경우
   where deptno=40 -- 과거 시간대 40번 부서가 있었는지 확인
   SQL> select * from dept as of timestamp
   sysdate - 10/1440 -- 과거 시간대로 돌아갈 경우(10분/60분 환산 * 24)
   where deptno=40 -- 과거 시간대 40번 부서가 있었는지 확인
   SQL> show parameter undo
   undo_retention integer 900 -- 복구 가능한 최대 시간(900초)
   undo 공간이 부족할 경우 해당 과거 시간까지의 데이터를 보장 못함(젠장)
   SQL> show recyclebin -- 휴지통에서 확인(drop된 테이블 확인할 수 있음)
   ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
   ---------------- ------------------------------ ------------ -------------------
   EMP_FLASH BIN$s/Vv2p9dSemiALQ05QJWuw==$0 TABLE 2011-06-03:16:09:14
   EMP_FLASH BIN$ahDZ8KZxQ2+26wiG+vjimA==$0 TABLE 2011-06-03:16:07:48
   EMP_FLASH BIN$aS6Q/kqETYa2NLqb7m+LHA==$0 TABLE 2011-06-03:16:06:36
   SQL> select * from "BIN$ahDZ8KZxQ2+26wiG+vjimA==$0"
   #### flashback drop
   SQL> flashback table emp_flash to before drop; -- drop 테이블 복원
   플래시백이 완료되었습니다.
   SQL> flashback table "BIN$aS6Q/kqETYa2NLqb7m+LHA==$0" to before drop; -- 특정 테이블 복구시
   플래시백이 완료되었습니다.
   SQL> flashback table "BIN$ahDZ8KZxQ2+26wiG+vjimA==$0" to before drop rename to emp_new; -- 테이블 이름을 변경 하여 복구도 가능
   플래시백이 완료되었습니다.
   #### flashback table
   16:24:37 SQL> insert into dept values(50,'New','CA');

   1 개의 행이 만들어졌습니다.

   16:25:07 SQL> commit;

   커밋이 완료되었습니다.

   16:25:36 SQL> update dept set loc = 'Test1' where deptno=10;

   1 행이 갱신되었습니다.

   16:25:51 SQL> commit;

   커밋이 완료되었습니다.

   16:25:54 SQL> select * from dept;

   DEPTNO DNAME LOC
   ---------- -------------- -------------
   50 New CA
   10 ACCOUNTING Test1
   20 RESEARCH DALLAS
   30 SALES CHICAGO
   16:27:59 SQL> alter table dept enable row movement; -- 테이블의 row 이동 가능 활성화
   16:28:07 SQL> flashback table dept to timestamp to_timestamp('2011/06/03 16:24:37', 'yyyy/mm/dd hh24:mi:ss'); -- 테이블 자체 rollback
   플래시백이 완료되었습니다.