고민의 흔적

오라클 서버에서 .sql 파일 실행법

@파일이름.sql

전체 복구(기본)

ls -lh EXPDP_STR_FULL_20221207*

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
DUMPFILE=EXPDP_STR_FULL_20221207.DMP \
LOGFILE=IMPDP_STR_FULL_20221207.log

전체 복구(기본+TDE)

ls -lh EXPDP_STR_FULL_TDE_20221207*

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_TDE_20221207.DMP \
LOGFILE=IMPDP_STR_FULL_TDE_20221207.log

전체 복구(압축+TDE)

ls -lh EXPDP_STR_FULL_COMP_20221207*

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_COMP_20221207.DMP \
LOGFILE=IMPDP_STR_FULL_COMP_20221207.log

전체 복구(백업 파일 100M 분할)

ls -lh EXPDP_STR_FULL_DIV_20221207

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_DIV_20221207_%U.DMP \
LOGFILE=IMPDP_STR_FULL_DIV_20221207.log

전체 복구(Parallel+압축+TDE)

ls -lh EXPDP_STR_FULL_PCOMP_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
PARALLEL=4 FULL=YES ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_PCOMP_20221207_%U.DMP \
LOGFILE=IMPDP_STR_STR_FULL_PCOMP_20221207.log

Datapump 스키마(유저) 복구 STR계정 복구(Parallel+압축)

ls -lh EXPDP_STR_STR_P_20221207*

impdp system/oracle DIRECTORY=STR_DUMP SCHEMAS=STR \
PARALLEL=4 \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_STR_P_20221207_%U.dmp \
LOGFILE=IMPDP_STR_STR_P_20221207.log

Datapump 테이블 복구(계정명: STR 테이블명: CUST)

ls -lh EXPDP_STR_CUST_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
DUMPFILE= EXPDP_STR_CUST_20221207.DMP  \
LOGFILE=IMPDP_STR_CUST_20221207.log \
TABLES=STR.CUST

Datapump 테이블 복구

-- 테이블스페이스 위치 변경: TS_STR_D, TS_STR_I  -->  SYSTEM

ls -lh EXPDP_STR_CUST_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
DUMPFILE= EXPDP_STR_CUST_20221207.DMP  \
LOGFILE=IMPDP_STR_CUST_20221207.log \
TABLES=STR.CUST \
REMAP_TABLESPACE=TS_STR_D:SYSTEM \
REMAP_TABLESPACE=TS_STR_I:SYSTEM

복구 진행 중에 계속 멈춰 있다면 테이블 스페이스 공간을 확인하자

SELECT 
    OWNER, 
    SEGMENT_TYPE, 
    SEGMENT_NAME,
    TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE  SEGMENT_NAME IN ('CUST','CUST_IDX1');

ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/STR/system01.dbf' RESIZE 2048M;

Datapump 테이블 복구(테이블명 변경: CUST --> CUST_BACKUP_20221003)

ls -lh EXPDP_STR_CUST_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
DUMPFILE= EXPDP_STR_CUST_20221207.DMP  \
LOGFILE=IMPDP_STR_CUST_20221207.log \
TABLES=STR.CUST \
REMAP_TABLE=CUST:CUST_BACKUP_20221207

참고

https://www.youtube.com/watch?v=fEVnBQJNuvQ 

EXPDP

Oracle Data Pump Export Utility로 백업을 한 시점에서 복구를 한다던가 다른 데이터 베이스에 이관을 쉽게 할 수 있어서 매우 유용하다.

디렉터리 생성과 삭제

cd /home/oracle
mkdir /home/oracle/oracle_dump

-- 디렉토리 생성
CREATE OR REPLACE DIRECTORY STR_DUMP AS '/home/oracle/oracle_dump';
GRANT READ, WRITE ON DIRECTORY STR_DUMP TO PUBLIC;

--디렉토리 삭제
REVOKE READ,WRITE ON DIRECTORY STR_DUMP FROM PUBLIC;
DROP DIRECTORY STR_DUMP;

전체 백업

-- FULL=YES
expdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
DUMPFILE=EXPDP_STR_FULL_20221206.DMP LOGFILE=EXPDP_STR_FULL_20221206.log

암호화가 되어 있는 테이블 스페이스나 테이블이 있을 경우 오류가 난다.

