'OraclE' 카테고리의 글 목록 (2 Page)

-- =====================================================
-- == OGG 동기화 테이블 추가
-- == 대상 : SCOTT.STB_PROD_STOCK_OPT
-- =====================================================
-- == 1. supplemental logging (ogg에서)
-- == 2. extract 파라미터 대상 테이블선언
-- == 3. extract 재기동
-- == 4. 테이블 exp/imp scn
-- == 5. replicate 생성
-- == 6. scn base replicat start
-- == 7. replicat 병합
-- =====================================================

-- =====================================================
-- 소스 OGG
-- =====================================================
-- 대상 테이블 : SCOTT.STB_PROD_STOCK_OPT

-- == 1. supplemental logging (ogg에서)
-- 디비 로그인
GGSCI> dblogin UserID oraogg, password oraogg01
GGSCI> add trandata SCOTT.STB_PROD_STOCK_OPT

-- == 2. extract 파라미터 대상 테이블선언
-- dext001 파라미터에 SCOTT.STB_PROD_STOCK_OPT 테이블 추가
GGSCI (testdb02p as oraogg@TEST2) 19> edit param dext001

TABLE SCOTT.STB_PROD_STOCK_OPT;

GGSCI (testdb02p as oraogg@TEST2) 27> send dext001 showtrans

Sending SHOWTRANS request to EXTRACT DEXT001 ...

------------------------------------------------------------
XID:                  0.3114.6.4282796
Items:                0
Extract:              DEXT001
Redo Thread:          2
Start Time:           2022-01-13:13:10:49
SCN:                  8795.3898902885 (37778136271205)
Redo Seq:             150728
Redo RBA:             8026364444
Status:               Running

-- ogg extract 빠른 재기동을 위한 체크포인트 생성
GGSCI (testdb02p as oraogg@TEST2) 31> send dext001 br brcheckpoint immediate
GGSCI (testdb02p as oraogg@TEST2) 34> SEND EXTRACT DEXT001, FORCESTOP

-- == 3. extract 재기동
-- ogg extract 시작
GGSCI (testdb02p as oraogg@TEST2) 43> start dext001

GGSCI (testdb02p as oraogg@TEST2) 60> sh tail -f ./dirrpt/DEXT001.rpt

GGSCI (testdb02p as oraogg@TEST2) 63> stats zpmp001 table SCOTT.STB_PROD_STOCK_OPT

stats dext001 table SCOTT.STB_PROD_STOCK_OPT

-- =====================================================
-- expdp/impdp
-- =====================================================

-- == 4. 테이블 exp/imp scn
소스DB에서 해당 테이블을 scn 포함하여 expdp 수행
타겟DB에 remap_schema, remap_tablespace 사용하여 impdp 수행

-- TEST2 현재 scn 조회
SYS@TEST2> col CURRENT_SCN for 99999999999999999999999
SYS@TEST2> select current_scn from v$database;

             CURRENT_SCN
------------------------
          38106140088422

1 row selected.

-- TEST2 expdp
cd /DBMS/LFMP/BACKUP_PUMP_DIR

vi exp_ogg.par
-- ----------------------------------------------
DIRECTORY=BACKUP_PUMP_DIR
DUMPFILE=expogg.dmp
TABLES=SCOTT.STB_PROD_STOCK_OPT
FLASHBACK_SCN=38106140088422
-- ----------------------------------------------

expdp mon/mon PARFILE=exp_ogg.par

scp ./expogg.dmp oraREPLTEST@192.168.0.123:/DBMS/REPLTEST/dump/

-- REPLTEST impdp
cd /DBMS/REPLTEST/dump

vi imp_ogg.par
-- ----------------------------------------------
DIRECTORY=DIR1029
DUMPFILE=expogg.dmp
LOGFILE=impogg.log
REMAP_SCHEMA=(SCOTT:SCOTTREAL)
REMAP_TABLESPACE=(TS_TEST_DAT:TS_REAL_DAT ,TS_TEST_IDX:TS_REAL_DAT )
-- ----------------------------------------------

impdp mon/mon PARFILE=imp_ogg.par

-- =====================================================
-- 타겟 OGG
-- =====================================================

