따봉 구걸남's 블로그

조인을 통한  업데이트 작업시 보통 서브쿼리를 활용하게 되는데 

EMP 와 DEPT 라는 테이블을 예시로 들면 아래와 같은데.. 

UPDATE emp e
SET e.dname = (
  SELECT d.dname
  FROM dept d
  WHERE d.deptno = e.deptno
)
WHERE EXISTS (
  SELECT 1
  FROM dept d
  WHERE d.deptno = e.deptno
);

이 방식의 단점은 SET 절의 서브쿼리가 업데이트할 행마다 반복 실행된다는 점이다. 

단 건의 업데이트는 비효율이 크지 않지만 대량 업데이트 일수록 단점이 커진다. 

오라클에서는 이를 수정가능 조인뷰로 처리할수 있고 아래와 같다. 

UPDATE (
  SELECT e.dname AS e_dname,
         d.dname AS d_dname
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.deptno IS NOT NULL
)
SET e_dname = d_dname; 

주의할 점은 1:M 관계에서 M 에 해당하는 컬럼만 수정이 가능한 제약이 있다. 

위의 경우에서는 dept 에 속한 컬럼을 수정하려고 하면 에러가 날 것이다. 

UPDATE (
  SELECT e.empno,,
 d.dname
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.empno = 7788
)
SET dname = 'RESEARCH';

 => 에러발생 

ora-01779 cannot modify a column which maps to a non key-preserved table

 # 타 DBMS 에서는.. 

- PostgreSQL: 조인이 포함된 뷰는 기본적으로 수정 불가  (단, INSTEAD OF 트리거를 사용하면 우회 가능)
- MySQL: 일부 조건에서 조인 뷰 업데이트가 가능, 오라클만큼 유연하지는 않으며 제약이 많다.
- SQL Server: 조인 뷰 업데이트는 제한적, 대부분 INSTEAD OF 트리거를 사용

성능뷰에서 SQL 수집하기 

 -- 셀프로 선정 v$sqlarea

SELECT *
FROM (
    SELECT
        parsing_schema_name,
        sql_id,
        plan_hash_value,
        executions,
        buffer_gets,       -- logical reads
        disk_reads,        -- physical reads
        rows_processed,
        cpu_time,          -- total CPU time in microseconds
        elapsed_time,      -- total elapsed time in microseconds
        ROUND(cpu_time / DECODE(executions, 0, 1, executions) / 1000000, 2) AS avg_cpu_sec,
        ROUND(elapsed_time / DECODE(executions, 0, 1, executions) / 1000000, 2) AS avg_elapsed_sec,
        SUBSTR(sql_text, 1, 100) AS sql_text_snippet
    FROM v$sqlarea
    WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM')
      AND executions > 0
    ORDER BY cpu_time DESC
)
WHERE ROWNUM <= 20;

-- sql full text 확인
SET LONG 100000
SET PAGESIZE 500
SET LINESIZE 150

--SELECT DBMS_LOB.SUBSTR(SQL_FULLTEXT, 100000, 1) AS full_sql_text
SELECT DBMS_LOB.SUBSTR(SQL_FULLTEXT, DBMS_LOB.GETLENGTH(SQL_FULLTEXT), 1)
-- SELECT SQL_FULLTEXT
FROM v$sql
WHERE sql_id = 'gngtvs38t0060';

 -- 바인드 값
SELECT sql_id, name, position, datatype_string, value_string, was_captured, last_captured
  FROM v$sql_bind_capture
 WHERE sql_id = '91gau055wpf2p'
 ORDER BY position;

-- ACS 확인
 SELECT sql_id,
              sql_fulltext,
             IS_BIND_SENSITIVE,
             IS_BIND_AWARE,
             LAST_LOAD_TIME,  
             SQL_PROFILE,
             SQL_PLAN_BASELINE,
             LAST_ACTIVE_TIME
   FROM v$sql
  WHERE sql_id =''
 
 

virtual box 사용하여 오라클 19c asm 환경에서 만들어진 백업본으로 파일시스템 환경 서버에 복구하기

1. 환경세팅

대략적인 세팅은 아래와 같다. 

hostname(source) : bnrtest
SID (source) : oclasm
hostname(target) : recodb
SID (target) : ora19

oclasm 는 ASM 으로 설치하고 ora19 는 filesystem 으로 설치한다. 

