Oracle 73일차

외부에서 DB접속 불가 시 check

1. DB가 정상인지 확인

sqlplus system/oracle@자기자신tns
-- 이거 접속 잘 되면 무조건 DB쪽 잘못은 아니고 사용자쪽임.

2. client쪽에서 tnsnames.ora 확인

3. 방화벽 확인 (네트워크 담당자)

[증분 백업 Incremental backup]

full backup과는 다르게 이전 백업파일을 기준으로 변경된 블럭만 압축하여 백업을 받는 방식.
백업 속도 개선.
백업 용량 효율적 관리 (압축함) -> 보관 비용 절약
레벨을 사용하여 백업 계획

** 레벨
– 증분 백업을 받는 기준 설정
– 현재 레벨보다 작거나 같은 레벨의 이후 변경된 블럭을 백업함
1) 0레벨
– full backup을 의미.
2) 1레벨
– 0레벨 백업셋 이후에 변경된 블럭만 백업.
3) 2레벨
– 1레벨 백업셋 이후에 변경된 블럭만 백업.

** 명령어
backup incremental level 0 database;
backup incremental level 0 database format ‘/home/oracle/backup/db_&U’;

backup incremental level 1 database;
backup incremental level 1 database format ‘/home/oracle/backup/db_&U’;

[RMAN 증분 백업을 사용한 복구(물리장애)]

1. oel8copy에서 0레벨 증분 백업 진행

backup incremental level 0 database;
-- tag=TAG20250122T110440

2. 테이블 생성

create table recover_test200(no number);
insert into recover_test200 values(1000);
insert into recover_test200 values(2000);
insert into recover_test200 values(3000);
commit;
alter system checkpoint;

3. 1레벨 증분 백업

backup incremental level 1 for recover of copy with tag 'tag_recover_test1' database;

list backup;
list backup of database summary;

5. 로그 스위치 5번

alter system switch logfile;

6. 장애 발생

select 'rm '||file_name from dba_data_files;
------------------
rm /oracle19/app/oracle/oradata/ORA19DB/system01.dbf
rm /oracle19/app/oracle/oradata/ORA19DB/sysaux01.dbf
rm /oracle19/app/oracle/oradata/ORA19DB/undotbs01.dbf
rm /oracle19/app/oracle/oradata/ORA19DB/users01.dbf
rm /oracle19/app/oracle/oradata/ORA19DB/ts_test01.dbf
rm /oracle19/app/oracle/oradata/ORA19DB/ts_test02.dbf

7. db shutdown abort

8. 복구

startup mount;

run {
restore database from tag TAG20250122T110440;
recover database from tag tag_recover_test1;
recover database; 
sql 'alter database open';
}

-- 0 level backupset tag
-- 1 level backupset tag
-- archive를 사용한 recover

8. 데이터 확인

select * from system.recover_test200;

[RMAN 증분 백업을 사용한 복구 (논리 장애)]

ORA19DB에서 테이블 truncate 발생. (증분백업 상황 가정)
-> recoverdb 서버에 새로운 복구 DB 구축 후 시점 복구 진행
-> ora19db -> recover dblink를 통한 데이터 이관

사전준비

1. recoverdb 용 리스너 구성

1. listener.ora 수정
vi $ORACLE_HOME/network/admin/listener.ora
---------------------------------------------------
LISTENER_RECOVERDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = recoverdb)(PORT = 1521))
    )
  )

2. tnsnames.ora 수정
RECOVERDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = recoverdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora19db)
    )
  )
LISTENER_RECOVERDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = recoverdb)(PORT = 1521))

3. 로컬리스너 추가
alter system set local_listener = LISTENER_RECOVERDB scope = both;
show parameter local_listener

4. 리스너 start
lsnrctl start LISTENER_RECOVERDB

2. ora19db -> recoverdb dblink 생성

1. /etc/hosts 수정
172.16.124.135 recoverdb

2. ora19db의 tnsnames.ora 수정
RECOVERDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = recoverdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora19db)
    )
  )

3. dblink 생성
create public database link dblink_recoverdb connect to system identified by oracle using 'RECOVERDB';

복구 실습

0. 양쪽서버 모두에서 기존 백업 / 아카이브 삭제
편하게 scp로 보내기 위해서

