따봉 구걸남's 블로그

오라클은 8i 에서부터 백업을 위해 rman 이라는 것을 처음 소개했다. 
백업과 복구가 보다 쉬워진다는 것이 오라클이 내세운 명분이다.
하지만 새로운 것을 다시 배워야 하기에 한동안 DBA 들에겐 외면받아왔다. 
그렇지만 본격적으로 오라클이 ASM 이라는 개념을 들고왔다.
그리고 이것은 rman 을 통해서만 백업이 가능해서 이제 DBA 들은 RMAN 을 반드시 익혀야 하는 현실이다. 

각설하고..

Recovery catalog DB 란

한마디로 rman 이 사용할 metadata 들을 저장한 db스키마 라고 생각하면된다. 
RMAN 을 사용하려면 백업과 복구를 위한 정보들을 저장할 공간이 필요하다.
별도로 CATALOG DB 를 설치하지 않아도 rman 사용에는 지장이 없다.
이 경우 controlfile 에 해당 정보들이 저장된다. 


Recovery catalog 가 담는 metadata types 은 어떤 것들인가

 1) datafile, archived redo log backup sets & backup pieces
 2) datafile copies
 3) archived redo logs and their copies
 4) db structure (tbs, datafiles)
 5) stored scripts
 6) rman config


Recovery catalog DB 사용의 이점

안써도 사용가능하지만 굳이 catalog db 를 사용해서 얻는 이점은 아래와 같다.
1) redundancy of controlfile
 =>  catalog db 를 쓴다고 controlfile 에 metadata 를 저장하지 않는 것은 아니다. 정보의 중복 저장이다. 
  이것이 이점이 될수 있을까 싶지만, 원본db 의 controlfile 이 삭제되었을때도 카탈로그에는 남아있는 장점이 있다.
  
** backup, restore, and crosscheck 같은 operation 발생시 metadata 가 변경이 된다. 
이때 순서는 언제나 target db controlfile 우선 update, 카탈로그db 는 후에 sync 된다. 

2) 모든 타겟 DB metadata 중앙관리화 
 => 여러 서버를 가진 경우 한곳에서 metadata 정보 관리하기 편함. 

3) longer metadata history
 => controlfile 에 저장할때 보다 더 긴 기간 정보 저장이 가능. 

4) stored script 사용
 => catalog 에서만 가능한 것. 한번 스크립트를 생성해서 재사용 가능. 아래에서 확인해보자


 * 아카이브로그를 지우는 script 를 생성해보자. 

CREATE SCRIPT DEL_ARCH
{
 crosscheck archivelog all;
 delete noprompt expired archivelog all;
 delete noprompt archivelog all completed before 'sysdate -1';
}

* 이번에는 global keyword 로 생성해보자. 

CREATE GLOBAL SCRIPT GLB_DEL_ARCH
COMMENT 'use when deleting arc log..'
{
crosscheck archivelog all;
  delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate -1';
}
* 아래 구문으로 실행해보자. 
RUN
{
EXECUTE SCRIPT GLB_DEL_ARCH;
}

카탈로그 디비에 해당 스크립트가 저장이 되고 이는 아래 뷰에서 확인이 가능하다. 
SELECT *
  FROM RC_STORED_SCRIPT
;
 

전통적 테이블복구방식
recover table 명령
개념
undo, 해당 datafile, archive 을 자동 restore
dump로 떨구기

모니터링
어느시점의 백업을 쓰는가

어느시점의 아카이브를 적용중인가

-- rman table backup test
* archive mode
@testdb

1) setting 
rman target / 
backup database;

drop table oraadm.rman_test purge;
create table oraadm.rman_test(a int, b timestamp);

2) dml & arch

set time on

insert into oraadm.rman_test(a,b) values(1,systimestamp);
insert into oraadm.rman_test(a,b) values(2,systimestamp);
insert into oraadm.rman_test(a,b) values(3,systimestamp);
insert into oraadm.rman_test(a,b) values(4,systimestamp);
insert into oraadm.rman_test(a,b) values(5,systimestamp);

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

commit;

select count(*) from oraadm.rman_test;

select systimestamp from dual;

3) human error

insert into oraadm.rman_test(a,b) values(6,systimestamp);
insert into oraadm.rman_test(a,b) values(7,systimestamp);

-- human error
delete from oraadm.rman_test;

commit;

select count(*) from oraadm.rman_test;

4) restore & recover

backup database plus archivelog;

/* ##### init ora ###############
db_name=TESTDB
db_unique_name=AUX_TESTDB
compatible=19.0.0
db_block_size=8192
db_files=3000
diagnostic_dest=/oralog
_system_trig_enabled=FALSE
sga_target=2194M
processes=200
db_create_file_dest=+RECO_DG
log_archive_dest_1='location=+RECO_DG'
max_string_size=EXTENDED
############################## */