-- == 5. replicate 생성
-- 기존 replicate (이름 zrep011) 복제 하여 신규 replicate (이름 ztmp) 생성
retestdt:/DBMS/REPLTEST/ogg19c/dirprm> cp zrep011.prm ztmp.prm

GGSCI (retestdt) 4> add rep ztmp exttrail ./dirdat/zw
REPLICAT added.

-- 신규 replicate (이름 ztmp) 파라미터 수정
GGSCI (retestdt) 6> edit param ztmp

MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT;

GGSCI (retestdt) 34> sh ls -lrt ./dirdat/

total 22524288
-rw-r----- 1 oraREPLTEST dba 1023999912 Apr  3 17:43 zw000006421
-rw-r----- 1 oraREPLTEST dba 1023999687 Apr  3 17:43 zw000006422
-rw-r----- 1 oraREPLTEST dba 1023999977 Apr  3 18:43 zw000006423
-rw-r----- 1 oraREPLTEST dba 1023999820 Apr  3 18:44 zw000006424
-rw-r----- 1 oraREPLTEST dba 1023999944 Apr  3 19:44 zw000006425
-rw-r----- 1 oraREPLTEST dba 1023999839 Apr  3 19:51 zw000006426
-rw-r----- 1 oraREPLTEST dba 1023999759 Apr  3 20:03 zw000006427
-rw-r----- 1 oraREPLTEST dba 1023999979 Apr  3 21:40 zw000006428
-rw-r----- 1 oraREPLTEST dba 1023999760 Apr  3 22:05 zw000006429
-rw-r----- 1 oraREPLTEST dba 1023999941 Apr  4 00:33 zw000006430
-rw-r----- 1 oraREPLTEST dba 1023999979 Apr  4 01:53 zw000006431
-rw-r----- 1 oraREPLTEST dba 1023999866 Apr  4 02:50 zw000006432
-rw-r----- 1 oraREPLTEST dba 1023999646 Apr  4 04:13 zw000006433
-rw-r----- 1 oraREPLTEST dba 1023999758 Apr  4 07:43 zw000006434
-rw-r----- 1 oraREPLTEST dba 1023999824 Apr  4 07:43 zw000006435
-rw-r----- 1 oraREPLTEST dba 1023999767 Apr  4 08:02 zw000006436
-rw-r----- 1 oraREPLTEST dba 1023999924 Apr  4 08:03 zw000006437
-rw-r----- 1 oraREPLTEST dba 1023999962 Apr  4 08:39 zw000006438
-rw-r----- 1 oraREPLTEST dba 1023999749 Apr  4 08:43 zw000006439
-rw-r----- 1 oraREPLTEST dba 1023999985 Apr  4 09:46 zw000006440
-rw-r----- 1 oraREPLTEST dba 1023999864 Apr  4 09:49 zw000006441
-rw-r----- 1 oraREPLTEST dba 1023999722 Apr  4 09:56 zw000006442
-rw-r----- 1 oraREPLTEST dba  407555339 Apr  4 10:34 zw000006443

-- 리스트 중 expdp 시점보다 이전에 생성된 번호 사용
GGSCI (retestdt) 35> alter ztmp extseqno 6439 extrba 0

2022-04-04 10:36:30  INFO    OGG-06594  Replicat ZTMP has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start ZTMP with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.

-- == 6. scn base replicat start
-- expdp시 사용한 scn값 사용하여 ztmp 시작
GGSCI> start ztmp aftercsn 38106140088422

GGSCI> dblogin UserID oraogg, password oraogg01
GGSCI (retestdt as ORAOGG@REPLTEST) 115> stats ztmp total

-- == 7. replicat 병합

-- =====================================================
-- 소스 OGG
-- =====================================================
-- 소스 전송프로세스 중지
GGSCI (testdb02p as oraogg@TEST2) 69> stop zpmp001

Sending STOP request to EXTRACT ZPMP001 ...
Request processed.

-- =====================================================
-- 타겟 OGG
-- =====================================================
-- 타켓 프로세스 중지
GGSCI (retestdt as ORAOGG@REPLTEST) 124> stop ztmp

Sending STOP request to REPLICAT ZTMP ...
Request processed.

GGSCI (retestdt as ORAOGG@REPLTEST) 125> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     ZREP001     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:06
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:06
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:06
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:01

