Oracle 34일차

by

[실습 – controlfile 삭제]

1. 조회

select * from v$controlfile;

2. 삭제

3번 4번 controlfile 삭제

근데 그 전에 spfile 환경인지 pfile 환경인지 확인.

> show parameter spfile
-- control file 개수 수정
alter system set control_files='/oracle12/app/oracle/oradata/db1/control01.ctl','/oracle12/app/oracle/oradata/db1/control02.ctl' scope = spfile;

-- db shutdown
shutdown immediate

-- 서버에서 파일 지우기
$ data
$ rm control03.ctl
$ rm control04.ctl

-- db open
startup

[실습 – redo member 삭제]

3개 그룹으로 만들고 각 그룹에 하나의 멤버만 남기기

1. 조회

select a.group#,
        a.member,
        b.bytes/1024/1024 as "SIZE(MB)",
        b.archived,
        b.status,
        b.sequence# as seq#
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;

2. 삭제

-- current 아닌 애들의 멤버부터 삭제
alter database drop logfile member '/oracle12/app/oracle/oradata/db1/redo01_2.log';
alter database drop logfile member '/oracle12/app/oracle/oradata/db1/redo02_2.log';
alter database drop logfile member '/oracle12/app/oracle/oradata/db1/redo03_2.log';
-- log switch 후에 current였었던 멤버도 삭제
alter system switch logfile;

-- 잘 바뀌었나 확인
@log.sql

-- 서버에서도 삭제
$ rm redo01_2.log redo02_2.log redo03_2.log

[tablespace]

하나 이상의 datafile로 구성.
저장된 테이블과 인덱스를 분류하기 위해 만든 논리적인 단위.
하나의 테이블/인덱스는 같은 tablespace 에 속해야 한다.

ex)
create table system.table2(no number);
-- tablespace를 지정하지 않아도 자동으로 생기는 default tablespace가 있다.
-- 방금 만든 table2의 경우엔 'SYSTEM'이라는 tablespace에 저장돼있다.
-- orange에서 F4눌러서 tablespace 이름 확인 가능
-- 또는
select owner, table_name, tablespace_name
from dba_tables where table_name='TABLE2';
-- 이걸로도 확인 가능

** default tablespace
테이블/인덱스 생성 시 tablespace를 지정하지 않을 경우 자동으로 지정되는 tablespace를 의미.
유저별로 지정 가능.

ex) SYSTEM 계정에서 아래 문장 수행
create table scott.table3(no number); — SCOTT의 default tablespace에 저장이 된다.

유저별 default tablespace 조회

select username, default_tablespace, temporary_tablespace
from dba_users;
-- SCOTT의 경우 USERS 가 default tablespace
-- HR의 경우 SYSAUX 가 default tablespace
-- SYSTEM의 경우 SYSTEM 이 default tablespace

[tablespace 종류]

select *
from dba_tablespaces;

1) SYSTEM
system 계정의 default tablespace.
data dictionary 저장을 위해 만들어진 tablespace.

2) SYSAUX
성능 데이터를 저장하는 tablespace.
AWR 정보 저장.

3) undotbs1
읽기 일관성을 보장하기 위해 존재하는 tablespace.
rollback을 위한 공간.
과거 이미지(수정 전 데이터)를 보관하기 위한 영역.

4) temp
정렬을 위한 공간.
유저는 반드시 딱 하나의 default temp tablespace를 가짐.
여러 temp tablespace 생성 가능(유저별로 각각 서로 다른 temp tablespace 지정 가능).

5) users
oracle 생성 시 자동으로 생성되는 일반 tablespace로 필수가 아님.
일반 유저들의 default tablespace로 사용.

[tablespace 실습]

1. tablespace 구성(이름) 조회

select tablespace_name
from dba_tablespaces;

2. datafile 조회

-- 일반 tablespace에 대한 뷰
select tablespace_name,
        file_name,  -- datafile 이름
        bytes,  -- datafile size
        autoextensible  -- 자동으로 크기 증가시키는지 여부
from dba_data_files
order by 1;

-- temp tablespace에 대한 뷰
select tablespace_name,
        file_name,
        bytes,
        autoextensible
from dba_temp_files;

3. 새로운 tablespace 생성

create tablespace class1 datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' size 1m;

조회해보면 새로 만든 tablespace는 autoextensible이 NO다.

-- SCOTT 유저가 있는지 확인
select * from dba_users where username='SCOTT';

-- scott에게 class1 tablespace로 table하나 생성
create table scott.test_table1(no number) tablespace class1;

-- 테이블 잘 생성됐나 조회
select owner, table_name, tablespace_name
from dba_tables
where table_name='TEST_TABLE1';

-- 테이블 저장 공간 확인 - 최초 생성 시 segment 할당 안됨(deferred segment creation)
select owner, segment_name, tablespace_name, bytes, blocks, extents
from dba_segments
where segment_name = 'TEST_TABLE1';
-- insert 후 조회 시 segment 할당 됨.
insert into scott.test_table1 values(1);
commit;

