따봉 구걸남's 블로그

성능뷰에서 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; --> 복구대상 아님

 끗

타이완은 대중교통이 잘되어있다. 
비행기 스케쥴이 낮이면 메트로를 타고 시내로 들어오면되지만, 나처럼 자정 비행기를 탈 경우 지하철은 이미 끝나있다. 
돈으로 해결하면 머리 아플 필요가 없다. 택시를 타면 된다. 
하지만 다소 비싼 요금( 5만원 상당) 에 갸우뚱 해진다면 버스를 타는 방법이 있다. 
대부분의 여행자들은 시먼딩에 숙소를 많이 잡게 될텐데 (나 또한 그랬듯이) 
국광버스는 시먼딩을 가지 않고 타이베이 메인역으로 간다. 
 
1. 타는 위치

 타오위안 공항에서 arrival hall 지하 1층에 티켓 카운터가 있다. 

 
공항에서 짐찾고 에스컬레이터를 타고 계속 내려와서 편의점을 지나면 있다. 
발권기에서 이지카드로 구매 가능하나 나는 창구에서 현금으로 구매했음. 
심카드는 창구가 닫혔는데 버스는 24시간이라 그런지 새벽에도 여는 듯함. 
티켓을 사고 밖에 나가면 승강장이 있다. 5번 승강장으로 가면 됨.

 
2. 스케쥴

 24시간 운영 이긴 하지만 심야시간은 배차간격이 길다. 
주간배차는 10~15분 간격, 새벽 2시10 분부터 4시 까지는 1시간에 1번 배차된다. 
 
3. 노선

 버스는 타오위안 공항에서 타이베이 메인역 북쪽 방면에서 내려온다. 
공항부터 시내까지 정차 없이 쭉 가다가 시내에서 몇번 정차하고 메인역을 종점으로 모든 승객이 내린다.

 
시먼딩까지 안가는건 아쉽지만 다행이 거리가 그닥 멀지는 않다. 
다만 나는 택시를 타려다가 엄청난 냄새때문에 포기했다...