NOARCHIVELOG 모드

- 데이터베이스를 설치하면은 설정되는 기본모드 입니다.

- 체크포인트가 발생한 후 즉시 리두 로그 파일을 재사용 할 수 있습니다.

- 리두 로그가 겹쳐 쓰여지면서 변경정보가 없어지므로 마지막 전체 백업에 대해서만 복구가 가능합니다.

- 데이터베이스가 종료(NORMAL, IMMEDIATE, TRANSACTIONA옵션만 가능<이 세개의 옵션만 ckpt가 수행된 이후 데이터베이스가 shutdown되므로 모든 데이터파일, control file, Redo Log File이 동기화 되어져 있으므로>)되었을 때만 복구 가능한 백업 본 생성이 가능합니다.

- 백업 할 때마다 전체 데이터파일 및 Controlfile을 백업해야 합니다. NoArchiveLog Mode의 DB는 동기화되어 있으므로(NORMAL, IMMEDIATE, TRANSACTIONAL을 사용할 경우) 반드시 온라인 로그 파일을 백업해야 하는 것은 아닙니다.

- Redo Log 파일이 겹쳐 써지기 때문에 마지막 전체 백업 이후의 모든 데이터가 손실 됩니다.

ARCHIVELOG 모드

- 다 쓰여진 리두 로그 파일은 Log Switch가 일어나기 전 체크포인트가 발생하고 ARCn 프로세스에 의해 리두로그 파일을 백업할 때까지(Archivelog 파일 생성) Redo Log File은 재사용될 수 없습니다.

- Archived Log File은 Media 장애가 발생 했을 때 데이터가 손실되지 않도록 데이터베이스를 보호합니다.

- ARCHIVE Log Mode는 온라인 상태에서 데이터베이스를 백업할 수 있습니다.(Hot Bakup)

◈ 운영 중인 오라클의 로그 모드를 확인합니다.

SQL> ARCHIVE LOG LIST; 데이터베이스 로그 모드 아카이브 모드가 아님 자동 아카이브 사용 안함 아카이브 대상 C:\oracle\ora92\RDBMS 가장 오래된 온라인 로그 순서 17 현재 로그 순서 19

◈ PFILE을 이용하여 ARCHIVE LOG MODE로 변경

1. 파라미터 파일에서 수정한다.(INIT<SID>.ORA파일의 parameter 수정) # 아카이브 프로세스를 오라클 시작과 함께 실행하도록 설정 # log switch 발생시 자동으로 archive를 수행 합니다 LOG_ARCHIVE_START = TRUE # 아카이브 로그 파일을 저장할 디렉토리 설정 LOG_ARCHIVE_DEST = "C:\oracle\ora92\database\archive" # 아카이브 로그 파일의 이름 설정 LOG_ARCHIVE_FORMAT = %S.ARC 2. 데이터베이스를 종료 합니다(NORMAL, IMMEDIATE, TRANSACTIONAL) SQL> SHUTDOWN IMMEDIATE 3. 데이터베이스를 Mount 상태로 시작합니다. SQL> STARTUP MOUNT 4. ALTER DATABASE 명령을 사용하여 데이터베이스의 모드를 변경합니다. SQL> ALTER DATABASE ARCHIVELOG; 5. 데이터베이스를 OPEN합니다. SQL> ALTER DATABASE OPEN; 6. ARCHIVE LOG MODE가 정상적으로 설정되어 있는지 확인합니다. SQL> ARCHIVE LOG LIST; 데이터베이스 로그 모드 아카이브 모드 자동 아카이브 사용ㄴ 아카이브 대상 C:\oracle\ora92\database\archive 가장 오래된 온라인 로그 순서 17 아카이브할 다음 로그 19 현재 로그 순서 19 7. 데이터베이스에 대한 전체 백업을 수행합니다.

※ LOG_ARCHIVE_FORMAT 옵션
- %S : redo 로그 시퀀스 번호를 표시하여 자동으로 왼쪽이 0으로 채워져 파일 이름 길이를 일정하게 만든다.
- %s : redo 로그 시퀀스 번호를 표시하고, 파일 이름 길이를 일정하게 맞추지 않는다.
- %T : redo 스레드 넘버를 표시하며, 자동으로 왼쪽이 0으로 채워져 파일 이름 길이를 일정하게 만든다.
- %t : redo 스레드 넘버를 표시하며, 파일 이름 길이를 일정하게 맞추지 않는다.
※ NOARCHIVELOG 모드에서 ARCHIVELOG 모드로 변경한 후에는 Control file정보가 변경되어 이전의 백업본은 사용할 수 없으므로 반드시 모든 데이터파일 및 Control file을 백업하여야만 합니다.


