Brand new JayK

서버 컴퓨터를 따로두고 쿼리박스를 이용해 접속하고자 하니

"'192.168.0.*' is not allowed to connect to this MySQL server ~ " 와 같은 에러가 발생 했다.

처음엔 방화벽 혹은 단순 네트워크 문제라 생각했지만 구글링해보니 문제는 간단하였다.

계정에 IP 권한을 막는 기능이 존재 한다고 한다.

아래와 같은 쿼리를 해당 서버컴에 날려주면 쉽게 해결된다.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username' @'%' identified by 'passwd';

1. 오라클 다운로드

http://www.oracle.com 접속

Log Miner 활용과 Redo log 장애

○ Log Miner

- Oracle 8i 버전부터 Binary 형식으로 되어있는 Redo log File의 내용을 조회할 수 있도록 Log Miner를 제공

- Redo log File에 있는 내용을 추출해서 SQL로 변환한 후 사용자가 SQL 문장으로 조회해서 원하는 내용 확인할 수 있음

- Redo log File 내부에는 데이터가 변경된 시점을 정확하게 알 수 있기 때문에 변경이력을 추적해서 원래 값으로

돌려야 할 경우 등에 유용

○ Log Miner의 제약 사항

- Log Miner는 8i 이후 버전에서만 작동

- 분석 대상 인스턴스와 같은 플랫폼에서 동작하고 같은 Character set을 사용하는 db에서 생성된 Redo Log File만 분석할 수 있음

- 분석 대상 인스턴스와 Redo log가 생성된 인스턴스는 DB_BLOCK_SIZE가 동일해야 한다.

다를 경우 Corruption 발생 내용이 기록

- Supplemental logging이 enable되어 있어야 함

○ supplemental logging

- 데이터에 DML이 발생할 경우 Redo log에 추가적인 내용을 기록 (기본 값 보다 자세한 내용 기록)

- supplemental log 기능을 사용할 경우 redo log 생성 양이 많아지기 때문에 아카이브 로그 모드를 사용 할 경우 아카이브 경로를

충분하게 주고 사용해야 한다

- 기본 값은 disable

min level supplemental logging 조회 및 변경

# 조회

Offline 백업 본을 이용한 DB 복구

1) shutdown immediate

2) OS 상에서 Cold 백업해둔 파일로 대체

--151228 날짜 백업 본을 이용해 복구

SELECT 'cp /oracle11/backup/20151228' ||substr(name,instr(name,'/',-1))||' '||name

FROM v$datafile

union all

SELECT 'cp /oracle11/backup/20151228' || substr(name,instr(name,'/',-1))||' '||name

FROM v$controlfile

union all

SELECT 'cp /oracle11/backup/20151228' || substr(name,instr(name,'/',-1))||' '||name

FROM (select member as "NAME"

from v$logfile);

3) startup

Control File 장애 복구

** Control File이 과거 버전이면 반드시 다시 만들어야 함(redo를 읽는건 datafile)

실습 1. Parameter File의 경로와 실제 파일 경로가 다른 경우 대처하기

==> Parameter File 에 적힌 control file 경로와 실제 control file 의 경로가 달라서 생기는 에러로 두 경로만 일치시키면 간단히 해결

※ 실습환경 만들기

SQL> create pfile from spfile;

* spfile로 운영시 spfile은 삭제 후 open

-- 복구 수행 2) 나머지 손상되지 않은 control file copy 후 DB open

!cp /oracle11/oracle/oradata/testdb/control01.ctl /oracle11/oracle/flash_recovery_area/testdb/control02.ctl;

SQL> alter database mount;

SQL> alter database open;

실습 2. Control File끼리 버전이 다른 경우 대처하기

==> Control file 끼리의 정보가 서로 다른 경우에 생기는 에러. Control file 끼리 정보를 맞춰주면 됨

※ 실습환경

-- 1. pfile control file 주석 후 pfile로 DB OPEN (spfile 있을 시 spfile 삭제)

-- 2. shutdown immediate

-- 3. startup

-- 4. 다시 pfile control file 주석 해제 후 DB OPEN 시 에러 확인

※ 해결방법

--버전 정보가 더 큰 파일을 작은 파일로 cp

cp /oracle11/oracle/oradata/testdb/control01.ctl /oracle11/oracle/flash_recovery_area/testdb/control02.ctl;