-- 병합 할 타켓 프로세스 중지
GGSCI (retestdt as ORAOGG@REPLTEST) 126> stop zrep001

Sending STOP request to REPLICAT ZREP001 ...
Request processed.

GGSCI (retestdt as ORAOGG@REPLTEST) 127> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     ZREP001     00:00:00      00:00:01
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:00
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:00
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:15

GGSCI (retestdt as ORAOGG@REPLTEST) 128> view param ztmp

replicat ztmp

--# Setenv
USERID ORAOGG, PASSWORD oraogg01

DISCARDFILE ./dirout/ztmp.dsc, APPEND, MEGABYTES 2000
DISCARDROLLOVER AT 00:01 ON MONDAY

GETTRUNCATES

--ERROR SKIP PARAMETER
--HANDLECOLLISIONS
REPERROR 1403 DISCARD
REPERROR    1 DISCARD

--# Tune
EOFDELAYCSECS 10

MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT;

GGSCI (retestdt as ORAOGG@REPLTEST) 129> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     ZREP001     00:00:00      00:00:10
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:00
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:00
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:24

-- 병합 할 타겟 프로세스 파라미터 변경
-- MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT; 추가
GGSCI (retestdt as ORAOGG@REPLTEST) 130> edit param zrep001
replicat zrep001

--# Setenv
USERID ORAOGG, PASSWORD oraogg01

DISCARDFILE ./dirout/zrep001.dsc, APPEND, MEGABYTES 2000
DISCARDROLLOVER AT 00:01 ON MONDAY

GETTRUNCATES

--ERROR SKIP PARAMETER
--HANDLECOLLISIONS
REPERROR 1403 DISCARD
REPERROR    1 DISCARD

--# Tune
EOFDELAYCSECS 10
--22EA
... 중략 ...
MAP SCOTT.STB_PROD_STOCK_OPT, TARGET LFMREAL.STB_PROD_STOCK_OPT;

"dirprm/zrep001.prm" 43L, 2067C written

GGSCI (retestdt as ORAOGG@REPLTEST) 131> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     ZREP001     00:00:00      00:00:30
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:00
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:09
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:09
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:10
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:44

-- 병합 대상 타겟 프로세스의 체크포인트 일치 확인
GGSCI (retestdt as oraogg@REPLTEST) 30> info zrep001

REPLICAT   ZREP001   Last Started 2022-03-31 07:00   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:24 ago)
Log Read Checkpoint  File /DBMS/REPLTEST/ogg19c/dirdat/zw000006444
                     2022-04-04 10:49:13.001638  RBA 990214542

GGSCI (retestdt as oraogg@REPLTEST) 31> info ztmp

REPLICAT   ZTMP      Last Started 2022-04-04 10:36   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:50 ago)
Log Read Checkpoint  File /DBMS/REPLTEST/ogg19c/dirdat/zw000006444
                     2022-04-04 10:49:13.001638  RBA 990214542

-- 병합 된 타겟 프로세스 기동
GGSCI (retestdt as ORAOGG@REPLTEST) 135> start zrep001

-- 병합 이전 타겟 프로세스 삭제
GGSCI (retestdt as ORAOGG@REPLTEST) 138> delete ztmp
Deleted REPLICAT ZTMP.

-- =====================================================
-- 소스 OGG
-- =====================================================
-- 소스 전송프로세스 기동
GGSCI (testdb02p as oraogg@TEST2) 43> start zpmp001

Sending START request to MANAGER ...
EXTRACT ZPMP001 starting

'OraclE' 카테고리의 다른 글

SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법 (0)	2022.06.13
SPM(SQL PLAN MANAGEMENT) (0)	2022.06.13
active session history (0)	2022.01.30
orange plan 테이블 생성 (0)	2022.01.30
벌크 insert 빠르게 하는 방법 (0)	2018.03.21

-- 특정 시간 active session 학인
select SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, SESSION_TYPE, USER_ID, SQL_ID, SQL_OPNAME, SQL_EXEC_START, PROGRAM, MODULE, MACHINE
from v$active_session_history
where SAMPLE_TIME >= to_date('2022-01-30 11:59:00','YYYY-MM-DD HH24:MI:SS')
and SAMPLE_TIME <= to_date('2022-01-30 12:02:00','YYYY-MM-DD HH24:MI:SS')
;

