Oracle 66일차

[asm 구성 정보 확인]

1. DISK_GROUP

select group_number, name, type, total_mb, free_mb
from v$asm_diskgroup;

2. 실제 DISK

select group_number, disk_number, label, path, total_mb, free_mb
from v$asm_disk;

3. 실제 FILE

select *
from v$asm_file;

[asm 환경에서 테이블스페이스 관리]

1. 테이블스페이스 생성

create tablespace ts_new1 datafile size 5m;  -- default disk group에 생성
create tablespace ts_new2 datafile '+DATA' size 5m;  -- disk group 변경 가능
create tablespace ts_new3 datafile '+DATA/ORADB/DATAFILE/file3' size 5m;  -- 파일이름 지정 가능. 근데 ASM 환경에선 추천되지 않음.

select * from dba_data_files;

2. datafile 추가

alter tablespace ts_new add datafile size 5m;

select *
from dba_data_files
order by tablespace_name, file_id;

3. datafile resize

alter database datafile '+DATA/ORADB/DATAFILE/ts_new1.269.1190284705' resize 10m;

4. datafile autoextend on/off

alter database datafile '+DATA/ORADB/DATAFILE/ts_new1.269.1190284705' autoextend on;
-- off가 default 상태임

select tablespace_name, file_id, file_name, bytes, autoextensible
from dba_data_files
order by tablespace_name, file_id;

5. 테이블스페이스 삭제

drop tablespace ts_new1 including contents and datafiles;

실습

A유저 생성, B테이블스페이스 생성하는데 hr소유의 테이블 사이즈만큼, A유저가 B테이블스페이스 내 테이블 생성하도록 작업. hr유저(orcl DB)의 모든 테이블을 A유저 소유로 생성.

1. A유저 생성

create user A identified by oracle;
grant create table to A;  -- 때에 따라 다르겠지만 웬만해선 create drop 같은 권한은 주지 말자.
grant create session to A;

2. DB 링크 생성 (orcl)

cd $ORACLE_HOME/network/admin
vi tnsnames.ora
---------------------------------------
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.13.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

create public database link dblink_orcl
connect to system identified by oracle
using 'ORCL';
-- 실무에서는 system으로 연결되는 dblink는 public으로 만들면 안됨!

3. hr 소유 테이블 사이즈 계산

select segment_name as table_name,
       sum(bytes) / (1024 * 1024) as size_mb
from dba_segments@dblink_orcl
where owner = 'HR'
and segment_type = 'TABLE'
group by segment_name
order by size_mb desc;
-- 0.0625MB(64KB)짜리가 6개

select sum(bytes)/(1024*1024) as size_mb
from dba_segments@dblink_orcl
where owner = 'HR'
and segment_type = 'TABLE';
-- total = 0.375MB

4. B테이블스페이스 생성 및 A유저 default tbs 변경

create tablespace B datafile size 1M;
alter user a default tablespace b;
alter user a quota unlimited on b;  -- 이거 까먹기 쉬운 듯!

select username, default_tablespace
from dba_users
where username = 'A';

5. hr유저의 모든 테이블 A유저 소유로 생성

select 'create table a.'||table_name||' as select * from hr.'||table_name||'@dblink_orcl;'
from all_tables@dblink_orcl
where owner = 'HR';
--------------------------------------------------------
create table a.COUNTRIES as select * from hr.COUNTRIES;
create table a.JOB_HISTORY as select * from hr.JOB_HISTORY;
create table a.REGIONS as select * from hr.REGIONS;
create table a.DEPARTMENTS as select * from hr.DEPARTMENTS;
create table a.EMPLOYEES as select * from hr.EMPLOYEES;
create table a.LOCATIONS as select * from hr.LOCATIONS;
create table a.JOBS as select * from hr.JOBS;

-- pl/sql 이긴한데 실행은 안 해봐서 잘 돌아가는지 확실하진 않음
begin
   for tbl in (
      select table_name
      from all_tables@dblink_orcl
      where owner = 'HR'
   ) loop
      execute immediate 'create table a.' || tbl.table_name || ' as select * from hr.' || tbl.table_name || '@dblink_orcl';
   end loop;
end;
/

-- 확인
select *
from dba_tables
where owner = 'A';

[ASM 다중화]

1. control file
– 최소 2개 이상 권고
– default : 1개
– 변경 : parameter file 수정

2. redo log file
– 최소 3개 그룹(RAC의 경우 각 노드별로 2개 그룹), 각 그룹별 2개 멤버 구성 권고
– 19c RAC 2node의 default : 4개 그룹(우리가 2노드라서), 1개 멤버
– 변경 : control file 수정

실습

control file : 2개로 변경
redo log file : 각 그룹별 멤버 2개씩으로 만들고, 그룹 개수는 4개 그대로 유지