SQL> alter database mount;

SQL> alter database open;

실습 3. Old Control File 또는 Control File이 삭제되었을 경우 대처하기

1) noresetlogs : 리두 리셋 안함 (resetlogs는 redo에도 에러있을시 사용)

-- control file 재생성 스크립트 만들기

alter database backup controlfile to trace as '/oracle11/backup/20151229/control.sql';

* 공백 / 주석을 제거해야 합니다.

-- 실습환경 만들기

-- 과거 컨트롤 파일로 바꿔줌

cp /oracle11/backup/20151228/control01.ctl /oracle11/oracle/oradata/testdb/control01.ctl

cp /oracle11/backup/20151228/control01.ctl /oracle11/oracle/flash_recovery_area/testdb/control02.ctl;

SQL> startup

==> 반드시 control file 재생성 해주어야 함

SQL> shutdown immediate

* 해당 폴더에서 sqlplus 로 접속 후 @control.sql

-- control file 재생성 스크립트 만들기

alter database backup controlfile to trace as '/oracle11/backup/20151230/control.sql';

--장애상황 발생

--old 백업 본으로 현재 control file 덮어쓰기

alter database backup controlfile to '/oracle11/backup/20151230/control_bak.ctl'

drop table scott.recover_test1;

create table scott.recover_test1(a number);

insert into scott.recover_test1 values(1);

insert into scott.recover_test1 values(2);

insert into scott.recover_test1 values(3);

commit;

SQL> shutdown immediate

!cp /oracle11/backup/20151230/control_bak.ctl /oracle11/oracle/oradata/testdb/control01.ctl

!cp /oracle11/backup/20151230/control_bak.ctl /oracle11/oracle/flash_recovery_area/testdb/control02.ctl;

-- startup 시 에러 확인

* 컨트롤 파일은 정상인데 시점정보가 맞지 않음(01589)

SQL> alter database noresetlogs;

* controlfile을 이용해서만 noresetlogs 가능(01610)

SQL> alter database resetlogs;

* control 파일은 과거 버전인데 system01.dbf는 최신버전 즉, 다 restore 해야 함(01152)

==> control file 재생성 판단

--shutdown 상태에서 수행

SQL> shutdown immediate

SQL> @/oracle11/backup/20151230/control.sql

SQL> alter database open;

control file 재생성 하기

--장애 상황 만들기

--01. 현재 online redo 확인

select a.group#, a.member, b.bytes/1024/1024 mb, b.sequence# "seq#", b.status, b.archived arc

from v$logfile a , v$log b

where a.group# = b.group#

order by 1,2;

-- inactive 이면서 arc가 YES인 리두로그 파일 확인 및 삭제

!rm /oracle11/oracle/oradata/testdb/redo03.log;

-- control file 확인

select * from v$controlfile;

-- 모든 control file 삭제

!rm /oracle11/oracle/oradata/testdb/control01.ctl;

!rm /oracle11/oracle/flash_recovery_area/testdb/control02.ctl;

-- shutdown immediate로 내릴 경우 에러 확인

SQL > shutdown immediate

SQL > shutdown abort

!cp /oracle11/backup/20151230/control01.ctl /oracle11/oracle/oradata/testdb/control01.ctl

!cp /oracle11/backup/20151230/control01.ctl /oracle11/oracle/flash_recovery_area/testdb/control02.ctl

SQL > startup

SQL > @control.sql (전에 만들어둔 noresetlogs 옵션)

** redo 파일이 없어져서 error

control.sql 파일의 noresetlogs를 resetlogs로 수정 한 후

SQL > @control.sql

SQL > alter database open;

##ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL > alter database open resetlogs;

Database altered.

1. 마운트 상태에서 alter database backup control file to trace as ‘....’; 명령어로 재생성 할 수 있는 스크립트 생성

2. 스크립트를 열어서 no resetlogs case와 resetlogs case를 구분해서 재생성 스크립트를 편집

3. DB shutdown 상태로 만든 후 위 2번에서 만든 스크립트를 실행시켜 DB를 마운트 까지 시킨 후 시작

Hot Backup

열린 백업 (Hot backup/Open backup/Begin backup/Online backup)

- 데이터베이스 가동 중 전체 파일 백업

- Tablespace 단위로 백업 수행

- 11g부터 Database 단위도 가능 (Redo 발생량 문제로 비 권고)