◈ SPILE을 수정하여 데이터베이스를 ARCHIVE LOG MODE로 변경하기

1. SPFILE설정 -- sqlplus 실행 C:\> sqlplus /nolog -- SYSDBA 권한으로 접속 SQL> conn / as sysdba --LOG_ARCHIVE_START 파라미터 변경 SQL> ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE; --LOG_ARCHIVE_DEST 파라미터 변경 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST=’C:\oracle\ora92\database\archive’ SCOPE=SPFILE; --log_archive_format 파라미터 변경 SQL> ALTER SYSTEM SET log_archive_format = 'name_%t_%s_%r.arc' scope=spfile; %t = 스레드 넘버, %s = 시퀸스 넘버, %r = reset logs 10g에서는 반드시 %s, %t, %r를 모두 설정 2. 데이터베이스를 종료 합니다(NORMAL, IMMEDIATE, TRANSACTIONAL) SQL> SHUTDOWN IMMEDIATE 3. 데이터베이스를 Mount 상태로 시작합니다. SQL> STARTUP MOUNT 4. ALTER DATABASE 명령을 사용하여 데이터베이스의 모드를 변경합니다. SQL> ALTER DATABASE ARCHIVELOG; 5. 데이터베이스를 OPEN합니다. SQL> ALTER DATABASE OPEN; 6. ARCHIVE LOG MODE가 정상적으로 설정되어 있는지 확인합니다. SQL> ARCHIVE LOG LIST; 데이터베이스 로그 모드 아카이브 모드 자동 아카이브 사용ㄴ 아카이브 대상 C:\oracle\ora92\database\archive 가장 오래된 온라인 로그 순서 17 아카이브할 다음 로그 19 현재 로그 순서 19 7. 데이터베이스에 대한 전체 백업을 수행합니다. 아카이브 로그모드 비활성화 > ALTER DATABASE NOARCHIVELOG; SQL> show parameter db_recovery_file_dest; 아카이브로그 경로확인 SQL> show parameter recovery *db_recovery 관련정보 8. DB startup 후 archive file 이 정상적으로 쌓이는지 확인 SQL> ALTER SYSTEM SWITCH LOGFILE; 9. 설정한 경로에 아카이브 파일이 생성되었는지 확인

운영중 아카이브 경로수정 및 기동

sys@TGDOM>show parameters spfile ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/10.1.0/dbs
/spfileTGDOM.ora
sys@TGDOM>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /a01b/TGDOM/arch
Oldest online log sequence 1487
Next log sequence to archive 1488
Current log sequence 1488
sys@TGDOM>show parameters log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=/a01b/TGDOM/arch MAND
ATORY REOPEN

sys@TGDOM> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/a01b/TGDOM/arch2' SCOPE=MEMORY;
System altered.

sys@TGDOM>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /a01b/TGDOM/arch2
Oldest online log sequence 1487
Next log sequence to archive 1488
Current log sequence 1488

sys@TGDOM>ALTER SYSTEM SET log_archive_dest_1='LOCATION=/a01b/TGDOM/arch2' SCOPE=both ;
System altered.

sys@TGDOM>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /a01b/TGDOM/arch2
Oldest online log sequence 1487
Next log sequence to archive 1488
Current log sequence 1488

sys@TGDOM>ALTER SYSTEM SET log_archive_dest_1='LOCATION=/a01b/TGDOM/arch' SCOPE=both ;
System altered.

## ARchive Process 수동 시작
sys@TGDOM>ALTER SYSTEM ARCHIVE LOG START;
System altered.

select distinct field1

from tb_test

이렇게 하면 field1에 있는 데이터 중 중복을 제거하고 유니크 한 값들만 나온다.

그러나 시간 개념이 들어가서 최신의 데이터에 해당하는 row의 값을 다 가져 오고 싶다면?

distinct에 시간까지 걸면 모든 데이터가 다 유니크 해버린다.

oracle db 데이터 중 특정 필드의 중복을 제거하고 최신의 데이터 1개만 가져오기.

숨어 있던 rowid가 이때 도움을 준다.

select *

from tb_test

where reg_tm between start_tm and end_tm

and rowid in (select max(rowid) from tb_test group by field1)

중복이 되면 안되는 필드를 group by 해서 rowid를 큰 것 순으로 뽑아내면 된다.

...라고 썼는데, 생각해보니.. scan 범위를 더 줄일 수 있을 것 같다.

물론 이건 환경과 쓰는 사람 마음에 따라 다르다. 내가 찾은 예제에선 저렇게 되어 있었고..