DATAPUMP 백업 상태 확인

-- SQL Developer 확인(SYS계정)
SELECT * FROM DBA_DATAPUMP_JOBS;

암호화되어있는지 확인(TDE Check)

-- Tablespace Level
SELECT 
    TABLESPACE_NAME,
    ENCRYPTED,
    STATUS 
FROM DBA_TABLESPACES;

-- Table Level
SELECT * 
FROM DBA_ENCRYPTED_COLUMNS;

암호화가 되어 있는지 테이블스페이스 단위, 테이블 단위로 확인을 한다.

전체 백업(기본+TDE)

-- FULL=YES ENCRYPTION_PASSWORD=oracle123
expdp system/oracle DIRECTORY=STR_DUMP FULL=YES ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_TDE_20221206.DMP LOGFILE=EXPDP_STR_FULL_TDE_20221206.log

전체 백업(압축+TDE)

-- FULL=YES COMPRESSION=ALL ENCRYPTION_PASSWORD=oracle123
expdp system/oracle DIRECTORY=STR_DUMP FULL=YES COMPRESSION=ALL ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_COMP_20221206.DMP LOGFILE=EXPDP_STR_FULL_COMP_20221206.log

전체 백업(백업 파일 100M 분할)

-- FULL=YES FILESIZE=100M ENCRYPTION_PASSWORD=oracle123
expdp system/oracle DIRECTORY=STR_DUMP FULL=YES FILESIZE=100M ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_DIV_20221206_%U.DMP LOGFILE=EXPDP_STR_FULL_DIV_20221206.log

DUMPFILE에 파일 명 쓰고 %U를 붙여줘야 1,2,3… 숫자가 포함된다.

전체 백업(Parallel+압축+TDE)

-- FULL=YES COMPRESSION=ALL PARALLEL=4 ENCRYPTION_PASSWORD=oracle123
expdp system/oracle DIRECTORY=STR_DUMP FULL=YES COMPRESSION=ALL PARALLEL=4 ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_PCOMP_20221206_%U.DMP LOGFILE=EXPDP_STR_FULL_PCOMP_20221206.log &

Parallel의 장점은 병렬로 처리하기 때문에 매우 빠르고 단점은 CPU 사용량이 높아서 전체 CPU 코어의 반 정도 사용하는 것을 추천한다.

부분 백업 STR계정 백업(Parallel+압축)

-- SCHEMAS=STR COMPRESSION=ALL PARALLEL=4
expdp system/oracle DIRECTORY=STR_DUMP SCHEMAS=STR COMPRESSION=ALL PARALLEL=4 \
DUMPFILE=EXPDP_STR_STR_P_20221206_%U.dmp LOGFILE=EXPDP_STR_STR_P_20221206.log

테이블 단위 백업

-- 계정명: STR, 테이블명: CUST
expdp system/oracle DIRECTORY=STR_DUMP DUMPFILE= EXPDP_STR_CUST_20221206.DMP LOGFILE=EXPDP_STR_CUST_20221206.log \
TABLES=STR.CUST

참고

https://www.youtube.com/watch?v=Ie0jjerBqvk&t=1s 

Optimizer_mode

• RULE
  • RBO(Rule-based optimizer) 규칙 기반 옵티마이저
• CHOOSE
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
• FIRST_ROWS
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • 옵티마이저가 인덱스를 가장 많이 사용할 수 있도록 하는 모드 중 하나이다.
• FIRST_ROWS_N
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • N은 1, 10, 100, 1000개 행을 의미하고 최초의 N건의 응답 속도를 최적화할 때 사용하는 모드 중 하나이다. B2C 고객의 수가 엄청 많다면 FIRST_ROWS_1을 추천한다.
  • FIRST_ROWS_1을 사용한다면 optimizer_index_caching과 optimizer_index_cost_adj를 변경해주어야 한다.
  • optimizer_index_caching는 인덱스를 사용해 접근한 블록을 오라클 메모리, 버퍼 캐시 디비 캐시 사이즈 파라미터에서 찾을 수 있는 비율을 말한다. 보통 90 ~ 95
  • optimizer_index_cost_adj는 테이블을 Full Scan을 많이 할지 아니면 인덱스를 더 많이 사용할지를 결정하는 파라미터이다. 보통 30 ~ 35