- 백업 전 대상 Tablespace를 백업 모드로 적용하고, 백업 후 백업 모드를 해제

- Archive Log Mode 필수 (Redo log 파일로 변경하기 위해)

- Redo 발생량 급증, Archive 발생량 급증

- Data file과 Control file만 백업 가능, Online Redo Log File은 백업 불가능 --> resetlogs 필요

○ 열린 백업 원리

SQL > alter tablespace users begin backup; (DBWR 발생)

Checkpoint 발생해서 변경사항 datafile로 저장

마지막 checkpoint scn 정보를 data file 과 control file 에 저장

데이터파일 헤더의 Hot-backup-in-progress 플래그에 백업 시작 정보 기록

이후의 변경된 데이터는 리두로그에 블록단위(데이터 손실을 막기위해)로 기록

--> Begin backup을 수행해도 테이블스페이스 변경은 가능

--> 백업 수행 시 archive 양이 급증 하므로 모니터를 더 잘해야함

백업 완료 후에는 반드시 end backup을 수행해야 함(hot backup을 수행 중에는 shutdown immediate나 normal로 종료불가)

SQL > alter tablespace users end backup;

ALTER TABLESPACE BEGIN BACKUP 내부 처리 과정

1. ALTER TABLESPACE BEGIN BACKUP 명령이 수행되는 순간 체크포인트 신호가 발생하여 db 버퍼 캐시의

Dirty Block 들을 데이터 파일로 내려쓰는 작업이 시작됩니다.

2. 체크포인트 신호가 발생되면서 SCN은 동기화 작업이 일어나며 데이터 파일 헤더에 SCN이 기록됩니다.

3. ALTER TABLESPACE END BACKUP 명령이 완료 될 때까지 데이터 파일에 기록된 SCN은 변경되지 않는다.

하지만, 데이터 파일의 블록은 읽고 변경이 된다.

4. 최종 SCN 이후의 변경된 내용은 리두 로그 파일에 저장되어 있다가 ALTER TABLESPACE END BACKUP

명령을 수행하면 리두로그에 있는 데이터 파일에 기록

5. 복구는 ALTER TABLESPACE BEGIN BACKUP 명령을 수행했을 때 고정된 SCN을 기준으로 HOT백업 한

데이터 파일을 복권해서 복구할 수 있습니다. 그러면 리두 로그 파일에 기록된 내용을 ROLL FORWARD

되면서 HOT백업 한 시간까지의 모든 변경된 내용들이 적용됩니다.

01. BACK UP 대상 확인

SELECT TABLESPACE_NAME, FILE_NAME

FROM DBA_DATA_FILES;

02. BEGIN BACKUP MODE : 백업모드 적용 (CHECKPOINT 발생)

SELECT TABLESPACE_NAME, 'ALTER TABLESPACE '||TABLESPACE_NAME||' BEGIN BACKUP;'

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME NOT LIKE 'TEMP%';

03. OS BACKUP 수행

SELECT TABLESPACE_NAME, FILE_NAME, 'CP '||FILE_NAME||' /oracle11/backup/'||to_char(sysdate,'yyyymmdd')

FROM DBA_DATA_FILES;

04. END BACKUP MODE : 백업 모드 해제 (CHECKPOINT 발생)

SELECT TABLESPACE_NAME, 'ALTER TABLESPACE ' ||TABLESPACE_NAME||' END BACKUP;'

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME NOT LIKE 'TEMP%';

확인

select a. file#, a.name, b.status, to_char(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time

from v$datafile a, v$backup b

where a.file#=b.file#;

05. control file 백업

alter database backup controlfile to '/oracle11/backup/20151229/control01.ctl';

Cold Backup

□ 백업 대상

- 필수 : Data File, Redo Log File(archive가 있다면 안 가져갈 수 도 있음), Control File

- 선택 : Parameter File, Password File, sqlnet.ora, listener.ora, tnsnames.ora 등

1) Data File

- 사용 중인 Data File은 자주 백업을 받아야 함

SQL > select name, status from v$datafile;

NAME STATUS

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

/oracle11/oracle/oradata/testdb/system01.dbf SYSTEM

/oracle11/oracle/oradata/testdb/sysaux01.dbf ONLINE

/oracle11/oracle/oradata/testdb/undotbs01.dbf ONLINE