'OraclE' 카테고리의 다른 글

SPM(SQL PLAN MANAGEMENT) (0)	2022.06.13
OGG 동기화 테이블 추가 (0)	2022.04.04
orange plan 테이블 생성 (0)	2022.01.30
벌크 insert 빠르게 하는 방법 (0)	2018.03.21
RMAN backup set 에서 archivelog 추출하여 logminor 수행 (0)	2018.02.22

CREATE TABLE PLAN_TABLE -- 테이블 이름
(
STATEMENT_ID     VARCHAR2 (30),
TIMESTAMP DATE,
REMARKS          VARCHAR2 (80),
OPERATION        VARCHAR2 (30),
OPTIONS          VARCHAR2 (255),
OBJECT_NODE      VARCHAR2 (128),
OBJECT_OWNER     VARCHAR2 (30),
OBJECT_NAME      VARCHAR2 (30),
OBJECT_INSTANCE  NUMBER,
OBJECT_TYPE      VARCHAR2 (30),
OPTIMIZER        VARCHAR2 (255),
SEARCH_COLUMNS   NUMBER,
ID               NUMBER,
PARENT_ID        NUMBER,
POSITION         NUMBER,
COST             NUMBER,
CARDINALITY      NUMBER,
BYTES            NUMBER,
OTHER_TAG        VARCHAR2 (255),
PARTITION_START  VARCHAR2 (255),
PARTITION_STOP   VARCHAR2 (255),
PARTITION_ID     NUMBER,
OTHER            LONG,
DISTRIBUTION     VARCHAR2 (30),
CPU_COST         NUMBER,
IO_COST          NUMBER,
TEMP_SPACE       NUMBER,
ACCESS_PREDICATES VARCHAR2 (4000),
FILTER_PREDICATES VARCHAR2 (4000)
);

'OraclE' 카테고리의 다른 글

OGG 동기화 테이블 추가 (0)	2022.04.04
active session history (0)	2022.01.30
벌크 insert 빠르게 하는 방법 (0)	2018.03.21
RMAN backup set 에서 archivelog 추출하여 logminor 수행 (0)	2018.02.22
Oracle RAC 마스터 노드 찾기 (find Master Node) (0)	2017.09.14

기본적으로 100만개의 레코드를 벌크 인서트 할 때 아래 방법을 사용 합니다.

create table LARGE_TBL (id number, value varchar2(50));

begin

for i in 1 .. 1000000 loop

insert into large_tbl values (i,'foobar');

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.53

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1000000 15.67 15.42 11 2838 1040617 1000000

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 1000001 15.67 15.42 11 2838 1040617 1000000

그럼 테이블 레코드 형식의 배열에 값을 저장하고 한번에 insert 하면,

declare

TYPE tbl_ins IS TABLE OF LARGE_TBL%ROWTYPE index by binary_integer;

w_ins tbl_ins;

begin

for i in 1 .. 1000000 loop

w_ins(i).id := i;

w_ins(i).value := 'foobar';

end loop;

forall i in 1 .. 1000000 insert into large_tbl values w_ins(i);

commit;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.26

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.49 0.50 11 4804 24093 1000000

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.49 0.51 11 4804 24093 1000000

느낌이 오시나요?

sql 실행 횟수가 줄어 듭니다.

저작자표시 비영리 변경금지

'OraclE' 카테고리의 다른 글

active session history (0)	2022.01.30
orange plan 테이블 생성 (0)	2022.01.30
RMAN backup set 에서 archivelog 추출하여 logminor 수행 (0)	2018.02.22
Oracle RAC 마스터 노드 찾기 (find Master Node) (0)	2017.09.14
DATAPUMP 암호화 (0)	2017.01.19

JJON's StorY

OraclE

OGG 동기화 테이블 추가

'OraclE' 카테고리의 다른 글

active session history

'OraclE' 카테고리의 다른 글

orange plan 테이블 생성

'OraclE' 카테고리의 다른 글

벌크 insert 빠르게 하는 방법

'OraclE' 카테고리의 다른 글

+ Recent posts

티스토리툴바