SET AUXILIARY INSTANCE PARAMETER FILE TO '/xxx/initAUX.ora'

recover table oraadm.rman_test
until time "to_date('2023-02-23 15:49:30','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '+RECO_DG'
DATAPUMP DESTINATION '/dawork/TESTDB/dpdump'
DUMP FILE 'exp_rman_test.dmp'
NOTABLEIMPORT;

-- monitoring
select
from v$backup files
where tag like

select
from v$archived log
where archived yes
  and completion time > sysdate 1



impdp oraadm/oraadm \
job_name=imp_aux_t
DUMPFILE='exp_rman_test.dmp'
remap_table=RMAN_TEST:RMAN_TEST2
LOGFILE='imp_test.log'
DIRECTORY=DATA_PUMP_DIR
CONTENT=all
TABLE_EXISTS_ACTION=REPLACE

col b for a40
select * from oraadm.rman_test2;

오라클에서 사용가능한 백업 복구 방식에 대해 알아보자 

1. flash back
 : 사용자 실수로 삭제된 데이터, 테이블 복원에 적합하다.
   recyclebin 에 남아있는 정보를 기반으로 복원하기 때문에 
   undo retention이나 undo 사이즈에 따라 일정 시간이 지나면 사용 불가한 복구방식. 
   복구대상 테이블에 DDL 이나 truncate 가 되었다면 사용불가

2. ctas + 압축
 : 예정된 작업 전의 백업에 사용하기 적당.   
 
3. rman  
 : 다양한 방식으로 백업 가능. 클론디비가 필요한 복원은 비교적 간단하진 않다.
   datafile + arc + redo 가 사용되어 원하는 시점복구가 가능.
 - physical level 방식의 이점 => block-level compression, incremental backup 
 - physical level 방식의 단점 => db버전이나 설정등에 따른 제약 존재  
 
4. datapump 
 : logical level (row level) 방식. 
   rman 에 비해서 조작이 비교적 단순하다.
   오직 백업받은 시점으로만 복원이 가능    
   
5. sqlloader 
 : samfile 을 내려서 백업/복구 하는 방식.
 이기종 DBMS 간의 데이터 이동시는 거의 유일한 선택지 
  

[oracle@asmtest ~]$ oerr ora 24962
24962, 00000, "connect string could not be parsed, error = %s"
// *Cause: The address portion of the connect string could not be parsed.
//         The client may be using a format of the connect string that the
//         server does not understand.
// *Action: Correct the connect string.
[oracle@asmtest ~]$

jdbc 로 db 에 접속하려고 할때 connect string 에 잘못된 특수문자가 있으면 발생하는 오류

예를 들면 아래형태로 디비에 접속하는데 괄호를 실수로 닫지 않는 등의 문제 

jdbc.dbalias.oracle-thin=@(description=(address=(host=orahost)(protocol=tcp)(port=1521))(connect_data=(service_name=orcl)))

클라이언트 쪽이 둔한 사람이면 에러가 나는 지도 모르고 있을 확률이 높다.

was 쪽 담당자에게 해당 에러를 알려주자 

오라클 인스턴스가 시작되면 초기화 파라미터에 명시한대로 메모리영역을 할당하고 백그라운드 프로세스를 시작한다. 

메모리영역에 저장되는 정보는 다음과 같다. 

- 프로그램코드
- DB에 연결된 세션 정보 (inactive 포함)
- 프로그램 실행 중에 필요한 정보(예: 결과 rows 을 가져오는 쿼리의 현재 상태)
- 프로세스 간에 공유되는 lock 정보 
- 캐싱된 data (data blocks, redo record)

1. 오라클 메모리 구조

 1) SGA (System Global Area)

- Shared pool : SQL statements, data dictionary, Stored procedures 등을 캐싱하여 공유

- DB buffer cache : data block 을 disk I/O 를 통해 캐싱하여 공유. 
- Redo log buffer : redo log file 을 캐싱하여 성능 향상 
- Large pool : 아래 case 에서 shared pool 을 사용할수 있는데 large pool 을 사용하는 것을 고려해볼수 있다.
    shared server (session memory)
    Parallel query (message buffers)  
    Recovery Manager(I/O buffers )

- Java pool

 2) PGA (Program Global Area)

 : Server process 가 사용하는 메모리 영역 ( oracle 에서는 작업대 정도로 비유함)
  각 server process 간에 각자의 PGA 를 사용하고 서로 공유되지 않음.
  세션 A 가 쿼리를 날렸을때 중간계산된 값이 다른 세션에 의해 사용이 되면 안될것이다. 
 

 각 서버프로세스는 각자의 PGA 를 할당받는다고 했는데, 시스템 전체의 PGA를 total instance PGA 혹은 Instance PGA 라고 부름.