NAME STATUS

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

/oracle11/oracle/oradata/testdb/users01.dbf ONLINE

/oracle11/oracle/oradata/testdb/example01.dbf ONLINE

2) Control File

- 현재 Control File을 백업 받아야 함

SQL > select name from v$controlfile;

NAME

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

/oracle11/oracle/oradata/testdb/control01.ctl

/oracle11/oracle/flash_recovery_area/testdb/control02.ctl

3) Redo Log File

SQL >select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC"

from v$logfile a, v$log b

where a.group#=b.group#

order by 1,2;

4) Parameter File/Password File

- Parameter File

  · Oracle 서버의 각종 설정 정보 저장

  · 기동에 반드시 필요, 최적값 백업 필요

  · $ORACLE_HOME/dbs

- Password File

  · sysdba 권한 암호 저장

  · DB종료 상태에서 딕셔너리 없이 로그온 하기 위해 존재

  · orapwd 로 재생성 가능

5) sqlnet.ora & listener.ora & tnsnames.ora

- sqlnet.ora

  · $ORACLE_HOME/network/admin

  · local 접속의 권한 관리

  · 외부 접속자 list 관리

- listener.ora

  · $ORACLE_HOME/network/admin

  · 외부에서 DB서버로 접속을 받아주는 리스너의 설정 파일

- tnsnames.ora

  · $ORACLE_HOME/network/admin

  · 외부 DB서버로 접속할 때의 TNS 정보 설정 파일

□ 백업의 종류

1) 닫힌 백업 (Cold backup/Closed backup)

- 데이터베이스 종료 후 전체 파일 백업

- 모든 파일의 Checkpoint SCN은 같아야 함

· 일반적으로 각각 다른 시점에 받은 백업 파일은 복구에 같이 사용 할 수 없음

- 정상 shutdown 상태에서 백업 가능

· shutdown abort, instance crash 등 상태는 일관성이 흐트러진 상태로 복구에 사용될 수 없음

· OFFLINE 되어 있는 Data File 도 복구에 사용 불가 (Checkpoint scn정보가 다름)

- Archive Log Mode 여부 무관

- Data File, Online Redo Log File, Control File 백업 가능

- 주로 복구가 아닌 복제를 할 때 사용

01. datafile 확인

select name, status from v$datafile;

02. controlfile 확인

select name from v$controlfile;

03. redo log file 확인

select a.group#, a.member as "NAME", b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC"

from v$logfile a, v$log b

where a.group#=b.group#

order by 1,2;

04. copy 문 script 작성

SELECT 'cp '||NAME|| ' /oracle11/backup/'||to_char(sysdate,'YYYYMMDD')|| substr(name,instr(name,'/',-1))

FROM v$datafile

union all

SELECT 'cp '||NAME|| ' /oracle11/backup/'||to_char(sysdate,'YYYYMMDD') || substr(name,instr(name,'/',-1))

FROM v$controlfile

union all

SELECT 'cp '||NAME|| ' /oracle11/backup/'||to_char(sysdate,'YYYYMMDD') || substr(name,instr(name,'/',-1))

FROM (select member as "NAME"

from v$logfile);

05. DB 정상 종료

SQL > shutdown immediate

06. 폴더 만든 후 파일 백업 (copy문 실행)

07. DB OPEN

SQL > startiup

No Archive log / Archive log

□ No Archive log

Recovery 원리

Oracle 시작 단계

□ NOMOUNT

· 서버 프로세스가 PFILE(SPFILE)에 지정된 대로 INSTANCE 생성

□ MOUNT

· CKPT 프로세스가 Control File을 읽고 일시 lock 설정

· Control File Header 정보 검증 (Control File 이상 여부 확인),

    -> MOUNTID 계산 값 Control File 저장

· “Successful mount of redo thread”

· “Database mounted...”

□ OPEN

· Data File Header 정보와 Control File 의 Checkpoint SCN 정보 비교

Controlfile dump

- Control File은 Binary File의 한 종류이므로 OS 명령어로 내용을 직접 확인할 수 없음

- dump 작업을 통해 Control File 내용 확인 가능

 파티션 테이블(partition table) - 인덱스(index)

□ 인덱스 유형 선택