[권한 현황]

-- 특정 유저가 갖는 오브젝트 권한 조회 (아무것도 안 나오는게 정상)
select *
from dba_tab_privs
where 1=1
and grantee = 'SCOTT';

-- 특정 유저가 갖는 시스템 권한 조회
select *
from dba_sys_privs
where 1=1
and grantee = 'SCOTT';

-- 특정 유저가 갖는 롤 권한 조회
select *
from dba_role_privs
where grantee = 'SCOTT';

4. 대용량 insert 작업 수행

-- PL/SQL 구문
begin
for i in 1..1000000000
loop
  insert into scott.test_table1 values(i);
  commit;
end loop;
end;
/
-- ORA-1653: unable to extend table SCOTT.TEST_TABLE1 by 8 in tablespace CLASS1
-- 입력되다가 error 발생

select max(no) from scott.test_table1;

5. 실제 사용량 조회

SELECT  TABLESPACE_NAME
     ,  sum(t_mbytes)                                                  AS "Allocated(MB)"
     ,  sum(t_mbytes) - sum(f_mbytes)                                  AS "Used(MB)"
     ,  sum(f_mbytes)                                                  AS "Free(MB)"
     ,  round((sum(t_mbytes) - sum(f_mbytes))/sum(t_mbytes)*100,2)     AS "PCT_Used(%)"
     ,  sum(mf_mbytes)                                                 AS "Max_free(MB)"
     ,  round((sum(t_mbytes) - sum(f_mbytes)) / sum(t_mbytes) * 100,2) AS "USAGE(%)"
  FROM (SELECT  TABLESPACE_NAME
             ,  sum(bytes/1024/1024) as f_mbytes
             ,  0                    as t_mbytes
             ,  max(bytes/1024/1024) as mf_mbytes
          FROM  DBA_FREE_SPACE a
         GROUP  BY TABLESPACE_NAME
         UNION  ALL
        SELECT  TABLESPACE_NAME
             ,  0                    as f_mbytes
             ,  sum(bytes/1024/1024) as t_mbytes
             ,  0                    as mf_mbytes
          FROM  DBA_DATA_FILES a
         GROUP  BY TABLESPACE_NAME
       )
 GROUP BY TABLESPACE_NAME
 order BY TABLESPACE_NAME;

[tablespace 용량 관리]

테이블은 tablespace 내 저장되므로 테이블에 대용량 데이터 입력 시 해당 테이블이 저장된 tablespace 여유공간이 넉넉해야한다.
tablespace 용량 증설은 datafile resize, datafile autoextend on, datafile 추가 3가지 방법으로 작업 가능

[실습 – tablespace 증설]

1. datafile resize

-- 조회
select tablespace_name,
        file_name,
        bytes,
        autoextensible
from dba_data_files
order by 1;

-- resize (1M->5M으로)
alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' resize 5m;

-- 대용량 insert 시도
begin
for i in 72600..1000000000
loop
  insert into scott.test_table1 values(i);
  commit;
end loop;
end;
/

select max(no) from scott.test_table1;
-- 근데 usage 100%가 아닌데 멈추네?? -> next extent문제인듯

2. datafile 추가

alter tablespace class1 add datafile '/oracle12/app/oracle/oradata/db1/class02.dbf' size 1m;
alter tablespace class1 add datafile '/oracle12/app/oracle/oradata/db1/class03.dbf' size 5m;

3. datafile autoextend on

alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' autoextend on;

select tablespace_name, file_name, bytes, autoextensible, maxbytes/1024/1024/1024 as "SIZE(GB)"
from dba_data_files
order by 1;
-- 최대 사이즈(32GB)가 정해져 있어서 autoextend on이라고 해도 datafile이 무한으로 늘어나진 않는다.

-- 조회 쿼리
select tablespace_name,
       file_name,
       bytes,
       autoextensible
  from dba_data_files
 order by 1;

[tablespace offline]

tablespace내 테이블 접근이 불가능하도록 비활성화.
읽기, 쓰기 금지.
장애 복구, 작업 등을 위해 offline 선언
DBWR 즉시 동작
datafile 단위로도, tablespace 단위로도 offline선언이 가능하다.

[offline mode]

1) normal mode
default.
가장 안전하게 offline 동작(DBWR가 동기화를 정상적으로 진행).
online 시 복구 필요 X.(이미 안전하게 데이터가 내려써졌으므로)

alter tablespace 테이블스페이스명 offline;

2) temporary mode
normal mode로 offline이 불가할 때(=tablespace를 구성하는 datafile 중 일부가 문제가 있을 경우) temporary mode로 offline 시도.
online 시 복구가 필요할 수도 있다.

alter tablespace 테이블스페이스명 offline temporary;

3) immediate mode
tablespace를 구성하는 모든 datafile에 문제가 생겼을 경우 복구를 시도하기 위한 offline 방식.
archive log mode 일 때만 선언 가능.