SGA 는 시스템 전체에서 하나이기에 (single node 기준)  sga_max_size, sga_target 을 설정하지만 
PGA 는 서버프로세스별 할당하므로 연관된 초기화 파라미터에 전체를 의미하는 aggregate 가 붙는다.(pga_aggregate_limit,pga_aggregate_target) 

PGA 는 전체의 크기만 설정 가능하고 개별 세션의 PGA 를 조정하려면 아래와 같이 해야함.

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1024000; 

하지만 오라클에서는 memory 관리를 수동으로 조정하는 것을 권장하지 않고 있다. 

3) UGA (User Global Area) 
  The UGA is memory associated with a user session.
  
 4) Software code areas

참조문서 https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT007

테이블스페이스 사용량 확인할때 보통 dba_data_files 와 dba_free_space 뷰를 조인하는데

특정 디비에서 쿼리 수행시간이 매우 오래 걸리는 현상 확인. 

dba_data_files 만 조회했을때는 이상이 없으나 dba_free_space 가 원인임을 파악.

MOS(Doc ID 271169.1) 를 확인해보면 10g 부터 나온 기능인 recycle bin 에서 표시되는 object 까지 dba_free_space 가 포함하는 것으로 변경이 됨.

sys 로 로긴후 아래 실행

SELECT substr(DROPTIME,1,10), count(*)
  FROM dba_recyclebin
GROUP BY substr(DROPTIME,1,10)
; 

purge dba_recyclebin;

[oracle@asmtest ~]$ oerr ora 12801
12801, 00000, "error signaled in parallel query server %s"
// *Cause: A parallel query server reached an exception condition.
// *Action: Check the following error message for the cause, and consult
//          your error manual for the appropriate action.
// *Comment: This error can be turned off with event 10397, in which
//           case the server's actual error is signaled instead.
[oracle@asmtest ~]$

병렬쿼리를 실행하다가 발생하는 에러. 

어떠한 이유로 인해서 병렬쿼리를 실행할수 없게 되었다는 메세지 일뿐, 직접적인 원인이 되는 에러는 따로 있다.

근본적인 원인인 에러(ora 12801 이 아닌) 가 발생해서, 병렬로 실행하던 쿼리를 더이상 진행할수 없다는 말이다.

 선행해서 발생한 에러를 확인해 보자. 

앞선 포스팅에서 single node ASM 기반 database 를 설치하였다. 

터미널로 붙어서 sqlplus 로 접속할수도 있지만 불편하다. 

local 환경에서 client tool 을 설치하고 VM 의 oracle 에 접속하자. 

1. DBeaver 설치 

client tool 은 DBeaver 를 설치해보자. 무료이기도 하고 많이들 사용하고 있다. 

아래 링크에서 설치파일을 다운받자. 

https://dbeaver.io/download/

개인의 로컬환경에 맞는 버전을 선택하자. 나는 윈도 환경이다. 

설치는 별거 없고 다운받은 .exe 파일을 실행만 하면 된다.

2. VM Port Forwarding

우리는 VM 에 있는 oracle 을 사용하기에 VM 에서 한가지더 해줘야 할게 있다. 

앞에서도 ssh 접속을 위해 추가한 포트포워딩 룰 이 있다. 

여기에 1521 포트도 똑같이 추가하자. 

- port, 방화벽 확인 

 터미널로 접속해서 oracle 포트가 열려있는지 확인한다. 

1521 port 가 잘 listen 하고 있다. 

방화벽이 동작하고 있다면 아래 명령으로 방화벽도 내려놓자.

systemctl stop firewalld

3. DBeaver Connection 설정 

다시 dbeaver 로 돌아와서 커넥션 정보를 추가하자. 

아래 빨간 박스 안의 내용을 확인하고 하단의 Test connection 클릭.

접속 잘된다.

이상 끗

도움 되었으면 따봉 눌러주기!

'ORA-1555 snap shot too old'  에러는 개발자들을 당혹케 만드는 오라클 에러 중 하나이다.

해당 에러에 대한 oerr 유틸의 메세지는 아래와 같다. 

[oracle@asmtest ~]$ oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

한 사용자가  A 쿼리를 날렸는데 해당 데이터 블록이 (내가 읽으려고 하는 시점 이전에 다른 사용자에 의해 )
변경이 되어, 롤백 세그먼트에서 해당 블록의 변경전 데이터를 찾으려고 하는데 이미 다른사용자가 
해당 롤백 레코드를 덮어 써버려서 이전 데이터를 찾을수 없어서 발생하는 에러. 

이를 그림으로 표현하자면 아래와 같다. 