• ALL_ROWS(DEFUALT)
  • CBO(Cost-based optimizer) 비용 기반 옵티마이저
  • 데이터의 모든 ROW를 빠르게 가져오고 테이블 Full Scan을 선호하는 경향이 있다.
  • 소형 프로그램이거나 DBA가 없을 경우 권장한다.

Adaptive Query Optimization

Oracle 자동 SQL 최적화 관리이고 Oracle 12C부터 적용 되었다. 데이터 베이스가 내부적으로 분석해서 SQL 실행 계획을 최적화하는 방식이다.

• 오라클 옵티마이저 통계 및 통계 어드바이저
• SQL Plan 관리
• 정확도가 비교적 높은 SQL 처리

Adaptive Query Optimization를 false로 처리할려면

• 완벽에 가까운 모델링(테이블 설계)
• 아름다운 인덱스 설계
• 확실한 테이블, 인덱스 통계정보 관리
• 환상적인 SQL 튜닝

Adaptive Query Optimization 기능을 제어하는 대표적인 파라미터

• optimizer_adaptive_plans (Defualt: true)
  • 옵티마이저가 adaptive_plan을 고려 대상으로 판단할지 여부를 결정한다.
• optimizer_adaptive_statistics (Defualt: false)
  • 통계 피드백을 비활성화해서 SQL Plan 변경을 방지한다.
• optimizer_adaptive_reporting_only (Defualt: false)
  • 어뎁티브 기능에 필요한 정보만 수집하고 SQL Plan을 변경할지 여부를 결정한다.
• _optimizer_adaptive_cursor_sharing (Defualt: true)
  • 변숫값에 따라서 SQL Plan을 변경하는 파라미터이다.
• _optimizer_aggr_groupby_elim (Defualt: true)
  • 특정 기준 조건으로 group by를 할 때 안에 group by 있고 밖에 group by가 또 있다고 하면 두 개의 group by가 아닌 하나의 group by만 사용하는 경우가 있다. 이럴 경우 문제가 있을 수도 있어 false로 놓고 많이 사용한다.
• _add_col_optim_enabled (Defualt: true)
  • 오라클 내부에서 테이블 칼럼을 추가하면 빠른 속도로 처리가 된다. 하지만 Not null 조건에 defualt라고 하면 약간 문제가 있을 수 있어서 false로 놓고 많이 사용한다.

참고

https://www.youtube.com/watch?v=Y7scjyhY_1E&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=4 

Parameter

파라미터는 오라클을 설정할 수 있는 환경 변수 값들이 모여있는 곳들을 말한다. 서버 파라미터 파일인 spfileDB이름.ora 안에 환경 변수 값들이 있다.

AMM

Automatic Memory Management로 오라클 데이터 베이스 메모리의 대표적인 SGA, PGA를 자동으로 관리하는 방식이다.

ASMM

Automatic Shared Memory Management로 오라클의 메모리 영역인 SGA를 자동으로 관리하는 방식이다. DB의 사용자가 적고 트렌젝션이 별로 없거나 DBA가 없고 개발자가 관리한다면 ASMM을 사용하는 것을 권장하긴 한다.

파라미터 변경

ALTER SYSTEM SET 파라미터명=값 SCOPE=MEMORY|SPFILE|BOTH;

MEMORY는 오라클의 메모리에 즉시 적용, SPFILE은 SPFILE에만 적용, BOTH는 둘다 적용

파라미터 값 복구

ALTER SYSTEM RESET 파라미터명 SCOPE=MEMORY|SPFILE|BOTH;

가능한 SPFILE로 변경하는 것이 좋다.

pfile, spfile 백업

cp spfileSTR.ora spfileSTR.ora_20220711
mv initSTR.ora initSTR.ora_20220706

sqlplus / as sysdba
create pfile from spfile;
exit

mv initSTR.ora initSTR.ora_20220711

파라미터 값 확인

SELECT * FROM V$PARAMETER;

파라미터 값 변경하기 전에 모든 파라미터 값 조회한 것을 엑셀 파일로 백업한다.

오라클 메모리 자동 관리 확인

show parameter statistics_level

위의 명령어를 치면 TYPICAL로 나오는 것을 확인할 수 있다. TYPICAL 값이 DEFUALT 값이다.