rm /oracle19/backup/ora19db/*
rm /oracle19/arch/*

> delete backupset;

1. 0레벨 백업

configure controlfile autobackup format for device type disk to '/oracle19/backup/ora19db/%F';
configure channel device type disk format '/oracle19/backup/ora19db/d_%d_%U_%T.bk';

backup incremental level 0 database;
-- tag=TAG20250122T131249

select sysdate from dual;
-- 2025/01/22 13:14:10

2. 테이블 생성

create table recover_test300(no number);
insert into recover_test300 values(300);
insert into recover_test300 values(400);
insert into recover_test300 values(500);
commit;
alter system checkpoint;

3. 1레벨 백업

backup incremental level 1 for recover of copy with tag 'tag_recover_test1' database;

list backup;
list backup of database summary;

4. 장애 발생

select sysdate from dual;
-- 2025/01/22 14:13:42

truncate table recover_test300;
alter system switch logfile;

5. copydb (recoverdb) 를 사용한 복구를 위해 파일 전송하기
** 필요 파일
– parameter file
– archive file
– backup file

create pfile='/home/oracle/backup/init.ora' from spfile;

scp /home/oracle/backup/* oracle@recoverdb:/home/oracle/backup
scp /oracle19/arch/* oracle@recoverdb:/oracle19/arch
scp /oracle19/backup/ora19db/* oracle@recoverdb:/oracle19/backup/ora19db

6. recoverdb에서 복구

1) init.ora 수정하기
* 수정대상
- 로그위치 수정(실제 물리적인 디렉토리도 생성돼야 함)
- controlfile 위치 수정
- 아카이브 위치 수정
- ORA19DB로 시작하는 모든 파라미터 삭제
- local_listener 설정 삭제
- pluggable 설정 유지

mv /home/oracle/backup/init.ora /home/oracle/backup/initrecoverdb.ora
vi /home/oracle/backup/initrecoverdb.ora
--------------------------------------
*.audit_file_dest='/oracle19/recover/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle19/recover/data/control01.ctl'
*.db_block_size=8192
*.db_name='ora19db'
*.diagnostic_dest='/oracle19/recover'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora19dbXDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/oracle19/arch'
*.memory_target=1192m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
----------------------------------------
cp initrecoverdb.ora $ORACLE_HOME/dbs

2) nomount
export ORACLE_SID=recoverdb
startup nomount

3) controlfile restore - 0레벨 controlfile!
RMAN> restore controlfile from '/oracle19/backup/ora19db/c-73391101-20250122-04';

4) mount
alter database mount;

5) restore & recover
catalog start with '/oracle19/backup/ora19db';

-- 아래 이거 맞나? 모름...
run {
allocate channel ch1 device type disk ;
allocate channel ch2 device type disk ;

set newname for tempfile 1 to '/oracle19/recover/data/temp01.dbf';     
set newname for datafile 1 to '/oracle19/recover/data/system01.dbf';
set newname for datafile 3 to '/oracle19/recover/data/sysaux01.dbf';
set newname for datafile 4 to '/oracle19/recover/data/undotbs01.dbf';
set newname for datafile 5 to '/oracle19/recover/data/pdbseed/system01.dbf';
set newname for datafile 6 to '/oracle19/recover/data/pdbseed/sysaux01.dbf';
set newname for datafile 7 to '/oracle19/recover/data/users01.dbf';
set newname for datafile 8 to '/oracle19/recover/data/pdbseed/undotbs01.dbf';
set newname for datafile 9 to '/oracle19/recover/data/orapdb/system01.dbf';
set newname for datafile 10 to '/oracle19/recover/data/orapdb/sysaux01.dbf';
set newname for datafile 11 to '/oracle19/recover/data/orapdb/undotbs01.dbf';
set newname for datafile 12 to '/oracle19/recover/data/orapdb/users01.dbf';
set newname for datafile 13 to '/oracle19/app/oracle/oradata/ORA19DB/ts_test01.dbf';
set newname for datafile 14 to '/oracle19/app/oracle/oradata/ORA19DB/ts_test02.dbf';

restore datafile 1,3,4,5,6,7,8,9,10,11,12,13,14;

switch datafile all;
switch tempfile all;
}

restore database from tag TAG20250122T131249;

run {
set until time "to_date('2025/01/22 14:13:42', 'YYYY/MM/DD HH24:MI:SS')";
recover database from tag tag_recover_test1;
recover database;
}

-- 위에 잘 안되면
run {
set until time "to_date('2025/01/22 14:13:42', 'YYYY/MM/DD HH24:MI:SS')";
restore database from tag TAG20250122T131249;
recover database from tag tag_recover_test1;
recover database;
}

6) open
alter database open resetlogs;

7. 조회

select * from system.recover_test300;

[Multi-Tenant DB]

하나의 인스턴스에 데이터를 분리하여 여러 DB를 구성하기 위한 아키텍처.
새로운 DB를 쉽게 생성, 삭제 가능함.
각 DB들이 공유 자원을 사용하므로 자원 사용이 효율적임.

1. CDB (container DB)
– sqlplus / as sysdba로 접속할 수 있는 main DB
– 여러 PDB를 관리
– 공통 인프라(메모리, 프로세스, 로그 파일 등을 공유)
– 공통 인프라를 사용하여 여러 개의 DB를 관리할 수 있기 때문에 자원을 보다 효율적으로 사용할 수 있음

2. PDB (pluggable DB)
– CDB와 다르게 개별적으로 분리된 DB. 보통 업무용 DB
– CDB에서 간단하게 생성/해제 가능
– 각 PDB마다 독립적인 스키마 생성, 운영
– PDB 템플릿을 사용하여 새로운 PDB 생성이 용이

1. CDB datafile 조회
select * from dba_data_files;

2. 전체 datafile 조회
select file#, name, bytes/1024/1024
from v$datafile
order by 2;
-----------------------
-- PDB
/oracle19/app/oracle/oradata/ORA19DB/orapdb/sysaux01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/system01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/undotbs01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/users01.dbf

-- pdbseed = template
/oracle19/app/oracle/oradata/ORA19DB/pdbseed/sysaux01.dbf
/oracle19/app/oracle/oradata/ORA19DB/pdbseed/system01.dbf
/oracle19/app/oracle/oradata/ORA19DB/pdbseed/undotbs01.dbf

-- CDB datafile
/oracle19/app/oracle/oradata/ORA19DB/sysaux01.dbf
/oracle19/app/oracle/oradata/ORA19DB/system01.dbf
/oracle19/app/oracle/oradata/ORA19DB/ts_test01.dbf
/oracle19/app/oracle/oradata/ORA19DB/ts_test02.dbf
/oracle19/app/oracle/oradata/ORA19DB/undotbs01.dbf
/oracle19/app/oracle/oradata/ORA19DB/users01.dbf

** 아키텍처 architecture
1. 공유 구성 요소
– 시스템 자원 (cpu, memory, process 등)
– 인스턴스 (SGA)
– 백그라운드 프로세스
– parameter file / controlfile / redo log file

2. 독립 구성 요소
– datafile only
– 스키마, 유저 정보

1. CDB/PDB 조회

1. CDB(기본 접속 DB)
sqlplus / as sysdba
show con_name
CON_NAME
------------------------------
CDB$ROOT

2. PDB
col name format a10
col open_mode format a10
select name, open_mode from v$pdbs;
NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
ORAPDB     MOUNTED

2. PDB open

PDB는 최초 설치 후 상태가 mounted 이므로 사용을 원하면 open해줘야 함

alter pluggable database ORAPDB open;

select name, open_mode from v$pdbs;
NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
ORAPDB     READ WRITE
-- READ WRITE가 open상태라는 뜻

3. PDB 접속 (CDB를 통한 접속)

alter session set container=ORAPDB;
show con_name
CON_NAME
------------------------------
ORAPDB

select * from system.recover_test1;  -- CDB에서 생성한 데이터는 PDB에서 조회 불가

4. PDB 접속 (직접 접속)

1. tns 이용하는 방
1-1. tnsnames.ora에 내용 추가
vi tnsnames.ora
ORAPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8copy)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orapdb)
    )
  )

1-2. 접속
sqlplus system/oracle@orapdb
show con_name
CON_NAME
------------------------------
ORAPDB

2. easy connect
sqlplus system/oracle@localhost:1521/orapdb
show con_name
show con_name
CON_NAME
------------------------------
ORAPDB

[PDB 생성]

기존 템플릿을 사용한 PDB 생성

1. 템플릿 확인
select name from v$pdbs;
NAME
--------------------------------------------------------------------------------
PDB$SEED -- 얘가 기본으로 제공되는 템플릿
ORAPDB

2. 템플릿 위치 확인
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle19/app/oracle/oradata/ORA19DB/system01.dbf
/oracle19/app/oracle/oradata/ORA19DB/sysaux01.dbf
/oracle19/app/oracle/oradata/ORA19DB/undotbs01.dbf
/oracle19/app/oracle/oradata/ORA19DB/pdbseed/system01.dbf  -- /pdbseed/ 하위에 생성됨
/oracle19/app/oracle/oradata/ORA19DB/pdbseed/sysaux01.dbf
/oracle19/app/oracle/oradata/ORA19DB/users01.dbf
/oracle19/app/oracle/oradata/ORA19DB/pdbseed/undotbs01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/system01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/sysaux01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/undotbs01.dbf
/oracle19/app/oracle/oradata/ORA19DB/orapdb/users01.dbf
/oracle19/app/oracle/oradata/ORA19DB/ts_test01.dbf
/oracle19/app/oracle/oradata/ORA19DB/ts_test02.dbf

3. 새로운 PDB 생성 (CDB에서 작업)
create pluggable database orapdb2 admin user scott identified by oracle file_name_convert=('/pdbseed/', '/pdbseed2/');

4. 접속
alter session set container=orapdb2;

show con_name

col name format a10
col open_mode format a10
select name, open_mode from v$pdbs;
NAME       OPEN_MODE
---------- ----------
ORAPDB2    MOUNTED

5.  PDB open
alter pluggable database orapdb2 open;
select name, open_mode from v$pdbs;
NAME       OPEN_MODE
---------- ----------
ORAPDB2    READ WRITE

6. (생략 가능) tnsnames.ora 수정해서 접속하기
sqlplus scott/oracle@orapdb2

PDB 2개 모두 listener 설정해서 외부접속 가능하게 하기

-- windows의 tnsnames.ora 파일 수정
ORAPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.124.134)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orapdb)
    )
  )

ORAPDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.124.134)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orapdb2)
    )
  )