<Partition Index Type>

  - 한 파티션에서 조회하는 액세스 패스는 Local 인덱스를 사용하도록 함

  - 운용측면에서 Global Index보다는 Local Index 사용이 권장

  - Table Partition Key를 Index로 설정할 경우  Local Prefixed Index를 사용

  - PK 컬럼은 Table Partition Key를 첫번째 컬럼으로 하는 Local Prefixed Index를 사용

  - Non-partition 컬럼에 대한 빈번한 배치 작업 수행시 Local Nonprefixed Index를 사용

  - Non-partition 컬럼에 대한 Unique Index 설정시 Global Index를 사용 (OLTP)

테이블 전체 스캔의 경우 등 여러 파티션을 조회하는 경우는 비분할(Non-partitioned) 인덱스를 생서아여

이 인덱스를 통한 액세스가 가능하도록 한다.

예제 table create

CREATE TABLE ORDER_ITEMS_PT_RG_1
(
    ORDER_ID       VARCHAR2(17),
    PRODUCT_ID     VARCHAR2(7),
    ORDER_DATE     DATE,
    UNIT_PRICE     NUMBER,
    QUANTITY       NUMBER,
    NEW_ORDER_DATE VARCHAR2(10) GENERATED ALWAYS AS(TO_CHAR(ORDER_DATE,'YYYYMMDD'))
)
tablespace users
partition by range(new_order_date)(
partition p2007 values less than('20080101'),
partition p2008 values less than('20090101'),
partition p2009 values less than('20100101'),
partition p2010 values less than('20110101'),
partition p2011 values less than('20120101'),
partition p2012 values less than('20130101'),
partition pmax values less than(maxvalue));

--date insert
ALTER TABLE ORDER_ITEMS_PT_RG_1 NOLOGGING;
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL (A 4) */
INTO ORDER_ITEMS_PT_RG_2 A
(ORDER_ID, PRODUCT_ID,ORDER_DATE,UNIT_PRICE,QUANTITY)
SELECT /*+ PARALLEL (B 4) */*
FROM TUNER.ORDER_ITEMS B;

commit;

ALTER TABLE ORDER_ITEMS_PT_RG_1 LOGGING;

select * from dba_tab_partitions
where table_name like 'ORDER_ITEMS_PT_RG_1';

* local과 global index를 구분하기 위해 ORDER_ITEMS_PT_RG_2 테이블도 똑같이 생성한다.

partition index 생성

-- partition global index

create index ORDER_ITEMS_PT_RG_1_date on order_items_pt_rg_1(new_order_date)
parallel 8;

alter index order_items_pt_rg_1_date noparallel;

-- partition local index
create index ORDER_ITEMS_PT_RG_2_date on order_items_pt_rg_2(new_order_date)
parallel 8
local;

alter index order_items_pt_rg_2_date noparallel;

-- index 조회해서 생성 확인
select * from dba_indexes
where table_name like 'ORDER_ITEMS_PT_RG%';

select * from dba_ind_partitions
where index_name like 'ORDER_ITEMS_PT_RG%';

global local index의 성능차이

select sum(QUANTITY)
  from ORDER_ITEMS_PT_RG_1 a
 where new_order_date between '20090601' and '20090610';

select sum(QUANTITY)
  from ORDER_ITEMS_PT_RG_2 a
 where new_order_date between '20090601' and '20090610';
 

global local 관리 차이

-- partition drop
alter table ORDER_ITEMS_PT_RG_1 drop partition p2009;  -- global
alter table ORDER_ITEMS_PT_RG_2 drop partition p2009;  -- local

-- index상태 조회
select table_name, index_name, status
  from dba_indexes
 where index_name like 'ORDER_ITEMS_PT_RG%';
 

select index_name ,status
  from dba_ind_partitions
 where index_name like 'ORDER_ITEMS_PT_RG%';

-- index rebuild 스크립트 작성
select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild;' as script
  from dba_indexes
 where status = 'UNUSABLE'
union
select 'ater index '||index_owner||'.'||index_name|| 'rebuild partition;' as script
  from dba_ind_partitions
 where status='UNUSABLE';
  
 -- index rebuild
alter index ORDER_ITEMS_PT_RG_1_DATE rebuild;

alter index ORDER_ITEMS_PT_RG_2_DATE rebuild;
-- ora-14086 분할영역된 인덱스는 전체를 다시 만들 수 없습니다

* local은 rebuild를 따로 해줄 필요가 없다.