나는 시간 범위 내에서 중복된 값 중 최신의 것만 찾아내면 되니까.

select *

from tb_test

where rowid in (select max(rowid) from tb_test where reg_tm between start_tm and end_tm group by field1)

요렇게 작성하면 스캔 범위가 줄어들어 위의 쿼리보단 성능향상에 도움이 될 수 있을 것 같다.

이 구문은 rowid가 숫자일 경우에만 유효합니다.

9. 2000년 이후 입사자 출력
SELECT *
FROM employees
WHERE hire_date >= '00/01/01';

10. 문자열의 정렬(문자도 크기를 갖는다)
SELECT *
FROM jobs
WHERE job_title > 'President';

11. 논리연산자 OR를 이용한 조건식
SELECT *
FROM jobs
WHERE job_title = 'President'
OR job_title = 'Sales Manager';

12. 논리연산자 AND를 이용한 조건식
SELECT *
FROM employees
WHERE salary >= 10000
AND TO_CHAR(hire_date, 'YYYY') = '1994';

문제) 급여가 5000~10000인 사원 출력

문제) manager_id가 147이고, department_id가 80인 사원 출력

13. between~and : 논리연산자 AND의 특수한 상황
--급여가 5000~10000인 사원 출력
SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000;

14. IN : 논리연산자 OR의 특수한 상황 -> 자주 사용하는 표현
SELECT *
FROM jobs
WHERE job_title IN ('President', 'Sales Manager');

14. LIKE : 부분 문자열을 이용한 조건. 패턴문자와 함께 사용.
--이름이 특정 글자로 시작하는 경우
SELECT *
FROM employees
WHERE first_name LIKE 'St%';

문제) 전화번호 지역번호가 650인 경우만 출력.
전화번호는 650.123.1234 형태로 구성됨. LIKE 연산자 이용.

문제) employees 테이블에서 first_name의 두번째 글자가 'an'인 경우만 출력
SELECT *
FROM employees
WHERE first_name LIKE '_an%';

15. IS NULL : NULL 값 확인
SELECT *
FROM employees
WHERE commission_pct IS NULL;

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

-------------------------------------
ORDER BY 구문 사용
- 순서 지정
- ASC(Ascending, 오름차순), DESC(Desceding, 내림차순)

16. 이름순으로 정렬해서 출력
SELECT *
FROM employees
ORDER BY first_name ASC;

17. 급여를 기준으로 내림차순 정렬해서 출력
SELECT *
FROM employees
ORDER BY salary DESC;

문제) 커미션을 받는 사원 정보 출력하되, 급여가 높은순으로 출력.
SELECT
FROM
WHERE
ORDER BY

-------------------------------------
집계 함수
- SUM(), AVG(), COUNT(), MAX(), MIN()
- 주의. 집계 함수는 집계 함수들만 단독으로 사용해야 합니다.

18. 직원의 수
SELECT COUNT(*) FROM employees;

19. 최대 급여의 액수
SELECT MAX(salary) FROM employees;

문제) 직원들의 평균 급여액
SELECT AVG(salary) FROM employees;

----------------------------------
GROUP BY 구문 사용
- 집계 함수와 같이 사용하기 위한 그룹 기준 제시
- SELECT 컬럼명, 집계함수 FROM 소스 GROUP BY 컬럼명;

20. job_id별 직원의 수 출력
SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;

문제) 전화번호 앞 3자리를 가지고 지역별 직원의 수 출력.
SELECT SUBSTR(phone_number, 1, 3), COUNT(*)
FROM employees
GROUP BY SUBSTR(phone_number, 1, 3);

21. 입사년도별 직원의 수 출력. 년도별 정렬해서 출력.
SELECT TO_CHAR(hire_date, 'YYYY'),COUNT(*)
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY')
ORDER BY TO_CHAR(hire_date, 'YYYY') ASC;

문제) 직무(job_id)별 직원의 평균 급여 출력.

문제) job_id별 직원의 수 출력. 입사년도가 1996년도만.

--------------------------------------------
HAVING 구문 사용
- GROUP BY 구문에서 조건 지정시 사용.
- WHERE 구문과 유사한 역할.

22. job_id별 직원의 수 출력. 단, 10명 이상인 경우만.
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id
HAVING COUNT(*) >= 10;

문제) 직무(job_id)별 직원의 평균 급여 출력. 단, 10000달러 이상만 출력.
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= 10000;

문제) 직무(job_id)별 직원의 평균 급여 출력.
단, 10000달러 이상이면서 직무가 'MAN'으로 끝나는 경우만 출력.