오라클 대표 파라미터

• sga_max_size : SGA 메모리의 최대 크기를 변경할 때 사용
• sga_target : ASMM 관리할 때 사용하는 방식 이 파라미터를 0으로 저장하면 메모리가 관리 방식이 수동으로 바뀐다.
  • shared_pool_size : 라이브러리 캐시, 데이터 딕셔너리 캐시가 저장되는 장소
  • shared_pool_reserved_size : shared_pool의 공간을 예약한다.
  • db_cache_size : 데이터 파일에서 불러온 블럭을 저장한다.
  • java_pool_size
  • large_pool_size : 오라클의 페러럴 처리 작업이나 RMAN 백업, 복구할 때 사용
  • streams_pool_size : 데이터 백업과 관련있다. 버퍼링 된 큐 메시지 저장하거나 캡처 프로세스 및 적용 프로세스를 위한 메모리 공간 중 하나이다.
• pga_aggregate_target : DB 인스턴스에 연결된 모든 서버 프로세스가 사용할 수 있는 PGA 메모리 영역 중 하나이다.

데이터 베이스에 동시에 접속할 수 있는 숫자를 결정하는 대표적인 파라미터

• processes
• sessions
• transactions
• open_cursors

옵티마이저와 관련된 파라미터

• optimizer_dynamic_sampling : 테이블 통계정보가 없는 상태에서 테이블의 특정 조건이 맞으면 자동으로 통계 정보가 갱신된다. 이 파라미터에 따라서 SQL Plan이 변경될 수 있다.
• "_optim_peek_user_binds" : SQL 사용할 때 바인드 변수를 많이 넣는데 이 파라미터에 따라서 SQL Plan이 변경될 수 있다.
• "_optimizer_use_feedback" : 새로운 SQL이 실행될 때 예측 값과 실측 값이 있다. 이 두 값의 차이가 발생하면 실행 계획이 변경될 수 있다.

앞에 _가 붙은 파라미터는 히든 파라미터라고 부른다.

업계에서 권장하는 메모리 값은 물리적인 서버 메모리 대략 10~20% 수준에서 오라클 메모리 파라미터 초기 설정을 하고 테스트를 통해서 계속 변경한다. 파라미터 적용할 때 spfile에 적용했다가 DB를 내렸다 올리는 게 좋다.

히든 파라미터 조회

-- Hidden Parameter 조회
SELECT 
    a.ksppinm "Parameter", 
    b.KSPPSTDF "Default Value",
    b.ksppstvl "Session Value", 
    c.ksppstvl "Instance Value",
    decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
    decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/'
AND    a.ksppinm IN ('_optim_peek_user_binds','_optimizer_use_feedback');

SGA, PGA Hit Ratio 상태 정보 조회

-- SGA, PGA Hit Ratio 상태 정보
SELECT 
    'SHARED_POOL_SIZE(LIBRARY CACHE)' AS "MEMORY_PARAMETER", 
    ROUND((1-SUM (reloads)/SUM(pins))*100,2) AS "HIT_RATIO"
From V$LIBRARYCACHE
UNION ALL
SELECT 
    'SHARED_POOL_SIZE(DATA DICTIONARY)', 
    ROUND((1-SUM(getmisses)/SUM(gets))*100,2)