1. parameter file 수정

startup nomount

alter system set control_files = '+DATA/ORADB/CONTROLFILE/current.257',
                                 '+DATA/ORADB/CONTROLFILE/current.258' scope = spfile;

2. db shutdown

srvctl stop database -d oradb

3. control file 물리적으로 cp

asmcmd cp '+DATA/ORADB/CONTROLFILE/current.257' '+DATA/ORADB/CONTROLFILE/current.258'

4. db startup

srvctl start database -d oradb

-- 조회
select * from v$controlfile;

5. 멤버 생성

alter database add logfile member '+DATA' to group 1;
alter database add logfile member '+DATA' to group 2;
alter database add logfile member '+DATA' to group 3;
alter database add logfile member '+DATA' to group 4;

-- 조회
select * from v$logfile;

추가 작업

기존의 restore 과정에서 incarnation 넘버 없는 파일 형태로 DB를 오픈시킨 상황 다시 incarnation 넘버가 있는 형태로 모든 리두 멤버를 관리하기 위해 incarnation 넘버가 없는 멤버는 삭제, 다시 새로운 멤버 추가.

alter database drop logfile member '+DATA/ORADB/ONLINELOG/group_1.258';
alter database drop logfile member '+DATA/ORADB/ONLINELOG/group_2.259';
alter database drop logfile member '+DATA/ORADB/ONLINELOG/group_3.266';
alter database drop logfile member '+DATA/ORADB/ONLINELOG/group_4.267';
alter system switch logfile;  -- current redo 인 애들 삭제하려고 사용.
-- 만약 그런 멤버 없다면서 drop이 안되면 log switch 많이 해놓고 다시 하면 drop 됨.

alter database add logfile member '+DATA' to group 1;
alter database add logfile member '+DATA' to group 2;
alter database add logfile member '+DATA' to group 3;
alter database add logfile member '+DATA' to group 4;

-- asmcmd 에서 물리 파일도 삭제 해줘야 됨.
ASMCMD> rm group_1.258
ASMCMD> rm group_2.259
ASMCMD> rm group_3.266
ASMCMD> rm group_4.267
ASMCMD> rm group_4_2
ASMCMD> rm group_3_2
ASMCMD> rm group_2_2
ASMCMD> rm group_1_2

[ASM 환경에서 파일 추가 방식]

1) 자동
– 파일이 생성되는 위치만 지정
– 데이터파일의 경우 위치도 생략 가능

ex)
alter tablespace ts_new1 add datafile size 10m;  -- datafile의 경우 위치 생략 가능
alter tablespace ts_new1 add datafile '+DATA' size 10m;  -- 위치 지정(이름은 자동 부여)
alter database add logfile member '+DATA' to group 1;  -- redo member는 위치 지정 필수

2) 수동
– 파일의 위치와 이름을 사용자가 직접 지정
– 자동으로 file_number, incarnation을 갖는 실제 파일이 생기고 사용자가 만든 파일은 alias로 관리.

[ADR (Automatic Diagnostic Repository)]

– ORACLE에서 제공하는 자동 진단 저장소
– ORACLE 설치 및 운영에 필요한 로그들을 자동 기록함.
ex) alert log, crs log, css log
– ADR Base 하위에 여러 로그가 자동 저장
– 설치 시 ADR Base = ORACLE_BASE (변경 가능)
– ADR Base 변경은 diagnostic_dest 파라미터로 변경

-- 기본 Diagnostic 설정
select * from v$diag_info;

-- diagnostic_dest 파라미터 조회
select *
from v$parameter
where name = 'diagnostic_dest';

-- alert log 위치
ADR_Home/trace/alert_{INSTANCE_NAME}.log
ADR Home : ADR_BASE/diag/rdbms/{db_name}/{instance_name}
현재 위치 : /oracle/app/oracle/diag/rdbms/oradb/ORADB1/trace/alert_ORADB1.log

-- crs log 위치

현재 위치 : /oracle/app/oracle/diag/crs/oel8db1/crs/trace/alert.log

-- 1번노드 vi .bash_profile 수정
alias alert='tail -f /oracle/app/oracle/diag/rdbms/oradb/ORADB1/trace/alert_ORADB1.log'
alias crs='tail -f /oracle/app/oracle/diag/crs/oel8db1/crs/trace/alert.log'

-- 2번노드 vi .bash_profile 수정
alias alert='tail -f /oracle/app/oracle/diag/rdbms/oradb/ORADB2/trace/alert_ORADB2.log'
alias crs='tail -f /oracle/app/oracle/diag/crs/oel8db2/crs/trace/alert.log'

-- archive log file 뭐 있어야 되는지 조회하는 쿼리
select *
from v$archived_log
order by first_time, thread#;