[실습 – tablespace offline]

1. tablespace 조회

select tablespace_name,
        'alter tablespace '||tablespace_name||' offline;'
from dba_tablespaces;

2. offline 시도

alter tablespace SYSTEM offline;  -- 불가능
alter tablespace SYSAUX offline;  -- SYSTEM계정은 권한없음. SYSDBA계정에서는 가능
alter tablespace UNDOTBS1 offline;  -- 불가능
alter tablespace TEMP offline;  -- 불가능 (temp는 1개 이상 있어야해서)
alter tablespace USERS offline;  -- 가능
alter tablespace CLASS1 offline;  -- 가능

3. online 시도

alter tablespace USERS online;  -- 가능
alter tablespace CLASS1 online;  -- 가능

online이 가능한 이유 : normal mode로 offline 해서 offline 전에 필요한 정보를 DBWR가 내려썼기 때문에 복구할 정보가 없으므로.

4. 시점 정보 확인

select b.name as tablespace_name,
       a.name as file_name,
       a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#
order by 1,2;
-- 시점 정보가 불일치. (offline 시켰다 online한 게 더 미래임)

5. checkpoint 발생 후 시점 재확인

alter system checkpoint;

select b.name as tablespace_name,
       a.name as file_name,
       a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#
order by 1,2;

[datafile offline]

tablespace내 특정 datafile만 offline하는 경우를 말함.
주로 장애 복구 시 offline 시도.
online 시 복구가 필요(offline하지 않은 datafile과 시점이 불일치하니까)
archive mode 여부에 따라 방법이 다름.

1. archive mode 일 때

alter database datafile 데이터파일명 offline;

2. no archive mode 일 때

alter database datafile 데이터파일명 offline drop;

[실습 – class1 tablespace 내 datafile offline]

-- 1. datafile 확인
select *
from dba_data_files
order by tablespace_name;

-- class01.dbf datafile offline
alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' offline drop;

-- 2. 상태 조회
-- 시점 check 쿼리
select b.name as tablespace_name,
        a.name as file_name,
        a.status,
        a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#
order by 1,2;
-- offline한 datafile의 status는 recover로 조회됨!

-- 3. online 시도
alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' online;
-- ORA-01113: file 5 needs media recovery
-- ORA-01110: data file 5: '/oracle12/app/oracle/oradata/db1/class01.dbf'
-- media recovery 필요하다는 error 발생

-- 4. recovery 진행. Orange에선 안됨
> recover datafile '/oracle12/app/oracle/oradata/db1/class01.dbf';
-- 거의 바로 했기 때문에 잘 됨. (복구에 필요한 시점 일치 정보가 redo log file에 존재하기 때문에)
-- 근데 만약 늦게(log switch된다면) 시도한다면 못살림. 그때는 그냥 cold_backup해둔 걸로 DB살려야됨.

alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' online;

[실습 – datafile offline해놓고 늦게 online해서 망가뜨리기]

-- 1. datafile 조회
select *
from dba_data_files
order by tablespace_name;

-- 2. datafile offline
alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' offline drop;
-- 시점 check 쿼리
select b.name as tablespace_name,
        a.name as file_name,
        a.status,
        a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#
order by 1,2;

-- 3. online 시도
alter database datafile '/oracle12/app/oracle/oradata/db1/class01.dbf' online;

-- 4. log switch 5회 이상
alter system switch logfile;
-- 망한 거 확인
> recover datafile '/oracle12/app/oracle/oradata/db1/class01.dbf';
# ORA-00279: change 2890335 generated at 11/28/2024 16:20:43 needed for thread 1
# ORA-00289: suggestion :
# /oracle12/app/oracle/product/12.2.0.1/db_1/dbs/arch1_20_1185548852.dbf
# ORA-00280: change 2890335 for thread 1 is in sequence #20

# Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
# auto  <---------------------------------------------------------auto 직접 입력!
# ORA-00308: cannot open archived log
# '/oracle12/app/oracle/product/12.2.0.1/db_1/dbs/arch1_20_1185548852.dbf'
# ORA-27037: unable to obtain file status
# Linux-x86_64 Error: 2: No such file or directory
# Additional information: 7

# ORA-00308: cannot open archived log
# '/oracle12/app/oracle/product/12.2.0.1/db_1/dbs/arch1_20_1185548852.dbf'
# ORA-27037: unable to obtain file status
# Linux-x86_64 Error: 2: No such file or directory
# Additional information: 7

-- 6. tablespace drop (또는 full restore로 원복하든가)
drop tablespace class1 including contents;

-- data 디렉터리에서 rm으로 datafile 지우기
rm class0*

-- 조회 쿼리
select *
from dba_data_files
order by tablespace_name;

-- 조회 쿼리
select b.name as tablespace_name,
        a.name as file_name,
        a.status,
        a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#
order by 1,2;

Leave a Comment