* ora19 는 그냥 엔진이 필요하니까 설치한거다.

single DB ASM 설치는 아래 글을 참조한다. 

19c grid 설치 Standalone server (Oracle Restart)

grid 설치가 잘 완료되었는지 아래 명령으로 확인 해본다. 

crsctl stat res -t  
crsctl start has
crsctl stop has
* crs log 위치 :  $ORACLE_BASE/diag/crs/<hostname>/crs/trace/alert.log

2. 백업 디스크 추가하기 

source 에서 백업한 파일을  target 에 전달해주기 위해 source VM 에 백업디스크를 추가해준다. 

디스크는 VM 이 내려간 상태에서 추가 가능하다. 

설정 -> 저장소 -> 디스크 추가 

 lsblk 로 확인하고 사용전 포맷해준다. (* root 수행) 

lsblk
fdisk -l /dev/sdg

fdisk /dev/sdg
n -> p -> 1 -> ent -> ent -> w 

mkfs.ext4 /dev/sdg1

/data 란 이름으로 디스크를 만들어 붙여준다. 

mkdir -p /data
mount /dev/sdg1 /data
df -h /data

chown oracle:oinstall /data 

3. 백업 복구 시나리오

테이블스페이스 3개를 생성하고 그 중 일부만 (TS_RECO) 복구한다.  

 1) 테이블스페이스 생성
CREATE TABLESPACE TS_NORECO1 datafile size 10M;
CREATE TABLESPACE TS_NORECO2 datafile size 10M;
CREATE TABLESPACE TS_RECO datafile size 10M;

  
 2) 유저 생성 

CREATE USER USR_NORECO1 IDENTIFIED BY "user123" default tablespace TS_NORECO1 QUOTA UNLIMITED ON TS_NORECO1;
CREATE USER USR_NORECO2 IDENTIFIED BY "user123" default tablespace TS_NORECO2 QUOTA UNLIMITED ON TS_NORECO2;
CREATE USER USR_RECO    IDENTIFIED BY "user123" default tablespace TS_RECO   QUOTA UNLIMITED ON TS_RECO;

grant connect to USR_NORECO1, USR_NORECO2, USR_RECO;
grant resource to USR_NORECO1, USR_NORECO2, USR_RECO;


 3) 테이블 생성 
 
CREATE TABLE USR_NORECO1.TAB1(A INT, B TIMESTAMP, C VARCHAR2(10)); 
CREATE TABLE USR_NORECO2.TAB1(A INT, B TIMESTAMP, C VARCHAR2(10));
CREATE TABLE    USR_RECO.TAB1(A INT, B TIMESTAMP, C VARCHAR2(10));

col table_name for a20
col owner for a20 
set linesize 200

select owner, table_name, tablespace_name 
  from dba_tables 
 where owner like 'USR%';
 

INSERT INTO USR_NORECO1.TAB1 values(1, SYSDATE, 'A');
INSERT INTO USR_NORECO1.TAB1 values(2, SYSDATE, 'AA');
INSERT INTO USR_NORECO2.TAB1 values(1, SYSDATE, 'B');
INSERT INTO USR_NORECO2.TAB1 values(2, SYSDATE, 'BA');
INSERT INTO    USR_RECO.TAB1 values(1, SYSDATE, 'C');
INSERT INTO    USR_RECO.TAB1 values(2, SYSDATE, 'CA');

SQL> select systimestamp from dual;

 4) 1차 백업 

rman target /

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  BACKUP DATABASE FORMAT '/data/backup/db_%U';
  BACKUP ARCHIVELOG ALL FORMAT '/data/backup/arch_%U';
  BACKUP CURRENT CONTROLFILE FORMAT '/data/backup/ctl_%U';
  RELEASE CHANNEL c1;
}

 5) 추가 트랜잭션 발생 

INSERT INTO USR_NORECO1.TAB1 values(3, systimestamp, 'AB');
INSERT INTO USR_NORECO2.TAB1 values(3, systimestamp, 'BB');
INSERT INTO    USR_RECO.TAB1 values(3, systimestamp, 'CB');

INSERT INTO USR_NORECO1.TAB1 values(4, SYSDATE, 'AC');
INSERT INTO USR_NORECO1.TAB1 values(5, SYSDATE, 'AAA');