개발자들이 이 현상을 이해하려면 아래 두가지에 대한 이해가 필요하다. 

- 읽기 일관성

- 언두(롤백) segment 의 매커니즘  

1. 읽기 일관성 ( Read consistency)

여기서는 읽기 일관성을 최대한 간단하게 설명해보겠다. (자세한건 검색으로..)

읽기 일관성이란 쿼리의 리턴 데이터들의 시점이 일관성이 있어야 한다는 것이다. 

즉, 위의 그림에서 쿼리 A 가 아무리 오래 걸려도 이들 결과 값은 같은 시점의 데이터여야 한다는 것이다. 

마치 쿼리 A 가 시작했을때, 해당 DB 의 그 시점의 스냅샷을 뜬것처럼 데이터를 리턴해야 한다는 것이다. 

위 그림의 예를 들면 table 이 존재하고, pk 가 aa 인 1 row 가 있다고 가정하자.

조회쿼리 Query A 가 08:00 시에 실행되었다고 치면, 그 당시에 col A 의 값은 1 이었다. 

그런데 다른 쿼리 Query B 가 해당 row 를 08:10 에 col A 를 2로 업데이트 했다. 

또다시 Query C 가 해당 row 를 08:15 에 col A 를 3 으로 업데이트 했다.

 그러면 08:00 에 시작한 Query A 가 08:16 분에 해당 row 를 읽으려고 하면 어떤 값을 return 해야하는가?

현재 데이터블록의 해당 row 값은 3 이지만, 읽기 일관성의 관점에서는 Query A 의 시작시점 값인 1 을 리턴해야한다. 

이를위해서 오라클은 과거시점의 데이터를 읽기일관성을 위해 DB의 공간에 저장할 필요가 있는데 

이곳이 언두(롤백) 세그먼트 이다.  

2. 언두세그먼트 의 매커니즘

언두세그먼트는 디비 전체 (정확히는 instance별) 에서 공유하는 resource 이다. 

설정된 공간을 계속해서 덮어쓰는 구조이다. 언두 공간이 부족하면 추가를 할수 있지만 아무런 기준없이 추가를 계속할수는 없고 관리자의 입장에서 사이트의 특성을 고려해서 전략적으로 선택해야한다.

- undo retention

 언두 사이즈도 중요하지만 언두데이터를 얼마의 시간동안 유지 할것인가도 중요하다. 

  유지시간을 너무 짧게 잡으면 금방 다른 데이터로 덮어씌여져 SNAPSHOT TOO OLD 가 발생할 수 있다. 

 반면 필요 이상으로 길게 잡으면 언두블록을 할당받아야할 세션들에 병목이 발생할수 있다.

 식당으로 치면 회전률이 안나오는 격이니 주인 입장에서는 골치가 아프다. 

3. 에러에 대한 조치

 - ORA-1555 가 발생한 쿼리가 너무 오래동안 수행되었다면 쿼리성능 개선을 고려해본다.

 - UNDO RETENTION 이 너무 짧다면 좀 더 길게 변경 해준다. 

- UNDO RETENTION 이 적정한데도 에러가 났다면 UNDO DATAFILE 을 추가해준다. 

- 업무시간 중에 대량의 DML 이 발생하지 않게 트랜잭션을 재배치 해준다. 

앞선 포스팅에서 single server 에서 asm 을 사용하기 위한 grid 설치를 알아보았다. 

19c grid 설치 Standalone server (Oracle Restart)

이제 FILESYSTEM 이 아닌 ASM diskgroup 에 데이터베이스를 생성해보자. 

oracle 19c database 설치파일은 아래링크에서 다운받을수 있다. 

https://www.oracle.com/kr/database/technologies/oracle-database-software-downloads.html

oracle 계정의 .bash_profile 은 위의 글을 참조하자.

본격적으로 설치 시작 

db_env => 

=================================================
mkdir -p $ORACLE_HOME
mv LINUX.X64_193000_db_home.zip $ORACLE_HOME
cd $ORACLE_HOME
unzip LINUX.X64_193000_db_home.zip 

./runInstaller

엔진설치, DB생성을 모두 하자. 

설치파일이 있는 디렉토리와 oracle base 디렉토리가 자동으로 잡힌다. 

 container 설치는 목적이 아니니까 체크해제 한다.

ASM 을 선택하자 

 recovery 영역도 ASM 을 선택하자  

데이터베이스는 앞서 생성한 DATA 디스크그룹에 생성한다. 

 귀찮으니까 같은 패스워드로

설치중 root 로 진행하는 부분을 자동으로 설정 

설치가 완료되면 sqlplus 로 DB에 접속해보자. 

grid 환경으로 바꾸고(grid_env) crsctl stat res -t 명령어를 치면 database 가 online 으로 표시 되면 끝