FROM V$ROWCACHE
UNION ALL
SELECT 
    'DB_CACHE_SIZE',
    ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0)) / 
    (SUM(DECODE(name, 'db block gets', value,0))+(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) 
FROM V$SYSSTAT
UNION ALL
SELECT 
    'PGA_AGGREGATE_TARGET',
    ROUND(VALUE,2) 
FROM V$PGASTAT
WHERE NAME = 'cache hit percentage';

Hit Ratio값이 높으면 거의 사용 안한다는 뜻이다. 값이 낮아지면 사용량이 높아지는 거여서 확인하면서 메모리 값을 변경해야 한다.

참고

https://www.youtube.com/watch?v=aBBgIYxKSkE&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=3 

DBA_DATA_FILES에서 DATAFILE 파일 조회

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 AS "SIZE(MB)",
    AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES';

DBA_TEMP_FILES에서 TEMPFILE 파일 조회

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 AS "SIZE(MB)",
    AUTOEXTENSIBLE
FROM DBA_TEMP_FILES
WHERE AUTOEXTENSIBLE = 'YES';

데이터 파일과 템프 파일 테이블 합쳐서 AUTOEXTENSIBLE이 YES인 것만 출력하기

SELECT 
    'ALTER DATABASE DATAFILE '''||FILE_NAME||''' AUTOEXTEND OFF;'
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES'
UNION ALL
SELECT 
    'ALTER DATABASE TEMPFILE '''||FILE_NAME||''' AUTOEXTEND OFF;'
FROM DBA_TEMP_FILES
WHERE AUTOEXTENSIBLE = 'YES';

데이터 파일 템프 파일 AUTOEXTEND 설정 끄기

ALTER DATABASE DATAFILE '경로' AUTOEXTEND OFF;

파라미터 변경

cd $ORACLE_HOME/dbs

-- 파라미터 백업
cp spfileSTR.ora spfileSTR.ora_20220706

ORACLE INSTANCE 가 START UP 시에 SPFILE과 PFILE을 읽어 들어 파라미터 값을 적용시킨다. SPFILE이 있으면 SPFILE을 읽게 되며, SPFILE가 존재하지 않으면 PFILE을 읽게 된다. 기본적으로 pfile은 initdXXXX.ora로 정의되어 있고 spfile은 spfileXXXX.ora로 지정되어 있다. 여기서 XXXX는 SID값이다.

-- pfile 생성 
create pfile from spfile;

spfile을 기본으로 pfile을 생성했다. 여기서 주의할 점은 pfile은 아스키 값이고 spfile은 바이너리 파일이다. 그래서 pfile은 직접 편집기로 수정해도 되지만, spfile은 명령어로 값을 변경하던지 pfile을 변경한 뒤 spfile로 만들어야 한다.

show parameter db_files
alter system set db_files=2000 scope=spfile;

디비를 내렸다 다시 올려야 db_files 파라미터 변경된다. 운영 시스템에서 절때 하면 안 된다!!!

SELECT 
    NAME,
    VALUE, 
    ISSYS_MODIFIABLE 
FROM V$PARAMETER
WHERE NAME = 'db_files';

V$PARAMETER 테이블에서도 db_files 파라미터가 변경된 것을 확인할 수 있다.

테이블 스페이스 구성 작업 후 PROFILE 설정 꼭 해줘야 한다. 설정하지 않으면 비밀번호 틀렸다고 시스템 계정이 접속이 안될 수도 있다.

SELECT 
    USERNAME, 
    ACCOUNT_STATUS, 
    DEFAULT_TABLESPACE, 
    TEMPORARY_TABLESPACE,
    PROFILE
FROM DBA_USERS;

디비 처음 설치했을 때 생성되는 계정들과 PROFILE이 DEFAULT값으로 설정돼있는 것을 확인할 수 있다.

SELECT 
    * 
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT'
AND LIMIT <> 'UNLIMITED';

위의 테이블을 조회하면 DEFAULT 값이 상세하게 나온다.

ALTER PROFILE DEFAULT 
LIMIT 
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME    UNLIMITED
PASSWORD_LOCK_TIME    UNLIMITED
PASSWORD_GRACE_TIME   UNLIMITED;

UNLIMITED가 아닌 5개의 값이 나오는데 4개의 값만 UNLIMITED로 바꾼다. 엑셀 파일로 백업을 진행하고 설정을 변경한다. 항상 백업을 진행하고 설정을 바꿔야 한다.

• FAILED_LOGIN_ATTEMPTS 부정확한 password 허용 횟수 변경
• PASSWORD_LIFE_TIME 패스워드 유효기간
• PASSWORD_LOCK_TIME 암호 잠김 시간
• PASSWORD_GRACE_TIME 암호 변경 메시지 출력 일자

테이블 스페이스 생성

CREATE TABLESPACE TS_STR_D
DATAFILE '/oracle/app/oracle/oradata/STR/TS_STR_D_001.DBF' SIZE 1G
AUTOEXTEND OFF;

CREATE TABLESPACE TS_STR_I
DATAFILE '/oracle/app/oracle/oradata/STR/TS_STR_I_001.DBF' SIZE 1G
AUTOEXTEND OFF;

이름 규칙은 보통 테이블 스페이스명(TBS, TS) + DB 계정명 + 테이블(D), 인덱스(I)로 보통 사용한다.

DB계정 생성과 DB계정 권한

-- DB계정 생성
CREATE USER STR
IDENTIFIED BY "oracle"
DEFAULT TABLESPACE TS_STR_D;

-- DB계정 권한
GRANT CONNECT, RESOURCE TO STR;

DB계정 상태 & 권한 확인

-- DB계정 상태
SELECT 
    USERNAME, 
    ACCOUNT_STATUS, 
    DEFAULT_TABLESPACE, 
    PROFILE
FROM DBA_USERS
WHERE USERNAME = 'STR';

-- 권한 확인
SELECT 
   * 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'STR';

테이블 생성 예시

-- 샘플 테이블 생성
CREATE TABLE STR.CUST
(
  CUST_NAME   VARCHAR2(20),
  MOB_NUMBER  VARCHAR2(20)
)
TABLESPACE TS_STR_D;

테이블, DB 계정, 테이블 스페이스 삭제

-- 테이블 삭제
DROP TABLE STR.CUST;

-- 휴지통 비우기(완전 삭제)
PURGE DBA_RECYCLEBIN;

-- DB계정 삭제
DROP USER STR CASCADE;

-- 테이블스페이스 삭제
DROP TABLESPACE TS_STR_D INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TS_STR_I INCLUDING CONTENTS AND DATAFILES;

참고

https://www.youtube.com/watch?v=bIRjupXHTvw&t=1041s 

테이블 스페이스란

테이블 공간을 의미하며 데이터 공간이라고 말한다.

스토리지

데이터베이스의 데이터가 저장되는 공간

블록

블록은 오라클 데이터 베이스에 저장되는 논리적인 최소 단위이다. 블록 사이즈의 기본값은 8k 8192byte이다.

show parameter db_block_size

익스텐트

익스텐트는 여러 개의 블록들이 모여있는 집합이다. 하나의 익스텐트는 여러 개의 블록을 가질 수 있다.

세그먼트

세그먼트는 한개 이상의 익스텐트를 가지고 있다. 여러 개의 익스텐트가 모인 집합을 의미한다.

테이블 스페이스

여러 개의 세그먼트가 모이면 테이블 스페이스라고 한다. 테이블 스페이스는 논리적인 가장 큰 집합이다.

오라클 DB 기본 값 구성

오라클 DB를 기본 값으로 구성하면 5개의 테이블 스페이스와 데이터 파일이 만들어진다.

내 로컬 경로는 /opt/oracle/oradata/XE/이다.

SELECT
	TABLESPACE_NAME,
	FILE_NAME,
	BYTES/1024/1024 AS "SIZE(MB)"
FROM DBA_DATA_FILES
UNION ALL
SELECT
	TABLESPACE_NAME,
	FILE_NAME,
	BYTES/1024/1024
FROM DBA_TEMP_FILES
ORDER BY 1;

1. SYSTEM : /oracle/app/oracle/oradata/DB이름/system01.dbf
   • 오라클 내부의 중요한 데이터를 저장하는 데이터 딕셔너리 테이블이 저장되는 장소
2. SYSAUX : /oracle/app/oracle/oradata/DB이름/sysaux01.dbf
   • 대표적으로 AWR이 저장되는 장소이다. 그 외의 다른 것도 있다.
3. USERS : /oracle/app/oracle/oradata/DB이름/users01.dbf
   • 오라클의 기본 계정의 데이터가 저장되어 있는 공간이다.
4. UNDOTBS1 : /oracle/app/oracle/oradata/DB이름/undotbs01.dbf
   • 데이터 읽기 일관성이나 데이터 변경 작업을 했을 때 필요한 장소이다.
5. TEMP : /oracle/app/oracle/oradata/DB이름/temp01.dbf
   • 메모리에 맞지 않는 여러 정렬 작업을 동시에 할 수 있는 아주 중요한 임시 공간이다.

DBA_SEGMENTS를 이용해 내부 테이블 인덱스를 조회

SELECT
    OWNER,
    SEGMENT_NAME,
    SEGMENT_TYPE,
    SEGMENT_SUBTYPE,
    TABLESPACE_NAME,
    BYTES,
    BLOCKS,
    EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('WRH$_SQL_PLAN','WRH$_SQL_PLAN_PK','IDL_UB1$')
ORDER BY 1,2;

세그먼트 서브 타입 MSSM

Manual Segment Space Management으로 수동으로 세그먼트를 관리한다.

세그먼트 서브 타입 ASSM(Default)

Automatic Segment Space Management으로 자동으로 세그먼트를 관리한다.

옛날에는 MSSM을 사용하였지만 요즘엔 ASSM으로 많이 사용한다.

테이블 스페이스 데이터 파일 사이즈 변경

ALTER DATABASE DATAFILE '파일명' RESIZE 1G;

테이블 스페이스 데이터 파일 추가

ALTER DATABASE ADD DATAFILE  '파일명' SIZE 1G;

테이블 스페이스 생성

CREATE TABLESPACE 이름 DATAFILE '파일명' SIZE 1G AUTOEXTEND OFF;

테이블 스페이스를 MSSM으로 구성

CREATE TABLESPACE 이름 DATAFILE '파일명' SIZE 1G SEGMENT SPACE MANAGEMENT MANUAL;

테이블 스페이스를 ASSM으로 구성

CREATE TABLESPACE 이름 DATAFILE '파일명' SIZE 1G SEGMENT SPACE MANAGEMENT AUTO;

테이블 스페이스 상태 정보 확인

SELECT
    TABLESPACE_NAME,
    BLOCK_SIZE,
    SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLESPACES 

보통 DB 관리는 80% 사용을 할 때 데이터 파일을 늘려준다.

참고

https://www.youtube.com/watch?v=aTfu6zx2b28&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=1 

listener 포트 변경

포트를 변경하는 이유는 한대의 디비 서버에 여러 개의 디비를 구성할 때가 종종 있다. 한대의 디비 서버에 여러 개의 인스턴스를 구성하려면 리스너도 여러 개가 필요하기 때문에 여러 개의 포트도 필요하다.

spfile 백업파일 생성

cp spfileSTR.ora spfileSTR.ora_20221125

작업을 진행하기 전 우선 spfileSTR.ora 파일을 백업을 한다.

listener, tnsnames 파일 백업

cp listener.ora listener.ora_20220907
cp tnsnames.ora tnsnames.ora_20220907

포트 변경

vi listener.ora
vi tnsnames.ora

vi 편집기로 listener.ora과 tnsnames.ora를 열어서 포트번호를 1521에서 1522로 변경한다.

리스너 서비스 시작

lsnrctl start

파라미터 변경

show parameter local_listener

alter system set local_listener='(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1522))
)' scope=both;

alter system register;

local_listener 파라미터도 변경해야 한다.

Listener 포트 변경(1522 -> 1521)

위와 동일한데 파라미터 변경에서 조금 코드 차이가 있다.

show parameter local_listener

alter system set local_listener='LISTENER_STR' scope=both;

alter system register;

Warning 메시지 제거

vi listener.ora
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

싱글 디비 같은 경우는 오라클 노티피케이션 서비스가 특별히 필요 없기 때문에 WARNING: Subscription for node down event still pending 메시지 제거해도 된다. vi 편집기로 listener.ora 파일을 열고 파일 제일 마지막에 SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF를 추가한다.

lsnrctl reload

Multi Listener 설정(1521, 1522, 1523 포트 3개 사용)

vi listener.ora

LIST_STR1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521))
    )
  )

LIST_STR2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1522))
    )
  )

LIST_STR3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1523))
    )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LIST_STR1=OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LIST_STR2=OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LIST_STR3=OFF

vi tnsnames.ora

STR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STR)
    )
  )

리스너 서비스 시작

lsnrctl start LIST_STR1
lsnrctl start LIST_STR2
lsnrctl start LIST_STR3

파라미터 변경

show parameter local_listener

alter system set local_listener='(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1522))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1523))
)' scope=both;

alter system register;

포트 확인

ps -efl | grep tnslsnr
netstat -plnt | grep tns

Audit Dump 위치 확인

show parameter audit_file_dest

ls | wc -l

파일 개수 확인하는 명령어

Background & User Dump Log 위치 확인

show parameter background_dump_dest
show parameter user_dump_dest

Alert Log, Trace Log 위치 확인 쿼리문

select value from v$diag_info;

cd /opt/oracle/diag/rdbms/xe/XE/trace
cd /opt/oracle/diag/rdbms/xe/XE/alert

오라클 로그 파일 자동 삭제 Shell 스크립트

vi delete_oracle_log.sh

vi로 delete_oracle_log.sh 쉘 파일을 만든다.

#!/bin/bash

# Audit Log (보관주기 6개월)
find /opt/oracle/admin/XE/adump/*.aud -mtime +180 -exec rm {} \\;

# Background & User Dump Log (보관주기 3개월)
find /opt/oracle/homes/OraDBHome21c/XE/rdbms/log/*.trc -mtime +90 -exec rm {} \\;

# Alert Log (보관주기 3개월)
find /opt/oracle/diag/rdbms/xe/XE/alert/*.xml -mtime +90 -exec rm {} \\;

# Trace Log (보관주기 3개월)
find /opt/oracle/diag/rdbms/xe/XE/trace/*.trc -mtime +90 -exec rm {} \\;
find /opt/oracle/diag/rdbms/xe/XE/trace/*.trm -mtime +90 -exec rm {} \\;

chmod 755 delete_oracle_log.sh

chmod 명령어로 쉘을 실행할 수 있는 권한을 준다.

crontab -e
## Oracle Log Delete
01 0 1 * * /delete_oracle_log 만든 경로/delete_oracle_log.sh
crontab -l

크론텝으로 매월 1일 00:01에 실행하도록 설정한다.

크론텝 시간

분 시 일(*:매일, 5:5일) 달(*:매달, 5:5월) 요일(0은 일요일, 6은 토요일)
0 5 * * * : 매일 5시 0분에 실행.
5 * * * * : 매시 5분이 될 때마다 실행. 즉, 한 시간 간격으로 실행.
* * * * * : 1분에 한 번씩 실행.
0 5 1 * * : 매달 1일 새벽 5시에 실행.

*/5 * * * * : 5분마다 한 번씩
0 */5 * * * : 5시간마다 한 번씩

0 5,11 * * * : 새벽 5시와 밤 11시.
0 5,11 * * 0,3 : 매주 일요일과 수요일 새벽 5시와 밤 11시.

리스너 로그 파일 자동 정리(압축 및 삭제) Shell 스크립트

vi delete_listener_log.sh

vi로 delete_listener_log.sh 쉘 파일을 만든다.

ls -lh

용량을 나타내는 명령어이다.

#!/bin/bash
export ORACLE_SID=XE
export ORACLE_HOME=/opt/oracle/homes/OraDBHome21c/XE
export PATH=$ORACLE_HOME/bin:$PATH
export DATE=`date -d '1 month ago' +%Y%m` 

cd /opt/oracle/diag/rdbms/xe/XE/trace

cp listener.log listener.log_${DATE}
zip listener.log_${DATE}.zip listener.log_${DATE}

cat /dev/null > /opt/oracle/diag/rdbms/xe/XE/trace/listener.log
rm -rf /opt/oracle/diag/rdbms/xe/XE/trace/listener.log_${DATE}

chmod 755 delete_listener_log.sh

chmod 명령어로 쉘을 실행할 수 있는 권한을 준다.

crontab -e
## Listener Backup & Delete
01 0 1 * * /home/oracle/scripts/delete_listener_log.sh
crontab -l

크론텝으로 매월 1일 00:01에 실행한다.

adrci 삭제

수동으로 삭제하는 방법 중에 adrci로 삭제하는 방법이 있다.

-- adrci 설정 확인
show homes

--  adrci Home Path 변경
set HOMEPATH diag/rdbms/xe/XE

-- Alert Log 삭제(분) (90*24*60) 90일 
purge -age 129600 -type ALERT

-- Trace Log 삭제(분) (90*24*60) 90일 
purge -age 129600 -type TRACE 

-- CDUMP Log 삭제(분) (90*24*60) 90일 
purge -age 129600 -type CDUMP 

-- HM Log 삭제(분) (90*24*60) 90일 
purge -age 129600 -type HM

-- INCIDENT Log 삭제(분) (90*24*60) 90일 
purge -age 129600 -type INCIDENT

참고

https://www.youtube.com/watch?v=-7PfFVLYdwQ&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=11