INSERT INTO USR_NORECO2.TAB1 values(4, SYSDATE, 'BC');
INSERT INTO USR_NORECO2.TAB1 values(5, SYSDATE, 'BBB');

INSERT INTO    USR_RECO.TAB1 values(4, SYSDATE, 'CC');
INSERT INTO    USR_RECO.TAB1 values(5, SYSDATE, 'CBB');

col b for a30
select * from USR_RECO.TAB1; 
select * from USR_NORECO1.TAB1; 

SQL> SELECT systimestamp FROM DUAL;

  * 목표 복구시점은 drop 전 이다. 

6) 아카이브 로그 생성 
  
ALTER SYSTEM SWITCH LOGFILE;
DROP TABLE USR_RECO.TAB1;
ALTER SYSTEM SWITCH LOGFILE;

col name for a70
set linesize 300 

SELECT thread#,
       sequence#,
       first_time,
       next_time,
       name,
       applied,
       deleted
  FROM v$archived_log
 ORDER BY thread#, sequence#;
 

3. RMAN 아카이브 백업 

RMAN> BACKUP ARCHIVELOG ALL FORMAT '/data/backup/arch_%U';

백업파일 확인 

ls -l /data/backup/

4. recodb VM start  

/data/backup 에 백업이 완료 되었고, 이 디스크를 recodb 에 연결이 필요하다. 

source VM 을 먼저 내려준다. 저장소에서 bak.vdi 를 연결 삭제한다. 

target VM 인 recodb 를 시작하기전 bak.vdi 를 연결 추가 한다. 

1) bak.vdi 연결 

(root로 수행 ) su - 

df -h 
lsblk
mkdir -p /data/
mount /dev/sdb1 /data/
chown oracle:oinstall /data 

ls /data/backup/ 

2) pfile 수정 & directory mkdir 

  rm -rf /app/oracle/admin/oclasm/adump
mkdir -p /app/oracle/admin/oclasm/adump
   ls -l /app/oracle/admin/oclasm/adump

  rm -rf /app/oracle/oradata/oclasm/
mkdir -p /app/oracle/oradata/oclasm/
   ls -l /app/oracle/oradata/oclasm/

  rm -rf /app/oracle/arch/oclasm/
mkdir -p /app/oracle/arch/oclasm/
   ls -l /app/oracle/arch/oclasm/


3) nomount 

s1)
echo $ORACLE_SID
export ORACLE_SID=oclasm

sqlplus / as sysdba 
startup nomount pfile='/home/oracle/initoclasm.ora';


s2)
echo $ORACLE_SID
export ORACLE_SID=oclasm
rman target / 
RESTORE CONTROLFILE FROM '/data/backup/ctl_0j4tjtbp_1_1';
ALTER DATABASE MOUNT;

report schema;

 

RUN {
  SET NEWNAME FOR DATAFILE 1 TO '/app/oracle/oradata/oclasm/system01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/app/oracle/oradata/oclasm/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/app/oracle/oradata/oclasm/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 8 TO '/app/oracle/oradata/oclasm/ts_reco01.dbf';
  
  RESTORE DATABASE SKIP TABLESPACE TS_NORECO1, TS_NORECO2, USERS;
  SWITCH DATAFILE 1;
  SWITCH DATAFILE 3;
  SWITCH DATAFILE 4;
  SWITCH DATAFILE 8;
}

RUN {
  SET UNTIL TIME "TO_DATE('2026-07-19 12:47:30', 'YYYY-MM-DD HH24:MI:SS')";
  RECOVER DATABASE SKIP TABLESPACE TS_NORECO1, TS_NORECO2, USERS;
}

나중에 백업한 아카이브를 인식하지 못한다. 

수동으로 인식시켜주자 

CATALOG START WITH '/data/backup/' NOPROMPT;
 

ALTER DATABASE DATAFILE 2 OFFLINE DROP; 
ALTER DATABASE DATAFILE 5 OFFLINE DROP; 
ALTER DATABASE DATAFILE 7 OFFLINE DROP; 

ALTER DATABASE OPEN RESETLOGS;

오픈이 완료되면 테이블을 확인하자. 

col b for a30
select * from USR_RECO.TAB1;     --> 복구대상
select * from USR_NORECO1.TAB1; --> 복구대상 아님

 끗